mysql基于GTID的主从复制 - HelloWorld开发者社区

相对传统基于binlog+位置的方法来说，gtid让配置主从更加方便

从库提升为主时比较方便

安装MySQL5.7+（此步骤略）

主库配置

mysql基于GTID的主从复制

[mysql]
no-auto-rehash
default-character-set=utf8

[mysqld]
port=3306
character-set-server=utf8
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
datadir=/data2/mysql
pid-file =/data2/mysql/mysql.pid
explicit_defaults_for_timestamp=true
lower_case_table_names=1
back_log=103
max_connections=3000
max_connect_errors=100000
table_open_cache=512
external-locking=FALSE
max_allowed_packet=32M
sort_buffer_size=2M
join_buffer_size=2M
thread_cache_size=51
query_cache_size=32M
#query_cache_limit=4M
transaction_isolation=REPEATABLE-READ
tmp_table_size=96M
max_heap_table_size=96M
connect_timeout = 60
net_read_timeout = 120
log_timestamps=SYSTEM

###***slowqueryparameters
long_query_time=1
slow_query_log = on
slow_query_log_file=/data2/slowlog/slow.log
innodb-file-per-table=1
innodb_flush_log_at_trx_commit = 2

###***binlogparameters
log-bin=master-bin
log-bin-index = master-bin.index
relay-log = relay-log
relay-log-index = relay-log.index
binlog_cache_size=4M
max_binlog_cache_size=4096M
max_binlog_size=1024M
binlog_format=row
expire_logs_days=7

###***relay-logparameters
#relay-log=/data/3307/relay-bin
#relay-log-info-file=/data/3307/relay-log.info
#master-info-repository=table
#relay-log-info-repository=table
#relay-log-recovery=1

#***MyISAMparameters
key_buffer_size=16M
read_buffer_size=1M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=1M
skip-name-resolve

###***master-slavereplicationparameters
server-id=1
gtid_mode=on
enforce_gtid_consistency=on
#slave-skip-errors=all

#***Innodbstorageengineparameters
innodb_buffer_pool_size=8676M
innodb_data_file_path=ibdata1:10M:autoextend
#innodb_file_io_threads=8
innodb_thread_concurrency=16
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_log_file_size=512M
innodb_log_files_in_group=2
innodb_max_dirty_pages_pct=75
innodb_buffer_pool_dump_pct=50
innodb_lock_wait_timeout=50
innodb_file_per_table=on

[mysqldump]
quick
max_allowed_packet=32M
user=root

[myisamchk]
key_buffer=16M
sort_buffer_size=16M
read_buffer=8M
write_buffer=8M

[mysqld_safe]
open-files-limit=8192
log-error=/data2/mysql/error.log
pid-file=/data2/mysql/mysqld.pid

View Code

从库配置

mysql基于GTID的主从复制

[mysql]
no-auto-rehash
default-character-set=utf8

[mysqld]
port=3306
character-set-server=utf8
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
datadir=/data/mysql
pid-file =/data/mysql/mysql.pid
explicit_defaults_for_timestamp=true
lower_case_table_names=1
back_log=103
max_connections=3000
max_connect_errors=100000
table_open_cache=512
external-locking=FALSE
max_allowed_packet=32M
sort_buffer_size=2M
join_buffer_size=2M
thread_cache_size=51
query_cache_size=32M
#query_cache_limit=4M
transaction_isolation=REPEATABLE-READ
tmp_table_size=96M
max_heap_table_size=96M
log_timestamps=SYSTEM

###***slowqueryparameters
long_query_time=1
slow_query_log = on
slow_query_log_file=/data/slowlog/slow.log
innodb-file-per-table=1
connect_timeout = 60
net_read_timeout = 120

###***binlogparameters
log-slave-updates=1
log-bin=master-bin
log-bin-index = master-bin.index
relay-log = relay-log
relay-log-index = relay-log.index
binlog_cache_size=4M
max_binlog_cache_size=4096M
max_binlog_size=1024M
binlog_format=row
expire_logs_days=7

###***relay-logparameters
#relay-log=/data/3307/relay-bin
#relay-log-info-file=/data/3307/relay-log.info
#master-info-repository=table
#relay-log-info-repository=table
#relay-log-recovery=1

#***MyISAMparameters
key_buffer_size=16M
read_buffer_size=1M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=1M
skip-name-resolve

###***master-slavereplicationparameters
server-id=2
gtid_mode=on
enforce_gtid_consistency=on

#slave-skip-errors=all

#***Innodbstorageengineparameters
innodb_buffer_pool_size=7910M
innodb_data_file_path=ibdata1:10M:autoextend
#innodb_file_io_threads=8
innodb_thread_concurrency=16
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_log_file_size=512M
innodb_log_files_in_group=2
innodb_max_dirty_pages_pct=75
innodb_buffer_pool_dump_pct=50
innodb_lock_wait_timeout=50
innodb_file_per_table=on

