MySQL 主从复制原理与实践详解

时间:2022-11-15 19:52:44

本文实例讲述了mysql 主从复制原理与实践。分享给大家供大家参考,具体如下:

简介

mysql 的主从复制又叫 replication、ab 复制。至少需要两个 mysql 服务(可以是同一台机器,也可以是不同机器之间进行)。

比如a服务器做主服务器,b服务器做从服务器,在a服务器上进行数据的更新,通过 binlog 日志记录同步到b服务器上,并重新执行同步过来的 binlog 数据,从而达到两台服务器数据一致。

mysql 数据库的主从复制方案,与使用 scp/rsync 等命令进行的文件级别复制类似,都是数据的远程传输。

只不过 mysql 的主从复制是其自带的功能,无需借助第三方工具,而且mysql的主从复制并不是数据库磁盘上的文件直接拷贝,而是通过逻辑的 binlog 日志复制到要同步的服务器本地,然后由本地的线程读取日志里面的 sql 语句,重新应用到 mysql 数据库中。

作用

1. 可以实时灾备,用于故障切换;

2. 读写分离,提供查询服务,实现负载均衡;

3. 数据热备,避免影响业务。

原理

MySQL 主从复制原理与实践详解

1、主服务器mysql服务将所有的写操作记录在 binlog 日志中,并生成 log dump 线程,将 binlog 日志传给从服务器mysql服务的 i/o 线程。

2、从服务器mysql服务生成两个线程,一个是 i/o 线程,另一个是 sql 线程。

3、从库 i/o 线程去请求主库的 binlog 日志,并将 binlog 日志中的文件写入 relaylog(中继日志)中。

4、从库的 sql 线程会读取 relaylog 中的内容,并解析成具体的操作,来实现主从的操作一致,达到最终两个数据库数据一致的目的。

?
1
2
3
4
5
6
注意点:
- 主从复制是异步的逻辑的 sql 语句级的复制;
- 复制时,主库有一个 i/o 线程,从库有两个线程,及 i/o 和 sql 线程;
- 实现主从复制的必要条件是主库要开启记录 binlog 的功能;
- 作为复制的所有 mysql 节点的 server-id 都不能相同;
- binlog 文件只记录对数据内容有更改的 sql 语句,不记录任何查询语句。

形式

一主一从

MySQL 主从复制原理与实践详解

主主复制

MySQL 主从复制原理与实践详解

一主多从

MySQL 主从复制原理与实践详解

多主一从(5.7后开始支持)

MySQL 主从复制原理与实践详解

联级复制

MySQL 主从复制原理与实践详解

实践

需求

实现一主一从复制模式,同一台主机两个mysql实例

环境

?
1
2
3
4
5
6
mac:10.15.1
docker:2.0.0.3//采取docker安装mysql主要是管理维护方便、独立ip、启动秒级
mysql-master:5.7.29//主服务器
mysql-master ip:172.17.0.3
mysql-slave:5.7.29//从服务器
mysql-slave ip:172.17.0.4

步骤

第一步:准备好两台mysql服务器
mysql-master(主服务器):
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
1. 创建目录结构:
master/conf、master/data、master/logs
2. 启动运行实例容器:
docker run --name mysql-master
> -p 3310:3306
> -v ~/docker/master/conf:/etc/mysql/conf.d
> -v ~/docker/master/data:/var/lib/mysql
> -v ~/docker/master/logs:/var/log/mysql
> -e mysql_root_password=123456
> -d mysql:5.7
3. 进入容器
docker exec -it mysql-master bash
4. 登录mysql
mysql -uroot -p
mysql-slave(从服务器):
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
1. 创建目录结构:
slave/conf、slave/data、slave/logs
2. 启动运行实例容器:
docker run --name mysql-slave
> -p 3310:3306
> -v ~/docker/slave/conf:/etc/mysql/conf.d
> -v ~/docker/slave/data:/var/lib/mysql
> -v ~/docker/slave/logs:/var/log/mysql
> -e mysql_root_password=123456
> -d mysql:5.7
3. 进入容器
docker exec -it mysql-slave bash
4. 登录mysql
mysql -uroot -p
第二步:配置文件(my.cnf)修改
?
1
2
3
4
5
6
7
8
9
10
11
主服务器:
 
