MySQL主从复制-基于日志点配置
一、mysql复制类型及原理
mysql复制类型:
-
SBR :基于sql语句的复制
二进制日志格使用的是statement格式
优点:
- 生成日志量少,节约网络传输I/0
- 并不强制要求主从数据库的表定义完全相同
- 相比于基于行的复制方式更为灵活
缺点:
- 对于非确定性事件,无法保证主从复制数据的一致性
- 对于存储过程,触发器,自定义函数进行的修改也可能造成数据的不一致
- 相比于基于行的复制方式在从上执行时需要更多的行锁
-
RBR:基于行的复制
二进制的日志格式使用的是基于行的日志格式
有点:
- 可以应用于任何SQL的复制包括非确定函数,存储过程等
- 可以减少数据库锁的使用
缺点
- 要求主从数据的表结构相同,否则可能中断复制
- 无法在从上单独执行触发器
-
混合模式
根据实际内容在以上两者建切换
原理图
二、配置主MySQL
主:192.168.163.102
从:192.168.163.103
在主DB服务器上建立复制账号
CREATE USER 'wx'@'192.168.163.103' IDENTIFIED BY 'root';
GRANT REPLICATION SLAVE ON *.* TO 'wx'@'192.168.163.103';
配置主数据库服务器my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1
#为了在使用带有事务的InnoDB的复制设置中获得最大的持久性和一致性
InnoDB_flush_log_at_trx_commit=1
sync_binlog=1
二、配置从数据库服务器
my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1
# 终极日志 默认启动,默认名字是主机名字所以进行固定
relay_log=mysql-relay-bin
#可选
log_slave_updates=on
#可选 安全配置可以阻止任何没有sql语句权限账号对数据进行修改
read_only=on
三、 基于日志点的复制配置步骤
初始化从服务器数据
mysqldump --master-data=2-single-transaction
# 示例从主数据导出sql
mysqldump --single-transaction --master-data --triggers -routines --all-databases -uroot -p > all.sql
scp all.sql root@192.168.163.103:/root
# 从库导入
mysql -uroot -p < all.sql;
启动复制连路
CHANGE MASTER TO
MASTER_HOST='192.168.163.102',
MASTER_USER='wx',
MASTER_PASSWORD='root',
MASTER_LOG_FILE='mysql-bin.000014', #二进制日志文件名
MASTER_LOG_POS=154;#偏移量
MASTER_LOG_FILE和MASTER_LOG_POS为主服务器备份文件开头位置名称及参数
查看连路配置
show slave status \G;
#启动
start slave;
#查看
show slave status \G;
注意:上图表示配置成功,如果此处显示为null有可能是数据库的UUID相同导致,可修改 auto.cnf文件uuid和主服务器不同即可。
查看从服务器的线程
show processlist;
查看主服务器复制线程
优点:
- 是MySQL最早支持的复制技术,Bug相对较少
- 对SQL查询没有任何限制
- 故障处理比较容易
缺点
- 故障转移时重新获取新主的日志点信息比较空难