MYSQL主从复制是运维工程师、数据库管理员必备的知识。

MySQL单台和多台服务器对比

mysql单台服务器缺点

单台服务器如果磁盘出现问题的话，数据库数据就会丢失
单台服务器的备份，如果把数据文件备份到本地的话，难以自动定期备份
单台服务器无法应对高并发的读，只有单台的话，读写都在同一台，数据压力大

mysql单台服务器优点
数据不会出现不一致的现象
维护简单

推荐:如果确实只有单台服务器的话，可考虑采用单台服务器 + 云快照或者云存储

mysql多台服务器实现主从复制缺点
复制出问题或者写量比较大的时候，数据可能会不一致
主从复制维护比较麻烦

主从复制

MySQL主从复制原理

首先放几张Mysql主从复制的原理图

架构图

image-20230710141630676

架构详解

Mysql的主从复制中主要有三个线程：master（binlog dump thread）、slave（I/O thread 、SQL thread），Master一条线程和Slave中的两条线程。

master（binlog dump thread）主要负责Master库中有数据更新的时候，会按照binlog格式，将更新的事件类型写入到主库的binlog文件中。并且，Master会创建log dump线程通知Slave主库中存在数据更新，这就是为什么主库的binlog日志一定要开启的原因。

I/O thread线程在Slave中创建，该线程用于请求Master，Master会返回binlog的名称以及当前数据更新的位置、binlog文件位置的副本。然后，将binlog保存在 「relay log（中继日志）」 中，中继日志也是记录数据更新的信息。

SQL线程也是在Slave中创建的，当Slave检测到中继日志有更新，就会将更新的内容同步到Slave数据库中，这样就保证了主从的数据的同步。

概括的说就是:

  • 从库会生成两个线程，一个I/O线程，一个SQL线程
  • I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中
  • SQL线程会读取relay-log文件中的日志，并解析成sql语句注意执行

注意事项

  • 主库的binlog日志一定要开启（通常为了数据安全，slave也会开启）
  • master和slave最好同一版本，如果不满足，保证master版本高于slave
  • master和slave的时间需要同步

以上就是主从复制的过程，当然，主从复制的过程有不同的策略方式进行数据的同步，主要包含以下几种：

  1. 「同步策略」：Master会等待所有的Slave都回应后才会提交，这个主从的同步的性能会严重的影响。
  2. 「半同步策略」：Master至少会等待一个Slave回应后提交。
  3. 「异步策略」：Master不用等待Slave回应就可以提交。
  4. 「延迟策略」：Slave要落后于Master指定的时间。

MySQL主从形式

  • 一主一从
  • 主主复制(互为主从)
  • 一主多从
  • 多主一从
  • 联级复制
image-20230710151720635 image-20230710151756109 image-20230710151958891 image-20230710152045725 image-20230710152159280

主从复制搭建

步奏：

  1. 服务器准备
  2. 主库配置
  3. 从库配置

知识储备:mysql的安装配置（提前搭建好，记得修改远程访问权限）

服务器准备

都安装好MySQL

服务器1是192.168.2.3(master)
服务器2是192.168.2.6(slave)

开放3306端口

1
2
firewall-cmd --permanent --zone=public --add-port=3306/tcp
firewall-cmd --reload

主库配置Master

  1. 配置文件修改

vim /etc/my.cnf

注意这个要在[mysqld]分组下面

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 添加以下内容

# 注意这个要在[mysqld]分组下面
# 服务ID，这个是id唯一的
server-id=1
# 是否只读，1是只读，0是读写
read-only=0


# 可选项目
# 开启二进制日志
log-bin=master-bin
# 开启二进制日志索引
log-bin-index = master-bin.index

# 不需要同步的数据库
# binlog-ignore-db=xx数据库
# 指定同步的数据库
# binlog-do-db=xx数据库

server-id #binlog配置需要给mysql服务器设置id用来标识mysql服务器，每个mysql服务器都有不一样的id。从1开始标识

log-bin #开启binlog，mysql主服务器都需要开启

log-bin-index #binlog存放到文件里面，index里面存放着binlog的文件名

  1. 数据目录(目录看配置文件，可以自定义)
    创建数据目录:mkdir -pv /data/mysql
    更改所有者:chown mysql:mysql /data/mysql/

  2. 重启service mysqld restart

  3. 创建远程连接账号，并赋予主从复制权限
    先登录MySQL
    创建用户

    1
    2
    3
    4
    5
    # 创建itcast用户和密码，此用户可以在任意主机
    create user 'itcast'@'%' identified with mysql_native_password by 'daokunpwd'; 
    # 为'itcast'@'%'分配主从复制权限
    grant replication slave on *.* to 'itcast'@'%';
    flush privileges;

  4. 查看二进制日志坐标

    1
    show master status;
    image-20230710203930939

    file:从哪个日志文件开始推送

    position:哪个位置开始推送

    binlog_ignore_db:指不需要同步的数据库

