MySQL的主從復制、半同步復制和主主復制的概念

本篇內容主要講解“的主從復制、半同步復制和主主復制的概念”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強。下面就讓小編來(lái)帶大家學(xué)習“MySQL的主從復制、半同步復制和主主復制的概念”吧!

MySQL支持的兩種復制方案：基于語(yǔ)句復制、基于行復制
   基于語(yǔ)句復制基于行復制，這兩種復制方式都是通過(guò)記錄主的二進(jìn)制日志中任何有可能導致數據庫內數據發(fā)生改變的SQL語(yǔ)句到中繼日志，并且在從服務(wù)器上 執行以下中繼日志內的SQL語(yǔ)句，而達到與主服務(wù)器的數據同步。不同的是，當主服務(wù)器上執行了一個(gè)基于變量的數據并將其更新到數據庫中，如now()函 數，而此時(shí)基于語(yǔ)句復制時(shí)記錄的就是該SQL語(yǔ)句的整個(gè)語(yǔ)法，而基于行復制就是將now()更新到數據庫的數值記錄下來(lái)。

例如：在主服務(wù)器上執行以下語(yǔ)句：
mysql> update user set createtime=now() where sid=16;
假如此時(shí)now()返回的值是：2012-04-16 20:46:35
基于語(yǔ)句的復制就會(huì )將其記錄為：update user set createtime=now() where sid=16;
基于行復制的就會(huì )將其記錄為：update user set createtime='2012-04-16 20:46:35' where sid=16;

進(jìn)行主從復制啟動(dòng)的三個(gè)線(xiàn)程
Binlog dump線(xiàn)程：將二進(jìn)制日志的內容發(fā)送給從服務(wù)器
I/O從線(xiàn)程：將接受的的數據寫(xiě)入到中繼日志
SQL線(xiàn)程：一次從中繼日志中讀出一句SQL語(yǔ)句在從服務(wù)器上執行

一、主從復制：
準備工作：
1.修改配置文件(server_id一定要修改)
2.建立復制用戶(hù)
3.啟動(dòng)從服務(wù)器的從服務(wù)進(jìn)程
規劃：
Master：IP地址：172.16.4.11   版本：mysql-5.5.20
Slave：IP地址：172.16.4.12    版本：mysql-5.5.20
這里需注意，mysql復制大部分都是后向兼容，所以，從服務(wù)器的版本一定要高于或等于主服務(wù)器的版本。
1、Master
修改配置文件，將其設為mysql主服務(wù)器
# vim /etc/my.cnf
server_id=11                #修改server_id=11
log_bin=mysql-bin            #開(kāi)啟二進(jìn)制日志
sync_binlog=1               #任何一個(gè)事務(wù)提交之后就立即寫(xiě)入到磁盤(pán)中的二進(jìn)制文件
innodb_flush_logs_at_trx_commit=1       #任何一個(gè)事物提交之后就立即寫(xiě)入到磁盤(pán)中的日志文件

保存退出
# service mysql reload             #重新載入mysql的配置文件

2、Master上創(chuàng )建用戶(hù)，授予復制權限
mysql> grant replication client,replication slave on *.* to repl@172.16.4.12 identified by '135246';
mysql> flush privileges;

3、Slave
修改配置文件，將其設置為一個(gè)mysql從服務(wù)器
# vim /etc/my.cnf
server_id=12                #修改server_id=12
#log-bin                #注釋掉log-bin，從服務(wù)器不需要二進(jìn)制日志，因此將其關(guān)閉
relay-log=mysql-relay                #定義中繼日志名，開(kāi)啟從服務(wù)器中繼日志
relay-log-index=mysql-relay.index     #定義中繼日志索引名，開(kāi)啟從服務(wù)器中繼索引
read_only=1                    #設定從服務(wù)器只能進(jìn)行讀操作，不能進(jìn)行寫(xiě)操作

保存退出
# service mysql reload             #重新載入mysql的配置文件

4、驗證Slave上中繼日志以及server_id是否均生效
mysql> show variables like 'relay%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| relay_log             | relay-bin       |
| relay_log_index       | relay-bin.index |
| relay_log_info_file   | relay-log.info  |
| relay_log_purge       | ON              |
| relay_log_recovery    | OFF             |
| relay_log_space_limit | 0               |
+-----------------------+-----------------+

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 12    |
+---------------+-------+

5、啟動(dòng)從服務(wù)器的從服務(wù)進(jìn)程
場(chǎng)景一、如果主服務(wù)器和從服務(wù)器都是新建立的，并沒(méi)有新增其他數據，則執行以下命令：
mysql> change master to master_host='172.16.4.11',master_user='repl',master_password='135246';

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 172.16.4.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 107
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 25520
              Relay_Log_Space: 2565465
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0

mysql> start slave;

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: 172.16.4.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 107
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             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: 360
              Relay_Log_Space: 300
              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:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 11

場(chǎng)景二、如果主服務(wù)器已經(jīng)運行過(guò)一段了，從服務(wù)器是新添加的，則需要將主服務(wù)器之前的數據導入到從服務(wù)器中：
Master：
# mysqldump -uroot -hlocalhost -p123456 --all-databases --lock-all-tables --flush-logs --master-data=2 > /backup/alldatabase.sql
mysql> flush tables with read lock;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      360 |              |                  |
+------------------+----------+--------------+------------------+
mysql> unlock tables;
# scp /backup/alldatabase.sql 172.16.4.12:/tmp

