ITPub博客

首页 > 数据库 > MySQL > MySQL的复制功能

MySQL的复制功能

原创 MySQL 作者:yoyobabadalian 时间:2018-09-27 14:51:13 0 删除 编辑

什么是复制功能?

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

主→从(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 之前) 没有崩溃安全机制,从服务器发生故障时,需要重新安装从服务器。

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31549334/viewspace-2215034/,如需转载,请注明出处,否则将追究法律责任。

上一篇: MySQL 8.0的新特征
请登录后发表评论 登录
全部评论

注册时间:2018-08-09

  • 博文量
    3
  • 访问量
    2233