一、MySQL 復(fù)制架構(gòu)

• Master/Slave主從架構(gòu)
  • Master:主節(jié)點(diǎn)可讀可寫
  • Slaves: 從節(jié)點(diǎn)只讀、不可寫

1、主從架構(gòu)的優(yōu)點(diǎn)：

1、解決了冗余問題和異地災(zāi)備；
2、配合高可用工具程序MHA，提高可靠性；
3、解決負(fù)載均衡問題，轉(zhuǎn)移一部分“讀”請(qǐng)求；
4、支援安全的備份操作；
5、測(cè)試，主從復(fù)制提供實(shí)驗(yàn)環(huán)境；
...

2、主/從架構(gòu)類型：

• 異步復(fù)制：mysql復(fù)制過程就是異步的；

• 半同步復(fù)制：當(dāng)主節(jié)點(diǎn)完成寫操作時(shí)候，此時(shí)會(huì)立即同步到一部分帶寬較大的節(jié)點(diǎn)（此節(jié)點(diǎn)可提升為主節(jié)點(diǎn)），然后返回結(jié)果給客戶，最后同步給其他節(jié)點(diǎn)；

• 一主多從；一個(gè)主節(jié)點(diǎn)可以有多個(gè)從節(jié)點(diǎn)

  
  
  一主多從

• 一從一主；默認(rèn)時(shí)候是，一從節(jié)點(diǎn) 有一個(gè)一主節(jié)點(diǎn)，mysql5.X以后可以復(fù)制多個(gè)主節(jié)點(diǎn)中的不同數(shù)據(jù)庫；

• 級(jí)聯(lián)復(fù)制；中繼節(jié)點(diǎn)從主節(jié)點(diǎn)復(fù)制，其他從節(jié)點(diǎn)到中繼節(jié)點(diǎn)復(fù)制，減少主節(jié)點(diǎn)復(fù)制壓力；

• 循環(huán)復(fù)制；多個(gè)主節(jié)點(diǎn)基于server id機(jī)制，循環(huán)復(fù)制事物；

  
  
  循環(huán)復(fù)制

• 雙主復(fù)制；兩個(gè)主節(jié)點(diǎn)互相復(fù)制。

• 一從多主：每個(gè)主服務(wù)器提供不同的數(shù)據(jù)庫；

3、主從復(fù)制要考慮問題

• 復(fù)制的開始位置是否從0開始；
• 從備份中恢復(fù)到從節(jié)點(diǎn)后啟動(dòng)的復(fù)制，復(fù)制的起始點(diǎn)備份操作時(shí)主節(jié)點(diǎn)所處的日志文件及其事件位置；
• 主從服務(wù)器mysqld程序版本不一致，從的版本號(hào)高于主的版本號(hào)；

二、主從復(fù)制示例

1、主服務(wù)器：

時(shí)間同步；

[root@mysql-19 ~]# ntpdate time1.aliyun.com

配置文件my.cnf

[root@mysql-19 ~]# vim /etc/my.cnf.d/server.cnf 
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON

server_id=1
log_bin=log-bin

啟動(dòng)服務(wù)：

systemctl start mariadb.service

授權(quán)從服務(wù)器復(fù)制賬號(hào)

MariaDB [(none)]> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.1.20' IDENTIFIED BY 'replpass';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
#查看二進(jìn)制日志信息
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000001 |      245 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

2、從服務(wù)器：

時(shí)間同步；

[root@mysql-19 ~]# ntpdate time1.aliyun.com

配置文件my.cnf

[root@mysql-20 ~]# vim /etc/my.cnf.d/server.cnf 
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON

server_id=12
relay_log=relay-log
read_only=ON

啟動(dòng)服務(wù)：

systemctl start mariadb.service

設(shè)置從服務(wù)器復(fù)制

[root@mysql-20 ~]# mysql

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.19',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_PORT=3306,MASTER_LOG_FILE='master-log.000001',MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.05 sec)
#啟動(dòng)線程進(jìn)行復(fù)制
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
#查看
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.19
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-log.000001
          Read_Master_Log_Pos: 498
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 783
        Relay_Master_Log_File: master-log.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: 498
              Relay_Log_Space: 1071
              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: 1
1 row in set (0.01 sec)

3、測(cè)試：

#主數(shù)據(jù)庫操作
MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use mydb;
Database changed

MariaDB [mydb]> CREATE TABLE tbl1(id INT,name CHAR(30),age INT);
Query OK, 0 rows affected (0.33 sec)

