如果已经开启基于bin-log的主主热备,先停止备份进程

stop slave;

然后进行锁表操作,先锁定主库

show global variables like "%read_only%";
flush tables with read lock;
set global read_only=1;
show global variables like "%read_only%";

看见read_only=ON就是锁定成功了,现在主库只读

mysql> show global variables like "%read_only%";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.00 sec)

现在将主库的数据同步到从库确保数据一致

同步完成后停止数据库

systemctl stop mysql

修改数据库配置文件

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
bind-address	= 0.0.0.0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#为服务器分配id,可以自定义,不区分大小,起标识作用。不同数据库节点分配不同的id
server_id=1
# 打开Mysql 日志,日志格式为二进制
log-bin=mysql-bin
# 可选项Mixed,Statement,Row,默认格式是 Statement,mixed混合Satement,ROW两种模式
binlog_format=mixed
# 当启用时,服务器通过只允许执行可以使用GTID安全地记录的语句来强制GTID一致性。
enforce-gtid-consistency=true
# 启用基于GTID的复制,启用之前必须保证enforce-gtid-consistency=true
gtid_mode=ON
# 该选项让从库写入哪些来自于主库的更新,并把这些更新写入bin-log文件,一台服务器即做主库又做从库必须开启
log-slave-updates=true
# 哪些数据库需要进行记录 ps:英文逗号分割多个
binlog-do-db = db1
# 哪些数据库需要进行同步
replicate-do-db = db1
# 中继日志
relay_log = mysql-relay-bin
# 日志过期时间
expire_logs_days = 10
# 日志最大大小
max_binlog_size = 200M
# 不进行域名反解析,注意由此带来的权限/授权问题
#skip-name-resolve
# 索引缓存,根据内存大小而定,如果是独立的db服务器,可以设置高达80%的内存总量
key_buffer_size = 4G
# 连接排队列表总数
back_log = 200
max_allowed_packet = 128M
# 打开表缓存总数,可以避免频繁的打开数据表产生的开销
table_definition_cache = 512
# 每个线程排序所需的缓冲
sort_buffer_size = 64M
# 每个线程读取索引所需的缓冲
read_buffer_size = 64M
# MyISAM表发生变化时重新排序所需的缓冲
myisam_sort_buffer_size = 128M
# 缓存可重用的线程数
thread_cache_size = 128
# 查询结果缓存
query_cache_size = 1G
# 设置超时时间,能避免长连接
wait_timeout = 60

两端机器的配置文件设置不一样的server-id即可

下面启动数据库

systemctl start mysql

登录数据库后还是先stop slave;确保不会立即同步数据

最好用Navicat等工具对比一下数据

创建用于同步的用户(已有可忽略,两端都创建)

grant replication slave on *.* to 'username'@'%' identified by 'password';
flush privileges;

然后进行锁表操作(操作见上文),两端机器都锁,确保不会写入新的数据

两端机器配置同步信息

CHANGE MASTER TO 
    MASTER_HOST='hostname-1',
    MASTER_PORT=3306,
    MASTER_USER='username',
    MASTER_PASSWORD='password',
    MASTER_AUTO_POSITION=1,
    GET_MASTER_PUBLIC_KEY=1;

PS:对于MYSQL8.0+需要设置GET_MASTER_PUBLIC_KEY=15.7.x版本可忽略

两端机器启动同步

start slave;

查看同步状态

show slave status\G;

显示以下状态则正常

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
I/O线程:表示线程会连接远程Master的mysql,并把它的bin-log拷贝到本机的中继日志relay-log中。

SQL线程:表示该线程会读取本机中继日志中的数据,并把这些数据写入到本机自己的表中。

两端解除锁定

unlock tables;
set global read_only=0;

当有一端数据库发生重启时,不再需要手动设置Position,MySQL会自动同步数据

参考文章

https://segmentfault.com/a/1190000038223139

https://blog.51cto.com/u_15127614/4372418

Q.E.D.