MySQL8.0主从同步

参考：https://www.cnblogs.com/kylinlin/p/5258719.html

一、 主数据库

1.修改配置文件

vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]

log-bin=mysql-bin

server-id=1

2.锁表

mysql > flush tables with read lock;

3.备份

mysqldump -u root -p 'root' -S /var/run/mysqld/mysqld.sock kpc | gzip > /tmp/mysql_bak.$(date +%F).sql.gz

4.创建用户并授权

CREATE USER 'rep'@'%' IDENTIFIED WITH mysql_native_password BY 'rep';

GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%';

5.记录信息

mysql > show master status;

6.重启

sudo systemctl restart mysql

二、从数据库

1.修改配置文件

vim /etc/mysql/mysql.conf.d/mysqld.cnf

server-id != 1

[mysqld]

server-id=2

2.配置Master

MASTER_USER

MASTER_PASSWORD

MASTER_LOG_FILE

MASTER_LOG_POS

mysql> CHANGE MASTER TO

-> MASTER_HOST='master_host_name',

-> MASTER_USER='rep',

-> MASTER_PASSWORD='rep',

-> MASTER_LOG_FILE='mysql-bin.000001',

-> MASTER_LOG_POS=156

3.运行

mysql > start slave;