MySQL復制詳解

目錄：

       1、簡介

       2、原理

       3、常見復制架構

       4、一主一叢異步復制演示

       5、測試結果

       6、額外的配置參數

       7、提升備庫成為主庫

              7.1計劃內的提升

              7.2計劃外的提升

       8、半同步復制配置演示

       9、雙主雙寫配置演示

       10、處理可以忽略的錯誤

       11、總結

1、簡介：MySQL內建的復制功能是構建基于MySQL的大規模，高性能應用的基礎。復制就是讓一臺服務器的數據和其它服務器保持同步，一臺主庫可以同步到多臺備庫上面，備庫也可以作為另一臺服務器的主庫。主庫和備庫之間可以有多種不同的組合方式。

2、原理： 在主庫上記錄二進制日志，在備庫重放日志的方式實現異步數據復制。

總的來說，復制有三個步驟：

1)、主庫記錄二進制日志，每次準備提交事物完成數據庫更新前，先記錄二進制日志，記錄二進制日志后，主庫會告訴存儲引擎可以提交事物了

2)、備庫將主庫的二進制日志復制到本地的中繼日志中，首先，備庫會先啟動一個工作進程，稱為IO工作線程，負責和主庫建立一個普通的客戶端連接。如果該進程追趕上了主庫，它將進入睡眠狀態，直到主庫有新的事件產生通知它，他才會被喚醒，將接收到的事件記錄到中繼日志中。

3)、備庫的SQL線程執行最后一步，該線程重中繼日志中讀取事件并且在備庫執行，當SQL線程趕上IO線程的時候，中繼日志通常記錄在系統緩存中，所以中繼日志的開銷很低。SQL線程也可以根據配置選項來決定是否寫入其自己的二進制日志中。

3、常見復制架構

3.1 一主多從

3.2主主

3.3環形復制 等等，下面這張圖片包含了常見復制架構

4、一主一從實驗演示，本實驗都使用多實例演示。

1)、安裝MySQL

mkdir -p /data/{3306,3307}/data
/usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/3306/data/ --basedir=/usr/local/mysql/
/usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/3307/data/ --basedir=/usr/local/mysql/

2)、為主服務器提供配置文件

cp /usr/local/mysql/support-files/my-small.cnf /data/3306/my.cnf 
vim /data/3306/my.cnf  
[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock1
# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock1
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
 
datadir = /data/3306/data    #指定數據存放位置
server-id       = 3306     #指定一個唯一的server id，可以使用ip后8位，本機采用端口
log-bin=/data/3306/data/mysql-bin  #二進制日志的位置
binlog_format=mixed            #二進制日志的格式
sync_binlog=1              #MySQL在每次提交事物前會把二進制日志同步到磁盤上面。

3)、為備服務器提供配置文件

cp /usr/local/mysql/support-files/my-small.cnf /data/3307/my.cnf
vim /data/3307/my.cnf
[client]
#password       = your_password
port            = 3307
socket          = /tmp/mysql.sock2
# The MySQL server
[mysqld]
port            = 3307
socket          = /tmp/mysql.sock2
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
 
datadir = /data/3307/data  #指定數據存放位置
server-id       = 3307   #為備服務器提供唯一的server id
relay-log = /data/3307/data/relay-log    #指定中繼日志的位置和名稱
log_bin = /data/3307/data/mysql-bin    #指定二進制日志的位置和名稱
log_slave_updates = 1    #允許備庫將其重放的事件也記錄到自身的二進制日志中。
read_only = 1   #該選項會阻止任何沒有特權權限的線程修改數據。
skip_slave_start #該選項能夠阻止備庫在崩潰后自動啟動復制，崩潰后啟動復制，數據可能不一致
#sysnc_master_info = 1 
#sync_relay_log = 1
#sync_relay_log_info = 1

4)、主庫創建復制賬號

mysql -S /tmp/mysql.sock1   #登錄主服務器

mysql> grant replication slave on *.* to 'rep'@'192.168.198.%' identified by '123456';  #創建并授權復制賬號
Query OK, 0 rows affected (0.05 sec)

mysql>flush privileges;  #刷新權限
Query OK, 0 rows affected (0.05 sec)

5)、為備庫提供數據，備庫和主庫數據不一致會出現錯誤，生產場景下，大多數也是主庫已經運行了一段時間了。這里給主庫做個全備，然后恢復到備庫上面。

mysqldump -S /tmp/mysql.sock1 -A -B -x --events --master-data=1 > /opt/data.sql    #做一次完全備份
mysql -S /tmp/mysql.sock2 < /opt/data.sql     #備庫恢復數據

