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;
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)
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)
主要参数项说明
从服务器 sql 线程执行状态
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)
手机扫一扫
移动阅读更方便
你可能感兴趣的文章