学习资料高端MySQL DBA实训课程-最佳实践高级运维

八、MySQL复制最佳实践

2017-09-14  本文已影响460人  BruceLiu1
图片来自网络

文/Bruce.Liu1

文章大纲

  1. 复制概述
    1.1. 复制基本概念
    1.2. 常见复制结构
    1.3. 主从复制原理
    1.4. 主从复制模式
  2. 复制最佳实践
    2.1. 双主复制部署
    2.2. 分发、级联复制部署
    2.3. 分发、级联结构调整
    2.4. 复制常见错误处理
  3. 复制过滤应用
    3.1. 复制参数介绍
    3.2. 复制过滤最佳实践
    3.3. 复制状态、延迟监控
  4. 半同步复制
    4.1. 半同步复制原理
    4.2. 半同步复制最佳实践

1.复制概述

MySQL内建的复制功能是构建大型，高性能应用程序的基础。将MySQL的数据分布到多个系统上去，这种分布的机制，是通过将MySQL的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

1.1.复制基本概念

请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。

图片来自原创

复制的基本概念

复制解决的问题

1.2.常见复制结构

图片来自原创

1.3.主从复制原理

图片来自原创

主从复制原理

请思考：
1.这个复制结构是同步还是异步的?
2.复制结构中哪个环节容易出现性能瓶颈?

图片来自原创

SQL Thread应用流程(ROW格式)

1.4.主从复制模式

MySQL在5.6之前都是传统的复制方式，唯一不同的是记录的binary log格式有所不同，但5.6之后出现了全新的复制模式即：GTID模式，下面我们抽丝剥茧的一一道来。(配置一个主从，对binary log格式，逐一解释)

复制模式中传统可以基于任何的日志格式进行复制，但全新的GTID模式下只能基于row格式进行复制，见下图：(what is log format? 其实就是event写入binary log的形式而已)

binary log format

传统复制模式

GTID复制模式

在传统的复制模式下，主从切换后，就需要找到对应的bin log file以及position，基于这些信息指向新的主库，这对于不是很有经验的运维或者DBA来说，往往会出错最终造成主库同步错误。GTID的出现极大的解决了该问题的发生。

图片来自官方文档

每一个序号表示一个事务，当然事务可以包含一个或多个dml
server_uuid来自datadir目录的auto.cnf文件，官方不建议修改，但可以启动MySQL实例后删除，启动后MySQL实例，会自动生成(Master的DB，就不要给自己找刺激了)

2.复制最佳实践

图片来自原创

双主的缺陷

双主的实现

问题：
1.GTID环境下能否解决双边写入时，乱数据问题?
2.环形复制结构中，master会重复执行自己已执行的event吗?
3.为什么master1可以直接change master2上的任何Binlog File以及Binlog Position

双主最佳实践

log_bin
relay-log
binlog-format
server_id
log-slave-updates
gtid_mode #5.7后复制必须开启才参数
enforce-gtid-consistency

mysql> grant replication slave,replication client on *.* to 'repl'192.168.5.@'%' identified by 'mycat';
mysql> flush privileges;

# mysqldump -S /data1/db16000/my16000.sock -u root -p --single-transaction --master-data=2 -A > /tmp/full_16000_dump.sql
# scp /tmp/full_16000_dump.sql 192.168.5.192:/tmp

# mysql16000  < /tmp/full_16000_dump.sql
#解析full_16000_dump.sql备份文件的master1 binlog file、position
# head -50 /tmp/full_16000_dump.sql | grep CHANGE

mysql> change master to master_host='192.168.5.191',master_port=16000,master_user='repl',master_password='mycat',master_log_file='519116000-bin.000004',master_log_position=356;
mysql> start slave;
mysql> show slave status \G

mysql> show master status \G

mysql> change master to master_host='192.168.5.192',master_port=16000,master_user='repl',master_password='mycat',master_log_file='519216000-bin.000003',master_log_pos=504;
mysql> start slave;
mysql> show slave status \G;

2.2.分发、级联复制部署

分发复制结构

图片来自原创

实现原理

级联复制结构

图片来自原创

实现原理

2.3.分发、级联结构调整

2.3.1.复制结构调整场景一
图片来自原创 图片来自原创

