淘宝的MySQL博客月报首页

第十章复制

10.2配置复制

主主及主从示例：

1.两主互为主从
（1）在主1上执行
grant replication slave on *.* to slave@'10.161.0.168' identified by 'lemon123';
（2）在主2上执行
CHANGE MASTER TO MASTER_HOST='10.161.0.145',MASTER_USER='slave',MASTER_PASSWORD='lemon123',MASTER_LOG_FILE='mysql-bin.000020',MASTER_LOG_POS=107;
start slave;
（3）在主2上执行
grant replication slave on *.* to slave@'10.161.0.145' identified by 'lemon123';
（4）在主1上执行
CHANGE MASTER TO MASTER_HOST='10.161.0.168',MASTER_USER='slave',MASTER_PASSWORD='lemon123',MASTER_LOG_FILE='mysql-bin.000021',MASTER_LOG_POS=107;

2.主从复制
（1）在主1上执行
grant replication slave on *.* to slave@'10.161.0.164' identified by 'lemon123';
grant replication slave on *.* to slave@'10.161.0.165' identified by 'lemon123';
grant replication slave on *.* to slave@'10.161.0.169' identified by 'lemon123';

（2）在三个从库上分别执行
CHANGE MASTER TO MASTER_HOST='10.129.150.89',MASTER_USER='slave',MASTER_PASSWORD='lemon123',MASTER_LOG_FILE='mysql-bin.000020',MASTER_LOG_POS=261;

10.2.1创建复制账号

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

10.2.2配置主库和备库

在主库的my.cnf文件中增加或修改如下内容：

log_bin = mysql-bin 
server_id = 10

不要在my.cnf中设置master_port或master_host这些选项，它们已被废弃，只会导致问题没有好处重启后检查主库状态：

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

在备库的my.cnf文件中增加或修改如下内容（事实上只有sever_id是必须的）：

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

其中

relay_log: 允许备库将其重放的事件也记录到自身的二进制日志中
read_only: 如果可能的话最好使用read_only选项，该选项会阻止任何没有特权权限的线程修改数据。但不是很实用，尤其是那些需要在备库建表的应用

10.2.3启动复制

连到主：

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

启动：

START SLAVE;

查看状态：SHOW SLAVE STATUS\G;

结果

查看复制线程列表：SHOW PROCESSLIST\G

查看线程列表

10.2.5推荐的复制配置

如果用的InnoDB强烈推荐如下配置（这些都是默认配置）：

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

主库(一定要指定日志文件的绝对路径)：

log_bin=/var/lib/mysql/mysql-bin # Good; specifies a path and base name

#log_bin # Bad; base name will be server’s hostname

备库(一定要指定中继日志的绝对路径)：

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

skip_slave_start

read_only #除了复制线程和其它超级线程之外不能修改

如果正在使用MySQL5.5并且不介意额外的fync()导致的性能开销，最好设置如下选项：

sync_master_info = 1

sync_relay_log = 1

sync_relay_log_info = 1

10.4复制拓扑

记住下面的基本原则：

一个MySQL备库只能有一个主库
每个备库必须有一个唯一的服务器ID
一个主库可以有多个备库
如果打开了log_slave_updates选项，一个备库可以把其主库上的数据变化传播到其他备库

10.4.8定制的复制方案

创建日志服务器: 日志服务器不需要任何数据，它的目的仅仅是将数据提供给别的数据服务器; 它更加容易重放且/或者过滤二进制日志事件，它对崩溃后重启恢复很有帮助。同时对基于时间点的恢复也很有帮助，在第15章我们会讨论。; 可以从mysqlsandbox 上获得一个沙箱脚本来创建日志服务器。

示例，假设有 somelog-bin.000001, somelog-bin.000002 两个日志文件。把它放在MySQL日志文件夹中，如/var/log/mysql，然后编辑my.cnf：

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

log_bin_index = /var/log/mysql/somelog-bin.index

我们再来创建日志索引文件（然后启动日志服务器并通过 SHOW MASTER LOGS 来确保其找到日志文件）：

# /bin/ls −1 /var/log/mysql/somelog-bin.[0-9]* > /var/log/mysql/somelog-bin.index

10.5复制和容量规划

10.5.2备库什么时候开始延迟

监控方式: 如果有复制延迟的曲线图则要观察图上的一些短暂的延迟骤升
测试法: 如果将备库停止一个小时，然后开启并在1个小时内赶上，说明只消耗了一半的容量。也就是说备库在一个小时内赶上了两个小时内所有的变更

