Mysql主从同步

以下描述了如何快速设置MySQL同步服务器。假设你打算同步全部的数据库,并且之前没有设置过。需要关闭master服务器以完成全部的步骤。

本章描述的过程可以用于一个slave的情况,也可以用于多个slave的情况。

这只是一个最直接设置slave的办法,并不是只有一个。例如,已经有了master的数据快照(snapshot),master已经设置了服务器编号ID(server_id)并且启用了二进制日志,这就无需关闭master或者阻止在master上更新数据了。

注意,这个过程以及后面一些同步SQL语句需要有 SUPER 权限。

  1. 请确认master和slave上都安装了较近的MySQL版本,且这些版本之间要能兼容。尽量保持master和slave的版本一致,减少不必要的麻烦。

  2. 在master上新加一个帐户,slave才能用它来连接。这个帐户必须授予 REPLICATION SLAVE 权限。如果这个帐户只用于同步(推荐这么做),那就没必要授予其他权限了。设定你的域是 mydomain.com,想要授权一个帐户 repl 使用密码 slavepass,允许它可以在域里的任何主机连接到master上。用 GRANT 语句来创建帐户:

    mysql>  GRANT REPLICATION SLAVE ON *.*  TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
    

    如果打算在slave上执行 LOAD TABLE FROM MASTERLOAD DATA FROM MASTER 语句,那么必须给该帐户授予附加权限:

    • 授予全局 SUPER 和 RELOAD 权限。
    • 授予对想要加载的所有表上的 SELECT 权限。在master上任何没有 SELECT 权限的表都会被 LOAD DATA FROM MASTER 略过。
  3. 如果只用到 MyISAM 表,执行 FLUSH TABLES WITH READ LOCK 语句刷新所有表并且阻止其他写入:

    mysql>  FLUSH TABLES WITH READ LOCK;
    

    不要退出执行 FLUSH TABLES 语句的客户端,以保持读锁有效(如果退出了,读锁就释放了)。然后从master上取得数据快照。比较简单的办法就是把数据目录打包压缩。例如,Unix上的 tar, PowerArchiver, WinRAR, WinZip,或Windows上的类似程序。想要用 tar 来创建一个压缩包,包括所有的数据库,只需执行以下命令(把目录改成你的真实路径):

    shell> tar -cvf /tmp/mysql-snapshot.tar .
    

    如果只想打包一个数据库 this_db,只需执行命令:

    shell> tar -cvf /tmp/mysql-snapshot.tar ./this_db
    

    然后把这个文件拷贝到slave的 /tmp 目录下。在slave上,执行以下命令解开压缩包(把目录改成你的真实路径):

    shell> tar -xvf /tmp/mysql-snapshot.tar
    

    可能不需要同步 mysql 数据库,因为在slave上的权限表和master不一样。这时,解开压缩包的时候要排除它。同时在压缩包中也不要包含任何日志文件,或者 master.info~relay-log.info 文件。当在master上的 FLUSH TABLES WITH READ LOCK 语句还生效时,在master上读取当前二进制文件的文件名及偏移位置:

    mysql > SHOW MASTER STATUS;
    

    SHOW MASTER STATUS

    File 字段显示了二进制日志文件名,Position 字段显示了日志偏移位置。
    在这个例子中,日志文件是 mysql-bin.003,偏移位置是 73。记下这些值,在后面设置slave的时候就需要用到它们了。它们表示了slave要从master的哪个偏移位置开始处理更新操作。取得快照和记录下日志名及偏移位置后,就可以让master释放读锁了:

    mysql> UNLOCK TABLES;
    

    如果用到 InnoDB 表,那么最好使用 InnoDB Hot Backup 工具。它无需在master上请求任何锁就能做到快照的一致性,并且在后面中在slave上要用到的快照中已经记录了日志文件名以及偏移位置。
    InnoDB Hot Backup 是免费(商业的)的附加工具,它没有包含在MySQL发布包中。访问 InnoDB Hot Backup 的主页 http://www.innodb.com/manual.php 查看更多详细信息。
    除去 InnoDB Hot Backup 的另一个最快的办法就是关闭master服务器,拷贝 InnoDB 数据文件,日志文件,以及表结构定义文件(.frm 文件)。想要记录当前日志文件及偏移位置,需要在master关闭前执行如下可语句:

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

    尽快记下 SHOW MASTER STATUS 显示结果中的日志文件及偏移位置。然后,在不解锁的情况下关闭master,确保master上的快照和记录的结果一致:

    shell> mysqladmin -u root shutdown
    

    还有一个方法可以同时用于 MyISAM 和 InnoDB 表,这就是在master上作SQL转储而无需如上所述备份二进制日志。在master上运行 mysqldump –master-data 命令,然后把结果文件转储到slave上。不过,这比拷贝二进制日志慢点。如果master在以前没有启用 –log-bin 选项,那么执行 SHOW MASTER STATUS 语句的结果中的文件名及偏移位置值为空了,那么后面在slave上指定的参数值就是空字符串(‘’) 和 4了。

  4. 确认master上的 my.cnf 文件 [mysqld] 区间有 log-bin 选项。这个区间还必须有 server-id=master_id 选项,的值必须是 1 到 2^32-1 之间的正整数。例如:

    [mysqld]
    log-bin
    server-id=1
    

    如果这些配置选项不存在,那么就加上并且重启master。

  5. 关闭要做slave的服务器,在 my.cnf 文件中增加如下选项:

    [mysqld]
    server-id=slave_id
    

    slave_id 的值和 master_id 类似,是 1 到 2^32-1 之间的正整数。另外,这个ID必须不能和master的ID一样。例如:

    [mysqld]
    server-id=2
    

    如果有多个slave,那么每个slave都必须要有一个唯一的 server-id,它的值不能和master以及其其他slave的值一样。可以把 server-id 想象成为IP地址:这些ID标识了整个同步组合中的每个服务器。如果没有指定 server-id 的值,如果也没定义 master-host,那么它的值就为1,否则为2。
    注意,如果没有设定 server-id,那么master就会拒绝所有的slave连接,同时slave也会拒绝连接到master上。因此,省略配置 server-id 只对备份二进制日志有利。

  6. 如果已经备份了master上的数据(直接打包压缩的方式),那么在slave启动之前把它们拷贝过去。要确保文件的权限属主等设置没问题。MySQL运行的用户必须对这些文件有读写权限,就跟在master上一样。如果是用 mysqldump 备份的,那么可以直接启动salve(直接跳到下一步)。

  7. 启动slave,如果它之前已经运行同步了,那么在启动slave的时候使用 –skip-slave-start 选项使之不会立刻去连接master。最好也使用 –log-warnings 选项(从 MySQL 4.0.19 和 4.1.2 开始就是默认启用了)来启动slave,以知道发生问题时的更详细的信息(例如,网络或者连接问题)。从开始MySQL 4.0.21 和 4.1.3,异常中止的连接不再记录到错误日志中,除非 –log-warnings 选项的值大于1。

  8. 如果在master上用 mysqldump 备份数据的话,把文件导入slave中:

    shell> mysql -u root -p < dump_file.sql
    
  9. 在slave上执行如下语句,把各个选项的值替换成真实值:

    mysql> CHANGE MASTER TO
        ->     MASTER_HOST='master_host_name',
        ->     MASTER_USER='replication_user_name',
        ->     MASTER_PASSWORD='replication_password',
        ->     MASTER_LOG_FILE='recorded_log_file_name',
        ->     MASTER_LOG_POS=recorded_log_position;
    
  10. 启动slave线程:

    mysql> START SLAVE;
    

做完上述过程后,slave应该会连接到master上并且捕获所有从取得快照后的更新操作。

如果忘了设置master的 server-id 值,那么slave就不能连接到master上。
如果忘了设置master的 server-id 值,那么在错误日志中就会记录如下内容:

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的日志上。

一旦slave开始同步了,就能在数据文件目录下找到2个文件 master.inforelay-log.info。slave利用这2个文件来跟踪处理了多少master的二进制日志。

不要删除或者修改这2个文件,除非知道怎么改。尽管如此,我们更推荐用 CHANGE MASTER TO 语句来做。


参考资料

坚持原创技术分享,您的支持将鼓励我继续创作!