MySQL 主- 主双向同步测试报告一、测试环境服务器 1（Master1）：IP 地址：192.168.0.20，操作系统：Windows XP SP3，MySQL 版本：5.5.11服务器 2（Master2）：IP 地址：192.168.0.252，操作系统：Windows XP SP3，MySQL 版本：5.5.11前提条件：服务器 1 和服务器 2 中 MySQL 数据库的个数、名称，数据表的个数、名称、结果强烈建议一致，并且 MySQL 的版本保证一致，且在 5.0 以上。Linux 系统下配置方法与 Windows 系统相同，只是需要注意配置文件为 my.cnf，且配置文件中一定要注意将 bind-address=127.0.0.1 注释，否则，远程无法登录 MySQL 数据库，更谈不上同步。二、同步原理两台数据库服务器既为主服务器，亦为从服务器。某一时刻只允许某台服务器进行数据更新操作，另外一台服务器同步数据。换言之，两台服务器互为对方的主服务器，亦互为对方的从服务器。主服务器用于数据更新或者（和）查询操作，从服务器用于数据同步和查询操作。三、服务器配置1）在服务器 1 上为服务器 2 授权同步账户执行 SQL 指令：mysql GRANT REPLICATION SLAVE ON *.* TO SlaveRepl1192.168.0.252 IDENTIFIED BY 123456;mysql FLUSH PRIVILEGES;图 1 为服务器 2 授权同步账户2）在服务器 2 上为服务器 1 授权同步账户执行 SQL 指令：mysql GRANT REPLICATION SLAVE ON *.* TO SlaveRepl2192.168.0.20 IDENTIFIED BY 123456;mysql FLUSH PRIVILEGES;图 2 为服务器 1 授权同步账户3）停止服务器 1 和服务器 2 上 MySQL 数据库服务4）修改服务器 1 和服务器 2 的配置文件 my.ini（Linux 下为 my.cnf）A）服务器 1mysqldlog-bin = mysql-bin #记录二进制日志文件server-id = 1 #服务器 ID 标识，每台服务器必须唯一binlog-ignore-db = mysql #通知主服务器忽略记录二进制日志的数据库replicate-ignore-db = mysql #通知从服务器忽略同步复制的数据库log-slave-updates #通知从服务器将更新记录二进制日志文件sync_binlog = 1 #保证事务 InnoDB 复制设置的innodb_flush_log_at_trx_commit = 1#最大可能的耐受性和一致性auto_increment_increment = 2 #控制自增列列值增加的间隔auto_increment_offset = 1 #控制自增列列值的起点log-warnings #输出更详细的执行操作信息A）服务器 2mysqldlog-bin = mysql-bin #记录二进制日志文件server-id = 2 #服务器 ID 标识，每台服务器必须唯一binlog-ignore-db = mysql #通知主服务器忽略记录二进制日志的数据库replicate-ignore-db = mysql #通知从服务器忽略同步复制的数据库log-slave-updates #通知从服务器将更新记录二进制日志文件sync_binlog = 1 #保证事务 InnoDB 复制设置的innodb_flush_log_at_trx_commit = 1#最大可能的耐受性和一致性auto_increment_increment = 2 #控制自增列列值增加的间隔auto_increment_offset = 2 #控制自增列列值的起点log-warnings #输出更详细的执行操作信息注：:红色部分非常重要，如果一个服务器当掉的话，另外一个服务器马上接管；:紫红色部分标识服务器频繁二进制日志文件，保证一条服务器当掉的话，日志刷新到另外一台，最大限度保证数据的同步；:每台服务器配置的 auto_increment_offset 必须不一样，其最大值建议与auto_increment_increment 相同。一个数据库复制同步群中，有多少台主服务器（或者预期会转化为主服务器） ，auto_increment_increment 就设置多少，且每台服务器上的设置值必须保证一致；:根据需要配置忽略同步复制的数据库，有多少个数据库需要忽略，则需配置多少行binlog-ignore-db 和 replicate-ignore-db。如果每个数据库欲保留其本身独立的权限分配和系统参数，建议将系统数据库 mysql、information_schema、performance_schema 忽略同步复制；5）启动服务器 1 和服务器 2 上 MySQL 数据库服务。6）获取服务器主数据库状态A）参照图 3 所示，获取服务器 1 的状态。图 3 服务器 1 主数据库状态File：mysql-bin.000009;Position：107;B）参照图 4 所示，获取服务器 2 状态。图 3 服务器 2 主数据库状态File：mysql-bin.000003;Position：107;注意：1）如果有重要应用数据存在，建议执行完该步骤后，备份各自的数据库，有备无患。2）获取主数据库状态后，请确保服务器没有更新操作（创建/修改/ 删除数据库、数据表、数据字段，插入/更新/删除数据） ，否则，请务必不要执行 UNLOCK TABLES，直到同步模式全部配置完毕。7）停止所有 Slave 进程，并修改主服务器指向参数A）服务器 1 对应的主服务器为服务器 2，修改主服务器参数如图 4 所示。图 4 服务器 1 对应主服务器指向参数B）服务器 2 对应的主服务器为服务器 1，修改主服务器参数如图 5 所示。图 5 服务器 2 对应主服务器指向参数8）在服务器 1 和服务器 2 中各自启动 Slave 进程。9）利用 SHOW PROCESSLIST 指令查询 IO 进程和 SLAVE 进程是否开启A）服务器 1 进程图 6 服务器 1 Slave 进程红色框中的内容出现，表示配置参数成功。否则，请检查 Data 命令下的错误日志。图 7 服务器 1 Slave 进程状态红色框中的内容出现，表示配置参数成功。否则，请检查 Data 命令下的错误日志。B）服务器 2 状态图 8 服务器 2 Slave 进程红色框中的内容出现，表示配置参数成功。否则，请检查 Data 命令下的错误日志。图 9 服务器 2 Slave 进程状态红色框中的内容出现，表示配置参数成功。否则，请检查 Data 命令下的错误日志。四、同步测试一）创建数据库（包括数据库、数据表、初始化数据等）1）通过数据库创建工具，在服务器 1 上创建数据库 pxdb_master，服务器 1 和服务器 2 查询的结果如下：图 10 服务器 1 数据库查询结果图 11 服务器 2 数据库查询结果2）通过数据库创建工具，在服务器 2 上创建数据库 pxdb_client，服务器 1 和服务器 2 查询的结果同 1）所述，下面只列出存储过程、函数、触发器结果。.图 12 服务器 2 存储过程、函数、触发器查询结果.图 13 服务器 1 存储过程、函数、触发器查询结果二）一台服务器运行并更新数据，另一台服务器停止 MySQL 服务1）服务器 1 更新数据，服务器 2 停止服务。服务器 1 更新数据之前的查询结果图 14 服务器 1 更新数据之前的查询结果图 15 服务器 2 停止服务之前的查询结果图 16 服务器 1 插入数据及其查询结果图 17 服务器 2 重启 MySQL 服务后的查询结果2）服务器 2 更新数据，服务器 1 停止 MySQL 服务。图 18 服务器 2 更新操作及其查询结果图 19 服务器 1 重启 MySQL 服务之后同步查询的结果