MySQL 主從復制資料匯總

1，復制原理

官方參考文檔：http://dev.mysql.com/doc/refman/5.6/en/replication.html

博客地址1：http://blog.csdn.net/mchdba/article/details/11354771

博客地址2：http://blog.csdn.net/mchdba/article/details/8717513

 如下圖1.png所示：

該過程的第一部分就是master記錄二進制日志。在每個事務更新數(shù)據(jù)完成之前，master在二日志記錄這些改變。MySQL將事務串行的寫入二進制日志，即使事務中的語句都是交叉執(zhí)行的。在事件寫入二進制日志完成后，master通知存儲引擎提交事務。

    下一步就是slave將master的binary log拷貝到它自己的中繼日志。首先，slave開始一個工作線程——I/O線程。I/O線程在master上打開一個普通的連接，然后開始binlog dump process。Binlog dump process從master的二進制日志中讀取事件，如果已經(jīng)跟上master，它會睡眠并等待master產(chǎn)生新的事件。I/O線程將這些事件寫入中繼日志。

    SQLslave thread（SQL從線程）處理該過程的最后一步。SQL線程從中繼日志讀取事件，并重放其中的事件而更新slave的數(shù)據(jù)，使其與master中的數(shù)據(jù)一致。只要該線程與I/O線程保持一致，中繼日志通常會位于OS的緩存中，所以中繼日志的開銷很小。

    此外，在master中也有一個工作線程：和其它MySQL的連接一樣，slave在master中打開一個連接也會使得master開始一個線程。復制 過程有一個很重要的限制——復制在slave上是串行化的，也就是說master上的并行更新操作不能在slave上并行操作。

2，mysql主從同步應用場景

（1）：數(shù)據(jù)分布

（2）：負載均衡

（3）：備份

（4）：高可用和容錯

3，搭建mysql主從的環(huán)境要求

主從系統(tǒng)要保持一致：包括數(shù)據(jù)庫版本，操作系統(tǒng)版本，磁盤IO磁盤容量，網(wǎng)絡帶寬等。

[root@data02 ~]# cat /etc/redhat-release

CentOS release 6.2 (Final)

[root@data02 ~]#

4，開始搭建mysql主從復制

4.1建立復制賬號

GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.*TO repl@'192.168.52.130' IDENTIFIED BY 'repl_1234';

建立一個復制賬號，只允許從192.168.52.130上來訪問登錄主庫進行二進制日志傳輸同步。PS:如果mysql版本新舊密碼算法不同，可以設置set password for 'backup'@'10.100.0.200'=old_password('1234')）

4.2 手動同步數(shù)據(jù)

因為當開始搭建的時候，主庫上已經(jīng)有了數(shù)據(jù)，所以要先把主庫已經(jīng)存在的數(shù)據(jù)先手動同步遷移到從庫上面去。搭建過程中，禁止在主庫從庫上進行任何對數(shù)據(jù)庫的ddl、dml等數(shù)據(jù)操作。

這里可以用mysqldump也可以用xtrabackup導出主庫上面的數(shù)據(jù)：

（4.2.1）：xtrabackup方式

在主庫上192.168.52.129上面進行數(shù)據(jù)備份，備份命令，要添加--safe-slave-backup參數(shù)：

innobackupex --user=backup--password="123456" --host=192.168.52.129  --socket=/tmp/mysql.sock--defaults-file=/etc/my.cnf /data/backups/mysql/repl/backup_slave --parallel=3--safe-slave-backup --no-timestamp

去備份目錄/data/backups/mysql/repl/backup_slave查看備份時候的主庫二進制信息，需要根據(jù)這個二進制信息來進行數(shù)據(jù)同步，如下所示：

[root@data01 test]# cd/data/backups/mysql/repl/backup_slave

[root@data01 backup_slave]#  more xtrabackup_binlog_info

mysql-bin.000147    120

[root@data01 backup_slave]#

壓縮備份文件并且傳輸?shù)綇膸?92.168.52.130上面：

tar -zcvf backup_slave.tar.gz backup_slave/

scp backup_slave.tar.gz192.168.52.130:/tmp/

（4.2.2）mysqldump方式

