mysql/mariadb-双主复制

双主复制可以简单的理解为
一台服务器只用奇数的行，另一台只用偶数的行
主主复制：
互为主从：两个节点各自都要开启binlog和relay log；
1、数据不一致；
2、自动增长id；
定义一个节点使用奇数id
auto_increment_offset=1
auto_increment_increment=2
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2

配置：
1、server_id必须要使用不同值；
2、均启用binlog和relay log；
3、存在自动增长id的表，为了使得id不相冲突，需要定义其自动增长方式；

服务启动后执行如下两步：
4、都授权有复制权限的用户账号；
5、各把对方指定为主节点；

复制时应该注意的问题：
1、从服务设定为“只读”；
在从服务器启动read_only，但仅对非SUPER权限的用户有效；

    阻止所有用户：
        mysql> FLUSH TABLES WITH READ LOCK;

2、尽量确保复制时的事务安全

    在master节点启用参数：
        sync_binlog = ON 

        如果用到的是InnoDB存储引擎：
            innodb_flush_logs_at_trx_commit=ON
            innodb_support_xa=ON

3、从服务器意外中止时尽量避免自动启动复制线程

4、从节点：设置参数

    sync_master_info=ON

    sync_relay_log_info=ON

实验开始

node1
[ root@node1 ~ ]# vim /etc/my.cnf.d/server.cnf 

[server]
skip_name_resolve = ON
innodb_file_per_table = ON
max_connections = 20000

log_bin = /mydata/logs/master-log
server_id = 1
relay_log = relay-log

auto_increment_offset=1
auto_increment_increment=2

node2
[ root@node2 ~ ]# vim /etc/my.cnf.d/server.cnf 
[server]
skip_name_resolve = ON
innodb_file_per_table = ON
max_connections = 20000

relay_log = relay-log
server_id = 2
log-bin = master-log

auto_increment_offset=2
auto_increment_increment=2

然后都启动mariadb
查看各自的主日志在哪个节点上

node1
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000032 |      245 |              |                  |
+-------------------+----------+--------------+------------------+

node2
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000001 |      245 |              |                  |
+-------------------+----------+--------------+------------------+

接下来，各自把对方当主，并且都指向对面的二进制日志的当时的节点，
且确认都拥有对方复制权限的账号。

node1
MariaDB [(none)]> select user,host,password from mysql.user;
+----------+-------------+-------------------------------------------+
| user     | host        | password                                  |
+----------+-------------+-------------------------------------------+
| root     | localhost   |                                           |
| root     | node2       |                                           |
| root     | 127.0.0.1   |                                           |
| root     | ::1         |                                           |
|          | localhost   |                                           |
|          | node2       |                                           |
| repluser | 172.18.25.% | *D98280F03D0F78162EBDBB9C883FC01395DEA2BF |
+----------+-------------+-------------------------------------------+

node2
MariaDB [(none)]> select user,host,password from mysql.user;
+----------+-------------+-------------------------------------------+
| user     | host        | password                                  |
+----------+-------------+-------------------------------------------+
| root     | localhost   |                                           |
| root     | node2       |                                           |
| root     | 127.0.0.1   |                                           |
| root     | ::1         |                                           |
|          | localhost   |                                           |
|          | node2       |                                           |
| repluser | 172.18.25.% | *D98280F03D0F78162EBDBB9C883FC01395DEA2BF |
+----------+-------------+-------------------------------------------+

现在我们都change指向对方合适的节点就好了

node1
MariaDB [(none)]> change master to master_host='172.18.25.52',master_user='repluser',master_password='replpass',master_log_file='master-log.000001',master_log_pos=245;
MariaDB [(none)]>start slave;

node2
MariaDB [(none)]> change master to master_host='172.18.25.51',master_user='repluser',master_password='replpass',master_log_file='master-log.000032',master_log_pos=245;
MariaDB [(none)]>start slave;

接下来测试一下
在node1上面

MariaDB [hidb]> insert into students (name,age,gender,major) values ('trump',73,'M','president'),('obama',57,'M','ex-president');
MariaDB [hidb]> select * from students;
| 1001 | trump  |   73 | M      | president           |
| 1003 | obama  |   57 | M      | ex-president        |
+------+--------+------+--------+---------------------+

在node2上面查看

MariaDB [hidb]> select * from students;
| 1001 | trump  |   73 | M      | president           |
| 1003 | obama  |   57 | M      | ex-president        |
+------+--------+------+--------+---------------------+

但是再在node2上面插入两行数据

MariaDB [hidb]> insert into students (name,age,gender,major) values ('xin ba',40,'M','NK president'),('xin ba die',50,'M','NKK-president');

查询会发现

MariaDB [hidb]> select * from students;
| 1004 | xin ba     |   40 | M      | NK president        |
| 1006 | xin ba die |   50 | M      | NKK-president       |
+------+------------+------+--------+---------------------+

                                    就会发现数据中间有缝隙

好了实验结束