Slave：
# mysql -uroot -p123456 < /tmp/alldatabase.sql
mysql> change master to master_host='172.16.4.11',master_user='repl',master_password='135246',master_log_file='mysql-bin.000004',master_log_pos=360;

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 172.16.4.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 360
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 360
              Relay_Log_Space: 107
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0

mysql> start slave;

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: 172.16.4.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 360
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.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: 360
              Relay_Log_Space: 300
              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:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 11

說(shuō)明MySQL的主從復制架構成功
注1：MySQL的復制可以基于某個(gè)數據庫或庫中的默寫(xiě)表進(jìn)行復制，要想實(shí)現該功能，只需在其配置文件中添加以下配置：
Master：
binlog-do-db=db_name        只復制db_name數據庫
binlog-ignore-db=db_name    不復制db_name數據庫

注2：在Master上定義過(guò)濾規則，意味著(zhù)，任何不涉及到該數據庫相關(guān)的寫(xiě)操作都不會(huì )被記錄到二進(jìn)制日志中，因此不建議在Master上定義過(guò)濾規則，并且不建議binlog-do-db與binlog-ignore-db同時(shí)定義。
Slave：
replicate_do_db=db_name              只復制db_name數據庫
replicate_ignore_db=db_name          不復制db_name數據庫
replicate_do_table=tb_name           只復制tb_name表
replicate_ignore_table=tb_name       只復制tb_name表
replicate_wild_do_table=test%        只復制以test為開(kāi)頭并且后面跟上任意字符的名字的表
replicate_wild_ignore_table=test_    只復制以test為開(kāi)頭并且后面跟上任意單個(gè)字符的名字的表

注3：如果需要指定多個(gè)db或table時(shí)，則只需將命令多次寫(xiě)入

二、半同步復制
   由于Mysql的復制都是基于異步進(jìn)行的，在特殊情況下不能保證數據的成功復制，因此在mysql 5.5之后使用了來(lái)自google補丁，可以將Mysql的復制實(shí)現半同步模式。所以需要為主服務(wù)器加載對應的插件。在Mysql的安裝目錄下的 lib/plugin/目錄中具有對應的插件semisync_master.so，semisync_slave.so

在Master和Slave的mysql命令行運行如下命令：
Master：
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; 
mysql> set global rpl_semi_sync_master_enabled = 1; 
mysql> set global rpl_semi_sync_master_timeout = 1000; 
mysql> show variables like '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 1000  |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+

Slave：
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; 
mysql> set global rpl_semi_sync_slave_enabled = 1; 
mysql> stop slave;
mysql> start slave;
mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+

檢查半同步是否生效：
Master：
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
說(shuō)明半同步成功。

讓半同步功能在MySQL每次啟動(dòng)都自動(dòng)生效，在Master和Slave的my.cnf中編輯：
Master：
[mysqld] 
rpl_semi_sync_master_enabled=1 
rpl_semi_sync_master_timeout=1000     #1秒

Slave：
[mysqld] 
rpl_semi_sync_slave_enabled=1 

也可通過(guò)設置全局變量的方式來(lái)設置是否啟動(dòng)半同步插件：
Master：
mysql> set global rpl_semi_sync_master_enabled=1
取消加載插件
mysql> uninstall plugin rpl_semi_sync_master;

Slave：
mysql> set global rpl_semi_sync_slave_enabled = 1;
mysql> uninstall plugin rpl_semi_sync_slave;

三、主主復制架構
1、在兩臺服務(wù)器上各自建立一個(gè)具有復制權限的用戶(hù)；
Master：
mysql> grant replication client,replication slave on *.* to repl@172.16.4.12 identified by '135246';
mysql> flush privileges;

Slave：
mysql> grant replication client,replication slave on *.* to repl@172.16.4.11 identified by '135246';
mysql> flush privileges;

2、修改配置文件：
Master：
[mysqld]
server-id = 11
log-bin = mysql-bin
auto-increment-increment = 2
auto-increment-offset = 1
relay-log=mysql-relay           
relay-log-index=mysql-relay.index

Slave：
[mysqld]
server-id = 12
log-bin = mysql-bin
auto-increment-increment = 2
auto-increment-offset = 2
relay-log=mysql-relay           
relay-log-index=mysql-relay.index

3、如果此時(shí)兩臺服務(wù)器均為新建立，且無(wú)其它寫(xiě)入操作，各服務(wù)器只需記錄當前自己二進(jìn)制日志文件及事件位置，以之作為另外的服務(wù)器復制起始位置即可
Master：
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      360 |              |                  |
+------------------+----------+--------------+------------------+

Slave：
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      107 |              |                  |
+------------------+----------+--------------+------------------+

4、各服務(wù)器接下來(lái)指定對另一臺服務(wù)器為自己的主服務(wù)器即可：
Master：
mysql> change master to master_host='172.16.4.12',master_user='repl',master_password='135246',master_log_file='mysql-bin.000005',
master_log_pos=107;

Slave：
mysql> change master to master_host='172.16.4.11',master_user='repl',master_password='135246',
master_log_file='mysql-bin.000004',master_log_pos=360;

5、啟動(dòng)從服務(wù)器線(xiàn)程：
Master：
mysql> start slave;

Slave：
mysql> start slave;
到此主主架構已經(jīng)成功！