监控mysql的几个脚本

如果您不太熟悉MySQL监控，开始可以看一看流行的监控软件。例如，下面列出了MySQL Cacti Plugin

Innodb Buffer Pool Activity

•Pages Created

•Pages Written

•Pages Read

Innodb Buffer Pool Pages

•Pool Size

•Database Pages

•Free Pages

•Modified Pages

Inoodb File I/O

•File Reads

•Files Writes

•Log Writes

•File Fsyncs

Innodb Pending I/O

•Aio Log Ios

•Aio Sync ios

•Buffer Pool Flushes

•Chkp Writes

•Ibuf Aio Reads

•Log Flushes

•Log Writes

•Normal Aio Reads

•Normal Aio Writes

Innodb Insert Buffer

•Inserts

•Merged

•Merges

Innodb Log

•Log Buffer Size

•Log Bytes Written

•Log Bytes Flushed

•Unflushed Log

Innodb Row Operations

•Rows Read

•Rows Deleted

•Rows Updated

•Rows Inserted

Innodb Semaphores

•Spin Rounds

•Spin Waits

•OS Waits

Innodb Transactions

•Innodb Transactions

•Current Transactions

•History List

•Read Views

MySQL Binary/Relay Logs

•Binlog Cache use

•Binlog Cache Disk Use

•Binary Log Space

•Relay Log Space

MySQL Command Counters

•Questions

•SELECT

•DELETE

•INSERT

•UPDATE

•REPLACE

•LOAD

•DELETE MULTI

•INSERT SELECT

•UPDATE MULTI

•REPLACE SELECT

MySQL Connections

•Max Connections

•Max Used Connections

•Aborted Clients

•Aborted Connects

•Threads Connected

•Connections

MySQL Files and Tables

•Table Cache

•Open Tables

•Open Files

•Opened Tables

MySQL Network Traffic

•Bytes Received

•Bytes Sent

MySQL Processlist

•State Closing Tables

•State Copying to Tmp Table

•State End

•State Freeing Items

•State Init

•State Locked

•State Login

•State Preparing

•State Reading From Net

•State Sending Data

•State Sorting Result

•State Statistics

•State Updating

•State Writing to Net

•State None

•State Other

MySQL Query Cache

•Queries In Cache

•Hits

•Inserts

•Not Cached

•Lowmem Prunes

MySQL Query Cache Memory

•Query Cache Size

•Free Memory

•Total Blocks

•Free Blocks

MySQL Replication

•Slave Running

•Slave Stopped

•Slave Lag

•Slave Open Temp Tables

•Slave Retried Transactions

MySQL Select Types

•Select Full Join

•Select Full Range Join

•Select Range

•Select Range Check

•Select Scan

MySQL Sorts

•Sort Rows

•Sort Range

•Sort Merge Passes

•Sort Scan

MySQL Table Locks

•Table Locks Immediate

•Table Locks Waited

•Slow Queries

MySQL Temporary Objects

•Created Tmp Tables

•Created Tmp Disk Tables

•Created Tmp Files

MySQL Threads

•Thread Cache Size

•Threads Created

如果您觉得文本对您有帮助，请打赏，谢谢。

新建执行脚本：mysql_install.sh，并添加执行权限

#!/bin/bash

#zhouyihua V0.1 2021.07.08

#For centos 7.0

# add DNS

echo "----------  Add DNS  --------"

echo "nameserver 8.8.8.8" >>/etc/resolv.conf

#stop firewall

systemctl stop firewalld

if [ $? -ne 0 ]then

    echo "Firewall stop failed"

else

    echo "Firewall stop success"

fi

#download mysql yum source

wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

#install mysql yum source

rpm -ivh mysql80-community-release-el7-3.noarch.rpm

#enable 5.7

sed -i '21s/enabled=0/enabled=1/g' /etc/yum.repos.d/mysql-community.repo

sed -i '21s/enabled=0/enabled=1/g' /etc/yum.repos.d/mysql-community.repo

#disable 8.0

sed -i '28s/enabled=1/enabled=0/g' /etc/yum.repos.d/mysql-community.repo

#install mysql

yum -y remove mariadb-libs

yum install -y mysql-community-server

#config mysql

echo "-----------  Config my.cnf  ----------"

sed -i '/\[mysqld\]/a\lower_case_table_names=1' /etc/my.cnf

sed -i '/\[mysqld\]/a\skip-grant-tables' /etc/my.cnf

#start mysql

echo "----------  Starting mysql service  ----------"

systemctl start mysqld.service

mysql -uroot -p123456  <<EOF

    use mysql

    update mysql.user set authentication_string=password('Oracle1234!@#$') where user='root'

EOF

echo "----------  Annotation skip grant tables in my.cnf    ----------"

sed -i 's/skip-grant-tables/\#skip-grant-tables/g' /etc/my.cnf

echo "----------  Restart mysql service  ----------"

systemctl restart mysqld.service

echo "----------  Grant all privileges  ----------"

mysql -uroot -p'Oracle1234!@#$'    --connect-expired-password -e "alter user 'root'@'localhost' identified by 'Oracle1234!@#$'"

mysql -u root -p'Oracle1234!@#$'  <<EOF

    use mysql

    GRANT ALL PRIVILEGES ON *.* TO 'iam'@'%' IDENTIFIED BY 'Oracle1234!@#$'

EOF

1、首先，选中一个数据库world，为了防止破坏其他的数据库表，这里新建一张数据库表t_people_info，如下图所示。

2、创建t_people_info数据库表，可以查看下表结构，使用SQL语句或直接点击进行查看表结构。

3、接着，在world数据库找到”事件“菜单项，鼠标右键”创建事件...“，如下图所示，输入事件名称，单击”创建“。

4、就会生成insert_data事件，如下图所示。

5、接着，编写insert_data事件，每10秒钟向t_people_info插入一条数据。

6、最后查看事件的状态，调用事件运行成功。

---