原理:
Master设置同步账号:
# 不加mysql_native_password会使用sha2密码,连不上
CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
# 授予主备权限,否则权限异常
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'ASUS';
# 刷新权限
FLUSH PRIVILEGES;
配置目录/var/mysql/mysqld.conf
Master配置:
# 不能重复
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 100M
# 指定复制库
binlog_do_db = auth_db
# 主从复制的格式(mixed,statement,row,默认格式是statement。建议是设置为row,主从复制时数据更加能够统一)
binlog_format = row
# 默认不能远程连接
bind-address:0.0.0.0 监听所有,默认监听本地
Slaver配置:
server-id = 2
# 关闭log_bin 因为没有Slave了。
log_bin = off
max_binlog_size = 100M
# 指定复制库
replicate-do-db = auth_db
binlog_format = row
relay_log = /var/log/mysql/relay.log
# 只读
read_only = ON
log_replica_updates = OFF
登录主库查看文件和位置
show master status;
登录从库打开复制
# 修改同步源
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='ASUS',
SOURCE_USER='repl',
SOURCE_PASSWORD='zxc521cxzPass!',
SOURCE_LOG_FILE='mysql-bin.000001',
SOURCE_LOG_POS=157;
# 开始复制
start replica;
# 查看状态
show replica status\G;