#從數(shù)據(jù)庫查詢是否同步
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)

MariaDB [(none)]> use mydb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mydb]> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| tbl1           |
+----------------+
1 row in set (0.00 sec)

MariaDB [mydb]> desc tbl1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(30) | YES  |     | NULL    |       |
| age   | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

三、主主復(fù)制：

• 互為主從：兩個(gè)節(jié)點(diǎn)各自都要開啟binlog和relay log；
• 需要解決數(shù)據(jù)不一致和自動(dòng)增長(zhǎng)id問題；

定義一個(gè)節(jié)點(diǎn)使用奇數(shù)id

auto_increment_offset=1 #起始偏移值
auto_increment_increment=2 #步進(jìn)值

另一個(gè)節(jié)點(diǎn)使用偶數(shù)id

auto_increment_offset=2 #起始偏移值
auto_increment_increment=2 #步進(jìn)值

1、節(jié)點(diǎn)一服務(wù)器配置：

[root@mysql-19 ~]# vim /etc/my.cnf.d/server.cnf 
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON

server_id=1
log-bin=master-log
relay_log=relay-log

auto_increment_offset=1 #起始偏移值
auto_increment_increment=2  #步進(jìn)值
[root@mysql-19 ~]# systemctl start mariadb
[root@mysql-19 ~]# mysql
#復(fù)制授權(quán)
MariaDB [(none)]> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.1.20' IDENTIFIED BY 'replpass';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
#查看二進(jìn)制日志位置
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 |      770 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

#設(shè)置訪問節(jié)點(diǎn)二用戶名密碼
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.20',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_PORT=3306,MASTER_LOG_FILE='master-log.000003',MASTER_LOG_POS=770;
Query OK, 0 rows affected (0.01 sec)
#啟動(dòng)復(fù)制
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

2、節(jié)點(diǎn)二配置：

[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON

server_id=12
log_bin=master-log
relay-log=relay-log
read_only=ON

auto_increment_offset=2
auto_increment_increment=2

#從服務(wù)器訪問授權(quán)
MariaDB [(none)]> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.1.19' IDENTIFIED BY 'replpass';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
#設(shè)置訪問節(jié)點(diǎn)一用戶名密碼
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.19',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_PORT=3306,MASTER_LOG_FILE='master-log.000003',MASTER_LOG_POS=770;
Query OK, 0 rows affected (0.06 sec)
#查看二進(jìn)制日志位置
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 |      770 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#啟用復(fù)制
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

3、測(cè)試：

#節(jié)點(diǎn)一操作：創(chuàng)建mydb數(shù)據(jù)庫，待節(jié)點(diǎn)二創(chuàng)建完表后，再次查詢
MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> use mydb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mydb]> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| tbl1           |
+----------------+
1 row in set (0.00 sec)

MariaDB [mydb]> desc tbl1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(30) | YES  |     | NULL    |       |
| age   | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#節(jié)點(diǎn)二操作：在mydb庫中創(chuàng)建表
MariaDB [(none)]> use mydb;
Database changed
MariaDB [mydb]> CREATE TABLE tbl1(id INT,name CHAR(30),age INT);
Query OK, 0 rows affected (0.06 sec)

4、配置注意：

• server_id必須要使用不同值；
• 均啟用binlog和relay log；
• 存在自動(dòng)增長(zhǎng)id的表，為了使得id不相沖突，需要定義其自動(dòng)增長(zhǎng)方式；

5、服務(wù)啟動(dòng)后執(zhí)行如下兩步：

• 所有節(jié)點(diǎn)都授權(quán)有復(fù)制權(quán)限的用戶賬號(hào)；
• 各把對(duì)方指定為主節(jié)點(diǎn)；

6、復(fù)制時(shí)應(yīng)該注意的問題：

• (1)從服務(wù)設(shè)定為“只讀”；
  在從服務(wù)器啟動(dòng)read_only，但僅對(duì)非SUPER權(quán)限的用戶有效；

• (2)盡量確保復(fù)制時(shí)的事務(wù)安全
  在master節(jié)點(diǎn)啟用參數(shù)：
  sync_binlog = ON
  如果用到的是InnoDB存儲(chǔ)引擎：
  innodb_flush_logs_at_trx_commit=ON
  innodb_support_xa=ON

• (3)、從服務(wù)器意外中止時(shí)盡量避免自動(dòng)啟動(dòng)復(fù)制線程

• (4)、從節(jié)點(diǎn)：設(shè)置參數(shù)
  sync_master_info=ON 同步到磁盤
  sync_relay_log_info=ON

