MySQL数据库主从同步

做开发的时候要做MySQL的数据库同步，两台安装一样的系统，都是FreeBSD5.4，安装了Apache 2.0.55和PHP 4.4.0，MySQL的版本是4.1.15，都是目前最新的版本。

1. 安装配置

两台服务器，分别安装好MySQL，都安装在 /usr/local/MySQL 目录下（安装步骤省略，请参考相关文档），两台服务器的IP分别是192.168.0.1和192.168.0.2，我们把192.168.0.1作为 master数据库，把192.168.0.2作为slave服务器，我们采用单向同步的方式，就是master的数据是主的数据，然后slave主动去 master哪儿同步数据回来。

两台服务器的配置一样，我们把关键的配置文件拷贝一下，默认的配置文件是在 /usr/local/MySQL/share/MySQL目录下，分别有 my-large.cnf, my-medium.cnf, my-small.cnf等几个文家，我们只是测试，使用my-medium.cnf就行了。MySQL安装完后，默认的配置文件是指定在数据库存放目录 下的，我们用的是4.1.X的，所以配置文件就应该在 /usr/local/MySQL/var 目录下，于是把配置文件拷贝过去：

 

cp /usr/local/MySQL/share/MySQL/my-medium.cnf  /usr/local/MySQL/var/my.cnf
 

两台服务器做相同的拷贝配置文件操作。

2. 配置Master服务器

我们要把192.168.0.1配置为主MySQL服务器（master），那么我们就要考虑我们需要同步那个数据库，使用那个用户同步，我们这里为了简单起见，就使用root用户进行同步，并且只需要同步数据库abc。

打开配置文件：

 

vi /usr/local/MySQL/var/my.cnf
 

找到一下信息：

 

# required unique id between 1 and 2^32 - 1# defaults to 1 if master-host is not set# but will not function as a master if omittedserver-id        = 1    //1为master，2为salve
 

添加两行：

 

sql-bin-update-same     //同步形式binlog-do-db     = abc  //要同步的数据库
 

重启192.168.0.1的MySQL服务器：

 

/usr/local/MySQL/bin/MySQLadmin shutdown/usr/local/MySQL/bin/MySQLd_safe --user=MySQL &
 

3. 配置Slave服务器

我们的slave服务器主要是主动去master服务器同步数据回来，我们编辑配置文件：

 

vi /usr/local/MySQL/var/my.cnf
 

找到下面类似的信息：

 

# required unique id between 1 and 2^32 - 1# defaults to 1 if master-host is not set# but will not function as a master if omittedserver-id        = 1
 

把上面的server-id修改为2，同时添加一些信息：

 

server-id                   = 2          //本MySQL是slave服务器master-host             = 192.168.0.1    //master服务器的IPmaster-user             = root           //连接master服务器的用户master-password   = ''                   //连接master服务器的密码master-port              = 3306          //连接端口master-connect-retry    = 10             //重试次数replicate-do-db        = abc             //要同步的数据库log-slave-updates                        //同步的形式
 

重启192.168.0.2的MySQL服务器：

 

/usr/local/MySQL/bin/MySQLadmin shutdown/usr/local/MySQL/bin/MySQLd_safe --user=MySQL &
 

4. 测试安装

首先查看一下slave的主机日志：

 

cat /usr/local/MySQL/var/xxxxx_err （xxx是主机名）
 

检查是否连接正常, 看到类似这样的信息就成功了

 

051031 11:42:40  MySQLd started051031 11:42:41  InnoDB: Started; log sequence number 0 43634/usr/local/MySQL/libexec/MySQLd: ready for connections.Version: '4.1.15-log'  socket: '/tmp/MySQL.sock'  port: 3306  Source distribution051031 11:42:41 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log './new4-relay-bin.000001' position: 4051031 11:43:21 [Note] Slave I/O thread: connected to master ,  replication started in log 'FIRST' at position 4
 

在Master查看信息

 

/usr/local/MySQL/bin/MySQL -u root
 

查看master状态：

 

MySQL> show master status;
 

查看Master下MySQL进程信息：

 

MySQL> show processlist;
 

在slave上查看信息：

 

