MySQL主从复制的原理及配置方法（比较详细）

MySQL 复制基于主服务器在⼆进制⽇志中跟踪所有对数据库的更改(更新、删除等等)。每个从服务器从主服务器接收主服务器已经记录到其⼆进制⽇志的保存的更新，以便从服务器可以对其数据拷贝执⾏相同的更新。

将主服务器的数据拷贝到从服务器的⼀个途径是使⽤LOAD DATA FROM MASTER语句。请注意LOAD DATA FROMMASTER⽬前只在所有表使⽤MyISAM存储引擎的主服务器上⼯作。并且，该语句将获得全局读锁定。

MySQL 使⽤3个线程来执⾏复制功能,其中1个在主服务器上，另两个在从服务器上。当发出START SLAVE时，从服务器创建⼀个I/O线程，以连接主服务器并让它发送记录在其⼆进制⽇志中的语句。

主服务器创建⼀个线程将⼆进制⽇志中的内容发送到从服务器。该线程可以识别为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。

从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据⽬录中的本地⽂件中，即中继⽇志。

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

有多个从服务器的主服务器创建为每个当前连接的从服务器创建⼀个线程；每个从服务器有⾃⼰的I/O和SQL线程。复制代码 代码如下:

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

线程停⽌时发⽣的⼀个很简单的状态。复制代码 代码如下: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选项指定重试之间的间隔。

Reconnecting after a failed binlog dump request线程正尝试重新连接主服务器。

Waiting for master to send event

线程已经连接上主服务器，正等待⼆进制⽇志事件到达。如果主服务器正空闲，会持续较长的时间。如果等待持续slave_read_timeout秒，则发⽣超时。此时，线程认为连接被中断并企图重新连接。Queueing master event to the relay log

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

Waiting to reconnect after a failed master event read

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

Reconnecting after a failed master event read

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

Waiting for the slave SQL thread to free enough relay log space

正使⽤⼀个⾮零relay_log_space_limit值，中继⽇志已经增长到其组合⼤⼩超过该值。I/O线程正等待直到SQL线程处理中继⽇志内容并删除部分中继⽇志⽂件来释放⾜够的空间。Waiting for slave mutex on exit

线程停⽌时发⽣的⼀个很简单的状态。

复制代码 代码如下:

Reading event from the relay log

线程已经从中继⽇志读取⼀个事件，可以对事件进⾏处理了。

Has read all relay log; waiting for the slave I/O thread to update it

线程已经处理了中继⽇志⽂件中的所有事件，现在正等待I/O线程将新事件写⼊中继⽇志。Waiting for slave mutex on exit

线程停⽌时发⽣的⼀个很简单的状态。

从服务器靠中继⽇志来接收从主服务器上传回来的⽇志。并依靠状态⽂件来记录已经从主服务器接收了哪些⽇志，已经恢复了哪些⽇志。

中继⽇志与⼆进制⽇志的格式相同，并且可以⽤mysqlbinlog读取。SQL线程执⾏完中继⽇志中的所有事件并且不再需要之后，⽴即⾃动删除它。可以采⽤–relay-log和–relay-log-index服务器选项覆盖默认中继⽇志和索引⽂件名。其中索引⽂件名的作⽤是记录⽬前正在使⽤中继⽇志。

在下⾯的条件下将创建新的中继⽇志：

1.每次I/O线程启动时创建⼀个新的中继⽇志。

2.当⽇志被刷新时；例如，⽤FLUSH LOGS或mysqladmin flush-logs。3.当当前的中继⽇志⽂件变得太⼤时。“太⼤”含义的确定⽅法：max_relay_log_size，如果max_relay_log_size > 0max_binlog_size，如果max_relay_log_size = 0

状态⽂件名默认为master.info和relay-log.info。其中IO线程更新master.info⽂件，SQL线程更新relay-log.info⽂件。⽂件中的⾏和SHOW SLAVE STATUS显⽰的列的对应关系为：master.info⽂件：

复制代码 代码如下:⾏ 描述

1 ⽂件中的⾏号2 Master_Log_File

3 Read_Master_Log_Pos4 Master_Host5 Master_User

