MySQL数据库的可扩展与高可用（读书笔记）- 1 如何做到主从复制

复制

概述

MySQL 复制支持两种复制方式：1. 基于行的复制。2. 基于语句的复制。两种方式都是通过主库上的二进制日志在备库上重放。这两种方式都可能造成库之间短时间上的数据不一致。但是复制不是备份也不能够取代备份。

会带来一些额外开销，比如写二进制log和备库读取二进制log的时候带来的IO开销，多个备份线程的开销，还有锁竞争的开销。

复制常见用途

数据分布

基于行的复制会比传统的基于语句的复制带来更大压力。我们可能需要随意的停止或开始复制，并且在不同的地理位置进行远程复制。

负载均衡

对于读数据库操作的负载均衡。通过使用硬编码或者DNS轮询实现。或者用一些网络负载均衡的标准方案。

高可用和故障切换

复制可以避免单点故障，也能够缩短宕机时间。

升级测试

使用一个更高版本的MySQL作为备库，保证升级钱全部实例前查询能够在备库按照预期执行。

复制如何工作

1. 主库把数据更改的记录放到binlog里面
2. 备库将主库的日志复制到自己的中继日志（relay log）
3. 备库读取中继日志的事件，将其重放到备数据库上

在第一步中，MySQL会根据事务的提交顺序来写binlog。在记录完binlog后，主库才会让存储引擎提交事务。

在第二步中，备库启动一个IO线程和主库建立连接，之后主库启动一个特殊的二进制转储线程，这个二进制转储线程会读取主库上的二进制日志中的事件。他不会对事件进行轮询。如果线程追赶上了主库，他将进入睡眠状态，直到主库发送信号量通知其有新的事件产生才会被唤醒。

在第三步中，当SQL追赶上IO的时候，relog已经在系统缓存中，所以开销很低。我们还可以通过设置决定我们是否将relog写进备库的binlog里面。

瓶颈：备库只能串行化执行查询任务。因为只有一个SQL线程来重放中继日志中的事件。

配置复制

步骤分为以下几步：

1. 在每台服务器上创建复制账号
2. 配置主库和备库
3. 通知备库连接到主库并从主库复制数据

创建复制账号

在备库运行IO线程的时候会通过TCP/IP连接，这意味着必须在主库创建一个用户，并赋予其合适的权限。备库I/O线程以该用户连接到主库并且读取binlog。

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
TO repl@'192.168.0.%' IDENTIFIED BY 'p4ssword',;

我们把这个账号限制在本地网络因为这是一个特权账号

提示：复制账户实际上只需要有REPLICATION SLAVE权限，并不一定要每一端服务器都要有 REPLICATION CLIENT 权限。但是我们把两种权限都给了主/备库。两个原因：

1. 用来监控和管理复制的账号都需要REPLICATION CLINET 权限，并且针对这两种目的使用同一账号更加容易。
2. 如果在主库上建立账号，然后从主库将数据克隆到备库的时候，备库也就设置好了——变成主库所需要的配置。这样后续有需要可以方便的交换主备库的角色。

配置主库和备库

首先需要打开二进制日志并指定一个独一无二的服务器ID。在主库的my.cnf文件中增加或者修改如下内容：

log_bin = mysql-bin
server_id = 10

server_id根据需要自行改变。如果之前没有在MySQL的配置文件中指定log-bin选项，就需要重新启动。为了确认二进制日志文件是否已经在主库上创建，使用SHOW MASTER STATUS命令，检查输出是否如下

备库上也需要修改my.cnf文件，增加配置并重启服务器

log_bin = mysql-bin
server_id = 2
relay_log = /var/lib/mysql/mysql-relay-bin
log_slave_updates = 1
read_only = 1

这些选项也并不总是必要的，只有server_id是一定要填写的。在配置文件中relay_log是指定中继日志的位置和命名，而log_slave_updates是允许备库将重放事件记录到自己的二进制文件里面。这样会给备库增加额外工作。所以我们可以根据情况取消此设置。但是不开启的话会碰到一些奇怪的现象，比如当配置错误的时候可能会导致备库数据被修改。

启动复制

这一步是告诉备库如何连接到主库并重放其二进制日志。这一步通过 CHANGE MASTER TO语句配置。通过my.cnf也可以达到同样的效果但是需要重启数据库。下面命令是：

CHANGE MASTER TO MASTER_HOST='server1',
MASTER_USER='repl',
MASTER_PASSWORK='p4ssword',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0;

最后一个参数的意义是binlog是不是要从头读。之后可以通过SHOW SLAVE STATUS来检测复制是否正确执行。

