MySQL高可用与读写分离

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

架构模式

1. 一主一从：
   
2. 一主多从提供系统读的性能
   
3. 多主一从，可以将多个mysql备份到一台存储性能好的mysql上（从5.7开始支持）
   
4. 双主复制（双主复制，也就是互做主从复制，每个master既是master，又是另外一台服务器的slave。这样任何一方所做的变更，都会通过复制应用到另外一方的数据库中）
   
   5.级联复制
   级联复制模式下，部分slave的数据同步不连接主节点，而是连接从节点。因为如果主节点有太多的从节点，就会损耗一部分性能用于replication，那么我们可以让3~5个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响。
   

搭建准备

本次采用，一主一从模式
服务器：2台
数据库：mysql 5.7 / mysql 8.0.28
结构：主机使用的是window服务器，
注意：部分截屏是在window呈现的效果（与在linux配置相差不大）。

主从分离

原理

注释：黄色表示数据、路径线条表示传递过程。
原理：

1. 当主节点有一条数据改变时会存放到二进制文件中（该过程为：二进制日志事件）。
2. 然后Relav 将master中的binaryLog中改变的数据拷贝一份到Relav的RelavLog中（该过程：中继日志事件）。
3. 中级日志事件将数据放入到自己的数据库中mysql同步数据是异步的、串行化、并且有延迟的（如果主数据坏了要使用从数据，有可能会丢失延迟这段时间的数据）。

配置

1. 配置文件：

主机配置：my.ini
从机配置：my.cnf

2. 配置准备：
   设置权限（mysql允许远程链接）：
   执行以下两个步骤需要登录mysql后执行
   2.1 授权远程访问

   方法一：
   grant all privileges on . to ‘root’@‘%’ identified by ‘root’ with grant option;
   flush privileges;
   方法二：
   update user set host=“%” where user=“root”;
   flush privileges;

   2.2 授权那几台机是当前主机的从机

   GRANT REPLICATION slave,reload,super ON . TO ‘root’@‘192.168.2.%’ IDENTIFIED BY ‘root’;
   flush privileges;

   2.3 关闭防火墙：

   查看状态：systemctl status firewalld
   
   此状态表示启动
   执行关闭：systemctl stop firewalld
   
   已关闭

3. 开始配置
   3.1 主机配置
   3.1.1 主机版本（5.5 - 5.7）

   

   #id 表示服务的编号
   server-id=1
   #二进制日志文件的存放路径（注意是/ 不是\）mysql-bin表示文件名称
   log-bin="d:/Mysql/Mysql Server 5.5/data/mysql-bin“
   #错误记录文件
   log-error=”d:/Mysql/Mysql Server 5.5/data/mysql-error“
   #主从同步忽略的数据库
   binlog-ignore-db=mysql
   #(可选)指定主从同步，同步那些数据库
   binlog-do-db=test

   3.1.2 主机数据库版本（8.0-up）
   

   [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 #跳过从库错误

   3.2 配置完成后需要重启，然后可以查看一下主机的状态(每次在做主从同步前需要观察状态的最新值)

   show master status;
   
   File：二进制日志文件名称
   Position：写入二进制数据的位置（重启后会发生改变）
   Binlog_Do_DB：指定的从库
   Binlog_Ignore_DB：指定过滤的数据库

   3.3 启动后生成文件如下图：

   
   mysql-error.err : 错误日志输出
   mysql-bin.index：二进制日志的索引
   mysql-bin.000001：二进制日志文件

   3.4 从机配置
   打开mysql.cnf文件，添加如下配置：
   其他参数可进行选配
   3.4.1 从机数据库版本（5.5 - 5.7）

   
   #id 表示服务的编号
   server-id=1
   #相对路径
   log-bin=mysql-bin
   #与主机binlog-do-db相呼应
   repliate-du-db=test

   3.4.2 从机数据库版本 （8.0-up）

   
   [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.%

   3.5 执行授权，需要指定某一个主机做老大

   CHANGE MASTER TO
   MASTER_HOST = ‘192.168.2.2’,
   MASTER_USER = ‘root’,
   MASTER_PASSWORD = ‘root’
   MASTER_PORT = 3306
   #二进制文件
   master_log_file = ‘mysql-bin.000001’,
   #Position
   master_log_pos=107;
   
   如果产生以下报错（表示之前做过主从同步，需要执行STOP SLAVE 关闭从节点，然后再次执行上面的语句即可）
   

   3.5 从机配置工作完成后，开启从机：

   start slave;

   检验效果

   show slave status \G
   
   需要观察：Slave_IO_Running、Slave_SQL_Running，需要确保二者都是Yes。
   如果都不是Yes，则需要看下方的错误日志
   
   通过本次排错发现主机server-id与从机server-id一致。检查：
   在主从机上分别查看server_id：
   命令：show variables like ‘server_id’;
   分析：发现在主机中配置了server-id=1 数据库加载的配置是1，在从机中配置的server-id=2，但数据库中加载的配置还是1。
   原因：可能是linux版Mysql的一个bug。
   解决(设置全局变量、注意：
   mysql重启服务后需要重新设置)：set global server_id = 2;
   再检查发现已经变成Yes了：
   
   演示主从同步：
   只需要在主数据库创建一个表，插入一条数据。看是否有同步到对应的数据库即可。

注意事项

以上配置启动完成后，不会同步数据库原有的历史数据，需要手动同步。

历史数据同步

1. 加锁：

FLUSH TABLES WITH READ LOCK;

2. 备份数据库

 mysqldump -u root -p -h 192.168.1.6  myerp --single-transaction --master-data=2 --flush-logs > /var/lib/mysql/20220405.sql
注释：
mysqldump --------mysql自带的备份数据库命令
-u'root' --------------登陆用户名（实验用的是root用户，生产环境下只能用普通用户）
-p-----------登陆密码
-h 192.168.1.6---------远程服务器
--all-databases -------备份所有库 （如果只是备份单独一个数据库，写数据库名即可）
​ （如果备份多个数据库 --databases database1 database2 database3 ）
--single-transaction -------- 一致性服务可用性，锁表机制 , 热备份
--master-data=2------该选项将会记录binlog的日志位置与文件名，可以选择1或者2，效果一样
--flush-logs--------------自动刷新日志
/var/lib/mysql/20220405.sql --------------sql存放的位置