Skip to main content

Mysql Replication

Mysql Replication

屏幕快照_2016-09-01_上午10.31.37

Mysql 复制原理

原理

在每次准备提交事务完成数据更新前,主库将数据更新的事件记录到二进制日志中.MySQL会按照事务提交的顺序而非每次语句执行的顺序来记录二进制日志.在记录二进制日志后,主库会告诉存储引擎可以提交事务了
首先备库会启动一个工作线程(IO thread),IO thread跟主库建立一个普通的客户端连接,然后在主库上启动一个二进制转储线程,备库IO thread会将接收到的事件记录 到中继日志中.(binlog dump会读取主库上二进制日志中的事件.该线程不会对事件进行轮询.如果线程追赶上了主库,就会进入睡眠状态,直到主库发送信号量通知有新 的事件产生时才会被唤醒)
备库的SQL thread读取relay log,将变更的事件依次重放.relay log通常来讲在系统中都会有缓存

SQL Thread工作过程

屏幕快照<em>2016-09-01</em>上午10.35.49

Binlog日志格式

Statement(SBR)

binlog_format=statement

Statement格式是Mysql的一种原始格式,所记录的数据为用户执行的SQL记录。因此体积小、兼容性好,方便查找问题及修复。但是有一个严重的问题是会导致数据记录不一致。因为对于用户执行的SQL中的函数,他会原原本本记录下来,而不是记录其函数执行的结果。

Row(RBR)

binlog_format=row

Row格式是使用最广泛的格式。他相比其他格式有更少的锁,由于在记录中记录了结果集,所以对于函数的执行结果,也能一并同步过去。除了文件体积会很大,基本没别的弱点。

Mixed

binlog_format=mixed

Mix格式会混合使用Statement和Row,其DDL会使用Statement格式,而表操作则使用Row格式。可是如果使用Innodb数据库,且事务级别使用READ COMMITTED或READ UNCOMMITTED。则会被强制使用Row格式。

GTID

GTID是Mysql中的一个全局事务标识,他是在全局范围内唯一的一个序号。在开启GTID的时候,会和事务一并记录在binlog中用于标记事务。
GTID由UUID和Seq Num两部分组成。UUID是服务器唯一标识,Seq Num是Mysql内部的事务唯一标识。
在进行复制的时候,通过GTID,从服务器和主服务器之间可以只交互没有复制的事务。从而也实现了并发复制的功能。

使用GTID有以下限制:
1. 不支持非事务引擎 2. 不支持create table ... select 语句复制(主库语法报错) 3. 不支持一个SQL同时更新事务引擎表和非事务引擎表 4. GTID复制环境中,必须要求统一开启GTID或者关闭GTID 5. 开启GTID参数时需要重启生效 6. 不支持create temporary table 和 drop temporary table语句 7. 不支持sqlslaveskip_counter(传统的跳过错误方式)

半同步(Semi-sync)

原因

由于MySQL replication是异步复制因此在极端条件下主库发生宕机时, binlog并没有及时的传输到从库,一旦发生主从切换,就会有很大概率发生主从数据不一致问题。

解决

半同步复制则要求复制过程中(异步),半同步复制要求执行的每一个事务,都要求至少有一个备库成功接收后,才返回给用户。实现原理也很简单,主库本地执行完毕后,等待备库的响应消息(包含最新备库接收到 的binlog(log_file,pos)),接收到备库响应消息后,再返回给用户,这样一个事务才算真正完成

传统模式主从

Master 3306,Slave 3307

配置主服务器

修改配置文件

修改配置文件,配置server_id参数,并开启binlog。注意不要开启gtid相关参数。

server_id=71493306  
#gtid_mode=ON
#enforce-gtid-consistency
log-slave-updates  
binlog-format=row  

启动mysql,检查gtid状态是否开启:

mysql> show variables like '%gtid%';  
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | OFF       |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | OFF       |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.00 sec)

mysql>  

然后创建主从用户:

grant replication slave on *.* to 'fgy58963'@'%' identified by 'xiaocan';  