从库配置

  1. 配置文件修改

vim /etc/my.cnf

1
2
3
4
# 服务ID，这个是id唯一的
server-id=2
# 是否只读，1是只读，0是读写
read-only=1
  1. 数据目录(可选项)
    创建数据目录:mkdir -pv /data/mysql
    更改所有者:chown mysql:mysql /data/mysql/
  2. 重启service mysqld restart

到这里基本上两个数据库各自就差不多了。但是现在两个数据仍然是独立的，得把他们关联起来

关联数据库

登录从库！从库！

关联语法(8.0.23的语法)

1
change replication source to source_host='xxx.xxx',source_user='xxx',source_password='xxx',source_log_file='xxx',source_log_pos='xxx';

关联语法(8.0.23之前的语法)

1
change master to master_host='IP地址',master_user='xxx',master_password='xxx',master_log_file='xxx',master_log_pos=xxx;

同步语法

1
2
3
4
# 8.0.22之后版本
start replica;
# 8.0.22之前版本
start slave;

实践(我的是mysql 8.0.17)

  1. 关联

    1
    2
    change master to master_host='192.168.2.3',master_user='itcast',master_password='daokunpwd',master_log_file='binlog.000010',master_log_pos=996;

  2. 开启同步

    1
    start slave;

做好之后可以查看状态

1
2
3
4
# 8.0.22之后版本
show replica status\G;
# 8.0.22之前版本
show slave status\G;
image-20230710210621012

翻车了，出了些问题，我这个问题是因为slave服务器是复制而来的。matser和slave的uuid一样，修改一下，然后重新开启架构或者重启。uuid在auto.cnf文件注意uuid:位数不能多也不能少！

那么auto.cnf在哪里呢？两种办法找:

  1. 这个文件的位置查询:find -name auto.cnf
  2. 进入mysql：show variables like 'datadir';

在MySQL中，输入select uuid();生成新的uuid，然后去auto.cnf修改

image-20230710211207934

分别在master和slave的MySQL查看UUID:show variables like '%server_uuid%';

验证有没有改变

有改变的话，在slave:

1
2
stop slave;
start slave;

如果不是这个问题，还有可能是binlog.xxxx的位置或者position不对。

或者是server_id相同，这个也是很有可能的，你看你的日志报错是什么

测试

master查看状态show master status\G;

image-20230710220915168

slave查看状态:show slave status\G;

image-20230710220853384

现在我们在master创建库和插入数据

1
2
3
4
5
6
7
8
create database db01;
use db01;
create table user(
    id int(11) primary key not null auto_increment,
    name varchar(50) not null,
    sex varchar(5)
)engine=innodb default charset=utf8mb4;
insert into user(name,sex) values('daokun','man'),('afu','man'),('yingbaby','woman');
image-20230710221939756

其他问题

主从复制之主库主机名问题

mysql主服务器的正确配置需要指定log-bin、log-bin-index

1
2
3
4
5
server-id = 1

log-bin=master-bin

log-bin-index = master-bin.index

主机名改变后，即使mysql重启了，主从同步是能够照常进行的。

但是:如果log-bin、log-bin-index没有指定，主机名改变之后，重启，主从同步会不正常。

如不指定的话，binlog文件名就会依赖于主机名。

主机名改变会导致binlog文件名改变，binlog文件名改变带来的影响？

答：主从同步会出问题，多台从库的话，所有从库都会出问题

主从复制之主库主机名问题

mysql从库正确的配置需要指定relay-log、relay-log-index

1
2
3
server-id = 2
relay-log = relay-log
relay-log-index = relay-log.index

如果是使用正确的配置，从库重启的话，主从同步能够照常进行。

没指定relay-log、relay-log-index

默认依赖于主机名，主机名改变，有发生mysql重启，重启后主从同步失败

Last_SQL_Errno: 1872

Last_SQL_Error: Slave failed to initialize relay log info structure from the repository

解决方案

需要记录当前主从同步的位置信息，一定得记录，不然同步的数据就会不全

Master_Log_File: shijiange1-bin.000002

Read_Master_Log_Pos: 32922

1
2
stop slave; #关闭主从同步
reset slave; #从库重置
1
2
3
4
5
6
7
change master to 
 master_host='192.168.237.128',
 master_port=3306,
 master_user='replication', 
 master_password='shijiange',
 master_log_file='shijiange1-bin.000002',
 master_log_pos=32922;
