MySQL主從備份

2024-06-28 16:02:40

字體：大中小

來源：轉載

供稿：網友

master：主服務器 slave：從服務器

1、開啟master的binarylog

打開MySQL的配置文件

# vim /etc/my.cnf

添加以下代碼

log-bin=mysql-binbinlog_format=mixedserver-id=1read-only=0binlog-do-db=<dbname>

重啟master數據庫

# /etc/inint.d/mysqld restart

2、導出master數據庫

鎖定master數據庫

mysql> FLUSH TABLES WITH READ LOCK;

導出master數據庫

# mysqldump --master-data -uroot -p <dbname> > <path>+<dbname>.sql

記錄master的日志信息

mysql> show master status/G*************************** 1. row *************************** File: mysql-bin.000001 Position: 154 Binlog_Do_DB: <dbname> Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

解除鎖定

mysql> UNLOCK TABLES；

3、開啟slave的binarylog

打開MySQL的配置文件

# vim /etc/my.cnf

添加以下代碼

log-bin=mysql-binbinlog_format=mixedserver-id=2replicate-do-db=<dbname>relay_log=mysql-relay-binlog-slave-updates=OFF

重啟slave數據庫

# /etc/inint.d/mysqld restart

4、導入master的數據到slave中

在slave中創建相應的數據庫

CREATE DATABASE `<dbname>` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

導入數據庫

mysql> source <path>+<dbname>.sql

5、在master中創建同步賬戶

grant replication slave on *.* to '<master_user>'@'<slave_host>' identified by '<master_passWord>';

6、在slave中開啟同步

mysql> CHANGE MASTER TO MASTER_HOST='<master_host>', MASTER_USER='<master_user>', MASTER_PASSWORD='<master_password>', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;

此處的MASTER_LOG_FILE、MASTER_LOG_POS的值參考2中的master日志信息

mysql> show master status/G ***************** 1. row ***************** File: mysql-bin.000001 Position: 154 Binlog_Do_DB: < dbname > Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

重啟slave數據庫

# /etc/inint.d/mysqld restart

查看slave的slave線程是否開啟

mysql> show slave status/G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: <master_host> Master_User: <master_user> Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 639 Relay_Log_File: mysqld-relay-bin.001075 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: <dbname> Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 233 Relay_Log_Space: 15668 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

Slave_IO_State: Waiting for master to send event 表示線程已開啟，等待master的數據

7、遇到的問題

1、在my.cnf中加入log-bin=mysql-bin后仍無法開啟binarylog

添加后需重啟MySQL存在多個my.cnf，用find / -name “my.cnf” 找到其他的my.cnf，一般來說，其中有socket=…的一般都是有效的配置文件在my.cnf中添加的代碼位置不對，盡量上移，在[mysqld]的最后面即可

2、slave的狀態中有Slave_IO_Running: Connecting 錯誤

檢查< master_host > 、< master_user >、< master_password >是否手誤填寫錯誤master服務器的3306端口沒有允許外部訪問，在master中添加防火墻例外。然后用telnet <master_host> 3306測試是否連通檢查master中創建的同步賬戶的< slave_host >

上一篇：UDP協議

下一篇：阿里云ECS服務器在Mac下遠程配置ftp服務器