/usr/local/MySQL/bin/MySQL -u root
 

查看slave状态：

 

MySQL> show slave status;
 

查看slave下MySQL进程信息：

 

MySQL> show processlist;
 

你再在master的abc库里建立表结构并且插入数据，然后检查slave有没有同步这些数据，就能够检查出是否设置成功。

最后，如果有兴趣的话，可以研究一下双击热备份，或者一台master，多台slave的同步实现。

｛2009-03-26号研究的内容｝

MySQL 主从同步操作文档

目录
[隐藏]
1 同步原理说明
2 同步设置基本步骤(以下设置均涉及my.cnf文件的修改)
2.1 设置主服务器:启用bin-log日志、同步帐号
2.2 设置从服务器:设置需要同步的内容
2.3 拷贝主服务器数据到从服务器
2.3.1 方式1:停止主服务器或其中一台从服务器并拷贝数据
2.3.2 方式2：在主服务器或其中一台从服务器上设置锁，禁止一切读写操作并拷贝数据
2.4 启动同步
3 实例参考:
4 FAQ

[编辑] 1 同步原理说明
MySQL的Replication基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等)。
MySQL使用3个线程来完成Replication工作，具体分布是主上1个相关线程、从上2个相关线程；
主的相关线程可以理解为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程、从服务器分别为IO和SQL线程;
主服务器创建将binlog中的内容发送到从服务器。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的中继日志文件（relay-log）里，SQL线程用于读取中继日志并执行日志中包含的更新。
MySQL的Replication是单向，异步同步

MySQL同步机制基于master把所有对数据库的更新、删除等)都记录在二进制日志里。因此，想要启用同步机制，在master就必须启用二进 制日志。每个slave接受来自master上在二进制日志中记录的更新操作，因此在slave上执行了这个操作的一个拷贝。应该非常重要地意识到，二进 制日志只是从启用二进制日志开始的时刻才记录更新操作的。所有的 slave必须在启用二进制日志时把master上已经存在的数据拷贝过来。如果运行同步时slave上的数据和master上启用二进制日志时的数据不 一致的话，那么slave同步就会失败。把master上的数据拷贝过来的方法之一实在slave上执行 LOAD DATA FROM MASTER 语句。不过要注意，LOAD DATA FROM MASTER 是从MySQL 4.0.0之后才开始可以用的，而且只支持master上的 MyISAM 类型表。同样地，这个操作需要一个全局的读锁，这样的话传送日志到slave的时候在master上就不会有更新操作了。当实现了自由锁表热备份时(在 MySQL 5.0中)，全局读锁就没必要了。由于有这些限制，因此我们建议只在master上相关数据比较小的时候才执行 LOAD DATA FROM MASTER 语句，或者在master上允许一个长时间的读锁。由于每个系统之间 LOAD DATA FROM MASTER 的速度各不一样，一个比较好的衡量规则是每秒能拷贝1MB数据。这只是的粗略的估计，不过master和slave都是奔腾700MHz的机器且用 100MBit/s网络连接时就能达到这个速度了。slave上已经完整拷贝master数据后，就可以连接到master上然后等待处理更新了。如果 master当机或者slave连接断开，slave会定期尝试连接到master上直到能重连并且等待更新。重试的时间间隔由 –master-connect-retry 选项来控制，它的默认值是60秒。每个slave都记录了它关闭时的日志位置。master是不知道有多少个slave连接上来或者哪个slave从什么 时候开始更新。

