mysql主從同步設定

標籤：超過   資料匯入   my.cnf   效率   主從同步   recover   刪除   row   重啟   

主機設定
/etc/my.cnf

#ID 必須唯一
server-id=1
##二進位檔案名稱首碼，產生的檔案mysql-bin.000001,mysql-bin.000002
log-bin=mysql-bin
##記錄目前有哪些mysql-bin檔案 SHOW MASTER STATUS;
log-bin-index=master-bin.index
##有更新是否立即同步到日誌,效能會低，但不會丟資料。
innodb_flush_log_at_trx_commit=1
sync_binlog=1
##二進位日誌自動刪除的天數。預設值為0,表示“沒有自動刪除”
expire_logs_days=10
##二進位檔案大小,操作大小產生新檔案儲存體(最小4096byte最大1073741824(1G)預設
max_binlog_size=1073741824
##同步哪些資料庫
binlog-do-db=kintech_crm
binlog-do-db=kintech_eq
binlog-do-db=kintech_pd
binlog-do-db=kintech_sys

#二進位複製方式
binlog_format=ROW
#控制當伺服器無法寫入二進位日誌時發生的情況，這可能導致主伺服器日誌變得不一致，從伺服器會失去同步。
#IGNORE_ERROR忽略錯誤繼續進行，ABORT_SERVER當發生錯誤不能寫入二進位日誌時，停止日誌並關閉服務。
binlog_error_action=ABORT_SERVER

##下面配置不是必須的
##資料庫編碼
character-set-server=utf8
##忽略表ming大小寫
lower_case_table_names=1
##開啟慢查詢日誌,超過10秒則記錄到日誌
slow_query_log=1
long_query_time=10.000000
##預設值：128M 專用MySQL伺服器設定的大小： 作業系統記憶體的70%-80%最佳。
##並不是設定的越大越好。設定的過大，會導致system的swap空間被佔用，導致作業系統變慢，從而減低sql查詢的效率。
innodb_buffer_pool_size=500M

建立使用者
grant replication slave on *.* to [email protected]‘%‘ identified by ‘[email protected]‘;
flush privileges;

備份資料
mysqldump -u root -p --databases mytest --master-data > dbdump.db

--master-data 這個參數比較重要
1:首先在備份之前它會調用 FLUSH TABLES WITH READ LOCK 鎖住資料庫，資料庫就不能進行寫操作了。
2:它會在備份檔案最上面記錄 CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000002‘, MASTER_LOG_POS=154，紅色的可能與你測試的不一樣。
它記錄了master伺服器二進位日誌的檔案名稱與位置，也就是當前資料截止的位置，這樣當slave啟動以後，就從這個點開始複製資料。
3:然後備份資料到檔案。
4:最後釋放鎖 UNLOCK TABLES;

將資料匯入從機
scp /var/lib/mysql/dbdump.db [email protected]:/home/mysqldata/

重啟主機
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;（mysql-bin.000002 154）
UNLOCK TABLES;

修改從機
my.ini

#唯一ID
server-id=2
#中繼日誌
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
#二進位日誌自動刪除的天數。預設值為0,表示“沒有自動刪除”
expire_logs_days=10
relay_log_recovery=ON
relay_log_info_repository=TABLE

重啟
CHANGE MASTER TO
MASTER_HOST=‘101.200.50.31‘,
MASTER_USER=‘repl‘,
MASTER_PASSWORD=‘[email protected]‘,
MASTER_LOG_FILE=‘mysql-bin.000007‘,
MASTER_LOG_POS=154;

mysql> START SLAVE;
mysql> show slave status;

mysql主從同步設定