如何实现mysql高可用集群

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
日志服务 SLS,月写入数据量 50GB 1个月
简介: 如何实现mysql高可用集群

前言

一、概念:

高可用是指通过尽可能缩短系统的维护时间(包括日常维护和突发系统故障),使得服务器或服务能够最大限度地提供可用性。

高可用是在IT生产环境里对每个系统或服务的基本要求。当用户需要在IT系统上执行操作时,系统或服务都需要保证能够正常访问。如果系统无法使用或宕机,则用户无法执行所需的操作。

MySQL在高可用方面持续进行开发和改进,能够提供详细的管理和配置、状态报告,以及主服务器自动故障转移等功能,以确保即使主服务器发生宕机,数据仍然能够使用。

二、如何实现?

(一)可靠性:

可靠性是指在一定时间内、一定条件下,使系统无故障地执行指定的功能,并使系统尽可能具有可访问性。可靠性可以通过多种方式来实现,因此,系统会产生不同级别的可靠性。

通常情况下,用户可通过以下三个原则来实现可靠性。

1. 消除单点故障:尽可能减少使用的组件,如果单一组件发生故障,那么系统将无法继续使用。

2. 增加恢复能力:建立多个主动冗余机制,通过冗余组件将系统从故障中快速恢复。

3.容错:增加主动故障检测,通过切换到冗余组件的机制进行自动恢复。

今天主要介绍高可用集群的搭建(主主+keepalived)。

一、先做主从复制

修改主库和从库的参数文件(/etc/my.cnf)

master1:192.168.1.1

[mysqld]

user=mysql

basedir=/usr/local/mysql

datadir=/data/mysql

socket=/tmp/mysql.sock

log-error=/data/mysql/mysql.err

pid-file=/data/mysql/mysql.pid

server_id=1

port=3306

character-set-server=utf8

innodb_rollback_on_timeout = ON

character-set-server = utf8

collation-server=utf8_general_ci

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

lower_case_table_names=1

max_connections=10000

sync_binlog=1

binlog_format=row

secure-file-priv=/tmp

autocommit=0

log_bin=/data/mysql/master20-bin

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=1

[mysql]

socket=/tmp/mysql.sock

default-character-set=utf8

[client]

master2:192.168.1.2

[mysqld]

user=mysql

basedir=/usr/local/mysql

datadir=/data/mysql

socket=/tmp/mysql.sock

log-error=/data/mysql/mysql.err

pid-file=/data/mysql/mysql.pid

server_id=2

port=3306

character-set-server=utf8

innodb_rollback_on_timeout = ON

character-set-server = utf8

collation-server=utf8_general_ci

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

lower_case_table_names=1

max_connections=10000

sync_binlog=1

binlog_format=row

secure-file-priv=/tmp

autocommit=0

log_bin=/data/mysql/slave21-bin

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=1

[mysql]

socket=/tmp/mysql.sock

default-character-set=utf8

[client]


master1

#创建用AB复制所需的用户

mysql> grant replication slave  on *.* to repl@'%' identified by '123123';

mysql> flush privileges;

master2

#建立复制关系

mysql>change master to

master_host='192.168.1.1',

MASTER_PORT=3306,

master_user='repl',

master_password='123123' ,

MASTER_AUTO_POSITION=1;    

# 该参数会自动读取relaylog中的最后一个gtid号,来向主库申请日志, 如未从relaylog中读取到,则从第一个gtid开始同步

# 该参数值代表从哪个gtid号开始请求, 如从库是使用xbk物理恢复的,则此处值需要是恢复文件中最后一个gtid号+1

start slave;

二、再做主主

重启上述的动作就可以完成主主搭建(反向做复制关系)。

mysql> show master status;

mysql> show slave status \G;

Slave_IO_Running: Yes #I/O线程状态OK

Slave_SQL_Running: Yes #SQL线程状态OK

Seconds_Behind_Master: 0 #同步效率非常好,没有延迟

master1 #模拟产生数据

mysql> create database slave; #创建数据库

mysql> use slave; #选择数据库

mysql> create table a(a int); #创建表

mysql> insert into a values(1); #插入数据

mysql> insert into a values(2); #插入数据

master2 #查看数据同步状态(可以互相查看)

mysql> show databases; #查看当前有哪些数据库(主库新建的slave库同步过来了)

mysql> use slave; #选择数据库

