mysql 复制建立_Mysql复制搭建

Mysql复制：

1. 复制类型：

1. 逻辑复制：Mysql

记录每次逻辑操作

主从数据库要求可以不一致

2. 物理逻辑复制：Oracle Data Guard，SQL Server Mirroring

记录每次对于数据页的操作

主从数据物理严格一致

基于重做日志

2. 逻辑复制的基础：二进制日志(binary log)

1. mysql开启二进制日志：

log-bin = /data/mha/mysql3306/logs/mysql-bin

binlog_format = row

server-id = 363306

2. 读取二进制日志：

show binary logs：列出当前日志文件及大小

show master status：显示MySQL当前的日志及状态(需要super, replication client权限)

show binlog events in "mysql-bin.00000xx": Mysql的二进制日志是以事件为单位存储到日志中，一个INSERT、UPDATE。。有多个事件组成(Gtid/Query/Table_map/Write_rows/Xid)

mysqlbinlog -v --base64-output=decode-rows mysql-bin.000002

3. 二进制日志的格式：推荐为row格式

* statement: 记录操作的SQL语句；文件小，并非所有语句都可以复制，从系统是可以采用不同行结构的较新版本；INSERT和SELECT要求数量更多的行锁；支持时间点恢复；可以用于迁移或是运维的过渡状态

* row: 记录操作的每一行数据； 文件大，所有语句都可以复制；从系统必须是完全相同的版本和行结构；INSERT、UPDATE和DELETE在从系统上需要较少的锁；支持时间点恢复(由于日志事件是二进制格式，难度更高)；推荐使用的格式

* mixed: 混合statement/row；

4. 日志内容：

等待补充

3. 几种不同的复制方式

1. 传统复制

2. 基于GTID的复制

3. 半同步复制

4. 增强半同步复制

3.1 传统复制

* master主机上会启用一个dump_thread线程来对应一个slave主机

* slave主机启用两个线程来完成复制：

- IO_Thread：注册到master上去获取binlog的事件的变更，通过IO_thread写到本地的relay_log

- SQL_thread：重放relay_log的内容逻辑的进行变更写入slave上

* 复制过程：

1. 间隔时间长的数据：master的dump_thread从磁盘把没有传输的binlog全部读出来告诉IO_thread，IO_thread再一个个拉取过来放到本地；

2. 实时变更的数据：master在接收到日志变更之后，先写入Innodb的redo日志，写完后从redo日志拿到一个prepare_mutex_lock，开始写入binlog日志(先写入缓存)，只要有commit标志(而不一定非要写入磁盘)，就会通知IO_thread有变更；

3. 传统复制不会自动寻找同步点，需要手动记录下`master_log_file`和`master_log_pos`；(可在备份前show master status查看, 可flush logs强制刷新binlog)

3.2 基于GTID的复制

1. GTID介绍：GTID是全局事务编号，在一个复制group中是唯一的；同时开启GTID后，会同事务一起记录到binlog中(gtid_log_event)，用来标识事务；

* GTID的构成：`UUID:Sequence_Number`，Sequence_Number是MySQL内部的事务编号，一个MySQL不会重复的顺序号，保证服务器内惟一；每个MySQL都有一个全局唯一的UUID在`$datadir/auto.cnf`中存储

[root@node6 mha]# cat /data/mha/mysql3306/data/auto.cnf

[auto]

server-uuid=78ad4d54-2c3e-11e8-9251-000c29d30cfc

* gtid_mode：

- OFF：不产生GTID，基于BBinlog+position，Slave也不能接受GTID的日志

- OFF_PERMISSIVE：不产生GTID，但作为slave可以识别GTID事务也可以识别非GTID事务

- ON_PERMISSIVE：产生GTID事务，SLave可以处理GTID事务和非GTID事务

- ON：产生GTID事务，Slave只接受GTID的事务

* MySQL 5.7 GTID会存储到表里(mysql.gtid_executed)

* 支持SLAVE不用开启binlog(log-slave-updates)，但是建议开启

* 如何记录GTID

* 如果开启binlog, 在binlog切换时，会将当前GTID插入到gtid_executed表中

insert into mysql.gtid_executed(UUID,xxx,xxxx)

* 如果没有开启binlog，每个事务提交前，会有一个insert操作

Begin;

[...]

insert into mysql.gtid_executed(UUID,xxx,xxxx) # 隐式MySQL内部添加

* gtid_executed表压缩：

- 控制压缩频率

- mysql> set global gtid_executed_compression_period=N;(默认1000)

* enforce-gtid-consistency

* OFF：不检测是否有GTID不支持的语句/事务

* WARN：当发现不支持语句/事务，返回警告，并在日志中记录警告信息

* ON：当发现语句/事务不支持GTID时，返回错误

* 在线上从非GTID到GTID切换过程中，可以先设置成 WARN

* GTID的限制

* 不能使用 create table ... select...语句

* 事务中更新非事务表：导致故障

- begin; update no_table set col1=xxx where .... commit;

* 事务中创建临时表

* sql_slave_skip_counter不支持

3.3 搭建过程：

1. 主库上备份数据库

mysqldump --master-data=2 --single-transaction -A >db_`date +'%Y%m%d'`.sql

--master-data=2

2. 从库上恢复数据库

[root@node7 backup]# mysqldump --master-data=2 --single-transaction -A -p > zst-37-3306-full_`date +'%Y%m%d'`

SET @@GLOBAL.GTID_PURGED='84a94c78-21f1-11e6-8eef-000c296695fc:1-4'; # GTID复制

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=899; # 传统复制

