程序员经验分享-hwhale

MySQL进阶-监控、高可用

2023-05-16

MySQL监控

1、常见的监控方式

一般来说，常见的监控方式主要有如下三种：

监控方式特点优点缺点
工具/脚本自己编写工具或脚本，适合初期机器很少的生产环境在企业初期可以快速满足监控需求后期部署和维护成本大
商业产品可以快速搭建一套成熟的监控平台，有非常专业酷炫的监控大屏功能强大，有技术支持，见效快成本高昂，扩展性也较差
开源产品免费产品，可以满足大部分的监控场景成本很低，定制能力强，自主可控需要一定的开发能力

2、主流开源监控产品

2.1 Zabbix

Zabbix是一个企业级的高度集成开源监控软件，提供分布式监控解决方案，可以用来监控设备、服务等的可用性和性能，由国外团队进行维护以及持续更新源码，可以自由下载，是一个真正的源代码开放产品。

Zabbix的通用架构是C/S架构，通过B/S在web端进行展示和配置，分布式架构为Client/Proxy/Server，Zabbix-Server将采集到的数据持久地存储到数据库中。

Zabbix数据的采集不仅可以使用Agent方式，也可以使用SNMP、SSH、Telent、IPMI等多种协议。

Zabbix的主要特点：

  • 安装配置简单；
  • 免费开源，试错成本低；
  • 支持多语言；
  • 自动发现服务器；
  • 分布式监控；
  • 集中式web管理；
  • 邮件、微信等通知功能。

Zabbix 的新版本 5.0，可以支持 proxy/server 端通过 odbc 连接 MySQL 进行采集监控数据，所有的配置在web端都可以完成，而且有现成的完善的监控模板，使用起来非常简单高效。
在这里插入图片描述

2.2 PMM

Percona Monitoring and Management (PMM) 是 percona 公司一款开源的用于管理和监控 MySQL、PostgreSQL、MongoDB 以及 ProxySQL 性能的开源平台，通过第三方软件 Grafana 将 DB 监控数据展示出来。PMM 可以帮助你降低数据库环境的复杂性，提升 DB 性能和安全性，是非常流行的开源数据库监控方案。

PMM主要包括两个部分：

  • PMM client：部署在每台数据库主机上，采集服务器，数据库和慢 SQL 等监控数据；
  • PMM Server：PMM 的核心部分，汇集数据并 web 界面中展示监控数据。

PMM 的架构如下图：
在这里插入图片描述

MySQL高可用

提起 MySQL的高可用，大家都会想到 MHA、MMM、PXC、MGR，特别是 MGR，近几年成为新一代网红，也是 MySQL 官方主推的一套高可用方案。在 MGR 的基础上，结合一些成熟的开源软件，可以组合出几乎满足所有业务场景的高可用方案。

1、架构方案

介绍一套时髦的高可用架构：3节点 MGR+3节点 ProxySQL+3节点 Consul

该架构主要分为三层：

  1. Consul：dns解析、服务发现、健康检查；
  2. ProxySQL：负载均衡、读写分离、故障发现；
  3. MGR：单主模式、故障转移、强一致性。

在这里插入图片描述

2、MGR

基本环境

MySQL-1MySQL-2MySQL-3
MySQL版本MySQL 5.7.30MySQL 5.7.30MySQL 5.7.30
操作系统CentOS 7.8CentOS 7.8CentOS 7.8
服务器IP192.168.0.1192.168.0.2192.168.0.3
端口对外服务端口-3066\MGR通讯端口-33061对外服务端口-3066\MGR通讯端口-33061对外服务端口-3066\MGR通讯端口-33061
服务器配置2c4g2c4g2c4g

安装配置

  1. 安装插件：
--安装mgr插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
--检查
show plugins
  1. 配置 hosts：
vi /etc/hosts
192.168.0.1 mysql-1
192.168.0.2 mysql-2
192.168.0.3 mysql-3
  1. 修改 auto.cnf：
    如果三台 MySQL 目录的 server-uuid 一致，需进行修改
vi /mysql/data/auto.cnf
--主库的server-uuid的末尾建议设置成0001，依次类推，这样方便识别
[auto]
server-uuid=c734397b-3b0a-11e8-add1-fa163e8e0001
  1. 配置 my.cnf：