四、半同步復(fù)制

• google為mysql提供的插件

• 支持多種插件：/usr/lib64/mysql/plugins/

• 需要安裝方可使用：

mysql> INSTALL PLUGIN plugin_name SONAME 'shared_library_name';

• 半同步復(fù)制：
  semisync_master.so 主節(jié)點(diǎn)半同步插件
  semisync_slave.so 從節(jié)點(diǎn)半同步插件

1、主節(jié)點(diǎn)：

#安裝主節(jié)點(diǎn)半同步插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
#查詢主節(jié)點(diǎn)半同步插件狀態(tài)
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | OFF   |  #啟用狀態(tài)
| rpl_semi_sync_master_timeout       | 10000 |  #等待從節(jié)點(diǎn)返回值超時(shí)時(shí)長(zhǎng)
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
#啟用主節(jié)點(diǎn)半同步插件
MariaDB [mydb]> SET @@global.rpl_semi_sync_master_enabled=ON;

2、從節(jié)點(diǎn)：

#安裝從節(jié)點(diǎn)半同步插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
#查看從節(jié)點(diǎn)半同步插件當(dāng)前狀態(tài)
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | OFF   |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
#啟用從節(jié)點(diǎn)半同步插件
MariaDB [mydb]> SET @@global.rpl_semi_sync_slave_enabled=ON;
#查看從節(jié)點(diǎn)半同步插件當(dāng)前狀態(tài)
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
#重啟線程
MariaDB [mydb]> STOP SLAVE IO_THREAD;
MariaDB [mydb]> START SLAVE IO_THREAD;

3、測(cè)試：

主節(jié)點(diǎn)：

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'rpl%';
+--------------------------------------------+-------------+
| Variable_name                              | Value       |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients               | 1           |   #已同步一臺(tái)從節(jié)點(diǎn)
| Rpl_semi_sync_master_net_avg_wait_time     | 0           |  #平均等待時(shí)長(zhǎng)（毫秒）
| Rpl_semi_sync_master_net_wait_time         | 0           |   #共計(jì)等待時(shí)長(zhǎng)（毫秒）
| Rpl_semi_sync_master_net_waits             | 0           |  #等待次數(shù)
| 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           |
| Rpl_status                                 | AUTH_MASTER |
+--------------------------------------------+-------------+
15 rows in set (0.00 sec)

五、復(fù)制過濾器：

• 僅復(fù)制有限一個(gè)或幾個(gè)數(shù)據(jù)庫相關(guān)的數(shù)據(jù)，而非所有；由復(fù)制過濾器進(jìn)行；

有兩種實(shí)現(xiàn)思路：

1、主服務(wù)器端過濾

• 主服務(wù)器僅向二進(jìn)制日志中記錄有關(guān)特定數(shù)據(jù)庫相關(guān)的寫操作；
  問題：其它庫的time-point recovery將無從實(shí)現(xiàn)，只能到庫級(jí)別過濾，不建議使用此種過濾方法；

注意：過濾設(shè)置不能同時(shí)使用。

binlog_do_db= 僅對(duì)某個(gè)庫有寫權(quán)限（白名單）
binlog_ignore_db= 除此庫之外的所有有寫權(quán)限（黑名單）

2、從服務(wù)器端過濾

• 從服務(wù)器的SQL THREAD僅重放關(guān)注的數(shù)據(jù)庫或表相關(guān)的事件，并將其應(yīng)用于本地；
  問題：因?yàn)橐阎鞴?jié)點(diǎn)的所有庫都復(fù)制過來，然后再過濾需要的庫和表進(jìn)行重放，所以無用的復(fù)制占用了網(wǎng)絡(luò)IO和磁盤IO；

Replicate_Do_DB= 庫級(jí)別白名單
Replicate_Ignore_DB= 庫級(jí)別黑名單
Replicate_Do_Table= 表級(jí)別黑名單
Replicate_Ignore_Table= 表級(jí)別黑名單
Replicate_Wild_Do_Table= 使用通配符匹配白名單
Replicate_Wild_Ignore_Table= 使用通配符匹配黑名單

從服務(wù)器：

