MySQL主从复制（基于GTID）

拓扑结构：（一主多从）

Mysql-master01  (192.168.43.123)

Mysql-slave01     (192.168.43.124)

Mysql-slave02     (192.168.43.125)

版本信息：

OS版本      ： CentOS release 6.10 最小化安装

Mysql版本： mysql-5.7.31-1.el6.x86_64.rpm-bundle.tar

一、系统调整（测试）

适用范围：所有服务器

1.关闭防火墙

2.修改/etc/selinux/config关闭selinux

3.其他工具软件的安装 vim openssh-* net-tools 等

二、Mysql的安装

适用范围：所有服务器

1.安装

[root@localhost opt]# tar -xf mysql-5.7.31-1.el6.x86_64.rpm-bundle.tar

[root@localhost opt]# yum install mysql-community-* -y

2.root密码调整

[root@localhost opt]# /etc/init.d/mysqld start

Initializing MySQL database:                              [  OK  ]

Starting mysqld:                                          [  OK  ]

[root@localhost opt]#

mysql> alter user 'root'@'localhost' identified by 'Test.123';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

三、配置主从复制

1.配置调整

主服务器配置：

mysql> system cat /etc/my.cnf

[mysqld]

log-bin=/var/log/mysql/mysql-bin

server-id=1

innodb_flush_log_at_trx_commit = 1

sync_binlog = 1

gtid_mode=ON

enforce_gtid_consistency=1

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

default-storage-engine = INNODB

character-set-server = utf8

collation-server = utf8_general_ci

[client]

default-character-set = utf8

从服务器配置：

[root@localhost mysql]# cat /etc/my.cnf

[mysqld]

log-bin=/var/log/mysql/mysql-bin

server-id=2

innodb_flush_log_at_trx_commit = 1

sync_binlog = 1

gtid_mode=ON

enforce_gtid_consistency=1

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

default-storage-engine = INNODB

character-set-server = utf8

collation-server = utf8_general_ci

[client]

default-character-set = utf8

[root@localhost opt]# /etc/init.d/mysqld restart

Stopping mysqld:                                          [  OK  ]

Starting mysqld:                                          [  OK  ]

2.复制用户创建（主服务器配置）

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' identified by 'Test.123';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql>

从服务器测试一下：

[root@localhost opt]# mysql -urepl -p'Test.123' -h 192.168.43.123

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.7.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

3.将从服务器加入新服务器（2台从服务器）

mysql>  FLUSH TABLES WITH READ LOCK;

Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='192.168.43.123', MASTER_USER='repl', MASTER_PASSWORD='Test.123', MASTER_AUTO_POSITION=1;

Query OK, 0 rows affected, 2 warnings (0.03 sec)

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.43.123

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000002

          Read_Master_Log_Pos: 154

              Relay_Log_File: localhost-relay-bin.000002

                Relay_Log_Pos: 367

        Relay_Master_Log_File: mysql-bin.000002

            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: 154

              Relay_Log_Space: 578

              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

                  Master_UUID: 04baf52c-e083-11ea-9564-000c29298862

            Master_Info_File: /var/lib/mysql/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:

                Auto_Position: 1

        Replicate_Rewrite_DB:

                Channel_Name:

          Master_TLS_Version:

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> unlock tables;

Query OK, 0 rows affected (0.01 sec)

四、测试

主服务器操作：

mysql> create table test(id int primary key ,name varchar(20));

Query OK, 0 rows affected (0.03 sec)

mysql> insert into test(id,name) values(1,'A'),(2,'B'),(3,'C');