10.6复制的管理和维护

10.6.1监控复制

在主库上可以使用 SHOW MASTER LOGS 命令来查看当前主库的二进制日志位置和配置（该命令用于给PURGE MASTER LOGS命令决定使用哪些参数）

SHOW MASTER LOGS

此外还可以通过SHOW BINLOG EVENTS来查看复制事件：

SHOW BINLOG EVENTS IN 'mysql-bin.000223' FROM 13634\G

10.6.2测量备库延迟

Seconds_behind_master: SHOW SLAVE STA TUS中的Seconds_behind_master 可以显示延迟，但不准确。
heartbeat record: 最好的方式是使用heartbeat record; 这是一个在主库上每秒执行一次的时间戳。延时 = 备库当前时间戳 - 心跳时间翟; pt-heartbeat 脚本是复制心跳最流行的一种实现

10.6.3确定主备是否一致

可能使用工具：pt-table-checksum
利用pt-table-checksum做主从一致性校验

通常情况下可以在主库上运行该工具：

$ pt-table-checksum --replicate=test.checksum <master_host>

该命令会检查所有的表，并将结果插入到test.checksum 中。当查询在备库执行完就可以简单的比较主备之间的不同了。pt-table-checksum能够发现服务器所有的备库，在每台备库上运行查询，并自动输出结果

10.6.4从主库重新同步备库

最简单的办法是mysqldump转储受影响的数据并重新导入
但问题多多尤其在一个繁忙的数据库里；或从一百万行找出一千行的不同，是非常浪费资源的

pt-table-sync: 可以解决该问题。该工具能高效地查找及解决服务器间的不同; 它是结合pt-table-checksum生成的checksum表来工作的，所以只能同步那些已知的不同步的表的数据块

10.6.5改变主库

整个过程最困难的是获取新主库上合适的二进制日志的位置，这样才可以从和老主库相同的逻辑位置开始复制

计划内提升

RESET MASTER 和RESET SLAVE

计划外提升

确定期望的日志位置

如果有备库和新主库的位置不相同，则需要找到该备库最后一条执行的事件在新主库的二进制日志中相应的位置，然后执行CHANGE MASTER TO 。可以通过mysqlbinlog工具来找到备库执行的最后一条查询，然后在主库找到同样一条查询，进行简单的计算即可得到。示例命令如下（不需要用户名密码）：

 mysqlbinlog   mysql-bin.000011 | tail -n 30

mysqlbinlog

例子——提升server2为新主库：
假设server1是server2和server3的主库，其中server1已经崩溃。根据SHOW SLAVE STATUS获得Master_Log_File/Read_Log_Pos的值，server2已经执行完server1的所有二进制日志，但server3还不是最新的数据。如下图所示：

当server1崩溃，server2已追赶上，但server3的复制落后

根据以上结果，server2已经跟执行完主库上所有二进制日志，但server3还不是最新数据
应该在server3的CHANGE MASTER TO语句赋予一个什么样的参数呢？server3在1493停止，比server2的1582要小89字节。server2正在向偏移量为8167的二进制日志写入，8167-89=8078，因此理论上我们应将server3指向server2的日志偏移量为8087的位置。最好去确认下这个位置附近的日志事件（使用mysqlbinlog）
假设我们观察到事件是一样的，下面这条语句可以完成从server3到server2的切换：CHANGE MASTER TO MASTER_HOST="server2", MASTER_LOG_FILE="mysql-bin.000009", MASTER_LOG_POS=8078;

10.6.6在一个主主配置中交换角色

master_pos_wait函数与MySQL主从切换

10.7复制的问题和解决方案

mysql开启二进制日志记录文件简介： MySQL的二进制日志

10.7.1数据损坏或丢失的错误

主库意外关闭: 如果没有设置sync_binlog，当主库故障重启后可能在二进制日志里丢失一些事务。这时解决方案是从备库的下一个二进制日志的开头读日志。但一些事件会永久性的丢失。; 建议使用pt-table-checksum工具检查主备一致性，以便于修复。可以通过开启sync_binlog来避免事件丢失，innodb_flush_log_at_trx_commit没有设置为1也可能导致数据丢失（但不会损坏）
备库意外关闭: 可能会导致同步信息没来得及写入master.info，备库可能桧重新尝试一些二进制日志事件，可能导致唯一索引错误。唯一的办法就是忽略这些错误; 可以用pt-slave-restart来帮助完成这一点

