mysql主從備份配置

MySQL主從複製原理
主伺服器資料庫的每次操作都會記錄在其二進位文件mysql-bin.xxx（該文件可以在mysql目錄下的data目錄中看到）中，從伺服器的I/O線程使用專用賬號登錄到主伺服器中讀取該二進位文件，並將文件內容寫入到自己本地的中繼日誌relay-log文件中，然後從伺服器的SQL線程會根據中繼日誌中的內容執行SQL語句。

原理

準備工作
主從伺服器需要有相同的初態

將主伺服器要同步的資料庫枷鎖，避免同步時數據發生改變

mysql>use db;
mysql>flush tables with read lock;
將主伺服器資料庫中數據導出（其中db為示例）

mysql>mysqldump -uroot -pxxxx db > db.sql;
mysql>mysqldump -R -ndt db -uroot -pxxxx > db.sql
備份完成後，解鎖主伺服器資料庫

mysql>unlock tables;
將初始數據導入從伺服器資料庫

mysql>create database db;
mysql>use db;
mysql>source db.sql;
至此，主從資料庫保持一致。下麵配置主從同步。

上述命令是導出資料庫中所有表結構和數據，如果要導出函數和存儲過程的話使用

主伺服器配置
修改MySQL配置

vi /etc/my.cnf
在[mysqld]中添加：

#主資料庫端ID號
server_id = 1
#開啟二進位日誌
log-bin = mysql-bin
#需要複製的資料庫名，如果複製多個資料庫，重覆設置這個選項即可
binlog-do-db = db
#將從伺服器從主伺服器收到的更新記入到從伺服器自己的二進位日誌文件中
log-slave-updates
#控制binlog的寫入頻率。每執行多少次事務寫入一次(這個參數性能消耗很大，但可減小MySQL崩潰造成的損失)
sync_binlog = 1
#這個參數一般用在主主同步中，用來錯開自增值, 防止鍵值衝突
auto_increment_offset = 1
#這個參數一般用在主主同步中，用來錯開自增值, 防止鍵值衝突
auto_increment_increment = 1
#二進位日誌自動刪除的天數，預設值為0,表示“沒有自動刪除”，啟動時和二進位日誌迴圈時可能刪除
expire_logs_days = 7
#將函數複製到slave
log_bin_trust_function_creators = 1
重啟MySQL，創建允許從伺服器同步數據的賬戶

#創建slave賬號sync_account，密碼password
mysql>grant replication slave on . to 'sync_account'@'slave_db_ip' identified by 'password';
#更新資料庫許可權
mysql>flush privileges;
查看主伺服器狀態

記住以下結果值：File以及Position，在從庫配置時會用到。

mysql>show master status\G;
***************** 1. row ****************
File: mysql-bin.000033 #當前記錄的日誌
Position: 337523 #日誌中記錄的位置
Binlog_Do_DB:
Binlog_Ignore_DB:
執行完這個步驟後不要再操作主伺服器資料庫了，防止其狀態值發生變化

從伺服器配置
修改MySQL配置

vi /etc/my.cnf
在[mysqld]中添加

server_id = 2
log-bin = mysql-bin
log-slave-updates
sync_binlog = 0
#log buffer將每秒一次地寫入log file中，並且log file的flush(刷到磁碟)操作同時進行。該模式下在事務提交的時候，不會主動觸發寫入磁碟的操作
innodb_flush_log_at_trx_commit = 0
#指定slave要複製哪個庫
replicate-do-db = db
#MySQL主從複製的時候，當Master和Slave之間的網路中斷，但是Master和Slave無法察覺的情況下（比如防火牆或者路由問題）。Slave會等待slave_net_timeout設置的秒數後，才能認為網路出現故障，然後才會重連並且追趕這段時間主庫的數據
slave-net-timeout = 60
log_bin_trust_function_creators = 1
重啟從伺服器。

執行同步命令

#執行同步命令，設置主伺服器ip，同步賬號密碼，同步位置
mysql>change master to master_host='10.10.20.111',master_user='sync_account',master_password='password',master_log_file='mysql-bin.000033',master_log_pos=337523;
#開啟同步功能
mysql>start slave;
查看從伺服器狀態

mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.20.111
Master_User: account
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000033
Read_Master_Log_Pos: 337523
Relay_Log_File: db2-relay-bin.000002
Relay_Log_Pos: 337686
Relay_Master_Log_File: mysql-bin.000033
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Slave_IO_Running及Slave_SQL_Running進程必須正常運行，即Yes狀態，否則說明同步失敗。 若失敗查看mysql錯誤日誌中具體報錯詳情來進行問題定位。 最後可以去主伺服器上的資料庫中創建表或者更新表數據來測試同步。