mysql设置主从数据库的同步

待我称王封你为后i 2023-10-11 16:48 65阅读 0赞

## 1.业务场景 ##

*  场景1：**实现服务器负载均衡**  
    若我们所有的业务代码存于一个服务器上，而这个服务器有的时候回宕机，导致业务停顿，造成影响。这个时候 就需要做高可用，在数据库的处理上如果两个mysql实现高可用，避免单点问题。
 *  场景2：读写分离；master写，其他slave读，这种架构最大问题I/O压力集中在Master上;
 *  场景3：**通过复制实现数据的异地备份**  
    可以定期的将数据从主服务器上复制到从服务器上，这无疑是先了数据的异地备份。在传统的备份体制下，是将数据备份在本地。此时备份 作业与数据库服务器运行在同一台设备上，当备份作业运行时就会影响到服务器的正常运行。有时候会明显的降低服务器的性能。同时，将备份数据存放在本地，也 不是很安全。如硬盘因为电压等原因被损坏或者服务器被失窃，此时由于备份文件仍然存放在硬盘上，数据库管理员无法使用备份文件来恢复数据。这显然会给企业 带来比较大的损失。

## 2.实现的目标 ##

一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务器即可充当主机，也可充当从机。  
一句话表示就是，主数据库做什么，从数据库就跟着做什么。

## 3.原理 ##

我们的主从复制基于Mysql Replication来进行实现

### 3.1 什么是Mysql Replication ###

Replication可以实现将数据从一台数据库服务器（master）复制到一台或多台数据库服务器（slave）

*  默认情况下属于异步复制，无需维持长连接
 *  通过配置，可以复制所有的库或者几个库，甚至库中的一些表
 *  是MySQL内建的，本身自带的

### 3.2 Mysql Replication的原理 ###

简单的说就是master将数据库的改变写入二进制日志，slave同步这些二进制日志，并根据这些二进制日志进行数据操作。  
![在这里插入图片描述][5309a7855439492db2bbddb565a6f060.png_pic_center]

注意：

*  DML表示SQL操作语句如：update, insert，delete
 *  Relay log :中继日志

整体上来说，复制有3个步骤：  
（1） master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）  
（2） slave将master的binary log events拷贝到它的中继日志(relay log)  
（3） slave重做中继日志中的事件，修改salve上的数据。

### 3.3 My Replication的作用 ###

*  Fail Over 故障切换
 *  Backup Server 备份服务，无法对SQL语句执行产生的故障恢复，有限的备份
 *  High Performance 高性能，可以多台slave,实现读写分离  
    ![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center]

### 3.4 MySQL主从复制 ###

**第一步：master记录二进制日志**

> 在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。

**第二步：slave将master的binary log拷贝到它自己的中继日志**

> 首先，slave开始一个工作线程—I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经执行完master产生的所有文件，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

**第三步：SQL slave thread（SQL从线程）处理该过程的最后一步**

> SQL线程从中继日志读取事件，并重新执行其中的事件而更新slave的数据，使其与master中的数据一致。

## 4 My Replication常见方案 ##

#### One master and Muti salve 一主多备（MS） ####

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_17_color_FFFFFF_t_70_g_se_x_16_pic_center]

一般用来做读写分离的，master写，其他slave读，这种架构最大问题I/O压力集中在Master上<多台同步影响IO>

#### M-S-S ####

使用一台slave作为中继，分担Master的压力，slave中继需要开启bin-log,并配置log-slave-updates  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 1]

Slave中继可使用Black-hole存储引擎，不会把数据存储到磁盘，只记录二进制日志。

#### M-M 双主互备（互为主从） ####

每个服务器需要做同样的同步更新，破坏了事物的隔离性和数据的一致性。  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 2]

#### M-M-M ####

监控三台机器互相作对方的master  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 3]

天生的缺陷：复制延迟，slave上同步要慢于master，如果大并发的情况那延迟更严重。  
mysql在5.6已经自身可以实现fail over故障切换

