Mysql的双主架构

                                            Mysql的双主架构

双主模型架构：两台双主都有二进制日志和中继日志。都要有dump线程，io线程，sql线程。

         io线程：负责从其他节点请求二进制日志事件。

         dump线程：从本地的二进制日志读取事件。

         sql线程：从中继日志中读取事件保存在本地数据库中，并写入二进制日志。

配置实现双主模型：

        1）双方节点具有复制权限的用户

        2）双方节点都得启用中继日志和需要配置二进制日志

        3）为保证具有自动增长功能的字段能正确生成id，需要配置两个节点分别使用偶数或奇数。

本次实验使用两台服务器两节点分别node1:192.168.1.117，node2:192.168.1.119。在上面两个节点最好干净的数据库，先做下初始化。之后就可以配置mysql的双主模型了。

[root@www mydata]# ls    

binlogs  data  relaylogs               //此三个目录文件在配置中都会使用到。

[root@www mydata]# ll                //还需要一下三个目录，他们的属主属组为mysql。

total 12

drwxr-xr-x. 2 mysql mysql 4096 Feb 15 13:25 binlogs

drwxr-xr-x. 2 mysql mysql 4096 Feb 15 13:24 data

drwxr-xr-x. 2 mysql mysql 4096 Feb 15 13:26 relaylogs

[root@www mydata]#

在两个node节点上的配置文件处分别编辑如下：

在node1上

[root@www mysql]# vim /etc/mysql/my.cnf

# binary logging is required for replication

log-bin=/mydata/binlogs/mysql-master1-bin   //二进制日志存放位置。

relay-log=/mydata/relaylogs/mysql-relay-log   //中继日志存放位置。

# binary logging format - mixed recommended

binlog_format=mixed

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id       = 1

auto-increment-offset  =1

auto-increment-increment  =2            //id每次增加2。

在node2节点上。

log-bin=/mydata/binlogs/mysql-master2-bin

relay-log=/mydata/relaylogs/mysql-relay-log

# binary logging format - mixed recommended

binlog_format=mixed 

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id       = 11

auto-increment-offset  =2

auto-increment-increment  =2

分别启动数据库

[root@bogon data]# service mysqld start

Starting MySQL.......                                      [  OK  ]

在node1和node2上

MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO reuser@‘192.168.1.%‘

IDENTIFIED BY ‘repass‘;

Query OK, 0 rows affected (0.09 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

在node1上

MariaDB [(none)]> SHOW MASTER STATUS;

+--------------------------+----------+--------------+------------------+

| File                     | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+--------------------------+----------+--------------+------------------+

| mysql-master1-bin.000001 |      501 |              |                  |

+--------------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

MariaDB [(none)]>

在node2上

MariaDB [(none)]> SHOW MASTER STATUS;

+--------------------------+----------+--------------+------------------+

| File                     | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+--------------------------+----------+--------------+------------------+

| mysql-master2-bin.000003 |      501 |              |                  |

+--------------------------+----------+--------------+------------------+

1 row in set (0.81 sec)

MariaDB [(none)]>

在node2上

MariaDB [(none)]> CHANGE MASTER TO

MASTER_HOST=‘192.168.1.117‘,MASTER_USER=‘reuser‘,MASTER_PASSWORD=‘repass‘,MASTER_LOG_FILE=‘mysql-master1-bin.000001‘,MASTER_LOG_POS=501;

Query OK, 0 rows affected (0.06 sec)

在node1上

MariaDB [(none)]> CHANGE MASTER TO

MASTER_HOST=‘192.168.1.119‘,MASTER_USER=‘reuser‘,MASTER_PASSWORD=‘repass‘,MASTER_LOG_FILE=‘mysql-master2-bin.000003‘,MASTER_LOG_POS=501;

Query OK, 0 rows affected (0.16 sec)

MariaDB [(none)]> START SLAVE;

Query OK, 0 rows affected (0.08 sec)

MariaDB [(none)]> SHOW SLAVE STATUS\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.1.119

                  Master_User: reuser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-master2-bin.000003

          Read_Master_Log_Pos: 501

               Relay_Log_File: mysql-relay-log.000002

                Relay_Log_Pos: 537

        Relay_Master_Log_File: mysql-master2-bin.000003

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

在node2上

MariaDB [(none)]> CHANGE MASTER TO

MASTER_HOST=‘192.168.1.117‘,MASTER_USER=‘reuser‘,MASTER_PASSWORD=‘repass‘,MASTER_LOG_FILE=‘mysql-master1-bin.000001‘,MASTER_LOG_POS=501;

Query OK, 0 rows affected (0.16 sec)

MariaDB [(none)]> START SLAVE;

Query OK, 0 rows affected (0.08 sec)

MariaDB [(none)]> SHOW SLAVE STATUS\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.1.117

                  Master_User: reuser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-master1-bin.000001

          Read_Master_Log_Pos: 501

               Relay_Log_File: mysql-relay-log.000002

                Relay_Log_Pos: 537

        Relay_Master_Log_File: mysql-master1-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:  

在node2上创建库。

MariaDB [(none)]> CREATE DATABASE double_master;

Query OK, 1 row affected (0.00 sec)

在node1上查看所有数据库。

MariaDB [(none)]> SHOW DATABASES;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| double_master      |

| mysql              |

| performance_schema |

| test               |

+--------------------+

5 rows in set (0.05 sec)

在node1上创建表。

MariaDB [(none)]> USE double_master;

Database changed

MariaDB [double_master]> CREATE TABLE double_tb1(id INT(11) AUTO_INCREMENT PRIMARY KEY,name CHAR(20),gender ENUM(‘m‘,‘f‘));

Query OK, 0 rows affected (0.55 sec) 

MariaDB [double_master]> INSERT INTO double_tb1 VALUES(NULL,‘Wu Zetian‘,‘f‘),(NULL,‘Li Shimin‘,‘m‘);

Query OK, 2 rows affected (0.07 sec)

Records: 2  Duplicates: 0  Warnings: 0

MariaDB [double_master]> SELECT * FROM double_tb1;    //查看表double_tb1数据。

+----+-----------+--------+

| id | name      | gender |

+----+-----------+--------+

|  1 | Wu Zetian | f      |

|  3 | Li Shimin | m      |

+----+-----------+--------+

2 rows in set (0.06 sec) 

在node2节点上查看。

MariaDB [(none)]> USE double_master;

Database changed

MariaDB [double_master]> SHOW TABLES;

+-------------------------+

| Tables_in_double_master |

+-------------------------+

| double_tb1              |

+-------------------------+

1 row in set (0.00 sec)

MariaDB [double_master]> SELECT * FROM double_tb1;    //查看结果和node1上一样。

+----+-----------+--------+

| id | name      | gender |

+----+-----------+--------+

|  1 | Wu Zetian | f      |

|  3 | Li Shimin | m      |

+----+-----------+--------+

2 rows in set (0.07 sec)