#Group Replication
binlog_checksum = NONE
transaction_write_set_extraction = XXHASH64
slave_preserve_commit_order = true
loose-group_replication_group_name = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = '192.168.0.1:33061'
loose-group_replication_group_seeds ='192.168.0.2:33061, 192.168.0.3:33061, 192.168.0.1:33061'
loose-group_replication_bootstrap_group = off
# single primary
loose-group_replication_single_primary_mode = on
# multi primary
#loose-group_replication_single_primary_mode = off
#loose-group_replication_enforce_update_everywhere_checks=true
  1. 启动MGR-主库
--配置复制用户
set sql_log_bin=off;
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY '123456';
set sql_log_bin=on;
 
--建立channel
CHANGE MASTER TO MASTER_USER='repl_user', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';

--第一个节点启动时，需要设置bootstrap_group
SET GLOBAL group_replication_bootstrap_group = ON;

--启动MGR
START GROUP_REPLICATION;

--取消bootstrap_group
SET GLOBAL group_replication_bootstrap_group = OFF;

--查看当前MGR成员信息
SELECT * FROM performance_schema.replication_group_members;
  1. 启动MGR-从库
--配置复制用户
set sql_log_bin=off;
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY '123456';
set sql_log_bin=on;
 
--建立channel
CHANGE MASTER TO MASTER_USER='repl_user', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';

--启动MGR
START GROUP_REPLICATION;
  1. 查看MGR
--查看当前MGR成员信息
SELECT * FROM performance_schema.replication_group_members;
--查看只读参数
show variables like '%read_only%';

基础维护

#1. 启动MGR
START GROUP_REPLICATION;

#2. 停止MGR
STOP GROUP_REPLICATION;

3、ProxySQL

ProxySQL 是基于 MySQL 的一款开源的中间件产品，是一个轻量级的 MySQL 代理层，可以实现读写分离，故障切换，集群部署，监控后端节点，可缓存查询结果，支持 Query 路由功能，支持动态指定某个SQL 进行缓存，支持动态加载。

基本环境

ProxySQL-1ProxySQL-2ProxySQL-3
MySQL版本proxysql-2.0.5proxysql-2.0.5proxysql-2.0.5
操作系统CentOS 7.8CentOS 7.8CentOS 7.8
服务器IP192.168.0.1192.168.0.2192.168.0.3
端口管理端口-6032\服务端口-6033管理端口-6032\服务端口-6033管理端口-6032\服务端口-6033
服务器配置2c4g2c4g2c4g

安装配置

安装 proxysql：

--新安装
rpm -ivh proxysql-2.0.5-1-centos7.x86_64.rpm
--升级
rpm -Uvh proxysql-2.0.5-1-centos7.x86_64.rpm
--删除
rpm -qa | grep proxysql
rpm -e proxysql-2.0.5-1-centos7.x86_64

--启动proxysql
service proxysql start
--检查版本
proxysql –version
--proxysql路径
/var/lib/proxysql

配置后端 MySQL 机器：
登入 ProxySQL，把 MySQL 主从的信息添加进去。将主库 master 也就是做写入的节点放到 HG 100中，salve 节点做读放到 HG 1000。

--配置后端MySQL机器（在ProxySQL中执行）
--登录proxysql管理端口
mysql -uadmin -padmin -h127.0.0.1 -P6032;
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) 
values(100,'192.168.0.1',3306,1,500,10,'mysql-1'),
(1000,'192.168.0.1',3306,10,500,10,'mysql-1'),
(1000,'192.168.0.2',3306,45,500,10,'mysql-2'),
(1000,'192.168.0.3',3306,45,500,10,'mysql-3');
select * from mysql_servers; 

-- 持久化（在ProxySQL中执行）
save mysql servers to disk;

-- 加载到线上（在ProxySQL中执行）
load mysql servers to runtime;

配置后端 MySQL 用户：
这个用户需要先在后端 MySQL 里真实存在，一个是监控账号，一个是程序账号：

--监控账号（在MySQL中执行）
GRANT USAGE ON *.* TO 'monitor_user'@'192.%' IDENTIFIED BY '123456';
--程序账号（在MySQL中执行）
GRANT SELECT, INSERT, UPDATE, DELETE ON center.* TO 'app_user'@'192.%' identified by '123456';

在每个 ProxySQL 中执行，配置后端 MySQL 监控用户：

--登录proxysql管理端口
mysql -uadmin -padmin -h127.0.0.1 -P6032;
UPDATE global_variables SET variable_value='monitor_user' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='123456' WHERE variable_name='mysql-monitor_password';
--加载和持久化
save mysql variables to disk;
load mysql variables to runtime;
 