6 密码(不由SHOW SLAVE STATUS显⽰)7 Master_Port8 Connect_Retry

9 Master_SSL_Allowed10 Master_SSL_CA_File11 Master_SSL_CA_Path12 Master_SSL_Cert13 Master_SSL_Cipher14 Master_SSL_Keyrelay-log.info⽂件：复制代码 代码如下:⾏ 描述

1 Relay_Log_File2 Relay_Log_Pos

3 Relay_Master_Log_File4 Exec_Master_Log_Pos

当备份从服务器的数据时，你还应备份这两个⼩⽂件以及中继⽇志⽂件。它们⽤来在恢复从服务器的数据后继续进⾏复制。如果丢失了中继⽇志但仍然有 relay-log.info⽂件，你可以通过检查该⽂件来确定SQL线程已经执⾏的主服务器中⼆进制⽇志的程

度。然后可以⽤ Master_Log_File和Master_LOG_POS选项执⾏CHANGE MASTER TO来告诉从服务器重新从该点读取⼆进制⽇志。当然，要求⼆进制⽇志仍然在主服务器上。所以最好建议将⾃动删除中继⽇志的特性关闭，⼿⼯写shell⾓本来防⽌空间满的问题。

1.创建专门⽤于复制的⽤户(建议这样做)，从服务器采⽤该帐户登陆主服务器：复制代码 代码如下:

GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%' IDENTIFIED BY 'logzgh' ;

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

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

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

2.将数据库⽂件移到从服务器上情况⼀：若只⽤到MyISAM表

复制代码 代码如下:

mysql> FLUSH TABLES WITH READ LOCK;

(刷新所有表并且阻⽌其它写⼊，不要退出该客户端，以保持读锁有效。若退出，读锁就会释放。)⽐较简单的办法就是把数据⽬录打包压缩。

复制代码 代码如下:

$ tar -cvf /home/mysql/snapshot.tar ./data (在master上)$ tar -xvf /home/mysql/snapshot.tar (在slave上)

可能不需要同步 mysql 数据库，因为在slave上的权限表和master不⼀样。这时，解开压缩包的时候要排除它。同时在压缩包中也不要包含任何⽇志⽂件，和状态⽂件master.info、relay-log.info。复制代码 代码如下:

mysql> SHOW MASTER STATUS;

+——————+———-+————–+——————+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+——————+———-+————–+——————+| mysql-bin.000058 | 45036137 | | |

+——————+———-+————–+——————+mysql> UNLOCK TABLES;情况⼆：若⽤到InnoDB表

⽅法⼀：使⽤InnoDB Hot Backup⼯具。它⽆需在master上请求任何锁就能做到快照的⼀致性，并且在后⾯中在slave上要⽤到的快照中已经记录了⽇志⽂件名以及偏移位置。

⽅法⼆：记录当前⽇志⽂件及偏移位置，在master关闭前执⾏：复制代码 代码如下:

mysql> FLUSH TABLES WITH READ LOCK;mysql> SHOW MASTER STATUS;

尽快记下显⽰结果中的⽇志⽂件及偏移位置。然后，在不解锁的情况下关闭master，确保master上的快照和记录的结果⼀致。关闭master服务器，$ mysqladmin -u root shutdown

拷贝 InnoDB 数据⽂件，⽇志⽂件，以及表结构定义⽂件(.frm⽂件)。

情况三：可以同时⽤于MyISAM和InnoDB表

在master上做SQL转储⽽⽆需如上所述备份⼆进制⽇志。运⾏mysqldump –master-data命令，然后把结果⽂件转储到slave

上。

不过，这⽐拷贝⼆进制⽇志慢点。3.修改my.cnf⽂件

在master上my.cnf⽂件：(重启⽣效)

复制代码 代码如下:[mysqld]log_bin

server_id=1 (值是 1 到 2^32-1 之间的正整数)

在slave上my.cnf⽂件：复制代码 代码如下:[mysqld]

server_id=2 (ID必须和master的ID不同。若有多个slave，则每个slave都必须有唯⼀的id。)配置slave的扩展选项

复制代码 代码如下:

master_host=db-master.mycompany.commaster_port=3306master_user=rep