MySQL同步功能由3个线程(master上1个，slave上2个)来实现。执行 START SLAVE 语句后，slave就创建一个I/O线程。I/O线程连接到master上，并请求master发送二进制日志中的语句。master创建一个线程来把日 志的内容发送到slave上。这个线程在master上执行 SHOW PROCESSLIST 语句后的结果中的 Binlog Dump 线程便是。slave上的I/O线程读取master的 Binlog Dump 线程发送的语句，并且把它们拷贝到其数据目录下的中继日志(relay logs)中。第三个是SQL线程，salve用它来读取中继日志，然后执行它们来更新数据。如上所述，每个mster/slave上都有3个线程。每个 master上有多个线程，它为每个slave连接都创建一个线程，每个slave只有I/O和SQL线程。在MySQL 4.0.2以前，同步只需2个线程(master和slave各一个)。slave上的I/O和SQL线程合并成一个了，它不使用中继日志。slave上 使用2个线程的优点是，把读日志和执行分开成2个独立的任务。执行任务如果慢的话，读日志任务不会跟着慢下来。例如，如果slave停止了一段时间，那么 I/O线程可以在slave启动后很快地从master上读取全部日志，尽管SQL线程可能落后I/O线程好几的小时。如果slave在SQL线程没全部 执行完就停止了，但I/O线程却已经把所有的更新日志都读取并且保存在本地的中继日志（relay-log）中了，因此在slave再次启动后就会继续执 行它们了。这就允许在 master上清除二进制日志，因为slave已经无需去master读取更新日志了。执行 SHOW PROCESSLIST 语句就会告诉我们所关心的master和slave上发生的情况。
[编辑] 2 同步设置基本步骤(以下设置均涉及my.cnf文件的修改)

[编辑]

2.1 设置主服务器:

启用bin-log日志、同步帐号
server-id = n #设置ID
log-bin #启用bin-log记录，如需要指定binlog文件名，可使用如下语法
#log-bin=binlog #binlog文件名为binlog.0，binlog.1……
set-variable=binlog-ignore-db=mysql #不记录数据库mysql的更新日志
binlog-do-db = #记录指定的数据库的更新日志，还可以单独处理只更新表内容

此外，需要注意两个环境变量：
innodb_flush_log_at_trx_commit=1 #Default: 1。在使用了事务的数据库中，强烈建议该值显式的设置为1，以确保在每个事务提交时，日志缓冲被写到日志文件，并将该写操作同步到磁盘上
sync_binlog=1 #在每进行1次binlog写操作后就将该操作同步到硬盘上。设为更高的值可以有效降低io并获得更好的性能，但会增加bilog丢失的风险

｛说明：第一步在90主机上面进行修改my.cnf文件｝

为从服务器上的同步帐号授权：
mysql>GRANT REPLICATION SLAVE ON *.* TO 同步帐号的用户名@从服务器ip地址 IDENTIFIED BY ‘同步帐号的密码’;

如果想要从服务器上有权限执行”LOAD TABLE FROM MASTER”和”LOAD DATA FROM MASTER”还需要增加”FILE”和”SELECT”权限;

｛到此为止主数据库配置成功！现在来配置从数据库｝

[编辑]

2.2 设置从服务器:设置需要同步的内容
server-id = n #设置ID
master-host = #设置主服务器的ip
master-user = #设置同步帐户的用户名
master-password = #设置同步帐户的密码
master-port = #设置TCP／IP端口，默认为3306
set-variable=replicate-ignore-db=mysql #设置忽略的同步数据库名
set-variable=replicate-db-db= #设置同pass_adm步的数据库名若需要单独设置可以针对表级设置的数据多个，请设置多次

这里的设置可以在后面登陆进从数据库之后用CHANGE MASTER TO来修改。

｛这些东西是由主数据库来赋权限进来的！｝

[编辑] 2.3 拷贝主服务器数据到从服务器
[编辑] 2.3.1 方式1:停止主服务器或其中一台从服务器并拷贝数据

在需要拷贝数据的服务器上停止mysqld，完全停止服务。如果是从服务器，则需要在停止mysqld之前，停止同步进程。
mysql>stop slave;
$mysqladmin -uroot -p shutdown

确认mysqld停止之后，将其中的数据文件ib*，master.info需要同步的数据库目录和其他相关文件拷贝到新机器上。binlog和relay-log不用拷贝
[编辑] 2.3.2 方式2：在主服务器或其中一台从服务器上设置锁，禁止一切读写操作并拷贝数据

在主服务器上执行:
mysql>FLUSH TABLES WITH READ LOCK;

导出数据到从数据库(可以使用tar、scp、mysqldump等方式，此处实现比较灵活):
$cd ~/mysql/
$tar zcvf var.tar.gz var/ #数据目录可能不为var目录

然后是拷贝此var.tar.gz包到从服务器并解压缩到相应目录