mysql> select * from a; #查看a表数据

三、安装keepalived

本次安装采用yum的方式进行安装(两台主机都安装)

yum -y install keepalived

keepalived的启动与停止

service keepalived start

service keepalived stop

service keepalived restart

192.168.1.1配置文件:

! Configuration File for keepalived

global_defs {

  notification_email {

    acassen@firewall.loc

    failover@firewall.loc

    sysadmin@firewall.loc

  }

  notification_email_from Alexandre.Cassen@firewall.loc

  #smtp_server 127.0.0.1

  #smtp_connect_timeout 30

  router_id MYCAT_HA # 定义名称

}


#检测mycat脚本

vrrp_script chk_mycat_alive {

   script "/etc/keepalived/mysql_check.sh"    # 返回状态码为0表示正常,检测脚本为true;返回状态码非0表示异常,检测脚本为false

   interval 2                                 # 检测脚本执行的间隔,单位是秒

   weight 20

}


vrrp_instance VI_1 {

   state MASTER

   interface ens33  

   virtual_router_id 88  

   priority 100    

   advert_int 1  

   authentication {

       auth_type PASS

       auth_pass qwert

   }


   track_script {

       chk_mycat_alive            # 调用检测脚本

   }

   virtual_ipaddress {

       192.168.1.60/24            # 定义虚拟ip(VIP)

   }

}

192.168.1.2配置文件:

! Configuration File for keepalived

 

global_defs {

  notification_email {

    acassen@firewall.loc

    failover@firewall.loc

    sysadmin@firewall.loc

  }

  notification_email_from Alexandre.Cassen@firewall.loc

  #smtp_server 127.0.0.1

  #smtp_connect_timeout 30

  router_id MYCAT_HA

}


#检测mycat脚本

vrrp_script chk_mycat_alive {

   script "/etc/keepalived/mysql_check.sh"    # 返回状态码为1表示正常,检测脚本为true;返回状态码0表示异常,检测脚本为false

   interval 2                                 # 检测脚本执行的间隔,单位是秒

   weight 20

}


vrrp_instance VI_1 {

   state BACKUP

   interface ens33

   virtual_router_id 88

   priority 90    

   advert_int 1  

   authentication {

       auth_type PASS

       auth_pass qwer

   }

   track_script {

       chk_mycat_alive            # 调用检测脚本

   }

   virtual_ipaddress {

       192.168.1.60/24            # 定义虚拟ip(VIP)

   }

}

编写mysql_check.sh 服务拉起脚本(两个节点都设置)

#!/bin/bash

count=`ps -C mysqld --no-heading|wc -l`

time=$(date "+%Y-%m-%d %H:%M:%S")

if [ $count = 0 ]; then

   echo "$time : count=$count, mysql is not running..." >> /var/log/keepalived_check.log

   exit 1 # 返回1说明脚本非正常执行,mysql不在运行中

else

   echo "$time : count=$count, mysql is running..." >> /var/log/keepalived_check.log

   exit 0 # 返回0说明脚本正常执行,mysql正在运行中

fi

四、测试集群是否搭建成功

登录两台主机查看VIP地址在哪台机器,然后通过vip登录Mysql服务器

mysql -h 192.168.1.60 -uxtgss -pXtgss@123

附录 主从巡检脚本

#!/bin/bash

host="192.168.1.1"

port="3306"

userName="root"

password="123456"

dbname="mysql"

base='/usr/local/mysql'

##数据文件位置##

echo "================= mysql配置信息 ==============================="

echo "========= 基本配置信息 ==========="

lower_case_table_names="show variables like 'lower_case_table_names';"

lower_case_table_names_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${lower_case_table_names}")

echo "不区分大小写:" `echo ${lower_case_table_names_val} | cut -d' ' -f4`

_port="show variables like 'port';"

_port_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${_port}")

echo "端口:" `echo ${_port_val} | cut -d' ' -f4`

socket="show variables like 'socket';"

socket_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${socket}")

echo "socket的值:" `echo ${socket_val} | cut -d' ' -f4`

skip_name_resolve="show variables like 'skip_name_resolve';"

skip_name_resolve_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${skip_name_resolve}")

echo "域名解析skip_name_resolve:" `echo ${skip_name_resolve_val} | cut -d' ' -f4`

character_set_server="show variables like 'character_set_server';"

