如何设置MySQL同步(Replication)

1、MySQL提供了数据库的同步功能，这对我们实现数据库的冗灾、备份、恢复、负载均衡等都是有极大帮助的。本文描述了常见的同步设置方法。一、准备服务器由于MySQL不同版本之间的（二进制日志）binlog格式可能会不一样，因此最好的搭配组合是Master的MySQL版本和Slave的版本相同或者更低，Master的版本肯定不能高于 Slave版本。本文中，我们假设主服务器（以下简称Master）和从服务器（以下简称Slave）的版本都是5.0.15，操作系统是 Linux Ubuntu 5.0.x。假设同步 Master的主机名为：rep1， Slave主机名为：rep2，2个MySQL的based

2、ir目录都是/usr/local/mysql ， datadir 者0是：/usr/local/mysql/data。二、设置同步服务器1、设置同步Master每个同步服务器都必须设定一个唯一的编号，否则同步就不能正常运行了。接下来开始修改f，增加以下几行：server-id = 1log-b inset-variable=bi nlog-ig no re-db=mysql然后在Master上增加一个账号专门用于同步，如下：mysql>GRANT REPLICATION SLAVE ON *.* TO reprep2 IDENTIFIED BY 'rep'如果想要在Sla

3、ve上有权限执行"LOAD TABLE FROM MASTER" 或"LOAD DATA FROM MASTER"语句的话，必须授予全局的FILE和SELECT权限：mysql>GRANFILE,SELECT,REPLICATIONLAVEON* TOreprep2 IDENTIFIED BY 'rep'第三行表示不记录数据库 mysql的更新日志，这就避免了 Master上的权限设置等被同步到 Slave 上，如果对这方面没有限制，就可以不设置这个参数。接下来备份 Master上的数据，首先执行如下 SQL语句：mysql>

4、FLUSH TABLES WITH READ LOCK;不要退出这个终端，否则这个锁就不生效了；接着导出数据，可以直接打包压 缩数据文件，也可以使用 mysqldump工具来做，推荐前者的方法，这样更为快 捷简便。root$cd /usr/local/mysqlroot$tar zcf data.tar.gz ./data （在这里也可能是"var"等其它实际存放数据文件的目录，根据实情而定）然后将这些数据拷贝到Slave服务器上，解开，设置好正确的权限及属主等；之后，执行"UNLOCK TABLES"语句来释放锁。2、设置 Slave修改f，增加如下几

5、行：server-id = 2master-host = rep1 #主服务器名master-user = rep #同步账户名，默认是 testmaster-password = rep # 同步帐户密码，默认是空master-port = 3306 # 主服务器的TCP/IP 端口号，默认是 3306 set-variable=replicate-ig nore-db=mysql #略过同步的数据库名，如果有多个，请设置多次set-variable=replicate-do-db=yejr #想要同步的数据库名，如果有多个，请设置多次接下来在Slave上检验一下是否能正确连接到Master

6、上，并且具备相应的权限。root$mysql -hrepl -urep -prepmysql>SHOW GRANTS;+|Gran tsforreprep2|+| GRANTSELECT, FILE, REPLICATION SLAVE ON * TO 'rep''rep2'IDENTIFIED BYPASSWORD '*9FF2C222F44C7BBA5CC7E3BE8573AA4E1776278C'|+现在，可以启动 Slave 了。启动成功后，登录Slave,查看一下同步状态:mysql -hlocalhost -urootmysql

7、>SHOW SLAVE STATUSG*1. row*Slave_IO_State: Waiti ng for master to send eve ntMaster_Host: rep1Master_User: repMaster_Port: 3306Conn ect_Retry: 60Master_Log_File: binl og.000001Read_Master_Log_Pos: 98Relay_Log_File: relay.000003Relay_Log_Pos: 232Relay_Master_Log_File: binl og.000001Slave_IO_R unni

