MySQL(Percona Server) 5.6 主從複製

標籤：

    MySQL(Percona Server) 5.6.15    主庫：192.168.2.21    從庫：192.168.2.22

 

例如我們同步的資料庫為：test。 如果需要同步多個資料庫下面會有說明。

MySQL 主從複製原理

1. master將改變記錄到二進位日誌(binary log)中（這些記錄叫做二進位日誌事件，binary log events，可以通過show binlog events進行查看）；2. slave將master的binary log events拷貝到它的中繼日誌(relay log)；3. slave重做中繼日誌中的事件，將改變反映它自己的資料。

主庫（192.168.2.21）:

主庫開放 3306 連接埠

# /sbin/iptables -I INPUT -s 192.168.2.0/24 -p tcp --dport 3306 -j ACCEPT# service iptables save

 

查看開放的連接埠：

# service iptables status或# iptables -L -n

 

授權同步賬戶

mysql> GRANT REPLICATION SLAVE ON *.* TO [email protected]‘192.168.2.22‘ IDENTIFIED BY ‘123‘;mysql> flush privileges;

 

授權格式為：GRANT REPLICATION SLAVE ON *.* TO [email protected]‘ipaddress‘ IDENTIFIED BY ‘password‘。

現在可以在 192.168.2.22 從庫上測試是否可以訪問：

# /usr/local/mysql/bin/mysql -h192.168.2.21 -urep -p123

 

配置主庫 my.cnf

# vi /etc/my.cnf[mysqld]log-bin=mysql-bin  #開啟 MYSQL 二進位日誌server-id=1  #主程式庫伺服器 ID，注意伺服器 ID 不能重複binlog-do-db=test  #需要做主從備份的資料庫名，如果複製多個資料庫，重複設定這個選項即可expire-logs-days=7  #只保留 7 天的二進位日誌，以防磁碟被日誌佔滿binlog_format=MIXED  #設定 binlog 的格式為 MIXED

 

binlog 的三種格式 STATEMENT、ROW、MIXED，詳見 MySQL Binlog ROW 和 STATEMENT 選擇。

可以通過 show variables like ‘binlog_format‘; 查看。

儲存，重啟 MySQL：

# service mysql restart

拷貝主庫需要同步的資料到從庫

進入 mysql 終端 /usr/local/mysql/bin/mysql -uroot -p：

設定主庫成唯讀狀態

mysql> use test;mysql> FLUSH TABLES WITH READ LOCK;

重設 binlog 日誌

mysql> RESET MASTER;

查看運行狀態

mysql> SHOW MASTER STATUS\G;******************* 1. row *******************             File: mysql-bin.000001         Position: 120     Binlog_Do_DB: test Binlog_Ignore_DB:Executed_Gtid_Set:1 row in set (0.00 sec)

此處需記錄 File 和 Position 兩個值，下面從庫的 CHANGE MASTER TO 命令中需要用到這兩個值。

接下來切換到從程式庫伺服器，匯出主庫中需要同步的資料，並匯入到從庫：

# /usr/local/mysql/bin/mysqldump -h192.168.2.21 -urep -p test > dumptest.db# /usr/local/mysql/bin/mysql -uroot -p test < dumptest.db

再回到主程式庫伺服器， 解鎖主庫資料庫唯讀狀態

mysql> UNLOCK TABLES;

從庫（192.168.2.22）:

配置從庫 my.cnf

# vi /etc/my.cnf[mysqld]server-id=2  #從程式庫伺服器 ID，注意伺服器 ID 不能重複replicate-do-db=test  #需要做複製的資料庫名，如果複製多個資料庫，重複設定這個選項即可slave-skip-errors=1032,1062,126,1114,1146,1048,1396  #自動跳過的錯誤碼，以防複製出錯被中斷

如果資料庫中有不需要同步的表可以添加 replicate-ignore-table=table_name,... 配置項，自動跳過這些表。

儲存，重啟 MySQL：

# service mysql restart

指定主庫

進入 MySQL 終端：

mysql> CHANGE MASTER TO  MASTER_HOST=‘192.168.2.21‘,  MASTER_USER=‘rep‘,  MASTER_PASSWORD=‘123‘,  MASTER_LOG_FILE=‘mysql-bin.000001‘,  MASTER_LOG_POS=120;

啟動從庫串連

mysql> start slave;

查看從庫狀態

mysql> SHOW SLAVE STATUS\G; ......  Slave_IO_Running: Yes Slave_SQL_Running: Yes ......

兩個 Yes 成功。

測試

現在可以在主庫中插入/更新資料，添加表等操作，看從庫是否可以同步相應的資料。

問題

在最後使用 SHOW SLAVE STATUS\G; 查看 slave 運行狀態時，有一個錯誤：

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

server uuid 是從 MySQL 5.6 開始添加進來的， 由於我偷了個懶，從庫是直接在 VirtualBox 中 Clone 的主庫，所以 /var/lib/mysql/auto.cnf 檔案中的 server-uuid 的值是一樣的。 隨便改一個值，但是值的格式不能變，不與群組中的其他 server-uuid 重複即可。

改完之後，重啟 mysql：

# service mysql restart

再次使用 SHOW SLAVE STATUS\G; 查看狀態正常。

附兩個小命令：

mysql> SHOW SLAVE HOSTS;  #需在 master 中執行，查看 slave 列表，包括相應的 server-uuid 值mysql> show variables like ‘server_uuid‘;  #查看原生 server-uuid 值

Good Luck!

MySQL(Percona Server) 5.6 主從複製