1
start slave;

记得指定relay-log、relay-log-index

基于GTID的主从复制实践

阿里云的RDS目前已经使用GTID。

GTID介绍

  • MySQL5.6往后版本支持的新特性
  • GTID Global Transaction Identified
    • 全局事务ID
  • GTID构成
    • server_uuid+事务ID
  • 在配置主从的时候不再需要自行去找二进制日志文件位置

相对使用binlog+位置的方法来说:

  1. gtid让配置主从更加方便
  2. 从提升为主时比较方便

说明：

主库192.168.2.4

从库192.168.2.5

主库配置

在my.cnf添加以下内容

1
2
3
4
5
6
7
8
9
10
11
# 注意要在[mysqld]下面
#server_id=10 # 你有就不配置，没有就配置
log_bin=master
gtid_mode=on	#启用gtid
enforce_gtid_consistency=true	#强制gtid一致性
log-slave-updates=1
log-bin=master-bin
log-bin-index = master-bin.index
relay-log = relay-log
relay-log-index = relay-log.index
binlog_format=row

在主服务器创建从服务器登录用户，为用户授权，刷新

1
2
3
create user 'daokun'@'%' identified with mysql_native_password by 'daokunpwd'; 
grant replication slave on *.* to "daokun"@"%";
flush privileges;

从库配置

create user ‘itcast‘@’%’ identified with mysql_native_password by ‘daokunpwd’;

在my.cnf添加以下内容

1
2
3
4
5
6
7
8
9
10
# 注意要在[mysqld]下面
log_bin=master
gtid_mode=on	#启用gtid
enforce_gtid_consistency=true	#强制gtid一致性
log-slave-updates=1
log-bin=master-bin
log-bin-index = master-bin.index
relay-log = relay-log
relay-log-index = relay-log.index
binlog_format=row

从库命令

1
2
3
4
5
6
7
8
change master to 
  master_host='192.168.2.4', 
  master_port=3306,
  master_user='daokun', 
  master_password='daokunpwd', 
  master_auto_position = 1;

报错

image-20230711141052510

查看是否关闭

1
2
3
show global variables like 'gtid_mode';
# 如果关闭，设置以下
set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
image-20230711141214511 
1
start slave;

查看状态

1
show slave status\G;

测试

主库插入数据

1
2
3
4
5
6
7
8
create database db02;
use db02;
create table afu_happy(
    id int(11) primary key not null auto_increment,
    name varchar(50) not null,
    mysql_grade varchar(5)
)engine=innodb default charset=utf8mb4;
insert into afu_happy(name,mysql_grade) values('daokun','93'),('afu','90'),('yingerBaby','98');
image-20230711142001493

面试相关

❝Mysql主从有什么优点？为什么要选择主从？❞

  1. 高性能方面：主从复制通过水平扩展的方式，解决了原来单点故障的问题，并且原来的并发都集中到了一台Mysql服务器中，现在将单点负载分散到了多台机器上，实现读写分离，不会因为写操作过长锁表而导致读服务不能进行的问题，提高了服务器的整体性能。
  2. 可靠性方面：主从在对外提供服务的时候，若是主库挂了，会有通过主从切换，选择其中的一台Slave作为Master；若是Slave挂了，还有其它的Slave提供读服务，提高了系统的可靠性和稳定性。

❝若是主从复制，达到了写性能的瓶颈，你是怎么解决的呢？❞

主从模式对于写少读多的场景确实非常大的优势，但是总会写操作达到瓶颈的时候，导致性能提不上去。

这时候可以在设计上进行解决采用分库分表的形式，对于业务数据比较大的数据库可以采用分表，使得数据表的存储的数据量达到一个合理的状态。

也可以采用分库，按照业务进行划分，这样对于单点的写，就会分成多点的写，性能方面也就会大大提高。

❝主从复制的过程有数据延迟怎么办？导致Slave被读取到的数据并不是最新数据。❞

主从复制有不同的复制策略，对于不同的场景的适应性也不同，对于数据的实时性要求很高，要求强一致性，可以采用同步复制策略，但是这样就会性能就会大打折扣。

若是主从复制采用异步复制，要求数据最终一致性，性能方面也会好很多。只能说，对于数据延迟的解决方案没有最好的方案，就看你的业务场景中哪种方案使比较适合的。

读写分离

image-20230710141456139

读写分离是基于主从同步的，上面的知识不会，这边就先不要看了。

简而言之，读写分离就是让写操作让master去做，读操作交给slave。但是这个配置实在是太麻烦了。

MyCat实现读写分离

可以使用MyCat

image-20230710223332874