实现思路

问题：两个slave drop table的时间点不一致，master在drop table的时候就说明slave之间停的不是同一个position

最佳实践

mysql> create table t_slave_stop (id int);

drop table t_slave_stop;

drop table t_slave_stop;

mysql> drop table t_slave_stop;

mysql> show master status \G;

mysql> stop slave;
mysql> change master to master_host='10.209.5.192',master_port=16000,master_log_file='519216000-bin.000003',master_log_pos=3638;
mysql> start slave;
mysql> show slave status \G

mysql> stop slave;
mysql> create table t_slave_stop (id int);
mysql> start slave;
mysql> show slave status \G
2.3.2.复制结构调整场景二
图片来自原创

实现思路

最佳实践

mysql> stop slave;
mysql> show slave status \G;

mysql> stop slave;
mysql> change master to master_host='10.209.5.191',master_port=16000,master_user='repl',master_password='mycat',master_log_file='519116000-bin.000005',master_log_pos=2961;
mysql> start slave;
mysql> show slave status \G;

mysql> start slave;
mysql> show slave status \G;

2.4.复制常见错误处理

2.4.1.配置类的错误处理
2.4.1.1.IO_Thread访问不到主库

Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'replica@192.168.5.191:16001' - retry-time: 60 retries: 1

2.4.1.2.主从字段类型不一致

Last_SQL_Errno: 1677
Last_SQL_Error: Column 1 of table 'test.t' cannot be converted from type 'int' to type 'bigint(20)'

2.4.1.3.MyISAM表损坏

Last_SQL_Errno: 1194
Last_SQL_Error: Error 'Table 'traincenter' is marked as crashed and should be repaired' on query. Default database: 'basketballman'. Query: 'update traincenter set points='4',pointstime='1361912066' where uid = '1847482697' limit 1'

2.4.1.4.从库找不到主库的bin-log

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

2.4.1.5.server-id冲突

Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

2.4.1.6.MyIASM表"事务"丢失

Last_Errno: 1053
Last_Error: Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: 'insert into ...

2.4.1.7.日志格式不统一

Last_SQL_Errno: 1666
Last_SQL_Error: Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'

2.4.1.8.slave_load_tmpdir空间不足

Last_Errno: 28
Last_Error: Error in Append_block event: write to '/tmp/SQL_LOAD-32343798-72213798-1.data' failed

