MySQL 的复制功能

MySQL 的 主从 复制来源于其特有的词汇:复制,即 Replication。因此在 MySQL 的语境中,提到的复制主要是指这个复制,而不是通常意义的 copy。

通过复制,可以让数据从一个 MySQL 数据库服务器被复制到另一个或多个 MySQL 数据库服务器中,前者称为 主服务器,即 Master,后者称为 从服务器,即 Slaves,政治正确的说法是 Replica。这就是所谓的主从

为了叙述方便,本文中经常会把主服务器简称为 “”,把从服务器简称为 “”,颜色加深。

复制默认是 单向异步 进行的,因此,从服务器无需为了收到更新而一直保持连接。这就意味着更新是可以在远距离连接上进行的,甚至可以在临时的或间断性的连接上进行,如拨号连接。根据配置,可以复制全部数据库、部分数据库、或库中指定的表。

复制的优点

MySQL 内置的复制功能,配合使用这种可扩展的架构,是建设大型、高性能应用的基础。这种 主从 复制不仅仅有利于高性能的应用程序,它还是许多策略的基石,包括:高可用、可扩展性、灾难恢复、备份、数据分析、数据仓库等任务。

  • 可扩展解决方案:把负载分摊到多个从服务器上,从而提升性能。在该环境下,所有的 更新 操作必须发生于 服务器上,即主服务器专用于更新。而 操作,可以发生在一个或多个 服务器上。这种模式一方面会提升写的性能,另一方面,不断增长的从服务器还会大大提升读的速度。

  • 数据安全:因为数据被复制到从服务器,而且从服务器可以暂停复制的进程,因此,可以做到在从服务器上运行 备份 服务,同时还不会破坏对应的主服务器上的数据。

  • 数据分析:可以在主服务器上创建实时数据,而对信息的分析可以发生在从服务器,不会影响主服务器的性能。

  • 远距离数据分布:如果由于工作需要,公司在远方的分部门需要主数据的一个副本,可以利用复制在本地创建一个副本,而无需与主服务器保持持续的连接。

复制的分类

在两个服务器之间配置复制有很多种办法,但最好的办法取决于使用的数据以及引擎的类别。

MySQL 复制的格式有两个核心的类别:

  • 基于 语句 的复制:SBR,Statement Based Replication。也称逻辑复制,它会复制全部 SQL 语句。启用于 MySQL 3.23 版本。
  • 基于 的复制:RBR,Row Based Replication。只复制发生修改的行。启用于 MySQL 5.1 版本。

为了叙述的简便和阅读的方便,下文经常把 “基于行的复制” 简称为 “行复制”,把 “基于语句的复制” 称为 “逻辑复制”,仅限本篇文章。

传统的 基于二进制日志的复制

早期的复制是基于主服务器的二进制日志,其中保存了主服务器对数据库所做的修改,从服务器读取这些日志,据此来在自己本地进行同样的操作,实现更新。以上所述的 SBR 和 RBR 均属于这种,均是异步完成的,意味着有可能 主从 之间会存在数据同步延迟。

主服务器将更新写入 二进制日志文件,并维护文件的一个索引,以跟踪日志循环。当从服务器连接主服务器时,它会把自己在日志中读取的最后一次成功更新的位置告知主服务器。从服务器接收该位置之后发生的任何更新,然后封锁,等待主服务器通知新的更新。

较新的 基于 GITD 的复制

在 MySQL 5.6.5 开始及之后的版本中,支持事务复制,是基于全局事务标识符,即 GITD,Global Transaction IDentifier。使用这类的复制时,无需直接处理日志文件或其字节偏移量,极大简化了许多日常的复制任务。因为使用 GTID 进行复制是完全事务性的,只要在主服务器上提交的所有事务都已应用于从服务器,则可以保证 主从 的一致性。

复制所解决的问题

复制通常用于以下方面:

数据分布

