如何设置mysql同步(replication).doc

MySQL 提供了数据库的同步功能，这对我们实现数据库的冗灾、备份、恢复、负载均衡等都是有极大帮助的本文描述了常见的同步设置方法一、准备服务器由于 MySQL 不同版本之间的(二进制日志)binlog 格式可能会不一样，因此最好的搭配组合是Master 的 MySQL 版本和 Slave 的版本相同或者更低，Master 的版本肯定不能高于 Slave 版本本文中，我们假设主服务器(以下简称 Master)和从服务器(以下简称 Slave)的版本都是 5.0.15，操作系统是 Linux Ubuntu 5.0.x假设同步 Master 的主机名为：rep1，Slave 主机名为：rep2，2 个 MySQL 的 basedir 目录都是/usr/local/mysql，datadir 都是：/usr/local/mysql/data 二、设置同步服务器1、设置同步 Master每个同步服务器都必须设定一个唯一的编号，否则同步就不能正常运行了接下来开始修改 f，增加以下几行：server-id = 1log-binset-variable=binlog-ignore-db=mysql然后在 Master 上增加一个账号专门用于同步，如下：mysql>GRANT REPLICATION SLAVE ON *.* TO rep@rep2 IDENTIFIED BY 'rep';如果想要在 Slave 上有权限执行 “LOAD TABLE FROM MASTER“ 或 “LOAD DATA FROM MASTER“ 语句的话，必须授予全局的 FILE 和 SELECT 权限：mysql>GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO rep@rep2 IDENTIFIED BY 'rep';第三行表示不记录数据库 mysql 的更新日志，这就避免了 Master 上的权限设置等被同步到Slave 上，如果对这方面没有限制，就可以不设置这个参数。

接下来备份 Master 上的数据，首先执行如下 SQL 语句：mysql>FLUSH TABLES WITH READ LOCK;不要退出这个终端，否则这个锁就不生效了；接着导出数据，可以直接打包压缩数据文件，也可以使用 mysqldump 工具来做，推荐前者的方法，这样更为快捷简便root$cd /usr/local/mysqlroot$tar zcf data.tar.gz ./data (在这里也可能是 “var“ 等其它实际存放数据文件的目录，根据实情而定)然后将这些数据拷贝到 Slave 服务器上，解开，设置好正确的权限及属主等；之后，执行 “UNLOCK TABLES“ 语句来释放锁2、设置 Slave修改 f，增加如下几行：server-id = 2master-host = rep1 #主服务器名master-user = rep #同步账户名，默认是 testmaster-password = rep #同步帐户密码，默认是空master-port = 3306 #主服务器的 TCP/IP 端口号，默认是 3306set-variable=replicate-ignore-db=mysql #略过同步的数据库名，如果有多个，请设置多次set-variable=replicate-do-db=yejr #想要同步的数据库名，如果有多个，请设置多次接下来在 Slave 上检验一下是否能正确连接到 Master 上，并且具备相应的权限。

root$mysql -hrep1 -urep -prepmysql>SHOW GRANTS;+------------------------------------------------------------------------------+| Grants for rep@rep2 |+------------------------------------------------------------------------------+| GRANT SELECT, FILE, REPLICATION SLAVE ON *.* TO 'rep'@'rep2' IDENTIFIED BY PASSWORD '*9FF2C222F44C7BBA5CC7E3BE8573AA4E1776278C' |+------------------------------------------------------------------------------+现在，可以启动 Slave 了。

启动成功后，登录 Slave，查看一下同步状态：mysql -hlocalhost -urootmysql>SHOW SLAVE STATUS\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: rep1Master_User: repMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000001Read_Master_Log_Pos: 98Relay_Log_File: relay.000003Relay_Log_Pos: 232Relay_Master_Log_File: binlog.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 98Relay_Log_Space: 232Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 01 row in set (0.00 sec)可以看到，Slave_IO_Running 和 Slave_SQL_Running 两列的值都为 “Yes“，这表明 Slave 的 I/O 和 SQL 线程都在正常运行。

至此，同步设定成功三、同步设定 FAQ问问: master 还在运行中，如何在不停止它的情况下配置 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_pass',7. -> MASTER_LOG_FILE='recorded_log_file_name',-> MASTER_LOG_POS=recorded_log_position; 8.在 slave 上执行 START SLAVE 语句如果事先没有备份 master 的数据，可以用以下方法快速创建一个备份。

以下所有的操作都是在 master 上9.提交语句： 10.mysql> FLUSH TABLES WITH READ LOCK;11.确保这个锁一直存在,执行以下命令(或者其他类似的)： 12.shell> tar zcf /tmp/backup.tar.gz /var/lib/mysql13.执行以下语句，记录下输出的结果，后面要用到： 14.mysql> SHOW MASTER STATUS;15.释放锁： mysql> UNLOCK TABLES;上述步骤的另一个办法是创建 master 的 SQL 转储文件只需在 master 上执行 mysqldump --master-data 命令，然后将导出来的 SQL 转储文件载入 slave不过，这么做会制作二进制数据快照的方式慢一点无论使用上述两种方法的哪种，最后都能创建 master 的数据快照然后记录二进制日志文件名以及偏移位置可以在好几的其他的 slave 上使用同一个备份的二进制数据快照得到 master的快照后，只要 master 的二进制日志完好无损，接着就能开始设置 slave 了两个决定是否需要等待较长时间的限制是：在 master 上磁盘空间保存二进制日志，以及 slave 从 master 抓取更新事件。

也可以使用 LOAD DATA FROM MASTER这个语句可以很方便地在 slave 上取得数据快照并且能立刻调整二进制日志文件名以及偏移位置在将来，我们推荐用 LOAD DATA FROM MASTER 来设置 slave警告，它只能用于 MyISAM 表，并且可能会保持一个较长时间的读锁由于它还没达到所期望的高效率，因此如果数据表很大，最好还是在执行完 FLUSH TABLES WITH READ LOCK 后直接制作二进制数据快照问问：是否 slave 总是需要连接到 master？答答：不，非必需slave 可以好几小时甚至几天关闭或者不连接 master，然后重连再取得更新操作日志例如，可以在拨号链接上设置一个 mater/slave 关系，拨号可能只是零星的不定期的连接这种做法隐含的是，在任何指定的时间里，除非使用特殊的度量标准，否则 slave 不能保证总是能和 master 保持同步在未来，有个选项可以阻止 master，除非至少有一个 slave在同步中问问：怎么知道比 master 晚了多少？也就是说，怎么知道 slave 最后同步的时间？答答：如果 slave 是 4.1.1 或者更高，只需查看 SHOW SLAVE STATUS 结果中的 Seconds_Behind_Master 字段。

对于老版本，可以用以下办法如果在 slave 上执行 SHOW PROCESSLIST 语句结果显示 SQL 线程(对 MySQL 3.23 则是 slave 线程)正在运行，这就意味着该线程至少从 master 读取一个更新操作事件详情请看“6.3 Replication Implementation Details“当 SQL 线程执行一个 master 上读取的更新操作事件时，它把自己的时间改成事件的时间(这也就是 TIMESTAMP 也要同步的原因)在SHOW PROCESSLIST 结果中的 Time 字段中，slave 的 SQL 线程显示的秒数就是最后一次同步的时间戳和 slave 本机的实际时间相差秒数。