master_password=freitag

master_connect_retry=60 (若master宕机或者slave连接断开，slave会定期尝试连接到master上，重试的间隔由该选项来控制，默认值是60秒。)

report_host=db-slave.mycompany.com

slave_net_timeout=3600 (slave默认会在3600秒后，若还没收到来⾃master的数据，则会当作⽹络断开的情况来处理。)服务器认为master.info的优先级⽐配置⽂件my.cnf⾼，

第⼀次启动slave时，master.info不存在，它从my.cnf中读取选项值，然后把它们保存在master.info中。下次重启slave时，它只读取master.info的内容，⽽不会读取my.cnf中的选项值。

想要使⽤不同的选项值，可以删除master.info后重启slave，或者使⽤CHANGE MASTER TO语句(推荐)重置选项值。4.启动从服务器线程

复制代码 代码如下:

mysqld_safe –user=mysql –skip-slave-start & (启动MySQL服务器，但不启动slave)设置master_log_file等参数

mysql> CHANGE MASTER TO MASTER_HOST='qa-sandbox-1′,MASTER_USER='rep',

MASTER_PASSWORD='logzgh',

MASTER_LOG_FILE='mysql-bin.000007′,MASTER_LOG_POS=471632;mysql> START SLAVE；

执⾏这些程序后，从服务器应连接主服务器，并补充⾃从快照以来发⽣的任何更新。如果你忘记设置主服务器的server-id值，从服务器不能连接主服务器。

注释：为了保证事务InnoDB复制设置的最⼤可能的耐受性和⼀致性，

应在主服务器的my.cnf⽂件中使⽤innodb_flush_log_at_trx_commit=1和sync-binlog=1。复制代码 代码如下:

mysql> show variables; (检查是否read-only，该选项令slave除了slave线程或者拥有SUPER权限⽤户之外的都不能更新数据，确保slave不会接受来⾃其他客户端的更新。)mysql> show processlist; (检查是否slave-start)在启动mysql的同时启动slave：

复制代码 代码如下:

mysqld_safe –user=mysql –read-only & (启动MySQL服务器，同时启动slave的I/O线程)

mysql> SHOW SLAVE STATUSG;5.切换slave为master，在slave上：复制代码 代码如下:mysql> STOP SLAVE;mysql> RESET MASTER;

–read_only

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

告诉从服务器只做默认数据库(由USE所选择)为db_name的语句的复制。要指定多个数据库，应多次使⽤该选项，每个数据库使⽤⼀次。请注意不复制跨数据库的语句

–replicate_do_table=db_name.tbl_name

告诉从服务器线程只做对指定表的复制。要指定多个表，应多次使⽤该选项，每个表使⽤⼀次。同–replicate-do-db对⽐，允许跨数据库更新。

–replicate_ignore_db=db_name

告诉从服务器不要复制默认数据库(由USE所选择)为db_name的语句。要想忽略多个数据库，应多次使⽤该选项，每个数据库使⽤⼀次。

–replicate-ignore-table=db_name.tbl_name

告诉从服务器线程不要复制更新指定表的任何语句(即使该语句可能更新其它的表)。要想忽略多个表，应多次使⽤该选项，每个表使⽤⼀次。

–replicate_wild_do_table=db_name.tbl_name

告诉从服务器线程限制复制更新的表匹配指定的数据库和表名模式的语句。模式可以包含‘%'和‘_'通配符，与LIKE模式匹配操作符具有相同的含义。要指定多个表，应多次使⽤该选项，每个表使⽤⼀次。该选项可以跨数据库进⾏更新。

–replicate_wild_ignore_table=db_name.tbl_name

告诉从服务器线程不要复制表匹配给出的通配符模式的语句。要想忽略多个表，应多次使⽤该选项，每个表使⽤⼀次。该选项可以跨数据库进⾏更新。

–replicate_rewrite_db=from_name->to_name

告诉从服务器如果默认数据库(由USE所选择)为主服务器上的from_name，则翻译为to_name。只影响含有表的语句–report_host=slave_name

