MySQL主從庫搭建(原創(chuàng))

一、 單主庫配置
1.1環(huán)境說明
本節(jié)講述的環(huán)境為一個主庫，兩個從庫，具體環(huán)境如下。
1.1.1Linux version
Red Hat Enterprise Linux Server release 6.4 (Santiago)
1.1.2 mysql version
5.1.73
1.1.3 master 服務器ip 192.168.212.134， slaver 服務器IP 192.168.212.142 ，slaver 服務器IP 192.168.212.143

目前創(chuàng)新互聯(lián)建站已為近1000家的企業(yè)提供了網站建設、域名、虛擬主機、綿陽服務器托管、企業(yè)網站設計、鳩江網站維護等服務，公司將堅持客戶導向、應用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長，共同發(fā)展。

1.2 master 服務器配置
1.2.1 創(chuàng)建同步賬號
mysql> grant replication slave on . to 'rep'@'192.168.212.%' identified by 'rep';
mysql> flush privileges;
說明： . 代表所有的數據庫的所有表，也可指定對某個數據庫的所有表進行同步，例如testdb.*
192.168.212.%代表對192.168.212網段都可以連接master 數據庫
1.2.2 打開master 數據庫的binlog功能
編輯/etc/my.cng
在mysqld模塊中添加bin-log參數。例如bin-log=master
重啟數據庫
#service mysqld restart
1.2.3 備份數據庫
? 為了保持一致性，檢查binlog。
mysql>show master status;
當前的語句節(jié)點在mysqlbin.000002 ，位置在106，這個在slaver數據庫中恢復起始位置
mysql>show master logs;

? 備份全庫
為了保持數據一致，對于非innodb的庫表需要鎖庫進行備份，可以通過mysql命令mysql>flush table with read lock;鎖數據庫(unlock tables;解鎖)，此命令鎖表的時間是通過系統(tǒng)參數wait_time和interactive_timeout控制，缺省時間是28800秒。如果此命令執(zhí)行窗口關閉，鎖表也失效。
也可以在mysqldump中加參數-x鎖表，如下命令# mysqldump -uroot -B -A -x --events –master-data=2|gzip >/root/mysql_all.gz
對于innodb存儲引擎的表可以在mysqldump中加參數--single-transaction在備份時快照來保持數據一致，如下命令# mysqldump -uroot -B -A --single-transaction --events --master-data=2|gzip >/root/mysql_all.gz
將備份文件傳至slaver 服務器，如下：
Scp /root/mysql_all.gz root@192.168.212.141:/home/mysql/

1.3 Slaver 服務器配置
本部分只對192.168.212.142進行配置，192.168.212.143配置與此相同。
1.3.1 將備份數據導入數據庫，如下：
$gzip –dv /root/mysql_all.gz
$mysql –uroot 1.3.2 設置恢復點
Mysql>change master to
master_host='192.168.212.134',
master_port=3306,
master_user='rep',
master_password='rep',
master_log_file='mysqlbin.000002',
master_log_pos=106;
說明：
把備份文件中change master 命令master_log_file，master_log_pos值填入以上命令對應位置。如果備份時設置--master-data=1（2會把增量位置信息注釋），增量位置信息會寫入備份文件，備份文件導入從數據庫時就會把位置信息一起導入，這樣在從庫執(zhí)行change master 時就不再需要寫master_log_file和master_log_pos。
1.3.3 啟動同步
Mysql>start slave;
1.3.4 檢查主從狀態(tài)
Mysql>show slave status\G;
mysql> show slave status\G;
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.212.134
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqlbin.000004
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000004
Relay_Log_Pos: 250
Relay_Master_Log_File: mysqlbin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 106
Relay_Log_Space: 550
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
ERROR:
No query specified
說明：如果Slave_IO_Running和 Slave_SQL_Running都是yes 說明開始同步，Seconds_Behind_Master說明從庫落后主庫的時間（秒）。

二、多主庫配置
多主庫模式包含超過一個以上的主庫，主庫間數據庫互相同步。Master1、Master2 分別代表兩個主庫。
2.1 參數配置
Mater1:
auto_increment_offset=1 #解決主鍵自增變量沖突.自增初始位置，此例為1
auto_increment_increment=2 #解決主鍵自增變量沖突.自增間隔為2，例如1、3、5
log_slave_updates
Mater2:
auto_increment_offset=2 #解決主鍵自增變量沖突。自增初始位置，此例為2
auto_increment_increment=2 #解決主鍵自增變量沖突。自增間隔為2，例如2、4、6
log_slave_updates

2.2 把master1的數據備份,導入mater2數據庫
參照單主庫的備份與導入章節(jié)。

2.3 master1啟動同步
Mysql> change master to
master_host='192.168.212.141',
master_port=3306,
master_user='rep',
master_password='rep';
mysql>start slave;

2.4 master2啟動同步
Mysql> change master to
master_host='192.168.212.141',
master_port=3306,
master_user='rep',
master_password='rep';
mysql>start slave;

三、Binlog日志的自動刪除
可以通過系統(tǒng)參數expire_logs_days配置Binlog日志保留天數。此參數默認值為0，
例如expire_logs_days=10 ，表示保留10天。

四、讀寫分離
為了防止應用對從庫進行DML操作而參數主從不一致，可以在從庫添加read-only參數，使從庫只讀（super,或all privileges權限用戶不受此限制）。

五、從庫切換為新主庫
5.1選擇同步最新的從庫為新主庫。
5.1.1 查看各從庫如果如下紅框顯示說明同步一致。

5.1.2 查看兩臺從庫的master.info文件，對比不同從庫紅色部分判斷是否最新，選擇logbin file和pos最大的為最新，此例為logbinfile:000006，pos:106。
#cat master.info
15
mysqlbin.000006
106
192.168.212.134
rep
rep
3306
60
0
5.2 把所有從庫relaylog內容都導入庫中。
在每個從庫執(zhí)行stop slave io_thread;show processlist\G;直到看到Has read all relay log;表示從庫導入完畢。
mysql> stop slave io_thread;
mysql> show processlist\G

5.3 新主庫操作

          新主庫的檢查

? 在新主機的數據目錄下，刪除master.info,relay-log.info 兩個文件。
? 確認mysql.user表滿足應用和其它從庫的連接需求。
? 確認去掉了read-only、log_slave_updates系統(tǒng)參數。
? 確認打開了binlog功能。
? 檢查原主庫是否還有沒同步到新主庫的binlog，不沒同步到新從庫的binlog通過mysqlbinlog工具生成可讀命令文檔，導入新主庫。

在同步最新的從庫上執(zhí)行如下語句，把此從庫改成新主庫,語句如下。
Mysql>stop slave; --停止slave服務
Mysql>reset master; --進行主庫binlog初始化工作
#service mysqld restart 重啟數據庫
5.4 其它從庫操作
Mysql>stop slave;
Mysql>change master to master host=’192.168.212.142’; --指向新主庫
Mysql>start slave;
Mysql>show slave status\G;