在主服务器上执行:
mysql>UNLOCK TABLES;

说明： 拷贝主服务器的数据到从服务器的实现方式太多：可以直接停止mysqld程序拷贝数据、可以直接tar方式打包处理、可以直接scp方式处理、可以直接导出相应表结构等，具体实现方式看具体应用，适而择之，主要保证在拷贝数据的时候没有数据写入和更改即可
[编辑] 2.4 启动同步

启动从数据库的mysqld,推荐使用–skip-slave-start启动，进入mysql后再start slave启动同步。

说明： 通常使用的mysqld_safe会记住启动时的参数，例如–skip-slva-start。如果从库因为意外原因重启，且该数据库启动的时候使用了 mysqld_safe –skip-slave-start，则需要手工启动同步进程，否则同步进程会始终处于停止状态

从服务器上执行：
mysql>SHOW SLAVE STATUS

若Slave_IO_Running 和 Slave_SQL_Running 两行的值都为 “Yes”，这表明 Slave 的 I/O 和 SQL 线程都在正常运行。

说明： 推荐使用CHANGE MASTER TO 语句进行同步参数设置; 具体参照help change master to,语法如下：

master_def:aster_def [, master_def] …

master_def:
MASTER_HOST = ‘host_name’
MASTER_USER = ‘user_name’
MASTER_PASSWORD = ‘password’
MASTER_PORT = port_num
MASTER_CONNECT_RETRY = count
MASTER_LOG_FILE = ‘master_log_name’
MASTER_LOG_POS = master_log_pos
RELAY_LOG_FILE = ‘relay_log_name’
RELAY_LOG_POS = relay_log_pos
MASTER_SSL = {0|1}
MASTER_SSL_CA = ‘ca_file_name’
MASTER_SSL_CAPATH = ‘ca_directory_name’
MASTER_SSL_CERT = ‘cert_file_name’
MASTER_SSL_KEY = ‘key_file_name’
MASTER_SSL_CIPHER = ‘cipher_list’
[编辑] 3 实例参考:

主数据库A的my.cnf配置：
server-id = 1 #设置ID
log-bin #启用bin-log记录
binlog-do-db = iknow #记录指定库的binlog更新记录，每个数据库一行
binlog-do-db = iknow_adm
binlog-do-db = …

现在，新搭建一个从数据库B。需要进行如下步骤

1.修改配置文件: 修改my.cnf，加入如下项：
server-id = 2 #指定server id，每台机器的id必须保证唯一
log-bin #启用binlog记录
master-connect-retry = 60 #连接主库的重试间隔
master-host = xxx.xxx.xxx.xxx #指定主库A的IP，不能用虚IP
master-port = 3306 #指定主库端口
master-user = rep #指定同步用户的用户名为replication
master-password = rep #指定同步用户的密码为PassWORd
replicate-do-db = iknow #指定需要同步的库，每个库独占一行
replicate-do-db = iknow_adm
replicate-do-db = …
log-slave-updates #在从库上对同步进行日志记录，磁盘占用量较大，建议从库运行稳定后将其去除，如要作为第二层主数据库，则此项必须

这里的设置可以在后面登陆进从数据库之后用CHANGE MASTER TO来修改。

2.拷贝数据:从一台从数据库C拷贝数据到B 从数据库没有对外提供服务，因此可以直接停掉从数据库拷贝数据。

注意：
停从数据库的时候需要显式的执行stop slave，并执行show slave status，确认Read_Master_Log_Pos和Exec_Master_Log_Pos一致，再执行mysqladmin -uroot -p shutdown停掉mysqld。
mysql>stop slave;
mysql>show slave status

确认Read_Master_Log_Pos和Exec_Master_Log_Pos一致
$ mysqladmin -uroot -p shutdown

待mysqld完全退出后，将var目录下的ib*，master.info，以及需要同步的库cp到本地目录var.$DATE下（主要是为了缩短C上mysqld的服务停止时间）
$ cd $MYSQL/var; mkdir ../var.$DATE
$ cp -r ib* iknow iknow_adm master.info ../var.$DATE/
$ scp $MYSQL/var.$DATE B:~/mysql