Query OK, 3 rows affected (0.03 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql>

mysql> select * from test;

+----+------+

| id | name |

+----+------+

|  1 | A    |

|  2 | B    |

|  3 | C    |

+----+------+

3 rows in set (0.00 sec)

从服务器查询：

mysql> show slave status\G;

*************************** 1. row ***************************

              Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.43.123

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000002

          Read_Master_Log_Pos: 781

              Relay_Log_File: localhost-relay-bin.000002

                Relay_Log_Pos: 994

        Relay_Master_Log_File: mysql-bin.000002

            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: 781

              Relay_Log_Space: 1205

              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

                  Master_UUID: 04baf52c-e083-11ea-9564-000c29298862

            Master_Info_File: /var/lib/mysql/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: 04baf52c-e083-11ea-9564-000c29298862:1-3

            Executed_Gtid_Set: 04baf52c-e083-11ea-9564-000c29298862:1-3

                Auto_Position: 1

        Replicate_Rewrite_DB:

                Channel_Name:

          Master_TLS_Version:

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> show databases;

+--------------------+

| Database          |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

| test              |

| testrep            |

+--------------------+

6 rows in set (0.00 sec)

mysql> use test

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

mysql> select * from test;

+----+------+

| id | name |

+----+------+

|  1 | A    |

|  2 | B    |

|  3 | C    |

+----+------+

3 rows in set (0.00 sec)

五、日志分析

[root@localhost mysql]# ls

mysql-bin.000001  mysql-bin.000002  mysql-bin.index

说明：mysql-bin.000001为未开启GTID的binlog，mysql-bin.000002为开启GTID的binlog

[root@localhost mysql]# mysqlbinlog --no-defaults --base64-output=decode-rows -v  mysql-bin.000001 --result-file=1.sql

[root@localhost mysql]# mysqlbinlog --no-defaults --base64-output=decode-rows -v  mysql-bin.000002 --result-file=2.sql

[root@localhost mysql]# vim 2.sql

[root@localhost mysql]# vim 1.sql

[root@localhost mysql]# cat  1.sql  | grep 'SET @@SESSION.GTID_NEXT='

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

[root@localhost mysql]# cat  2.sql  | grep 'SET @@SESSION.GTID_NEXT='

SET @@SESSION.GTID_NEXT= '04baf52c-e083-11ea-9564-000c29298862:1'/*!*/;

SET @@SESSION.GTID_NEXT= '04baf52c-e083-11ea-9564-000c29298862:2'/*!*/;

SET @@SESSION.GTID_NEXT= '04baf52c-e083-11ea-9564-000c29298862:3'/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

[root@localhost mysql]#

GTID的生命周期介绍：

1.事务提交时会获取到一个GTID，该GTID将位于事务前面一同被记录到二进制日志中。若事务不被记录到二进制日志中，它也不会获取到GTID。

2.事务的GTID会以Gtid_log_event的形式记录到二进制日志，当二进制日志轮换或实例关闭时，MySQL会将所有已写入二进制日志的事务的GTID刷新到系统表mysql.gtid_executed。

{

这里说明一下gtid_executed表的刷新机制：

【1】主库上mysql.gtid_executed也是会进行更新，开启了binlog后，会在binlog rotation时进行更新(比如执行：flush logs;)。

【2】备库上mysql.gtid_executed表的更新情况主要取决于log_slave_updates参数。log_slave_updates = ON，不会每个事务进行实时更新，而是在binary log rotation的时候会更新并进行压缩。log_slave_updates = OFF，备库上的mysql.gtid_executed会每一个事务进行更新。并且，此时mysql.gtid_executed表的压缩取决于executed_gtids_compression_period，默认为1000个事务进行压缩。

【3】MySQL 5.6版本开启GTID模式，必须打开参数log_slave_updates，简单来说就是必须在从机上再记录一份二进制日志。这样的无论对性能还是存储的开销，无疑会相应的增大。而从MySQL 5.7.5版本开始无需在GTID模式下启用参数log_slave_updates，而是将GTID值持久化保存在一张InnoDB表中，并与用户事务一起进行提交，从而实现数据的一致性。

【4】关于log_slave_updates 参数

Normally, a slave does not log to its own binary log any updates that are received from a master server.This option tells the slave to log the updates performed by its SQL thread to its own binary log.For this option to have any effect, the slave must also be started with the –log-bin option to enable binary logging.Prior to MySQL 5.5, the server would not start when using the –log-slave-updates option without also startingthe server with the –log-bin option, and would fail with an error; in MySQL 5.5, only a warning is generated.

(Bug #44663) –log-slave-updates is used when you want to chain replication servers.For example, you might want to set up replication servers using this arrangement: A -> B -> CHere, A serves as the master for the slave B, and B serves as the master for the slave C.For this to work, B must be both a master and a slave. You must start both A and B with –log-bin to enable binary logging,and B with the –log-slave-updates option so that updates received from A are logged by B to its binary log.

}

3.事务提交后，极短时间内其GTID会被加入到一个GTID集合中（注意该过程不具有原子性），即全局系统变量gtid_executed，它代表全部已提交了的事务的GTID。在复制关系中，它也表明了该从实例已执行过哪些事务，主节点会据此判断还需发送哪些GTID。

4.当二进制日志传送到从节点转储为中继日志后，从节点会读取GTID，在会话级别将该GTID赋值给会话系统变量gtid_next，这也就告诉了从节点接下来要执行的事务的GTID是什么。

5.从节点会确认会话系统变量gtid_next中的GTID没被占用，然后为其分配一个线程，并使用全局系统变量gtid_owned（@@GLOBAL.gtid_owned）标识出该GTID和使用它的线程的ID。若该GTID已被使用，就忽略该事务。

6.若此GTID没被使用，从节点会回放该事务。从节点会使用会话系统变量gtid_next中的GTID，作为该事务的GTID，而不会为其重新生成GTID。

7.若从节点开启了二进制日志，该GTID会以Gtid_log_event的形式和事务一同记录进二进制日志。当二进制日志轮换或实例关闭时，所有已提交事务的GTID又会被刷新到系统表mysql.gtid_executed（同2）。

8.若从节点没开启二进制日志，MySQL会将回放该事务和将其GTID直接写入系统表mysql.gtid_executed作为一个事务进行，但在MySQL 5.7版本这还仅限于DML语句，在8.0版本，DDL也可以了。

9.当事务在从节点回放后，从节点上全局系统变量gtid_executed的变化同3。

附：多线程复制

在5.6中被引入，并且在5.7中得到了进一步的完善，5.7中是基于逻辑时钟的方式进行的多线程复制。

参阅：

https://www.cnblogs.com/f-ck-need-u/p/9164823.html

©著作权归作者所有,转载或内容合作请联系作者
禁止转载，如需转载请通过简信或评论联系作者。
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 199,902评论 5赞 468
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 84,037评论 2赞 377
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 146,978评论 0赞 332
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 53,867评论 1赞 272
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 62,763评论 5赞 360
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,104评论 1赞 277
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,565评论 3赞 390
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,236评论 0赞 254
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,379评论 1赞 294
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,313评论 2赞 317
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,363评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,034评论 3赞 315
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,637评论 3赞 303
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,719评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,952评论 1赞 255
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,371评论 2赞 346
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 41,948评论 2赞 341