8、ng: YesSlave_SQL_Ru nning: YesReplicate_Do_DB:Replicate_lg no re_DB:Replicate_Do_Table:Replicate_lg no re_Table:Replicate_Wild_Do_Table:Replicate_Wild_lg no re_Table:Last_Err no: 0Last_Error:Skip_Cou nter: 0Exec_Master_Log_Pos: 98Relay_Log_Space: 232Un til_Co nditi on: No neUn til_Log_File:Un til_Lo

9、g_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Ce rt:Master_SSL_Cipher:Master_SSL_Key:Seco nds_Behi nd_Master: 01 row in set (0.00 sec)Slave 的可以看到，Slave_IO_Running和Slave_SQL_Running两列的值都为 "Yes"，这表明I/O和SQL线程都在正常运行。至此，同步设定成功。三、同步设定 FAQ问：master还在运行中，如何在不停止它的情况下

10、配置slave?答：需要设计几个选项参数。如果已经有了 master的备份并且记录了数据快照二进制日志文件名以及偏移位置（运行SHOW MASTER STATUS查看结果），执行以下步骤：1.确定slave指定了一个唯一的服务器编号。2.在slave上执行如下语句，把一些选项值改成实际值：3.mysql> CHANGE MASTER TO4.->MASTER_HOST='master_host_name',5.->MASTER_USER='master_user_name',6.->MASTER_PASSWORD='master_

11、pass',7.->MASTER_LOG_FILE='recordedog_file_name'.->MASTER_LOG_POS=recordedo g_positio n;8.在slave上执行START SLAVE语句如果事先没有备份 master的数据，可以用以下方法快速创建一个备份。以下所有的操作都是在master 上。9.提交语句:10. mysql> FLUSH TABLES WITH READ LOCK;11. 确保这个锁一直存在，执行以下命令（或者其他类似的）：12. shell> tar zcf /tmp/backup.tar

12、.gz /var/lib/mysql14. mysql> SHOW MASTER STATUS;15. 释放锁：mysql> UNLOCK TABLES;上述步骤的另一个办法是创建master的SQL转储文件。只需在 master上执行 mysqldump-master-data命令，然后将导岀来的sql转储文件载入 slave。不过，这么做会制作二进制数据快照的方式慢一点。无论使用上述两种方法的哪种，最后都能创建 master的数据快照然后记录二进制日志文件名以及偏移位置。可以在好几的其他的slave上使用同一个备份的二进制数据快照。得到master的快照后，只要 master的

13、二进制日志完好无损，接着就能开始设置slave 了。两个决定是否需要等待较长时间的限制是：在master上磁盘空间保存二进制日志，以及slave从master抓取更新事件。也可以使用 LOAD DATA FROM MASTER 。这个语句可以很方便地在 slave上取得数据 快照并且能立刻调整二进制日志文件名以及偏移位置。在将来，我们推荐用LOAD DATAFROM MASTER来设置slave。警告，它只能用于 MylSAM 表，并且可能会保持一个较 长时间的读锁。由于它还没达到所期望的高效率，因此如果数据表很大，最好还是在执行完FLUSH TABLES WITH READ LOCK 后直接

14、制作二进制数据快照。问：是否slave总是需要连接到master?答：不，非必需。slave可以好几小时甚至几天关闭或者不连接master,然后重连再取得更新操作日志。例如，可以在拨号链接上设置一个mater/slave关系，拨号可能只是零星的不定期的连接。这种做法隐含的是，在任何指定的时间里，除非使用特殊的度量标准，否则slave不能保证总是能和 master保持同步。在未来，有个选项可以阻止master,除非至少有一个 slave在同步中。问：怎么知道比 master晚了多少？也就是说，怎么知道slave最后同步的时间?答：如果 slave是4.1.1或者更高，只需查看SHOW SLAVE

