MySQL：13.基于GTID的主主复制

环境

第一步：安装 MySQL Server（by all）

第二步：修改配置文件

根据实际情况分别修改每台服务器以下值：server-id、auto_increment_increment、auto_increment_offset

cp /etc/my.cnf /etc/my.cnf.bak
cat <<\EOF >/etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

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

# 设置服务器编码
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
# 跳过mysql程序起动时的字符参数设置 ，使用服务器端字符集设置
skip-character-set-client-handshake

# 最大数据包
max_allowed_packet=200M

# 不区分大小写
lower_case_table_names= 1

# 最大连接数
max_connections = 20000
# 最大错误连接数
max_connect_errors = 3000
# https://www.cnblogs.com/mydriverc/p/8296814.html
# show variables like 'back_log';

# 禁止DNS查询
skip-name-resolve

# 解决errro_logs时间戳与linux系统时间不一致问题
log_timestamps = SYSTEM

# mysql总共能够打开的文件的数量
# show variables like 'open%';
#open_files_limit= 59000

# STRICT_TRANS_TABLES：在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做任何限制
# NO_AUTO_CREATE_USER：防止GRANT自动创建新用户，除非还指定了密码。
# NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# 确保默认存储引擎是InnoDB
# Galera Cluster不能与MyISAM或类似的非事务存储引擎一起使用
default_storage_engine=InnoDB

# InnoDB缓冲池 建议为物理内存的3/4或4/5
innodb_buffer_pool_size = 5G

open_files_limit= 30000
innodb_open_files=60000
innodb_lock_wait_timeout=500
back_log = 500
key_buffer_size = 124M
read_buffer_size = 16M
read_rnd_buffer_size = 2M

# 打开慢日志查询
slow_query_log = 1
# 设置慢查询阈值
long_query_time = 2

## 从这里往上为 MySQL 优化相关
## 从这里往下为开启 GTID 的必须项和推荐项目
## 指定 Server-ID（要求所有服务器唯一）
server-id=141
# 步进值auto_imcrement。一般有n台主MySQL就填n
auto_increment_increment=2
#起始值。一般填第n台主MySQL。
auto_increment_offset=1
### 开启二进制日志
log-bin=mysql-bin
### 基于行
binlog_format=row
## 如果 binlog_format=ROW 会将执行的SQL语句记录到日志
binlog_rows_query_log_events=1
### 保留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
## 安全的过滤掉某些不需要同步的表或者库
## 避免在主服务器使用 binlog-do-db 和 binlog-ignore-db
## 原因详见：https://www.percona.com/blog/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous/
## replicate-wild-ignore-table=mysqlslap.%
EOF

第三步：设置主从同步

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;

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

2.从库恢复

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

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

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

3.两台服务器分别执行以下语句

IP 地址需要修改为对方服务器的 IP

## 由于恢复出 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;

4.两台服务器分别启动 Slave 复制线程

start slave;
show slave status\G

5.测试数据

在两台服务器上分别写入数据，到另外服务器查看数据是否已同步

6.测试性能

## 读性能
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