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 = ONinnodb_file_per_table = ONmax_connections = 20000log_bin = /mydata/logs/master-logserver_id = 1relay_log = relay-logauto_increment_offset=1auto_increment_increment=2

node2[ root@node2 ~ ]# vim /etc/my.cnf.d/server.cnf [server]skip_name_resolve = ONinnodb_file_per_table = ONmax_connections = 20000relay_log = relay-logserver_id = 2log-bin = master-logauto_increment_offset=2auto_increment_increment=2

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

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

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

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

node1MariaDB [(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 |+----------+-------------+-------------------------------------------+

node2MariaDB [(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指向对方合适的节点就好了

node1MariaDB [(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;

node2MariaDB [(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       |+------+------------+------+--------+---------------------+

                                    就会发现数据中间有缝隙

好了实验结束