错误：ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

解决：需要reset master，重建从库

root@localhost [(none)]>reset master;

全备不建议导入mysql库，导入了权限库的话，需要flush privileges

3. 主库上创建并授权一个复制用户的权限

root@localhost [(none)]>create user 'repl'@'%' identified by 'repl4slave';

root@localhost [(none)]>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

root@localhost [zst]>show grants for repl;

+----------------------------------------------+

| Grants for repl@% |

+----------------------------------------------+

| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' |

+----------------------------------------------+

1 row in set (0.02 sec)

4. 从库 CHANGE MASTER TO ... 主库

1. 传统复制：

change master to master_host='MASTERIP',

master_port=3306, master_user="repl", master_password='replpass', master_log_file="mysql-bin.xxxx", master_log_pos=xxxx

2. GTID复制：

change master to master_host='MASTERIP',

master_port=, master_user="repl", master_password='replpass', master_auto_position=1;

5. 启动复制

start slave

6. 检查复制状态

root@localhost [(none)]>show slave status\G;

*************************** 1. row ***************************

...

Slave_IO_Running: Yes # 复制线程，start/stop slave io_thread

Slave_SQL_Running: Yes

...

3.4 传统复制和GITD复制的切换

* 从GTID复制切换到传统复制

1. 停掉GTID复制，禁止master_auto_position

```

STOP SLAVE [FOR CHANNEL 'channel']

CHANGE MASTER TO MASTER_AUTO_POSITION=0,MASTER_LOG_FILE=file, MASTER_HOST='', MASTER_PORT=,MASTER_USER='',MASTER_PASSWORD='' [FOR CHANNEL 'channel']

推荐show slave status\G; 中的 Master_Log_File: mysql-bin.000006

START SLAVE [FOR CHANNEL 'channel']

```

2. 更改日志格式

3. SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE; (从库可以先转)

4. SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

5. SELECT @@GLOBAL.GTID_OWNED;

6. SET @@GLOBAL.GTID_MODE = OFF;

SET GLOBAL enforce_gtid_consistency=OFF;

7. 修改配置文件或注释：

gtid-mode=OFF

enforce_gtid_consistency=OFF

* 从传统复制切换到GTID复制

1. SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN; (先主后从)

2. SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;

3. SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE; (不产生GTID但可以处理，binlog开始切换)

4. SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE; (建议先从库，产生GTID)

5. SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT' (确认连接是否为0，否则不能继续进行)

6. SET @@GLOBAL.GTID_MODE = ON

7. stop slave;

change master to master_auto_position=1;

start slave

8. 修改配置文件或去掉注释

gtid-mode=ON

enforce_gtid_consistency=ON

3.5 半同步复制和增强半同步复制

1. 区别：

* 传统复制：

Master再接收到client写的数据之后，写入存储引擎，再写入binlog(基于事务的话，需要回写到redo-log中)，最后提交磁盘；

Slave通过I/O_Thread取到binlog到本地之后，保存成为relay-log，通过执行relay-log完成数据的同步；

问题：binary-log何时传递到Slave上是无法确定的，不能保证数据的安全；

* 半同步复制：

Master再接收到client写的数据之后，Mysql在经过SQL解析，存储引擎处理之后，写入binlog日志，提交磁盘；在提交磁盘之后，返回给客户端之前等待用户将binlog传递到　SLave上(waiting slave dump)，Slave返回一个响应给Master，Master接收这个响应后，再返回给Client读取；

问题：在commit时，当前会话可能无法读取，但是如果此时(commit后)新连接一个client会话，就已经可以读取到这个数据；此时就无法保证数据已经传递到Slave上；

* 增强半同步复制：

在写入binlog之后，提交磁盘之前等待用户将binlog传递到SLave上(waiting slave dump)，等到Slave的ack回应之后，再进行提交磁盘的动作；

创建单独的应答接收线程，变成双工模式，发送和接受互不影响

2. 当Master有多个Slave节点时，Master可设置在接收到N个SLAVE应答后，才commit事务

mysql>set global rpl_semi_sync_master_wait_for_slave_count=2;

3. 低于5.7的版本，在从库关闭后一段时间内，刚启动时，注意先使用异步复制，复制追上后，在用半同步复制

- master: set global rpl_semi_sync_master_enabled=ON|OFF

- slave: set global rpl_semi_sync_slave_enabled=ON|OFF

4. 增强半同步Master Crash Recovery

- 扫描最后一个Binlog 提取其中的 Xid (如果事务binlog中记录下了xid，就可以任务事务是安全的)

- 扫描InnoDB维持状态在Prepare的事务链表，和binlog中的Xid比较，如果Binlog中存在，则提交，否则回滚事务

- 提交到Innodb中处于Prepare，并且写入Binlog的，就可以从崩溃中恢复事务

5. 半同步复制的搭建

- 安装模块

root@localhost [(none)]>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

Query OK, 0 rows affected (0.03 sec)

root@localhost [(none)]>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

Query OK, 0 rows affected (0.01 sec)

root@localhost [(none)]>show plugins;

......

| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |

| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |

+----------------------------+----------+--------------------+--------------------+---------+

# vim /data/mysql/mysql3306/conf/my.cnf

rpl_semi_sync_master_enabled=1

rpl_semi_sync_master_timeout=1000 # 1 Second

rpl_semi_sync_slave_enabled=

- 启动复制

start slave

# 如果是修改已经建好的复制结构

stop slave io_thread

start slave io_thread