MySQL进阶-监控、高可用

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

基本环境

安装配置

1. 安装插件：

--安装mgr插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
--检查
show plugins

2. 配置 hosts：

vi /etc/hosts
192.168.0.1 mysql-1
192.168.0.2 mysql-2
192.168.0.3 mysql-3

3. 修改 auto.cnf：
   如果三台 MySQL 目录的 server-uuid 一致，需进行修改

vi /mysql/data/auto.cnf
--主库的server-uuid的末尾建议设置成0001，依次类推，这样方便识别
[auto]
server-uuid=c734397b-3b0a-11e8-add1-fa163e8e0001

4. 配置 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

5. 启动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;

6. 启动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;

7. 查看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：

--新安装
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日志
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