（高可用）四、MySQL

已于 2023-01-31 12:41:04 修改
阅读量59 收藏
点赞数
分类专栏： 运维 文章标签： mysql 数据库
于 2023-01-29 21:12:16 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/akswyh/article/details/128794248
版权

文章目录

一、主从复制

  • https://dev.mysql.com/doc/refman/5.7/en/replication-howto.html

  • slave向master认证

  • master向slave发送二进制日志

  • slave端IO线程将master发送来的日志写入本地，slave端SQL线程用于变更本地数据库

  • 影响IO线程正常工作的原因：

    1. master端防火墙
    1. 执行CHANGE MASTER时的信息在master端是否可用？

  • 影响SQL线程正常工作的原因：

    1. 原本的主从数据不一致

  • slave端SQL线程单线程写入，相比IO线程较慢，怎么解决？

  • slave端IO线程是异步处理的，怎么解决同步问题？

1.打开GTID功能

  • https://dev.mysql.com/doc/refman/5.7/en/replication-gtids.html
  • 这样每次重启机器后，slave不需要再知道具体的master的日志id
  • gtid会自动增加，slave端根据gtid来进行同步
  • slave自动找拥有最近gtid的主机作为master
# master端
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock

log-bin=mysql-bin
server-id=1

gtid_mode=ON
enforce-gtid-consistency=ON


# slave端
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock

server-id=2
gtid_mode=ON
enforce-gtid-consistency=ON


# 重启mysql，先起master

2.半同步模式

  • https://dev.mysql.com/doc/refman/5.7/en/replication-semisync.html
  • 无损模式
# master端
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

# slave端
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

# 查看激活状态
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
    ->        FROM INFORMATION_SCHEMA.PLUGINS
    ->        WHERE PLUGIN_NAME LIKE '%semi%';
+---------------------+---------------+
| PLUGIN_NAME         | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+
1 row in set (0.00 sec)

# 打开半同步功能（热激活）
## master端
SET GLOBAL rpl_semi_sync_master_enabled = 1;

## slave端
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

# 查看半同步变量
mysql> show variables like 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.01 sec)

# 查看半同步状态
mysql> show status like 'rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

# 重启slave端的IO线程
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)

mysql>  show status like 'rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.01 sec)

# 如果slave端的IO线程挂掉，master端会等待一个超时时间后，切换成异步工作状态

3.延迟复制

mysql> stop slave SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_delay=10;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave SQL_THREAD;

4.慢查询

mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "long%";
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> set long_query_time=5;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "long%";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

5.并行SQL写入

  • 加速slave端的SQL线程写入
# slave端修改配置文件
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock

server-id=2
gtid_mode=ON
enforce-gtid-consistency=ON

slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON


# 查看slave端的线程
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  1 | system user |           | NULL | Connect |   53 | Slave has read all relay log; waiting for more updates | NULL             |
|  3 | system user |           | NULL | Connect |   53 | Waiting for master to send event                       | NULL             |
|  4 | system user |           | NULL | Connect |   53 | Waiting for an event from Coordinator                  | NULL             |
|  5 | system user |           | NULL | Connect |   53 | Waiting for an event from Coordinator                  | NULL             |
|  6 | system user |           | NULL | Connect |   53 | Waiting for an event from Coordinator                  | NULL             |
|  7 | system user |           | NULL | Connect |   53 | Waiting for an event from Coordinator                  | NULL             |
|  8 | system user |           | NULL | Connect |   53 | Waiting for an event from Coordinator                  | NULL             |
|  9 | system user |           | NULL | Connect |   53 | Waiting for an event from Coordinator                  | NULL             |
| 10 | system user |           | NULL | Connect |   53 | Waiting for an event from Coordinator                  | NULL             |
| 11 | system user |           | NULL | Connect |   53 | Waiting for an event from Coordinator                  | NULL             |
| 12 | system user |           | NULL | Connect |   53 | Waiting for an event from Coordinator                  | NULL             |
| 13 | system user |           | NULL | Connect |   53 | Waiting for an event from Coordinator                  | NULL             |
| 14 | system user |           | NULL | Connect |   53 | Waiting for an event from Coordinator                  | NULL             |
| 15 | system user |           | NULL | Connect |   53 | Waiting for an event from Coordinator                  | NULL             |
| 16 | system user |           | NULL | Connect |   53 | Waiting for an event from Coordinator                  | NULL             |
| 17 | system user |           | NULL | Connect |   53 | Waiting for an event from Coordinator                  | NULL             |
| 18 | system user |           | NULL | Connect |   53 | Waiting for an event from Coordinator                  | NULL             |
| 19 | system user |           | NULL | Connect |   53 | Waiting for an event from Coordinator                  | NULL             |
| 20 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
19 rows in set (0.00 sec)

二、组复制

  • https://dev.mysql.com/doc/refman/5.7/en/group-replication.html
  • 仅支持innodb
  • 最多支持9个节点为一组
  • 每个节点既是主又是从
  • 所有节点之间同步，如果一个节点出现故障，则全部回滚
  • 组复制需要保证节点之间的原始数据完全相同