从服务器注册过程中报告给主服务器的主机名或IP地址。该值出现在主服务器上SHOW SLAVE HOSTS的输出中。如果不想让从服务器⾃⼰在主服务器上注册，则不设置该值。

–report_port=slave_port

连接从服务器的TCP/IP端⼝号，从服务器注册过程中报告给主服务器。

–skip_slave_start

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

–slave_skip_errors=[err_code1,err_code2,… | all]

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

–slave-skip-errors=1062,1053–slave-skip-errors=all⽅法⼀：

如果你在某时间点做过主服务器备份并且记录了相应快照的⼆进制⽇志名和偏移量(通过SHOW MASTER STATUS命令的输出)，采⽤下⾯的步骤：

1. 确保从服务器分配了⼀个唯⼀的服务器ID号。2. 将备份⽂件拷到从服务器上。

3. 在从服务器上执⾏下⾯的语句，为每个选项填⼊适当的值：

复制代码 代码如下:

mysql> CHANGE MASTER TO

-> MASTER_HOST='master_host_name',-> MASTER_USER='master_user_name',-> MASTER_PASSWORD='master_pass',

-> MASTER_LOG_FILE='recorded_log_file_name',-> MASTER_LOG_POS=recorded_log_position;4.在从服务器上执⾏START SLAVE语句。

如果你没有备份主服务器，这⾥是⼀个创建备份的快速程序。所有步骤都应该在主服务器主机上执⾏。1. 发出该语句：

复制代码 代码如下:

mysql> FLUSH TABLES WITH READ LOCK；2. 仍然加锁时，执⾏该命令（或它的变体）：复制代码 代码如下:

shell> tar zcf /tmp/backup.tar.gz /var/lib/mysql并拷到从服务器上。

3. 发出该语句并且确保记录了以后⽤到的输出：复制代码 代码如下:

mysql>SHOW MASTER STATUS；4. 释放锁：

复制代码 代码如下:

mysql> UNLOCK TABLES；⽅法⼆：

⼀个可选择的⽅法是，转储主服务器的SQL来代替前⾯步骤中的⼆进制复制。要这样做，你可以在主服务器上使⽤mysqldump –master-data，以后装载SQL转储到到你的从服务器。然⽽，这⽐进⾏⼆进制复制速度慢。1.不能从使⽤新⼆进制⽇志格式的主服务器向使⽤旧⼆进制⽇志格式的从服务器复制。

2.升级从服务器时，应先关闭从服务器，升级到相应5.1.x版本，然后重启从服务器并重新开始复制。5.1版本的从服务器能够读取升级前写⼊的旧的中继⽇志并执⾏⽇志中包含的语句。升级后从服务器创建的中继⽇志为5.1格式。

3.必须在主服务器和从服务器上总是使⽤相同的全局字符集和校对规则(–default-character-set、–default- collation)。否则，会在从服务器上遇到复制键值错误，因为在主服务器的字符集中被认为是唯⼀的键值在从服务器的字符集中可能不是唯⼀的。4.Q：从服务器需要始终连接到主服务器吗？

A：不，不需要。从服务器可以宕机或断开连接⼏个⼩时甚⾄⼏天，重新连接后获得更新信息。

5.Q：我怎样知道从服务器与主服务器的最新⽐较? 换句话说，我怎样知道从服务器复制的最后⼀个查询的⽇期？A：你可以查看SHOW SLAVE STATUS语句的Seconds_Behind_Master列的结果。6. Q：我怎样强制主服务器阻塞更新直到从服务器同步？A：使⽤下⾯的步骤：

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

复制代码 代码如下:

mysql> FLUSH TABLES WITH READ LOCK;mysql> SHOW MASTER STATUS;

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

2.在从服务器上，发出下⾯的语句，其中Master_POS_WAIT()函数的参量是前⾯步骤中的得到的复制坐标值：mysql> SELECT MASTER_POS_WAIT('log_name', log_offset);

SELECT语句阻塞直到从服务器达到指定的⽇志⽂件和偏移量。此时，从服务器与主服务器同步，语句返回。3.在主服务器上，发出下⾯的语句允许主服务器重新开始处理更新：

复制代码 代码如下:

mysql> UNLOCK TABLES；

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

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