15、 STATUS结果中的Seconds_Behind_Master字段。对于老版本，可以用以下办法。如果在slave上执行SHOWPROCESSLIST语句结果显示SQL线程（对MySQL 3.23则是slave线程）正在运行，这就意 味着该线程至少从master读取一个更新操作事件。详情请看"6.3 Replication ImplementationDetails"当SQL线程执行一个 master上读取的更新操作事件时，它把自己的时间改成事件的时间（这也就是TIMESTAMP也要同步的原因）。在SHOW PROCESSLIST结果中的Time字段中，slave的SQL线

16、程显示的秒数就是最后 一次同步的时间戳和slave本机的实际时间相差秒数。可以根据这个值来判断最后同步的时间。slave 的注意，如果slave已经从master断开好几个小时了，然后重新连接，就能看到SHOW PROCESSLIST结果中的SQL线程的Time字段的值类似3600。这是因为slave正在执行一个小时前的语句问:如何强制 master在slave赶上全部更新之前阻止更新操作?答：执行以下步骤:16. 在master上，执行以下语句：17. mysql> FLUSH TABLES WITH READ LOCK;mysql> SHOW MASTER STATUS;记录下

17、结果中的日志文件名以及偏移位置，它们是同步的坐标值。18. 在slave上，提交以下语句，MASTER_POS_WAIT()函数的参数的值就是前面取得的同步坐标值：19. mysql> SELECT MASTER_POS_WAIT('log_name', log_offset);SELECT语句会阻止更新，直到 slave同步到了上述日志文件及位置。在这个时候， slave就和master保持同步了，并且这个语句就会返回。20. 在master上，执行以下语句允许 master重新处理更新操作：mysql> UNLOCK TABLES;问：设置一个双向复制时要注意什

18、么问题?答：MySQL同步目前还不支持任何在master和slave上的分布式（跨服务器）更新锁协议以保证操作的原子性。也就是说，存在这样的可能性：客户端A在并存的master 1上做了一个更新，同时，在它同步到并存master 2上之前，客户端 B在master 2上可能也做了一个和客户端A在master 1上不同的更新操作。因此，当客户端A所做的更新同步到master 2时，它将产生和master 1上不同的数据表，尽管master 2上的更新操作也全都同步到master 1上去。这意味着除非能确保所有的更新都能以任何顺序安全地执行，否则不要使用双向同步，或者除非注意在客户端程序中的不知原

19、因的无序更新操作。同时也要意识到在所关心的更新问题上，双向同步实际上并不能很大地改善性能（甚至没有）。两个服务器都需要执行同样数量的更新操作，在一台服务器上也是。唯一区别的是，可能这样 做会减少一些锁争夺，因为来自其他服务器的更新操作都会被串行地放到slave线程中。甚至这种好处还可以作为网络延迟的补偿。问：我如何利用同步来提高系统性能?然后配置多个廉价答：需要安装一个服务器作为master并且把所有的写操作直接放在这上面。的使用机架磁盘的slave，把读操作分配给master和slave。还可以在启动slave时使用-skip-innodb, -skip-bdb, -low-priority

20、-updates,和-delay-key-write=ALL 选项来提高slave端的性能。这种情况下，slave会使用非事务的MylSAM表来代替InnoDB和BDB表，已取得更快速度。问：如何准备客户端应用程序的代码来适应同步应用?答：如果代码中负责存取数据库的部分已经被合理地抽象化/模块化了，将它们转化成适用运行于同步环境中将会很平滑和简单。只需要修改数据库存取实现部分，把所有的写操作放到 master上，把所有的读操作放到master或者slave上。如果你的代码还没达到这个层次的抽象化，那么这将成为整理代码的机会和动机。可以使用类似以下函数创建封装类库或者模块：osafe_write

21、r_co nn ect()osafe_reader_c onn ect()osafe_reader_stateme nt()osafe_writer_stateme nt()每个函数名的 safe_表示它们会处理所有的错误情况。可以使用其他函数名。重要的是，要 为读连接、写连接、读、写定义好统一的接口。然后将客户端代码转换成使用封装的类库。已开始可能是很痛苦且麻烦的，不过在将来长期运行中就能得到回报了。所有使用上述方法的应用程序都会在master/slave配置中有优势，即使包含多个slave。这些代码将很容易维护，一些额外的麻烦也会很少。自豪需要修改一个或者 两个函数；例如，想要记录每个语句