第一个节点配置

# 关闭mysqld，清除原data
[root@localhost ~]# /etc/init.d/mysqld stop
[root@localhost ~]# rm -rf /data/mysql/*
[root@localhost ~]# ll /data/mysql/
total 0

# 修改my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "server1:33061"
group_replication_group_seeds= "server1:33061,server2:33061,server3:33061"
group_replication_bootstrap_group=off
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_ip_whitelist="192.168.147.0/24,127.0.0.1/8"
group_replication_allow_local_disjoint_gtids_join=1


# 重新初始化mysql
[root@localhost ~]# mysqld --initialize --user=mysql

# 进入mysql修改root密码
[root@localhost ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/server1.err'.
 SUCCESS!
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.36-log

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user root@localhost identified by 'passwd';
Query OK, 0 rows affected (0.00 sec)


# 创建用于组复制的用户，在此期间关闭二进制日志记录
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)


# 激活组复制，在初始节点激活组复制时需要开启bootstrap，其他节点不用
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.09 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1ddadb97-93f0-11ed-9d79-000c299c98e4 | server1     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

其他节点配置

  • 只需要注意修改my.cnf中的部分内容
  • 在创建rpl_user需要暂时关闭二进制日志写入（防止数据不一致）
  • 开启组复制功能时，不需要再开启bootstrap功能
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (3.28 sec)

实验效果

# 可以看到各环节都是online状态
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1ddadb97-93f0-11ed-9d79-000c299c98e4 | server1     |        3306 | ONLINE       |
| group_replication_applier | 52d2a816-93f2-11ed-ad4b-000c29ad4e3c | server3     |        3306 | ONLINE       |
| group_replication_applier | de36bbfc-93f0-11ed-905c-000c29f090f3 | server2     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

# 此时在任意一个节点插入数据，都会被同步到其他节点

三、读写分离

  • 使用包mysql-router
  • 下载地址：https://cdn.mysql.com/archives/mysql-router/mysql-router-community-8.0.21-1.el7.x86_64.rpm
  • 配置文件：/etc/mysqlrouter
[routing:ro]  # 配置读路由策略
bind_address=0.0.0.0 
bind_port=7001
destinations=192.168.147.138:3306,192.168.147.139:3306,192.168.147.140:3306
routing_strategy=round-robin  # 轮询

