天天看點
傳回

mysql主從(一)--搭建(GTID+row+增強半同步)1、主從搭建2、主從不一緻時的修複

文章目錄

  • 1、主從搭建
    • 1.1、mysql5.7 GTID+row+增強半同步
      • 1.1.1、主從庫環境準備
      • 1.1.2、主庫(3309)備份資料,在從庫(3306)恢複
      • 1.1.3、建立複制使用者
      • 1.1.4、從庫(3306)執行change master,start slave
    • 1.2、配置為雙主模式
  • 2、主從不一緻時的修複
    • 2.1、從庫已經有T2表，再在主庫上建立T2
    • 2.2、主鍵沖突的錯誤 1062
    • 2.3、主庫上更新後，從庫找不到記錄 1032
    • 2.4、主庫上delete後，從庫找不到記錄 1032

1、主從搭建

1.1、mysql5.7 GTID+row+增強半同步

實踐經驗證明GTID+row+增強半同步方式是一種靠譜的方式，已經在金融環境廣泛應用。

GTID的限制

1) 不支援非事務引擎，從庫報錯，stop ,start slave 忽略報錯
2) 不支援create table .. select 語句複制
3) 不允許一個SQL同時更新一個事務引擎和非事務引擎的表
4) 在一個複制組中，必須要求統一開啟GTID或者關閉GTID。5.7開始支援GTID的線上切換
5) 5.7之前，開啟GTID需要重新開機
6) 開啟GTID後，就不在使用原來傳統的複制方式。MHA也被GTID功能替代。
7) 對于create temporary table不會同步，但是drop temporary table會同步，要避開這個坑
8) 不支援sql slave skip counter

複制格式

推薦使用row格式，statement和mixed格式坑太多。

關于增強半同步

rpl_semi_sync_master_wait_point = AFTER_SYNC #5.7中預設已經是after_sync

測試環境

庫 IP 端口
主庫 1.1.3.111 3309
從庫 1.1.3.111 3306

1.1.1、主從庫環境準備

主從庫需要修改的參數：

–主庫(3309)

############replication###############
master_info_repository = TABLE
relay_log_info_repository = TABLE

max_binlog_size = 1G
binlog_cache_size = 4M
max_binlog_cache_size = 2G
binlog_format = row
log_bin=/data/mysql/mysql57/logs/mysql-bin
relay_log = /data/mysql/mysql57/logs/mysql-relay-bin
relay_log_index = /data/mysql/mysql57/logs/mysql-relay-bin.index
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
#除了中繼節點
log_slave_updates = 1
sync_binlog = 10

#######gtid####################
gtid_mode = on
enforce_gtid_consistency = 1 
binlog_gtid_simple_recovery = 1
###############################
#并行複制參數
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=8

########semi sync replication settings########
#plugin_dir=/usr/local/mysql-5.7.15/lib/plugin  #不同執行個體不同的位置
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000
rpl_semi_sync_master_wait_point = AFTER_SYNC   #5.7中預設值是after_sync

–從庫(3306)的log_bin位置,其它參數和主庫的設定一樣

log_bin=/data/mysql/mysql3306/logs/mysql-bin
relay_log = /data/mysql/mysql3306/logs/mysql-relay-bin
relay_log_index = /data/mysql/mysql3306/logs/mysql-relay-bin.index

1.1.2、主庫(3309)備份資料,在從庫(3306)恢複

方法一：mysqldump

  • 在備份時候需要指定–master-data
  • 導出的語句中包含 set@@GLOBAL.GTID_PURGED=’’，恢複時，需要先在slave上執行reset master
  • 導入資料後做change master to。

如果主從1:1複制，使用-A參數全庫級别備份:

/usr/local/mysql57/bin/mysqldump --single-transaction --master-data=2 -uroot -pbigbangdata.cn  -A -S /usr/local/mysql57/mysql.sock >3309-full.sql

如果要排除mysql、sys等系統庫使用下面的腳本：

/usr/local/mysql57/bin/mysql -uroot -pbigbangdata.cn -S /usr/local/mysql57/mysql.sock -e 'show databases;'|grep -E -v "Database|information_schema|mysql|performance_schema"|xargs /usr/local/mysql57/bin/mysqldump -uroot -pbigbangdata.cn -S /usr/local/mysql57/mysql.sock --databases > 3309.sql

方法二：percona xtrabackup

  • xtrabakcup binlog info 包含了GTID資訊
  • 做從庫恢複後，需要手工設定：set gbloabl gtid_purged=‘server_uuid:1-N’
  • 恢複後，執行change master to

注意 這裡的 N 是特指

從庫恢複

source 3309-full.sql

1.1.3、建立複制使用者

在主庫(3309)上建立repl使用者，如果是雙主，從庫上也要建立

