MySQL 双主模型其实就是在主从模型上,在主服务器上启用复制功能并将master_host指向从服务器,这样两台服务器都启用复制功能并将master_host各指向对方,两台服务器的任何一台上面的数据库存发生了改变都会同步到另一台服务器上。
配置Master1和Master2服务器的基本要求:
1、Master1和Master2都得创建具有复制权限的账号;
2、Master1和Master2都得启动中继日志和二进制日志;
3、为了保证具有自动增长功能的字段能正确生成ID,需要配置Master1和Master2分别使用奇数和偶数ID号;
4、Master1和Master2都要将对方配置为自己的主节点。
以下使用以下基于 CentOS x86_64 使用 mariadb-10.1.11.tar.gz 进行配置,首先在两个节点安装好mariadb,可以在https://downloads.mariadb.org/interstitial/mariadb-10.1.11/source/mariadb-10.1.11.tar.gz此处进行下载。编译安装的过程可以参考编译安装MySQL。
master1:192.168.1.106 CentOS6.6 x86_64 master2:192.168.1.113 CentOS6.6 x86_64
1)配置master1
创建具有复制权限的账号:
MariaDB [(none)]> grant replication slave,replication client on *.* to 'repluser'@'192.168.%.%' identified by 'replpass'; Query OK, 0 rows affected (0.04 sec)
修改master1的配置文件:
[root@localhost ~]# vim /etc/my.cnf [mysqld] server-id = 10 log-bin = mysql-bin relay-log = relay-mysql auto-increment-offset = 1 # 起始值 auto-increment-increment = 2 # 步长
修改好配置文件后,然后创建所需目录:
[root@localhost ~]# mkdir /mydata/binlogs [root@localhost ~]# mkdir /mydata/relaylogs [root@localhost ~]# chown -R mysql.mysql /mydata/relaylogs/ /mydata/binlogs mysql> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000002 | 314 | | | +-------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
2)配置master2
创建具有复制权限的账号:
MariaDB [(none)]> grant replication slave,replication client on *.* to 'repluser'@'192.168.%.%' identified by 'replpass'; Query OK, 0 rows affected (0.04 sec)
修改master1的配置文件:
[root@localhost ~]# vim /etc/my.cnf [mysqld] server-id = 20 log-bin = mysql-bin relay-log = relay-mysql auto-increment-increment = 2 # 步长 auto-increment-offset = 2 # 起始值
修改好配置文件后,然后创建所需目录:
[root@localhost ~]# mkdir /mydata/binlogs [root@localhost ~]# mkdir /mydata/relaylogs [root@localhost ~]# chown -R mysql.mysql /mydata/relaylogs/ /mydata/binlogs MariaDB [(none)]> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-log.000001 | 534 | | | +-------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
配置slave服务,确保两边的数据库一致,先不要启动slave服务,:
MariaDB [(none)]> change master to master_host='192.168.1.106',master_user='repluser',master_password='replpass', master_log_file='master-bin.000002',master_log_pos=314; Query OK, 0 rows affected (0.01 sec)
然后继续在master1上配置 slave 服务:
mysql> change master to master_host='192.168.1.113',master_user='repluser',master_password='replpass',master_log_file='master-log.000001',master_log_pos=534; Query OK, 0 rows affected (0.06 sec)
两边都配置完成后,再启动服务 :
mysql> start slave; Query OK, 0 rows affected (0.00 sec)
3)验证主主模型是否正常工作
在master1上插入数据:
mysql> create database newdb; Query OK, 1 row affected (0.00 sec) mysql> use newdb; Database changed mysql> create table t1(id int not null primary key auto_increment,name char(20)); Query OK, 0 rows affected (0.03 sec)
在master2上查看:
MariaDB [(none)]> use newdb; Database changed MariaDB [newdb]> show tables; +-----------------+ | Tables_in_newdb | +-----------------+ | t1 | +-----------------+ 1 row in set (0.00 sec)
在master1上插入数据验证id号是否冲突:
mysql> insert into t1(name) values('tom'),('feiyu'); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t1; +----+-------+ | id | name | +----+-------+ | 1 | tom | | 3 | feiyu | +----+-------+ 2 rows in set (0.00 sec)
在master2上插入数据并验证:
MariaDB [newdb]> insert into t1(name) values('user1'),('user2'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [newdb]> select * from t1; +----+-------+ | id | name | +----+-------+ | 1 | tom | | 3 | feiyu | | 4 | user1 | | 6 | user2 | +----+-------+ 4 rows in set (0.00 sec)
可以看到数据没有冲突,主主模型已经可以正常工作了!