6)、備庫連接主庫

mysql -S /tmp/mysql.sock2  #登錄備庫
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.198.139',
    -> MASTER_PORT=3306,
    -> MASTER_USER='rep',
    -> MASTER_PASSWORD='123456';    
#注意：因為備份的時候指定了--master-data=1,里面已經告訴了備庫，主庫復制的二進制的位置，假如沒有指定--master-data=1 ;
mysql> show master status;   #在主庫上，查看當前二進制日志的位置
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |      402 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
 
#備庫上面，執行change master 的時候需要如下指定指定
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.198.139',
    -> MASTER_PORT=3306,
    -> MASTER_USER='rep',
    -> MASTER_PASSWORD='123456',
    ->MASTER_ LOG_FILE='mysql-bin.000006',   #指定二進制日志的名稱
    ->MASTER_LOG_POS=402;   # 指定二進制日志位置

7)、啟動復制

mysql> start slave;  
Query OK, 0 rows affected (0.00 sec)
 
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.198.139
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 402
               Relay_Log_File: relay-log.000003
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes        #IO線程狀態
            Slave_SQL_Running: Yes             #SQL線程狀態
              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: 402
              Relay_Log_Space: 549
              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: 3306
1 row in set (0.00 sec)

8)、查看主庫和備庫進程狀態

mysql> show processlist\G    #這是主庫
*************************** 1. row ***************************
     Id: 9
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 10
   User: rep
   Host: 192.168.198.139:37230
     db: NULL
Command: Binlog Dump
   Time: 348
  State: Master has sent all binlog to slave; waiting for binlog to be updated   #線程狀態
   Info: NULL
2 rows in set (0.00 sec)
### 
mysql> show processlist\G    #從庫線程狀態
*************************** 1. row ***************************
     Id: 3
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 12
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 395
  State: Waiting for master to send event     #從庫IO線程狀態
   Info: NULL
*************************** 3. row ***************************
     Id: 13
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 395
  State: Slave has read all relay log; waiting for the slave I/O thread to update it   #從庫SQL線程狀態
   Info: NULL
3 rows in set (0.00 sec)

關于復制線程狀態，參考：http://blog.itpub.net/7970627/viewspace-717664/

9)、復制文件信息

mysql-bin.index  #二進制文件索引，不可以被刪除，立面每一行包含一個二進制文件的文件名，如果被刪除，MySQL將識別不了二進制文件

cat /data/3306/data/mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
/data/3306/data/mysql-bin.000003
/data/3306/data/mysql-bin.000004
/data/3306/data/mysql-bin.000005
/data/3306/data/mysql-bin.000006

mysql-relay-bin-index  #中繼日志的索引文件，和mysql-bin.index 作用類似

cat /data/3307/data/relay-log.index
/data/3307/data/relay-log.000002
/data/3307/data/relay-log.000003

master.info #保存備庫連接到主庫的信息，格式為純文本，該文件不能被刪除，否則備庫無法連接到主庫  

master.info內容  #請自行查看

relay-log.info #包含當前備庫復制的二進制日志和中繼日志坐標，不可刪除，否則備庫不知道從哪個位置開始復制

行 描述  

cat /data/3307/data/relay-log.info
/data/3307/data/relay-log.000003    # Relay_Log_File
253                                 # Relay_Log_Pos
mysql-bin.000006                    # Master_Log_File
402                                 # Exec_Master_Log_Pos 
967                             #####

5、測試結果

主庫：

mysql> use test;
Database changed
mysql> create table student (                     #在主庫上創建表，并且插入數據
    -> id int(2) AUTO_INCREMENT PRIMARY KEY,
    -> names char(20));
Query OK, 0 rows affected (0.19 sec)
 
mysql> insert into student(names) values('yun zhongehe');
Query OK, 1 row affected (0.03 sec)

從庫：

mysql> select * from test.student;    #在從庫上查看主庫的數據是否同步過來
+----+--------------+
| id | names        |
+----+--------------+
|  1 | yun zhongehe |
+----+--------------+
1 row in set (0.00 sec)

可以看出，從庫會自動復制主庫的數據。

6、額外的配置參數

6.1、推薦的復制選項