在主庫上192.168.52.129上做基于主庫做數(shù)據(jù)備份

/usr/local/mysql/bin/mysqldump -ubackup--password=123456 --host=192.168.52.129 --single-transaction --flush-logs --master-data=2 --add-drop-table--create-option --quick --extended-insert=false --set-charset --disable-keys -A> /tmp/alldb.sql

壓縮備份文件，傳輸?shù)綇膸?92.168.52.130上面去。

gzip /tmp/alldb.sql

scp /tmp/alldb.sql.gz 192.168.52.130:/tmp/

4.3 配置master庫（192.168.52.129）

Vim /etc/my.cnf

server-id=129                                   # Server ID

log-bin =/home/data/mysql/binlog/mysql-bin

binlog-ignore-db=mysql                          # No sync databases

binlog-ignore-db=test                           # No sync databases

binlog-ignore-db=information_schema             # No sync databases

binlog-ignore-db=performance_schema

binlog-do-db=user_db

保存退出，重啟mysql主庫，可以查看主庫的狀態(tài)，如下：

mysql> show master status;

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

| File             | Position | Binlog_Do_DB |Binlog_Ignore_DB                                | Executed_Gtid_Set |

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

| mysql-bin.000151 |      120 | user_db      |mysql,test,information_schema,performance_schema |                   |

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

1 row in set (0.00 sec)

mysql>

mysql> show master status\G;

*************************** 1. row***************************

            File: mysql-bin.000151

        Position: 120

    Binlog_Do_DB: user_db

 Binlog_Ignore_DB:mysql,test,information_schema,performance_schema

Executed_Gtid_Set:

1 row in set (0.00 sec)

ERROR:

No query specified

mysql>

4.4 配置slave從庫（192.168.52.130）

Slave的配置與master庫類似，如下所示：

Vim /etc/my.cnf

#------------------Master-Slaveconfig-----------------

log-slave-updates=1

replicate-same-server-id=0

server-id=230                                   # Server ID

log-bin=/home/data/mysql/binlog/mysql-bin.log

relay-log=mysql-relay-bin

master-info-repository=TABLE

relay-log-info-repository=TABLE

binlog-ignore-db=mysql                          # No sync databases

binlog-ignore-db=test                           # No sync databases

binlog-ignore-db=information_schema             # No sync databases

binlog-ignore-db=performance_schema

binlog-do-db=user_db

expire-logs-days=10

max_binlog_size = 10485760

server_id 是必須的，而且唯一。slave沒有必要開啟二進制日志，但是在一些情況下，必須設置，例如，如果slave為其它slave的master，必須設置 bin_log。在這里，我們開啟了二進制日志，而且顯示的命名(默認名稱為hostname，但是，如果hostname改變則會出現(xiàn)問題)。

relay_log配置中繼日志，log_slave_updates表示slave將復制事件寫進自己的二進制日志(后面會看到它的用處)。

有些人開啟了slave的二進制日志，卻沒有設置log_slave_updates，然后查看slave的數(shù)據(jù)是否改變，這是一種錯誤的配置。所以，盡量使用read_only，它防止改變數(shù)據(jù)(除了特殊的線程)。但是，read_only并是很實用，特別是那些需要在slave上創(chuàng)建表的應用。

配置玩，重啟slave數(shù)據(jù)庫

mysql> show slave status;

Empty set (0.05 sec)

mysql>

沒有記錄，需要設置一些主從配置。

4.5 設置主從連接復制

生成CHANGE MASTER語句，然后在從上執(zhí)行，master信息，從備份集合里面獲?。?

xtrabackup備份的話，從xtrabackup_binlog_info里面獲取，如下所示：

[root@data02 tmp]#tar -xvfbackup_slave.tar.gz

[root@data02 tmp]#cd backup_slave

 [root@data02 backup_slave]# morextrabackup_binlog_info

mysql-bin.000141    120

[root@data02 backup_slave]#

Mysqldump的啊，從sql文件的頭幾列獲取，如下所示：

[root@data02 tmp]#cd /tmp/

[root@data02 tmp]#gunzip alldb.sql.gz