MySQL 的复制通常对带宽不算敏感,因此可以用来远距离维护数据的副本,例如在不同的数据中心之间同步数据。

负载平衡

MySQL 的复制有助于把读请求分配到多个服务器上,这一点对于读敏感的应用来说特别好用。对代码做简单的修改就可以实现基本的负载平衡。无论是简单的轮询 DNS,还是更复杂的方法,都可以在 MySQL 服务器上很好地工作。

备份

主从 分开非常有利于备份的稳定进行。

高可用 与 失效切换

复制还有助于避免单点失效的发生,失效切换系统如果使用复制,可以大幅减小当机时间。

MySQL 升级前测试

通常会在从服务器上进行新版本 MySQL 的测试,以确保所有查询都能正常工作,之后再升级所有实例。

复制的工作原理

MySQL 是如何复制数据的

从高层级来看,复制的进程包含三部分:

  1. 主服务器把修改保存到二进制日志中,这些记录称为二进制日志事件。
  2. 从服务器把主服务器的日志事件复制进自己的中继日志,即 relay log。
  3. 从服务器重现中继日志中的事件,把修改应用到自己的数据中。

image-center

主服务器保存二进制日志

主服务器上,MySQL 在每个更新数据的事务开始之前,会 提前 把即将发生的修改记录到二进制日志里。写入日志的操作是 串行 的,即使如果事务中的语句在执行过程中是交错进行的。写完以后,主服务器会告诉存储引擎去提交事务。

从服务器拷贝二进制日志

从服务器启动一个工人线程,I/O slave,它会与主服务器打开一个普通的客户端连接,然后在主服务器上启动一个特殊的线程,binlog dump 线程,它会从主服务器的二进制日志中 读取事件。它并不会对事件进行轮询。如果发现已经同步,它会进入休眠,等待产生新事件时,主服务器向其发送信号。然后,I/O 线程把读取到的事件写入从服务器的中继日志。

事件重演

SQL slave 线程从中继日志中读取并重演事件,从而更新从服务器的数据,与主服务器完全匹配。只要 SQL slave 线程能与 I/O 线程保持同步,中继日志通常会保持在操作系统的缓存中,因此其开销很少。如果有需要,可以把 SQL 线程所执行的事件保存到从服务器自己的二进制日志中。

在从服务器上,这种复制的架构 拆分 了获取与重演事件的过程,从而可以实现二者的 异步。即,I/O 线程可以独立于 SQL 线程运行。同时,复制的进程也受到一定的约束,其中最重要的约束就是:复制在从服务器上是串行的。这就意味着即使在主服务器上可以并行用多个线程同时更新,在从服务器上却无法实现并行,因为所有更新都是在一个线程中执行的。这一点对于许多工作负载来说是个瓶颈,需要另外解决,不过对于大多数用户来说,一个线程就够用了。

复制的配置

虽然复制的配置相当简单,但在基本步骤上还有很多变化,取决于具体的实施场景。最基本的场景是新安装的 主从 服务器。从高层级来看,可以分为以下步骤:

  1. 在每个服务器上配置复制的帐户
  2. 配置 主从 服务器
  3. 从服务器连接到主服务器进行复制

这是在假设默认配置就能满足工作的基础上的,事实也是如此。新安装了 主从 服务器时,它们拥有相同的数据,即默认的 mysql 数据库。

创建复制帐户

MySQL 有几个特殊的权限是用于复制进程的,从服务器上的 I/O 线程会与主服务器创建一个 TCP/IP 连接。因此,必须在主服务器上 创建 一个用户帐户,赋予 适当 权限,这样,I/O 线程才可以用该用户的身份连接过去,读取二进制日志。

创建帐户 repl,并为其赋予权限:

mysql> CREATE USER 'repl'@'192.168.0.%' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
-> TO 'repl'@'192.168.0.%';

在 主从 服务器上都要创建同样的用户。

配置主从

主服务器

需要在主服务器上 启用二进制日志,并 指定服务器 ID

