MySQL：12.基于GTID的主从复制

环境

[root@gtid2]# cat /etc/centos-release
CentOS Linux release 7.6.1810 (Core)

[root@gtid2]# mysql -V
mysql  Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using  EditLine wrapper

第零步：安装 MySQL（by all）

## 安装
yum install -y mysql-community-client-5.7.25-1.el7.x86_64.rpm \
mysql-community-common-5.7.25-1.el7.x86_64.rpm \
mysql-community-libs-5.7.25-1.el7.x86_64.rpm \
mysql-community-libs-compat-5.7.25-1.el7.x86_64.rpm \
mysql-community-server-5.7.25-1.el7.x86_64.rpm

## 启动
systemctl enable mysqld
systemctl stop mysqld
systemctl start mysqld
systemctl status mysqld

## 初始化
grep 'temporary password' /var/log/mysqld.log
mysql -u root -p

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Xiaoliu123!';

第一步：修改 my.cnf 配置文件（by all）

注意修改 server-id，要求两台服务器不能一样

cp /etc/my.cnf /etc/my.cnf.bak
vim /etc/my.cnf
## 指定 Server-ID
server-id=101
## 开启二进制日志
log-bin=mysql-bin
## 基于行
binlog_format=row
## 保留2天自动删除
expire_logs_days=2
## 开启 GTID
gtid-mode=ON
## 启用后，服务器通过仅允许执行可使用GTID安全记录的语句来强制执行GTID一致性
## 仅在语句进行二进制日志记录时才生效
## 如果禁用了二进制日志记录，或者过滤器删除了语句而未将语句写入二进制日志，则不会对未记录的语句检查或强制执行GTID一致性
enforce-gtid-consistency=ON
## 将从主库同步数据写入 binlog
log-slave-updates
## 启用并行复制
slave_parallel_type=logical_clock

第二步：配置并开始同步

## 查看是否开启 GTID
show variables like "%gtid%";

## 查看服务器 UUID
show global variables like '%uuid%';
cat /var/lib/mysql/auto.cnf

## 查看当前二进制日志名称
SHOW MASTER STATUS;
## 查看二进制日志列表
SHOW BINARY LOGS;
SHOW BINLOG EVENTS;
SHOW SLAVE HOSTS;

1.创建复制用账号（主服务器）

grant replication slave on *.* to 'slave'@'192.168.30.%' identified by 'Xiaoliu123!';
select user,host from mysql.user;

2.连接到主服务器（从服务器）

## 连接到主服务器
CHANGE MASTER TO \
        MASTER_HOST='192.168.30.102',\
        MASTER_PORT =3360,\
        MASTER_USER='slave',\
        MASTER_PASSWORD='Xiaoliu123!',\
        MASTER_AUTO_POSITION=1;

## 开启复制
START SLAVE;

## 再次查看主从状态
show master status;
show slave status\G

第三步：测试

1.测试数据是否同步

## 主库创建数据库并写入数据
CREATE DATABASE galeratest;
USE galeratest;
CREATE TABLE test_table (
        id INT PRIMARY KEY AUTO_INCREMENT,
        msg TEXT ) ENGINE=InnoDB;
INSERT INTO test_table (msg)
        VALUES ("Hello my dear cluster.");
INSERT INTO test_table (msg)
        VALUES ("Hello, again, cluster dear.");

select * from galeratest.test_table;

## 登录从库查看是否复制成功
select * from galeratest.test_table;

2.测试性能

## 读性能
mysqlslap -uroot -pXiaoliu123!  \
    --auto-generate-sql \
    --auto-generate-sql-load-type=read \
    --number-char-cols=10 \
    --number-int-cols=5 \
    --number-of-queries=10000 \
    --concurrency=300 \
    --iterations=5

## 写性能
mysqlslap -uroot -pXiaoliu123!  \
    --auto-generate-sql \
    --auto-generate-sql-load-type=write \
    --number-char-cols=10 \
    --number-int-cols=5 \
    --number-of-queries=10000 \
    --concurrency=300 \
    --iterations=5

第四步：GTID与备份恢复

1.备份

在做备份恢复的时候，有时需要恢复出来的 MySQL 实例可以作为从库连上原来的主库继续复制，这就要求从备份恢复出来的 MySQL 实例拥有和主数据库数据一致的 gtid_executed 值。这也是通过设置 gtid_purged 实现的，下面看下 mysqldump 做备份的例子。

这里使用 --all-databases选项是因为基于 GTID 的复制会记录全部的事务, 所以要构建一个完整的dump。

mysqldump \
--all-databases \
--single-transaction  \
--triggers \
--routines \
--events \
--host=127.0.0.1 \
--port=3306 \
--user=root \
-p000000 > dump.sql

        ## 生成的 dump.sql 文件里包含了设置 gtid_purged 的语句
        SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
        SET @@SESSION.SQL_LOG_BIN= 0;
        ...
        SET @@GLOBAL.GTID_PURGED='f75ae43f-3f5e-11e7-9b98-001c4297532a:1-14:20';
        ...
        SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

2.从库恢复

## 恢复迁需要先清空``gtid_executed`` 变量
reset master

## 执行恢复
mysql -uroot -p123456 < /home/dump.sql

## 查看恢复出实例的 GTID_EXECUTED 是否和主库一致
show master status;

## 由于恢复出 MySQL 实例已经被设置了正确的 GTID_EXECUTED
## 下面以 master_auto_postion = 1 的方式 CHANGE MASTER 到原来的主节点即可开始复制。
CHANGE MASTER TO MASTER_HOST='192.168.2.210', \
                MASTER_PORT =3360, \
                MASTER_USER='repl', \
                MASTER_PASSWORD='000000', \
                MASTER_AUTO_POSITION = 1;

## 如果不希望备份文件中生成设置 ``GTID_PURGED`` 的 SQL，可以给 mysqldump传入 ``--set-gtid-purged=OFF`` 关闭。

附录：注意事项

参考：https://www.hi-linux.com/posts/47176.html

1.enforce_gtid_consistency 强制 GTID 一致性, 启用后以下命令无法再使用。

## 因为实际上是两个独立事件，所以只能将其拆分。先建立表，然后再把数据插入到表中。
create table … select …
    mysql> create table test2 select * from test1;
    ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

2.事务内部不能创建临时表

同一事务中不能同时更新事务表与非事务表(MyISAM)，建议都选择 Innodb 作为默认的数据库引擎。

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

    mysql> create temporary table test2(id int);
    ERROR 1787 (HY000): Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context.  These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.

    ## 同一事务中不能同时更新事务表与非事务表(MyISAM)，建议都选择 Innodb 作为默认的数据库引擎。
    mysql> CREATE TABLE `test_innodb` (id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT);
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> CREATE TABLE `test_myisam` (id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT)   ENGINE = `MyISAM`;
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> insert into test_innodb(id) value(1);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into test_myisam(id) value(1);
    ERROR 1785 (HY000): Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.