第一行和单数后两行显示当前备库尚未运行。你可能发现日志的开头是4，这是因为0不是日志真正的开头，4才是。之后开始运行命令START SLAVE。

可以看到IO和SQL线程都已经开始工作。

我么还可以通过 SHOW PROCESSLIST来看线程的状态。主库会看到一个IO线程。备库会看到一个SQL一个IO线程。

旧主库与新备库

如果我们有一个全新的备库怎么办？有几种方法：

• 主库复制数据
• 从另外一台备库克隆数据
• 用最近的一次备份

需要有三个条件来让主库和备库同步：

• 某个时间点的主库的数据快照
• 主库当前的二进制日志文件，和获得数据快照时在该二进制日志文件中的偏移量，我们把这两个值称为日志文件坐标。通过这两个值可以确定binlog的位置。可以通过SHOW MASTER STATUS命令来获取这些值。
• 从快照时间到现在的二进制日志。

下面是一些从别的服务器克隆备库的方法

1. 使用冷备份：
   最基本的方法就是关闭主库，把数据复制到备库。重启主库后使用一个新的二进制文件。用 CHANGE MASTER TO 来改变二进制文件的位置

2. 热备份：
   如果仅仅用了MyISAM表，可以在主库运行的时候使用mysqlhotcopy或rsync来复制数据

3. mysqldump
   如果只包含了InnoDB表，那么可以使用以下命令来转储主库数据并将其加载到备库，然后设置相应的二进制日志坐标：

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

选项 --single-transaction 使得转储的数据为事务开始前的数据。如果使用的是非事务型表，可以使用 --lock-all-tables选项来获得所有表的一致性转储。

4. 使用快照或备份
   通过二进制日志坐标和快照或备份来初始化备库。（如果用备份需要确保从备份的时间点开始的主库二进制文件都要存在。

5. 使用Percona Xtrabackup
   这个是一款开元备份工具。它能够在备份时不阻塞服务器的操作，因此可以在不影响主库的情况下设置备库。可以通过克隆主库或另一个已存在的备库的方式来建立新的备库。

6. 使用另外的备库
   可以使用任何一种上面提及的技术。如果用mysqldump， --master-data选项不会起作用。另外不能用 SHOW MASTER STATUS 来获得主库的二进制日志坐标，而是在获取快照时使用SHOW SLAVE STATUS 来获取备库在主库上的执行位置。另外最大的缺点就是如果备库和主库不一致那么拷贝的就是脏数据。

注意： 不能使用 LOAD DATA FROM MASTER 或者 LOAD TABLE FROM MASTER这些命令。十分危险并且仅适用于MyISAM存储引擎。

推荐的复制配置

本小节推荐一种“安全”的配置，可以最小化问题发生的概率。

在主库上二进制日志最重要的选项就是 sync_binlog。如果开启这个选项，MySQL每次在提交事务前会将二进制日志同步到磁盘上，保证在服务器崩溃时不会丢失事件。如果禁止该选项，服务器会少做一些工作，但二进制文件可能在服务器崩溃时损坏或丢失信息。在一个不需要作为主库的备库上，该选项带来了不必要的开销。如果无法容忍服务器崩溃导致的表损坏，推荐用InnoDB。在表损坏无关紧要时，MyISAM是可以接受的。

如果使用InnoDB，我们强烈推荐设置如下：

innodb_flush_logs_at_trx_commit #Flush every log write
innodb_support_xa # MySQL 5.0 and newer only
innodb_safe_binlog # MySQL 4.1 only, roughly equivalent to innodb_support_xa

我推荐明确指定二进制日志的名字，以保证二进制日志名在所有服务器上是一致的。我们需要给log_bin选项指定一个参数。可以随意地给一个绝对路径。
在备库中我们同样推荐给中继日志一个绝对路径：

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

skip_slave_start 可以阻止备库在崩溃后自动启动复制。如果备库重启后开启自动复制并处于不一致的状态，可能会导致更多的损坏，最后不得不把所有数据删除。

read_only之前已经讲过。

目前备库仍然可能在崩溃之后中断，因为master.info和中继日志文件都不是崩溃安全的。默认情况下，甚至不会刷新到磁盘。如果不介意fsync导致的性能开销可以设置一下选项：

sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info =1

如果备库和主库延迟很大，备库IO线程可能会写很多的中继日志，SQL线程在重放完一个中继日志中的事件后会尽快将其删除（通过 relay_log_purge选项控制)。如果延迟严重，可能会把磁盘撑满。我们可以配置relay_log_space_limit。如果所有中继日志大小超过这个值，IO会停止，等待SQL线程释放磁盘空间。