search
HomeSystem TutorialLINUXHow to use Linux to monitor MySQL and ensure database operation stability

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

Feb 10, 2024 pm 02:06 PM
linuxlinux tutoriallinux systemlinux commandshell scriptmysql developmentembeddedlinuxGetting started with linuxlinux learning

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.

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

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.

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

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

# mytop --prompt -d xuanyuan
密码:
How to use Linux to monitor MySQL and ensure database operation stability

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.

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

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.

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

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
How to use Linux to monitor MySQL and ensure database operation stability

通过本文的介绍,相信大家已经了解如何使用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!

Statement
This article is reproduced at:良许Linux教程网. If there is any infringement, please contact admin@php.cn delete
The Future of Linux Software: Will Flatpak and Snap Replace Native Desktop Apps?The Future of Linux Software: Will Flatpak and Snap Replace Native Desktop Apps?Apr 25, 2025 am 09:10 AM

For years, Linux software distribution relied on native formats like DEB and RPM, deeply ingrained in each distribution's ecosystem. However, Flatpak and Snap have emerged, promising a universal approach to application packaging. This article exami

What are the differences in how Linux and Windows handle device drivers?What are the differences in how Linux and Windows handle device drivers?Apr 25, 2025 am 12:13 AM

The differences between Linux and Windows in handling device drivers are mainly reflected in the flexibility of driver management and the development environment. 1. Linux adopts a modular design, and the driver can be loaded and uninstalled dynamically. Developers need to have an in-depth understanding of the kernel mechanism. 2. Windows relies on the Microsoft ecosystem, and the driver needs to be developed through WDK and signed and certified. The development is relatively complex but ensures the stability and security of the system.

Compare and contrast the security models of Linux and Windows.Compare and contrast the security models of Linux and Windows.Apr 24, 2025 am 12:03 AM

The security models of Linux and Windows each have their own advantages. Linux provides flexibility and customizability, enabling security through user permissions, file system permissions, and SELinux/AppArmor. Windows focuses on user-friendliness and relies on WindowsDefender, UAC, firewall and BitLocker to ensure security.

How does hardware compatibility differ between Linux and Windows?How does hardware compatibility differ between Linux and Windows?Apr 23, 2025 am 12:15 AM

Linux and Windows differ in hardware compatibility: Windows has extensive driver support, and Linux depends on the community and vendors. To solve Linux compatibility problems, you can manually compile drivers, such as cloning RTL8188EU driver repository, compiling and installing; Windows users need to manage drivers to optimize performance.

What are the differences in virtualization support between Linux and Windows?What are the differences in virtualization support between Linux and Windows?Apr 22, 2025 pm 06:09 PM

The main differences between Linux and Windows in virtualization support are: 1) Linux provides KVM and Xen, with outstanding performance and flexibility, suitable for high customization environments; 2) Windows supports virtualization through Hyper-V, with a friendly interface, and is closely integrated with the Microsoft ecosystem, suitable for enterprises that rely on Microsoft software.

What are the main tasks of a Linux system administrator?What are the main tasks of a Linux system administrator?Apr 19, 2025 am 12:23 AM

The main tasks of Linux system administrators include system monitoring and performance tuning, user management, software package management, security management and backup, troubleshooting and resolution, performance optimization and best practices. 1. Use top, htop and other tools to monitor system performance and tune it. 2. Manage user accounts and permissions through useradd commands and other commands. 3. Use apt and yum to manage software packages to ensure system updates and security. 4. Configure a firewall, monitor logs, and perform data backup to ensure system security. 5. Troubleshoot and resolve through log analysis and tool use. 6. Optimize kernel parameters and application configuration, and follow best practices to improve system performance and stability.

Is it hard to learn Linux?Is it hard to learn Linux?Apr 18, 2025 am 12:23 AM

Learning Linux is not difficult. 1.Linux is an open source operating system based on Unix and is widely used in servers, embedded systems and personal computers. 2. Understanding file system and permission management is the key. The file system is hierarchical, and permissions include reading, writing and execution. 3. Package management systems such as apt and dnf make software management convenient. 4. Process management is implemented through ps and top commands. 5. Start learning from basic commands such as mkdir, cd, touch and nano, and then try advanced usage such as shell scripts and text processing. 6. Common errors such as permission problems can be solved through sudo and chmod. 7. Performance optimization suggestions include using htop to monitor resources, cleaning unnecessary files, and using sy

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!