#查詢從節(jié)點(diǎn)相關(guān)設(shè)置信息
MariaDB [(none)]> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.19
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-log.000004
          Read_Master_Log_Pos: 245
               Relay_Log_File: relay-log.000005
                Relay_Log_Pos: 530
        Relay_Master_Log_File: master-log.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:    #庫級(jí)別白名配置
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
.......
#查詢庫級(jí)別白名單配置
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%do_db%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| replicate_do_db |       |
+-----------------+-------+
1 row in set (0.00 sec)
#停止從節(jié)點(diǎn)復(fù)制線程
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.01 sec)
#設(shè)置庫白名單啟用
MariaDB [(none)]> SET @@global.replicate_do_db=mydb;
Query OK, 0 rows affected (0.00 sec)
#啟用從節(jié)點(diǎn)復(fù)制線程
MariaDB [(none)]> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
#查詢從節(jié)點(diǎn)設(shè)置信息
MariaDB [(none)]> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.19
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-log.000004
          Read_Master_Log_Pos: 245
               Relay_Log_File: relay-log.000005
                Relay_Log_Pos: 530
        Relay_Master_Log_File: master-log.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: mydb   #設(shè)置成mydb庫
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
           .................

六、復(fù)制的監(jiān)控和維護(hù)：

1、清理日志：PURGE

格式：PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr };

#備份二進(jìn)制日志文件
[root@mysql-19 ~]# mkdir /data/backup/binlogs -pv
mkdir: created directory ‘/data/backup/binlogs’
[root@mysql-19 ~]# cp /var/lib/mysql/master-log.* /data/backup/binlogs/


MariaDB [(none)]> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-log.000001 |     30340 |
| master-log.000002 |   1038814 |
| master-log.000003 |       498 |
| master-log.000004 |       245 |
+-------------------+-----------+
4 rows in set (0.00 sec)
#刪除00004之前的日志
MariaDB [(none)]> PURGE MASTER LOGS TO 'master-log.000004';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-log.000004 |       245 |
+-------------------+-----------+
1 row in set (0.00 sec)
#自動(dòng)更新index文件
[root@mysql-19 ~]# cat /var/lib/mysql/master-log.index 
./master-log.000004

2、 復(fù)制監(jiān)控語句

• MASTER:顯示主節(jié)點(diǎn)信息

SHOW MASTER STATUS \G;
SHOW BINLOG EVENTS; #查詢二進(jìn)制文件數(shù)據(jù)記錄位置
SHOW BINARY LOGS; #查詢二進(jìn)制文件

• SLAVE:顯示從節(jié)點(diǎn)信息

SHOW SLAVE STATUS \G;

• 判斷從服務(wù)器是否落后于主服務(wù)器：

Seconds_Behind_Master: 0

3、 如何確定主從節(jié)點(diǎn)數(shù)據(jù)是否一致

通過表的CHECKSUM檢查；
使用percona-toolkit中pt-table-checksum命令；

• 用途： 可以用來檢測(cè)主、 從數(shù)據(jù)庫中數(shù)據(jù)的一致性。
• 原理： 在主庫上運(yùn)行， 對(duì)同步的表進(jìn)行checksum， 記錄下來。 然后對(duì)比主從中各個(gè)表的checksum是否一致， 從而判斷數(shù)據(jù)是否一致。

注意：
1、根據(jù)測(cè)試，需要一個(gè)技能登錄主庫，也能登錄從庫，而且還能同步數(shù)據(jù)庫的賬號(hào)；
2、只能指定一個(gè)host，必須為主庫的IP；
3、在檢查時(shí)會(huì)向表加S鎖；
4、運(yùn)行之前需要從庫的同步IO和SQL進(jìn)程是YES狀態(tài)。

#下載
[root@mysql-20 ~]# wget https://www.percona.com/downloads/percona-toolkit/3.0.12/binary/redhat/7/x86_64/percona-toolkit-3.0.12-1.el7.x86_64.rpm
#安裝
[root@mysql-20 ~]# yum install percona-toolkit-3.0.12-1.el7.x86_64.rpm -y
#校驗(yàn)
[root@mysql-20 ~]# pt-table-checksum h=192.168.1.19,u=myadmin,p=mypass,P=3306 --databases=mysql --tables=tbl3 --nocheck-replication-filters 
#結(jié)果
.............
# A software update is available:
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
11-03T18:51:23      0      0        3          0       1       0   0.035 mysql.tbl3
............

參數(shù)說明：

TS            ：完成檢查的時(shí)間。
ERRORS        ：檢查時(shí)候發(fā)生錯(cuò)誤和警告的數(shù)量。
DIFFS         ：0表示一致，1表示不一致。當(dāng)指定--no-replicate-check時(shí)，會(huì)一直為0，當(dāng)指定--replicate-check-only會(huì)顯示不同的信息。
ROWS          ：表的行數(shù)。
CHUNKS        ：被劃分到表中的塊的數(shù)目。
SKIPPED       ：由于錯(cuò)誤或警告或過大，則跳過塊的數(shù)目。
TIME          ：執(zhí)行的時(shí)間。
TABLE         ：被檢查的表名。

