双主复制可以简单的理解为
一台服务器只用奇数的行,另一台只用偶数的行
主主复制:
互为主从:两个节点各自都要开启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 |
+------+------------+------+--------+---------------------+
就会发现数据中间有缝隙
好了实验结束