浅谈MYSQL之主主复制
主主复制理念
- 双主架构是实现主库写高可用的一种架构模式，两个主库互为对方的从库，两个主库均可以执行写操作，然后复制给对方。
- 主库停机维护：由于双方都有对方的binlog position，即知道当前复制到对方什么位置了，故当其中一方停机可以使用另外一方来作为主库，当停机的主库重启恢复回来时，可以从原来的binlog position继续复制当前的主库的数据，实现自动切换恢复。
- 主库故障自动切换：可以结合第三方的高可用工具HA来实现当其中一个主库挂掉时，自动切换到另外一个主库，实现故障自动切换和高可用。
循环复制问题
- 在这种模式下，由于双方都是主库，故均会将写操作记录到二进制日志中并复制给对方，这样会存在循环复制的问题。
- 故为了解决这个问题，MySQL在设计中，在搭建主从复制时，各个库需要指定自己的server-id，并且不能相同，所以通过该server-id每个主库可以判断出这个写操作是否是自己产生的，如果是则忽略复制过来的写操作
主主复制配置要点
- 配置一个节点使用id=1
auto_increment_offset=1 / 开始点
auto_increment_increment=2 / 增长点，几台主主这个点就是几
- 配置一个节点使用id=2
auto_increment_offset=2
auto_increment_increment=2
- 主主复制的配置步骤：
- (1) 各节点使用一个惟一server_id
- (2) 都启动binary log和relay log
- (3) 创建拥有复制权限的用户账号
- (4) 定义自动增长id字段的数值范围各为奇偶
- (5) 均把对方指定为主节点，并启动复制线程
实验：实现主主复制
-
准备工作
- 二台干净环境的主机
- 时间同步完成
- 防火墙关闭
- SELINUX关闭
-
配置1号MASTER172.20.54.1
vim /etc/my.cnf
[mysqld]
server-id=1
log-bin
auto-increment-offset=1 / 起始点
auto-increment-increment=2 / 增长点
systemctl start mariadb
MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 245 |
+--------------------+-----------+
1 row in set (0.00 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE 'auto%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 2 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
+--------------------------+-------+
4 rows in set (0.00 sec)
/ 创建复制权限账户
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repliuser'@'172.20.%.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
/ 找到复制账户创建之前的POS点
MariaDB [(none)]> SHOW BINLOG EVENTS IN 'mariadb-bin.000001';
+--------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------+
| mariadb-bin.000001 | 4 | Format_desc | 1 | 245 | Server ver: 5.5.64-MariaDB, Binlog ver: 4 |
| mariadb-bin.000001 | 245 | Query | 1 | 402 | GRANT REPLICATION SLAVE ON *.* TO 'repliuser'@'172.20.54.%' IDENTIFIED BY '123456' |
| mariadb-bin.000001 | 402 | Query | 1 | 491 | CREATE DATABASE TESTDB1 |
| mariadb-bin.000001 | 491 | Query | 1 | 580 | CREATE DATABASE TESTDB2 |
| mariadb-bin.000001 | 580 | Query | 1 | 669 | CREATE DATABASE TESTDB3 |
+--------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
- 配置第二个MASTER节点172.20.54.2
vim /etc/my.cnf
[mysqld]
server-id=2
log-bin
auto-increment-offset=2 / 起始点
auto-increment-increment=2 / 增长点
systemctl start mariadb
MariaDB [(none)]> SHOW VARIABLES LIKE 'auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 2 |
| auto_increment_offset | 2 |
+--------------------------+-------+
2 rows in set (0.00 sec)
/ 注意2号节点没有创建复制账号，因为二号建立主从复制时候自动把1号节点账号已经复制过来了
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='172.20.54.1',
-> MASTER_USER='repliuser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000001',
-> MASTER_LOG_POS=245,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.20.54.1
Master_User: repliuser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 406
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 692
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 406
Relay_Log_Space: 988
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
ERROR: No query specified
/ 查看二进制日志 POS点
MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 245 |
+--------------------+-----------+
1 row in set (0.00 sec)
- 继续配置1号MASTER成为2号机的从SLAVE
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='172.20.54.2',
-> MASTER_USER='repliuser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000001',
-> MASTER_LOG_POS=245,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.20.54.2
Master_User: repliuser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 245
Relay_Log_Space: 827
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
1 row in set (0.00 sec)
ERROR: No query specified
- 开始测试
/ 1号MASTER
MariaDB [(none)]> CREATE DATABASE testdb1;
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb1]> CREATE TABLE t1(id int);
Query OK, 0 rows affected (0.00 sec)
MariaDB [testdb1]> INSERT INTO t1 VALUES(1);
Query OK, 1 row affected (0.00 sec)
/ 2号master
MariaDB [(none)]> INSERT INTO testdb1.t1 VALUES(2);
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> SELECT * FROM testdb1.t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
/ 1号MASTER查询
MariaDB [(none)]> SELECT * FROM testdb1.t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
/ 2个节点同时插入数据
MariaDB [(none)]> INSERT INTO testdb1.t1 VALUES(3);
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SELECT * FROM testdb1.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 3 |
+------+
4 rows in set (0.00 sec)
/ 两个窗口同时创建数据库冲突
MariaDB [testdb1]> CREATE DATABASE testdb2;
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb1]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.20.54.2
Master_User: repliuser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 723
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 915
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1007
Last_Error: Error 'Can't create database 'testdb2'; database exists' on query. Default database: 'testdb2'. Query: 'CREATE DATABASE testdb2'
Skip_Counter: 0
Exec_Master_Log_Pos: 629
Relay_Log_Space: 1305
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'testdb2'; database exists' on query. Default database: 'testdb2'. Query: 'CREATE DATABASE testdb2'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
1 row in set (0.00 sec)
主主复制全部要点实现完成
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