參數(shù)意義：
--nocheck-replication-filters ：不檢查復(fù)制過濾器，建議啟用。后面可以用--databases來指定需要檢查的數(shù)據(jù)庫。
--no-check-binlog-format      : 不檢查復(fù)制的binlog模式，要是binlog模式是ROW，則會(huì)報(bào)錯(cuò)。
--replicate-check-only :只顯示不同步的信息。
--replicate=   ：把checksum的信息寫入到指定表中，建議直接寫到被檢查的數(shù)據(jù)庫當(dāng)中。
--databases=   ：指定需要被檢查的數(shù)據(jù)庫，多個(gè)則用逗號(hào)隔開。
--tables=      ：指定需要被檢查的表，多個(gè)用逗號(hào)隔開
h=127.0.0.1    ：Master的地址
u=root         ：用戶名
p=123456       ：密碼
P=3306         ：端口

參數(shù)解釋：
# --h -u -p -P -S -d 連接信息
# --nocheck-replication-filters 檢測(cè)中忽略mysql 配置參數(shù)binlog_ignore_db等
# --nocheck-binlog-format 不檢測(cè)日志格式
# --replicate 指定checksum 存儲(chǔ)的db和表， 如：pt.checksum
# --databases 指定數(shù)據(jù)庫，如：nobserver_new
# --chunk-size， --chunk-size-limit 用于指定檢測(cè)塊的大小，可控性更強(qiáng)
# --ignore-databases/tables/column 跳出指定元素的過濾
# --lock-wait-timeout innodb 鎖的超時(shí)設(shè)定， 默認(rèn)為1
# --max-load 設(shè)置最大并發(fā)連接數(shù)
# --replicate-check-only 只輸出數(shù)據(jù)不一致的信息

#更多的參數(shù)請(qǐng)見官網(wǎng)，上面指出來的是常用的，對(duì)該場(chǎng)景夠用的參數(shù)。
#通過DIFFS是1可以看出主從的表數(shù)據(jù)不一致，通過查看從庫上的test.checksum表可以看到主從庫的檢驗(yàn)信息。
#當(dāng)DIFFS列全部為0時(shí)表示Master、Slave無差異。

4、 主從數(shù)據(jù)不一致時(shí)的修復(fù)方法

方法一：

重新復(fù)制數(shù)據(jù)庫，結(jié)合二進(jìn)制文件恢復(fù)。

方法二：

• pt-table-sync修復(fù)從庫不一致的數(shù)據(jù)
  使用方法：pt-table-sync [OPTIONS] DSN [DSN]
• pt-table-sync: 高效的同步MySQL表之間的數(shù)據(jù)，它可以做單向和雙向同步的表數(shù)據(jù)?？梢酝絾蝹€(gè)表，也可以同步整個(gè)庫。它不同步表結(jié)構(gòu)、索引、或任何其他模式對(duì)象。所以在修復(fù)一致性之前需要保證它們表存在。

#同步主從數(shù)據(jù)
[root@mysql-20 ~]# pt-table-sync --print --replicate=test.checksum h=192.168.1.21,u=myadmin,p=mypass,P=3306 h=192.168.1.19,u=myadmin,p=mypass,P=3306 #第一個(gè)ip是主節(jié)點(diǎn)，第二個(gè)ip是從節(jié)點(diǎn)
#或者
#用一個(gè)從節(jié)點(diǎn)ip執(zhí)行同步庫里的指定表
[root@mysql-20 bin]# pt-table-sync --print --sync-to-master h=192.168.1.19,u=myadmin,p=mypass,P=3306 --databases mysql --tables tbl3

參數(shù)的意義：
--replicate=  ：指定通過pt-table-checksum得到的表，這2個(gè)工具差不多都會(huì)一直用。
--databases=  : 指定執(zhí)行同步的數(shù)據(jù)庫，多個(gè)用逗號(hào)隔開。
--tables=     ：指定執(zhí)行同步的表，多個(gè)用逗號(hào)隔開。
--sync-to-master ：指定一個(gè)DSN，即從的IP，他會(huì)通過show processlist或show slave status 去自動(dòng)的找主。
h=127.0.0.1   ：服務(wù)器地址，命令里有2個(gè)ip，第一次出現(xiàn)的是M的地址，第2次是Slave的地址。
u=root        ：帳號(hào)。
p=123456      ：密碼。
--print       ：打印，但不執(zhí)行命令。
--execute     ：執(zhí)行命令。

