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

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

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

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

進行主從復制啟動的三個線程
Binlog dump線程：將二進制日志的內容發送給從服務器
I/O從線程：將接受的的數據寫入到中繼日志
SQL線程：一次從中繼日志中讀出一句SQL語句在從服務器上執行

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

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

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

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

保存退出
# 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、啟動從服務器的從服務進程
場景一、如果主服務器和從服務器都是新建立的，并沒有新增其他數據，則執行以下命令：
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

場景二、如果主服務器已經運行過一段了，從服務器是新添加的，則需要將主服務器之前的數據導入到從服務器中：
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

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

注2：在Master上定義過濾規則，意味著，任何不涉及到該數據庫相關的寫操作都不會被記錄到二進制日志中，因此不建議在Master上定義過濾規則，并且不建議binlog-do-db與binlog-ignore-db同時定義。
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為開頭并且后面跟上任意字符的名字的表
replicate_wild_ignore_table=test_    只復制以test為開頭并且后面跟上任意單個字符的名字的表

注3：如果需要指定多個db或table時，則只需將命令多次寫入

二、半同步復制
   由于Mysql的復制都是基于異步進行的，在特殊情況下不能保證數據的成功復制，因此在mysql 5.5之后使用了來自google補丁，可以將Mysql的復制實現半同步模式。所以需要為主服務器加載對應的插件。在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     |
+--------------------------------------------+-------+
說明半同步成功。

讓半同步功能在MySQL每次啟動都自動生效，在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 

也可通過設置全局變量的方式來設置是否啟動半同步插件：
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、在兩臺服務器上各自建立一個具有復制權限的用戶；
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、如果此時兩臺服務器均為新建立，且無其它寫入操作，各服務器只需記錄當前自己二進制日志文件及事件位置，以之作為另外的服務器復制起始位置即可
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、各服務器接下來指定對另一臺服務器為自己的主服務器即可：
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、啟動從服務器線程：
Master：
mysql> start slave;

Slave：
mysql> start slave;
到此主主架構已經成功！

到此，相信大家對“MySQL的主從復制、半同步復制和主主復制的概念”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！