MySql主从同步

MySql主从搭建

热备份方案，可解决以下问题
1.主机正常时，从机与主机断开了，需要重新挂主机
2.主从第一次搭建时

---

---

前言

主从原理：

MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

---

按照时间先后记录同步流程

一、修改配置

1.编辑主机的/etc/my.cnf

[mysqld]
# 设置server-id,唯一值，标识主机，必须与从库不一致
server-id=1

# 开启二进制日志，主库必须开启,因为要向里面写入增删改的操作日志
log-bin=mysql-bin

# 自动删除2592000秒（30天）前的日志
# 8.0以前的版本中这个配置为expire_logs_days，单位为天
binlog_expire_logs_seconds=2592000

# binlog记录的模式
# statement模式不会记录每一条更改语句，节约资源但主从数据可能不一致
# row模式记录每一条更改的语句，日志量非常大
# mixed模式是前两者优点的综合，但日志结构较为复杂
binlog_format=mixed

#设置只记录binlog的库，例如db1库变化，就会写入bin-log
binlog-do-db=db1

2.编辑从机的/etc/my.cnf

[mysqld]
# 设置server-id,唯一值，标识主机，必须与主库不一致
server-id=2

# 从机通过IO线程读取到的bin-log日志会写入中继日志里，要开启
relay_log = /soft/mysql/log/mysql-relay-bin

#设置要复制同步的库
replicate-do-db=db1

# 如果只同步table1表，就开启，不开就是全部的表
# replicate-wild-do-table=db1.table1

# 如果该从库还要当主库用，请开启这个，因为
# 从库只开启bin-log功能，不开启log-slave-updates参数，
# 从库从主库复制的数据不会写入log-bin日志文件里，直接向从机添加的才会记录到bin-log
# log_slave_updates = 1

二、操作主机

1.给从机授予复制权限

根据实际情况，如果是主机里面没有数据，那么开启mysql服务：

[root@localhost ~] service mysqld start
[root@localhost ~] mysql -uroot -p123456
# 查看log_bin日志是否属于开启状态
mysql> show variables like ‘log_bin’;
mysql> use db1;
# 查看当前操作的宿主机是否为master状态
mysql> show master status;
mysql> use mysql;
# 可以看到mysql登录的用户这些
mysql> select * from user;
# 创建zhangsan账户，密码123456，%-就是指ip不限，换句话说：
# 远端navicat也可以用zhangsan连接进来，这里是给从机创建的账号，
# 让从机通过该账号连接进来，进行数据同步
mysql> create user 'zhangsan'@'%' identified by '123456';
# 给从机账户开放复制和查看权限
# replication slave - 复制权限
# replication client- 允许从库查看同步状态
mysql> grant replication slave,replication client on *.* to 'zhangsan'@'%';
mysql> flush PRIVILEGES;

2.主机导出数据

这里导出数据时，要根据实际情况，如果主库此时是写入数据高峰时，请选择凌晨执行
因为导出数据时间很长，要将表锁住(保证数据一致性)，新增数据就进不来就丢失了

mysql> flush tables with read lock;
# --master-data=2 导出数据库时将binlog信息也一并导出，2表示注释binlog文件与位置信息
# 默认导出的都是要删表，重新建，所以从库之后不用清除数据
[root@localhost ~] mysqldump -uroot -p123456 --flush-logs --hex-blob --master-data=2  --all-databases > ./dp.sql
# 将数据文件传输到从机上
[root@localhost ~] scp -P 端口号 ./dp.sql root@192.168.165.171:/soft
# 导出完就立刻解锁,保证数据尽量不丢失
mysql> unlock tables;

三、操作从机

1.将从机挂载到主机

[root@localhost ~] mysql -uroot -p123456
# 查看主机的bin-log文件和位置
[root@localhost soft] head -n 30 dp.sql | grep CHANGE
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=156;
# 执行操作，导入数据(这一块如果50g的文件数据，大概要执行两个小时左右）
mysql> source dp.sql
# 导入完后更换主机，如果之前有挂过其他主机，请先执行这条
# reset slave 清空之前的主机bin-log和位置记录
mysql> change master to 
    master_host='192.168.165.170',
    master_port=3306,master_user='zhangsan',
    master_password='123456',
    master_log_file='mysql-bin.000006',
    master_log_pos=156;
mysql> start slave;
# 出现Slave_IO_Running: Yes Slave_SQL_Running: Yes 挂载成功
mysql>show slave status \G;

---

总结

这是mysql主从搭建常用的热备份，美中不足就是主机dump数据期间要锁表保证数据一致性，后续继续整理mysql的冷备份

注意：
1.主机重启，从机的IO线程就会尝试连接主机，bin-log文件和位置保持不变，直到主机启动好了，从机继续同步，是不会影响从机同步
2.从机重启后会自动开启slave
3.详情还可参考：https://cloud.tencent.com/developer/article/1862758