配置从服务器

我们假设从服务器是从主服务器通过Xtrabackup恢复。如果是从0开始配置或使用mysqldump导入,需要保证配置文件和主服务器所述的配置需求一致。

检查从服务器gtid状态

mysql> show variables like '%gtid%';  
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | OFF       |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | OFF       |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.00 sec)  

从Xtrabackup|Master database中找到binlog index和pos

Master Database:

mysql> show master status\G  
*************************** 1. row ***************************
             File: 3306-mysql-bin.000002
         Position: 1696
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set:  
1 row in set (0.00 sec)

mysql>  

Xtrabackup:

fangdeMac:back fangyuan$ cat xtrabackup_binlog_info  
3306-mysql-bin.000002    1696  
fangdeMac:back fangyuan$  

建立主从关系

change master to master_host='127.0.0.1',master_user='fgy58963',master_password='xiaocan',master_log_file='3306-mysql-bin.000002',master_log_pos=1696;  

启动Slave并检查状态是否可用

start slave;  
show slave status\G;  

GTID主从

Master 3306,Slave 3307

配置主服务器

配置文件中开启GTID相关配置

server_id=71493306  
gtid_mode=ON  
enforce-gtid-consistency  
log-slave-updates  
binlog-format=row  
slave-parallel-workers=6  
master-info-repository=TABLE  

检查主服务器是否开启,授权用户

mysql> show variables like '%gtid%';  
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.01 sec)

mysql> grant replication slave on *.* to 'fgy58963'@'%' identified by 'xiaocan';  
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;  
Query OK, 0 rows affected (0.00 sec)

mysql>  

配置从服务器

我们假设从服务器是从主服务器通过Xtrabackup恢复。如果是从0开始配置或使用mysqldump导入,需要保证配置文件和主服务器所述的配置需求一致。

检查从服务器GTID状态

mysql> show variables like '%gtid%';  
+----------------------------------+--------------------------------------------+
| Variable_name                    | Value                                      |
+----------------------------------+--------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                         |
| enforce_gtid_consistency         | ON                                         |
| gtid_executed_compression_period | 1000                                       |
| gtid_mode                        | ON                                         |
| gtid_next                        | AUTOMATIC                                  |
| gtid_owned                       |                                            |
| gtid_purged                      | 1108aea4-6ebf-11e6-926e-b472204a137f:1-137 |
| session_track_gtids              | OFF                                        |
+----------------------------------+--------------------------------------------+
8 rows in set (0.00 sec)

mysql> show global variables like 'gtid_executed';  
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| gtid_executed | 1108aea4-6ebf-11e6-926e-b472204a137f:1-137 |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

mysql>  

从服务器GTID_EXECUTED为空或者不符合配置的话,需要设置GTID

清空GTID_EXECUTED
reset master  
设置GTID
SET @@GLOBAL.GTID_PURGED='1108aea4-6ebf-11e6-926e-b472204a137f:1-137';  

建立主从关系

change master to master_host="127.0.0.1",master_port=3306,master_user='fgy58963',master_password='xiaocan',master_auto_position=1;  

启动Slave并检查状态是否可用

start slave;  
show slave status\G;  

链式主从

链式主从即Master <-- Slave <-- Slave。只要指定Slave的参数中包含log-slave-updates,然后按正常顺序建立主从即可。
以下仅作为日志记录

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

mysql> reset master;  
Query OK, 0 rows affected (0.01 sec)


mysql> show global variables like 'gtid_executed';  
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_executed |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql> change master to master_host="127.0.0.1",master_port=3307,master_user='fgy58963',master_password='xiaocan',master_auto_position=1;  
Query OK, 0 rows affected, 2 warnings (0.02 sec)


mysql> SET @@GLOBAL.GTID_PURGED='1108aea4-6ebf-11e6-926e-b472204a137f:1-138';  
Query OK, 0 rows affected (0.00 sec)

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

