MySQL的复制功能

什么是复制功能？

MySQL 的复制功能是将备份的数据移动到其他服务器的功能，通过简单的设定即可使用

成都创新互联公司长期为1000多家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为岑溪企业提供专业的成都做网站、成都网站建设，岑溪网站改版等技术服务。拥有十年丰富建站经验和众多成功案例,为您定制开发。

主→从（master ->slave）架构。

主服务器上发生数据变更，变更内容传送到从服务器，从服务器接受主服务器的变更内容，

将变更内容反映到数据库。

复制功能的优点

1. 提高查询性能

查询处理负载高的情况下，可以通过增加从服务器来实现负载均衡，提高性能

2. 实现高可用性

主服务器发生故障时，可以将从服务器升级为主服务器

3. 实现异地复制

构建异地容灾系统

4. 用作备份服务器

在从服务器上进行备份，可以不影响主服务器的性能

例如可以实现主服务器不停机运行，将从服务器的数据库停止以后进行备份

复制功能的组成

主服务器的所有变更点都记录在二进制日志里面。二进制日志里记录了与更新相关的SQL文，执行查询的日期时间等元数据也记录在内。事务提交的同时以二进制的形式进行记录(sync_binlog=1)。

mysqlbinlog 命令可以查看日志内容。指定启动选项进行输出二进制日志。

--log-bin[=file_name]

推荐将输出路径与数据文件路径放在不同的硬盘上。日志文件的扩展名以连号的形式记录

例）file_name-bin.001, file_name-bin.002, 等等。当前正在使用的日志号码会记录在索引文件里面 (file_name.index)

主服务器的所有变更点都记录在日志里面，在从服务器启动复制功能，将二进制日志的内容传送到从服务器后执行。

从服务器上的文件、线程

文件

中继日志文件：记录主服务器变更点的文件。

二进制日志文件：记录从服务器变更点的文件。　(log-slave-updates有效时才会输出)

master.info ：记录连接主服务器的必要信息、读取日志起始位置等信息的文件。 (MySQL 5.6开始可以保存在表内)

relay-log.info ：记录中继日志执行位置的文件。  (MySQL 5.6开始可以保存在表内)

线程

I/O 线程：将从主服务器上接收的二进制日志作为中继日志保存

SQL 线程：将中继日志里面的更新内容反映到DB里面

复制的种类

二进制日志的记录方式不同

STATEMENT ：文(SQL文)

ROW ：行

MIXED ：文和行混合

不确定的SQL文——执行过程中结果可能会发生变化的SQL

UUID() 、UUID_SHORT()

USER()

FOUND_ROWS()

LOAD_FILE()

SYSDATE()

GET_LOCK() 、RELEASE_LOCK()

IS_FREE_LOCK() 、IS_USED_LOCK()

MASTER_POS_WAIT()

SLEEP()

VERSION()

没有排序的LIMIT句

UDF 、非决定性的存储过程/函数

查询INFORMATION_SCHEMA

READ-COMMITTED/READ-UNCOMMITTED

同步方式不同

异步：将变更点异步传送

半同步：将变更点同步传送，向DB反映时候为异步

异步(默认)

异步传送变更点

优点：相比半同期方式，主服务器的更新响应迅速

缺点：主服务器发生故障时，故障发生前的部分内容可能没有传送到从服务器

适用于负载均衡
(故障发生前的数据需要保护的情况下，需要应用程序对应)

半同步(MySQL 5.5开始追加的功能)

变更点同步传送，异步将数据向DB反映

优点：主服务器发生故障时，故障发生前的更新数据可以确保传送到从服务器

缺点：与异步模式相比主服务器的更新响应差

适用于高可用性的运用 (需要保护故障发生前的更新数据)

是否使用GTID

不使用GTID传统的复制模式

使用GTID：MySQL 5.6新追加的模式

多台服务器组成的复制环境里可以很容易的跟踪比较事务

事务在全局里拥有唯一的识别ID，可以记录在二进制日志里面