2.4.2.数据冲突错误处理
mysql> CREATE TABLE `t_policy` (
         `id` int(11) NOT NULL AUTO_INCREMENT,
         `policy_name` VARCHAR(30) NOT NULL,
         `dimension` VARCHAR(255) NOT NULL,
         `active` VARCHAR(1) NOT NULL,
         `create_time` timestamp  DEFAULT NULL,
         `update_time` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
         PRIMARY KEY (`id`),
         KEY `IX_policy_name` (`policy_name`)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> insert into t_policy (policy_name,dimension,active,update_time) values ('AAA','1080x920',1,now()),('B','1080x920',2,now()),('B','1024x690',2,now()),('C','1024x768',3,now());
mysql> insert into t_policy (policy_name,dimension,active,update_time) values ('AAAA','4K',1,now()),('AAA','2K',1,now()),('AAA','1K',1,now()),('AAAAA','蓝光',1,now());
2.4.2.1.从库找记录不存在

Last_Errno: 1032
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 5 failed executing transaction '8117cf0f-96b9-11e7-b884-a0369f790658:31' at master log 519116000-bin.000005, end_log_pos 9324. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

1.从库删除一条记录

mysql> delete from t_policy where id = 8;

2.主库对删除的一条记录进行修改

mysql> update t_policy set dimension='蓝光v2'  where id = 8;

3.从库检查复制状态(复制已经断开)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.5.191
                  Master_User: repl
                  Master_Port: 16000
                Connect_Retry: 60
              Master_Log_File: 519116000-bin.000005
          Read_Master_Log_Pos: 9355
               Relay_Log_File: 519216000-relay-bin.000005
                Relay_Log_Pos: 6426
        Relay_Master_Log_File: 519116000-bin.000005
             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: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 5 failed executing transaction '8117cf0f-96b9-11e7-b884-a0369f790658:31' at master log 519116000-bin.000005, end_log_pos 9324. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 9023
              Relay_Log_Space: 7179
              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: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 5 failed executing transaction '8117cf0f-96b9-11e7-b884-a0369f790658:31' at master log 519116000-bin.000005, end_log_pos 9324. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 519116000
                  Master_UUID: 8117cf0f-96b9-11e7-b884-a0369f790658
             Master_Info_File: mysql.slave_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: 170913 15:13:24
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 8117cf0f-96b9-11e7-b884-a0369f790658:2-31
            Executed_Gtid_Set: 8117cf0f-96b9-11e7-b884-a0369f790658:1-30,
95ff34d2-96c0-11e7-850c-a0369f790bac:1-147
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

4.基于SQL thread停止的position解析主库的binlog

# mysqlbinlog -vv --base64-output=decode-rows 519116000-bin.000005 --start-position=9023 > /tmp/binlog

......  省略  ......
#170913 15:13:24 server id 519116000  end_log_pos 9324 CRC32 0x1a0f64c2         Update_rows: table id 221 flags: STMT_END_F
### UPDATE `mycat`.`t_policy`
### WHERE
###   @1=8 /* INT meta=0 nullable=0 is_null=0 */
###   @2='AAAAA' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
###   @3='languang' /* VARSTRING(765) meta=765 nullable=0 is_null=0 */
###   @4='1' /* VARSTRING(3) meta=3 nullable=0 is_null=0 */
###   @5=1505286407 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
###   @6=1505286487 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
### SET
###   @1=8 /* INT meta=0 nullable=0 is_null=0 */
###   @2='AAAAA' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
###   @3='蓝光v2' /* VARSTRING(765) meta=765 nullable=0 is_null=0 */
###   @4='1' /* VARSTRING(3) meta=3 nullable=0 is_null=0 */
###   @5=1505286407 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
###   @6=1505286804 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
# at 9324
......  省略  ......

5.生成数据启动从库的复制

mysql> insert into t_policy values (8,'AAAAA','languang','1',from_unixtime(1505286407),from_unixtime(1505286487));
mysql> start slave;
mysql> show slave status \G
2.4.2.2.从库主键冲突

Last_Errno: 1062
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 5 failed executing transaction '8117cf0f-96b9-11e7-b884-a0369f790658:32' at master log 519116000-bin.000005, end_log_pos 9619. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

1.从库写入一条数据

mysql> insert into t_policy values (9,'slave','768x1024',0,now(),now());

2.主库在写入一条数据

mysql> insert into t_policy (policy_name,dimension,active,update_time) values ('A+','2K',1,now());

3.从库查看复制状态

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.209.5.191
                  Master_User: repl
                  Master_Port: 16000
                Connect_Retry: 60
              Master_Log_File: 519116000-bin.000005
          Read_Master_Log_Pos: 9650
               Relay_Log_File: 519216000-relay-bin.000005
                Relay_Log_Pos: 6758
        Relay_Master_Log_File: 519116000-bin.000005
             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: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 5 failed executing transaction '8117cf0f-96b9-11e7-b884-a0369f790658:32' at master log 519116000-bin.000005, end_log_pos 9619. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 9355
              Relay_Log_Space: 7474
              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: 1062
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 5 failed executing transaction '8117cf0f-96b9-11e7-b884-a0369f790658:32' at master log 519116000-bin.000005, end_log_pos 9619. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 519116000
                  Master_UUID: 8117cf0f-96b9-11e7-b884-a0369f790658
             Master_Info_File: mysql.slave_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: 170913 15:47:40
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 8117cf0f-96b9-11e7-b884-a0369f790658:2-32
            Executed_Gtid_Set: 8117cf0f-96b9-11e7-b884-a0369f790658:1-31,
95ff34d2-96c0-11e7-850c-a0369f790bac:1-149
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

4.基于SQL thread停止的position解析主库的binlog

# mysqlbinlog -vv --base64-output=decode-rows 519116000-bin.000005 --start-position=9355 > /tmp/binlog

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 9355
#170913 15:47:40 server id 519116000  end_log_pos 9420 CRC32 0x8dac50fb         GTID    last_committed=34       sequence_number=35
SET @@SESSION.GTID_NEXT= '8117cf0f-96b9-11e7-b884-a0369f790658:32'/*!*/;
# at 9420
#170913 15:47:40 server id 519116000  end_log_pos 9501 CRC32 0x0cf2c142         Query   thread_id=317   exec_time=0     error_code=0
SET TIMESTAMP=1505288860/*!*/;
SET @@session.pseudo_thread_id=317/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 9501
#170913 15:47:40 server id 519116000  end_log_pos 9566 CRC32 0x73be9b73         Table_map: `mycat`.`t_policy` mapped to number 221
# at 9566
#170913 15:47:40 server id 519116000  end_log_pos 9619 CRC32 0x454a0acf         Write_rows: table id 221 flags: STMT_END_F
### INSERT INTO `mycat`.`t_policy`
### SET
###   @1=9 /* INT meta=0 nullable=0 is_null=0 */
###   @2='A+' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
###   @3='2K' /* VARSTRING(765) meta=765 nullable=0 is_null=0 */
###   @4='1' /* VARSTRING(3) meta=3 nullable=0 is_null=0 */
###   @5=NULL /* TIMESTAMP(0) meta=0 nullable=1 is_null=1 */
###   @6=1505288860 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
# at 9619
......  省略  ......

5.从库解决数据一致性问题，有两种处理方法：
a) 修改成基础数据，为stop position的事务提供基础变更环境
b) 修改成stop position的事务之后的数据，这样做需要跳过该事务
为了演示传统复制跳过错误，我们就通过跳过错误的方式处理问题