character_set_server_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${character_set_server}")

echo "数据库字符集character_set_server:" `echo ${character_set_server_val} | cut -d' ' -f4`

interactive_timeout="show variables like 'interactive_timeout';"

interactive_timeout_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${interactive_timeout}")

echo "交互式连接超时时间(mysql工具、mysqldump等)interactive_timeout(秒):" `echo ${interactive_timeout_val} | cut -d' ' -f4`

wait_timeout="show variables like 'wait_timeout';"

wait_timeout_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${wait_timeout}")

echo "非交互式连接超时时间,默认的连接mysql api程序,jdbc连接数据库等wait_timeout(秒):" `echo ${wait_timeout_val} | cut -d' ' -f4`

query_cache_type="show variables like 'query_cache_type';"

query_cache_type_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${query_cache_type}")

echo "查询缓存query_cache_type:" `echo ${query_cache_type_val} | cut -d' ' -f4`

innodb_version="show variables like 'innodb_version';"

innodb_version_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${innodb_version}")

echo "数据库版本:" `echo ${innodb_version_val} | cut -d' ' -f4`

trx_isolation="show variables like 'tx_isolation';"

trx_isolation_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${trx_isolation}")

echo "mysql5.6隔离级别trx_isolation:" `echo ${trx_isolation_val} | cut -d' ' -f4`

transaction_isolation="show variables like 'transaction_isolation';"

transaction_isolation_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${transaction_isolation}")

echo "隔离级别transaction_isolation:" `echo ${transaction_isolation_val} | cut -d' ' -f4`

datadir="show variables like '%datadir%';"

datadir_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${datadir}")

echo "mysql 数据文件存放位置:" `echo ${datadir_val} | cut -d' ' -f4`

echo "========= 连接数配置信息 ==========="

max_connections="show variables like 'max_connections';"

max_connections_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${max_connections}")

echo "最大连接数(max_connections):" `echo ${max_connections_val} | cut -d' ' -f4`

Max_used_connections="show status like 'Max_used_connections';"

Max_used_connections_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${Max_used_connections}")

echo "当前连接数(Max_used_connections):" `echo ${Max_used_connections_val} | cut -d' ' -f4`

max_connect_errors="show variables like 'max_connect_errors';"

max_connect_errors_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${max_connect_errors}")

echo "最大错误连接数(max_connect_errors):" `echo ${max_connect_errors_val} | cut -d' ' -f4`

echo "========= binlog配置信息 ==========="

sync_binlog="show variables like 'sync_binlog';"

sync_binlog_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${sync_binlog}")

echo "sync_binlog(0|1|n,查看是否采用双1模式):" `echo ${sync_binlog_val} | cut -d' ' -f4`

binlog_format="show variables like 'binlog_format';"

binlog_format_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${binlog_format}")

echo "binlog格式:" `echo ${binlog_format_val} | cut -d' ' -f4`

log_bin="show variables like 'log-bin';"

log_bin_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${log-bin}")

echo "binlog文件(log-bin):" `echo ${log_bin_val} | cut -d' ' -f4`

expire_logs_days="show variables like 'expire_logs_days';"

expire_logs_days_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${expire_logs_days}")

echo "binlog文件过期时间:" `echo ${expire_logs_days_val} | cut -d' ' -f4`

binlog_cache_size="show variables like 'binlog_cache_size';"

binlog_cache_size_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${binlog_cache_size}")

echo "binlog_cache_size:" `echo ${binlog_cache_size_val} | cut -d' ' -f4`

max_binlog_cache_size="show variables like 'max_binlog_cache_size';"

max_binlog_cache_size_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${max_binlog_cache_size}")

echo "max_binlog_cache_size:" `echo ${max_binlog_cache_size_val} | cut -d' ' -f4`

max_binlog_size="show variables like 'max_binlog_size';"

max_binlog_size_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${max_binlog_size}")

echo "binlog文件大小:" `echo ${max_binlog_size_val} | cut -d' ' -f4`

master_info_repository="show variables like 'master_info_repository';"

master_info_repository_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${master_info_repository}")

echo "master_info_repository(table|file,建议用table):" `echo ${master_info_repository_val} | cut -d' ' -f4`

relay_log_info_repository="show variables like 'relay_log_info_repository';"

relay_log_info_repository_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${relay_log_info_repository}")