使用时和传统的方式有变化

复制开始时自动识别位置

故障切换时，可以自动识别最新的从服务器

很容易组成多层复制

GTID 的优点：

可以自动识别日志文件里的位置，不需手动指定
(主服务器发生故障时，无需确认日志的位置可以实现故障切换)

GTID 的缺点：

限制因素

只能使用InnoDB

不能使用“CREATE TABLE ... SELECT“

不能在事务中使用“CREATE TEMPORARY TABLE” 和 "DROP TEMPORARY TABLE"

不支持sql_slave_skip_counter

从服务器需要输出二进制日志

无法使用复制功能的过滤功能，使用过滤功能GTID里面会出现从未使用过的ID

使GTID有效需要全部的服务器停止后再转成GTID模式。

MySQL 5.7 可以每台服务器轮番启动设置GTID

复制功能的设定方法(不使用GTID)

1 ．设定复制功能的参数

2 ．在主服务器上建立复制用的用户

3 ．备份主服务器数据，将其恢复到从服务器，需要记录备份时的二进制日志名称和位置

4 ．从服务器上执行 CHANGE MASTER TO

5 ．从服务器上执行 START SLAVE

主服务器：设置下记选项后启动

server-id

log-bin

datadir *

从服务器：设置下记选项后启动

server-id

datadir *

port *

socket *   (Lunix 系OS)

read_only   ( 推荐设置)

给用户赋予“REPLICATION SLAVE”权限

例

CREATE USER 'repl'@'localhost' IDENTIFIED BY 'repl';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost';

将主服务器的备份数据恢复到从服务器(不使用GTID)

将冷备份的数据恢复

使用mysqldump进行数据备份和恢复

备份

 $ mysqldump --user=root --password=root --master-data=2 \

 --socket=/usr/local/mysql/data/mysql.sock \

 --hex-blob --default-character-set=utf8 --all-databases \

 --single-transaction > mysql_bkup_dump.sql

※需要记录备份的日志文件名称和位置

补充：mysqldump的选项

--master-data=2

将备份时的文件名和位置作为注释记录在备份文件里

--hex-blob

将二进制类型(BINARY、VARBINARY、BLOG) 和BIT类型的数据以16进制输出

--default-character-set

设置mysqldump的默认字符

通常情况下与系统变量default-character-set设置一致

--all-databases

备份全部的数据库

--lock-all-tables

将所有的表加锁后进行备份

--single-transaction

利用InnoDB支持的事务处理，对InnoDB的表进行一致性备份

注意事项：使用mysqldump备份

为了保证数据的完整性，备份中不执行DDL(※)

ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE

引用手册里关于”--single-transaction”的说明

「While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE.

4 ．从服务器上执行 CHANGE MASTER TO

5 ．从服务器上执行 START SLAVE  (不使用GTID)

执行CHANGE MASTER TO

执行START SLAVE

例

CHANGE MASTER TO MASTER_HOST=‘localhost',

 -> MASTER_USER=‘repl',

 -> MASTER_PASSWORD=‘repl',

 -> MASTER_LOG_FILE='bin.000001',

 -> MASTER_LOG_POS=1790;

 START SLAVE;

  复制功能的设定方法(使用GTID)

1 ．设定复制功能的参数

2 ．在主服务器上建立复制用户

3 ．将主服务器的数据备份后恢复到从服务器，无需使用日志的名称和位置

4 ．从服务器执行 CHANGE MASTER TO

5 ．从服务器执行 START SLAVE

1 ．设置复制功能的参数(使用GTID)

主服务器：设置以下选项后启动

server-id

log-bin

datadir *

gtid-mode=on

enforce-gtid-consistency=on

log-slave-updates

从服务器：设置以下选项后启动

server-id

log-bin

datadir *

port *

socket *   ( 使用Lunix)

read_only   ( 推荐设置)

gtid-mode=on

enforce-gtid-consistency=on

log-slave-updates

