四:MySQL 主从复制实验
实验步骤:
- 搭建主从复制结构,node103 为 MASTER,node104 为 SLAVE;
- 搭建完成后查看两个节点的连接情况;
- MASTER 节点做一些数据修改操作,验证 SLAVE 节点是否同步;
- 模拟 SLAVE 节点宕机并恢复后,主从复制的情况;
- 删除主从,以进行后续实验;
4.1:搭建 MySQL 主从
为达到初始状态,先清空node103 上之前的测试数据库:
MariaDB [(none)]> DROP DATABASE sample_db;
采用配置文件模块化的方式,以方便之后的复制结构更改;
4.1.1:MASTER 节点
编辑配置文件
- 注释 my.cnf 中的相关配置:
[root@node103 ~]# vim /etc/my.cnf
#server-id=103
#log-bin=/data/mysql_logs/mysql-binlog
#relay-log=/data/mysql_logs/mysql-relay
- 另起一个配置文件 repl.cnf,配置 MASTER 节点相关参数:
[root@node103 ~]# vim /etc/my.cnf.d/repl.cnf
[mysqld]
server_id=103
log-bin=/data/mysql_logs/mysql-master
sync_binlog=1
MASTER 节点只需开启二进制日志;
sync_binlog=1
,表示每次事务提交都刷写磁盘上的二进制日志文件,保证了 MASTER 节点上事务的完整性;但在事务繁忙时会损耗一定性能;
重启 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-master.000001 | 245 | | |
+---------------------+----------+--------------+------------------+
4.1.2:SLAVE 节点
编辑配置文件
- 注释 my.cnf 中的相关配置:
#server-id=104
#log-bin=/data/mysql_logs/mysql-binlog
#relay-log=/data/mysql_logs/mysql-relay
- 另起一个配置文件 repl.cnf,配置 SLAVE 节点相关参数:
[root@node104 ~]# vim /etc/my.cnf.d/repl.cnf
[mysqld]
server_id=104
relay-log=/data/mysql_logs/mysql-slave
read_only=ON
sync_master_info=1
sync_relay_log_info=1
SLAVE 节点仅需开启中继日志;并且设为只读;
sync_master_info=1
、sync_relay_log_info=1
,表示每次事务提交都刷新数据目录下的 master.info 和 relay-log.info 文件,保证了主从节点事务的一致性;
重启 mariadb
[root@node104 ~]# systemctl restart mariadb
为 SLAVE 指定 MASTER
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.1.103',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-master.000001',MASTER_LOG_POS=245;
启动复制线程
MariaDB [(none)]> START SLAVE;
4.1.3:查看主从建立情况
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-master.000001
Read_Master_Log_Pos: 245
Relay_Log_File: mysql-slave.000003
Relay_Log_Pos: 532
Relay_Master_Log_File: mysql-master.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: 1109
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
4.2:测试主从复制
4.2.1:数据添加同步测试
- MASTER 节点创建数据库和表,添加相应数据;
MariaDB [(none)]> CREATE DATABASE sample_db;
MariaDB [(none)]> USE sample_db;
MariaDB [sample_db]> CREATE TABLE tbl1(id INT UNSIGNED NOT NULL, name VARCHAR(100));
MariaDB [sample_db]> INSERT tbl1 (id,name) VALUES (1,'user1'),(2,'user2'),(3,'user3');
MariaDB [sample_db]> select * from tbl1;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
| 2 | user2 |
| 3 | user3 |
+----+-------+
- SLAVE 节点验证同步结果:
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sample_db |
+--------------------+
MariaDB [(none)]> SELECT * FROM sample_db.tbl1;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
| 2 | user2 |
| 3 | user3 |
+----+-------+
- SLAVE 节点查看同步状态:
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-master.000001
Read_Master_Log_Pos: 1026
Relay_Log_File: mysql-slave.000003
Relay_Log_Pos: 1313
Relay_Master_Log_File: mysql-master.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: 1026
Relay_Log_Space: 1890
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
当前同步到的位置为 mysql-master.000001 的 1026;
- 查看 MASTER 状态:
MariaDB [sample_db]> SHOW MASTER STATUS;
+---------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-master.000001 | 1026 | | |
+---------------------+----------+--------------+------------------+
和 SLAVE 显示的同步位置一致;
4.2.2:数据删除同步测试
- MASTER 节点删除一条记录:
MariaDB [sample_db]> DELETE FROM tbl1 WHERE id=1;
- SLAVE 节点验证:
MariaDB [(none)]> SELECT * FROM sample_db.tbl1;
+----+-------+
| id | name |
+----+-------+
| 2 | user2 |
| 3 | user3 |
+----+-------+
4.2.3:模拟 SLAVE 宕机
- 关闭 SLAVE 节点的 mariadb:
[root@node104 ~]# systemctl stop mariadb
- MASTER 节点添加数据:
MariaDB [(none)]> INSERT INTO sample_db.tbl1 (id,name) VALUES (4,'user4'),(5,'user5');
MariaDB [(none)]> SELECT * FROM sample_db.tbl1;
+----+-------+
| id | name |
+----+-------+
| 2 | user2 |
| 3 | user3 |
| 4 | user4 |
| 5 | user5 |
+----+-------+
- 启动 SLAVE 节点的 mariadb:
[root@node104 ~]# systemctl start mariadb
- 查看复制进程状态:
MariaDB [(none)]> SHOW SLAVE STATUS\G
……
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……
- 验证数据同步:
MariaDB [(none)]> SELECT * FROM sample_db.tbl1;
+----+-------+
| id | name |
+----+-------+
| 2 | user2 |
| 3 | user3 |
| 4 | user4 |
| 5 | user5 |
+----+-------+
说明 SLAVE 节点的复制线程是随 mysql 进程启动的;
如果 SLAVE 宕机时间过长,恢复后会有很多数据等待同步,为加快恢复速度,可以将 MASTER 节点的数据进行备份(备份同时 flush-logs 重新建立一个二进制日志文件,以便记录同步位置),将备份恢复到 SLAVE 节点后,重新建立主从关系;
4.3:MySQL 半同步
MySQL 半同步是建立在主从复制基础上的,主从复制是异步,通过启用半同步插件来实现半同步;
MySQL 复制的同步类型有三种:
异步
主节点本地写入成功即返回 “成功”;半同步
主节点将请求发给从节点,确认从节点已接收请求后即返回 “成功”;MySQL的半同步是指:只有一个从节点是同步的,其它从节点是异步的;
同步
主节点收到写入请求,同步给从节点,待确认从节点写入完成后才返回 “成功”;
4.3.1:安装半同步复制插件
-
semisync_master.so,主节点安装;
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
-
semisync_slave.so,从节点安装;
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
4.3.2:启用半同步复制
- MASTER 节点
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=ON;
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
- SLAVE 节点
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=ON;
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
- 如果从节点的复制线程在启用半同步复制之前是启动的,需要重启其中的 IO_THREAD:
MariaDB [(none)]> STOP SLAVE IO_THREAD;
MariaDB [(none)]> START SLAVE IO_THREAD;
4.3.3:核实半同步连接
- MASTER 节点查看半同步复制的 SLAVE 节点是否已连接:
MariaDB [(none)]> SHOW STATUS LIKE 'rpl_semi_sync_master_clients';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
+------------------------------+-------+
显示已有一个 SLAVE 连接进来;
4.4:删除 MySQL 主从
删除主从,以继续后续实验。
- 停止 SLAVE 复制线程
MariaDB [(none)]> STOP SLAVE;
- 重置 SLAVE 节点
MariaDB [(none)]> RESET SLAVE;
- 重置 MASTER 节点
MariaDB [(none)]> RESET MASTER;
重置 MASTER 后,会创建一个序号为 000001 的空的二进制日志文件:
MariaDB [(none)]> SHOW MASTER STATUS; +---------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---------------------+----------+--------------+------------------+ | mysql-master.000001 | 245 | | | +---------------------+----------+--------------+------------------+