6.1. 复制介绍

MySQL支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。(这与同步

复制可以进行对比，

如果你想要设置链式复制服务器，从服务器本身也可以充当主服务器。

请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。

单向复制有利于健壮性、速度和系统管理：

·主服务器/从服务器设置增加了健壮性。主服务器出现问题时，你可以切换到从服务器作为备份。

·通过在主服务器和从服务器之间切分处理客户查询的负荷，可以得到更好的客户响应时间。SELECT查询可以发送到从服务器以降低主服务器的查询处理负荷。但修改数据的语句仍然应发送到主服务器，以便主服务器和从服务器保持同步。如果非更新查询为主，该负载均衡策略很有效，但一般是更新查询。

·使用复制的另一个好处是可以使用一个从服务器执行备份，而不会干扰主服务器。在备份过程中主服务器可以继续处理更新。参见5.9.1节，“数据库备份”。

6.2. 复制实施概述

MySQL复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。因此，要进行复制，必须在主服务器上启用二进制日志。参见5.11.3节，“二进制日志”。

每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新，以便从服务器可以对其数据拷贝执行相同的更新。

认识到二进制日志只是一个从启用二进制日志的固定时间点开始的记录非常

重要。任何设置的从服务器需要主服务器上的

数据库拷贝。如果启动从服务器时，其数据库与主服务器上的启动二进制日志时的状态不相同，从服务器很可能失败。

将主服务器的数据拷贝到从服务器的一个途径是使用LOAD DATA FROM MASTER语句。请注意LOAD DATA FROM MASTER目前只在所有表使用MyISAM存储引擎的主服务器上工作。并且，该语句将获得全局读锁定，因此当表正复制到从服务器上时，不可能在主服务器上进行更新。当我们执行表的无锁热备份时，则不再需要全局读锁定。

由于这些限制，我们建议只有主服务器上的数据集相对较小，或者主服务器上延迟读锁定已经被接受，才可以使用LOAD DATA FROM MASTER。而LOAD DATA FROM MASTER的实际速度随系统的不同而不同，对于执行时间，最好的规则是每1MB的数据用1秒钟。这是一个粗略的估计，但你会发现如果主服务器和从服务器的性能上等价于700MHz Pentium CPU，通过100Mbps的网络进行连接，则该估计相当准确。

从服务器设置为复制主服务器的数据后，它连接主服务器并等待更新过程。如果主服务器失败，或者从服务器失去与主服务器之间的连接，从服务器保持定期尝试连接，直到它能够继续帧听更新。由--master-connect-retry选项控制重试间隔。 默认为60秒。

每个从服务器跟踪复制时间。主服务器不知道有多少个从服务器或在某一时刻有哪些被更新了。

6.3. 复制实施细节

6.3.1. 复制主线程状态

6.3.2. 复制从I/O线程状态

6.3.3. 复制从SQL线程状态

6.3.4. 复制传递和状态文件

MySQL使用3个线程来执行复制功能(其中1个在主服务器上，另两个在从服务器上。当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让它发送记录在其二进制日志中的语句。主服务器创建一个线程将二进制日志中的内容发送到从服务器。该线程可以识别为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志

。第3个线程是SQL线程，是从服务器创建用于读取中继日志并执行日志中包含的更新。

在前面的描述中，每个从服务器有3个线程。有多个从服务器的主服务器创建为每个当前连接的从服务器创建一个线程；每个从服务器有自己的I/O和SQL线程。

这样读取和执行语句被分成两个独立的任务。如果语句执行较慢则语句读取任务没有慢下来。例如，如果从服务器有一段时间没有运行了，当从服务器启动时，其I/O线程可以很快地从主服务器索取所有二进制日志内容，即使SQL线程远远滞后。如果从服务器在SQL线程执行完所有索取的语句前停止，I/O 线程至少已经索取了所有内容，以便语句的安全拷贝保存到本地从服务器的中继日志中，供从服务器下次启动时执行。这样允许清空主服务器上的二进制日志，因为不再需要等候从服务器来索取其内容。

SHOW PROCESSLIST语句可以提供在主服务器上和从服务器上发生的关于复制的信息。

下面的例子说明了这3个线程在SHOW PROCESSLIST中的显示。

在主服务器上，SHOW PROCESSLIST的输出看上去应为：

*************************** 1. row ***************************

     Id: 2

   User: root

   Host: localhost:32931

     db: NULL

Command: Binlog Dump

   Time: 94

  State: Has sent all binlog to slave; waiting for binlog to

         be updated

   Info: NULL

这儿，线程2是一个连接从服务器的复制线程。该信息表示所有主要更新已经被发送到从服务器，主服务器正等待更多的更新出现。

在从服务器上，SHOW PROCESSLIST的输出看上去应为：

*************************** 1. row ***************************

     Id: 10

   User: system user

   Host:

     db: NULL

Command: Connect

   Time: 11

  State: Waiting for master to send event

   Info: NULL

*************************** 2. row ***************************

     Id: 11

   User: system user

   Host:

     db: NULL

Command: Connect

   Time: 11

  State: Has read all relay log; waiting for the slave I/O

         thread to update it

   Info: NULL

该信息表示线程10是同主服务器通信的I/O线程，线程11是处理保存在中继日志中的更新的SQL线程。SHOW PROCESSLIST运行时，两个线程均空闲，等待其它更新。

请注意Time列的值可以显示从服务器比主服务器滞后多长时间。参见6.9节，“复制FAQ”。

6.3.1. 复制主线程状态

·Sending binlog event to slave

二进制日志由各种事件组成，一个事件通常为一个更新加一些其它信息。线程已经从二进制日志读取了一个事件并且正将它发送到从服务器。

·Finished reading one binlog; switching to next binlog

线程已经读完二进制日志文件并且正打开下一个要发送到从服务器的日志文件。

·Has sent all binlog to slave; waiting for binlog to be updated

线程已经从二进制日志读取所有主要的更新并已经发送到了从服务器。线程现在正空闲，等待由主服务器上新的更新导致的出现在二进制日志中的新事件。

·Waiting to finalize termination

线程停止时发生的一个很简单的状态。

6.3.2. 复制从I/O线程状态

·Connecting to master

线程正试图连接主服务器。

·Checking master version

建立同主服务器之间的连接后立即临时出现的状态。

·Registering slave on master

建立同主服务器之间的连接后立即临时出现的状态。

·Requesting binlog dump

建立同主服务器之间的连接后立即临时出现的状态。线程向主服务器发送一条请求，索取从请求的二进制日志文件名和位置开始的二进制日志的内容。

·Waiting to reconnect after a failed binlog dump request

如果二进制日志转储请求失败(由于没有连接)，线程进入睡眠状态，然后定期尝试重新连接。可以使用--master-connect-retry选项指定重试之间的间隔。

线程正尝试重新连接主服务器。

线程已经连接上主服务器，正等待二进制日志事件到达。如果主服务器正空闲，会持续较长的时间。如果等待持续slave_read_timeout秒，则发生超时。此时，线程认为连接被中断并企图重新连接。

线程已经读取一个事件，正将它复制到中继日志供SQL线程来处理。

读取时(由于没有连接)出现错误。线程企图重新连接前将睡眠master-connect-retry秒。

线程正尝试重新连接主服务器。当连接重新建立后，状态变为Waiting for master to send event。

正使用一个非零relay_log_space_limit值，中继日志已经增长到其组合大小超过该值。I/O线程正等待直到SQL线程处理中继日志内容并删除部分中继日志文件来释放足够的空间。

线程停止时发生的一个很简单的状态。

6.3.3. 复制从SQL线程状态

线程已经从中继日志读取一个事件，可以对事件进行处理了。

线程已经处理了中继日志文件中的所有事件，现在正等待I/O线程将新事件写入中继日志。

线程停止时发生的一个很简单的状态。

I/O线程的State列也可以显示语句的文本。这说明线程已经从中继日志读取了一个事件，从中提取了语句，并且正在执行语句。

6.3.4. 复制传递和状态文件

默认情况，中继日志使用host_name-relay-bin.nnnnnn

形式的文件名

，其中

是从服务器主机名，

是序列号。用连续序列号来创建连续中继日志文件，从000001开始。从服务器跟踪索引文件中目前正使用的中继日志。 默认中继日志索引文件名为

中继日志与二进制日志的格式相同，并且可以用mysqlbinlog读取。SQL线程执行完中继日志中的所有事件并且不再需要之后，立即自动删除它。没有直接的删除中继日志的机制，因为SQL线程可以负责完成。然而，FLUSH LOGS可以循环中继日志，当SQL线程删除日志时会有影响。

在下面的条件下创建新的中继日志：

·每次I/O线程启动时创建一个新的中继日志。

·当日志被刷新时；例如，用FLUSH LOGS或mysqladmin flush-logs。

·当当前的中继日志文件变得太大时。“太大”含义的确定方法：

omax_relay_log_size，如果max_relay_log_size > 0

omax_binlog_size，如果max_relay_log_size = 0

从属复制服务器在数据目录中另外创建两个小文件。这些状态文件默认名为主master.info和relay-log.info。它们包含SHOW SLAVE STATUS语句的输出所显示的信息(关于该语句的描述参见13.6.2节，“用于控制从服务器的SQL语句”)。状态文件保存在硬盘上，从服务器关闭时不会丢失。下次从服务器启动时，读取这些文件以确定它已经从主服务器读取了多少二进制日志，以及处理自己的中继日志的程度。

由I/O线程更新master.info文件。文件中的行和SHOW SLAVE STATUS显示的列的对应关系为：

由SQL线程更新relay-log.info文件。文件中的行和SHOW SLAVE STATUS显示的列的对应关系为：

当备份从服务器的数据时，你还应备份这两个小文件以及中继日志文件。它们用来在恢复从服务器的数据后继续进行复制。如果丢失了中继日志但仍然有relay-log.info文件，你可以通过检查该文件来确定SQL线程已经执行的主服务器中二进制日志的程度。然后可以用Master_Log_File和Master_LOG_POS选项执行CHANGE MASTER TO来告诉从服务器重新从该点读取二进制日志。当然，要求二进制日志仍然在主服务器上。

如果从服务器正复制LOAD DATA INFILE语句，你应也备份该目录内从服务器用于该目的的任何SQL_LOAD-*文件。从服务器需要这些文件继续复制任何中断的LOAD DATA INFILE操作。用--slave-load-tmpdir选项来指定目录的位置。如果未指定， 默认值为tmpdir变量的值。

6.4. 如何设置复制

这里简单描述了如何为你当前的MySQL服务器设置完整的复制。假设你想要复制主服务器上的所有数据库，并且还没有配置的复制。你需要关闭主服务器来完成下面所列的步骤。

下面的程序针对设置一个从服务器，你可以用来设置多个从服务器。

虽然该方法是设置从服务器的最直接的途径，它并不是唯一的一个。例如，如果你有一个主服务器的数据快照，并且主服务器已经设置了服务器ID，启用了二进制日志，不需要关闭主服务器或停止对它的更新也可以设置从服务器。详情请参见6.9节，“复制FAQ”。

如果想要管理MySQL复制设置，我们建议你通读本章，并尝试13.6.1节，“用于控制主服务器的SQL语句”和13.6.2节，“用于控制从服务器的SQL语句”中的所有语句。还应熟悉6.8节，“复制启动选项”中描述的复制启动选项。

注释：该程序和后面章节所示的复制SQL语句需要SUPER权限。

1.确保在服务器和从服务器上安装的MySQL版本与6.5节，“不同MySQL版本之间的复制兼容性”所示的表兼容。理想情况，应在主服务器和从服务器上使用最近版本的MySQL。

请先证实问题不是出现在最新的MySQL版本中再通报bug。

2.在主服务器上为服务器设置一个连接账户。该账户必须授予REPLICATION SLAVE权限。如果账户仅用于复制(推荐这样做)，则不需要再授予任何其它权限。(关于设置用户 账户和权限的信息，参见5.8节，“MySQL用户账户管理”）。

假定你的域为mydomain.com,想要创建用户名为repl的一个账户，从服务器可以使用该账户从你的域内的任何主机使用密码slavepass来访问主服务器。要创建该 账户，可使用GRANT语句：

    -> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

如果你计划从从属服务器主机使用LOAD TABLE FROM MASTER或LOAD DATA FROM MASTER语句，你需要授予该账户其它权限：

·授予账户SUPER和RELOAD全局权限。

·为所有想要装载的表授予SELECT权限。任何该 账户不能SELECT的主服务器上的表被LOAD DATA FROM MASTER忽略掉。

3.执行FLUSH TABLES WITH READ LOCK语句清空所有表和块写入语句：

对于InnoDB表，请注意：FLUSH TABLES WITH READ LOCK还锁定COMMIT操作。当获得全局读锁定后，可以开始InnoDB表的文件系统快照。快照不能保证内部(在InnoDB存储引擎内部)一致性(因为InnoDB缓存没有刷新)，但并不需要关心该问题，因为InnoDB可以在启动时解决该问题并给出一致的结果。这说明InnoDB在启动快照时可以进行崩溃恢复，而不会破坏。然而，当保证一致的InnoDB表快照时，还没有途径来停止MySQL服务器。

让客户程序保持运行，发出FLUSH TABLES语句让读锁定保持有效。(如果退出客户程序，锁被释放）。然后对主服务器上的数据进行快照。

创建快照最简单的途径是使用归档程序对主服务器上的数据目录中的数据库进行二进制备份。例如，在Unix中使用tar，或者在Windows中使用PowerArchiver、WinRAR、WinZip或者类似的软件。要使用tar来创建包括所有数据库的归档文件，进入主服务器的数据目录，然后执行命令：

如果你想让归档只包括this_db数据库，应使用命令：

然后将归档文件复制到从服务器主机的/tmp目录。在该机器上，进入从服务器的数据目录，并使用下述命令解压缩归档文件：

如果从服务器的用户账户与主服务器的不同，你可能不想复制mysql数据库。在这种情况下，应从归档中排除该数据库。你也不需要在归档中包括任何日志文件或者master.info或relay-log.info文件。

当FLUSH TABLES WITH READ LOCK所置读锁定有效时，读取主服务器上当前的二进制日志名和偏移量值：

+---------------+----------+--------------+------------------+

| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+---------------+----------+--------------+------------------+

| mysql-bin.003 | 73       | test         | manual,mysql     |

+---------------+----------+--------------+------------------+

File列显示日志名，而Position显示偏移量。在该例子中，二进制日志值为mysql-bin.003，偏移量为73。记录该值。以后设置从服务器时需要使用这些值。它们表示复制坐标，从服务器应从该点开始从主服务器上进行新的更新。

取得快照并记录日志名和偏移量后，可以在主服务器上重新启用写活动：

如果你正使用InnoDB表，理想情况应使用InnoDB Hot Backup工具，使用该工具可以获得一致的快照而不需要在主服务器上进行锁定，并且可以对应从服务器上使用的快照来记录日志名和偏移量。Hot Backup是一个附加的非免费(商业)工具，没有包含在标准 MySQL分发中。详细信息参见http://www.innodb.com/manual.php的InnoDB Hot Backup主页。

不使用Hot Backup工具，最快捷的途径是使用InnoDB表的二进制快照来关闭主服务器并复制InnoDB数据文件、日志文件和表定义文件(.frm文件)。要记录当前的日志文件名和偏移量，关闭服务器之前应发出下面的语句：

mysql> SHOW MASTER STATUS;

然后记录前面所示的SHOW MASTER STATUS的输出中显示的日志名和偏移量。记录日志名和偏移量后，不

解锁表关闭服务器以确保服务器关闭时的快照与当前的日志文件和偏移量相对应：

适合MyISAM和InnoDB表的另一个方法是对主服务器上的SQL进行转储而不是对前面讨论的二进制复制进行转储。为了实现，可以在主服务器上使用mysqldump --master-data，以后将SQL转储文件装入从服务器。但是，这样比二进制复制要慢一些。

如果主服务器运行时没有启用--logs-bin，SHOW MASTER STATUS或mysqldump --master-data显示的日志名和位置值为空。在这种情况下，当以后指定从服务器的日志文件和位置时需要使用的值为空字符串('')和4.

5.确保主服务器主机上my.cnf文件的[mysqld]部分包括一个log-bin选项。该部分还应有一个server-id=Master_id选项

，其中

–1之间的一个正整数值。例如：

7.log-bin=mysql-bin

8.server-id=1

如果没有提供那些选项，应添加它们并重启服务器。

9.停止用于从服务器的服务器并在其my.cnf文件中添加下面的行：

11.server-id=slave_id

slave_id值同Master_id值一样，必须为1到2³²

–1之间的一个正整数值。并且，从服务器的ID必须与主服务器的ID不相同。例如：

server-id=2

如果设置多个从服务器，每个从服务器必须有一个唯一的server-id值，必须与主服务器的以及其它从服务器的不相同。可以认为server-id值类似于IP地址：这些ID值能唯一识别复制服务器群集中的每个服务器实例。

如果不指定一个server-id值，如果没有定义master-host，则将它设置为1；否则设置为2。请注意如果server-id太长，主服务器 拒绝所有来自从服务器的连接，并且从服务器拒绝连接到主服务器。这样，省略server-id只适合用二进制日志备份。

12.如果对主服务器的数据进行二进制备份，启动从服务器之前将它复制到从服务器的数据目录中。确保对这些文件和目录的权限正确。服务器 MySQL运行的用户必须能够读写文件，如同在主服务器上一样。

如果使用mysqldum备份，先启动从服务器(看下一步)。

13.启动从服务器。如果前面已经复制了，用--skip-slave-start选项启动从服务器，以便它不立即尝试连接主服务器。你也可能想要用--logs-warnings选项启动从服务器(默认设置启用)，以便在错误日志中显示更多的问题相关的信息(例如，网络或连接问题)。放弃的连接将记入错误日志，除非其值大于1。

14.如果使用mysqldump备份主服务器的数据，将转储文件装载到从服务器：

16.在从服务器上执行下面的语句，用你的系统的实际值替换选项值：

17.mysql> CHANGE MASTER TO

18.    ->     MASTER_HOST='master_host_name',

19.    ->     MASTER_USER='replication_user_name',

20.    ->     MASTER_PASSWORD='replication_password',

21.    ->     MASTER_LOG_FILE='recorded_log_file_name',

22.    ->     MASTER_LOG_POS=recorded_log_position;

下面的表显示了字符串选项的最大长度：

23.启动从服务器线程：

执行这些程序后，从服务器应连接主服务器，并补充自从快照以来发生的任何更新。

如果你忘记设置主服务器的server-id值，从服务器不能连接主服务器。

如果你忘记设置从服务器的server-id值，在从服务器的错误日志中会出现下面的错误：

we will force server id to 2, but this MySQL server will not act as a slave.

如果由于其它原因不能复制，从服务器的错误日志中也会出现错误消息。

从服务器复制时，会在其数据目录中发现文件dmaster.info和relay-log.info。从服务器使用这两个文件跟踪已经处理了多少主服务器的二进制日志。不要移除或编辑这些文件，除非你确切知你正在做什么并完全理解其意义。即使这样，最好是使用CHANGE MASTER TO语句。

注释：master.info的内容会覆盖命令行或in my.cnf中指定的部分选项。详情参见6.8节，“复制启动选项”。

有了一个快照，你可以用它根据刚刚描述的从服务器部分来设置其它从服务器。你不需要主服务器的另一个快照；每个从服务器可以使用相同的快照。

注释：为了保证事务InnoDB复制设置的最大可能的耐受性和一致性，应在主服务器的my.cnf文件中使用innodb_flush_log_at_trx_commit=1和sync-binlog=1。

6.5. 不同MySQL版本之间的复制兼容性

MySQL 5.1中使用的二进制日志格式与以前的版本中所使用的大大不同，特别是在字符集处理、LOAD DATA INFILE以及时区方面。

注释：你不能从使用新二进制日志格式的主服务器向使用旧二进制日志格式的从服务器复制(例如，从MySQL 5.0到MySQL 4.1）。。这样操作在复制设置升级服务器时后果严重，参见6.6节，“升级复制设置”。

我们推荐使用最近的MySQL版本，因为复制功能在不断地改进中。我们还推荐主服务器和从服务器使用相同的版本。我们建议升级主服务器和从服务器，运行alpha或beta版本到新的(产品)版本。在许多情况下，从新的主服务器向旧的从服务器复制将会失败。一般原则，运行MySQL 5.1.x的从服务器可以与旧的主服务器(可以运行MySQL 3.23、4.0或者4.1)一起使用，但不能反过来。

前面的信息适合协议级复制兼容性。然而，还会有一个约束条件，例如SQL级兼容性问题。例如， 5.1版本的主服务器不能复制到5.0版本的从服务器，如果复制语句使用5.1版本的SQL特性而不是5.0版本。这些问题和其它问题均在6.7节，“复制特性和已知问题”中讨论。

6.6. 升级复制设置

6.6.1. 将复制升级到5.0版

当在复制设置中升级服务器时，升级过程取决于当前的服务器版本和要升级的服务器版本。

6.6.1. 将复制升级到5.0版

4.0或者
4.1升级到
5.1。
4.0服务器应为
4.0.3或更新版。

当将早期MySQL版本系列主服务器升级到5.1时，应先确保该主服务器的所有从服务器使用了相同的5.1.x版本。如果不是这样，你应先升级从服务器。升级从服务器时，应先关闭从服务器，升级到相应5.1.x版本，然后重启从服务器并重新开始复制。5.1版本的从服务器能够读取升级前写入的旧的中继日志并执行日志中包含的语句。升级后从服务器创建的中继日志为5.1格式。

从服务器升级后，关闭主服务器，将它升级到与从服务器相同的5.1.x版本并重启它。5.1主服务器能够读取升级前写入的旧的二进制日志并将它们发送到5.1从服务器。从服务器可以识别旧的格式并正确处理它。升级后主服务器创建的二进制日志采用5.1格式。这样也可以由5.1从服务器识别。

换句话说，当升级到5.1时没有什么措施，只有将主服务器升级到5.1之前先将从服务器升级到5.1。请注意从5.1降级到旧版本不会如此简单：必须确保已经完全处理所有5.1版本的二进制日志或中继日志，以便在降级前可以移除它们。

6.7. 复制特性和已知问题

一般原则，SQL级复制兼容性要求主服务器和从服务器均支持使用的特性。例如，在MySQL 5.0.0中开始使用TIMESTAMPADD()函数。如果在主服务器上使用该函数，不能复制到MySQL 5.0.0之前的从服务器。如果你计划在5.1和以前版本的MySQL之间进行复制，你应查阅对应以前版本系列的MySQL参考手册，查询该系列复制特征相关信息。

下面列出了关于支持什么和不支持什么的详细信息。关于复制的其它InnoDB具体信息参见15.2.6.5节，“InnoDB和MySQL复制”。

关于保存的程序和触发器的复制问题在20.4节，“存储子程序和触发程序的二进制日志功能”中讨论。

·用AUTO_INCREMENT、LAST_INSERT_ID()和TIMESTAMP值正确实现复制。

·USER()、UUID()和LOAD_FILE()函数毫无改变地被，这样不能可靠地在从服务器上工作。

·下面的限制只适合基于语句的复制，而不是基于行的复制。

处理用户级锁定的函数GET_LOCK()、RELEASE_LOCK()、IS_FREE_LOCK()、IS_USED_LOCK()复制时从服务器不知道在主服务器上同时进行的相关文本；因此如果从服务器上的内容不同，这些函数不用来插入到主服务器的表中(例如不执行INSERT INTO mytable VALUES(GET_LOCK(...)))。

·在MySQL 5.1中FOREIGN_KEY_CHECKS、SQL_MODE、UNIQUE_CHECKS和SQL_AUTO_IS_NULL变量均复制。但TABLE_TYPE，即STORAGE_ENGINE变量 不复制，有利于在不同的存储引擎之间进行复制。

·即使主服务器和从服务器有不同的全局字符集变量，以及即使有不同的全局时区变量仍可以复制。

·下面适合使用不同字符集的MySQL服务器之间的复制：

1.必须在主服务器和从服务器上总是使用相同的全局字符集和校对规则(--default-character-set、--default-collation)。否则，会在从服务器上遇到复制键值错误，因为在主服务器的字符集中被认为是唯一的键值在从服务器的字符集中可能不是唯一的。

2.如果主服务器早于MySQL 4.1.3，则会话中的字符集不应与其全局值不同(换句话说，不要使用SET NAMES、SET CHARACTER SET等等)，因为从服务器不知道该字符集的更改。如果主服务器和从服务器均为4.1.3或更新版，可以随便将会话的字符集变量设置为本地值(例如NAMES、CHARACTER SET、COLLATION_CLIENT和COLLATION_SERVER)，因为这些设定值被写入二进制日志，因此从服务器知道。然而，禁止更改会话中这些变量的全局值；如前面所述，主服务器和从服务器必须具有唯一的全局字符集值。

3.如果在主服务器上的数据库的字符集与全局collation_server值不同，则应设计CREATE TABLE语句，以便它们不隐含依赖数据库的默认字符集(Bug #2326)；一个好的解决办法是在CREATE TABLE中明显说明字符集和校对规则。

·应在主服务器和从服务器上设置相同的系统时区。否则一些语句，例如使用NOW()或FROM_UNIXTIME()函数的语句，将不会正确复制。可以使用脚本mysqld_safe的--timezone=timezone_name

选项或通过设置TZ环境变量设置MySQL服务器运行的系统的时区。主服务器和从服务器还应有相同的默认连接时区设置；即主服务器和从服务器应有相同的--default-time-zone参数值。

·除了关闭从服务器(而不仅仅是从服务器线程) 临时表都被复制，并且还没有在从服务器上执行的更新所使用的临时表也已经复制。如果关闭从服务器，从服务器重启后更新需要的那些临时表不可再用。为了避免该问题，临时表打开时不要关闭从服务器。而应遵照下面的程序：

1.执行STOP SLAVE语句。

2.使用SHOW STATUS检查slave_open_temp_tables变量的值。

3.如果值为0，使用mysqladmin shutdown命令关闭从服务器。

4.如果值不为0，用START SLAVE重启从服务器线程。

5.后面再重复该程序看下次的运气是否好一些。

我们计划在不久的将来修复该问题。

·可以很安全地连接用--logs-slave-updates选项指定的循环主服务器/从服务器关系中的服务器。但请注意许多语句在这种设置中不能正确工作，除非你的客户代码关注了潜在的在不同的服务器不同顺序的更新中可能发生的这类问题。

这说明你可以象这样创建设置：

服务器ID被编码在二进制日志事件中，因此服务器A知道何时自己首次创建它读取的事件并且不执行事件(除非用--replicate-same-server-id选项启动了服务器A，只在很少情况下有意义)。这样，没有无限循环。只有对表执行没有冲突的更新时该类循环设置才能工作。换句话说，如果在A和C中插入数据，绝对不应在A中插入键值可能与插入到C中的行相冲突的一行。如果更新的顺序很重要，还不应更新两个服务器上相同的行。

·如果从服务器上的某个语句产生错误，则从服务器上的SQL线程终止，并且从服务器向错误日志写入一条消息。此时应手动连接从服务器，修复该问题(例如，一个不存在的表)，然后运行START SLAVE。

·可以很安全地关闭主服务器并在以后重启。如果某个从服务器丢失与主服务器的连接，从服务器尝试立即重新连接。如果失败，从服务器定期重试。(默认设置是每60秒重试一次。可以通过--master-connect-retry选项更改）。从服务器也能够处理网络连接中断。但是，只有从服务器超过slave_net_timeout秒没有从主服务器收到数据才通知网络中断。如果中断时间短，可以降低slave_net_timeout。参见5.3.3节，“服务器系统变量”。

·关闭从服务器（净关闭）也很安全，因为它可以跟踪它离开的地点。不纯净 的关闭操作会产生问题，特别是系统关闭前硬盘缓存未刷新到硬盘上时。如果有不间断电源，可以大大提高系统容错能力。不纯净的关闭主服务器会造成主服务器上 的表和二进制日志内容之间的不一致性；在主服务器上使用InnoDB表和--innodb-safe-binlog选项可以避免该问题。参见5.11.3节，“二进制日志”。(注释：MySQL 5.1中不需要--innodb-safe-binlog，由于引入了XA事务支持已经作废了）。

·由于MyISAM表的非事务属性，可以有一 个语句只是更新一个表并返回错误代码。例如，多行插入时有一个行超过键值约束，或者如果长的更新语句更新部分行后被杀掉了。如果发生在主服务器上，除非错 误代码合法并且语句执行产生相同的错误代码，从服务器线程将退出并等待数据库管理员决定如何做。如果该错误代码验证行为不理想，可以用--slave-skip-errors选项掩盖(忽视)部分或全部错误。

·如果从BEGIN/COMMIT系列的非事务表更新事务表，如果提交事务前更新非事务表，对二进制日志的更新可能会不同步。这是因为事务提交后才被写入二进制日志。

·事务混合更新事务表和非事务表时，二进制日志中语句的顺序是正确的，即使在ROLLBACK时，所有需要的语句也会写入二进制日志。但是如果在第1个连接的事务完成前，第2个连接更新非事务表，语句记入日志时会出现顺序错误，因为第2个连接的更新执行完后立即写入日志，而不管第1个连接执行的事务的状态如何。

6.8. 复制启动选项

在主服务器和从服务器上，均必须使用server-id选项为每个服务器建立唯一的复制ID。你应为每个主服务器和从服务器从1到2³²

–1的范围挑一个唯一的正整数。例如：server-id=3

用于主服务器上控制二进制日志的选项的相关描述见5.11.3节，“二进制日志”。

下表描述了可以用于MySQL 5.1从属复制服务器的选项。你可以在命令行中或在选项文件中指定这些选项。

某些从服务器复制选项按特殊方式处理，当从服务器启动时如果master.info文件存在并且包含选项值，它们将被忽略掉。下面的选项按这种方式处理：

·--master-host

·--master-user

·--master-password

·--master-port

·--master-connect-retry

·--master-ssl

·--master-ssl-ca

·--master-ssl-capath

·--master-ssl-cert

·--master-ssl-cipher

·--master-ssl-key

5.1中的master.info文件格式包括对应SSL选项的值。并且，文件格式包括文件中的行号，如同第1行。如果你将旧的服务器升级到新的版本，新服务器启动时自动将smaster.info文件升级到新的格式。然而，如果将新服务器降级到旧的版本，首次启动旧版本的服务器之前应删除第1行。

如果从服务器启动时master.info文件不存在，选项采用选项文件或命令行中指定的值。首次将服务器作为从服务器启动时，或者已经运行RESET SLAVE然后已经关闭并重启从服务器时会发生。

如果从服务器启动时master.info文件存在，服务器忽略那些选项。使用master.info文件中发现的值。

如果你使用与master.info文件中相对应的启动选项的不同的值重启从服务器，启动选项的不同的值不会生效，因为服务器继续使用master.info文件。要想使用启动选项的不同的值，必须删除master.info文件并重启从服务器，或(最好是)在从服务器运行时使用CHANGE MASTER TO语句重新设置值。

假定在my.cnf文件中指定该选项：

master-host=some_host

第1次作为复制从服务器启动服务器时，从my.cnf文件读取并使用选项。服务器然后记录master.info文件中的值。下次启动服务器时，它只从服务器的master.info文件读取主服务器主机值并忽略选项文件中的值。如果你修改my.cnf文件为some_other_host指定其它主服务器主机，更改仍然不会生效。你应使用CHANGE MASTER TO。

因为服务器给已有master.info文件的优先权高于刚刚描述的启动选项，可以选择不使用这些值的启动选项，而是使用CHANGE MASTER TO语句来指定。参见13.6.2.1节，“CHANGE MASTER TO语法”。

下面的例子显示了如何更广泛地使用启动选项来配置从服务器：

server-id=2

master-host=db-master.mycompany.com

master-port=3306

master-user=pertinax

master-password=freitag

master-connect-retry=60

report-host=db-slave.mycompany.com

下面列出了控制复制的启动选项：许多选项可以在服务器运行时通过CHANGE MASTER TO语句重新进行设置。其它选项，例如--replicate-*选项，只能在从服务器启动时进行设置。我们计划将修复该问题。

通常情况，从服务器从主服务器接收到的更新不记入它的二进制日志。该选项告诉从服务器将其SQL线程执行的更新记入到从服务器自己的二进制日志。为了使该选项生效，还必须用--logs-bin选项启动从服务器以启用二进制日志。如果想要应用链式复制服务器，应使用--logs-slave-updates。例如，可能你想要这样设置：

也就是说，A为从服务器B的主服务器，B为从服务器C的主服务器。为了能工作，B必须既为主服务器又为从服务器。你必须用--logs-bin启动A和B以启用二进制日志，并且用--logs-slave-updates选项启动B。

让从服务器向错误日志输出更详细的关于其执行操作的消息。例如，通知你网络/连接失败后已经成功重新连接，并通知你每个从服务器线程如何启动。该选项默认启用；要想禁用它，使用--skip-logs-warnings。放弃的连接不记入错误日志，除非该值大于1。

请注意该选项的效果不限于复制。可以对服务器的部分动作产生警告。

在主服务器宕机或连接丢失的情况下，从服务器线程重新尝试连接主服务器之前睡眠的秒数。如果主服务器.info文件中的值可以读取则优先使用。如果未设置， 默认值为60。

主复制服务器的主机名或IP地址。如果没有给出该选项，从服务器线程不启动。如果主服务器.info文件中的值可以读取则优先使用。

从服务器用于记录主服务器的相关信息使用的文件名。默认名为数据目录中的mysql.info。

连接主服务器时从服务器线程用于鉴定的账户的密码。如果主服务器.info文件中的值可以读取则优先使用。如果未设置，假定 密码为空。

主服务器正帧听的TCP/IP端口号。如果主服务器.info文件中的值可以读取则优先使用。如果未设置，假定使用编译进来的设定值。如果你未曾用configure选项进行修改，该值应为3306。

这些选项用于使用SSL设置与主服务器的安全复制连接。它们的含义与5.8.7.6节，“SSL命令行选项”中描述的相应—ssl、--ssl-ca、--ssl-capath、--ssl-cert、--ssl-cipher、--ssl-key选项相同。如果主服务器.info文件中的值可以读取则优先使用。

连接主服务器时从服务器线程用于鉴定的账户的用户名。该账户必须具有REPLICATION SLAVE权限。如果主服务器.info文件中的值可以读取则优先使用。如果未设置主服务器用户，假定使用用户test。

自动循环中继日志。参见5.3.3节，“服务器系统变量”。

该选项让从服务器只允许来自从服务器线程或具有SUPER权限的用户的更新。可以确保从服务器不接受来自客户的更新。

中继日志名。默认名为host_name-relay-bin.nnnnnn

，其中

是从服务器主机的名，

表示中继日志在编号序列中创建。如果中继日志太大(并且你不想降低max_relay_log_size)，需要将它们放到数据目录之外的其它地方，或者如果想要通过硬盘之间的负载均衡提高速度，可以指定选项创建与主机名无关的中继日志名。

中继日志索引文件使用的位置和名称。默认名为host_name-relay-bin.index

，其中

为从服务器名。

从服务器用于记录中继日志相关信息的文件名。默认名为数据目录中的relay-log.info。

禁用或启用不再需要中继日志时是否自动清空它们。默认值为1(启用)。这是一个全局变量，可以用SET GLOBAL Relay_log_purge动态更改。

限制所有中继日志在从服务器上所占用空间的上限(0值表示“无限制”)。从服务器主机硬盘空间有限时很有用。达到限制后，I/O线程停止从主服务器读取二进制日志中的事件，直到SQL线程被闭锁并且删除了部分未使用的中继日志。请注意该限制并不是绝对的：有可能SQL线程删除中继日志前需要更多的事件。在这种情况下，I/O线程将超过限制，直到SQL线程可以删除部分中继日志。(不这样做将会造成死锁）。--relay-log-space-limit的值不能小于--max-relay-logs-size(或如果--max-relay-logs-size为0，选--max-binlog-size)的值的两倍。在这种情况下，有可能I/O线程等待释放空间，因为超过了--relay-log-space-limit，但SQL线程没有要清空的中继日志，不能满足I/O线程的需求。强制I/O线程临时忽视--relay-log-space-limit。

·--replicate-do-db=db_name

告诉从服务器限制默认数据库(由USE所选择)为db_name

的语句的复制。要指定多个数据库，应多次使用该选项，每个数据库使用一次。请注意不复制跨数据库的语句，例如当已经选择了其它数据库或没有数据库时执行UPDATE

SET foo='bar'。如果需要跨数据库进行更新，使用--replicate-wild-do-table=

.%。请读取该选项列表后面的注意事项。

一个不能按照期望工作的例子：如果用--replicate-do-db=sales启动从服务器，并且在主服务器上执行下面的语句，UPDATE语句不会复制：

UPDATE sales.january SET amount=amount+1000;

如果需要跨数据库进行更新，应使用--replicate-wild-do-table=db_name.%。

“只检查默认数据库”行为的主要原因是语句自己很难知道它是否应被复制(例如，如果你正使用跨数据库的多表DELETE语句或多表UPDATE语句)。如果不需要，只检查默认数据库比检查所有数据库要快得多。

告诉从服务器线程限制对指定表的复制。要指定多个表，应多次使用该选项，每个表使用一次。同--replicate-do-db对比，允许跨数据库更新。请读取该选项列表后面的注意事项。

告诉从服务器不要复制默认数据库(由USE所选择)为db_name

的语句。要想忽略多个数据库，应多次使用该选项，每个数据库使用一次。如果正进行跨数据库更新并且不想复制这些更新，不应使用该选项。请读取该选项后面的注意事项。

一个不能按照期望工作的例如：如果用--replicate-ignore-db=sales启动从服务器，并且在主服务器上执行下面的语句，UPDATE语句不会复制：

·UPDATE sales.january SET amount=amount+1000;

如果需要跨数据库更新，应使用--replicate-wild-ignore-table=db_name.%。

告诉从服务器线程不要复制更新指定表的任何语句(即使该语句可能更新其它的表)。要想忽略多个表，应多次使用该选项，每个表使用一次。同--replicate-ignore-db对比，该选项可以跨数据库进行更新。请读取该选项后面的注意事项。

告诉从服务器线程限制复制更新的表匹配指定的数据库和表名模式的语句。模式可以包含‘%’和‘_’通配符，与LIKE模式匹配操作符具有相同的含义。要指定多个表，应多次使用该选项，每个表使用一次。该选项可以跨数据库进行更新。请读取该选项后面的注意事项。

例如：--replicate-wild-do-table=foo%.bar%只复制数据库名以foo开始和表名以bar开始的表的更新。

如果表名模式为%，可匹配任何表名，选项也适合数据库级语句(CREATE DATABASE、DROP DATABASE和ALTER DATABASE)。例如，如果使用--replicate-wild-do-table=foo%.%，如果数据库名匹配模式foo%，则复制数据库级语句。

要想在数据库或表名模式中包括通配符，用反斜线对它们进行转义。例如，要复制名为my_own%db的数据库的所有表，但不复制my1ownAABCdb数据库的表，应这样转义‘_’和‘%’字符：--replicate-wild-do-table=my\_own\%db。如果在命令行中使用选项，可能需要双反斜线或将选项值引起来，取决于命令解释符。例如，用bash外壳则需要输入--replicate-wild-do-table=my\\_own\\%db。

告诉从服务器线程不要复制表匹配给出的通配符模式的语句。要想忽略多个表，应多次使用该选项，每个表使用一次。该选项可以跨数据库进行更新。请读取该选项后面的注意事项。

例如：--replicate-wild-ignore-table=foo%.bar%不复制数据库名以foo开始和表名以bar开始的表的更新。

关于匹配如何工作的信息，参见--replicate-wild-do-table选项的描述。在选项值中包括通配符的规则与--replicate-wild-ignore-table相同。

告诉从服务器如果默认数据库(由USE所选择)为主服务器上的from_name

，则翻译为

。只影响含有表的语句(不是类似CREATE DATABASE、DROP DATABASE和ALTER DATABASE的语句)，并且只有

为主服务器上的默认数据库时。该选项不可以跨数据库进行更新。请注意在测试--replicate-*规则之前翻译数据库名。

如果在命令行中使用该选项， ‘>’字符专用于命令解释符，应将选项值引起来。例如：

将用于从服务器上。通常可以默认设置为0以防止循环复制中的无限循环。如果设置为1，该从服务器不跳过有自己的服务器id的事件；通常只在有很少配置的情况下有用。如果使用--logs-slave-updates不能设置为1。请注意默认情况下如果有从服务器的id，服务器I/O线程不将二进制日志事件写入中继日志(该优化可以帮助节省硬盘的使用)。因此如果想要使用--replicate-same-server-id，让从服务器读取自己的SQL线程执行的事件前，一定要用该选项启动。

从服务器注册过程中报告给主服务器的主机名或IP地址。该值出现在主服务器上SHOW SLAVE HOSTS的输出中。如果不想让从服务器自己在主服务器上注册，则不设置该值。请注意从服务器连接后，主服务器仅仅从TCP/IP套接字读取从服务器的IP号是不够的。由于 NAT和其它路由问题，IP可能不合法，不能从主服务器或其它主机连接从服务器。

连接从服务器的TCP/IP端口号，从服务器注册过程中报告给主服务器。只有从服务器帧听非默认端口或如果有一个特殊隧道供主服务器或其它客户连接从服务器时才设置它。如果你不确定，不设置该选项。

告诉从服务器当服务器启动时不启动从服务器线程。使用START SLAVE语句在以后启动线程。

如果该选项设置为 1，如果从服务器和主服务器均支持，使用压缩从服务器/主服务器协议。

从服务器创建临时文件的目录名。该选项默认等于tmpdir系统变量的值。当从服务器SQL线程复制LOAD DATA INFILE语句时，从中继日志将待装载的文件提取到临时文件，然后将这些文件装入到表中。如果装载到主服务器上的文件很大，从服务器上的临时文件也很大。因此，建议使用该选项告诉从服务器将临时文件放到文件系统中有大量可用空间的目录下。在这种情况下，也可以使用--relay-log选项将中继日志放到该文件系统中，因为中继日志也很大。--slave-load-tmpdir应指向基于硬盘的文件系统，而非基于内存的文件系统：从服务器需要用临时文件在机器重启时用于复制LOAD DATA INFILE。系统启动过程中操作系统也不能清除该目录。

放弃读之前从主服务器等候更多数据的秒数，考虑到连接中断和尝试重新连接。超时后立即开始第1次重试。由--master-connect-retry选项控制重试之间的间隔。

通常情况，当出现错误时复制停止，这样给你一个机会手动解决数据中的不一致性问题。该选项告诉从服务器SQL线程当语句返回任何选项值中所列的错误时继续复制。

如果你不能完全理解为什么发生错误，则不要使用该选项。如果复制设置和客户程序中没有bug，并且MySQL自身也没有bug，应不会发生停止复制的错误。滥用该选项会使从服务器与主服务器不能保存同步，并且你找不到原因。

对于错误代码，你应使用从服务器错误日志中错误消息提供的编号和SHOW SLAVE STATUS的输出。服务器错误代码列于附录B：错误代码和消息。

你也可以(但不应)使用不推荐的all值忽略所有错误消息，不考虑所发生的错误。无需而言，如果使用该值，我们不能保证数据的完整性。在这种情况下，如果从服务器的数据与主服务器上的不相近请不要抱怨(或编写bug报告)。已经警告你了。

例如：

--slave-skip-errors=all

从服务器按下面评估--replicate-*规则，确定是否执行或忽视语句：

1.是否有--replicate-do-db或--replicate-ignore-db规则？

·有：测试--binlog-do-db和--binlog-ignore-db(参见5.11.3节，“二进制日志”)。测试结果是什么？

o忽视语句：忽视并退出。

o许可语句：不立即执行语句。推迟决策；继续下一步。

·没有

：继续下一步。

2.我们目前正执行保存的程序或函数吗？

·是

：执行查询并退出。

·否

：继续下一步。

3.是否有--replicate-*-table规则？

·没有

：执行查询并退出。

·有

：继续下一步并开始按所示顺序评估表规则(首先是非通配规则，然后是通配规则)。只有待更新的表根据这些规则进行比较(INSERT INTO sales SELECT * FROM prices:只有sales根据这些规则进行比较)。如果要更新几个表(多表语句)，第1个匹配的表(匹配“do”或“ignore”)获赢。也就是说，根据这些规则比较第1个表。然后，如果不能进行决策，根据这些规则比较第2个表等等。

4.是否有--replicate-do-table规则？

·有

：表匹配吗？

o是

：执行查询并退出。

o否

：继续下一步。

·没有

：继续下一步。

5.是否有--replicate-ignore-table规则？

·有

：表匹配吗？

o是

：忽视查询并退出。

o否

：继续下一步。

·没有

：继续下一步。

6.是否有--replicate-wild-do-table规则？

·有

：表匹配吗？

o是

：执行查询并退出。

o否

：继续下一步。

·没有

：继续下一步。

7.是否有--replicate-wild-ignore-table规则？

·有

：表匹配吗？

o是

：忽视查询并退出。

o否

：继续下一步。

·没有

：继续下一步。

8.没有匹配的--replicate-*-table规则。要根据这些规则测试其它表吗？

·是

：执行循环。

·否

：我们现在已经测试了所有待更新的表，结果不能匹配任何规则。是否有--replicate-do-table或--replicate-wild-do-table规则？

o有

：有“do”规则但不匹配。忽视查询并退出。

o没有

：执行查询并退出。

6.9. 复制FAQ

Q：如果主服务器正在运行并且不想停止主服务器，怎样配置一个从服务器？

A：有多种方法。如果你在某时间点做过主服务器备份并且记录了相应快照的二进制日志名和偏移量(通过SHOW MASTER STATUS命令的输出)，采用下面的步骤：

1.确保从服务器分配了一个唯一的服务器ID号。

2.在从服务器上执行下面的语句，为每个选项填入适当的值：

    ->     MASTER_USER='master_user_name',

    ->     MASTER_PASSWORD='master_pass',

    ->     MASTER_LOG_FILE='recorded_log_file_name',

    ->     MASTER_LOG_POS=recorded_log_position;

3.在从服务器上执行START SLAVE语句。

如果你没有备份主服务器，这里是一个创建备份的快速程序。所有步骤都应该在主服务器主机上执行。

1.发出该语句：

2.仍然加锁时，执行该命令（或它的变体）：

3.发出该语句并且确保记录了以后用到的输出：

4.释放锁：

一个可选择的方法是，转储主服务器的SQL来代替前面步骤中的二进制复制。要这样做，你可以在主服务器上使用mysqldump --master-data，以后装载SQL转储到到你的从服务器。然而，这比进行二进制复制速度慢。

不管你使用这两种方法中的那一个，当你有一个快照和记录了日志名与偏移量时，后来根据说明操作。你可以使用相同的快照建立多个从服务器。一旦你拥有主服务器的一个快照，可以等待创建一个从服务器，只要主服务器的二进制日志完整。两个能够等待的时间实际的限制是指在主服务器上保存二进制日志的可用硬盘空间和从服务器同步所用的时间。

你也可以使用LOAD DATA FROM MASTER。这是一个方便的语句，它传输一个快照到从服务器并且立即调整日志名和偏移量。将来，LOAD DATA FROM MASTER将成为创建从服务器的推荐方法。然而需要注意，它只工作在MyISAM表上并且可能长时间持有读锁定。它并不象我们希望的那样高效率地执行。如果你有大表，执行FLUSH TABLES WITH READ LOCK语句后，这时首选方法仍然是在主服务器上制作二进制快照。

Q：从服务器需要始终连接到主服务器吗？

A：不，不需要。从服务器可以宕机或断开连接几个小时甚至几天，重新连接后获得更新信息。例如，你可以在通过拨号的链接上设置主服务器/从服务器关系

，其中

Q：我怎样知道从服务器与主服务器的最新比较? 换句话说，我怎样知道从服务器复制的最后一个查询的日期？

A：你可以查看SHOW SLAVE STATUS语句的Seconds_Behind_Master列的结果。参见6.3节，“复制实施细节”。

当从服务器SQL线程执行从主服务器读取的事件时，它根据事件时间戳修改自己的时间（这是TIMESTAMP能够很好复制的原因）。在SHOW PROCESSLIST语句输出的Time列内，为从服务器SQL线程显示的秒数是最后一个复制事件的时间戳和从服务器主机的实际时间之间相差的秒数。你可以使用它来确定最后一个复制事件的日期。注意，如果你的从服务器与主服务器连接断开一个小时，然后重新连接，在SHOW PROCESSLIST结果中，你可以立即看到从服务器SQL线程的Time值为3600。这可能是因为从服务器执行的语句是一个一小时之前的。

Q：我怎样强制主服务器阻塞更新直到从服务器同步？

A：使用下面的步骤：

1.在主服务器上，执行这些语句：

记录SHOW语句的输出的日志名和偏移量。这些是复制坐标。

2.在从服务器上，发出下面的语句

，其中

SELECT语句阻塞直到从服务器达到指定的日志文件和偏移量。此时，从服务器与主服务器同步，语句返回。

3.在主服务器上，发出下面的语句允许主服务器重新开始处理更新：

Q：当设置双向复制时我应该知道发出那些语句？

A：MySQL复制目前不支持主服务器和从服务器之间的任何锁定协议来保证分布式(跨服务器)更新的原子性。换句话说，这样做是可能的：客户A根据协作-主服务器1更新，同时，在它传给协作-主服务器2之前，客户B能够根据协作-主服务器2更新，这样客户A的更新与它在协作-主服务器1的更新不同。这样，当客户A根据协作-主服务器2更新时，它产生的表与在协作-主服务器1上的不同，即使所有根据协作-主服务器2的更新已经传过来。这意味着，在双向复制关系中，你不应该把两个服务器串连在一起，除非你确信任何顺序的更新是安全的，或者除非你在客户端代码中注意怎样避免更新顺序错误。

你还必须认识到从更新角度，双向复制实际上并不能显著地提高性能（或者根本不能提高性能）。两个服务器都需要做相同数量的更新，如同在一个服务器做的那样。唯一的差别是锁竞争要少，这因为源于另一个服务器的更新在一个从线程中序列化。即使这个益处可能被网络延迟抵消。

Q：怎样通过复制来提高系统的性能？

A：你应将一个服务器设置为主服务器并且将所有写指向该服务器。然后根据预算配置尽可能多的从服务器以及栈空间，并且在主服务器和从服务器之间分发读取操作。你也可以用--skip-innodb、--skip-bdb、--low-priority-updates以及--delay-key-write=ALL选项启动从服务器，以便在从服务器端提高速度。在这种情况下，为了提高速度，从服务器使用非事务MyISAM表来代替InnoDB和BDB表。

Q：为了使用高性能的复制，我应该在自己的应用程序中怎样准备客户端代码？

A：如果你的代码中数据库访问部分已经正确地模块化，应该能够平滑和容易地转换为在 复制步骤中运行的代码。仅需要更改数据库访问执行部分，以便发送所有的写操作到主服务器，以及发送读操作到主服务器或某个从服务器。如果你的代码没有这个 级别，设置一个复制系统以便清除。应先通过下面的函数创建一个包装库或模块：

每个函数名的safe_意味着函数比较小心地处理所有错误。你可以使用不同名的函数。重要是对于读连接、写连接、读和写有一个统一的接口。

然后，你应该转换客户端代码使用包装库。刚开始这可能是痛苦和恐慌的过程，但从长远来看是值得的。使用刚才讨论的方法的所有应用程序都能够利用主服务器/从服务器配置的优越性，即使是含有多个从服务器的配置。代码非常容易维护，并且添加排错选项也很容易。你仅需要修改一两个函数；例如，记录每个语句执行的时间，或者你的上千个语句中哪个语句发生了错误。

如果你已经编写了许多代码，你可能想使用replace工具自动进行转换，该工具随标准MySQL一起发布，或可以自己编写转换脚本。理想情况，你的代码使用一致的程序转换风格。否则，可能最好重新编写代码，或者至少手工对其进行规则化以使用一致的风格。

Q：MySQL复制能够何时和多大程度提高系统性能？

A：MySQL复制对于频繁读和频繁写的系统具有最大好处。理论上，通过使用单个主服务器/多从服务器设置，可以通过添加更多的从服务器来扩充系统，直到用完网络带宽，或者你的更新负载已经增长到主服务器不能处理的点。

在获得的收益开始吃平之前，为了确定可以有多少从服务器，以及可以将你的站点的性能提高多少，需要知道查询模式，并且要通过基准测试并根据经验确定一个典型的主服务器和从服务器中的读取（每秒钟读取量，或者max_reads）吞吐量和写（max_writes）吞吐量的关系。通过一个假设的带有复制的系统，本例给出了一个非常简单的计算结果。

假设系统负载包括10%的写和90%的读取，并且我们通过基准测试确定max_reads是1200 –2 × max_writes。换句话说，如果没有写操作，系统每秒可以进行1,200次读取操作，平均写操作是平均读操作所用时间的两倍，并且关系是线性的。我们假定主服务器和每个从服务器具有相同的性能，并且我们有一个主服务器和N

个从服务器。那么，对于每个服务器（主服务器或从服务器），我们有：

最后的等式表明了N

个从服务器的最大写操作数，假设最大可能的读取速率是每分钟1,200次，读操作与写操作的比率是9。

如上分析可以得到下面的结论：

·如果N

= 0（这表明没有复制），系统每秒可以处理大约1200/11 = 109个写操作。

·如果N

= 1，每秒得到184个写操作。

·如果N

= 8，每秒得到400个写操作。

·如果N

= 17，每秒得到480个写操作。

·最后，当 N

趋于无穷大（以及我们预算的负无穷大）时，可以得到非常接近每秒600个写操作，系统吞吐量增加将近5.5倍。然而，如果只用8个服务器，增加接近4倍。

请注意，这些计算假设网络带宽无穷大并忽略掉了其它一些因素，那些因素可能对系统产生重要的影响。在许多情况下，不能执行与刚才类似的计算，即如果添加N

台复制从服务器，应该准确预报系统将发生哪些影响。回答下面的问题应能够帮助你确定复制是否和在多大程度上能够提高系统的性能：

·系统上的读取/写比例是什么?

·如果减少读取操作，一个服务器可以多处理多少写负载？

·网络带宽可满足多少从服务器的需求?

Q：如何使用复制来提供冗余/高可用性?

A：利用目前的可用特性，必须设置一个主服务器和一个从服务器（或多个从服务器），以及写一个脚本来监视主服务器是否启动。如果主服务器失败，通知应用程序和从服务器切换主服务器。下面是一些建议：

·告知从服务器更改其主服务器，使用CHANGE MASTER TO语句。

·通知应用程序主服务器位置的一个很好的方法是对主服务器提供动态DNS入口。用bind可以使用nsupdate动态更新DNS。

·应该用--logs-bin选项而不用 --logs-slave-updates选项运行从服务器。这样，一旦你在其它从服务器上发出STOP SLAVE; RESET MASTER, 以及CHANGE MASTER TO语句，该从服务器可以切换为主服务器。例如，假设有下面的设置：

·        \

·         v

· WC----> M

·       / | \

·      /  |  \

·     v   v   v

·    S1   S2  S3

M代表主服务器，S代表从服务器，WC代表发出数据库写和读取操作的客户；只发出数据库读取操作的客户没有给出，因为它们不需要切换。S1、S2以及S3是从服务器，用--logs-bin选项而没有用--logs-slave-updates运行。因为从服务器收到的主服务器的更新没有记录在二进制日志中，除非指定 --logs-slave-updates选项，每个从服务器上的二进制日志是空的。如果因为某些原因M 变得不可用，你可以选取一个从服务器变为新的主服务器。例如，如果你选取了S1，所有WC应该重新指向S1和S2，并且S3然后应从S1复制。

确保所有从服务器已经处理了中继日志中的所有语句。在每个从服务器上，发出STOP SLAVE IO_THREAD语句，然后检查SHOW PROCESSLIST语句的输出，直到你看到Has read all relay log。当所有从服务器都执行完这些，它们可以被重新配置为一个新的设置。在被提升为主服务器的从服务器S1上，发出STOP SLAVE和RESET MASTER语句。

在其它从服务器S2和S3上，使用STOP SLAVE和CHANGE MASTER TO MASTER_HOST='S1'（其中'S1'表示S1实际的主机名）。为CHANGE MASTER添加关于从S2或S3如何连接到S1的所有信息（user、password、port）。在CHANGE MASTER命令中，不需要指定从其读取的S1的二进制日志名或二进制日志位置：我们知道它是第1个二进制日志，位置是4，这是CHANGE MASTER命令的默认值。最后，在S2和S3上使用START SLAVE 命令。

然后，指示所有WC把它们的语句指向S1。此后，WC发出的所有发送到S1的更新语句被写入S1的二进制日志，S1则包含M死掉之后的发送到 S1的每一个更新语句。

结果是下面的配置：

      /

      |

 WC   |  M(unavailable)

  \   |

   \  |

    v v

     S1<--S2  S3

      ^       |

      +-------+

当M重新启动后，你必须在M上发出相同的CHANGE MASTER语句，与在S2和S3上发出的语句一样，以便M变为S1的从服务器并且恢复在它宕机后丢失的所有WC写操作。要把 M 再次作为主服务器（例如，因为它是功能最强的机器），使用前面的步骤，好像S1不可用并且M变为一个新的主服务器一样。在这个过程中，在S1、S2以及S3作为M的从服务器之前，不要忘记在M上运行RESET MASTER。否则，它们可能拾取M变得不可用之前的旧WC写操作。

我们目前正在MySQL集成自动主服务器选择系统，但在准备好之前，你必须创建自己的监控工具。

6.10. 复制故障诊断与排除

如果你遵从了上述说明，复制设置仍然不工作，首先检查下面各项：

·检查错误日志的消息。许多用户遇到问题后没有及时地这样做而浪费了时间。

·主服务器记录到了二进制日志？用SHOW MASTER STATUS检查。如果已经记录，Position应为非零。如果没有记录，确认正用log-bin和server-id选项运行主服务器。

·是否从服务器在运行？使用SHOWSHOW SLAVE STATUS检查是否slave_IO_Running和slave_SQL_Running的值均为Yes。如果不是，验证当启动从服务器时使用的选项。

·如果从服务器正在运行，建立了与主服务器的连接吗？使用SHOW PROCESSLIST，找出I/O和SQL线程并检查它们的State列看它们如何显示。参见6.3节，“复制实施细节”。如果I/O线程状态为Connecting to master，验证主服务器上复制用户的权限、主服务器主机名、DNS设置，是否主服务器真正在运行，以及是否可以从从属服务器访问。

·如果从服务器以前在运行但是现在已经停止，原因通常是在主服务器上成功的部分语句在从服务器上失败了。如果你正确快照了主服务器，并且从来没有不通过服务器线程修改从服务器上的数据，这种现象不应发生。如果发生，应为一个bug或你遇到了一个6.7节，“复制特性和已知问题”描述的已知的复制限制。如果是一个bug，参见6.11节，“通报复制缺陷”查阅如何通报的说明。

·如果某个在主服务器上成功的语句拒绝在从服务器上运行，并且不能执行完全的数据库重新同步(即删除从服务器的数据库并从主服务器复制新的快照)，尝试：

1.确定是否从服务器的表与主服务器的不同。尽力了解发生的原因。然后让从服务器的表与主服务器的一样并运行START SLAVE。

2.如果前面的步骤不工作或不适合，尽力了解手动更新是否安全(如果需要)，然后忽视来自主服务器的下一个语句。

3.如果你确定可以跳过来自主服务器的下一个语句，执行下面的语句：

5.mysql> START SLAVE；

如果来自主服务器的下一个语句不使用AUTO_INCREMENT或LAST_INSERT_ID()，n

值应为1。否则，值应为2。使用AUTO_INCREMENT或LAST_INSERT_ID()的语句使用值2的原因是它们从主服务器的二进制日志中取两个事件。

6.如果你确保从服务器启动时完好地与主服务器同步，并且没有更新从服务器线程之外的表，则大概诧异是由于bug。如果你正运行最近的版本，请通报该问题。如果你正运行旧版本MySQL，尽力升级到最新的产品版本。

6.11. 通报复制缺陷

如果你确定没有用户错误，但复制仍然不工作或不稳定，则是向我们发送bug通报的时候了。我们需要尽可能从你那儿获得更多的信息已跟踪bug。请花一些时间和努力编写一份好的bug通报。

如果你有一个重复的测试案例来说明bug，请把它输入我们的bug数据库，位置为http://bugs.mysql.com/。如果你有一个“phantom”问题(不能按照期望进行复制)，则使用下面的程序：

1.确认未包括用户错误。例如，如果你不用从服务器线程来更新从服务器，数据将不同步，并且会遇到唯一的键值违背更新。在这种情况下，从服务器线程停止并等待你手动清理表使它们同步。这不是复制问题。这是一个外部接口问题造成复制失败。

2.用--logs-slave-updates和--logs-bin选项运行从服务器。这些选项使从服务器将从主服务器接收的更新记入自己的二进制日志。

3.重新设置复制状态之前保存所有的证据。如果我们没有信息或只有粗略的信息，则难以或不可能跟踪问题。应搜集的证据为：

·所有主服务器的二进制日志

·所有从服务器的二进制日志

·你发现问题时主服务器的SHOW MASTER STATUS的输出

·你发现问题时主服务器的SHOW SLAVE STATUS的输出

·主服务器和从服务器的错误日志

4.使用mysqlbinlog检查二进制日志。下面命令应有助于发现有问题的查询，例如：

6./path/to/log_from_slave_status | head

搜集了问题的证据后，首先作为一个测试案例隔离开。然后将问题输入我们的bug数据库，位置为http://bugs.mysql.com/，应提供尽可能多的信息。

6.12. 多服务器复制中的Auto-Increment

当将多个服务器配置为复制主服务器时，使用auto_increment时应采取特殊步骤以防止键值冲突，否则插入行时多个主服务器会试图使用相同的auto_increment值。

服务器变量auto_increment_increment和auto_increment_offset可以帮助协调多主服务器复制和AUTO_INCREMENT列。每个变量有一个默认的(并且是最小的)值1，最大值为65,535。

将这些变量设置为非冲突的值，当在同一个表主插入新行时，多主服务器配置主的服务器将不会与AUTO_INCREMENT值冲突。

这两个变量这样影响AUTO_INCREMENT列：

·auto_increment_increment控制列值增加的间隔。例如：

·+--------------------------+-------+

·| Variable_name            | Value |

·+--------------------------+-------+

·| auto_increment_increment | 1     |

·| auto_increment_offset    | 1     |

·+--------------------------+-------+

·2 rows in set (0.00 sec)

·

·mysql> CREATE TABLE autoinc1 (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

·Query OK, 0 rows affected (0.04 sec)

·

·mysql> SET @auto_increment_increment=10;

·Query OK, 0 rows affected (0.00 sec)

·

·mysql> SHOW VARIABLES LIKE 'auto_inc%';

·+--------------------------+-------+

·| Variable_name            | Value |

·+--------------------------+-------+

·| auto_increment_increment | 10    |

·| auto_increment_offset    | 1     |

·+--------------------------+-------+

·2 rows in set (0.01 sec)

·

·mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);

·Query OK, 4 rows affected (0.00 sec)

·Records: 4  Duplicates: 0  Warnings: 0

·

·mysql> SELECT col FROM autoinc1;

·+-----+

·| col |

·+-----+

·|   1 |

·|  11 |

·|  21 |

·|  31 |

·+-----+

·4 rows in set (0.00 sec)

考虑下面的命令，假定在前面所示示例中的相同的会话中执行这些命令：

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'auto_inc%';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| auto_increment_increment | 10    |

| auto_increment_offset    | 5     |

+--------------------------+-------+

2 rows in set (0.00 sec)

mysql> CREATE TABLE autoinc2 (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO autoinc2 VALUES (NULL), (NULL), (NULL), (NULL);

Query OK, 4 rows affected (0.00 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT col FROM autoinc2;

+-----+

| col |

+-----+

|   5 |

|  15 |

|  25 |

|  35 |

+-----+

4 rows in set (0.02 sec)

详细信息参见5.3.3节，“服务器系统变量”。

这是MySQL参考手册的翻译版本，关于MySQL参考手册，请访问dev.mysql.com。 原始参考手册为英文版，与英文版参考手册相比，本翻译版可能不是最新的。

--转自