五:MySQL 双主复制实验
实验步骤:
- 搭建 MySQL 双主复制结构,node103 为 MASTER1,node104 为 MASTER2;
- 搭建完成后验证两个节点的连接情况;
- MASTER1 创建测试数据库,并授权给测试用户,MASTER2 使用测试用户连入 MySQL,查看相关数据是否同步正常;
- MASTER2 删除数据,MASTER1 查看是否同步;
- 前端部署 HAProxy 代理两个 MySQL 节点,分别在两个节点上添加一些数据,用前端代理 IP 连入 MySQL,查看数据是否完整;
- 删除 MySQL 双主,以进行后续实验;
5.1:搭建 MySQL 双主
5.1.1:MASTER1 节点
编辑配置文件
- 编辑 repl.cnf:
[root@node103 ~]# vim /etc/my.cnf.d/repl.cnf
[mysqld]
server_id=103
log-bin=/data/mysql_logs/mysql-master1
relay-log=/data/mysql_logs/mysql-slave1
sync_binlog=1
sync_master_info=1
sync_relay_log_info=1
重启 mariadb
[root@node103 ~]# systemctl restart mariadb
授权复制数据的用户
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'172.16.1.%' IDENTIFIED BY '123456';
MariaDB [(none)]> FLUSH PRIVILEGES;
记录二进制日志位置
- 查看当前使用的二进制日志文件,以记录主从复制的起始位置:
MariaDB [(none)]> SHOW MASTER STATUS;
+----------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| mysql-master1.000001 | 490 | | |
+----------------------+----------+--------------+------------------+
5.1.2:MASTER2 节点
编辑配置文件
- 编辑 repl.cnf:
[root@node104 ~]# vim /etc/my.cnf.d/repl.cnf
[mysqld]
server_id=104
log-bin=/data/mysql_logs/mysql-master2
relay-log=/data/mysql_logs/mysql-slave2
sync_binlog=1
sync_master_info=1
sync_relay_log_info=1
重启 mariadb
[root@node104 ~]# systemctl restart mariadb
授权复制数据的用户
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'172.16.1.%' IDENTIFIED BY '123456';
MariaDB [(none)]> FLUSH PRIVILEGES;
记录二进制日志位置
- 查看当前使用的二进制日志文件,以记录主从复制的起始位置:
MariaDB [(none)]> SHOW MASTER STATUS;
+----------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| mysql-master2.000001 | 490 | | |
+----------------------+----------+--------------+------------------+
5.1.3:配置互为主从
- MASTER1
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.1.104',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-master2.000001',MASTER_LOG_POS=490;
MariaDB [(none)]> START SLAVE;
- MASTER2
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.1.103',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-master1.000001',MASTER_LOG_POS=490;
MariaDB [(none)]> START SLAVE;
5.1.4:查看主从建立情况
- MASTER1
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.104
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-master2.000001
Read_Master_Log_Pos: 490
Relay_Log_File: mysql-slave1.000002
Relay_Log_Pos: 533
Relay_Master_Log_File: mysql-master2.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: 490
Relay_Log_Space: 824
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: 104
- MASTER2
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.103
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-master1.000001
Read_Master_Log_Pos: 490
Relay_Log_File: mysql-slave2.000002
Relay_Log_Pos: 533
Relay_Master_Log_File: mysql-master1.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: 490
Relay_Log_Space: 824
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: 103
5.2:测试双主复制
5.2.1:MASTER1 数据添加测试
- MASTER1 创建测试数据库,并授权给测试用户:
MariaDB [(none)]> CREATE DATABASE sample_db;
MariaDB [(none)]> GRANT ALL ON sample_db.* TO 'sample'@'%' IDENTIFIED BY '123456';
MariaDB [(none)]> FLUSH PRIVILEGES;
- MASTER1 在测试数据库创建表,并插入数据:
MariaDB [sample_db]> CREATE TABLE tbl1(id INT UNSIGNED NOT NULL, name VARCHAR(100));
MariaDB [sample_db]> INSERT INTO tbl1 (id,name) VALUES (1,'user1'),(2,'user2');
- MASTER2 用测试用户登录,查看数据:
[root@node104 ~]# mysql -usample -p
Enter password:
MariaDB [(none)]> SELECT * FROM sample_db.tbl1;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
| 2 | user2 |
+----+-------+
5.2.2:MASTER2 数据删除测试
- MASTER2 删除一条数据:
MariaDB [(none)]> DELETE FROM sample_db.tbl1 WHERE id=1;
- MASTER1 验证数据同步:
MariaDB [sample_db]> SELECT * FROM sample_db.tbl1;
+----+-------+
| id | name |
+----+-------+
| 2 | user2 |
+----+-------+
5.3:搭建 HAProxy 代理
使用 node102 作为 HAProxy 代理;
为了方便,直接使用 yum 安装 HAProxy;
5.3.1:安装 HAProxy
[root@node102 ~]# yum install haproxy -y
5.3.2:配置 MySQL 代理
[root@node102 ~]# cp /etc/haproxy/haproxy.cfg{,.bak}
[root@node102 ~]# vim /etc/haproxy/haproxy.cfg
listen mysql
bind 172.16.1.102:3306
mode tcp
balance roundrobin
server 172.16.1.103 172.16.1.103:3306 check inter 3000 fall 3 rise 5
server 172.16.1.104 172.16.1.104:3306 check inter 3000 fall 3 rise 5
5.3.3:启动 HAProxy
[root@node102 ~]# systemctl enable haproxy
[root@node102 ~]# systemctl start haproxy
[root@node102 ~]# ss -tnl | grep 3306
LISTEN 0 3000 172.16.1.102:3306 *:*
5.4:利用代理测试双主数据同步
- MASTER1 插入数据:
MariaDB [sample_db]> INSERT INTO tbl1 (id,name) VALUES (3,'master1');
- MASTER2 插入数据:
MariaDB [sample_db]> INSERT INTO tbl1 (id,name) VALUES (4,'master2');
- 使用 navicat 通过代理 IP 连入 MySQL:
- 查看数据是否完整:
- 通过代理向表中插入一条数据:
- MASTER1 验证:
MariaDB [sample_db]> SELECT * FROM sample_db.tbl1;
+----+---------+
| id | name |
+----+---------+
| 2 | user2 |
| 3 | master1 |
| 4 | master2 |
| 5 | master |
+----+---------+
- MASTER2 验证:
MariaDB [sample_db]> SELECT * FROM tbl1;
+----+---------+
| id | name |
+----+---------+
| 2 | user2 |
| 3 | master1 |
| 4 | master2 |
| 5 | master |
+----+---------+
5.5:删除 MySQL 双主
- 重置 MASTER1 的 SLAVE:
MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> RESET SLAVE;
- 重置 MASTER2 的 SLAVE:
MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> RESET SLAVE;
- 重置 MASTER1 的 MASTER:
MariaDB [(none)]> RESET MASTER;
- 重置 MASTER2 的 MASTER:
MariaDB [(none)]> RESET MASTER;