配置mysql基于gtid主从复制架构一、二进制安装mysql[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 mysqlmkdir -p /data/mysql_datachown -R mysql.mysql /data/mysql_data# 编辑配置vim /etc/my.cnf[client]port = 3306socket = /tmp/mysql.sock# The MySQL server[mysqld]user = mysqlport = 3306bind-address = 0.0.0.0socket = /tmp/mysql.sockdatadir = /data/mysql_datapid-file = /data/mysql_data/mysql.pidskip-external-locking#memory is 16G#key_buffer_size = 16Mkey_buffer_size = 32M#table_open_cache = 64table_open_cache = 1024innodb_open_files = 450#sort_buffer_size = 512Ksort_buffer_size = 2M#net_buffer_length = 4Knet_buffer_length = 32K#read_buffer_size = 256Kread_buffer_size = 2M#read_rnd_buffer_size = 4Mread_rnd_buffer_size = 8M#myisam_sort_buffer_size = 4Mmyisam_sort_buffer_size = 32Mthread_cache_size = 800query_cache_size = 32Mquery_cache_type = 1max_write_lock_count = 300skip-name-resolvewait_timeout = 120interactive_timeout = 120max_connections = 400max_connect_errors = 10000max_allowed_packet = 320Mback_log = 1024log_timestamps = systemsync_binlog = 1 #当链接数耗尽后，通过设置别用端口，让root可以登录extra_max_connections = 2extra_port = 13306##让mysql不区分大小写敏感lower_case_table_names = 1character_set_server = utf8mb4performance_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 = 10slave-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.5slow_query_log = onslow-query-log-file = /data/mysql_data/slow.log#skip-networking# Replication Master Server (default)# binary logging is required for replicationlog-bin = mysql-binexpire_logs_days = 8log_error = error.loglog_warnings = 1# binary logging format - mixed recommendedbinlog_format = row#binlog_format=mixedrelay-log = mysql-relay-bin# 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 = 9662945782server-id =3862945782sql-mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"#sync_binlog = 2##### Replication #####replicate-ignore-db = mysqlreplicate-ignore-db = information_schemareplicate-ignore-db = performance_schemareplicate-ignore-db = sysreplicate-ignore-db = undologreplicate-ignore-db = for_nagiosreplicate_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:autoextendinnodb_log_files_in_group = 2innodb_log_file_size = 1572864000master-info-repository = TABLE ###Slave配置需要relay-log-info_repository = TABLE ###Slave配置需要binlog-format =ROW ####Slave配置需要gtid-mode = on ###开启GTID需要enforce-gtid-consistency = true ###开启GTID需要#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 tablesinnodb_data_home_dir = /data/mysql_data#innodb_data_file_path = ibdata1:1G;ibdata2:1G:autoextendinnodb_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 highinnodb_buffer_pool_size = 1Ginnodb_buffer_pool_instances = 1#innodb_additional_mem_pool_size = 8M# Set .._log_file_size to 25 % of buffer pool size#innodb_log_file_size = 256Minnodb_log_buffer_size = 64M#innodb_log_files_in_group = 3innodb_flush_log_at_trx_commit = 2innodb_lock_wait_timeout = 30innodb_file_per_table = 1innodb_thread_concurrency = 4innodb_max_dirty_pages_pct = 75innodb_flush_method = O_DIRECTinnodb_purge_threads = 4innodb_large_prefix = 1innodb_read_io_threads = 16innodb_write_io_threads = 16innodb_io_capacity = 1000innodb_io_capacity_max = 2000thread_pool_size = 8thread_handling = pool-of-threadsthread_pool_oversubscribe = 40thread_pool_stall_limit = 100thread_pool_max_threads = 60#解释： 在启动时把热数据加载到内存。innodb_buffer_pool_load_at_startup = 1 ##解释： 在关闭时把热数据dump到本地磁盘innodb_buffer_pool_dump_at_shutdown = 1[mysqldump]quickmax_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 = 20Mkey_buffer_size = 200Msort_buffer_size = 200Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout初始化MySQL数据库的数据文件路径，并且创建系统表，5.7.6及以上版本，要使用mysqld来初始化数据库将mysql命令加入环境变量中vim /etc/profileexport PATH=/usr/local/mysql/bin:$PATH# 修改权限chown -R mysql.mysql /data/mysql_data[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/mysqlbasedir=/usr/local/mysqldatadir=/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主从主库配置片段：log-slave-updates = ONmaster-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 = CRC32master-verify-checksum = 1从库配置片段：log-slave-updates = ONmaster-info-repository = TABLE ###Slave配置需要relay-log-info_repository = TABLE ###Slave配置需要binlog-format =ROW ####Slave配置需要gtid-mode = on ###开启GTID需要enforce-gtid-consistency = true ###开启GTID需要skip-slave-start = trueread_only = ONslave-sql-verify-checksum = 1relay-log = relay-logrelay-log-index = relay-log-indexrelay-log-recovery = ONslave-sql-verify-checksum = 1主库添加复制用户：mysql> grant replication slave on *.* to 'repl'@'10.11.0.215' identified by 'replpass';mysql> 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_uuidmysql> 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 | 194 | | | 190a16f8-63b4-11e8-a82d-000c29ff3eec:1-2 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)配置从库连接至主库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: YesSlave_SQL_Running: Yes...确认 Slave_IO_Running 和 Slave_SQL_Running 两个参数都为 Yes 状态。在主服务器查看从库连接的主机信息测试GTID主从复制在主库(node1)实例创建一些数据，看从库是否能够正常新增**********配置mysql基于gtid一主多从方式node1(master) --> node5(slave) --> node2(主库指向node5,slave)在之前基于gtid的主从模式：node1(master) --> node5(slave)基础上配置添加第二个从库node2 10.11.0.212的过程：通过二进制安装好数据库my.cnf配置片段*********************log-slave-updates###rds-ecs，此处配置根据rds的配置来进行设置innodb_data_file_path = ibdata1:200M:autoextendinnodb_log_files_in_group = 2innodb_log_file_size = 1572864000master-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 = 1relay-log = relay-logrelay-log-index = relay-log-indexrelay-log-recovery = ONslave-sql-verify-checksum = 1*********************主库node5中添加复制用户：mysql> grant replication slave on *.* to 'repl'@'10.11.0.212' identified by 'replpass';mysql> flush privileges;node2上配置从库连接至主库mysql> CHANGE MASTER TO MASTER_HOST='10.11.0.215',MASTER_USER='repl',MASTER_PASSWORD='replpass',MASTER_AUTO_POSITION=1;在从服务器上启动复制mysql> START SLAVE;启动成功后查看SLAVE的状态mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.11.0.215 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 3596 Relay_Log_File: relay-log.000003 Relay_Log_Pos: 3769 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes看到主库的数据成功复制过来了mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || china || master1 || mysql || performance_schema || sys |+--------------------+mysql> use master1;Database changedmysql> show tables;+-------------------+| Tables_in_master1 |+-------------------+| test1 |+-------------------+1 row in set (0.00 sec)mysql> select * from test1;+------+-------+| id | count |+------+-------+| 1 | 1 || 2 | 2 || 5 | 5 |报错处理：看到Slave_IO_Running: Connecting一只处于连接中状态mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 10.11.0.215 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: relay-log.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: Connecting Slave_SQL_Running: Yes观察日志：[root@node02 ~]# tail -f /data/mysql_data/error.log 2018-05-31T20:46:55.187975+08:00 2 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.2018-05-31T20:46:55.187987+08:00 2 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.2018-05-31T20:48:07.059454+08:00 3 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.2018-05-31T20:48:07.062633+08:00 3 [ERROR] Slave I/O for channel '': error connecting to master 'repl@10.11.0.215:3306' - retry-time: 60 retries: 1, Error_code: 1130原来是授权用户配置错误，修改到repl@10.11.0.212:3306即可*****************************主从切换：***********1.锁定原主数据写操作原主库中操作：mysql> flush tables with read lock;Query OK, 0 rows affected (0.01 sec)2.在从服务器中执行stop slave io_thread,用show processlist检查，查看状态是否是Has read all relay log,表示更新完成.从服务器执行mysql> show processlist;+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+-----------+---------------+| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+-----------+---------------+| 4 | system user | | NULL | Connect | 578 | Slave has read all relay log; waiting for more updates | NULL | 0 | 0 || 27 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 |+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+-----------+---------------+3.在新的主服务器上(原从服务器)执行stop slave，reset master命令，重置成主数据库新的主库上添加复制用户mysql> grant replication slave on *.* to 'repl'@'10.11.0.212' identified by 'replpass';mysql> flush privileges;mysql> stop slave;mysql> reset master;mysql> reset slave all; -- 清除同步信息4.删除新的主服务器数据库目录中的master.info和relay-log.info文件，否则下次重启时还会按照从服务器来启动.5.原主库切换到从库# 解锁mysql> unlock tables;mysql> CHANGE MASTER TO MASTER_HOST='10.11.0.212',MASTER_USER='repl',MASTER_PASSWORD='replpass',MASTER_AUTO_POSITION=1;Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> start slave;5.修改主从服务器的配置(下次重启后参数改变)主库配置片段：log-slave-updates = ONmaster-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 = CRC32master-verify-checksum = 1从库配置片段：log-slave-updates = ONmaster-info-repository = TABLE ###Slave配置需要relay-log-info_repository = TABLE ###Slave配置需要binlog-format =ROW ####Slave配置需要gtid-mode = on ###开启GTID需要enforce-gtid-consistency = true ###开启GTID需要skip-slave-start = trueread_only = ONslave-sql-verify-checksum = 1relay-log = relay-logrelay-log-index = relay-log-indexrelay-log-recovery = ONslave-sql-verify-checksum = 1报错的处理：mysql> start slave;ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository解决：mysql> reset slave;Query OK, 0 rows affected (0.00 sec)mysql> start slave; 配置percona mysql5.7主从，日志同步的方式：
注意server-id必须不同1.主库master上面配置repl复制同步用户
mysql> CREATE USER 'repl'@'10.11.0.212' IDENTIFIED BY 'replpass';mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.11.0.212';记录主库状态：
mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 | 615 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec) 2.从库配置放在一行执行方便CHANGE MASTER TO MASTER_HOST='10.11.0.210', MASTER_USER='repl', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=615;启动从服务器复制线程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: 10.11.0.210 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 615 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql,information_schema,performance_schema,mysql,information_schema,performance_schemaSlave_SQL_Running: Yes
数据库分析工具percona-toolkit[root@node1 data]# yum localinstall -y percona-toolkit-2.2.19-1.noarch.rpm