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

                     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

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

? 備份全庫
為了保持數(shù)據(jù)一致，對于非innodb的庫表需要鎖庫進行備份，可以通過mysql命令mysql>flush table with read lock;鎖數(shù)據(jù)庫(unlock tables;解鎖)，此命令鎖表的時間是通過系統(tǒng)參數(shù)wait_time和interactive_timeout控制，缺省時間是28800秒。如果此命令執(zhí)行窗口關閉，鎖表也失效。
也可以在mysqldump中加參數(shù)-x鎖表，如下命令# mysqldump -uroot -B -A -x --events –master-data=2|gzip >/root/mysql_all.gz
對于innodb存儲引擎的表可以在mysqldump中加參數(shù)--single-transaction在備份時快照來保持數(shù)據(jù)一致，如下命令# 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 將備份數(shù)據(jù)導入數(shù)據(jù)庫，如下：
$gzip –dv /root/mysql_all.gz
$mysql –uroot </root/mysql_all
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會把增量位置信息注釋），增量位置信息會寫入備份文件，備份文件導入從數(shù)據(jù)庫時就會把位置信息一起導入，這樣在從庫執(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說明從庫落后主庫的時間（秒）。

二、多主庫配置
多主庫模式包含超過一個以上的主庫，主庫間數(shù)據(jù)庫互相同步。Master1、Master2 分別代表兩個主庫。
2.1 參數(shù)配置
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的數(shù)據(jù)備份,導入mater2數(shù)據(jù)庫
參照單主庫的備份與導入章節(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)參數(shù)expire_logs_days配置Binlog日志保留天數(shù)。此參數(shù)默認值為0，
例如expire_logs_days=10 ，表示保留10天。

四、讀寫分離
為了防止應用對從庫進行DML操作而參數(shù)主從不一致，可以在從庫添加read-only參數(shù)，使從庫只讀（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 新主庫操作

          新主庫的檢查

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

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