create user repl@'1.1.3.111' identified by '1234.C0m';
grant replication slave on *.* to repl@'1.1.3.111';

1.1.4、從庫(3306)執行change master,start slave 

change master to master_host='1.1.3.111', master_user='repl', master_password='1234.C0m', master_port=3309, master_auto_position=1;

如果要設定忽略的庫，建議在從庫上配置：

CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB = (information_schema,performance_schema,mysql,sys);

檢查配置無誤後，start slave;

1.2、配置為雙主模式

基于完成1.1章節的配置，在主庫上反過來配置從庫的設定即可。

從庫(3306)建立repl使用者,如果IP相同，上面步驟同步了mysql庫，則下面腳本無需執行

create user repl@'1.1.3.111' identified by '1234.C0m';
grant replication slave on *.* to repl@'1.1.3.111';

主庫(3309)執行change master指令：

change master to master_host='1.1.3.111', master_user='repl', master_password='1234.C0m', master_port=3306, master_auto_position=1;

最後，start slave;

至此，執行個體3309和3306上的庫互為主從。

2、主從不一緻時的修複

2.1、從庫已經有T2表，再在主庫上建立T2

處理原則：以主庫為準，在從庫上drop t2。 然後重新開機slave。

注意： 在db裡的操作都會記錄到binlog中，如果不想被記錄到binlog中，可以先set sql_log_bin=0.drop完成後，再 set sql_log_bin=1即可。

如果drop 操作被記錄到binlog中，在發生主從切換後，主庫的t2 表會被删除。

從5.7 開始，有super read only。

Slave_IO_Running: Yes
            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: 1050
                   Last_Error: Error 'Table 't2' already exists' on query. Default database: 'myreptest'. Query: 'create table t2 (id int)'

基于GTID錯誤 跳過

由于mysql 中GTID 在複制中 GTID 要求必須是連續的，主庫的GTID，必須在從庫上也有。是以不能skip過去一個事務。隻能通過注入一個空事務去跳過一個事務。

stop slave;
set gtid_next='xxxx:N';
<<<<< 這裡xxxx 指定的是主庫的server_uuid 和你想跳過的事務ID
begin;commit;  <<<<<<<<<<< 空事務
set gtid_next='AUTOMATIC';
start slave;
show slave status\G

2.2、主鍵沖突的錯誤 1062 

[email protected]:mysql3307.sock [myreptest]>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.103
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000050
          Read_Master_Log_Pos: 5442
               Relay_Log_File: relay-bin.000011
                Relay_Log_Pos: 2178
        Relay_Master_Log_File: mysql-bin.000050
             Slave_IO_Running: Yes
            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: 1062
                   Last_Error: Could not execute Write_rows event on table myreptest.myreptest; Duplicate entry '2' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000050, end_log_pos 5411

<<<<<<<<< 這裡很明顯提示 duplicate entry 2 沖突了，在從庫上把這條記錄删掉，然後重新開機slave即可。

2.3、主庫上更新後，從庫找不到記錄 1032 

[email protected]:mysql3307.sock [myreptest]>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.103
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000050
          Read_Master_Log_Pos: 5121
               Relay_Log_File: relay-bin.000011
                Relay_Log_Pos: 1849
        Relay_Master_Log_File: mysql-bin.000050
             Slave_IO_Running: Yes
            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: 1032
                   Last_Error: Could not execute Update_rows event on table myreptest.myreptest; Can't find record in 'myreptest', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000050, end_log_pos 5090
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4792   <<<<<< 報錯事務的開始位置。
              Relay_Log_Space: 4142
              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: 1032
               Last_SQL_Error: Could not execute Update_rows event on table myreptest.myreptest; Can't find record in 'myreptest', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000050, end_log_pos 5090
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1033306
                  Master_UUID: d490cfb4-caac-11e6-b1da-080027d6827d
             Master_Info_File: /data/mysql/mysql3307/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 170215 09:38:48
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: d490cfb4-caac-11e6-b1da-080027d6827d:79-98
            Executed_Gtid_Set: d035c34f-cbaf-11e6-8a78-080027d6827d:1-7,
d490cfb4-caac-11e6-b1da-080027d6827d:1-97
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

這時需要解析主庫的binlog，把從庫的資料補回來。

mysqlbinlog --base64-output=decode-rows -v --start-position=4792    --stop-position=5090 mysql-bin.000050 > 50.sql

