Mysql 复制 Replication

参考

简介

复制是mysql自带数据同步功能,可用于构建大规模高可用Mysql集群.复制解决的基本问题就是将一台数据库上的数据同步到另一台数据库上.

解决的问题(应用场景)

1.数据分布,备份

在不同的数据中心存储备份数据,即使在不稳定的网络环境中,复制可以正常工作

作为数据备份的补充技术手段

2.负载均衡

解决读密集型业务,将读取请求分发到多台数据库服务器上

3.高可用和故障切换

避免单点问题一台数据库服务器宕机还有其它从库可用

4.mysql升级测试

升级更高版本的mysql时,使用一台备库升级测试,不影响正常业务使用.

工作原理和复制的方式

1. master(主实例)将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）

2. slave(从实例)将master的binary log events拷贝到它的中继日志(relay log)；

3.slave重做中继日志中的事件，更新数据,完成数据同步

复制方式

https://dev.mysql.com/doc/refman/5.7/en/replication-sbr-rbr.html#replication-sbr-rbr-sbr-disadvantages


复制的方式		基于语句的复制（SBR）基于行的复制（RBR）
简介复制的执行Sql语句(逻辑复制)复制数据
优点1.成熟的技术
2.写入日志文件的数据较少(快地完成从备份中获取和恢复) 传输效率更高
3.日志文件包含进行任何更改的所有语句，因此可用于审计数据库(云数据库的sql审计应该是这样实现的?)		1.可以复制所有更改,安全考高
2.执行效率高,因为复制的是数据,没有消耗系统资源的Sql操作

缺点 安全性差一点,有些特殊语句可能会报错
消耗系统资源,主库的操作需要从库要做一遍,		日志文件大传输效率低,I/O操作耗时高

Mysql支持混合复制模式,动态切换语句复制和行复制

复制拓扑及配置

基本原则

  • Mysql一个备库实例只能有一个主库(只能从一个主库读取binlog)(5.7以后支持多源复制)
  • 每一个备库必须有一个唯一服务器ID(同一网络内)
  • 一个主库实例可以有多个备库(一个备库可以有多个兄弟备库)
  • 开启log_slave_updates 选项 备库可以把它从主库获取的数据变化传递给其它备库(slave 此备库的其它备库)

学习拓扑图是,可以把复制的slave关系理解为面向对象中继承关系

一主多从

常用读写分离,适用于多读少写

双主复制

用于多写场景时,需要更改主键自增策略

其它形式的复制,请参考高性能<<Mysql>>

配置(主从实例)

1.创建复制账号

#创建用户
CREATE USER 'tongbu'@'192.168.%.%' IDENTIFIED BY '123456';
#配置账号同步权限
GRANT REPLICATION SLAVE ON *.* TO 'tongbu'@'192.168.%.%';
#刷新权限
flush privileges

需要在主库和从库 执行上述操作

2更改配置文件

配置 

#服务器id(保证主从的id都是唯一的)
server-id = 1
#开启binlog日志
log_bin = mysql-bin
#混合复制模式,支持语句复制和行复制
binlog_format = mixed
#备库记录更新
log-slave-updates = 1

查看主库日志和位置 show master status\G

MySQL [(none)]> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000320
Position: 154
Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 2d8be082-e379-11e6-9e2c-d89d672bc9d4:1-759145,
48496646-e379-11e6-9e2d-008cfaf6f158:1-4,
7c64616c-23a4-11e7-809c-7cd30abda0c4:1-9629173,
98d692c9-07a7-11e7-8a1b-7cd30abd9f90:1-6919876
1 row in set (0.00 sec)

配置从库监听主库

change master to master_host='192.168.31.106',master_port=3306,master_user='tongbu',master_password='123456',master_log_file='mysql-bin.000320',master_log_pos=154;

master_log_file 和 master_log_pos 要根据主库的信息来设置

 #开启从库
 start slave;
 #查看从库状态
 show slave status\G; 
 #状态为yes表示成功 
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

开启从库root用户远程访问(方便测试时用户端查看数据)

use mysql;
GRANT ALL ON *.* TO root@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
flush privileges;

测试

创建测试数据库 sync_test 刷新从库, 同步成功

注意

开启start slave 之前的数据不会同步,需要手动在从库创建并导入