mysql复制复制切换主主复制开启GTID

喜欢ヅ旅行 2022-06-13 00:16 258阅读 0赞

主从复制配置：

1.设置master参数  
\# BINARY LOGGING \#  
server-id                      = 1  
log-bin                        = /mysqldata/mysql-bin  
expire-logs-days               = 14  
sync-binlog                    = 1  
binlog\_format                  = row  
  
\# REPLICATION \#  
skip-slave-start               = 1  
log-slave-updates              = 1  
relay-log                      = /mysqldata/relay-bin  
slave-net-timeout              = 60  
2.创建复制用户  
mysql> grant replication slave on \*.\* to 'repl'@'10.10.26.94' identified by 'repl';  
Query OK, 0 rows affected, 1 warning (0.12 sec)  
  
mysql> show master status;  
\+------------------+----------+--------------+------------------+-------------------+  
| File             | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB | Executed\_Gtid\_Set |  
\+------------------+----------+--------------+------------------+-------------------+  
| mysql-bin.000007 |      444 |              |                  |                   |  
\+------------------+----------+--------------+------------------+-------------------+  
1 row in set (0.00 sec)  
3.获取master binary log日志坐标  
4.迁移数据库到slave  
5.设置slave端参数  
\# BINARY LOGGING \#  
server-id                      = 2  
log-bin                        = /mysqldata/mysql-bin  
expire-logs-days               = 14  
sync-binlog                    = 1  
binlog\_format                  = row  
  
\# REPLICATION \#  
skip-slave-start               = 1  
log-slave-updates              = 1  
relay-log                      = /mysqldata/relay-bin  
slave-net-timeout              = 60  
6.在slave执行配置  
CHANGE MASTER TO    
MASTER\_HOST='10.10.26.93',  
MASTER\_USER='repl',  
MASTER\_PASSWORD='repl',  
MASTER\_LOG\_FILE='mysql-bin.000007',  
MASTER\_LOG\_POS=444;  
7.启动slave  
start slave  
8.测试验证

开启GTID

开启GTID复制  
1.修改master slave配置  
\#GTID:  
gtid\_mode=on  
enforce\_gtid\_consistency=on  
2.重启mysql   
3.在slave执行  
CHANGE MASTER TO    
MASTER\_HOST='10.10.26.93',  
MASTER\_USER='repl',  
MASTER\_PASSWORD='repl',  
MASTER\_AUTO\_POSITION=1;  
4.验证测试

主从切换：  
  
1.从库建立复制用户  
grant replication slave on \*.\* to 'repl'@'10.10.26.94' identified by 'repl';  
2.查看从库状态  
show slave status\\G;  
停止io\_thread 线程  
stop slave io\_thread  
3.激活从库  
stop slave  
reset master  
reset slave all；  
show binary logs  
4.将原来的主库变为从库  
CHANGE MASTER TO    
MASTER\_HOST='10.10.26.93',  
MASTER\_USER='repl',  
MASTER\_PASSWORD='repl',  
MASTER\_AUTO\_POSITION=1;  
5.启动复制  
start slave；  
6.数据验证

猪猪复制  
1.在master端执行  
mysql> CHANGE MASTER TO    
    -> MASTER\_HOST='10.10.26.94',  
    -> MASTER\_USER='repl',  
    -> MASTER\_PASSWORD='repl',  
    -> MASTER\_AUTO\_POSITION=1;  
Query OK, 0 rows affected, 2 warnings (0.04 sec)  
  
mysql> show slave status \\G;  
2.在master启动复制  
start slave  
3.数据验证