mysql复制功能
MySQL 内建的复制功能是构建大型，高性能应用程序的基础。将 MySQL 的 数据分布到到多个系统上去，这种分布的机制，是通过将 MySQL 的某一台主机的数据复制到其它主机( Slave )上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置，从服务器接收从那时起发生的任何更新，然后封锁等等主服务器通知新的更新。请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对服务器上的表所进行的更新之间的冲突
复制问题解决了什么问题：
- 实现了不同服务器上的数据分布
- 利用二进制日志增量进行
- 不需要太多的带宽
- 但是使用基于行的复制在进行大批量的更改时会对带宽带来一定的压力，特别是跨IDC环境下进行复制应该分批进行。
- 实现在不同服务器上的数据分布
- 实现在数据读取的负载均衡，需要其他组件配合完成比如利用DNS轮询的方式把程序的读连接到不同的备份数据库，使用LVS，haproxy这样的代理方式。
- 非共享架构，同样的数据分布在多台服务器上，增强了数据的安全性
- 利用备库的备份来减少主库的复杂，复制并不能代替备份
- 方便的进行数据库高可用架构的部署，避免MySQL单点失败，实现数据库高可用和故障切换
- 实现数据库在线升级
MySQL二进制日志
MySQL有很多种日志，按照记录日志的MySQL组件可以分为MySQL服务层日志和MySQL存储引擎层日志，比较重要的是MySQL二进制日志，如下：
- 错误日志――MySQL服务启动和关闭过程中的信息以及其它错误和警告信息。默认在数据目录下。
- 一般查询日志――用于记录select查询语句的日志。general_log、general_log_file 默认关闭，建议关闭。
- 慢查询日志――log-slow-queries记录所有超过long_query_time时间的SQL语句。
- 二进制日志――记录任何引起数据变化的操作，用于备份和还原。默认存放在数据目录中，在刷新和服务重启时会滚动二进制日志。
- 中继日志――从主服务器的二进制文件中复制的事件，并保存为二进制文件，格式和二进制日志一样。
-
事务日志――保证事务的一致性。
主服务器的所有变更点都记录在二进制日志里面。二进制日志里记录了与更新相关的SQL文，执行查询的日期时间等元数据也记录在内。事务提交的同时以二进制的形式进行记录(sync_binlog=1)。
mysqlbinlog 命令可以查看日志内容。指定启动选项进行输出二进制日志。
--log-bin[=file_name]
MySQL二进制日志格式
基于段的格式：binlog_format=STATEMENT
- 优点：日志记录量相对较小，节约磁盘以及网络I/O
- 缺点：必须要记录上下文信息，保证语句自爱从服务器上执行结果和主服务器上相同，但是比如特定的函数UUID()，user()这样的非确定性函数还是无法复制，可能造成MySQL复制的主从服务器数据不一致。
mysql> show variables like 'binlog_format'; #查看二进制日志格式，下边显示使用row
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
mysql> set session binlog_format=statement; #设置成statement格式
mysql> show variables like 'log_bin'; #查询二进制日志是否开启，显示关闭
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
开启二进制日志
打开mysql的配置文件添加log_bin配置
[mysqld]
log_bin = mysql_bin#位置自定义,如/var/lib/mysql/sql_log/mysql_bin
刷新bin_logs;
mysql>flush logs; #刷新bin_logs
mysql>flush binary logs;
+---------------------+-------------+
| Log_name | File_size |
+---------------------+-------------+
| mysql-bin.000001 | 201 |
+---------------------+-------------+
| mysql-bin.000002 | 154 |
+---------------------+-------------+
#各种数据库操作，statement日志会记录自行的SQL
>create xx
>select xxx
>update xx
>delete xx
查看二进制日志
$ cd /var/lib/mysql/sql_log/
$ ls
mysql-bin.000001 mysql-bin.000002 mysql-bin.index mysql-error.log
$ mysqlbinlog -vv mysql-bin.000002 #查看日志内容，可以清楚的看到执行的SQL语句
基于行的日志格式：binlog_format=ROW
ROW格式可以避免MySQL复制中出现的主从不一致问题，MySQL5.7默认采用的日志格式
基于行的日志会分别记录每一行的数据修改
优点
- 使MySQL主从复制更加安全
- 对每一行数据的修改比基于段的复制高效
- 如果误操作修改了数据库中的数据，同时又么有备份可以恢复时，我们就可以通过分析二进制日志，对日志中的记录的数据修改操作做反向处理的方式达到恢复数据的目的。
缺点
- 记录日志量较大
binlog_row_image
MySQL5.6为row格式增加一个binlog_row_image参数，这个参数可以控制row格式记录日志的方式
binlog_row_image=[FULL|MINIMAL|NOBLOB]
FULL：数据修改时，会记录一行数据的所有的列的内容，不论列是否被修改过
MINIMAL：只会记录修改的列的变化
NOBLOB：不记录BLOB类型的变化
混合日志格式：binlog_format = MIXED
特点：
- 根据SQL语句由系统决定在基于段和基于行的日志格式中进行选择
- 数据量的大小由所执行的SQL语句决定
如何选择二进制日志的格式
建议：binlog_format=mixed 或 binlog_format=row，使用row格式的话建议设置参数为binlog_row_image=minimal
MySQL二进制日志格式对复制的影响
根据二进制的数据格式，复制可以分为：
基于SQL语句的复制（SBR）
二进制日志格式使用statement格式
优点：
- 生成的日志量少，节约网络传输I/O
- 并不强制要求主从数据库的表定义完全相同
- 相比于基于行的复制方式更加灵活
缺点：
- 对于非确定性时间，无法保证主从复制数据的一致性
- 对于存储过程，触发器，自定义函数进行的修改也可能造成数据不一致
- 相比基于行的复制方式在从执行时间上需要更多的执行时间
基于行的复制（RBR）
二进制日志格式使用的是基于行的日志格式
优点：
- 可以应用于任何SQL的复制包括非确定函数，存储过程等
- 可以减少数据库锁的使用
缺点：
- 要求主从数据库的表结构相同，否则可能会中断复制
- 无法在从上单独的执行触发器
混合模式
根据实际内容在以上两者间切换
MySQL复制工作方式
- 1、主服务器将变更写入二进制日志
- 2、从服务器读取主服务器的二进制日志变更并写入到relay_log中
- 基于日志点的复制
- 基于GTID的复制
- 3、在从上重放relay_log中的日志
- 基于SQL段的日志使在从库上重新执行记录的SQL完成的
- 基于行的日志则使在从库上直接应用对数据库行的修改完成的
基于日志点的复制
如果主从服务器上的MySQL都是最新安装的，为了实现主从复制需要进行如下步骤
- 1、在主DB服务器上建立复制的账号并授权
mysql>create user 'repl'@'IP段' identified by 'PassWord';
mysql>grant replication slave on *.* to 'repl'@'IP段';
- 2、配置主数据库服务器
binlog_format二进制格式要设置，前面内容讲解到
my.cnf文件配置
[mysqld]
#启用二进制日志(可自定义存放位置,需要先创建并赋予权限)
log_bin = /var/lib/mysql/sql_log/mysql_bin
# mkdir /var/lib/mysql/sql_log
# chown -R mysql:mysql /var/lib/mysql/sql_log/
#指定主服务器的服务ID,整个服务集群中式唯一的(自定义,集群中不重复即可)
server_id = 100
- 3、配置从数据库服务器
my.cnf文件配置
#启用二进制日志(可自定义存放位置,需要先创建并赋予权限)
log_bin = /var/lib/mysql/sql_log/mysql_bin
# mkdir /var/lib/mysql/sql_log
# chown -R mysql:mysql /var/lib/mysql/sql_log/
#指定从服务器的服务ID,整个服务集群中式唯一的(自定义,集群中不重复即可)
server_id = 101
#启用中继日志(可自定义存放位置)
relay_log = /var/lib/mysql/sql_log/mysql_relay_bin
#可选，建议从服务器只读
read_only = on
#可选(如果需要将从服务器作为其他服务器的主服务器时必须设置，否则不设置)
log_slave_updates = on
- 4、初始化从服务器数据，导出主库中的数据到从库
如果是新服务器 或 数据都是一致的跳过此步骤
两种导出方式：
mysqldump --master-data -single-transaction#会对表造成大量阻塞
xtrabackup --slave-info#对全部都是innodb的表最好的选择,不会阻塞
- 5、在主服务器查看日志
mysql> SHOW MASTER STATUS \G;
*************************** 1. row ***************************
File: mysql_bin.000007
Position: 5856
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
- 6、在从服务器启动复制链路
mysql>CHANGE MASTER TO
->MASTER_HOST='master_host_ip',#主DB服务器IP
->MASTER_USER='repl',#主DB复制用户
->MASTER_PASSWORD='PassWord',#主DB复制用户密码
->MASTER_LOG_FILE='mysql_bin.000007',#在主DB查看日志文件
->MASTER_LOG_POS=5856;#在主DB查看节点
mysql>show slave status \G;#查看复制链路是否启动
mysql>start slave;#启动复制链路
mysql>show processlist; #可以看到从服务器上建立的进程
- 7、在从服务器检查复制链路状态
检查以下两个参数是否为Yes： - Slave_IO_Running
- Slave_SQL_Running
mysql>show slave status \G;
*************************** 1. row ***************************
Slave_IO_Running: Yes #YES表示成功
Slave_SQL_Running: Yes #YES表示成功
Last_IO_Errno: 0 #IO错误代码
Last_IO_Error: #IO错误提示
Last_SQL_Errno: 0 #SQL错误代码
Last_SQL_Error: #SQL错误提示
- 8、在主服务器查看正在运行的进程
可以看到Command:Binlog Dump
mysql> SHOW PROCESSLIST \G;
*************************** 2. row ***************************
Id: 143
User: repl
Host: 192.168.10.1:55402
db: NULL
Command: Binlog Dump
Time: 9527
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
*************************** 3. row ***************************
基于日志点的复制的
优点：
- 是MySQL最早的复制技术，Bug相对较少
- 对SQL查询没有任何权限
- 故障处理比较容易
缺点：
- 故障转移时重新获取新的住的日志点信息比较困难
基于GTID的复制
MySQL5.6 时支持的模式
基于GTID的复制与基于日志的的复制有很大的区别
什么是GTID
即全局事务ID，其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID
GTID=source_id:transaction_id
使用了GTID后不可使用如下操作
- create table …select
- 在事务中使用create temporary table 建立临时表使用关联更新事务表和事非事务表
操作步骤
- 1、在主DB服务器上建立服务制账号
mysql>create user 'repl' @ 'IP段' identified by 'PassWord';
mysql>grant replication slave on *.* to 'repl' @ 'IP段';
- 2、配置主数据库服务器
my.cnf
log_bin = /usr/local/mysql/log/mysql-bin
server_id = 100
gtid_mode = on #启动gtid
enforce-gtid-consiste #强制gtid一致性，保证事务安全
log-slave-updates = on # mysql5.7可以不用这个参数
- 3、配置从数据库服务器
server_id = 101
relay_log = /usr/local/mysql/log/relay_log
gtid_mode = on
enforce-gtid-consistency
log-slave-updates = on # mysql5.7可以不用这个参数
read_only = on #建议
master_info_repository = TABLE #建议
realy_log_info_repository = TABLE #建议
- 4、初始化从服务器数据
mysqldump --single-transaction --master-data --triggers --routines --all-database -uroot -p >> all2.sql;
#会对表进行加锁
xtrabackup --slave-info
#scp all2.sql root@192.168.3.101:/root #拷贝备份文件到从库
# mysql -uroot -p < all2.sql #初始化从服务器，导入后主从数据库初始化是一致的。
- 5、启动基于GTID的复制(记录备份时最后的事务的GTID值)
mysql>CHANGE MASTER TO MASTER_HOST = 'master_host_ip'
MASTER_USER = 'repl'
MASTER_PASSWORD = 'PassWord'
MASTER_AUTO_POSITION = 1;
基于GTID复制的优缺点
优点：
- 可以很方便的故障转移
- 从库上不会丢失主库上的任何修改
缺点：
- 故障处理比较复杂
- 对执行SQL有一定的限制
选择复制模式要考虑的问题
- 所使用的MySQL版本
- 复制架构以及主从切换的方式
- 所使用的高可用管理组件
- 对应用的支持程度
MySQL复制拓扑
下边来了解一下MySQL常见的拓扑结构
1、一主多从的复制拓扑
优点：
- 配置简单
- 可以用多个从库分担读负载
用途：
- 为不同业务使用不同的从库
- 将一台从库放到远程的IDC，用于灾备恢复
- 分担主库的读负载
2、主-主复制
主备模式的主主复制模式：
有一台主服务器对外提供服务，一台服务器处于只读状态，并且只作为热备使用。
在对外提供服务的主库出现故障或者是计划性的维护时才会进行切换，使原来的备库成为主库，而原来的主库会成为新的备库，并处理只读或者是下线状态，维护完成后重新上线。
主备模式下的主-主复制的配置注意事项：
- 确保两台服务器上的初始化数据相同
- 确保两台服务器上已经启动logbin并且有不同的server_id
- 两台服务器上启动log_slave_updates参数
- 在初始化备库上启用read_only
主主模式的主主复制模式
两个主同时对外提供服务，并不能分担写负载
主主模式下的主主复制缺点：
- 产生数据冲突而造成复制链路中断，耗费大量的时间排查问题
- 容易造成数据丢失
如果一定要使用主主复制模式，应该注意一下事项：
- 两个主中所操作的表最好能够分开
- 使用下边两个参数控制自增ID的生成
auto_increment_increment = 2
auto_increment_offset = 1 | 2
3、拥有备库的主-主复制拓扑
4、级联复制
可以避免分发主库连接从库过多而占用主库过多的带宽，使用分发主库可以解决这个问题。
MySQL复制性能优化
影响主从延迟的原因：
- 主库写入二进制的日志时间
可以通过：控制主库的事务大小，分割事务来解决
- 二进制日志传输时间
可以通过：段使用MIXED日志格式或行设置set binlog_row_image=minimal来解决
- 默认情况下只有一个SQL线程，主上并发的修改在从上变成了串行
可以使用使用多线程复制（MySQL5.6）,在MySQL5.7中可以按照逻辑时钟的方式来分配线程
MySQL5.7上使用多线程复制
mysql>stop slave
mysql>set global slave_parallel_type = 'logical_clock'; #使用逻辑时钟的方式复制
mysql>set global slave_parallel_workers=4; #设置多线程复制的数量
mysql>start slave;#启动复制
mysql>show processlist; #查看进程的数量
mysql>show variables like 'slave_parallel_type'; #查看并发类型配置
mysql>show variables like 'slave_parallel_workers'; #查看并发线程数量
MySQL复制常见问题处理
由于数据损坏或丢失所引起的主从复制错误
- 主库或从库意外宕机引起的错误，可以使用跳过二进制日志事件或者注入空事务的方式恢复中断的复制链路再使用其他方法来对比主从服务器上的数据。
- 主库上的二进制的日志损坏，只能通过从库中使用change master命令来重新指定
- 备库上的中继日志的损坏
- 在从库上进行数据修改造成的主从复制错误，应该设置从库时read_only
- 不唯一的server_id或server_uuid（记录在数据目录中的auto.cnf文件中）
- max_allow_packet设置引起的主从复制错误
MySQL复制无法解决的问题
- 分担主数据库的写负载
- 自动的故障转移以及主从切换
- 提供读写分离
参考：
https://www.cnblogs.com/happy4java/p/11206023.html
http://blog.itpub.net/31549334/viewspace-2215034/
网友评论