[mysqld]
port = 3306
server-id = 1
#要同步的数据库
binlog-do-db = school
#要生成二进制日志文件 主服务器一定要开启
log-bin = mysql-bin
 
重启mysql: docker restart mysql-master
?
1
2
3
4
5
6
7
8
9
10
11
从服务器:
 
[mysqld]
port = 3306
server-id = 2
#要同步的数据库
binlog-do-db = school
#要生成二进制日志文件(从服务器可选)
log-bin = mysql-bin
 
重启mysql: docker restart mysql-slave
第三步:创建主服务器复制用户及相关权限
?
1
2
3
4
create user 'slave'@'%' identified by '123456';//创建用户
grant replication slave,replication client on *.* to 'slave'@'%';//设置用户权限
flush privileges;//刷新权限
show grants for 'slave'@'%';//查看用户权限
第四步:数据备份同步
?
1
2
3
4
5
6
7
8
9
1. 登录master,执行锁表操作
mysql -uroot -p
flush tables with read lock;
2. 将master中需要同步的db的数据dump出来
mysqldump -uroot -p school > school.dump
3. 将数据导入slave
mysql -uroot -h172.17.0.4 -p school < school.dump
4. 解锁master
unlock tables;
第五步:主服务器复制状态
?
1
2
3
4
5
6
7
8
9
10
1. 创建新数据表及增加数据
create table user( id int(10) auto_increment, name varchar(30), primary key (id) )charset=utf8mb4;
insert into user(name) values(222);
2. 主服务器 binlog 记录状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| file | position | binlog_do_db | binlog_ignore_db | executed_gtid_set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 769 | school | | |
+------------------+----------+--------------+------------------+-------------------+

MySQL 主从复制原理与实践详解

第六步:从服务器开始复制
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
1. 连接主服务器及设置复制的起始节点
mysql> change master to master_host='172.17.0.3',
-> master_port=3306,
-> master_user='slave',
-> master_password='123456',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=769;
2. 开始复制
start slave;
3. 查看复制状态
mysql> show slave status \g
*************************** 1. row ***************************
slave_io_state: waiting for master to send event
master_host: 172.17.0.3
master_user: slave
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000001
read_master_log_pos: 961
relay_log_file: 87dc5224655d-relay-bin.000003
relay_log_pos: 320
relay_master_log_file: mysql-bin.000001
slave_io_running: yes //表示i/o线程读取成功
slave_sql_running: yes //表示sql线程执行成功
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: 961
relay_log_space: 892
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: 1
master_uuid: 45540733-4e0c-11ea-b0ac-0242ac110003
master_info_file: /var/lib/mysql/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:
4. 查看数据表数据
mysql> show create table user\g
*************************** 1. row ***************************
table: user
create table: create table `user` (
`id` int(10) not null auto_increment,
`name` varchar(30) default null,
primary key (`id`)
) engine=innodb auto_increment=2 default charset=utf8mb4
 
mysql> select * from user;
+----+------+
| id | name |
+----+------+
| 1 | 222 |
+----+------+
?
1
2
3
4
5
6
7
复制的控制相关命令:
stop salve //停止slave连路
reset slave //重置slave连路
start slave //开启slave连路
stop master //停止master连路
reset master //重置master连路
start master //开启master连路
第七步:主从服务器的进程查看
mysql-master:

MySQL 主从复制原理与实践详解

mysql-slave:

MySQL 主从复制原理与实践详解

希望本文所述对大家MySQL数据库计有所帮助。

原文链接:https://segmentfault.com/a/1190000021749728