MySQL复制简介
- 从MySQL 3.23.x版本开始支持
- 利用复制，添加从库，通过从库提升读的能力及主库故障，可以从从库里提升出来一个新的主库，从而达到高可用的目的。
- 目前MySQL为了支持多引擎的主从复制，引入了一个binary log，所有引擎的复制都是基于binary log实现
- 现在标准的选择：Row+GTID复制
理解binary log
- 根据日志格式复制分
- 根据日志定义的格式不一样，可以分为：Statement(SBR)格式,Row(RBR)格式或是MIXED格式，参数为binlog_format
- 记录最小的单位是一个Event，日志前4个字节是一个magic number，接下来19个字节记录Format desc event：FDE
- 一个事物由多个Event组成
- bin log相关的包含：binary log和binary log index文件
- MySQL5.6增加了GTID复制
statement格式
- 基于语句级的复制
- binlog_format=statement
- 优点:
- bin log文件较小
- 日志是包含用户执行的原始SQL,方便统计和审计
- 出现最早,binlog兼容较好
- binlog阅读方便,故障修复方便
- 缺点:
- 存在安全隐患,可能导致主从不一致
- 对一些系统函数不能准确复制或是不能复制
- load_file()
- uuid()
- user()
- found_rows()
- sysdate()
- now()
- ...
Row格式
- Row格式，基于行级的复制
- binlog_format=row
- 优点：
- 相比statement更加安全的复制格式
- 在某些情况下复制速度更快(SQL复杂,表有主键)
- 系统的特殊函数也可以复制
- 更少的锁
- 缺点:
- binary log比较大(支持binlog_row_image)
- 单语句更新[删除]表的行数过多,会形成大量binlog
- 无法从binlog看见用户执行的SQL(Event:binlog_row_query_log_events,记录用户的query)
row复制解析
mixed格式
- 基于混合复制模式
- binlog_format=mixed
- 混合使用ROW和Statement格式,对于DDL记录会Statement,对于table里的行操作记录为ROW格式
- 如果使用innodb表,事物级别使用了read committed or read uncommitted日志级别只能使用Row格式
- 但在使用Row格式中DDL语句还是会记录成Statement格式
- Mixed模式,那么在以下几种情况下会自动将binlog的模式有SBR改成RBR模式
- 当DML语句更新一个NDB表时
- 当函数中包含UUID()时
- 2个及以上包含auto_increment字段的表被更新时
- 行任何insert delayed语句时
- 用UDF时
- 视图中必须要求使用RBR时,例如创建视图时使用了UUID()函数
GTID
- GTID
- Global transaction identifiers
- 一个事物对应一个唯一ID
- 一个GTID在一个服务器上只会执行一次
- GTID是用来替代以前classic的复制方法
- MySQL-5.6.2支持,MySQL-5.6.10后完善
- GTID组成
- server_uuid:sequence_number
[mysqld]
#GTID
gtid_mode=on
enforce-gtid-consistency=on
#bin log
log-bin=mysql-bin
log-slave-updates=1
基于binary log+file position方式的复制
- server-id在同一个复制结构里不能出现冲突
- server-id建议ip最后一位加端口号的方式
master:
[mysqld]
log-bin=mysql-bin
server-id=1003306
slave:
[mysqld]
server-id=1013306
- 创建复制用的账号
mysql>create user 'repl'@'%' identified by 'repl4slave';
mysql>grant replication slave on *.* to 'repl'@'%';
提示:确认GTID关闭
mysql>show global variables like 'gtid_moed';
gtid_mod=off
- 在主库上做备份
- mysqldump --master-data=2 --single-transaction -A > db_`%Y%m%d`.sql
- slave
- mysql < ./db_20170515.sql
- more ./db_20170515.sql
slave >change master to master_host='masterip',
master_port=3306,
master_user='repl',
master_password='repl4slave',
master_log_file='mysql-bin.xxxx',
master_log_pos=xxxx;
- slave>start slave;
- slave>show slave status\G
GTID环境搭建
msater:
[mysqld]
log-bin=mysql-bin
server-id=1003306
gtid-mode=on
enforce-gtid-consistency=1
slave:
[mysqld]
server-id=1013306
gtid-mode=on
enforce-gtid-consistency=1
- 创建复制用的账号
mysql>create user 'repl'@'%' identified by 'repl4slave';
mysql>grant replication slave on *.* to 'repl'@'%';