主库上的二进制文件损坏: 除了忽略，别无选择。可以在主库上执行 FLUSH LOGS 命令，这样主库会开始一个新的日志文件，然后将备库指向该文件开始的地方。; 也可以尝试去发现损坏区域的结束位置。某些情况下可以通过 SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 来忽略一个损坏的事件。如果有多个损坏的事件，就需要重复该事件。但如果有太多损坏的事件，损坏的事件头会阻止服务器去找到下一个事件。这种情况下，可能不得不手动找到下一个完好的事件
备库上的二进制文件损坏: 可以重新CHANGE MASTER TO

数据改变，但事件仍是有效SQL: 不幸的是，MySQL甚至不能察觉这种损坏。
数据改变且事件是无效SQL: 可以通过mysqlbinlog提取事件，并看到一些错乱的数据：UPDATE tbl SET col????????????????? 可以通过偏移量的方式找到下一个事件

数据遗漏并且/或者事件的长度是错误的

某些事件已经损坏或被覆盖，或者偏移量已经改变并且下一个事件的起始偏移量也是错误的

以上这两种情况 mysqlbinlog 已经起不了多少作用。

通过一些简单的分析后可用 mysqlbinlog --start-position来跨过损坏的事件。或者使用CHANGE MASTER TO 的 MASTER_LOG_POS参数。以下是找到偏移量的两个例子：

例1：$ mysqlbinlog mysql-bin.000113 | egrep '^# at '

例2：strings -n 2 -t d mysql-bin.000113

然后找到'G结尾的行，它们是固定长度事件头的一部分。(linux命令strings)

10.7.10丢失的临时表

尽量不用（在备库上使用的影响会小一点）
另一个办法是使用伪临时表：在一个temp库上创建，如表top_users_1234，其中1234是函数CONNECTION_ID()的返回值。可能通过清理线程将其移除——通过SHOW PROCESSLIST 来获得活跃的连接列表，并将其与表名中的连接ID比较

10.7.12InnoDB锁引起的锁争用

替换INSERT...SELECT: 在主库上先执行 SELECT INTO OUTFILE 再执行 LOAD DATA INFILE。这种方法更快，而且不需要加锁

以下是加锁示意图：

两个事务更新数据，使用共享锁串行化更新

10.7.13在主—主复制结构中写入两台数据库

可以使用auto_increment_increment 和 auto_increment_offset 。但自增问题只是所有问题的一小部分

10.7.14过大的复制延迟

log_slow_slave_statements: 在备库打开该选项，在标准的MySQL慢查询日志能够记录MySQL5.1及更新版本中执行的语句，这样就可以找到哪些语句执行的慢了。最好配合pt-query-digest使用

除了购买更快的磁盘和CPU，备库没有太多的优化空间

一个简单的办法是设置 innodb_flush_log_at_trx_commit = 2 来实现，这样事务会提交的快些（见8.5.1节）
还可以在备库上禁止二进制日志记录；
设置 innodb_locks_unsafe_for_binlog = 1
设置MyISAM的 delay_key_write = ALL

但这些都是牺牲安全为代价的，如果要把备库提升为主库要记得把这些选项设置回安全值。

不要重复写操作中代价较高的部分

LOAD DATA INFILE

可能先从备库执行，再回传到主库,防止在每个备库上重放。下面例子有效避免了在主库及其它备库上执行昂贵的GROUP BY操作：

先在备库上执行（summary_db是备库的特别保留的数据库）：

REPLACE INTO summary_db.summary_table (col1, col2, ...) -> SELECT col1, sum(col2, ...) FROM main_db.enormous_table GROUP BY col1;

现在就可以执行SELECT INTO OUTFILE，然后再执行LOAD DATA INFILE，将结果加载到主库中。

在复制之外并行写入

一个很好的例子：之前讨论的归档，可以禁止归档查询写到二进制日志中去
另一个例子，根据网上总结（非教材内容，需要在主备所有机器都执行，容易不一致）Mysql中load data infile主从复制注意点。

在主库、备库上执行如下语句：

time mysql -e "set foreign_key_checks=0; set sql_log_bin=0; set unique_checks=0; load data local infile 'infile.txt' into table load_test fields terminated by '\t' lines terminated by '\n' (col1, col2);"

注意： time命令是用于统计执行时间的。set sql_log_bin=0 等的设置仅针对本次连接有效。

为复制线程预取缓存

10.7.15来自主库过大的包