22、执行了多长时间，或者在上千个语句中哪个出现错误了如果已经写了很多代码，你可能想要自动转换它们，那么可以使用 MySQL发布的replace工具，或者自己写转换脚本。理想地，你的代码已经使用了统一的编程风格。如果不是，最好重写它们，或者可以遍历检查一下，手工规范化一下代码风格。问：MySQL同步何时且有多少能提高系统性能?答:MySQL同步对于频繁读但不频繁写的系统很有好处。理论上来讲，使用单一 master/多slave的配置，就可以通过这个方法来衡量系统：增加更多的slave直到用完所有的网络带宽或者master的更新操作增长到了不能再处理的点了。想要知道增加多少个slave之后得到的性能才

23、能平稳，以及能提高多少性能，就需要知道查询模式，并且根据经验对典型的master和slave做读(每秒读或 max_reads和写(max_write)基准测试得到它们之间的关系。下例展示了一个理想系统取得的性能的简单计算方法。设定系统负载由10%写和90%读组成，我们已经通过基准测试确定max_reads是1200 - 2 *max_writes。换句话说，系统可以达到每秒做没有写的1200次读操作，写操作平均是读操作的2倍慢，它们之间的关系是线性的。让我们假设master和每个slave都有同样的容量，有个 master和N个slave。每个服务器 (master或slave):reads

24、 = 1200 - 2 * writesreads = 9 * writes / (N + 1)(读是分开的，但是所有写是在所有的服务器上的)9 * writes / (N + 1) + 2 * writes = 1200writes = 1200 / (2 + 9/(N+1)1200和1次写9次读最后的等式说明了 N个slave的最大写数量，给它每分钟的最高读频率的机率分析结论比率如下:如果N = 0（意味着没有同步），系统大致可以处理每秒1200/11 = 109次写。如果N = 1，增加到每秒 184次写。如果N = 8，增加到每秒 400次写。如果N = 17，增加到每秒 480次写。

25、o最终，随着N接近无穷大（我们的预算为负无穷大），则可以达到几乎每秒600次写，大约提高系统吞吐量 5.5倍。尽管如此，当有8台服务器时，已经提高了 4倍了注意，上面的计算是假设了网络带宽无穷大，并且忽略了一些系统中比较大的因素。在很多情况下，当系统增加 N个同步slave之后，是无法精确计算岀上述预计结果的。不过，先看看下列问题将有助于你知道是否有和有多少系统性能上的改善：o系统读/写得比率是多少？o减少读操作后一个服务器能增加处理多少写操作?o你的网络带宽足够给多少 slave使用？问：如何利用同步提供冗余 /高可用性?答：使用当前已经可用的特性，可以配置一个master和一个（或多个）s

26、lave，并且写一个脚本监o使用 CHANGE MASTER TO 语句告诉 slave 修改 master。o一个让应用程序定位 master所在主机的办法就是给master使用动态DNS。例如bi nd就可以用'n supdate'来动态更新dns。o使用-log-bin选项，不使用-log-slave-updates选项来启动 slave。这样就能让 slave 运行 STOP SLAVE;RESET MASTER语句后随时准备变成 master，并且在其他slave上运行CHANGE MASTER TO。例如，有以下配置方案：oWCoovoVC> Mv v vS1

27、 S2 S3M表示masetr, S表示slave，WC表示提交读写操作的客户端；只提交读操作的客户端没有表示岀来，因为它们无需切换。S1，S2，S3都是使用-log-bin 选项，不用-log-slave-updates选项 运行的slave。由于 除非指定 -log-slave-updates参数，否则从 master读到的更新操作都不会记录到二进制日志中，因此每个slave上的二进制日志都是空的。如果因为某些原因M不能用了，可以指定一个slave作为mastero例如，如果指定 S1，则所有的 WC都要重定向到 S1 上, S2和S3都需要从S1上同步。确定所有的 slave都已经处理完

28、各自的中继日志了。在每个slave上，提交 STOPSLAVE IO_THREAD 语句，然后检查 SHOW PROCESSLIST的结果直到看 到Has read all relay log 了。当所有的slave都这样子之后，就可以按照新的方案 设置了。在slave S1上提交STOP SLAVE 和 RESET MASTER 语句将其提升为 master。在其他 slave S2 和 S3 上，提交 STOP SLAVE 和 CHANGE MASTERTO MASTER_HOST='S1' （ 'S1 '代表S1的真实主机名）语句修改master。把S2,

29、S3如何连接到S1的参数（用户，密码，端口等）都附加到 CHANGE MASTER后 面。在CHANGE MASTER中无需指定 S1的二进制日志文件名和偏移位置：因为CHANGE MASTER默认就是第一个二进制日志和偏移位置4。最后，在S2和S3上提交START SLAVE语句。然后让所有的 WC都把他们的语句重定向到S1上。从这个时候开始，从所有的WC发送到S1上的更新语句都会写到 S1的二进制日志中，它们包含了从 M死掉之后发送 到S1的全部更新语句。配置结果如下：WC/|WC | M（u navailable） | |v vS1v-S2 S3A I+当M又起来了之后，只需在M上提交和

30、在S2和S3上的一样的 CHANGE MASTER 语句，将它变成一个 slave并且读取自从它死掉之后的全部WC提交的更新操作。想要把M重新变成master（例如因为它的性能更好 ），就执行类似上面的操作， 把S1当作失效了，把 M提升为新的 master。在这个步骤中，别忘了在把S2和S3修改成为 M线程之外不会修改他的数据，那么应该不会发生这样的情形。如果确实发生了，那么效的那个时刻开始读取WC提交的更新操作日志。现在我们就运行着一个完整的自动选择master的MySQL同步系统，不过在它准备好之前，需要创建自己的监控工具。6.10同步疑难解答如果按照上述步骤设定好同步之后，它不能正常工

31、作的话，首先检查以下内容:o查看一下错入日志信息 。不少用户都在这方面做得不够好以至于浪费时间。omaster是否在记录二进制日志？用 SHOW MASTER STATUS检查一下状态。如果是，Position的值不为零；否则，确定master上使用了 log-bin和server-id 选项。oslave是否运行着？运行 SHOW SLAVE STATUS语句检查Slave_IO_Running和Slave_SQL_Running的值是否都是。如果不是，确定是 否用同步参数启动 slave服务器了。o如果slave正在运行，它是否建立了到master的连接？运行SHOW PROCESSLIS

32、T语句检查I/O和SQL线程的State字段值。详情请看"6.3 Replication Implementation Details "。如果 I/O 线程状态为 Connecting to maste，就 检查一下 master上同步用户的权限是否正确， master的主机名，DNS设置，master是 否确实正在运行着，以及 slave是否可连接到 master，等等。o如果slave以前运行着，但是现在停止了，原因通常是一些语句在master上能成功但在slave上却失败了。如果 salve已经取得了 master的全部快照，并且除了slave可能是一个 bug 或

33、者你碰到了 "6.7 Replication Features and Known Problems"中提到的 同步限制之一。如果是一个 bug，那么请按照"6.11 Reporting Replication Bugs"的说明 扌报告它。o如果一个语句在 master上成功了，但是在slave上却失败了，并且这时不能做一次完整的数据库再同步 (也就是删除slave上的数据，重新拷贝master的快照)，那么试 一下：1. 判断slave的数据表是否和 master的不一样。试着找到怎么会发生这种 情况，然后将slave的表同步成和 master 一样之后运行 START SLAVE。2. 如果上述步骤不生效或者没有执行，试着这个语句是否能被手工安全地运行(如果有必要)，然后忽略 master的下一个语句。3. 如果决定要忽略