MySql主从同步
MySql主从搭建
热备份方案,可解决以下问题
1.主机正常时,从机与主机断开了,需要重新挂主机
2.主从第一次搭建时
文章目录
- MySql主从搭建
- 前言
- 一、修改配置
- 1.编辑主机的/etc/my.cnf
- 2.编辑从机的/etc/my.cnf
- 二、操作主机
- 1.给从机授予复制权限
- 2.主机导出数据
- 三、操作从机
- 1.将从机挂载到主机
- 总结
前言
主从原理:
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