max_ allowed_packet: 如果该值在主备不匹配。主库可能会记录一个备库认为过大的包。当备库获取二进制日志时可能会碰到各种各样的问题，包括无限的报错和重试、或者中继日志损坏

10.7.16受限的复制还宽

可以开启 slave_compressed _protocol 选项。可减少三分之一的带宽

10.8复制有多快

时间插件NOW_USEC P290、P493

10.9MySQL复制的高级特性

半同步: 在某些场景下半同步能提高性能，在主库关闭sync_binlog的情况下保证更加安全。写入远程的内存（一台备库的反馈）比写入本地的磁盘（写入并刷新）要更快。有些性能测试表明性能提高了两倍！
多线程复制: MySQL5.6已提供

十一章可扩展的MySQL

10.1什么是可扩展性

可扩展性表明了，当需要增加资源以执行更多工作时系统能获得划算的同等提升。以下是可扩展性定律的三种模型：

线性扩展、Amdahl扩展及USL扩展定律

10.2扩展MySQL

10.2.4向外扩展

3.选择分区键

两个数据模型，一个易于分片，另一个则难以分片

4.多个分区键——一个例子：: 一个社交网站下的读书俱乐部站点，该站点可以显示任何书籍的任何评论，也能显示用户读过或评论过的所有书籍。假设用户数据和书籍数据都进行了分片设计。而评论同时拥有用户ID和评论ID，这样就跨越了两个分片的边界。实际上无须冗余存储两份评论数据，替代方案是：

将评论和用户数据一起存储，然后把每个评论的标题和ID与书籍数据存储在一起。这样在渲染大多数关于某本书的评论的视图时无须同时访问用户和书籍数据存储，如果需要显示完整的评论内容，可以从用户数据存储中获得。

5.跨分片查询

可以使用数据队列服务（如Gearman）来聚合查询，再用PHP等应用使用队列服务的聚合结果。

跨分片查询也可以借助汇总表来执行。可以遍历所有分片来生成汇总表并将结果在每个分片上冗余存储。也可以把汇总表放到另外一个数据存储中，这样就只需要存储一份了。

6.分配数据、分片和节点

应该让分片比节点节点容量小很多。可以假设100G的表，则可以1G/分片。

简单说，“易于管理的大小”是指保持表足够小，以便能在5或10分钟内提供日常维护操作，例如 ALTER TABLE、CHECK TABLE 或者 OPTIMIZE TABLE操作

7.在节点上部署分片

以下是一些常用的用法

每个分片使用单一数据库，并且数据库名要相同。典型的应用场景是需要每个分片都能镜像到原应用的结构。这在部署多个应用实例，并且每个实例对应一个分片时很有用。
将多个分片的表放到一个数据库中，在每个表名上包含分片号（如 bookclub.comments_23）这种配置下，单个数据库可以支持多个分片
为每个分片使用一个数据库，并在数据库中包含所有应用需要的表。在数据库名中包含分片号（如bookclub_23.comments, bookclub_23.users ）。
库和表都加分片号如 bookclub_23.comments_23
每个节点上多个MySQL实例，每个实例上有一个或多个分片。可以使用以上任意组合方式分片

如果为已有应用分片，结果往往是一个节点对应一个分片

8.静态分配

如：SELECT CRC32(111) % 100;

缺点：分片很大且数量不多时，很难平衡负载；无法处理热点数据；修改分片策略困难

9.动态分配

14.分片工具

JAVA: Hibernate Shards (http://shards.hibernate.org) 、 HiveDB (http://www.hivedb.org)
PHP

Shard-Query (http://code.google.com/p/shard-query/)
ScaleBase (http://www.scalebase.com)
ScalArc (http://www.scalarc.com)
dbShards (http://www.dbshards.com).

10.2.6通过集群扩展

1.MySQL Cluster

当使用MySQL来控制NDB时，行的主键就是键，其他的列是值。

需要高速连接设备+大量内存（服务器间需要大量内存）

缺点：

复杂查询现在支持的不是很好，如那些关联和聚合的查询
它是一个事务型的数据库，但不支持MVCC，所以需要读锁，也不做任何死锁检测

2.Clustrix: 它能直接替代MySQL。与MySQL Cluster很像；关键不同点是，Clustrix是完全的分布式执行并且缺少顶层的代理，它能很好的理解MySQL协议，无需MySQL进行协议转换。; 实验表明，Clustrix能提供高性能和扩展性
3.ScaleBase: 是一个软件代理。相似的还有 ScaleArc、dbShards

10.2.7内向扩展

高性能MySQL笔记（下）

目录