您好,登录后才能下订单哦!
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 之前) 没有崩溃安全机制,从服务器发生故障时,需要重新安装从服务器。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。