#### One slave Muti master 一从对多主 ####

好处：节省成本，将多个master数据自动化整合  
缺陷：对库和表数据的修改较多

## 5.实现 ##

### 5.1 环境准备 ###

本地安装两个mysql，或者使用虚拟机，或者使用docker安装，需要准备两个mysql，本文使用docker安装  
在docker中先创建两个mysql服务器  
主服务器：3308  
从服务器：3309  
![在这里插入图片描述][0ce54a409b124a0292bb94976d8d3d08.png_pic_center]

[不会创建？点击此处][Link 1]

### 5.2 修改 my.cnf配置文件 ###

*  master的my.cnf配置文件

#mysql master1 config 
    [mysqld]
    server-id = 1        # 节点ID，确保唯一
    
    # log config
    log-bin = mysql-bin     #开启mysql的binlog日志功能
    sync_binlog = 1         #控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
    binlog_format = mixed   #binlog日志格式，mysql默认采用statement，建议使用mixed
    expire_logs_days = 7                           #binlog过期清理时间
    max_binlog_size = 100m                    #binlog每个日志文件大小
    binlog_cache_size = 4m                        #binlog缓存大小
    max_binlog_cache_size= 512m              #最大binlog缓存大
    binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行
    
    auto-increment-offset = 1     # 自增值的偏移量
    auto-increment-increment = 1  # 自增值的自增量
    slave-skip-errors = all #跳过从库错误

*  slave1的my.cnf配置文件

[mysqld]
    server-id = 2
    log-bin=mysql-bin
    relay-log = mysql-relay-bin
    replicate-wild-ignore-table=mysql.%
    replicate-wild-ignore-table=test.%
    replicate-wild-ignore-table=information_schema.%

## 6.进入master数据库，创建复制用户并授权 ##

依次执行

CREATE USER repl_user IDENTIFIED BY 'root';
    CREATE USER 'repl_user'@'172.17.0.1' IDENTIFIED BY 'root';
    CREATE USER 'repl_user'@'192.168.1.2' IDENTIFIED BY 'root';

root表示该用户的用户密码  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 4]

你只需要修改的是将 192.168.1.2改为你自己主机的ip地址即可  
查看自己创建用户是否成功

SELECT User, Host FROM mysql.user;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 5]

### 6.1 赋予该用户复制的权利 ###

依次执行

grant replication slave on *.* to 'repl_user'@'%';
    grant replication slave on *.* to 'repl_user'@'172.17.0.1';
    grant replication slave on *.* to 'repl_user'@'192.168.1.2';
    
    FLUSH PRIVILEGES;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 6]  
注意：我们接下来进行一个很重要的步骤，就是修改主库repl\_user的plugin  
依次执行以操作：

alter user 'repl_user'@'%' identified with mysql_native_password by 'root';
    alter user 'repl_user'@'172.17.0.1' identified with mysql_native_password by 'root';
    alter user 'repl_user'@'192.168.1.2' identified with mysql_native_password by 'root';

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 7]

### 6.2 查看master的状态 ###

show master status;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 8]

## 7.配置从库 ##

登录进入从库的mysql服务内执行以下命令

CHANGE MASTER TO 
    MASTER_HOST = 'localhost',  
    MASTER_USER = 'repl_user', 
    MASTER_PASSWORD = 'root',
    MASTER_PORT = 3308,
    MASTER_LOG_FILE='binlog.000002',
    MASTER_LOG_POS=1374,
    MASTER_RETRY_COUNT = 60,
    MASTER_HEARTBEAT_PERIOD = 10000;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 9]

注意你要修改以下内容：  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 10]

### 7.1 启动从库slave进程 ###

start slave;

附：停止从库进程：stop slave;  
![在这里插入图片描述][1c02f2038d134b9596794e27f2238234.png_pic_center]