skip_slave_errors = xxx,xxx  #從服務器再復制過程中忽略的錯誤類型，xxx為mysql錯誤代碼。
skip_slave_start  #從服務器崩潰之后，重新啟動，不會自動復制
log_slave_updates=1  #可以把S變成M，讓從服務器把自身復制的事件和記錄都寫到自己的二進制日志里 read_only=1   #從機會阻止沒有特殊權限的線程更改數據，即就是從機只能讀，不能寫. sync_binlog = 1 #主機每次提交事務的時候把二進制日志的內容同步到磁盤上，所以即使服務器崩潰，也會把事件寫入日志中。

6.2復制過濾選項

binlog_do_db   #只復制指定的數據庫(主服務器配置文件里設置) binlog_ignore_db   #不復制指定的數據庫(主服務器配置文件里設置)
replicate_do_db  #從服務器復制指定的數據庫
replicate_ignore_db #從服務器不復制指定的數據庫   
replicate_ignore_table  #不復制指定的表(從服務器配置文件里設置) replicate_wild_ignore_table  #使用wild匹配來不復制的指定表(從服務器配置文件里設置),比如參數設為abc.%,表示不復制abc的所有表。

7、提升備庫成為主庫

有些情況需要將備庫提升為主庫，一種情況是計劃內的，另一種是計劃外的，如主庫宕機。

7.1計劃內的提升

1)、停止當前主庫上所有的寫操作，如果可以，最好將所有的客戶端程序關閉，如果正在使用虛擬IP地址，也可以簡單的關閉虛擬IP，然后斷開所有的客戶端連接以關閉其打開的事物。

2)、通過Flush tables with read lock; 在主庫停止所有活躍的寫入，這一步是可選的。這時刻應該禁止向即將被替換的主庫寫入，因為寫入的任何數據意味著丟失。為了更好的保證這一點，可以kill所有打開的事物，這會真正的結束所有寫入。

3)、選擇一個備庫作為主庫，并確保它執行完所有從主庫獲得的中繼日志。

4)、確保新主庫和舊主庫的數據是一致的。

5)、新主庫執行stop slave;

6)、在新主庫執行CHANGE MASTER TO MASTER_HOST='';，然后執行RESET SLAVE，使其斷開與老主庫的連接，并丟棄master.info 里面的信息。如果連接信息保存在my.cnf里面，會無法正確工作

7)、執行SHOW MASTER STATUS；記錄新主庫的二進制日志坐標

8)、確保其它備庫已經趕上。

9)、關閉舊主庫

10)、在MySQL 5.1 以及以上的版本，如果需要，激活新主庫的事件

11)、將客戶端連接到新主庫

12)、在每臺備庫上面，執行CHANGE MASTER TO語句使用之前通過SHOW MASTER STATUS獲得的二進制坐標日志，來指向新的主庫。

7.2計劃外的提升

1)、確定那臺備庫的數據最新，檢查每臺備庫上SHOW SLAVE STATUS；命令的輸出，選擇值最新的哪一個

2)、讓所有的被執行完所有其從崩潰前的舊主庫獲得的中繼日志，如果再未完成前修改備庫的主庫，它會拋棄剩下的日志事件，從而無法獲得該備庫在什么地方停止。

3)、執行計劃內提升的5~7步。

4)、比較每臺備庫和新主庫的MASTER_LOG_FILE/Read_Master_Log_Pos的值。

5)、執行計劃內提升的的10~12步。

8、mysql的半同步復制。

8.1半同步復制在提交過程中增加了一個延遲：當提交事物時，在客戶端接收到查詢結束反饋前必須保證二進制日志已經傳輸到至少一臺備庫上。主庫將事物提交到磁盤之后會增加一些延遲。同樣的也增加了客戶端的延遲。

主庫和從庫必須同時配置半同步插件才可以生效，否則還是異步的方式。

8.2臨時生效的配置：

ls /usr/local/mysql/lib/plugin/semisync_*    #查看相關的半同步插件
/usr/local/mysql/lib/plugin/semisync_master.so  /usr/local/mysql/lib/plugin/semisync_slave.so

主庫：

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';   #安裝插件
Query OK, 0 rows affected (0.10 sec)
 
mysql> show global variables like 'rpl%';   #查看插件狀態變量
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_recovery_rank                  | 0     |
| rpl_semi_sync_master_enabled       | OFF   |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
5 rows in set (0.00 sec)
 
mysql> set global rpl_semi_sync_master_enabled = 1;  #啟用插件
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global rpl_semi_sync_master_timeout = 1000;   #設置超時時長
Query OK, 0 rows affected (0.00 sec)
 
mysql> show global variables like 'rpl%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_recovery_rank                  | 0     |
| 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    |
+------------------------------------+-------+
5 rows in set (0.00 sec)