[root@data02 tmp]# more alldb.sql |grep"CHANGE MASTER TO MASTER_LOG_FILE" |grep "MASTER_LOG_POS"|more

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000141',MASTER_LOG_POS=120;

生成changemaster語句，如下：

 CHANGE MASTER TO MASTER_HOST='192.168.52.129',

         MASTER_USER='repl',

          MASTER_PASSWORD='repl_1234',

         MASTER_LOG_FILE='mysql-bin.000141',

         MASTER_LOG_POS=120;

有報錯信息：

mysql>  CHANGE MASTER TO MASTER_HOST='192.168.52.129',

   ->   MASTER_USER='repl',

   -> MASTER_PASSWORD='repl_1234',

   -> MASTER_LOG_FILE='mysql-bin.000141',

   -> MASTER_LOG_POS=120;

ERROR 1794 (HY000): Slave is not configuredor failed to initialize properly. You must at least set --server-id to enableeither a master or a slave. Additional error messages can be found in the MySQLerror log.

mysql>

具體原因目前不詳，網(wǎng)上查找到的資料:數(shù)據(jù)庫打開這幾張表的默認引擎為MyISAM，但是這幾張表在建表時的引擎為INNODB

但是能確定的，這幾張表確實是在mysql5.6中新入的

innodb_index_stats,

innodb_tables_stats,

slave_master_info,

slave_relay_log_info,

slave_worker_info

解決方法：

登錄數(shù)據(jù)庫，進入mysql庫，執(zhí)行如下SQL刪除5張表

記住，一定要是drop table if exists

drop table if exists innodb_index_stats;

drop table if exists innodb_table_stats;

drop table if exists slave_master_info;

drop table if exists slave_relay_log_info;

drop table if exists slave_worker_info;

執(zhí)行完后，可以用show tables查看一下，看表的數(shù)據(jù)是否已經(jīng)比刪除之前減少了，如果減少了，說明你成功了！

[root@data02 test] cd/home/data/mysql/data/mysql

[root@data02 mysql]# ll *.ibd

-rw-rw----. 1 mysql mysql 98304  1?  9 02:08 innodb_index_stats.ibd

-rw-rw----. 1 mysql mysql 98304  1?  9 02:08 innodb_table_stats.ibd

-rw-rw----. 1 mysql mysql 98304  1?  9 02:08 slave_master_info.ibd

-rw-rw----. 1 mysql mysql 98304  1?  9 02:08 slave_relay_log_info.ibd

-rw-rw----. 1 mysql mysql 98304  1?  9 02:08 slave_worker_info.ibd

[root@data02 mysql]#

強行刪除ibd文件：

[root@data02 mysql]# rm -f *.ibd

重啟數(shù)據(jù)庫，登錄mysql

source/usr/test/mysql/share/mysql_system_tables.sql

show tables;

發(fā)現(xiàn)表已經(jīng)回來了，表數(shù)據(jù)大概總數(shù)量為28個。

之后執(zhí)行change master to，OK，搞定，如下所示：

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.03sec)

mysql> CHANGE MASTER TOMASTER_HOST='192.168.52.129',MASTER_USER='repl',MASTER_PASSWORD='repl_1234',MASTER_LOG_FILE='mysql-bin.000141',MASTER_LOG_POS=120;

Query OK, 0 rows affected, 2 warnings (0.07sec)

mysql>

啟動slave

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

mysql>

4.6 驗證主從復制狀態(tài)

在slave服務器上查看slave狀態(tài)：

mysql> show slave status\G

*************************** 1. row***************************

               Slave_IO_State: Waiting formaster to send event

                  Master_Host: 192.168.52.129

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000151

         Read_Master_Log_Pos: 346

               Relay_Log_File:mysql-relay-bin.000018

                Relay_Log_Pos: 509

       Relay_Master_Log_File: mysql-bin.000151

            Slave_IO_Running: Yes

           Slave_SQL_Running: Yes

              Replicate_Do_DB:business_db,user_db,plocc_system

         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: 346

              Relay_Log_Space: 845

              Until_Condition: None

這里主要看：

            Slave_IO_Running: Yes

           Slave_SQL_Running: Yes

       Seconds_Behind_Master: 0

