配置mysql基于gtid主主复制架构 环境：
操作系统 centos7. x86_64
mysql版本：Percona-Server-5.7.- 测试环境：
node1 10.11.0.210
node2 10.11.0.212 一、二进制安装percona-mysql5. [root@node5 data]# tar -zxf Percona-Server-5.7.--Linux.x86_64.ssl101.tar.gz
[root@node5 data]# mv Percona-Server-5.7.--Linux.x86_64.ssl101 percona-server-5.7.-
[root@node5 data]# mv percona-server-5.7.- /usr/local/
[root@node5 local]# cd /usr/local/
[root@node5 local]# ln -s percona-server-5.7.- mysql # 添加用户
useradd -u -G users -s /sbin/nologin -M -d /usr/local/mysql/bin mysql mkdir -p /data/mysql_data # 编辑配置
vim /etc/my.cnf [client]
port =
socket = /tmp/mysql.sock # The MySQL server
[mysqld]
user = mysql
port =
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 =
table_open_cache =
innodb_open_files =
#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 =
query_cache_size = 32M
query_cache_type =
max_write_lock_count =
skip-name-resolve
wait_timeout =
interactive_timeout =
max_connections =
max_connect_errors =
max_allowed_packet = 320M
back_log =
log_timestamps = system sync_binlog =
#当链接数耗尽后，通过设置别用端口，让root可以登录
extra_max_connections =
extra_port =
##让mysql不区分大小写敏感
lower_case_table_names = 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 =
#ft_max_work_len =
slave-skip-errors = ,
#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 =
log_error = error.log
log_warnings =
# binary logging format - mixed recommended
binlog_format = row
#binlog_format=mixed
relay-log = mysql-relay-bin
auto-increment-increment =
auto-increment-offset =
skip_slave_start # required unique id between and ^ -
# defaults to if master-host is not set
# but will not function as a master if omitted
#server-id =
server-id =
sql-mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
#sync_binlog = ##### 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 =
innodb_log_file_size = 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 =
relay-log-index = relay-log-index
relay-log-recovery = ON
slave-sql-verify-checksum = #innodb_fast_checksum = false
#innodb_page_size =
#innodb_log_block_size = #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 =
#innodb_undo_tablespaces = # You can set .._buffer_pool_size up to - %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances =
#innodb_additional_mem_pool_size = 8M # Set .._log_file_size to % of buffer pool size
#innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
#innodb_log_files_in_group =
innodb_flush_log_at_trx_commit =
innodb_lock_wait_timeout =
innodb_file_per_table =
innodb_thread_concurrency =
innodb_max_dirty_pages_pct =
innodb_flush_method = O_DIRECT innodb_purge_threads =
innodb_large_prefix =
innodb_read_io_threads =
innodb_write_io_threads =
innodb_io_capacity =
innodb_io_capacity_max = thread_pool_size =
thread_handling = pool-of-threads
thread_pool_oversubscribe =
thread_pool_stall_limit =
thread_pool_max_threads = #解释： 在启动时把热数据加载到内存。
innodb_buffer_pool_load_at_startup = ##解释： 在关闭时把热数据dump到本地磁盘
innodb_buffer_pool_dump_at_shutdown = [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, rows affected, warning (0.00 sec) mysql> flush privileges; 三、通过gtid的方式配置mysql主主复制 node1配置片段：
# server-id不能相同
server-id =
# 步长
auto-increment-increment =
# 起始值
auto-increment-offset =
#不要自动启用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 = node2配置片段：
# server-id不能相同
server-id =
# 步长
auto-increment-increment =
# 起始值不能相同
auto-increment-offset =
#不要自动启用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 = 说明：
auto_increment_offset表示自增长字段从那个数开始，他的取值范围是1 ..
auto_increment_increment表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. # 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 | |
| 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 |
+---------------+-----------+
row in set (0.00 sec) 查看服务器server_uuid
mysql> show variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | ea04f6f3-631c-11e8-9eac-000c29ff3eec |
+---------------+--------------------------------------+
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. | | | | 0fae39d0-6f7f-11e8-86ee-000c29ff3eec:-,
b9d0f27b-6f80-11e8-87ae-000c292cb7df:- |
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+ 配置node1连接至主库node2
mysql> CHANGE MASTER TO MASTER_HOST='10.11.0.212',MASTER_USER='repl',MASTER_PASSWORD='replpass',MASTER_AUTO_POSITION=;
在从服务器上启动复制
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=;
在从服务器上启动复制
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, row affected (0.01 sec) mysql> use wanxing;
Database changed
mysql> create table users(id int primary key auto_increment, name varchar());
Query OK, rows affected (0.01 sec) mysql> select * from users;
+----+------+
| id | name |
+----+------+
| | jack |
| | tom |
+----+------+
rows in set (0.00 sec) mysql> insert into users(name) values('lucy'),('lily');
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: mysql> select * from users;
+----+------+
| id | name |
+----+------+
| | jack |
| | tom |
| | lucy |
| | lily |
+----+------+
rows in set (0.00 sec) mysql> insert into users(name) values('aa'),('bb');
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: mysql> select * from users;
+----+------+
| id | name |
+----+------+
| | jack |
| | tom |
| | lucy |
| | lily |
| | aa |
| | bb |
+----+------+
rows in set (0.00 sec)