--配置后端MySQL程序用户（在ProxySQL中执行）
insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent, max_connections) values('app_user','123456',1,100,1,500)
 
-- 持久化（在ProxySQL中执行）
save mysql users to disk;
-- 加载到线上（在ProxySQL中执行）
load mysql users to runtime;

加载配置和变量

-- 持久化（在ProxySQL中执行）
save mysql servers to disk;
save mysql users to disk;
save mysql variables to disk;
save mysql query rules to disk;

-- 加载到线上（在ProxySQL中执行）
load mysql servers to runtime;
load mysql users to runtime;
load mysql variables to runtime;
load mysql query rules to runtime;

定义路由规则

-- 发送到主库（在ProxySQL中执行）
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',100,1);

-- 发送到从库（在ProxySQL中执行）
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',1000,1);

-- 加载（在ProxySQL中执行）
save mysql query rules to disk;
load mysql query rules to runtime;

MGR配置

--配置MGR信息（在每个ProxySQL中执行）
insert into mysql_group_replication_hostgroups (writer_hostgroup,reader_hostgroup,backup_writer_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) 
values (100,1000,101,102,1,1,1,100);
select * from mysql_group_replication_hostgroups;

-- 加载（在每个ProxySQL中执行）
save mysql servers to disk;
load mysql servers to runtime;

--导入sys视图（在MySQL主库中执行）
source /software/addition_to_sys.sql
addition_to_sys.sql可以从http://lefred.be/content/mysql-group-replication-native-support-in-proxysql下载

--授权sys库（在MySQL主库中执行）
GRANT SELECT on sys.* to 'monitor_user'@'192.%';

--查看MGR成员基本信息（在MySQL中执行），监测节点的健康与落后情况
select * from sys.gr_member_routing_candidate_status;

--查看MGR各节点状态（在ProxySQL中执行）
select hostgroup_id, hostname, status from runtime_mysql_servers;

--查看MGR各节点日志信息（在ProxySQL中执行）
select * from mysql_server_group_replication_log order by time_start_us desc limit 5;

基础维护

#启动 ProxySQL：
service proxysql start

#停止 ProxySQL：
service proxysql stop

#连接 ProxySQL 管理端口：
mysql -uadmin -padmin -h127.0.0.1 -P6032;

#连接 ProxySQL 服务端口：
mysql -uadmin_user -padmin_user -h127.0.0.1 -P6033;

#sql 执行统计：
select hostgroup,schemaname,username,digest_text,count_star from stats_mysql_query_digest;

4、Consul

Consul 是 HashiCorp 公司推出的一个用于实现分布式系统的服务发现与配置工具。Consul 使用 Go 语言编写，具有天然可移植性，支持多平台部署，安装包仅仅是一个可执行文件，部署非常简单。

Consul 内置了服务注册与发现、分布一致性协议实现、dns 解析、健康检查、Key/Value 存储、多数据中心方案。

Consul 官方网址：https://www.consul.io；

基本环境

Consul-1Consul-2Consul-3
MySQL版本Consul_1.8.4Consul_1.8.4Consul_1.8.4
操作系统CentOS 7.8CentOS 7.8CentOS 7.8
服务器IP192.168.0.1192.168.0.2192.168.0.3
端口860086008600
服务器配置2c4g2c4g2c4g

安装配置

创建日志文件和配置文件：

--consul日志
sudo touch /consul/log/consul.log
--Consul配置
sudo touch /consul/consul.d/consul_config.json
--服务注册
sudo touch /consul/consul.d/proxysql.json

安装 Consul：

--解压缩即可
cd /consul
unzip consul_1.8.4_linux_amd64.zip
--创建软链接
ln -s /consul/consul /usr/bin/consul
--查看版本
consul --version
Consul v1.8.4

Consul配置-Server端

全局配置-consul_config.json：

vi /consul/consul.d/consul_config.json
{ 
"datacenter":"datacenter-1", 
"data_dir":"/consul/data", 
"log_level": "INFO", 
"node_name": "consul-server-01", 
"bootstrap_expect": 2,
"server": true, 
// ui界面在一台server设置为true，其它设置为false
"ui":true, 
// 如果绑定具体IP，会导致consul集群之间tcp8301端口失败
"bind_addr":"0.0.0.0", 
// 客户端允许访问ip
"client_addr":"0.0.0.0",
"enable_script_checks":true,
// 加入集群
"start_join":
["192.168.0.1", "192.168.0.2", "192.168.0.3"],
"retry_join":
["192.168.0.1", "192.168.0.2", "192.168.0.3"],
"ports": 
{"dns": 53}
}