從庫：

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; #安裝從庫半同步插件
Query OK, 0 rows affected (0.08 sec)
 
mysql> show global variables like 'rpl%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_recovery_rank               | 0     |
| rpl_semi_sync_slave_enabled     | OFF   |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
3 rows in set (0.00 sec)
 
mysql> set global rpl_semi_sync_slave_enabled = 1;  
Query OK, 0 rows affected (0.00 sec)
 
mysql> show global variables like 'rpl%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_recovery_rank               | 0     |
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
3 rows in set (0.00 sec)
 
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.02 sec)
 
mysql> start slave io_thread;      #重啟進程讓其生效
Query OK, 0 rows affected (0.00 sec)

8.2半同步永久有效方式

主庫配置文件：

[mysqld]  
rpl_semi_sync_master_enabled=1  
rpl_semi_sync_master_timeout=1000   #此單位是毫秒

從庫

[mysqld]  
rpl_semi_sync_slave_enabled=1

然后重新啟動服務即可。

9、配置雙寫主–主模型。

這種配置產生的問題比較多，一般不建議這樣配置。只需要設置auto_increment_incremnt和auto_increment_offset ，即可完成配置，比較簡單。

9.1配置

主庫:

[myqld]
#rpl_semi_sync_master_enabled=1     #注釋掉半同步復制
#rpl_semi_sync_master_timeout=1000   #注釋掉 
auto_increment_increment = 2          #在剛開始的文件加入此選項，此選項設置 為相同，數字一般為主服務器的數量
auto_increment_offset = 1           #偏移量設置為不同

從庫，即將提升為主庫

[mysqld]
#read_only = 1   #注釋掉
#skip_slave_start   #注釋
log-bin=/data/3307/data/mysql-bin   #開啟二進制文件
binlog_format=mixed
auto_increment_increment = 2     #和上面的主庫一樣
auto_increment_offset = 2      #自增偏移量。
#rpl_semi_sync_slave_enabled=1   #注釋

9.2重啟兩個實例

9.3在3307端口，也就是剛才的從庫上面

mysql> start slave;  
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
 
mysql> select user,host from mysql.user where user='rep';
+------+---------------+
| user | host          |
+------+---------------+
| rep  | 192.168.198.% |
+------+---------------+
1 row in set (0.00 sec)

在主庫上執行：

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.198.139',
    -> MASTER_PORT=3307,    #注意端口
    -> MASTER_USER='rep',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000005',   #即上面看到的日志
    -> MASTER_LOG_POS=107;
Query OK, 0 rows affected (0.04 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

9.4測試

主庫：

mysql> use test;
Database changed
mysql> insert into student(names) values ('yun zhonghe1');
Query OK, 1 row affected (0.02 sec)
 
mysql> insert into student(names) values ('yun zhonghe2'),('yun zhonghe3');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> select * from student;
+----+--------------+
| id | names        |
+----+--------------+
|  1 | yun zhongehe |
|  3 | yun zhonghe1 |
|  5 | yun zhonghe2 |
|  7 | yun zhonghe3 |
+----+--------------+
4 rows in set (0.00 sec)

從庫：

mysql> insert into student(names) values ('huang yaoshi2'),('huang yaoshi3');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> select * from student;
+----+---------------+
| id | names         |
+----+---------------+
|  1 | yun zhongehe |
|  3 | yun zhonghe1 |
|  5 | yun zhonghe2 |
|  7 | yun zhonghe3 |
|  8 | huang yaoshi2 |
|  10| huang yaoshi3 |
+----+---------------+
5 rows in set (0.00 sec)

大致就是這個樣子，中間還是可能會出現一些問題，建議不要這樣使用。

10、如何處理可以忽略的錯誤

有時候，測試數據會弄錯，但是又不想太麻煩，當前的錯誤可以忽略，則可以通過下面的命令實現。

mysql> stop slave;
Query OK, 0 rows affected (0.03 sec)
 
mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

然后就會忽略當前的錯誤了。，

11、總結：

       1)、復制是個大的話題，建議自己多去搜集些資料以供后來參考

       2)、實際中，不要使用mysql的超級用戶登錄，這樣在配置從服務器容易出錯。使用一般的用戶即可。

       3)、這篇博文還有很多未涉及到的東西，以后可能會補充。另外就是如果有錯誤，歡迎指出。

參考：

《高性能MySQL》

http://chrinux.blog.51cto.com/6466723/1204586

http://blog.itpub.net/7970627/viewspace-717664/   MySQL復制線程狀態