MySQL 資料主從同步

moscot發表於2020-04-10
MySql主從同步

環境搭建

主伺服器配置

[mysqld]
#base configuration
server-id=1
port=3306
#binlog-do-db=proxy_heart_beat // 設定指定資料庫生成二進位制檔案
basedir=/usr/local/mysql
datadir=/data/dbfiles/mysql
tmpdir=/data/dbfiles/mysql/tmp
socket=/data/dbfiles/mysql/mysql.sock
pid-file=/data/dbfiles/mysql/mysql.pid
log-error=/data/dblogs/mysql/mysql.err

#log-bin  configuration
log-bin = mysql-bin
log-bin-index = mysql-bin.index
binlog_format=mixed
log_bin_trust_function_creators=1
expire_logs_days=10

從伺服器配置

[client]
port=3306
socket= /data/dbfiles/mysql/mysql.sock
[mysqld]
#base configuration
server-id=2
port=3306
// 兩種思路 第一種主機設定生成二進位制檔案的庫，第二種從庫選擇需要同步的庫
// 推薦第二種，第一種有風險會導致其他庫無法生成二進位制。第二種從機有同步二進位制但是沒有執行
// 兩種只要配置一種即可，不要主從都配置。本人測試會導致不生效。
#replicate-do-db    //設定需要複製的資料庫 ，多個增加多條，不要用,分隔。
#replicate-ignore-db //設定需要忽略的複製資料庫
#replicate-do-table  //設定需要複製的表
#replicate-ignore-table //設定需要忽略的複製表
#replicate-wild-do-table //同replication-do-table功能一樣，但是可以萬用字元
#replicate-wild-ignore-table //同replication-ignore-table功能一樣，但是可以加萬用字元
basedir=/usr/local/mysql
datadir=/data/dbfiles/mysql
tmpdir=/data/dbfiles/mysql/tmp
socket=/data/dbfiles/mysql/mysql.sock
pid-file=/data/dbfiles/mysql/mysql.pid
log-error=/data/dblogs/mysql/mysql.err

#log-bin  configuration
log-bin = mysql-bin
log-bin-index = mysql-bin.index
binlog_format=mixed
log_bin_trust_function_creators=1
expire_logs_days=10

#slow_query_log 5 seconds for slow query
slow_query_log=1
long_query_time=5
slow_query_log_file=/data/dblogs/mysql/slow.log

#character set
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

#other configuration
lower_case_table_names=0

相關操作

１）基本思路

確保資料相同: 最好同一個sql檔案匯入，如果不限定指定資料庫，最後完全一樣

配置主伺服器：啟用binlog日誌，授權使用者，檢視當前正在使用的日誌

配置從伺服器：設定server_id（唯一），指定主庫資訊

測試配置：客戶端連線主庫寫入資料，在從庫上也能查詢到
2 ）操作
1.首先需要更新主從資料庫配置檔案到上面的配置，然後重新啟動資料庫 sudo service mysql restart

2.在主伺服器上建立slave使用者用於主從複製任務。並需要授權該使用者到從伺服器。
grant replication slave on . to ‘slave‘@此處是IP,或者”*號” identified by 此處是密碼;
flush privileges; 重新整理許可權

3.show master status\G; 檢視主機日誌記錄點。

4.CHANGE MASTER TO MASTER_HOST = ‘此處是主機IP‘, MASTER_USER = ‘slave’,MASTER_PASSWORD = ‘此處是密碼‘,MASTER_PORT = 3306,MASTER_LOG_FILE=’mysql-bin.000004’,MASTER_LOG_POS=691,MASTER_RETRY_COUNT = 60,MASTER_HEARTBEAT_PERIOD = 10000; // 設定從機同步主機的相關資訊 MASTER_LOG_FILE 為上一步驟檢視的檔名，MASTER_LOG_POS 為上一步驟檢視的二進位制節點。

5.如果是已經設定過 最好 在執行上一步驟前 stop slave; reset slave; 停止並重制下複製同步任務。

6.開始 start slave;

7.show slave status\G;檢視同步任務狀態 slave_id_running,slave_sql_running,如果都no,說明同步狀態錯誤，需要重新檢查下，如果都為Yes,說明同步任務開啟，但是如果資料庫不一致可以還是會導致同步無效。

8.測試主機update 從機自動更新。 如果沒有效果可以嘗試把資料庫表刪除重新從主機匯出匯入。作者就是多次嘗試最後才成功。

指定庫同步

兩種思路 第一種主機設定生成二進位制檔案的庫，第二種從庫選擇需要同步的庫
推薦第二種，第一種有風險會導致其他庫無法生成二進位制。第二種從機有同步二進位制但是沒有執行
兩種只要配置一種即可，不要主從都配置。本人測試會導致不生效。
主庫配置

#binlog-do-db=proxy_heart_beat

從庫配置

#replicate-do-db    //設定需要複製的資料庫 ，多個增加多條，不要用,分隔。
#replicate-ignore-db //設定需要忽略的複製資料庫
#replicate-do-table  //設定需要複製的表
#replicate-ignore-table //設定需要忽略的複製表
#replicate-wild-do-table //同replication-do-table功能一樣，但是可以萬用字元
#replicate-wild-ignore-table //同replication-ignore-table功能一樣，但是可以加萬用字元
本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章