Consul 配置-Client 端

全局配置-consul_config.json：

/consul/consul.d/consul_config.json
{ 
"datacenter":"datacenter-1", 
"data_dir":"/consul/data", 
"log_level": "INFO", 
"node_name": "consul-app-proxysql-01", 
"server":false, 
//ui界面在一台server设置为true，其它设置为false
"ui":false, 
// 如果绑定具体IP，会导致consul集群之间tcp8301端口失败
"bind_addr":"0.0.0.0", 
// 客户端允许访问ip
"client_addr":"0.0.0.0",
"enable_script_checks":true,
// 加入集群
"start_join":
["192.168.0.1", "192.168.0.2", "192.168.0.3"]],
"retry_join":
["192.168.0.1", "192.168.0.2", "192.168.0.3"]],
"ports": 
{"dns": 53}
}

服务注册-proxysql.json：

--采用mysqladmin检查
vi /consul/consul.d/proxysql.json
{
"service": {
"id": "proxysql-01",
"name": "proxysql",
"tags": ["6033-rw-app"],
"address": "192.168.0.1",
"port": 6033,
"check": {
"script": "mysqladmin ping --host=localhost --port=6033 --user=root --password=123456",
"interval": "3s"
}
}
}
 
--采用telnet检查
vi /consul/consul.d/proxysql.json
{
"service": {
"id": "proxysql1",
"name": "proxysql",
"tags": ["6033-rw-app"],
"address": "192.168.0.1",
"port": 6033,
        "check": {
            "interval": "3s", 
            "tcp": "127.0.0.1:6033", 
            "timeout": "1s"
        }
}
}

DNS 解析配置

--在应用端配置nameserver，指向consul集群
vi /etc/resolv.conf
#指向本地consul 53端口，正常由本地做dns解析
nameserver 127.0.0.1
#指向consul集群 53端口，备用
nameserver 192.168.0.1
nameserver 192.168.0.2
nameserver 192.168.0.3

基础维护

#Server 端启动：
consul agent -config-dir=/consul/consul.d/ >> /software/consul/log/consul.log &

#Client 端启动：
consul agent -config-dir=/consul/consul.d/ >> /software/consul/log/consul.log &

#域名测试：
dig @127.0.0.1 -p 53 proxysql.service.consul
dig 6033-rw-app.proxysql.service.consul

#退出 Consul：
--consul命令
consul leave

#查看 Consul 集群信息：
--查看consul集群信息
consul members
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

