MySQL双Master配置

MySQL双Master配置

环境
Centos 5.3                                      (32位版本)
MYSQL Server version: 5.0.45-log Source distribution  (32位版本)
两台服务器都是同样软件环境
1、环境描述。
主机：192.168.1.221（A）
主机：192.168.1.227（B）

此文章适用，当前主库备库已经内容当前一样，实现当前只要有一方内容变化，立刻实现同步。
如果系统中存在iptables,fail2ban这类软件，请先禁用，设定好之后再重新开启并设定好它.
===================================================================================
3、配置文件。
在两个机器上的my.cnf里面都开启二进制日志。
A：
vi /etc/my.cnf
[mysqld]
user = mysql
log-bin=mysql-bin              #ID值唯一的标识了复制群集中的主从服务器，因此它们必须各不相同。Master_id必须为1到232－1之间的                               #一个正整数值，slave_id值必须为2到232－1之间的一个正整数值
server-id = 1                  #ID值唯一的标识了复制群集中的主从服务器，因此它们必须各不相同。Master_id必须为1到232－1之间的一个正整数值，slave_id值必须为2到232－1之间的一个正整数值
binlog-do-db=extmail           #要同步的数据库，如何是多DB同步，可以重复此参数，下面会详细注解
binlog-ignore-db=mysql         #要同步的数据库，如何是多DB同步，可以重复此参数，下面会详细注解
replicate-do-db=extmail        #表示需要同步的数据库,如果有多个就用逗号隔开
replicate-ignore-db=mysql      #表示不需要同步的数据库,如果有多个数据库可用逗号隔开,
log-slave-updates              #配置从库上的更新操作是否写入二进制文件，如果这台从库，
                                 还要做其他从库的主库，那么就需要打这个参数，
                                 以便从库的从库能够进行日志同步
slave-skip-errors=all           #忽略所有错误信息,
sync_binlog=1                   #设置binlog有更新的时候刷新到磁盘
auto_increment_increment=2      #-- 表示控制列，行自增起始大小，这个起始增加从2开始，每次加+1
auto_increment_offset=1         #如果auto_increment_offset的值大于auto_increment_increment的值，
                                  则auto_increment_offset的值被忽略。

修改完成请重启MYSQL服务器
[root@test-mail ~]# service mysqld restart
Stopping MySQL:  [  OK  ]
Starting MySQL:  [  OK  ]
**********************************
如果是多DB同步的话就在
binlog-do-db,replicate-do-db设定
比如
binlog-do-db=extmail
binlog-do-db=dspam
replicate-do-db=extmail
replicate-do-db=dspam
**********************************
----------------------------------------------------------------------------------------------
B：
user = mysql
log-bin=mysql-bin
server-id       = 2
binlog-do-db=extmail
binlog-ignore-db=mysql
replicate-do-db=extmail
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1  
auto_increment_increment=2
auto_increment_offset=2


重新启动MYSQL服务器。
service mysqld restart
在A和B上执行相同的步骤
===================================================================================
注意：MySQL?5.1.7版本之后，已经不支持把master配置属性写入my.cnf配置文件中了，
只需要把同步的数据库和要忽略的数据库写入即可
===================================================================================

2、授权用户。