$ cat /etc/my.cnf

log_bin=/var/log/mysql/mysql-bin.log
server_id=10

server_id 必须是唯一的,通常避免用 1,因为 1 是默认值,避免混淆。通常会使用该服务器 IP 地址的最后一段。

配置好以后需要 重启 mysqld,然后登陆进去,查看以 确认 二进制日志已经生成:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      634 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从服务器

从服务器上需要进行类似的配置,同样也需要重启 mysqld。

$ cat /etc/my.cnf

log_bin=/var/log/mysql/mysql-bin.log
server_id=2
relay_log=/var/log/mysql/mysql-relay.log
log_slave_updates=1

其中 log_slave_updates 表示把复制过来的事件也更新到自己的二进制日志中。

启动从服务器

下一步就是告诉从服务器如何连接到主服务器,并开始对二进制日志进行重演。

指定主服务器

此时不要使用配置文件来做,而需要使用 CHANGE MASTER TO 语句,它会覆盖配置文件中对应的设置。

运行以下命令,启动复制

mysql> CHANGE MASTER TO MASTER_HOST='server1',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='password',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=0;

server1 为主服务器的主机名

MASTER_LOG_POS=0 因为这是日志的开头

查看从服务器状态

运行以上命令以后,就可以查看从服务器的状态了,从而确认配置是否正确:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***********
Slave_IO_State:
Master_Host: server1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
...omitted...
Seconds_Behind_Master: NULL

\G 是语句结束符,此处用它来代替 ;,可以获得可读性更好的返回结果。

注意,此时 I/O 和 SQL 线程都尚未启动,其运行状态均为 NO。Seconds_Behind_Master 此时是 NULL,意味着不知道自己与主服务器的数据的更新是否有延迟。

启动复制

mysql> START SLAVE;

再次查看状态

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ******************
Slave_IO_State: Waiting for master to send event
Master_Host: server1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 164
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 164
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...omitted...
Seconds_Behind_Master: 0

此时,I/O 和 SQL 线程均已启动,Seconds_Behind_Master 不再是 NULL,I/O 线程等待主服务器的事件,意味着从服务器已经同步了主服务器的二进制日志。日志的位置已经增加,表明取回了一些事件并执行了。如果在主服务器上发生了修改,在从服务器上也会看到相应的变化。

在主服务器上查看 Binlog Dump 线程

在主服务器上,可以看到由 I/O 线程创建的连接,即 Binlog Dump 线程:

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 55
User: repl
Host: replica1.webcluster_1:54813
db: NULL
Command: Binlog Dump
Time: 610237
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
在从服务器上查看 I/O 线程 和 SQL 线程
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 611116
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 33
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL

从服务器上的 I/O 和 SQL 这两个线程始终是用 system user 帐户来运行的。当 SQL 线程重演事件时,Info 字段会显示当前执行的查询内容。

在另一个服务器上初始化从服务器

实际生产中往往不会使用新装的服务器来配置,往往是运行了好久的服务器,通常在 主从 服务器上的数据,一开始也不会同步。

初始时把数据从主服务器复制到从服务器有很多方法:直接复制,从另一个从服务器复制,用最近的备份开始复制。

总之,要想在初始化时让从服务器的数据与主服务器同步,需要三样东西:

  • 主服务器某个时间点的快照
  • 主服务器当前的日志文件,以及在所采用快照其时间点上该日志的字节偏移量,用 log file coordinates 来表示这两个值,它们标识了二进制日志中的一个具体的位置。可以用 SHOW MASTER STATUS 来查看这两个值。
  • 主服务器的二进制日志文件,从快照时间点一直到现在的内容

从其它服务器复制数据的方法

离线复制

生成从服务器最基本的方法:

  • 关闭主服务器,把其中的文件复制到从服务器
  • 重启主服务器,开始一个新的二进制日志
  • 在从服务器上配置 CHANGE MASTER TO,从二进制日志的开头开始复制
在线复制

