MySQL 主从复制一主两从环境配置实战
MySQL 初始化
MySQL 主从复制是指数据可以从一个 MySQL 数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式;从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表.
1. 安装指定版本
yum install -y http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
yum install -y mysql-community*5.7.30-1.el7.x86_64
2. 初始化账号
set global validate_password_policy=0; set global validate_password_length=4;
alter user user() identified by 'root';
update mysql.user set host='%' where user='root';
flush privileges;
Master 端配置
1. 修改 mysqld 配置文件
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
max_connections = 2000
server-id=1
log-bin=mysql-binlog
binlog_format = ROW
binlog_row_image = minimal
; 仅记录数据库 demo01 的二进制日志 binlog
binlog-do-db = demo01
systemctl restart mysqld
2. 创建复制账号
grant replication slave on _._ to 'repl'@'%' identified by '123456';
flush privileges;
3. 查看主库状态
show master status\G;
*************************** 1. row ***************************
File: mysql-binlog.000002
Position: 306
Binlog_Do_DB: demo01
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
Slave 端配置
1. 修改 mysqld 配置文件
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
max_connections = 2000
server-id=2
log-bin=mysql-binlog
binlog_format = ROW
binlog_row_image = minimal
master_info_repository = TABLE
relay_log_info_repository = TABLE
; 当 relay-log 损坏时, 可根据当前slave端sql线程回放的位置, 重新从master上获取日志
relay_log_recovery = 1
; 备注:
; mysql slave端二进制日志没有其它用途，其实可以不必开启从端 binlog
2. 设置主从复制时的元数据(master 同步主机账号及 binlog 文件和位置)
mysql> change master to
master_host="192.168.31.17",
master_port=3306,
master_user="repl",
master_password="123456",
master_log_file="mysql-binlog.000002",
master_log_pos=306;
; 设置从库只读, 并启动slave
mysql> set global super_read_only=1;
mysql> start slave;
3. 查看状态
show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.17
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-binlog.000002
Read_Master_Log_Pos: 306
Relay_Log_File: c8-relay-bin.000002
Relay_Log_Pos: 323
Relay_Master_Log_File: mysql-binlog.000002
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: 306
Relay_Log_Space: 527
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 # slave 端落后于 master 端多少秒
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
Master_UUID: 7aa006de-1b3f-11eb-842d-525400f46c59
Master_Info_File: mysql.slave_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
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
主要参数项说明
- slave io thread 按照 Master_Log_File、Read_Master_Log_Pos 位置读取主库的 binlog，并写入到本地 relay log
- slave sql thread 按照 Relay_Log_File、Relay_Log_Pos 位置进行 realy log 的回放
从服务器 sql 线程执行状态
- Waiting for the next event in relay log 该状态是读取 relay log 之前的初始状态
- Reading event from the relay log 该状态表示此线程已经在 relay log 中读取了一个事件准备执行
- Making temp file 该状态表示此线程正在执行 LOAD_DATA_INFILE 并且正在创建一个临时文件来保存从服务器将要读取的数据
- Slave has read all relay log; waiting for the slave I/O thread to update it 该线程已经处理完了 relay log 中的所有事件，现在正在等待 slave /O 线程更新 relay log 文件
- Waiting for slave mutex on exit 当线程停止的时候会短暂的出现该情况
- updating # 表示正在更新
- Opening tables
- Sending data
验证测试程
Master 端建库建表, 看 slave 端是否同步
master 端
-- 查看 slave 情况
show slave hosts;
show master status\G;
create database demo01;
create database demo02;
use demo01;
create table user(
id int auto_increment,
name varchar(16) not null,
age int(16) not null,
primary key(id)
)engine=innodb charset=utf8;
-- 插入入数据
insert into user(name,age) values('tom',31),('user1', 18),('user2', 16),('user3', 33),('user4', 12);
-- 查看 二进制文件
show binary logs;
+---------------------+-----------+
| Log_name | File_size |
+---------------------+-----------+
| mysql-binlog.000001 | 177 |
| mysql-binlog.000002 | 1079 |
+---------------------+-----------+
2 rows in set (0.00 sec)
-- 查看 二进制事件
show binlog events in 'mysql-binlog.000002';
+---------------------+------+----------------+-----------+-------------+--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------------+------+----------------+-----------+-------------+--------------------------------------+
| mysql-binlog.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.30-log, Binlog ver: 4|
| mysql-binlog.000002 | 123 | Previous_gtids | 1 | 154 | |
| mysql-binlog.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'|
| mysql-binlog.000002 | 219 | Query | 1 | 306 | flush privileges |
| mysql-binlog.000002 | 306 | Anonymous_Gtid | 1 | 371 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'|
| mysql-binlog.000002 | 371 | Query | 1 | 471 | create database demo01|
| mysql-binlog.000002 | 471 | Anonymous_Gtid | 1 | 536 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'|
| mysql-binlog.000002 | 536 | Query | 1 | 748 | use `demo01`; create table user(
id int auto_increment,
name varchar(16) not null,
age int(16) not null,
primary key(id)
)engine=innodb charset=utf8 |
| mysql-binlog.000002 | 748 | Anonymous_Gtid | 1 | 813 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'|
| mysql-binlog.000002 | 813 | Query | 1 | 887 | BEGIN|
| mysql-binlog.000002 | 887 | Table_map | 1 | 940 | table_id: 108 (demo01.user)|
| mysql-binlog.000002 | 940 | Write_rows | 1 | 1048 | table_id: 108 flags: STMT_END_F|
| mysql-binlog.000002 | 1048 | Xid | 1 | 1079 | COMMIT /* xid=24 */|
+---------------------+------+----------------+-----------+-------------+--------------------------------------+
13 rows in set (0.00 sec)
-- 二进制事件位置 Pos 813 ~ 1048 执行的是如下插入操作
insert into user(name,age) values('tom',31),('user1', 18),('user2', 16),('user3', 33),('user4', 12);
slave 端
-- 从端查看是否同步
show databases;
-- 查看指定数据是否同步
use demo01;
select * from user;
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 1 | tom | 31 |
| 2 | user1 | 18 |
| 3 | user2 | 16 |
| 4 | user3 | 33 |
| 5 | user4 | 12 |
+----+-------+-----+
5 rows in set (0.01 sec)