echo "relay_log_info_repository(table|file,建议用table):" `echo ${relay_log_info_repository_val} | cut -d' ' -f4`

relay_log_recovery="show variables like 'relay_log_recovery';"

relay_log_recovery_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${relay_log_recovery}")

echo "relay_log_info_repository(建议开启):" `echo ${relay_log_recovery_val} | cut -d' ' -f4`

echo "========= GTID配置信息 ==========="

gtid_mode="show variables like 'gtid_mode';"

gtid_mode_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${gtid_mode}")

echo "是否开启gtid_mode:" `echo ${gtid_mode_val} | cut -d' ' -f4`

enforce_gtid_consistency="show variables like 'enforce_gtid_consistency';"

enforce_gtid_consistency_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${enforce_gtid_consistency}")

echo "enforce_gtid_consistency是否开启:" `echo ${enforce_gtid_consistency_val} | cut -d' ' -f4`

echo "(MySQL官方解释说当启用enforce_gtid_consistency功能的时候,MySQL只允许能够保障事务安全,并且能够被日志记录的SQL语句被执行,像create table … select 和 create temporary table语句,以及同时更新事务表和非事务表的SQL语句或事务都不允许执行)"

log_slave_updates="show variables like 'log_slave_updates';"

log_slave_updates_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${log_slave_updates}")

echo "级联复制是否开启log_slave_updates:" `echo ${log_slave_updates_val} | cut -d' ' -f4`

echo "======== innodb配置信息 ========="

innodb_data_home_dir="show variables like 'innodb_data_home_dir';"

innodb_data_home_dir_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${innodb_data_home_dir}")

echo "innodb_data_home_dir:" `echo ${innodb_data_home_dir_val} | cut -d' ' -f4`

innodb_buffer_pool_size="show variables like 'innodb_buffer_pool_size';"

innodb_buffer_pool_size_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${innodb_buffer_pool_size}")

echo "innodb_buffer_pool_size(不超过内存的75%):" `echo ${innodb_buffer_pool_size_val} | cut -d' ' -f4`

innodb_buffer_pool_instances="show variables like 'innodb_buffer_pool_instances';"

innodb_buffer_pool_instances_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${innodb_buffer_pool_instances}")

echo "innodb_buffer_pool_instances(innodb_buffer_pool_size小于8G实例个数建议为1):" `echo ${innodb_buffer_pool_instances_val} | cut -d' ' -f4`

innodb_log_file_size="show variables like 'innodb_log_file_size';"

innodb_log_file_size_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${innodb_log_file_size}")

echo "redo文件的大小innodb_log_file_size:" `echo ${innodb_log_file_size_val} | cut -d' ' -f4`

innodb_log_files_in_group="show variables like 'innodb_log_files_in_group';"

innodb_log_files_in_group_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${innodb_log_files_in_group}")

echo "redo文件的个数innodb_log_files_in_group:" `echo ${innodb_log_files_in_group_val} | cut -d' ' -f4`

innodb_flush_log_at_trx_commit="show variables like 'innodb_flush_log_at_trx_commit';"

innodb_flush_log_at_trx_commit_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${innodb_flush_log_at_trx_commit}")

echo "innodb_flush_log_at_trx_commit(0|1|2,跟sync_binlog双1):" `echo ${innodb_flush_log_at_trx_commit_val} | cut -d' ' -f4`

innodb_io_capacity="show variables like 'innodb_io_capacity';"

innodb_io_capacity_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${innodb_io_capacity}")

echo "innodb_io_capacity(机械硬盘200,固态2000,闪存20000):" `echo ${innodb_io_capacity_val} | cut -d' ' -f4`

transaction_isolation="show variables like 'transaction_isolation';"

transaction_isolation_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${transaction_isolation}")

echo "隔离级别transaction_isolation:" `echo ${transaction_isolation_val} | cut -d' ' -f4`

trx_isolation="show variables like 'tx_isolation';"

trx_isolation_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${trx_isolation}")

echo "mysql5.6隔离级别trx_isolation:" `echo ${trx_isolation_val} | cut -d' ' -f4`

innodb_max_undo_log_size="show variables like 'innodb_max_undo_log_size';"

innodb_max_undo_log_size_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${innodb_max_undo_log_size}")

echo "undo大小innodb_max_undo_log_size:" `echo ${innodb_max_undo_log_size_val} | cut -d' ' -f4`