[routing:rw]  # 配置写路由策略
bind_address=0.0.0.0
bind_port=7002
destinations=192.168.147.138:3306,192.168.147.139:3306,192.168.147.140:3306
routing_strategy=round-first-available  # 首位可用
akswyh
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql 8.0 高可用_MySQL8.0 MIC高可用集群搭建
weixin_32715349的博客
01-27 1478
mysql8.0带来的新特性，结合MySQLshell，不须要第三方中间件，自动构建高可用集群。pythonmysql8.0做为一款新产品，其内置的mysq-innodb-cluster(MIC)高可用集群的技术确实惊艳，结合MySQLshell可以实施集群的快速部署，MySQL-route可以实现灾备快速切换，内置读写分离技术，负载均衡技术。结合但实际效果如何，还需验证。mysql一，集群部署l...
keepalived实现mysql高可用_keepalived+MySQL实现高可用
weixin_35936869的博客
01-19 2960
(一)keepalived概述Keepalived通过VRRP(虚拟路由冗余协议)协议实现虚拟IP的漂移。当master故障后，VIP会自动漂移到backup，这时通知下端主机刷新ARP表，如果业务是通过VIP连接到服务器的，则此时依然能够连接到正常运行的主机，RedHat给出的VRRP工作原理如下图：本来对VIP漂移有一定了解的我，看了上面的图后，越来越懵了。因此只能根据我的个人理解，来对kee...
Mysql 的高可用详解
@小刘同学nice
02-26 1482
复制是解决系统高可用的常见手段。其思路就是：不要把鸡蛋都放在一个篮子里。复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。一台主库的数据可以同步到多台备库上，备库本身也可以被配置成另外一台服务器的主库。主库和备库之 间可以有多种不同的组合方式。MySQL 支持两种复制方式：基于行的复制和基于语句的复制。这两种方式都是通过在主库上记录 bin log、在备库重放日志的方式来实现异步的数据复制。这意味着：复制过程存在时延，这段时间内，主从数据可能不一致。
MySQL高可用方案
翔云
11-30 2816
MySQL高可用方案 MySQL高可用方案 MySQL高可用方案
keepalived+MySQL实现高可用
weixin_37266841的博客
01-09 1854
keepalived+MySQL实现高可用
MySQL高可用解决方案――从主从复制到InnoDB Cluster架构
学Java，找哪吒
03-25 4712
文末送5本《MySQL高可用解决方案――从主从复制到InnoDB Cluster架构》
Mysql数据库的高可用
wh柒八九的博客
08-21 1897
本文来说下有关mysql数据库的高可用 文章目录概述 概述 高可用，即High Availability，是分布式系统架构设计中必须考虑的因素之一，它通常是指，通过设计减少系统不能提供服务的时间。单机部署谈不上高可用，因为单点故障问题。高可用都是多个节点的，我们在考虑MySQL数据库的高可用的架构时，需要考虑这几个方面： 如果数据库节点宕机，需要尽快回复，保证业务不受宕机影响。 从数据库节点的数据，尽可能跟主节点数据实时保持一致，至少保证最终一致性。 数据库节点切换时，数据不能缺失。 ...
MySQL是如何保证高可用的
qq_48157004的博客
11-12 3462
主备延迟指的是主库执行完成的时间和备库执行完成的时间的差距主库 A 执行完成一个事务，写入 binlog，我们把这个时刻记为 T1;之后传给备库 B，我们把备库 B 接收完这个 binlog 的时刻记为 T2;备库 B 执行完成这个事务，我们把这个时刻记为 T3。所以主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是 T3-T1。
mysql高可用架构_mysql高可用架构详解
weixin_36488760的博客
01-18 2039
高可用(High Availabiltity)应用提供持续不间断(可用)的服务的能力系统高可用性的评价通常用可用率表示造成不可用的原因硬件故障(各种)预期中的系统软硬件维护软件缺陷(应用代码，服务程序都可能存在bug)***，泄露，认为失误...等安全事件对于系统来说，不可用时间是各关键组件不可用时间的总和.....提高可用性的主要手段冗余，Redundancy关键软硬件通过备用冗余避免故障时长时...
MySQL高可用架构知识整理
发现问题，面对问题，分析问题，解决问题，总结问题
02-08 3942
文章目录（1）MySQL 复制架构（2）MySQL复制类型（3）主库大事务处理（4）主从复制延迟优化（5）数据库高可用架构设计（6）容灾级别（7）数据核对（8）高可用套件——VIP（9）高可用套件MHA和Orchestrator（10） InnoDB Cluster和MGR技术（11）数据库备份 （1）MySQL 复制架构 【1】数据库复制本质上就是数据同步。MySQL 数据库是基于二进制日志（binary log）进行数据增量同步，而二进制日志记录了所有对于 MySQL 数据库的修改操作。 在默认 ROW
MySQL高可用浅析
12-14
MySQL高可用性是确保系统持续稳定运行的关键，尤其是在依赖MySQL作为数据存储中心的应用场景中。本文将探讨如何通过各种技术手段提升MySQL的高可用性，包括复制（Replication）、半同步复制（Semi-Synchronous ...
kubernetes部署mysql高可用
09-02
kubernetes部署mysql高可用 ,本资源为kubernetes集群部署mysql高可用读写分离数据库，内含所有需要的yaml文件，有需要的可进行下载
MySQL：SELECT 语句
Zachyy的博客
07-20 312
在 MySQL 中，可以使用 SELECT语句来查询数据。查询数据是指从数据库中根据需求，使用不同的查询方式来获取不同的数据，是使用频率最高、最重要的操作。
MySQL表中允许有多少个 TRIGGERS（触发器）？
sheji888的专栏
07-17 453
MySQL的触发器可以分为六种类型：BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE、BEFORE DELETE、AFTER DELETE，每种类型可以针对一个表定义多个触发器，但是每种类型的触发器在单个表上只能有一个与之关联的命名触发器。：虽然技术上讲每种类型的触发器在单个表上只能有一个命名触发器，但你可以通过创建具有不同名称的相同类型的多个触发器来绕过这个限制，只要这些触发器在触发时满足不同的条件（例如，检查不同的列或满足不同的业务逻辑）。
MySQL（事务、索引）&&MyBatis
hycccccch的博客
07-19 662
事务指的是一组操作的集合，是一个不可分割的工作单位。事务会将所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败默认MySQL的事务是自动提交，即当执行一条DML语句时，MySQL会立即隐式的提交事务开启事务后运行事务不会对数据改变，commit后才会改变数据如果有命令发生错误，需要进行rollback进行回滚、
mysql中ONLY_FULL_GROUP_BY报错
最新发布
zzzzzucc的博客
07-21 92
禁用会话级别的 ONLY_FULL_GROUP_BY SET SESSION sql_mode = ‘’;– 或者禁用全局级别的 ONLY_FULL_GROUP_BY SET GLOBAL sql_mode = ‘’;在 MySQL 中，sql_mode是一个服务器变量，用于定义 SQL 语句的运行模式。是sql_mode中的一个选项，它影响 SQL 查询中GROUP BY子句的行为。
MySQL企业高可用性参考指南
"MySQL Enterprise High Availability 是MySQL Group Replication提供的一种原生内置的高可用性解决方案，旨在确保MySQL数据库的持续运行。这份参考指南详细介绍了MySQL企业版的高可用性特性，涵盖了从基础架构到...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值