更多的參數(shù)請(qǐng)見官網(wǎng)，上面指出來的是常用的，對(duì)該場(chǎng)景夠用的參數(shù)

注意：要是表中沒有唯一索引或則主鍵則會(huì)報(bào)錯(cuò)：
Can't make changes on the master because no unique index exists at /usr/local/bin/pt-table-sync line 10591.

七、讀鎖FTWRL在備份中的使用

• 當(dāng)你使用邏輯方式進(jìn)行備份（mydumper，mysqldump）或物理方式進(jìn)行備份（percona-xtrabackup），為了保證數(shù)據(jù)的一致性，這兩種備份方式都會(huì)在備份過程中執(zhí)行 flush table with read lock 這個(gè)命令（以下簡(jiǎn)稱為FTWRL），通過執(zhí)行FTWRL，來對(duì)事務(wù)和非事務(wù)表來加table level級(jí)別的共享鎖，取得此時(shí)的gtid或者binlog偏移量，繼而得到某一個(gè)時(shí)間點(diǎn)的備份數(shù)據(jù)。

讀鎖的作用

flush tables with read lock的作用是關(guān)閉所有打開的表，同時(shí)對(duì)于所有數(shù)據(jù)庫中的表都加一個(gè)讀鎖，直到顯示地執(zhí)行unlock tables，該操作常常用于數(shù)據(jù)備份的時(shí)候也就是將所有的臟頁都要刷新到磁盤，然后對(duì)所有的表加上了讀鎖，于是這時(shí)候直接拷貝數(shù)據(jù)文件也就是安全的。

工作流程

• 第一步的作用是堵塞更新，備份時(shí)，我們期望獲取此時(shí)數(shù)據(jù)庫的一致狀態(tài)，不希望有更多的更新操作進(jìn)來。對(duì)于innodb引擎而言，其自身的MVCC機(jī)制，可以保證讀到老版本數(shù)據(jù)，因此第一步對(duì)它使多余的。
• 第二步，清理表緩存，這個(gè)操作對(duì)于myisam有意義，關(guān)閉myisam表時(shí)，會(huì)強(qiáng)制要求表的緩存落盤，這對(duì)于物理備份myisam表是有意義的，因?yàn)槲锢韨浞菔侵苯涌截愇锢砦募?duì)于innodb表，則無需這樣，因?yàn)閕nnodb有自己的redolog，只要記錄當(dāng)時(shí)LSN，然后備份LSN以后的redolog即可。
• 第三步，主要是保證能獲取一致性的binlog位點(diǎn)，這點(diǎn)對(duì)于myisam和innodb作用是一樣的。

讀鎖缺點(diǎn)：

• 由于FTWRL總共需要持有兩把全局的MDL鎖，并且還需要關(guān)閉所有表對(duì)象，因此這個(gè)命令的殺傷性很大，執(zhí)行命令時(shí)容易導(dǎo)致庫hang住。如果是主庫，則業(yè)務(wù)無法正常訪問；如果是備庫，則會(huì)導(dǎo)致SQL線程卡住，主備延遲。

改進(jìn)

• 在 5.6.16-64.0這個(gè)版本中，percona開始引入了兩個(gè)新的MDL類型的鎖，相應(yīng)的引入了兩個(gè)新的備份命令

LOCK TABLES FOR BACKUP
LOCK BINLOG FOR BACKUP

• lock tables for backup
  ? 執(zhí)行該命令后，獲得的新的MDL鎖會(huì)阻塞對(duì)非事務(wù)表的更新及所有DDL動(dòng)作，此時(shí)其他用戶可以繼續(xù)更新inonodb引擎的表，但是無法對(duì)myisam表進(jìn)行更新動(dòng)作。

• lock binglog for backup
  ? 執(zhí)行該命令后，如果加鎖成功，將會(huì)阻塞binglog的更新，此時(shí)所有的DML操作被阻塞。
  參考文獻(xiàn)：https://www.cnblogs.com/cchust/p/4603599.html
  https://www.cnblogs.com/conanwang/p/6925108.html
  https://www.cnblogs.com/sunss/archive/2012/02/02/2335960.html
  http://www.freeoa.net/osuport/db/chk-and-rep-mysql-msprob-by-percona-toolkit_3098.html