innodb_undo_tablespaces="show variables like 'innodb_undo_tablespaces';"

innodb_undo_tablespaces_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${innodb_max_undo_log_size}")

echo "undo个数innodb_undo_tablespaces:" `echo ${innodb_undo_tablespaces_val} | cut -d' ' -f4`

echo "========= rep配置信息 ==========="

slave_parallel_type="show variables like 'slave-parallel-type';"

slave_parallel_type_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${slave_parallel_type}")

echo "slave复制模式:" `echo ${slave_parallel_type_val} | cut -d' ' -f4`

slave_parallel_workers="show variables like 'slave-parallel-workers';"

slave_parallel_workers_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${slave_parallel_workers}")

echo "slave并发复制:" `echo ${slave_parallel_workers_val} | cut -d' ' -f4`

echo "================= 内存配置情况 ==============================="

mem_dis_1="show variables like 'innodb_buffer_pool_size';"

mem_dis_1_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_1}")

mem_dis_1_val_1=`echo ${mem_dis_1_val} | cut -d' ' -f4`

mem_dis_1_val_2=`echo | awk "{print $mem_dis_1_val_1/1024/1024}"`

echo "InnoDB 数据和索引缓存:" $mem_dis_1_val_1

mem_dis_2="show variables like 'innodb_log_buffer_size';"

mem_dis_2_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_2}")

mem_dis_2_val_1=`echo ${mem_dis_2_val} | cut -d' ' -f4`

mem_dis_2_val_2=`echo | awk "{print $mem_dis_2_val_1/1024/1024}"`

echo "InnoDB 日志缓冲区:" $mem_dis_2_val_1

mem_dis_3="show variables like 'binlog_cache_size';"

mem_dis_3_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_3}")

mem_dis_3_val_1=`echo ${mem_dis_3_val} | cut -d' ' -f4`

mem_dis_3_val_2=`echo | awk "{print $mem_dis_3_val_1/1024/1024}"`

echo "二进制日志缓冲区:" $mem_dis_3_val_1

mem_dis_4="show variables like 'thread_cache_size';"

mem_dis_4_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_4}")

echo "连接线程缓存:" `echo $mem_dis_4_val | cut -d' ' -f4`

mem_dis_5="show variables like 'query_cache_size';"

mem_dis_5_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_5}")

echo "查询缓存:" `echo ${mem_dis_5_val} | cut -d' ' -f4`

mem_dis_6="show variables like 'table_open_cache';"

mem_dis_6_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_6}")

echo "表缓存:" `echo ${mem_dis_6_val} | cut -d' ' -f4`

mem_dis_7="show variables like 'table_definition_cache';"

mem_dis_7_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_7}")

echo "表定义缓存:" `echo ${mem_dis_7_val} | cut -d' ' -f4`

mem_dis_8="show variables like 'max_connections';"

mem_dis_8_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_8}")

echo "最大线程数:" `echo ${mem_dis_8_val} | cut -d' ' -f4`

mem_dis_9="show variables like 'thread_stack';"

mem_dis_9_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_9}")

echo "线程栈信息使用内存:" `echo ${mem_dis_9_val} | cut -d' ' -f4`

mem_dis_10="show variables like 'sort_buffer_size';"

mem_dis_10_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_10}")

echo "排序使用内存:" `echo ${mem_dis_10_val} | cut -d' ' -f4`

mem_dis_11="show variables like 'join_buffer_size';"

mem_dis_11_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_11}")

echo "Join操作使用内存:" `echo ${mem_dis_11_val} | cut -d' ' -f4`

mem_dis_12="show variables like 'read_buffer_size';"

mem_dis_12_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_12}")

echo "顺序读取数据缓冲区使用内存:" `echo ${mem_dis_12_val} | cut -d' ' -f4`

mem_dis_13="show variables like 'read_rnd_buffer_size';"

mem_dis_13_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_13}")

echo "随机读取数据缓冲区使用内存:" `echo ${mem_dis_13_val} | cut -d' ' -f4`

mem_dis_14="show variables like 'tmp_table_size';"

mem_dis_14_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_14}")

echo "临时表使用内存:" `echo ${mem_dis_14_val} | cut -d' ' -f4`

echo "================= QPS ==============================="

Questions1="show global status like 'Questions';"