mysql> show slave status\G  
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: fgy58963
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: 3307-mysql-bin.000001
          Read_Master_Log_Pos: 316
               Relay_Log_File: 3308-relay-bin.000002
                Relay_Log_Pos: 429
        Relay_Master_Log_File: 3307-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: 316
              Relay_Log_Space: 635
              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: 71493307
                  Master_UUID: 6874f484-6f84-11e6-be73-1ce93c718239
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 1108aea4-6ebf-11e6-926e-b472204a137f:1-138
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> show databases;  
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mycat              |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> show databases;  
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mycat              |
| mysql              |
| performance_schema |
| slave_test         |
| sys                |
| test               |
+--------------------+
7 rows in set (0.00 sec)

mysql> show databases;  
+--------------------+
| Database           |
+--------------------+
| information_schema |
| master_data        |
| mycat              |
| mysql              |
| performance_schema |
| slave_test         |
| sys                |
| test               |
+--------------------+
8 rows in set (0.00 sec)

mysql>  

Semi-Sync配置

安装插件

在主从上都进行如下操作

检查动态加载是否可用:
mysql> select @@have_dynamic_loading;  
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES                    |
+------------------------+
1 row in set (0.00 sec)

mysql>  
主服务器安装插件:
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';  
Query OK, 0 rows affected (0.02 sec)  
从服务器安装插件:
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';  
Query OK, 0 rows affected (0.00 sec)  
检查插件:
mysql> show plugins;  
+----------------------------+----------+--------------------+--------------------+---------+
| Name                       | Status   | Type               | Library            | License |
+----------------------------+----------+--------------------+--------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
...
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+
45 rows in set (0.01 sec)  

开启功能

配置功能

Master:

开启功能
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_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 1000       |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+------------+
7 rows in set (0.01 sec)  
mysql>  

Slave:

mysql> set global rpl_semi_sync_slave_enabled=1;  
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave;  
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql>

rpl_semi_sync_master_enabled和rpl_semi_sync_slave_enabled同样可以在配置文件中设置

卸载插件

uninstall plugin rpl_semi_sync_master;  
uninstall plugin rpl_semi_sync_slave;  

状态检查

mysql> Show status like '%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             | 1     |
| 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      | 400   |
| Rpl_semi_sync_master_tx_wait_time          | 400   |
| Rpl_semi_sync_master_tx_waits              | 1     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 1     |
| Rpl_semi_sync_slave_status                 | ON    |
+-------------------------

Rpl_semi_sync_master_clients

记录支持半同步的slave的个数

Rpl_semi_sync_master_status

标记master现在是否是半同步复制状态

Rpl_semi_sync_master_no_tx

Master上多少提交的请求没收到slave的响应

Rpl_semi_sync_master_yes_tx

Master上多少提交的请求收到slave的响应

Rpl_semi_sync_slave_status

标记slave现在是否是半同步复制状态

Rpl_semi_sync_master_net_waits

Master等待slave回复总的等待次数

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_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)  

rpl_semi_sync_master_timeout

Master等待Slave返回的超时时间

传统复制错误处理步骤

1. 找到错误问题
2. 解决错误
    先找到错误的pos和错误信息。找到Read_Master_Log_Pos和Exec_Master_Log_Pos。然后解析binlog找到出现问题的事务,分析其执行的内容。再根据内容决定是直接跳过事务,或者手动在Slave上完成事务,或者在Slave上修改让事务可以正确执行。  
3. 如果是回退了错误操作,重新同步,则重新启动Slave。如果是直接在Slave上完成了错误的事务。则跳过错误`SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 跳过事务的数量`,然后再重启Slave。

GTID复制错误处理步骤

GTID解决错误的方法与传统复制一致。区别在于,如果是手动在Slave上完成了事务后跳过这个事务,需要创建一个空的事务来替换掉当前事务,并回复事务记录ID为Automatic状态:

设置要跳过的事务ID
set gtid_next="你要跳过的事务ID";  
进行一个空事务
begin;commit;  
恢复GTID_NEXT为自动模式
set gtid_next='automatic'