......
......
# at 4982
#170215  9:38:48 server id 1033306  end_log_pos 5032 CRC32 0xc902e88e   Table_map: `wu
bx`.`myreptest` mapped to number 152
# at 5032
#170215  9:38:48 server id 1033306  end_log_pos 5090 CRC32 0x820931f0   Update_rows: t
able id 152 flags: STMT_END_F
### UPDATE `myreptest`.`myreptest`
### WHERE
###   @1=2
###   @2='myreptest'
### SET
###   @1=2
###   @2='python'
ROLLBACK /* added by mysqlbinlog */ /*!*/;
......
......

這裡就能看到從庫丢失的那條記錄。然後在從庫補充這條記錄即可。

2.4、主庫上delete後，從庫找不到記錄 1032 

[email protected]:mysql3307.sock [myreptest]>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.103
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000050
          Read_Master_Log_Pos: 2779
               Relay_Log_File: relay-bin.000010
                Relay_Log_Pos: 985
        Relay_Master_Log_File: mysql-bin.000050
             Slave_IO_Running: Yes
            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: 1032
                   Last_Error: Could not execute Delete_rows event on table myreptest.myreptest; Can't find record in 'myreptest', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000050, end_log_pos 2748  <<<<<< 該事物的結束位置
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2465   <<<<<< 該事物的開始位置
              Relay_Log_Space: 1800
              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: 1032
               Last_SQL_Error: Could not execute Delete_rows event on table myreptest.myreptest; Can't find record in 'myreptest', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000050, end_log_pos 2748
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1033306
                  Master_UUID: d490cfb4-caac-11e6-b1da-080027d6827d
             Master_Info_File: /data/mysql/mysql3307/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 170214 11:32:39
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: d490cfb4-caac-11e6-b1da-080027d6827d:79-89 
            Executed_Gtid_Set: d035c34f-cbaf-11e6-8a78-080027d6827d:1-6,
d490cfb4-caac-11e6-b1da-080027d6827d:1-88
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)


Retrieved_Gtid_Set: d490cfb4-caac-11e6-b1da-080027d6827d:79-89   <<<<<< 這裡為什麼會從79開始 而不是1.是因為從庫是用mysqldump備份過來的。dump内容包含了1-78這部分所有的内容了。show global variables like 'gtid%';  

+----------------------------------+-------------------------------------------------------------------------------------+
| Variable_name                    | Value                                                                               |
+----------------------------------+-------------------------------------------------------------------------------------+
| gtid_executed                    | d035c34f-cbaf-11e6-8a78-080027d6827d:1-7,
d490cfb4-caac-11e6-b1da-080027d6827d:1-90 |
| gtid_executed_compression_period | 1000                                                                                |
| gtid_mode                        | ON                                                                                  |
| gtid_owned                       |                                                                                     |
| gtid_purged                      | d490cfb4-caac-11e6-b1da-080027d6827d:1-78                                           |
+----------------------------------+-------------------------------------------------------------------------------------+

這裡可以使用GTID的跳過功能。

stop slave;
set gtid_next='d490cfb4-caac-11e6-b1da-080027d6827d:89';  89是Retrieved_Gtid_Set的位置。Executed_Gtid_Set 裡面的ID 都是已經執行過了的。
begin;commit;
set gtid_next='automatic';
start slave;
show slave status\G;

想看某段pos内執行過的sql：

主庫執行：

mysqlbinlog --base64-output=decode-rows -v --start-position=2465 --stop-position=2748 mysql-bin.000050 > 50.sql

輸出如下：

### DELETE FROM `myreptest`.`myreptest`
### WHERE
###   @1=2
###   @2='myreptest'
ROLLBACK /* added by mysqlbinlog */ /*!*/;  <<<<< 注意這裡的rollback。如果以後基于binlog和時間點的恢複。這條資料會被rollback掉，造成一條資料的丢失。是以如果想保留這條資料，需要找到commit的位置，或者下個pos的位置。
           
[email protected]:mysql3307.sock [myreptest]>show processlist;
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time  | State                                                  | Info             |
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
| 22 | system user |           | NULL | Connect | 79359 | Waiting for master to send event                       | NULL             |  <<<<< IO_thread，從主庫拿日志。
| 32 | system user |           | NULL | Connect |  1420 | Slave has read all relay log; waiting for more updates | NULL      <<<<< SQL therad
| 33 | root        | localhost | myreptest | Query   |     0 | starting                                               | show processlist |
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
MySQL資料庫 mysql 主從 gtid+row+增強半同步
上一篇: 【C#+Access+WindowsAPI】實作仿360的安全衛士 四：設計功能集合窗體講解（附源碼和資源）一、功能集合窗體概述二、功能集合窗體實作流程 三、分割功能集合窗體四、設計導航工具欄五、設計系統清理面闆六、設計實用工具面闆 七、設計任務管理面闆八、設計選項面闆
下一篇: Java的I/O系統FilenameFilter輸入輸出存儲和恢複資料随機存儲通路資料

繼續閱讀