Questions1_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${Questions1}")

sleep 1

Questions2="show global status like 'Questions';"

Questions2_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${Questions2}")

echo "QPS:$((`echo ${Questions2_val} | cut -d' ' -f4`-`echo ${Questions1_val} | cut -d' ' -f4`))"

echo "================= TPS ==============================="

Com_commit="show  global status like 'Com_commit';"

Com_commit_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${Com_commit}")

Com_rollback="show global status like 'Com_rollback';"

Com_rollback_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${Com_rollback}")

echo "TPS:" $((`echo ${Com_commit_val} | cut -d' ' -f4` + `echo ${Com_rollback_val} | cut -d' ' -f4`))

##缓存命中率##

echo "================= 缓存命中情况 ==============================="

cache_hits="show global status like 'QCache_hits';"

hits=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${cache_hits}")

hits_val=`echo ${hits} | cut -d' ' -f4`

echo "缓存命中次数:" ${hits_val}

cache_not_hits="show global status like  'Qcache_inserts';"

not_hits=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${cache_not_hits}")

not_hits_val=`echo ${not_hits} | cut -d' ' -f4`

echo "缓存未命中次数:" ${not_hits_val}

cache_hits_rate_1=$(($hits_val - $not_hits_val))

cache_hits_rate_2=`echo | awk "{print $cache_hits_rate_1/$hits_val * 100}"`

echo "缓存命中率:" ${cache_hits_rate_2} "%"

echo "================= 主从复制 ============================="

slave_parallel_type="show variables like 'slave-parallel-type';"

slave_parallel_type_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${slave_parallel_type}")

echo "slave复制模式:" `echo ${slave_parallel_type_val} | cut -d' ' -f4`

slave_parallel_workers="show variables like 'slave-parallel-workers';"

slave_parallel_workers_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${slave_parallel_workers}")

echo "slave并发复制:" `echo ${slave_parallel_workers_val} | cut -d' ' -f4`

${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "show master status\G;"

${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "show slave status\G;"

echo "================= 半同步复制 ==============================="

${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "show variables like '%semi%';"

echo "================= 慢查询 ==============================="

slow_query_log_file=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "show variables like 'slow_query_log_file';"|grep 'slow'|awk '{print $2}')

slow_query_log=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "show variables like 'slow_query_log';"|grep 'slow'|awk '{print $2}')

long_query_time=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "show variables like 'long_query_time';"|grep 'long_query_time'|awk '{print $2}')

log_queries_not_using_indexes=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "show variables like 'log_queries_not_using_indexes';"|grep 'log_queries_not_using_indexes'|awk '{print $2}')

if [ ${slow_query_log} == "ON" ];then

echo "慢查询状态(slow_query_log):${slow_query_log} ;long_query_time(s) : ${long_query_time};log_queries_not_using_indexes: ${log_queries_not_using_indexes};慢查询top10,如下:"

mysqldumpslow -s c -t 10 ${slow_query_log_file};

else

   echo "慢查询状态(slow_query_log):${slow_query_log} ,未开启慢查询。"

fi

##等待事件##

echo "================= 数据库大小 ==============================="

