mysql主從復制

在應用開發(fā)中，為了解決單臺mysql服務器負載壓力，將兩臺及更多臺mysql服務器設(shè)置主從關(guān)系，并將數(shù)據(jù)通過mysql數(shù)據(jù)日志方式維護數(shù)據(jù)同步。其中主服務器（master server）作為增刪改的服務器，并及時將數(shù)據(jù)同步到從服務器（slave server），從服務器作為查詢服務器（select）。好處是從服務器作為查詢服務器，分擔主服務器的查詢負載壓力。同時從服務器也可作為數(shù)據(jù)備份。

環(huán)境說明：centos6.5，主服務器mysql5.5，從服務器mysql6.6。主服務器10.0.0.21，從服務器10.0.0.11。需要保證服務器之間能夠ping通，以及mysql防火墻正常訪問。

master的配置

在linux系統(tǒng)中，編輯主服務器的mysql配置文件，例如文件所在/etc/my.cnf。配置在[mysqld]節(jié)點下，如示例：

log-bin=mysql-bin

server-id=1

binlog-do-db=database1

binlog-do-db=database2

binlog-ignore-db=information_schema

binlog-ignore-db=mysql

expire_logs_days = 5

max_binlog_size = 500M

上面實例中，log-bin=mysql-bin則為開啟mysql二進制日志記錄；server-id可以取值，需要保證唯一性；binlog-do-db則是指定需要同步的數(shù)據(jù)庫，多個數(shù)據(jù)庫按照上面3、4行類似換行寫入，不填寫則代表同步全部數(shù)據(jù)庫；binlog-ignore-db則是需要排除的指定數(shù)據(jù)庫，不寫則不排除，原理同binlog-do-db；expire_logs_days代表過期后的文件幾天內(nèi)刪除；max_binlog_size代表單個日志文件的最大值，超過最大值生成新的日志文件；如果不設(shè)置最后兩項，則日志文件無限量生長，數(shù)據(jù)量過大的話會占用服務器磁盤空間。

：wq保存選項，重啟數(shù)據(jù)庫，service mysqld restart。

接下來進入mysql命令行，創(chuàng)建用戶并授權(quán)

mysql>CREATE USER 'slaveuser'@'10.0.0.11' IDENTIFIED BY 'slavepass';

mysql>GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'10.0.0.11';

mysql>flush privileges;?

查看主服務器信息

mysql> show master status;

+------------------+----------+-------------------+------------------+

| File? ? ? ? ? ? | Position | Binlog_Do_DB? ? ? | Binlog_Ignore_DB |

+------------------+----------+-------------------+------------------+

| mysql-bin.000446 |? 7659266 | adserver,copytest |? ? ? ? ? ? ? ? ? |

+------------------+----------+-------------------+------------------+

1 row in set (0.00 sec)

上面中，file字段表示當前主庫的bin-log文件，position表示記錄的行數(shù)。binlog-do-db表示當前所記錄的數(shù)據(jù)庫二進制文件，binlog-ignore-db表示當前排除記錄的數(shù)據(jù)庫，表格記錄為空，表示沒有任何指定排除的數(shù)據(jù)庫。

同步數(shù)據(jù)庫

#在主庫導出數(shù)據(jù)庫sql語句，保存在data目錄下。

mysqldump -uroot -ppassword? database? --skip-lock-tables>/data/database.sql

#將database.sql拷貝到從服務器。執(zhí)行mysql命令

#創(chuàng)建database數(shù)據(jù)庫

CREATE DATABASE database CHARACTER SET utf8 COLLATE utf8_general_ci;

use database;

#使用source命令導入數(shù)據(jù)到database

source /data/database.sql

slave的配置

配置從庫的my.cnf文件,在[mysqld]節(jié)點下編輯如下內(nèi)容。

log-bin=mysql-bin

server-id=2

:wq保存退出。重啟mysql服務：service mysqld restart。

進入到mysql命令行模式，操作如下：

mysql> stop slave;? #關(guān)閉Slave

mysql>set global ?sql_slave_skip_counter=1;? ? #跳過錯誤sql，保持同步進行

mysql> change master to master_host='10.0.0.21',master_user='slaveuser',master_password='slavepass',master_log_file='mysql-bin.000446', master_log_pos=0;

mysql> start slave;? #開啟Slave

在執(zhí)行show slave status查看當前配置狀態(tài)：

當slave_io_running和slave_sql_running都為yes時，則表明當前處于正常同步狀態(tài)。

測試，可以在主庫中增加一個測試表，增加或修改數(shù)據(jù)的同時，查看從庫的數(shù)據(jù)。當數(shù)據(jù)一致時，則表明當前配置成功；

配置完成后，經(jīng)過測試。有如下總結(jié)：

1、數(shù)據(jù)的實時同步很快的。幾乎沒有延遲。

2、每次主服務器重啟mysql服務后，都會使用新的binlog日志文件。slave在查看狀態(tài)時，也會自動使用新的二進制文件進行監(jiān)聽數(shù)據(jù)。所以不用擔心主服務器重啟后，每次都要手動更新配置二進制文件。

3、在配置的時候，例如my.cnf文件需要備份，防止修改出錯后，及時調(diào)整回來。避免出現(xiàn)mysql因為長時間不能正常啟動，尤其是在生產(chǎn)環(huán)境。

4、如果在數(shù)據(jù)庫導出的時候，數(shù)據(jù)在不斷的增長，而mysql服務器友不能停，所以會出現(xiàn)主從服務器延遲帶來的數(shù)據(jù)差異。為了防止導出過程中延遲帶來的數(shù)據(jù)差異過大，可在導出數(shù)據(jù)庫后，立刻重啟master的mysql服務器。在show master status信息，導出數(shù)據(jù)到slave后，設(shè)置slave同步時，填寫最新的二進制文件，日志文件起始行填寫為0?？勺畲蟪潭瓤s小數(shù)據(jù)差異。

嗯……，困了，整理完畢。晚安??

------------------------------------------------2018-11-20更新-----------------------------------------

今天因為同事在主庫跨庫查詢另外一個庫得sql語句，造成結(jié)構(gòu)更新事件同步執(zhí)行到了從庫，因為從庫數(shù)據(jù)庫群與主庫數(shù)據(jù)庫群不一致，導致從庫更新失敗。經(jīng)過了解，重新布置了一次主從同步，在從庫即將開始（start salve操作）前，設(shè)置跳過錯誤事件。如下：

mysql>?set global??sql_slave_skip_counter=1;? ? #跳過錯誤sql，保持數(shù)據(jù)庫同步進行