mysql配置主从库全流程纪录

业务需要，正好自己从来没有接触过mysql运维相关，把步骤记录下来。

环境信息

• MySQL 5.7.25
• 主库172.24.7.108
• 从库172.24.7.109

主库创建同步角色

查看已有用户

select distinct concat('User: ''',user,'''@''',host,''';') as query from mysql.user;

删除用户

drop user 'slave'@'172.24.7.109';

创建新用户并限定访问ip

create user 'slave'@'172.24.7.109' identified by '1234509876';

查询用户权限

show grants for 'slave'@'172.24.7.109';

开启数据复制权限(replication slave不能只作用于某一数据库，而是全局)

grant replication slave on *.* to 'slave'@'172.24.7.109';

刷新权限

flush privileges;

最终效果如下

修改主库配置(/etc/my.cnf)

增加以下内容

[mysqld]
# 启用日志，默认为mysql-bin，可替换为指定的目录
log-bin = mysql-bin
# 不可重复的序号
server-id = 1
# 日志保存期限(可选)
expire_logs_days = 30
# 基于行纪录，5.7.7及以后的版本默认是ROW
binlog_format = ROW

修改从库配置(/etc/my.cnf)

增加以下内容

[mysqld]
# 启用日志(可选)
log-bin = mysql-bin
# 不可重复的序号
server-id = 2
# 日志保存期限(可选)
expire_logs_days = 30
# 指定同步的数据库(可选)
replicate-do-db = database
# 基于行纪录，5.7.7及以后的版本默认是ROW
binlog_format = ROW
# 启动复制的进程数
slave_parallel_workers = 4
# 基于组提交
slave_parallel_type = LOGICAL_CLOCK
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON

主从同步

从这里才开始进行同步过程，首先锁定主库只读

flush tables with read lock;

备份主库需要做同步的数据库(如果主库没有数据的话此步可以省略)

mysqldump -h172.24.7.108 -P3306 -uroot -ppassword -B database > /home/backup.sql

-B: 导出的数据文件中已存在创建库和使用库的语句，不需要手动在原库是创建库的操作，在恢复过程中不需要手动建库，可以直接还原恢复。
如果需要压缩备份文件在语句最后增加 | gzip >/home/backup.gz

查看主库bin-log偏移量并记录

show master status;

解除只读

unlock tables;

从库导入备份的数据

mysql -uroot -ppassword < backup.sql

停止从库slave进程

stop slave;

设置从库同步信息

change master to master_host='172.24.7.108',master_port=3306,master_user='slave',master_password='1234509876',master_log_file='mysql-bin.000014',master_log_pos=73276696;

启动同步

start slave;

查看同步状态，等待红框内两项都变成Yes即为同步成功

show slave status\G;

此后需要备份时只需要关闭主从连接，再对从库进行备份即可。

从备份信息中恢复单个表数据

提取建表语句

sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `user_online`/!d;q' backup.sql > user_online.sql

提取insert 语句(update同理)

grep -i 'INSERT INTO `user_online`' backup.sql > user_online.sql