${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "SELECT

table_schema,

sum( data_length )/ 1024 / 1024 / 1024 AS data_length,

sum( index_length )/ 1024 / 1024 / 1024 AS index_length,

sum( data_length + index_length )/ 1024 / 1024 / 1024 AS sum_data_index

FROM

information_schema.TABLES

WHERE

table_schema NOT IN ( 'mysql', 'information_schema', 'performance_schema', 'sys' )

GROUP BY

table_schema;"

echo "================= 数据碎片 ==============================="

${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "SELECT

TABLE_SCHEMA,

TABLE_NAME,

ENGINE,

concat( splinter, 'G' ) '碎片(G)'

FROM

(

SELECT

TABLE_SCHEMA,

TABLE_NAME,

ENGINE,

ROUND(( DATA_LENGTH + INDEX_LENGTH - TABLE_ROWS * AVG_ROW_LENGTH )/ 1024 / 1024 / 1024 ) splinter

FROM

information_schema.TABLES

WHERE

TABLE_TYPE = 'BASE TABLE'

) a

WHERE

splinter > 1

ORDER BY

splinter DESC;"

echo "================= 锁查询 ==============================="

${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "SELECT

r.trx_isolation_level,

r.trx_id waiting_trx_id,

r.trx_mysql_thread_id waiting_trx_thread,

r.trx_state waiting_trx_state,

lr.lock_mode waiting_trx_lock_mode,

lr.lock_type waiting_trx_lock_type,

lr.lock_table waiting_trx_lock_table,

lr.lock_index waiting_trx_lock_index,

r.trx_query waiting_trx_query,

b.trx_id blocking_trx_id,

b.trx_mysql_thread_id blocking_trx_thread,

b.trx_state blocking_trx_state,

lb.lock_mode blocking_trx_lock_mode,

lb.lock_type blocking_trx_lock_type,

lb.lock_table blocking_trx_lock_table,

lb.lock_index blocking_trx_lock_index,

b.trx_query blocking_query

FROM

information_schema.innodb_lock_waits w

INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id

INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_schema.innodb_locks lb ON lb.lock_trx_id = w.blocking_trx_id

INNER JOIN information_schema.innodb_locks lr ON lr.lock_trx_id = w.requesting_trx_id \G;"

echo "================= 等待事件 ==============================="

top_event_10="select event_name, count_star, sum_timer_wait from performance_schema.events_waits_summary_global_by_event_name where count_star > 0 order by sum_timer_wait desc limit 10;"

echo "等待事件 TOP 10:"

${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${top_event_10}"

echo "==================最近一周的错误日志 =========================="

_time=$(date -d '6 days ago' +%Y-%m-%d)\|$(date -d '5 days ago' +%Y-%m-%d)\|$(date -d '4 days ago' +%Y-%m-%d)\|$(date -d '3 days ago' +%Y-%m-%d)\|$(date -d '2 days ago' +%Y-%m-%d)\|$(date -d '1 days ago' +%Y-%m-%d)\|$(date -d '0 days ago' +%Y-%m-%d)

log_error=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "show variables like 'log_error';"|grep  'log_error'|awk '{print $2}')

#grep -i -E 'error' /home/logs/mysql/mysqld.err* | grep -E '2024-01-28|2024-02-14'

grep -i -E "error" ${log_error}| grep -E "${_time}"

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
运维 容灾 关系型数据库
MySQL高可用方案--Xenon全解
MySQL高可用方案--Xenon全解
|
2月前
|
负载均衡 算法 关系型数据库
MySQL集群如何实现负载均衡?
【8月更文挑战第16天】MySQL集群如何实现负载均衡?
76 6
|
2月前
|
存储 负载均衡 关系型数据库
MySQL集群
【8月更文挑战第16天】MySQL集群
36 5
|
2月前
|
SQL 负载均衡 关系型数据库
*配置MySQL集群
【8月更文挑战第16天】*配置MySQL集群
36 2
|
2月前
|
SQL 关系型数据库 MySQL
Nacos 1.2.1 集群搭建(二)MySQL、cluster 配置
Nacos 1.2.1 集群搭建(二)MySQL、cluster 配置
52 1
|
2月前
|
SQL 关系型数据库 MySQL
orchestrator搭建mysql高可用
orchestrator搭建mysql高可用
22 0
|
2月前
|
安全 关系型数据库 MySQL
【MySQL】Orchestrator最简单的 mysql 高可用方案最细细细细~
【MySQL】Orchestrator最简单的 mysql 高可用方案最细细细细~
|
3月前
|
运维 关系型数据库 MySQL
面试题MySQL问题之MySQL集群的单点失效如何解决
面试题MySQL问题之MySQL集群的单点失效如何解决
98 2
|
3月前
|
缓存 监控 关系型数据库
MySQL PXC 集群死锁分析案例
前不久一个系统死锁导致部分业务受到影响,今次补上详细的节点日志分析过程。
63 1
|
2月前
|
缓存 关系型数据库 MySQL
MySQL调优秘籍曝光!从索引到事务,全方位解锁高可用秘诀,让你的数据库性能飞起来!
【8月更文挑战第6天】MySQL是顶级关系型数据库之一,其性能直接影响应用的高可用性与用户体验。本文聚焦MySQL的高性能调优,从索引设计到事务管理,逐一解析。介绍如何构建高效索引,如联合索引`CREATE INDEX idx_order_customer ON orders(order_id, customer_id);`,以及索引覆盖查询等技术。
68 0

推荐镜像

更多
下一篇
无影云桌面