标题：MySql主从同步配置详解
出处：沧海一粟
时间：Wed, 04 Apr 2012 02:05:39 +0000
作者：jed
地址：http://www.dzhope.com/post/909/

内容：
　MySQL的主从同步是一个很成熟的架构，优点为：
①在从服务器可以执行查询工作(即我们常说的读功能)，降低主服 务器压力;
②在从主服务器进行备份，避免备份期间影响主服务器服务;
③当主服务器出现问题时，可以切换到从服务器。
　一、MySQL复制概述

　　MySQL支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。MySQL复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。因此，要进行复制，必须在主服务器上启用二进制日志。每个从服务器从主服务器接收主服务器上已经记录到其二进制日志的保存的更新。当一个从服务器连接主服务器时，它通知主服务器定位到从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，并在本机上执行相同的更新。然后封锁并等待主服务器通知新的更新。从服务器执行备份不会干扰主服务器，在备份过程中主服务器可以继续处理更新。
　　二、复制实现细节

　　MySQL使用3个线程来执行复制功能，其中两个线程(Sql线程和IO线程)在从服务器，另外一个线程(IO线程)在主服务器。当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让它发送记录在其二进制日志中的语句。主服务器创建一个线程将二进制日志中的内容发送到从服务器。该线程可以即为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。第3个线程是SQL线程，由从服务器创建，用于读取中继日志并执行日志中包含的更新。在从服务器上，读取和执行更新语句被分成两个独立的任务。当从服务器启动时，其I/O线程可以很快地从主服务器索取所有二进制日志内容，即使SQL线程执行更新的远远滞后。

　　1、复制线程状态

　　通过show slave status\G和show master status可以查看复制线程状态。常见的线程状态有：

　　(1)主服务器Binlog Dump线程

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

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

　　(2)从服务器I/O线程状态

　　Waiting for master to send event

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

　　(3)从服务器SQL线程状态

　　Reading event from the relay log

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

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

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

　　2、复制过程中使用的传递和状态文件

　　默认情况，中继日志使用host_name-relay-bin.nnnnnn形式的文件名，其中host_name是从服务器主机名，nnnnnn是序列号。中继日志与二进制日志的格式相同，并且可以用mysqlbinlog读取。

　　从服务器在data目录中另外创建两个小文件。这些状态文件默认名为主master.info和relay-log.info。状态文件保存在硬盘上，从服务器关闭时不会丢失。下次从服务器启动时，读取这些文件以确定它已经从主服务器读取了多少二进制日志，以及处理自己的中继日志的程度。

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

　三、MySQL建立主从服务器配置方法

A、环境描述

　　服务器A(主) 192.168.1.106
　　服务器B(从) 192.168.1.107
　　Mysql版本：5.5.3
　　System OS：RedHat 5.4 X64
　　主从需同步的数据库内容保持一致。

B、准备工作
1、主从同步前建议ntpdate主从服务器的系统时间，以免同步时报错。
2、检查my.cnf设置，bind-address = 127.0.0.1，这条语句应该被注释掉。

C、主从配置过程

　　主服务器

　　a)创建同步用户

　　在主服务器上为从服务器建立一个连接帐户，该帐户必须授予REPLICAITON SLAVE权限,允许用户在主库上读取日志。


　　服务器A：

mysql>grant replication slave on *.* to 'replication'@'192.168.1.107' identified by '000000';
mysql>flush privileges;


# 可在Slave上做连接测试: mysql -h 192.168.1.105 -u replication -p

b)修改mysql配置文件

[mysqld]  
server-id = 1  
log-bin=mysql-bin 
binlog-do-db = test 
binlog-ignore-db=mysql


server-id = 1 //1代表主数据库（源） 2代表辅数据库（目的）配一个唯一的ID编号，1至32。
binlog-do-db=test //需要同步的数据库，如果没有本行，即表示同步所有的数据库,需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可。
binlog-ignore-db=mysql //不需要备份的数据库名
log-slave-updates //这个参数一定要加上，否则不会给更新的记录写到二进制文件里（不开也可以，只是开了以后你可以在从上实施日志恢复！）
slave-skip-errors //是跳过错误，继续执行复制操作

----------------------------------
server-id = 1(主数据库一般都是id为1)
log-bin=mysql-bin (必须的)
binlog_format=mixed （必须的，推荐类型为mixd）
expire_logs_days=5 （为避免日志文件过大，设置过期时间为5天）
binlog-ignore-db = mysql （忽略同步的文件，也不记入二进制日志，可列多行)
binlog-ignore-db = information_schema
replicate-do-db = test （需要同步的文件，记入二进制日志，可列多行）

c)同步主从数据库同步，可将主服务器mysql停掉，打包数据库文件或者其他方式，保证第一次同步之前，两边数据库是一致的。

主服务器重启mysql服务。
查看Mysql主 的状态：执行：show master status；这时会看到master数据库所处的位置，记录下来：

show master status;

| File             | Position |

| mysql-bin.000011 | 189      |




从服务器

　　a)修改mysql配置文件

