安装mysql
环境说明
- CentOS7、mysql5.7
mysql服务器
- master
host
:127.0.0.1port
:3306 - slave 1
host
:127.0.0.1port
:3307 - slave 2
host
:127.0.0.1port
:3308
- master
主从配置
- 原理
① 当master节点接收到一个用户写请求时,这个写请求可能是增删改操作,此时会把写请求的操作都记录到binlog日志中。
② 首先slave节点通过配置信息连接到master节点上,当slave节点连接到master节点上时,master节点会为每一个slave节点分别创建一个binlog dump线程,用于向每个slave节点发送binlog日志。
③ 此时,binlog dump线程会读取master节点上的binlog日志,然后将binlog日志发送给slave节点上的I/O线程。
④ slave几点上的I/O线程接收到binlog日之后,会将文件名、路径、密码等信息保存到master.info中,会将binlog日志先写入到本地的relaylog中,relaylog中就保存了master的binlog日志。
⑤ 最后,slave节点上的SQL线程会读取relaylog中的biinlog日志,将其解析成具体的增删改操作,把这些在master节点上进行过的操作,重新在slave节点上也重做一遍,打到数据还原的效果,这样就可以保证master节点和slave节点的数据一致性了。
- master配置
# 查看当前的二进制日志记录状态
root@(localhost) 01:23:33>SHOW VARIABLES like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
# 创建用于从服务器连接的用户
# 刷新权限
FLUSH PRIVILEGES;
# 创建用户
CREATE USER 'synchronous'@'%' IDENTIFIED by 'synchronous';
# 授予'synchronous'用户复制从服务器的权限
GRANT REPLICATION SLAVE ON *.* TO 'synchronous'@'%' IDENTIFIED WITH mysql_native_password BY 'synchronous';
# 刷新权限
FLUSH PRIVILEGES;
# 查看主服务器的状态,包括二进制日志文件名和位置
root@(localhost) 01:23:44>show MASTER status;
+-----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| mysql-3306-bin.000003 | 1757 | | | |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- slave 1 从服务器
# 添加主服务器信息
CHANGE MASTER TO
MASTER_HOST='127.0.0.1',
MASTER_PORT=3306,
MASTER_USER="synchronous",
MASTER_PASSWORD="synchronous",
MASTER_LOG_FILE="mysql-3306-bin.000003",
MASTER_LOG_POS=1757;
# 开启从服务器
start SLAVE;
# 查看从服务器状态
root@(localhost) 01:41:00>SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: synchronous
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-3306-bin.000003
Read_Master_Log_Pos: 1757
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 938
Relay_Master_Log_File: mysql-3306-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 1757
Relay_Log_Space: 1149
.....................................................................................
Master_Server_Id: 3306
Master_UUID: fcbfbd72-e0fc-11ee-8997-5254008afee6
Master_Info_File: /usr/local/mysql/data/3307/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
.....................................................................................
1 row in set (0.00 sec)
ERROR:
No query specified
- slave 2 从服务器
# 添加主服务器信息
CHANGE MASTER TO
MASTER_HOST='127.0.0.1',
MASTER_PORT=3306,
MASTER_USER="synchronous",
MASTER_PASSWORD="synchronous",
MASTER_LOG_FILE="mysql-3306-bin.000003",
MASTER_LOG_POS=1757;
# 开启从服务器
start SLAVE;
# 查看从服务器状态
root@(localhost) 01:41:00>SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: synchronous
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-3306-bin.000003
Read_Master_Log_Pos: 1757
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 938
Relay_Master_Log_File: mysql-3306-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 1757
Relay_Log_Space: 1149
.....................................................................................
Master_Server_Id: 3306
Master_UUID: fcbfbd72-e0fc-11ee-8997-5254008afee6
Master_Info_File: /usr/local/mysql/data/3308/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
.....................................................................................
1 row in set (0.00 sec)
ERROR:
No query specified
测试主从
# 主服务器创建test库,test表
CREATE DATABASE test;
USE test;
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`username` char(30) NOT NULL COMMENT '姓名',
`age` int(11) DEFAULT NULL COMMENT '昵称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='测试表';
# slave1、slave2 查看
root@(localhost) 01:41:10>SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
root@(none) 01:49:25>USE test;
Database changed
root@test 01:49:44>SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
相关文章