5.1.修复主从数据

mysql> delete from t_policy where id = 9;
mysql> insert into t_policy values (9,'A+','2K','1',NULL,from_unixtime(1505288860));

5.2.跳过复制错误
方法：解析binlog position，查看复制错误的当前事务的GTID序号，跳过即可
实现原理：在从库针对报错GTID的序号注入一个空事务，达到其跳过错误的目的

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

mysql> set gtid_next='8117cf0f-96b9-11e7-b884-a0369f790658:32';
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> set gtid_next='automatic';
Query OK, 0 rows affected (0.00 sec)

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

mysql> show slave status \G;
2.4.3.处理复制错误高阶方法
2.4.3.1.传统跳过复制错误
mysql> stop slave;
mysql> set sql_slave_skip_counter=1; #表示基于当前的binlog position之后允许跳过一个错误
mysql> start slave;
2.4.3.2.批量跳过复制错误

注意：该参数是只读参数，所以需要将参数配置到数据库中重启数据库后生效!
友情提示：数据追平后，记得把该参数关闭 !!!

3.复制过滤应用

图片来自原创

3.1.复制过滤应用

3.1.复制参数介绍

master

slave

3.2.复制过滤最佳实践

让从库只复制某个库或者某个表;让主库只记录某个库的变更日志(主库的颗粒度只能是库级别的);一般也用于拆库。

复制颗粒度
可以在主库上配置:只记录某个库的日志,支持反向(do ignore)
可以在从库上配置:只复制某个库或者是某个表的数据,支持反向(do ignore)

建议: 在从库上配置过滤,不要在主库上配置

3.2.1.复制过滤参数

master

思考下列问题：
示例一

binlog-do-db=db1
use db2;
update db1.tab1 set col2=2 where id = 1;

SBR/RBR会不会记录到日志中?
SBR:因为SBR下只作用于当前默认库,(use db2;) 所以不会记录复制的
RBR:因为RBR下是全局的,update中包含了db1.tab1 的数据,所以会记录

示例二

binlog-ignore-db=db1
use db2;
update db1.tab1 set col2=2 where id = 1;

SBR/RBR会不会记录到日志中?
SBR:因为SBR下只会忽略当前库是db1的,当前库是db2,所以是会记录复制内容的
RBR:RBR 在忽略的时候能够看到 update 更新是db1.tab1的数据 所以会忽略掉

slave