读写分离和负载均衡由MyCat的schema.xml文件datahost标签的balance属性控制。

ps:累了累了

这有一个大哥写的很好:Mysql+Mycat实现数据库主从同步与读写分离

Atlas实现读写分离

atlas为mysql数据库的中间件，可以实现mysql的读写分离

程序只需要连接atlas入口，无需读写单独指定

程序 -> atlas ->数据库(主库、读库)

主库+从库 给予atlas读写的权限

1
2
grant all privileges on *.* to 'daokun'@'%';
flush privileges;

atlas连接数据的用户是daokun

Atlas下载地址https://github.com/Qihoo360/Atlas/releases

下载tar.gz版本去二进制安装，当然也可以yum安装，rpm安装

Atlas的rpm安装

1
2
3
4
wegt https://github.com/Qihoo360/Atlas/releases/download/sharding-1.0.1/Atlas-sharding_1.0.1-el6.x86_64.rpm
rpm -i Atlas-sharding_1.0.1-el6.x86_64.rpm
# 进入安装目录
cd /usr/local/mysql-proxy

修改配置文件

进入了/usr/local/mysql-proxy/conf

mv test.cnf test.cnf.bak

vim test.cnf

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
[mysql-proxy]
 
#带#号的为非必需的配置项目
 
#管理接口的用户名
admin-username = daokun
 
#管理接口的密码
admin-password = daokunpwd
 
#Atlas后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔
proxy-backend-addresses = 192.168.2.4:3306
 
#Atlas后端连接的MySQL从库的IP和端口，@后面的数字代表权重，用来作负载均衡，若省略则默认为1，可设置多项，用逗号分隔
#proxy-read-only-backend-addresses = 127.0.0.1:3305@1
 
#用户名与其对应的加密过的MySQL密码，密码使用PREFIX/bin目录下的加密程序encrypt加>密，将其替换为你的MySQL的用户名和加密密码！
# 我的路径是使用/usr/local/mysql-proxy/bin/encrypt daokunpwd命令
pwds = daokun:QNdwApWplE7mTh762ogNww==
 
#设置Atlas的运行方式，设为true时为守护进程方式，设为false时为前台方式，一般开发>调试时设为false，线上运行时设为true,true后面不能有空格。
daemon = true
 
#设置Atlas的运行方式，设为true时Atlas会启动两个进程，一个为monitor，一个为worker，monitor在worker意外退出后会自动将其重启，设为false时只有worker，没有monitor，>一般开发调试时设为false，线上运行时设为true,true后面不能有空格。
keepalive = true
 
#工作线程数，对Atlas的性能有很大影响，可根据情况适当设置
event-threads = 8
 
#日志级别，分为message、warning、critical、error、debug五个级别
log-level = message
 
#日志存放的路径
log-path = /usr/local/mysql-proxy/log
 
#SQL日志的开关，可设置为OFF、ON、REALTIME，OFF代表不记录SQL日志，ON代表记录SQL日志，REALTIME代表记录SQL日志且实时写入磁盘，默认为OFF
#sql-log = OFF
 
#慢日志输出设置。当设置了该参数时，则日志只输出执行时间超过sql-log-slow（单位：ms)的日志记录。不设置该参数则输出全部日志。
#sql-log-slow = 10
 
#实例名称，用于同一台机器上多个Atlas实例间的区分
#instance = test
 
#Atlas监听的工作接口IP和端口
proxy-address = 0.0.0.0:1234
 
#Atlas监听的管理接口IP和端口
admin-address = 0.0.0.0:2345
 
#分表设置，此例中person为库名，mt为表名，id为分表字段，3为子表数量，可设置多项，以逗号分隔，若不分表则不需要设置该项
#tables = person.mt.id.3
 
#默认字符集，设置该项后客户端不再需要执行SET NAMES语句
#charset = utf8
 
#允许连接Atlas的客户端的IP，可以是精确IP，也可以是IP段，以逗号分隔，若不设置该项则允许所有IP连接，否则只允许列表中的IP连接
#client-ips = 127.0.0.1, 192.168.1
 
#Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP)，若有LVS且设置了client-ips则此项必须设置，否则可以不设置
#lvs-ips = 192.168.1.1

image-20230711151809817

命令

1
2
3
/usr/local/mysql-proxy/bin/mysql-proxyd test start # 启动
/usr/local/mysql-proxy/bin/mysql-proxyd test restart
/usr/local/mysql-proxy/bin/mysql-proxyd test stop

启动之后，检查有没有在监听

1
netstat -tulnp |grep mysql-proxy

安装教程来源:安装–csdn

更多内容:Mysql之读写分离架构-Atlas–腾讯云社区