MySQL主从复制-基于日志点配置

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查询没有任何限制
• 故障处理比较容易

缺点

• 故障转移时重新获取新主的日志点信息比较空难