MySQL进阶-监控、高可用 的相关文章

  • Preg_replace() 删除除查询结尾之外的所有内容

    首先 为我糟糕的英语感到抱歉 我有这样的疑问 SELECT t1 SELECT COUNT FROM table a t2 WHERE t1 id t2 id c AND t2 status 1 AS aula FROM table c t

  • MySQL 全文搜索不适用于某些单词，例如“house”

    我已经在 3 个字段中的一小部分记录上设置了全文索引 也尝试了 3 个字段的组合 并得到了相同的结果 有些单词返回结果很好 但某些单词如 house 和 澳大利亚 不这样做 有趣的是 澳大利亚 和 家乡 这样做 这似乎是奇怪的行为 如果我添

  • 如何将 MySQL 数据库更改为 UTC？

    我使用的是 Windows 7 对数据库方面的东西有点陌生 我尝试在 Google 上搜索如何将系统时区更改为 UTC 但文档有些高级 我不太确定如何更改此字段 在 my ini 文件的 mysqld 部分下 添加以下行 default t

  • 当“修复表”查询在 mysql 中不起作用时该怎么办？

    我收到此错误 表的存储引擎不支持修复 当我尝试使用查询修复表时repair table tbl college master 表是 innodb 类型 但我不知道我收到此错误 See 手册 http dev mysql com doc re

  • MySql 最后插入 ID，连接器 .net

    我正在使用 MySql Connector net 我需要获取最后一个查询生成的插入 id 现在 我假设返回值是MySqlHelper ExecuteNonQuery应该是最后一个插入id 但它只返回1 我正在使用的代码是 int inse

  • 比较表中的行以了解字段之间的差异

    我有一个包含 20 多列的表 客户端 其中大部分是历史数据 就像是 id clientID field1 field2 etc updateDate 如果我的数据如下所示 10 12 A A 2009 03 01 11 12 A B 200

  • 如何在 phpmyadmin 中创建 MySQL 触发器

    我想在 MySQL 中创建一个触发器 我运行以下命令 mysql gt delimiter mysql gt CREATE TRIGGER before insert money BEFORE INSERT ON money gt FOR

  • 如何使用 MySQL 的 LOAD DATA LOCAL INFILE 在导入 CSV 时将字符串日期更改为 MySQL 日期格式

    我正在使用 MySQL 的 LOAD DATA LOCAL INFILE SQL 语句将数据从 CSV 文件加载到现有数据库表中 下面是一个 SQL 语句示例 LOAD DATA LOCAL INFILE file csv INTO TAB

  • Mysql 检索所有有限制的行

    我想检索特定用户的所有行 限制为 0 x 所以我只是想问是否有任何方法可以检索 mysql 中的所有行 而不调用返回 x 的 count id 的方法 而不重载现有函数 该函数在查询中根本没有限制 与我们的 string Relace 功能

  • 无法通过套接字“/var/lib/mysql/mysql.sock”连接到本地 MySQL 服务器 (2)

    当我尝试连接 mysql 时出现以下错误 Can t connect to local MySQL server through socket var lib mysql mysql sock 2 这个错误有解决办法吗 其背后的原因可能是什

  • 使用 PHP 的 MySQL 连接字符串

    我正在尝试通过本地计算机连接到托管在我的服务器上的数据库 我的服务器有cPanel 11 它是一个典型的共享服务器 由CentOS提供支持 安装了PHP和MySQL 准确地说 我在同一台服务器上持有经销商帐户 我想在不同帐户或域之间访问数据

  • 如何在Sequelize中设置查询超时？

    我想看看如何在 Sequelize 中设置查询的超时时间 我查看了 Sequelize 文档以获取一些信息 但我找不到我要找的东西 我发现的最接近的是 pools acquire 选项 但我不想设置传入连接的超时 而是设置正在进行的查询的超

  • Apache、PHP 和 MySQL 可移植吗？

    我可以在外部硬盘上运行 Apache PHP 和 MySQL 吗 我需要这个 因为我在不同的地方工作 计算机 有时我没有安装和配置所有使用的应用程序 当然可以 XAMPP http www apachefriends org en xamp

  • 将古吉拉特语文本插入 MySQL 表会产生垃圾字符和不可读的文本

    我有三个 MySQL 表 我正在向其中插入古吉拉特语内容 当我插入两个表时 它们插入得很好并且可读 但在一个表中 它显示垃圾字符 不可读的文本 我怎样才能解决这个问题 MySQL 有每个表的字符集设置 http dev mysql com

  • 如何使用 vitess 仅对特定表进行分片

    我创建了一个包含三个表的未分片键空间 现在我想对前两个表的键空间进行分片 但不想对第三个表进行分片 如何才能做到这一点 Vitess 文档不包含任何与此相关的信息或示例 请帮忙 Thanks vitess 中的垂直分片与水平分片类似 您应该

  • Mysql 将 int 转换为 MAC

    我有一些数据可以转换 其中有 2 列 其中一列有 IP 它包含整数值 我在 mysql 查询中使用了以下函数 是否有一个函数可以用来转换我的 mac 列 其中包含整数和数据类型是bigint to MAC地址 SELECT INET NTO

  • 如何在 Play java 中创建数据库线程池并使用该池进行数据库查询

    我目前正在使用 play java 并使用默认线程池进行数据库查询 但了解使用数据库线程池进行数据库查询可以使我的系统更加高效 目前我的代码是 import play libs Akka import scala concurrent Ex

  • INSERT..RETURNING 在 JOOQ 中不起作用

    我有一个 MariaDB 数据库 我正在尝试在表中插入一行users 它有一个生成的id我想在插入后得到它 我见过this http www jooq org doc 3 8 manual sql building sql statemen

  • 如何从shell脚本自动登录MySQL？

    我有一个 MySQL 服务器 其中有一个用户和密码 我想在 shell 脚本中执行一些 SQL 查询而不指定密码 如下所示 config sh MYSQL ROOT root MYSQL PASS password mysql sh sou

  • 一次从多个表中删除行

    我正在尝试将 2 个查询合并为一个这样的查询 result db gt query DELETE FROM menu WHERE name new or die db gt error result db gt query DELETE F

随机推荐

热门标签