How to use Linux to monitor MySQL and ensure database operation stability

As one of the important relational databases in the industry, MySQL carries the core data of many enterprises. However, as the amount of data continues to increase, the stability and security of MySQL have also been put to more tests. In order to ensure the normal operation and data security of MySQL, it is particularly important to master certain Linux monitoring skills. This article will introduce how to use Linux to monitor MySQL, help you find and solve problems in time, and ensure the operational stability of the database.

There are many tools for monitoring mysql performance. Good tools are useful tools for diagnosing myql performance bottlenecks and troubleshooting servers. In daily work, we often use tools such as zabbix (with the help of scripts or plug-ins), Prometheus (commonly used in my work), MONyog, etc. But in some special cases, they don't always perfectly meet the common needs of MySQL developers or administrators, or they may not work properly in some cases. What's exciting is that the MySQL community has created various open source tools to fill these gaps. Today I share 4 command line tools to monitor the uptime, load and performance of mysql database in Linux.

一、Mytop

Mytop is one of the open source and free command line-based MySQL database monitoring tools, written by Jereme Zawodny using Perl language. Mytop runs in the terminal and displays statistics about threads, queries, slow queries, uptime, load, etc., very similar to the Linux monitoring command top. This indirectly helps administrators optimize and improve the performance of MySQl to handle a large number of requests and reduce server load.

mytop provides installation packages for a variety of systems, such as centos, arch linux, opensuse, etc.

1. Installation method

$ sudo apt install mytop    #Debian/Ubuntu
# yum install mytop         #RHEL/CentOS
# dnf install mytop         #Fedora 22+
# pacman -S mytop           #Arch Linux 
# zypper in mytop           #openSUSE

2. How to use Mytop to monitor MySQL/MariaDB

Mytop requires MySQL/MariaDB login credentials to monitor the database and connect to the server using the root username by default. You can specify the necessary options to connect to the database server at run time, either on the command line or in the file ~/.mytop.

Simply run the following command to start mytop and provide your MySQL/MariaDB root user password when prompted. This will connect to the test database by default.

# mytop --prompt
密码：

After entering the MySQL root password, you will see Mytop monitoring as shown below.

If you want to monitor a specific database, just add option -d, as shown in the figure below:

# mytop --prompt -d xuanyuan
密码：

If you have a specific administrator for each database, you only need to specify the username and password to connect to the data.

mytop -u admin -p password_here -d tdb

There are still certain security risks in specifying clear text passwords in the command line interface. In order to avoid such security risks,* we can use the configuration file ~/.mytop to specify options for connecting to the database.

# vi ~/.mytop
添加以下选项：
user=root 
pass=password_here 
host=localhost 
db=test 
delay=4 
port=3306 
socket=

2.mtop

mtop (MySQL top) is another similar open source, command line-based real-time MYSQL Server monitoring tool. It is written in Perl language and displays results similar to mytop. mtop monitors the MySQL queries that take the most time to complete and terminates those long-running queries after a certain specified time.

In addition, mtop has query optimizer information for running queries and terminated queries. It also displays server statistics, configuration information and some useful tuning tips to optimize and improve MySQL performance. But unfortunately mtop does not get Actively maintained and may not run on newly installed versions of MySQL.

Function:

Show real-time MySQL server queries.

Provide MySQL configuration information.

Display the zoom function of the process query.

Provide query optimizer information for queries and "kill" queries.

Provide MySQL tuning tips.

Ability to save output in .mtoprc configuration file.

Provides system administrator recommendation page (‘ T ‘).

Add queries/sec to main title.

Add per second information to the statistics screen.

3. Innotop

Innotop is another good command line-based mysql monitoring tool, which is used to monitor local and remote MySQL servers running under the InnoDB engine. Innotop includes many features and comes with different types of modes/options, which help us monitor various aspects of MySQL performance to find out what is going wrong with the MySQL server.

1. Install Innotop (MySQL monitoring)

By default, innotop packages are not included in Linux distributions, such as RHEL, CentOS, Fedora, and Scientific Linux. You need to install it by enabling the third-party epel repository and using the yum command as shown in the image below:

# yum install innotop

要启动innotop，只需在命令行中键入“ innotop ”并分别指定选项-u(用户名)和-p(密码)，然后按 Enter。

# innotop -u root -p 'tcm1nt'

2、Innotop 帮助

按“ ?” 获取命令行选项和用法的摘要。

切换到不同的模式：
   A Dashboard I InnoDB I/O Info Q Query List 
   B InnoDB Buffers K InnoDB Lock Waits R InnoDB Row Ops 
   C 命令摘要 L Locks S Variables & Status 
   D InnoDB Deadlocks M Replication Status T InnoDB Txns 
   F InnoDB FK Err O 打开表 U 用户统计
操作：
   d 更改刷新间隔 p 暂停 innotop 
   k 终止查询连接 q 退出 innotop 
   n 切换到下一个连接 x 终止查询
其他：
 TAB 切换到下一个服务器组/快速过滤您看到的内容
   ！显示许可证和保修 = 切换聚合
   # 选择/创建服务器组 @ 选择/创建服务器连接
   $ 编辑配置设置 \ 清除快速过滤器
按任意键继续

四、 mysqladmin

mysqladmin是一个默认的命令行 MySQL 客户端，预装在 MySQL 软件包，用于执行管理操作，例如监控进程、检查服务器配置、重新加载权限、当前状态、设置 root 密码、更改 root 密码、创建/删除数据库等。

要检查 mysql 状态以及正常运行时间，请从终端运行以下命令，请确保你有root的权限。

[root@localhost ~]# mysqladmin -u root -p version

通过本文的介绍，相信大家已经了解如何使用Linux监控MySQL，及时发现并解决问题，保障数据库的稳定性和安全性。在今后的实际操作中，希望大家能够灵活运用本文所介绍的监控工具和技巧，让MySQL在业务中发挥更大的价值，为企业的发展壮大保驾护航。

The above is the detailed content of How to use Linux to monitor MySQL and ensure database operation stability. For more information, please follow other related articles on the PHP Chinese website!