3.在主数据库上为一台从库授权同步
mysql>GRANT REPLICATION SLAVE ON *.* TO replication@B机器的ip地址 IDENTIFIED BY ‘Password;

4.在B上启动mysqld
$ mysqld_safe –-skip-slave-start &

5.进入mysql观察slave status
mysql> show slave status

6.观察master.info和relay-log.info
$ cat $MYSQL/master.info
$ cat $MYSQL/relay-log.info

在这里不需要change master一步是因为master.info中已记录了master_log_file和master_log_pos等相关信息。这是一种比较保险而且简单的办法

7.复查确认my.cnf中replication slave配置正确如果master.info文件存在的话，mysql是优先读取它的，但在某些情况下，比如mysqld异常重启等，有可能会读取 my.cnf中配置，因此需要保证my.cnf中的replication slave配置正确 由于mysqld在正常情况下优先读取master.info，因此，在主数据库上一定要保证master.info是不存在的，否则可能出现不可预知的 后果。

8. CHANGE MASTER TO语句这是用于在mysql命令行中显式的更改主库的语句。在mysql命令行中执行
mysql> help change master to

可获得关于这条语句的详细信息。如果在进行主从数据库切换后，或需要临时更改主库，而又不想重启数据库的时候，可以使用如下语句动态的更改主库：
mysql> CHANGE MASTER TO MASTER_HOST=xxx.xxx.xxx.xxx, MASTER_USER=’replication’, MASTER_PASSWORD=’Password’, MASTER_LOG_FILE=’master-binlog.00001′, MASTER_LOG_POS=’123456′;

以上语句各项含义如下：
MASTER_HOST主库的机器名或IP地址
MASTER_USER主库为从库授权的同步帐号
MASTER_PASSWORD主库为从库授权的同步帐号密码
MASTER_LOG_FILE主库的binlog文件名
MASTER_LOG_POS主库的binlog文件偏移

MASTER_LOG_FILE和MASTER_LOG_POS两项需要在启动主库之前执行show master status记录下来，并且必须保证准确无误，否则会出现同步失败的情况。

[编辑] 4 FAQ

1. scp了整个var目录到新的从库，start slave后，发现同步无法正常进行，Slave_IO_Running正常，但Slave_SQL_Running的状态为No，并且mysql错误日志中报找不到一个releylog文件
答：这是由于reley-log.info文件的存在造成的，它记录了数据库当前正在使用的relaylog，而mysql默认的relaylog命名是 以机器名命名的，因此如果从一台机器拷贝到另一台机器，它是找不到这个文件的。如果可以确认Read_Master_Log_Pos和 Exec_Master_Log_Pos一致，那么可以直接将relay-log.info删除，重启mysqld，再start slave就可以解决。

2. start slave后， Slave_IO_Running和Slave_SQL_Running的状态都为No，并且日志中报类似“Slave I/O thread: Failed reading log event, reconnecting to retry, log ‘tc-nsop-test00-bin.000177′ position 1019586208”的错误
答：这是由于主库对从库的同步帐号授权不正确造成的，更改并确认授权正确之后，重新start slave，就可以正常同步。

3. 如果错误日志中出现如下提示“Warning: You should set server-id to a non-0 value if master_host is set; we force server id to 2, but this MySQL server will not act as a slave.”，并且 Slave_IO_Running和Slave_SQL_Running的状态都为No
答：检查主库的my.cnf，这样的错误是由于没有设置主库的server id或者server id不合法造成的。更改主库server id并重启主库后，start slave，同步可以恢复正常。

4. 如果Slave_SQL_Running状态为No，并且错误日志中有类似“Error ‘Lock wait timeout exceeded; try restarting transaction’ on query. Default database: ……”这样的错误
答：这是由于同步某个行的时候恰好遇到有其他SQL进程对该行进行了锁定，并且锁定时间较长导致同步进程等待超时。直接start slave即可。

5. 同时启动多台从库的同步进程对主库有什么影响
答：通常情况会导致主库的io和网卡流量增加。MySQL的Binlog Dump进程是没有限速的，因此会全速进行binlog读取和数据分发，给主库带来较大的负担。

原文地址：