### 7.2 查看是否配置成功 ###

show slave status\G

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 11]

当这两个信息 都为yes时说明你配置成功啦。

## 8.测试主从同步 ##

我用navicat连接到主库和从库  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_14_color_FFFFFF_t_70_g_se_x_16_pic_center]

接下来我先在master中创建一个数据库test1  
![在这里插入图片描述][349d50b0b71347fc86a819d11e4f4fe9.png_pic_center]

然后刷新salve  
然后会发现salve中也会出现一个test1数据库  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 12]

至此我们的主从同步就完全实现了

[5309a7855439492db2bbddb565a6f060.png_pic_center]: https://img-blog.csdnimg.cn/5309a7855439492db2bbddb565a6f060.png#pic_center
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center]: https://img-blog.csdnimg.cn/a42c5afbf8e74015a22d658e48ca1732.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWk5pbmVTdW4=,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_17_color_FFFFFF_t_70_g_se_x_16_pic_center]: https://img-blog.csdnimg.cn/d83b9e6ad9804b2692f62bd500cfa1e5.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWk5pbmVTdW4=,size_17,color_FFFFFF,t_70,g_se,x_16#pic_center
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 1]: https://img-blog.csdnimg.cn/1c96e2fdacd34b93b4ea08720e599989.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWk5pbmVTdW4=,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 2]: https://img-blog.csdnimg.cn/bd48a473bc0f4652a0217bf1f2b0a78f.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWk5pbmVTdW4=,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 3]: https://img-blog.csdnimg.cn/13673bb5517f4beea2512eef5c40145d.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWk5pbmVTdW4=,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center
[0ce54a409b124a0292bb94976d8d3d08.png_pic_center]: https://img-blog.csdnimg.cn/0ce54a409b124a0292bb94976d8d3d08.png#pic_center
[Link 1]: https://blog.csdn.net/zhiyikeji/article/details/109202968
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 4]: https://img-blog.csdnimg.cn/cd292063acf5454c915dab5374a11b02.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWk5pbmVTdW4=,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 5]: https://img-blog.csdnimg.cn/d9e24cc284fb44e4a7e94d29f2c52516.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWk5pbmVTdW4=,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 6]: https://img-blog.csdnimg.cn/db7e5e4acc794151a6459dca82c67962.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWk5pbmVTdW4=,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 7]: https://img-blog.csdnimg.cn/1aab577543cf42d7b79e163f2e8c97bc.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWk5pbmVTdW4=,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 8]: https://img-blog.csdnimg.cn/6cdb99e23f3f4cf78ce32624828f79c9.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWk5pbmVTdW4=,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 9]: https://img-blog.csdnimg.cn/23a69b23a7164576a3dba39b2cf0e70c.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWk5pbmVTdW4=,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 10]: https://img-blog.csdnimg.cn/0fef813b6db2496ba2d3502ef57e0749.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWk5pbmVTdW4=,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center
[1c02f2038d134b9596794e27f2238234.png_pic_center]: https://img-blog.csdnimg.cn/1c02f2038d134b9596794e27f2238234.png#pic_center
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 11]: https://img-blog.csdnimg.cn/8e61d21cf4244762a32c897459a52349.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWk5pbmVTdW4=,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_14_color_FFFFFF_t_70_g_se_x_16_pic_center]: https://img-blog.csdnimg.cn/c87f729480f04f9b9aa9639071ae53e4.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWk5pbmVTdW4=,size_14,color_FFFFFF,t_70,g_se,x_16#pic_center
[349d50b0b71347fc86a819d11e4f4fe9.png_pic_center]: https://img-blog.csdnimg.cn/349d50b0b71347fc86a819d11e4f4fe9.png#pic_center
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAWk5pbmVTdW4_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 12]: https://img-blog.csdnimg.cn/12f007e4a86e43398e46e8af91335489.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWk5pbmVTdW4=,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center