如果只使用 MyISAM 表,可以用 mysqlhotcopyrsync 来在线复制文件,无需关闭主服务器。

使用 mysqldump

如果只使用 InnoDB 表,可以用以下命令从主服务器上复制所有内容,将其加载到从服务器中,并把从服务器的坐标修改到主服务器二进制日志的对应位置上:

$ mysqldump --single-transaction --all-databases --master-data=1 --host=server1 \
| mysql --host=server2

--single-transaction 选项会让复制进程以数据在事务开始时的状态来读取它们,如果没有使用事务性表,则可以使用 --lock-all-tables 选项来锁定,从而得到所有表的一致的复本。

使用快照或备份

只要知道对应的二进制日志的坐标,就可以使用快照或备份从主服务器中初始化复制。直接把备份或快照恢复到从服务器上,然后在 CHANGE MASTER TO 中使用适当的二进制日志坐标。

从其它从服务器

还可以用上面提到的快照及其它方法,从另一台从服务器上来复制数据。但是,如果使用 mysqldump--master-data 选项是不起作用的。

另外,需要使用 SHOW SLAVE STATUS 来查看具体的位置,即该快照建立时,从服务器正在执行哪些事件。

从另一个从服务器复制数据的最大缺点:如果这个从服务器没有完成同步,则再次生成的从服务器得到的数据一样是没有同步的。

绝对不要使用 LOAD DATA FROM MASTERLOAD TABLE FROM MASTER,这些语句已经淘汰了,经们不仅运行缓慢,还非常危险,而且只适用于 MyISAM。

推荐的复制配置

强制日志刷新到磁盘

主服务器 上,关于二进制日志最重要的设置是 sync_binlog

sync_binlog=1

该配置让 MySQL 每次提交一个事务时,都会把二进制日志的内容 强制同步到磁盘 中,这样可以避免突然的崩溃导致日志事件的丢失。该配置只适用于主服务器,因为该配置在从服务器上会产生不必要的开销。它只适用于二进制日志,不适用中继日志。

使用 InnoDB

如果无法忍受系统崩溃导致表被破坏,还是推荐使用 InnoDB。MyISAM 的表在系统崩溃之后容易出现不一致。如果在一个或多个表中没有执行完语句,那么即使在修复了表之后数据也会不一致,这一可能性是很大的。

如果使用 InnoDB,则强烈推荐在 主服务器 上使用以下配置:

innodb_flush_logs_at_trx_commit=1   # 每次提交后均刷新日志
innodb_support_xa=1   # MySQL 5.0 及以后版本适用
innodb_safe_binlog   # 仅 MySQL 4.1 适用

这些配置是 MySQL 5.0 及以后版本的默认设置。

指定统一的日志文件名

建议显式指定二进制日志文件的 基准名,以便在 所有服务器 上创建 统一的 二进制日志文件名。

log_bin=/var/log/mysql/mysql-bin.log

指定从服务器的中继日志

在从服务器中配置以下参数,建议用绝对路径指定中继日志。

relay_log=/path/to/logs/relay-bin
skip_slave_start
read_only

relay_log 选项目的是为了避免默认使用主机名做中继日志的文件名。

skip_slave_start 是为了避免从服务器在经历崩溃后自动启动,以便给管理员修复的机会。

read_only 选项可以避免大多数用户修改非临时表。只允许 SQL 线程修改,以及少数具有 SUPER 权限的用户。因此要尽量避免给普通帐户分配 SUPER 权限。

把中继日志和 master.info 强制同步到磁盘

即使启用了以上所有配置,从服务器在经历崩溃后还是容易发生损坏,因为中继日志和 master.info 文件无法抵御崩溃。它们甚至默认不会被同步到磁盘,直到 MySQL 5.5 版本才有对应的配置选项来控制该刷新行为。

如果使用的是 MySQL 5.5 以后的版本,而且不在意额外的 fsync() 调用所产生的性能开销,则建议以下配置:

sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1