IO和SQL線程都是Yes以及Seconds_Behind_Master是0就表示從庫正常運行了。

在master服務器上查看：

mysql> show full processlist;

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

| Id | User            | Host                 | db   | Command    | Time | State                                                                 |Info                  |

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

|  1| event_scheduler | localhost           | NULL | Daemon      | 5874 |Waiting on empty queue                                               | NULL                  |

| 21 | root            | localhost            | NULL | Query       |   0 | init                                                                 | show full processlist |

| 24 | repl            | 192.168.52.130:45665 | NULL |Binlog Dump |   88 | Master has sent allbinlog to slave; waiting for binlog to be updated | NULL                  |

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

3 rows in set (0.03 sec)

mysql>

看到有192.168.52.130:45665的線程在同步二進制數(shù)據(jù)

4.7 master添加數(shù)據(jù)驗證

去master（192.168.52.129）上操作，添加表記錄：

mysql> create table master_test select 1as a,'a' as b;

Query OK, 1 row affected (0.72 sec)

Records: 1 Duplicates: 0  Warnings: 0

mysql>

去slave（192.168.52.130）上查詢下，看表數(shù)據(jù)是否同步過來，看到數(shù)據(jù)已經(jīng)同步過來了，如下所示：

mysql> select * fromuser_db.master_test;

+---+---+

| a | b |

+---+---+

| 1 | a |

+---+---+

1 row in set (0.06 sec)

mysql>

5，如何添加新的slave服務器總結

如果master運行很久了，需要添加新的slave服務器，那么搭建新的slave，此時，有幾種方法可以使slave從另一個服務開始，例如，從master拷貝數(shù)據(jù)，從另一個slave克隆，從最近的備份開始一個slave。Slave與master同步時，需要三樣東西：

(1)master的某個時刻的數(shù)據(jù)快照；

(2)master當前的日志文件、以及生成快照時的字節(jié)偏移。這兩個值可以叫做日志文件坐標(log file coordinate)，因為它們確定了一個二進制日志的位置，你可以用SHOW MASTER STATUS命令找到日志文件的坐標；

(3)master的二進制日志文件。

也可以通過以下幾中方法來克隆一個slave：

(1)   冷拷貝(cold copy)

停止master，將master的文件拷貝到slave；然后重啟master。缺點很明顯。

(2)   熱拷貝(warm copy)

如果你僅使用MyISAM表，你可以使用mysqlhotcopy拷貝，即使服務器正在運行。

如果有myisam和innodb表，可以在業(yè)務低峰期用tar包的方式來熱拷貝。

(3)   使用mysqldump

使用mysqldump來得到一個數(shù)據(jù)快照可分為以下幾步：

<3.a>鎖表：如果你還沒有鎖表，你應該對表加鎖，防止其它連接修改數(shù)據(jù)庫，否則，你得到的數(shù)據(jù)可以是不一致的。如下：

mysql> FLUSH TABLES WITH READ LOCK;

<3.b>在另一個連接用mysqldump創(chuàng)建一個你想進行復制的數(shù)據(jù)庫的轉儲：

 見4.2.1小節(jié)

<3.c>對表釋放鎖。

mysql> UNLOCK TABLES;

(4) 使用xtrabackup

使用xtrabackup得到數(shù)據(jù)快照，見4.2.2小節(jié)

6，如何實現(xiàn)MSS

當設置log_slave_updates時，你可以讓slave扮演其它slave的master。此時，slave把SQL線程執(zhí)行的事件寫進行自己的二進制日志(binary log)，然后，它的slave可以獲取這些事件并執(zhí)行它。如下圖6.png所示：

7，復制過濾

復制過濾可以讓你只復制服務器中的一部分數(shù)據(jù)，有兩種復制過濾：在master上過濾二進制日志中的事件；在slave上過濾中繼日志中的事件。如下圖7.png所示：

8，常用的mysql主從拓撲結構

復制的體系結構有以下一些基本原則：

(1)   每個slave只能有一個master；

(2)   每個slave只能有一個唯一的服務器ID；

(3)   每個master可以有很多slave；

(4)   如果你設置log_slave_updates，slave可以是其它slave的master，從而擴散master的更新。