A：
本机登录MYSQL的方法
[root@test-mail ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.45-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
(授权用户)
mysql> grant all privileges on *.* to 'repl1'@'%'identified by '123456' with grant option;
Query OK, 0 rows affected (0.00 sec)

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

mysql> grant all privileges on *.* to 'repl2'@'%'identified by '123456' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
-------------------------------------------------------------------------------------------
B：
登录MYSQL
[root@test-mail2 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.45-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
授权用户
mysql> grant all privileges on *.* to 'repl1'@'%'identified by '123456' with grant option;
Query OK, 0 rows affected (0.00 sec)

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

mysql> grant all privileges on *.* to 'repl2'@'%'identified by '123456' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
---------------------------------------------------------------------------------------------
A: 测试授权的两个帐户是否OK
root@test-mail ~]# mysql -h 192.168.1.227 -u repl1 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.45-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

[root@test-mail ~]# mysql -h 192.168.1.227 -u repl2 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.45-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

B：测试授权的两个帐户是否OK
[root@test-mail2 ~]# mysql -h 192.168.1.221 -u repl1 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.45-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> Aborted
[root@test-mail2 ~]# mysql -h 192.168.1.221 -u repl2 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.45-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
=============================================================================================
A,B两点通过show master status命查看position，file下面两个参数，以这两个参数来设定change master
A:
mysql> show master status;
+------------------+----------+-----------------+------------------+
| File             | Position | Binlog_Do_DB    | Binlog_Ignore_DB |
+------------------+----------+-----------------+------------------+
| mysql-bin.000001 |       98 | extmail,extmail | mysql,mysql      |
+------------------+----------+-----------------+------------------+
1 row in set (0.00 sec)
---------------------------------------------------------------------------------------------
B:
mysql> show master status;
+------------------+----------+-----------------+------------------+
| File             | Position | Binlog_Do_DB    | Binlog_Ignore_DB |
+------------------+----------+-----------------+------------------+
| mysql-bin.000001 |       98 | extmail,extmail | mysql,mysql      |
+------------------+----------+-----------------+------------------+
1 row in set (0.00 sec)

=============================================================================================
在各自机器上执行CHANGE MASTER TO命令。
A：
mysql> change master to
    -> master_host='192.168.1.227',#主服务器的IP地址
    -> master_user='repl1',        #同步数据库用户
    -> master_password='123456', #同步数据库的密码
    -> master_log_file='mysql-bin.000001', #主服务器二进制日日志文件名
    -> master_log_pos=98;                #日志文件的开始位置(前面要求记住的参数)
Query OK, 0 rows affected (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)


B：
mysql> change master to
    ->master_host='192.168.1.221',
    ->master_user='repl2',
    ->master_password='123456',
    ->master_log_file='mysql-bin.000001',
    ->master_log_pos=98;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

7、查看各自机器上的IO进程和 SLAVE进程是否都开启。
A：查看A点设定是否正确
注意查看：
Slave_IO_Running:YesSlave_SQL_Running:Yes
以上这两个参数的值为Yes，即说明配置成功！
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.1.227
                Master_User: repl1
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 98
             Relay_Log_File: mysqld-relay-bin.000002
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: extmail,extmail
        Replicate_Ignore_DB: mysql,mysql,information_schema,mysql,mysql,information_schema
         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: 98
            Relay_Log_Space: 235
            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
1 row in set (0.00 sec)
------------------------------------------------------------------------------------
B 查看B点设定是否正确
注意查看：
Slave_IO_Running:YesSlave_SQL_Running:Yes
以上这两个参数的值为Yes，即说明配置成功！
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.1.221
                Master_User: repl2
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 98
             Relay_Log_File: mysqld-relay-bin.000002
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: extmail,extmail
        Replicate_Ignore_DB: mysql,mysql,information_schema,performance_schema,mysql,mysql,information_schema,performance_schema
         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: 98
            Relay_Log_Space: 235
            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
1 row in set (0.00 sec)
=====================================================================================================
好了。现在两个表互相为MASTER。
建表测试AB点是否真正OK。
A
mysql> use extmail
Database changed
mysql> CREATE TABLE t1(id int not null primary key,name char(20));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values ("1","123");
Query OK, 1 row affected (0.02 sec)
去B点查看，表是否已经同步过去
mysql> use extmail;
Database changed
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | 123  |
+----+------+
1 row in set (0.00 sec)

B.对同表插入数据查看A点表是否同步数据

mysql> insert into t1 values("2","456");
Query OK, 1 row affected (0.03 sec)
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | 123  |
|  2 | 456  |
+----+------+
2 rows in set (0.00 sec)
现在看来，两边数据已互通master to master 两边同步方式架设成功。

==============================================================================================

mysql远程访问另一台MYSQL的方法
mysql -h192.168.1.227 -uroot -p1

slave?start;#开启slave同步进程
slave?stop;#停止slave同步进程

#查看slave同步信息，出现以下内容
SHOW SLAVE STATUS\G


查看MYSQL版本
select version();


1、配置防火墙，开启MySQL默认3306端口
vi?/etc/sysconfig/iptables#编辑防火墙配置文件
-A INPUT-mstate --state NEW-mtcp-ptcp--dport?3306?-j?ACCEPT?/etc/init.d/iptables?restart??#重启防火墙，使配置生效?

查看MYSQL的日志
[root@test-mail mysql]# ps -ef|grep mysql
root      5143  2043  0 20:32 pts/0    00:00:00 mysql -u root -p
root      5901     1  0 21:01 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid
mysql     5949  5901  0 21:01 pts/1    00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
root      6502  2575  0 21:23 pts/1    00:00:00 grep mysql


--log-error=/var/log/mysqld.log #就是mysql地址

这里我可以用tail -f /var/log/mysqld.log   查看动态日志

[root@test-mail mysql]# tail -f 10  /var/log/mysqld.log  #我这里查看的是日志前10条信息，太多查看不过来
tail: cannot open `10' for reading: No such file or directory
==> /var/log/mysqld.log <==
130528 21:04:56 [Note] Slave I/O thread: connected to master 'repl1@192.168.1.227:3306',  replication started in log 'mysql-bin.000003' at position 98
130528 21:05:38 [Note] Slave: received end packet from server, apparent master shutdown:
130528 21:05:38 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000003' position 98
130528 21:05:38 [ERROR] Slave I/O thread: error reconnecting to master 'repl1@192.168.1.227:3306': Error: 'Lost connection to MySQL server at 'reading initial communication packet', system error: 111'  errno: 2013  retry-time: 60  retries: 86400
130528 21:06:38 [Note] Slave: connected to master 'repl1@192.168.1.227:3306',replication resumed in log 'mysql-bin.000003' at position 98
130528 21:09:05 [Note] Slave I/O thread killed while reading event
130528 21:09:05 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000004', position 13035
130528 21:09:05 [Note] Error reading relay log event: slave SQL thread was killed
130528 21:09:07 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000004' at position 13035, relay log '/var/run/mysqld/mysqld-relay-bin.000004' position: 13172
130528 21:09:07 [Note] Slave I/O thread: connected to master 'repl1@192.168.1.227:3306',  replication started in log 'mysql-bin.000004' at position 13035