MySQL初识复制

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'@'%';

备份文件的名称格式:dbname-port-full|incr-`%Y-%m%d-%h%m`.sql

备份时-A参数并不能全部包括,如果要全部备份要加上--all-databases --triggers --routines --events,备份时建议将表结构,数据文件,触发器,存储过程,事件分开来备份