主从复制配置:
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.数据验证