配置mysql基于gtid主主复制架构
环境：
操作系统 centos7.5 x86_64
mysql版本：Percona-Server-5.7.21-21
测试环境：
node1 10.11.0.210
node2 10.11.0.212
一、二进制安装percona-mysql5.7
[root@node5 data]# tar -zxf Percona-Server-5.7.21-21-Linux.x86_64.ssl101.tar.gz
[root@node5 data]# mv Percona-Server-5.7.21-21-Linux.x86_64.ssl101 percona-server-5.7.21-21
[root@node5 data]# mv percona-server-5.7.21-21 /usr/local/
[root@node5 local]# cd /usr/local/
[root@node5 local]# ln -s percona-server-5.7.21-21 mysql
# 添加用户
useradd -u 501 -G users -s /sbin/nologin -M -d /usr/local/mysql/bin mysql
mkdir -p /data/mysql_data
# 编辑配置
vim /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
# The MySQL server
[mysqld]
user = mysql
port = 3306
bind-address = 0.0.0.0
socket = /tmp/mysql.sock
datadir = /data/mysql_data
pid-file = /data/mysql_data/mysql.pid
skip-external-locking
#memory is 16G
#key_buffer_size = 16M
key_buffer_size = 32M
#table_open_cache = 64
table_open_cache = 1024
innodb_open_files = 450
#sort_buffer_size = 512K
sort_buffer_size = 2M
#net_buffer_length = 4K
net_buffer_length = 32K
#read_buffer_size = 256K
read_buffer_size = 2M
#read_rnd_buffer_size = 4M
read_rnd_buffer_size = 8M
#myisam_sort_buffer_size = 4M
myisam_sort_buffer_size = 32M
thread_cache_size = 800
query_cache_size = 32M
query_cache_type = 1
max_write_lock_count = 300
skip-name-resolve
wait_timeout = 120
interactive_timeout = 120
max_connections = 400
max_connect_errors = 10000
max_allowed_packet = 320M
back_log = 1024
log_timestamps = system
sync_binlog = 1
#当链接数耗尽后，通过设置别用端口，让root可以登录
extra_max_connections = 2
extra_port = 13306
##让mysql不区分大小写敏感
lower_case_table_names = 1
character_set_server = utf8mb4
performance_schema = ON
#for FULLTEXT index , if your progrom used fulltext index please change the value your want.
#ft_min_word_len = 1
#ft_max_work_len = 10
slave-skip-errors = 1062,1032
#if the query is exec time great than 2 seconds, the query will log to slow log if slowlog is enabled.
long_query_time = 0.5
slow_query_log = on
slow-query-log-file = /data/mysql_data/slow.log
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
log-bin = mysql-bin
expire_logs_days = 8
log_error = error.log
log_warnings = 1
# binary logging format - mixed recommended
binlog_format = row
#binlog_format=mixed
relay-log = mysql-relay-bin
auto-increment-increment = 2
auto-increment-offset = 1
skip_slave_start
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
#server-id = 9662945782
server-id = 210
sql-mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
#sync_binlog = 2
##### Replication #####
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = sys
replicate-ignore-db = undolog
replicate-ignore-db = for_nagios
replicate_wild_ignore_table = mysql.%
replicate_wild_ignore_table = information_schema.%
replicate_wild_ignore_table = performance_schema.%
replicate_wild_ignore_table = sys.%
log-slave-updates
#skip-slave-start
#skip-grant-tables
###rds-ecs，此处配置根据rds的配置来进行设置
innodb_data_file_path = ibdata1:200M:autoextend
innodb_log_files_in_group = 2
innodb_log_file_size = 1572864000
master-info-repository = TABLE ###Slave配置需要
relay-log-info_repository = TABLE ###Slave配置需要
binlog-format =ROW ####Slave配置需要
gtid-mode = on ###开启GTID需要
enforce-gtid-consistency = true ###开启GTID需要
slave-sql-verify-checksum = 1
relay-log-index = relay-log-index
relay-log-recovery = ON
slave-sql-verify-checksum = 1
#innodb_fast_checksum = false
#innodb_page_size = 16384
#innodb_log_block_size = 512
#innodb_checksum_algorithm = crc32
#innodb_log_checksum_algorithm = strict_crc32
#####
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /data/mysql_data
#innodb_data_file_path = ibdata1:1G;ibdata2:1G:autoextend
innodb_log_group_home_dir = /data/mysql_data
#innodb_undo_directory = /data/mysql_data/undolog/
#innodb_undo_logs = 128
#innodb_undo_tablespaces = 3
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
#innodb_additional_mem_pool_size = 8M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
#innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 30
innodb_file_per_table = 1
innodb_thread_concurrency = 4
innodb_max_dirty_pages_pct = 75
innodb_flush_method = O_DIRECT
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
thread_pool_size = 8
thread_handling = pool-of-threads
thread_pool_oversubscribe = 40
thread_pool_stall_limit = 100
thread_pool_max_threads = 60
#解释： 在启动时把热数据加载到内存。
innodb_buffer_pool_load_at_startup = 1
##解释： 在关闭时把热数据dump到本地磁盘
innodb_buffer_pool_dump_at_shutdown = 1
[mysqldump]
quick
max_allowed_packet = 320M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
#key_buffer_size = 20M
#sort_buffer_size = 20M
key_buffer_size = 200M
sort_buffer_size = 200M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
将mysql命令加入环境变量中
vim /etc/profile
export PATH=/usr/local/mysql/bin:$PATH
[root@node02 local]# source /etc/profile
# 修改权限
chown -R mysql.mysql /data/mysql_data
初始化MySQL数据库的数据文件路径，并且创建系统表，5.7.6及以上版本，要使用mysqld来初始化数据库
[root@node5 local]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql
[root@node5 local]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
[root@node5 local]# chmod +x /etc/init.d/mysql
修改启动脚本
vim /etc/init.d/mysql
basedir=/usr/local/mysql
datadir=/data/mysql_data
启动mysql
/etc/init.d/mysql start
设置root密码，默认密码为空
mysql> set password = password('root');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
三、通过gtid的方式配置mysql主主复制
node1配置片段：
# server-id不能相同
server-id = 210
# 步长
auto-increment-increment = 2
# 起始值
auto-increment-offset = 1
#不要自动启用slave
skip_slave_start
log-slave-updates = ON
master-info-repository = TABLE ###Slave配置需要
relay-log-info_repository = TABLE ###Slave配置需要
binlog-format =ROW ####Slave配置需要
gtid-mode = on ###开启GTID需要
enforce-gtid-consistency = true ###开启GTID需要
binlog-checksum = CRC32
master-verify-checksum = 1
node2配置片段：
# server-id不能相同
server-id = 212
# 步长
auto-increment-increment = 2
# 起始值不能相同
auto-increment-offset = 2
#不要自动启用slave
skip_slave_start
log-slave-updates = ON
master-info-repository = TABLE ###Slave配置需要
relay-log-info_repository = TABLE ###Slave配置需要
binlog-format =ROW ####Slave配置需要
gtid-mode = on ###开启GTID需要
enforce-gtid-consistency = true ###开启GTID需要
binlog-checksum = CRC32
master-verify-checksum = 1
说明：
auto_increment_offset表示自增长字段从那个数开始，他的取值范围是1 .. 65535
auto_increment_increment表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535
# node1和node2上添加复制的账户
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'10.11.0.%' IDEnTIFIED BY 'replpass';
FLUSH PRIVILEGES;
查看主库与从库的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 | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
mysql> show variables like '%gtid_next%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| gtid_next | AUTOMATIC |
+---------------+-----------+
1 row in set (0.00 sec)
查看服务器server_uuid
mysql> show variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | ea04f6f3-631c-11e8-9eac-000c29ff3eec |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
查看主服务器状态，如果Executed_Gtid_Set字段为空，则可能配置错误需要自行检查配置
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| mysql-bin.000003 | 1365 | | | 0fae39d0-6f7f-11e8-86ee-000c29ff3eec:1-4,
b9d0f27b-6f80-11e8-87ae-000c292cb7df:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
配置node1连接至主库node2
mysql> CHANGE MASTER TO MASTER_HOST='10.11.0.212',MASTER_USER='repl',MASTER_PASSWORD='replpass',MASTER_AUTO_POSITION=1;
在从服务器上启动复制
mysql> START SLAVE;
启动成功后查看SLAVE的状态
配置node2连接至主库node1
mysql> CHANGE MASTER TO MASTER_HOST='10.11.0.210',MASTER_USER='repl',MASTER_PASSWORD='replpass',MASTER_AUTO_POSITION=1;
在从服务器上启动复制
mysql> START SLAVE;
启动成功后查看SLAVE的状态
mysql> SHOW SLAVE STATUS\G
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
确认 Slave_IO_Running 和 Slave_SQL_Running 两个参数都为 Yes 状态。
测试GTID主主复制
在主库(node1)实例创建一些数据，node2能够正常识别并且是隔一个数依次增加的
mysql> create database wanxing;
Query OK, 1 row affected (0.01 sec)
mysql> use wanxing;
Database changed
mysql> create table users(id int primary key auto_increment, name varchar(30));
Query OK, 0 rows affected (0.01 sec)
mysql> select * from users;
+----+------+
| id | name |
+----+------+
| 2 | jack |
| 4 | tom |
+----+------+
2 rows in set (0.00 sec)
mysql> insert into users(name) values('lucy'),('lily');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from users;
+----+------+
| id | name |
+----+------+
| 2 | jack |
| 4 | tom |
| 5 | lucy |
| 7 | lily |
+----+------+
4 rows in set (0.00 sec)
mysql> insert into users(name) values('aa'),('bb');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from users;
+----+------+
| id | name |
+----+------+
| 2 | jack |
| 4 | tom |
| 5 | lucy |
| 7 | lily |
| 9 | aa |
| 11 | bb |
+----+------+
6 rows in set (0.00 sec)