MySQL不支持多主服務器復制(MultimasterReplication)——即一個slave可以有多個master。但是，通過一些簡單的組合，我們卻可以建立靈活而強大的復制體系結構。

9，mysql主從同步之間的管理

介紹一下基本的mysql主從的管理操作命令：

9.1 停止mysql從服務

STOP SLAVE IO_THREAD;    #停止IO進程

STOP SLAVE SQL_THREAD;    #停止SQL進程

STOP SLAVE;                               #停止IO和SQL進程

9.2 開啟mysql主從同步服務

START SLAVE IO_THREAD;    #啟動IO進程

START SLAVE SQL_THREAD;  #啟動SQL進程

START SLAVE;            #啟動IO進程和SQL進程

9.3 重置mysql主從同步

RESET SLAVE;

#用于讓從屬服務器忘記其在主服務器的二進制日志中的復制位置, 它會刪除master.info和relay-log.info文件，以及所有的中繼日志，并啟動一個新的中繼日志,當你不需要主從的時候可以在從上執(zhí)行這個操作。不然以后還會同步，可能會覆蓋掉你的數(shù)據(jù)庫。

9.4 查看主從同步狀態(tài)

SHOW SLAVE STATUS;

#這個命令主要查看Slave_IO_Running、Slave_SQL_Running、Seconds_Behind_Master、Last_IO_Error、Last_SQL_Error這些值來把握復制的狀態(tài)。

9.5 臨時跳過MYSQL同步錯誤

#經(jīng)常會朋友mysql主從同步遇到錯誤的時候，比如一個主鍵沖突等，那么我就需要在確保那一行數(shù)據(jù)一致的情況下臨時的跳過這個錯誤，那就需要使用SQL_SLAVE_SKIP_COUNTER = n命令了，n是表示跳過后面的n個事件,比如我跳過一個事件的操作如下：

STOP SLAVE;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

START SLAVE;

9.6 從指定位置重新同步數(shù)據(jù)

#有的時候主從同步有問題了以后，需要從log位置的下一個位置進行同步，相當于跳過那個錯誤，這時候也可以使用CHANGE MASTER命令來處理，只要找到對應的LOG位置就可以,比如：

CHANGE MASTER TOMASTER_HOST='10.1.1.75',MASTER_USER='replication',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=106;

START SLAVE;

PS：生產(chǎn)環(huán)境中這種操作盡量能避免就避免。

10，線上維護mysql主從需要注意的事項

1.   不要亂使用SQL_SLAVE_SKIP_COUNTER命令。

這個命令跳過之后很可能會導致你的主從數(shù)據(jù)不一致，一定要先將指定的錯誤記錄下來，然后再去檢查數(shù)據(jù)是否一致，尤其是核心的業(yè)務數(shù)據(jù)。

2.   結合percona-toolkit工具pt-table-checksum定期查看數(shù)據(jù)是否一致。

這個是DBA必須要定期做的事情，呵呵，有合適的工具何樂而不為呢？另外percona-toolkit還提供了對數(shù)據(jù)庫不一致的解決方案，可以采用pt-table-sync，這個工具不會更改主的數(shù)據(jù)。還可以使用pt-heartbeat來查看從服務器的復制落后情況。

3.   使用replicate-wild-ignore-table選項而不要使用replicate-do-db或者replicate-ignore-db。

原因已經(jīng)在上面做了說明。

4.   將主服務器的日志模式調(diào)整成mixed。

5.   每個表都加上主鍵，主鍵對數(shù)據(jù)庫的同步會有影響尤其是居于ROW復制模式。

6.   避免在master做批量更新操作，以免造成從庫主從延時。

  ----------------------------------------------------------------------------------------------------------------
<版權所有，文章允許轉載，但必須以鏈接方式注明源地址，否則追究法律責任!>
原博客地址：       http://blog.itpub.net/26230597/viewspace-1478126/
原作者：黃杉 (mchdba)
----------------------------------------------------------------------------------------------------------------

參考文章地址：

http://www.open-open.com/lib/view/open1373874692544.html

http://blog.chinaunix.net/uid-20639775-id-3254611.html