[mysqld]  
server-id = 2
log-bin = mysql-bin
replicate-do-db = test
replicate-ignore-db = mysql


把server-id改成不和主机相同的数字。
mysql5.1.17版本以后已经不支持把master配置属性写入my.cnf文件中了，只需要把同步的数据库和需要忽略的数据写入。
master-connect-retry=60   //如果发现主服务器断线，重新连接的时间差；
 #[必须]服务器唯一id，一般取ip最后两位 
2 server_id = 246 
3 #[可选]启用二进制日志 
4 log_bin = mysql-bin 
5 #[可选]定义中继日志的位置和文件名 
6 relay_log = mysql-relay-bin 
7 #[可选]使从服务器把复制的事件记录到自己的二进制日志中 
8 log_slave_updates = 1 
9 #[可选]只读 
10 read_only = 1 
11 #[可选]跳过1062主键冲突错误 Duplicate entry '1234' for key 'PRIMARY' 
12 slave-skip-errors = 1062 

b)重启mysql服务

c)用change master语句指定同步位置

mysql>change master to master_host='192.168.1.106', master_user='replication', master_password='000000', master_log_file='binlog.000011', master_log_pos=189; 


注：master_log_file，master_log_pos由上面主服务器查出的状态值中确定。master_log_file对应File，master_log_pos对应Position。
　　　　mysql 5.x以上版本已经不支持在配置文件中指定主服务器相关选项。

d)启动从服务器线程

mysql>start slave; 



e)查看从服务器状态


mysql>show slave status\G
 
Slave_IO_Running: Yes
Slave_SQL_Running: Yes 


Master_Log_File: mysql-bin.000011 (和主mysql一致)
Read_Master_Log_Pos: 189 （和主mysql一致）
Slave_IO_Running: Yes （读写）
Slave_SQL_Running: Yes （数据库状态）

还要注意状态中是否有error，如果没有的话，就差不多了。

四、测试主从是否同步
五、常用命令
Slave start; --启动复制线程
Slave stop; --停止复制线程
Reset slave; --重置复制线程
Show slave status; --显示复制线程的状态
Show slave status\G; --显示复制线程的状态(分行显示)
Show master status\G; --显示主数据库的状态(分行显示)
Show master logs --显示主数据库日志，需在主数据库上运行
Change master to; --动态改变到主数据库的配置
Show processlist --显示有哪些线程在运行


附：-------------------------------------------------
有的时候不想让mysql服务停止，那我们可以用下面的方法来删除binary文件。
我们可以看到产生了二进制文件

mysql> show binary logs;（或者show master logs;）

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

| Log_name        | File_size |

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

| mysql-bin.000001 |     15056 | 

| mysql-bin.000002 |    628368 | 

| mysql-bin.000003 |       377 | 

| mysql-bin.000004 |       141 | 

| mysql-bin.000005 | 1073742287 | 

| mysql-bin.000006 | 1073742035 | 

| mysql-bin.000007 |  823654620| 

| mysql-bin.000008 |      2265 | 

| mysql-bin.000009 |    628368 | 

| mysql-bin.000010 |       117 | 

| mysql-bin.000011 |      4525 | 

| mysql-bin.000012 |       117 | 

| mysql-bin.000013 |      3147 | 

| mysql-bin.000014 |   85468109| 

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



二进制文件一般用来做replication同步，当查看slave上同步正确，或者是同步已经完成了，这时如果硬盘空间又不是很大的话，那我们可以手动去清理这些binary文件。
很简单：

mysql> reset master;

Query OK, 0 rows affected (8.47 sec)


复制代码或清除指定部分logs：

mysql> pure binary logs to'mysql-bin.000013';


复制代码就是删除二进制文件到mysql-bin.000013，最后一个mysql-bin.000014保留着。

或将指定时间之前的binary logs清掉

mysql>purge binary logs before '2011-05-28 12:05:38';


复制代码
.查看当前binary log的情况：

mysql>show master status;


复制代码
. 查看binary logs的内容：

mysql>show binlog events;


复制代码命令行下：

#mysqlbinlog /var/log/mysql/log-bin.000140;


复制代码或者

#mysqlbinlog --start-datetime='2011-07-01 00:00:00' 

--stop-datetime='2010-07-15 00:00:00' /var/log/mysql/log-bin.000020 >

./tmp.log


复制代码
. 在my.cnf/my.ini中设定binary logs回滚天数：
[/code]
expire_logs_days = 7
[/code]


附二：------------------------------------------------------------------------------------
结果所有服务器都配置好后，发现从上报如下的错误 


Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).


意思就是从上的server_id和主的一样的，经查看发现从上的/etc/my.cnf中的server_id=1这行我没有注释掉（在下面复制部分我设置了server_id），于是马上把这行注释掉了，然后重启mysql，发现还是报同样的错误。

使用如下命令查看了一下server_id

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)


发现，mysql并没有从my.cnf文件中更新server_id，既然这样就只能手动修改了

mysql> set global server_id=2;   #此处的数值和my.cnf里设置的一样就行

mysql> slave start;


如此执行后，slave恢复了正常。
 





Generated by Bo-blog 2.1.1 Release