3.3.复制状态、延迟监控

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event  #io/thread连接Master时产生的等待事件
                  Master_Host: 192.168.1.1  #master主机
                  Master_User: replica  #复制账号
                  Master_Port: 3306  #master端口
                Connect_Retry: 60  #重试连接主库次数
              Master_Log_File: 113306-bin.032047  #IO thread读取Master的bin_log日志位置
          Read_Master_Log_Pos: 481887487  #IO thread读取Master的bin_log日志偏移量
               Relay_Log_File: 123306-relay-bin.052152  #SQL thread读取Raley_log中master的日志位置
                Relay_Log_Pos: 481651027  #SQL thread读取和执行的中继日志文件的名称
        Relay_Master_Log_File: 113306-bin.032047  #SQL thread读取和执行对应master的日志位置。
             Slave_IO_Running: Yes  #IO thread状态
            Slave_SQL_Running: Yes  #SQL thread状态
              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  #最近被使用SQL_SLAVE_SKIP_COUNTER的值
          Exec_Master_Log_Pos: 481650851  #SQL thread读取和执行对应master的日志偏移量
              Relay_Log_Space: 481886976  #原有的中继日志结合起来的总大小
              Until_Condition: None  #start slave语句的until子句中指定的值
               Until_Log_File:   #until子句终止时对应的binlog文件位置
                Until_Log_Pos: 0  #until子句终止时对应的binlog偏移量
           Master_SSL_Allowed: No  #是否允许SSL连接，下面是加密连接时的参数
           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  #IO thread错误代码
                Last_IO_Error:    #
               Last_SQL_Errno: 0  #SQL thread错误代码
               Last_SQL_Error:    #
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 113306
                  Master_UUID: a0f36bb1-fdbb-11e5-8413-a0369f7c3bb4
             Master_Info_File: mysql.slave_master_info  #在slave持久化master info的介质
                    SQL_Delay: 0  #延迟复制，单位是秒
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp:   IO thread错误时间戳
     Last_SQL_Error_Timestamp:  SQL thread错误时间戳
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: a0f36bb1-fdbb-11e5-8413-a0369f7c3bb4:11766950523-17011135306  #接收master的GTID
            Executed_Gtid_Set: 0921d44c-5d33-11e5-ad43-ecf4bbdee514:1-5946125770,  #已经执行过的GTID
a0f36bb1-fdbb-11e5-8413-a0369f7c3bb4:1-17011135306,
a6d9cf92-83bc-11e6-ade4-a0369f7c3f80:1-24
                Auto_Position: 1  #GTID模式复制

4. 半同步复制

4.1. 半同步复制概括及原理

MySQL默认的复制是属于异步同步。在mysql5.5之前，mysql复制是异步操作，主库和从库的数据之间存在一定的延迟，这样存在一个隐患，当主库上写入一个事物并提交成功，而从库尚未得到主库推送的binlog日志时，主库宕机了，例如主库可能因为磁盘损坏，内存故障等造成主库上该事务binlog丢失，此时，从库就可能损失这个事务，从而造成主从不一致；为了解决这个问题，mysql5.5引入了半同步复制机制，在mysql5.5之前的异步复制时，主库执行完commit提交操作后，在主库写入binlog日志后即成功返回客户端，无需等待binlog日志传送给从库。

4.1.1.MySQL复制架构
4.1.2.MySQL半同步原理

半同步复制时，为了保证主库上的每一个binlog事务都能够被可靠的复制到从库上，主库在每次事物成功提交时，并不及时反馈给前段应用用户，而是等待其中一个从库也接收到binlog事务并成功写入中继日志后，主库才返回commit操作成功给客户端。半同步复制保证了事务成功提交后，至少有两份日志记录，一份在主库的binlog，另一份在至少一个从库的中继日志relaylog上，从而更近一步保证了数据的完整性

4.1.2.1.官方半同步原理
图片来自原创

半同步缺陷
假设场景中客户端事务在存储引擎层提交后，在得到从库确认的过程中，主库宕机了，此时，可能的情况有两种：

4.1.2.2.社区增强版同步

社区增强半同步最早出现在社区中，后在官方5.7、MariaDB10.1被正式Release。

图片来自原创

4.2. 半同步复制最佳实践

作业

扫描下方二维码关注本人微信号！欢迎大家交流学习！

Bruce.Liu



# ###SQL thread读取Raley_log中Master的日志偏移量######

上一篇 下一篇

猜你喜欢

热点阅读