Mysql5.1.70-主主同步环境搭建

1、 前置声明

两个Mysql数据库部署节点信息如下：

• dbsrv1 数据库服务器一
• dbsrv2 数据库服务器二

数据库版本为:

Server version: 5.1.70-community MySQL Community Server (GPL)

配置文件位置：

Windows上的MySQL数据库 my.ini 配置文件一般在 C:\Program Files\MySQL\MySQL Server 5.1 目录下。

物理部署视图如下：

3、 主库A部署

这里指的主库A为 dbsrv1 ,按如下步骤配置主库。

①、 配置my.ini

编辑 dbsrv1 上的Mysql配置文件my.ini，在my.ini最后添加如下内容，并保存（以管理员用户）。

# Master 
server_id = 11                   #本机数据库ID标示为主，可以取IP地址第四部分                 
log_bin = master-binlog          #启动二进制日志系统
binlog_format = row
log_slave_updates = 1            #将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
binlog_cache_size = 256M
max_binlog_cache_size = 2G
max_binlog_size = 1G
skip_slave_start=1
auto_increment_offset = 1        #奇数
auto_increment_increment = 2     #偶数
binlog_do_db=demodb              #二进制需要同步的数据库名
binlog_ignore_db=mysql           #二进制不需要同步的数据库名

②、 重启mysql服务

重启命令如下：

net stop mysql
net start mysql

重启效果如下：

C:\Windows\system32>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。

C:\Windows\system32>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

注意事项： 上述命令以管理员账号权限运行！！！

③、 查看主库A状态

查询效果类似如下：

C:\Windows\system32>mysql -uroot -proot123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.70-community-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> show master status;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| master-binlog.000014 |      106 | demodb       | mysql            |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

④、 创建同步用户并授权

创建从库A同步用户，并授权。

SQL命令下创建一个名叫 sync的用户，密码为 syncr ：

create user sync@'%' identified by 'sync';
grant replication slave,replication client on *.* to 'sync'@'%';
flush privileges;

执行效果如下：

mysql> create user sync@'%' identified by 'sync';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave,replication client on *.* to 'sync'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>

4、 主库B部署

这里指的主库B为 dbsrv2 ,按如下步骤配置备库。

①、 配置my.ini

编辑 dbsrv2上的Mysql配置文件my.ini，在my.ini最后添加如下内容，并保存（以管理员用户）。

# Slave
server_id = 12                   #本机数据库ID标示为主，可以取IP地址第四部分                 
log_bin = master-binlog          #启动二进制日志系统
binlog_format = row
log_slave_updates = 1            #将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
binlog_cache_size = 256M
max_binlog_cache_size = 2G
max_binlog_size = 1G
skip_slave_start=1
auto_increment_offset = 2        #偶数
auto_increment_increment = 2     #偶数
binlog_do_db=demodb              #二进制需要同步的数据库名
binlog_ignore_db=mysql           #二进制不需要同步的数据库名

②、 重启mysql服务

重启命令如下：

net stop mysql
net start mysql

重启效果如下：

C:\Windows\system32>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。

C:\Windows\system32>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

注意事项： 上述命令以管理员账号权限运行！！！

③、 查看主库B状态

查询效果类似如下：

C:\Windows\system32>mysql -uroot -proot123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.70-community-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> show master status;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| master-binlog.000001 |      106 | demodb       | mysql            |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

④、 创建同步用户并授权

创建主库B同步用户，并授权。

SQL命令下创建一个名叫 sync的用户，密码为 sync ：

create user sync@'%' identified by 'sync';
grant replication slave,replication client on *.* to 'sync'@'%';
flush privileges;

执行效果如下：

mysql> create user sync@'%' identified by 'sync';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave,replication client on *.* to 'sync'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>

4、 配置主主同步

①、 主库A指定同步信息

• 主库 dbsrv1的同步库为dbsrv2 ;

主库A Mysql 执行如下 SQL语句。

change master to
master_host='192.168.58.163',      
master_port=3306,
master_user='sync',
master_password='sync',
master_log_file='master-binlog.000001',     
master_log_pos=106;

注意事项：

• 上述脚本中的 master_log_file 和 master_log_pos 值是从另一个同步库上查询到的master参数。
• master_host 指向的IP地址为两个数据库服务器间的**C**网（直连）IP地址。

执行效果如下：

C:\Users\Administrator>mysql -uroot -proot123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.70-community MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> change master to
    -> master_host='192.168.58.163',
    -> master_port=3306,
    -> master_user='sync',
    -> master_password='sync',
    -> master_log_file='master-binlog.000001',
    -> master_log_pos=106;
Query OK, 0 rows affected (0.07 sec)

mysql>

②、 主库B指定同步信息

• 主库 dbsrv2的同步库为dbsrv1 ;

主库B Mysql 执行如下 SQL语句。

change master to
master_host='192.168.58.1',      
master_port=3306,
master_user='sync',
master_password='sync',
master_log_file='master-binlog.000014',     
master_log_pos=106;

其中的 master_log_file 和 master_log_pos 是从主库A查询到的值，在主库A上查询master信息，查询效果如下：

mysql>
mysql>
mysql> show master status;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| master-binlog.000014 |     106  | demodb       | mysql            |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

执行效果如下：

C:\Users\Administrator>mysql -uroot -proot123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.70-community MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> change master to
    -> master_host='192.168.58.1',
    -> master_port=3306,
    -> master_user='sync',
    -> master_password='sync',
    -> master_log_file='master-binlog.000014',
    -> master_log_pos=106;
Query OK, 0 rows affected (0.07 sec)

mysql>

④、 主库A主库B启动slave服务

所有数据库节点开启 slave 服务，命令如下：

start slave

执行效果如下：

C:\Users\Administrator>mysql -uroot -proot123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.70-community MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql>

⑤、 查看同步状态

通过 show slave status\G 查看从库状态。

mysql>
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.58.1
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-binlog.000010
          Read_Master_Log_Pos: 411
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 560
        Relay_Master_Log_File: master-binlog.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 411
              Relay_Log_Space: 709
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

mysql>