赋予用户“REPLICATION SLAVE”权限

例

CREATE USER ‘repl'@'localhost' IDENTIFIED BY ‘repl';

GRANT REPLICATION SLAVE ON *.* TO ‘repl'@'localhost';

3 ．将主服务器的数据备份后恢复到从服务器(使用GTID)

冷备份后恢复数据

删除Datadir下面的auto.cnf

( 目的为主从服务器的server-uuid一致(※))

使用mysqldump进行备份和恢复

备份例

 $ mysqldump --user=root --password=root –master-data=2 \

 --socket=/usr/local/mysql/data/mysql.sock \

 --hex-blob --default-character-set=utf8 --all-databases \

 --single-transaction --triggers --routines --events > mysql_bkup_dump.sql

 4 ．从服务器执行 CHANGE MASTER TO

 5 ．从服务器执行 START SLAVE (使用GTID)

例

CHANGE MASTER TO MASTER_HOST=‘localhost',

 -> MASTER_USER=‘repl',

 -> MASTER_PASSWORD=‘repl',

 -> MASTER_AUTO_POSITION=1;

 START SLAVE;

管理日志

使用SHOW MASTER STATUS 确认现在的日志名称和位置

使用SHOW MASTER LOGS 列出全部的日志文件名

使用FLUSH [BINARY] LOGS 命令或者MySQL服务器再启动的时候日志轮换

使用PURGE MASTER 删除特定时点的日志

使用RESET MASTER 删除全部的日志

管理复制功能的命令(从服务器)

START SLAVE [SLAVE_TYPE] 启动从服务器

STOP SLAVE [SLAVE_TYPE] 停止从服务器

SHOW SLAVE STATUS 确认从服务器的状态

确认I/O线程传送二进制日志的位置

确认SQL线程执行的relay日志位置

STOP SLAVE;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

STAT SLAVE; 忽略下一个事务

发生错误时，确认状态以后需要将特定的事务忽略的情况下使用

GTID 模式下该方法不适用

其他注意事项

MySQL 复制自身不具备的功能

高可用时候的故障切换功能

　=>MySQL 5.6的MySQL Utilities提供自动故障切换的脚本

读写分离、负载均衡的控制功能

　=>Connector/J(Java)或mysqlnd_ms(PHP)等可以控制

一次不要执行大量的更新处理

防止从服务器的延迟

主服务器事务提交后，将变更的内容传送到从服务器，如果事务执行时间过长的话，反映到从服务器会产生延迟

监视复制功能

通过SHOW SLAVE STATUS的结果，来监视下面内容

I/O 线程、SQL线程是否正常活动？

I/O 线程 ： Slave_IO_Running

SQL 线程 ： Slave_SQL_Running

复制是否有延迟？

复制是否有延迟 ： Seconds_Behind_Master

二进制日志和中继日志的执行位置等

二进制日志的传送状况 ： Master_Log_File、Read_Master_Log_Pos

中继日志的执行状况 : Relay_Master_Log_File、Exec_Master_Log_Pos

确认网络延时需要在主服务器上执行SHOW MASTER STATUS 来确认。(比较SHOW SLAVE STATUS 的Master_Log_File、Read_Master_Log_Pos)

监视慢查询日志

确认延迟原因和查询执行时间长的原因

主从服务器的磁盘剩余空间

从服务器的磁盘空间减少的话无法删除中继日志，复制功能会停止

主从服务器的资源使用状况

(CPU 、内存、I/O量，网络流量)

复制功能构成高可用性

优点

MySQL 的标准功能不需要使用共享磁盘、软件等等，成本低廉

可以实现高可用性和查询处理的负载均衡

缺点

故障切换需要使用其他方法实现，运用的时候考虑事项较多

发生故障时候，使用什么样的方法进行切换？

发生故障切换的时候，应用程序的连接需要切换

(MySQL 5.5 之前) 没有崩溃安全机制，从服务器发生故障时，需要重新安装从服务器。