一、概述
复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。MySQL支持两种复制方式:基于行的复制和基于语句的复制。
基于语句的复制(也称为逻辑复制)早在MySQL3.23版本中就存在,而基于行的复制方式在5.1版本中才被加进来。
新版本的服务器可以作为老版本服务器的备库,但反过来是不可行的。
通过复制可以将读操作指向备库来获得更好的读扩展,但对于写操作,除非设计得当,否则并不适合通过复制来扩展写操作。在一主多备的架构中,写操作会被执行多次,这时候这个系统的性能取决于写入最慢的那部分。
复制比较常见的用途:
数据分布、负载均衡、备份、高可用性和故障切换、MySQL升级测试。
复制的三个步骤:
1、在主库上把数据更改记录到二进制日志(Binary Log)中。
2、备库将主库上的日志复制到自己的中继日志(Relay Log)中。
3、备库读取中继日志中的时间,将其重放到备库数据上。
MySQL的复制架构有一个弱点:在主库上并发运行的查询在备库只能串行化执行,因为只有一个SQL线程来重放中继日志中的事件。
二、配置复制
为MySQL服务器配置复制非常简单,分为以下几步:
- 在每台服务器上创建复制账号。(严格来讲不是必须每台,但推荐这么做)。
- 配置主库和备库。
- 通知备库连接到主库并从主库复制数据。
1、创建复制账号
给复制线程特殊权限
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'192.168.0.%' IDENTIFIED BY 'p4ssword';
我们在主库和备库创建该账号。
注意:
- 把账号限制在本地网络,因为这是一个特权账号。
- REPLICATION SLAVE是全局权限,不能限定某个数据库或表。过滤备份用其他方法,后面会介绍到。
- 实际上主库只需要REPLICATION SLAVE权限,并且不是每一端服务器都需要REPLICATION CLIENT权限,这么配置时方便出现问题时,主备互换角色。
2、配置主库和备库
主库:
在my.cnf文件中增加或修改如下内容:
log_bin=mysql-bin
server_id=10
实际值由你决定。
必须明确指定一个唯一的服务器ID。通常的做法是使用服务器IP地址的末8位。重启MySQL服务。
可以使用SHOW MASTER STATUS命令检查输出是否如下:
文件名和后面的数字可能有所不同。positon是当前日志文件的位置。
备库:
在my.cnf中增加类似的配置,并重启服务:
log-bin=mysql-bin
server_id=2
relay_log=/var/lib/mysql/mysql-relay-bin
log_slave_update=1
read_only=1
从技术上来说,有些选项并不总是必要的。但推荐这样配置。
read_only可以阻止没有特权的线程修改数据。
其他推荐配置:
主库:
sync_binlog=1 #证服务器崩溃时不会丢失事件。
innodb_flush_log_at_trx_commit=1 #保证每个commit都及时写入日志
innodb_support_xa=1 #MySQL5.0及以后版本的配置,支持跨存储引擎及二进制日志事务
log_bin="/var/lib/mysql/mysql-bin" #明确指定日志文件位置,对多台服务器的管理有帮助
备库:
relay_log="path/to/logs/relay-bin" #指定明确日志位置,可以避免多版本MySQL复制的bug
skip_slave_start #避免崩溃后自动启动日志,给修复赢取时间
如果使用MySQL5.5并且不介意额外的fsync()的性能开销,最好设置一下选项:
sync_master_info=1
sync_relay_log=1
sync_relay_log_info=1
3、启动复制
告诉备库连接到主库,这一步不要通过修改my.cnf来配置,而是使用CHANGE MASTER TO语句。
mysql> CHANGE MASTER TO MASTER_HOST='server1',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='p4ssword',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=0;
MASTER_LOG_FILE和MASTER_LOG_POS是主服务器上SHOW MASTER STATUS命令看到的文件和位置。位置设置成0,即从日志头开始复制。
上面的命令是连接主备库,下面是开始复制的命令:
mysql> START SLAVE;
可以通过SHOW SLAVE STATUS;查看备库复制情况。
4、从已有数据的数据库复制
上面的步骤是主库和备库都是新库,在都没有数据是,先配置复制。如果从一个已有数据的服务器复制,先要将备库和主库的数据进行同步。
需要三个条件让主库和备库保持同步:
- 在某个时间点的主库的数据快照。
- 主库当前二进制日志文件,和获得数据快照时在该二进制日志文件中的偏移量,称为日志文件坐标。可以通过SHOW MASTER STATUS命令获取这些值。
- 从快照时间到现在的二进制日志
- 使用冷备份
- 使用热备份
- 使用mysqldump
- 使用Percona Xtrabackup
- 使用MySQL Workbench 的utilities工具集
工具集中有很多有用的工具,其中mysqldbcopy.exe或mysqldbcopy.py可以用来备份数据。
下面这个命令可以从主库备份数据到备库,备份后,自动启动备库的复制线程。
$ mysqldbcopy --source=root@localhost:3310 --destination=root@localhost:3311 test123 --rpl=master --rpl-user=rpl
5、复制过滤器
一般主库记录所有内容,使用备库来进行过滤。
在备库上,可以通过设置下列replicate_*的选项从中继日志中读取事件时进行过滤。
replicate_do_db
replicate_do_table
replicate_ignore_db
replicate_ignore_table
replicate_rewrite_do
replicate_wild_do_table
replicate_wild_ignore_table
三、复制拓扑
可以在任意主库和备库之间建立复制,记住下面的旗本原则:
- 每一个备库只能有一个主库。
- 每个备库必须有一个唯一的服务器ID
- 一个主库可以有多个备库(或一个备库可以有多个兄弟备库)
- 如果打开了log_slave_updates选项,一个备库可以把其主库上的数据变化传播到其他备库。
1、一主库多备库
一主多备的结构和基本配置差不多简单,因为备库之间根本没有交互,它们仅仅是连接到同一个主库上。
这种结构非常灵活,有一下一些用途:
为不同的角色使用不同的备库
把一台备库当做代用的主库,除了复制没有其他数据传输。
将一台备库放到远程数据中心,用作灾难恢复。
延迟一个或多个备库,以备灾难恢复。
使用其中一个备库,作为备份、培训、开发或者测试使用服务器。
2、主动-被动模式下的主-主复制
简单来讲就是两台服务器互为对方的备库,并确保只有一台服务器可写。
在两台服务器上执行如下设置后,会使其拥有对称设置:
- 确保两台服务器上有相同的数据。
- 启用二进制日志,选择唯一的服务器ID,并穿件复制账号。
- 启用备库更新的日志记录,这是故障转移和恢复的关键。
- 把被动服务器配置成只读。
- 启动每个服务器的MySQL实例。
- 将每个主库设置为对方的备库,使用新常见的二进制日志开始工作。
这种结构某种意义上类似于创建一个热备份,但可以使用这个“备份”来提供性能,比如用它来执行读操作、备份、“离线”维护以及升级等。真正的热备份做不了这些事。
因为配置是几乎是相同的,所以很容易让主动库和被动库互换角色,这使得故障转移和故障恢复很容易。
切换角色的步骤:
- 停止主动服务器上的所有写入。
- 在主动服务器上执行SET GLOBAL read_only=1,同时在配置文件里也设置一个read_only,防止重启后失效。但记住这不会阻止拥有超级权限的用户更改数据。如果想阻止所有人更改数据,可以执行FLUSH TABLES WITH READ LOCK。如果没有这么做,你必须kill所有的客户端连接以保证没有长时间运行的语句或者未提交的事务。
- 在主动服务器上执行SHOW MASTER STATUS并记录二进制日志坐标。
- 使用主动服务器上的二进制日志坐标在被动服务器上执行SELECT MASTER_POS_WAIT()。该语句将阻塞住,知道复制跟上主动服务器。
- 在被动服务器上执行SET GLOBAL read_only=0,这样就变换成主动服务器。
- 修改应用的配置,使其写入到新的主动服务器中。