[mysqldump]
quick
max_allowed_packet=32M

[myisamchk]
key_buffer=16M
sort_buffer_size=16M
read_buffer=8M
write_buffer=8M

[mysqld_safe]
open-files-limit=8192
log-error=/data/mysql/error.log
pid-file=/data/mysql/mysqld.pid

View Code

查看数据库的uuid，在数据目录的auto.cnf

mysql基于GTID的主从复制

在mysql里面使用命令查看show global variables like 'server_uuid'

mysql> show global variables like 'server_uuid';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 06bca226-539f-11ea-baa1-fa163e65d481 |
+---------------+--------------------------------------+

配置主从

1、主库给从库复制权限

mysql> grant replication slave on *.* to 'replication'@'10.186.100.57' identified by '1234567';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |       177 |
+-------------------+-----------+
mysql> show binlog events in 'master-bin.000005';
+-------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| master-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| master-bin.000002 | 123 | Previous_gtids | 1 | 154 | |
| master-bin.000002 | 154 | Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ad5ee77e-9f2e-11e9-a76d-000c2991dd19:1' |  # 事件类型是GTID  数据库的uuid+事务ID
| master-bin.000002 | 219 | Query | 1 | 398 | ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| master-bin.000002 | 398 | Gtid | 1 | 463 | SET @@SESSION.GTID_NEXT= 'ad5ee77e-9f2e-11e9-a76d-000c2991dd19:2' |   
| master-bin.000002 | 463 | Query | 1 | 702 | GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.132.122' IDENTIFIED WITH 'mysql_native_password' AS '*6A7A490FB9DC8C33C2B025A91737077A7E9CC5E5' |
| master-bin.000002 | 702 | Gtid | 1 | 767 | SET @@SESSION.GTID_NEXT= 'ad5ee77e-9f2e-11e9-a76d-000c2991dd19:3' |
| master-bin.000002 | 767 | Query | 1 | 854 | flush privileges |
+-------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+

2、在slave上执行change master to并带上master_auto_position=1启用基于GTID的复制

mysql> change master to master_host='10.186.100.57',master_port=3306,master_user='replication',master_password='1234567',master_auto_position = 1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.186.100.57
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000006
          Read_Master_Log_Pos: 389
               Relay_Log_File: relay-log.000005
                Relay_Log_Pos: 604
        Relay_Master_Log_File: master-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 389
              Relay_Log_Space: 1060
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 06bca226-539f-11ea-baa1-fa163e65d481
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 06bca226-539f-11ea-baa1-fa163e65d481:3-7
            Executed_Gtid_Set: 06bca226-539f-11ea-baa1-fa163e65d481:1-7
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

3、观察从库binlog日志

mysql基于GTID的主从复制

mysql> show master status;
+-------------------+-----------+--------------+------------------+------------------------------------------+
| File              | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+-------------------+-----------+--------------+------------------+------------------------------------------+
| master-bin.000001 | 516105391 |              |                  | 06bca226-539f-11ea-baa1-fa163e65d481:1-7 |
+-------------------+-----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'master-bin.000001' limit 10;
+-------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name          | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                                                                                                                        |
+-------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| master-bin.000001 |   4 | Format_desc    |         2 |         123 | Server ver: 5.7.27-log, Binlog ver: 4                                                                                                                                                                                                                                       |
| master-bin.000001 | 123 | Previous_gtids |         2 |         154 |                                                                                                                                                                                                                                                                             |
| master-bin.000001 | 154 | Gtid           |         1 |         219 | SET @@SESSION.GTID_NEXT= '06bca226-539f-11ea-baa1-fa163e65d481:3'

View Code

日志名未指定,主机名改变导致主从失效,因为配置文件没有指定日志名,导致MySQL在重启的时候,日志名发生改变,导致同步失败

从端的slave日志未指定,解决

stop slave; #停掉salve

reset slave; #重置salve

change master to master_host='10.186.100.57',master_port=3306,master_user='replication',master_password='1234567',master_auto_position = 1; #重新配从端复制数据

start slave; #开启slave

主端未指定,修复主从

stop slave; #停掉slave

reset slave; #重置slave

reset master; #重置master,必须做,很重要

change master to master_host='10.186.100.57',master_port=3306, master_user='replication',master_password='123456',master_auto_position = 1; 3重新配置主从

start slave; #开启slave

常见错误

gtid_mode=ON,log_slave_updates,enforce_gtid_consistency这三个参数一定要同时在my.cnf中配置。否则在mysql.err中会出现如下的报错