Introduction to common mysql database tools

Status Series

Mysql provides a series of commands to obtain the running status of the database. The most common commands are show status, show innodb status, show processlist, and show mutex status. Through these commands, we can learn the database and storage engine I/O, memory, locks and other details.

References: http://wiki.babel.baidu.com/twiki/bin/view/Com/Ns/Mysqlstatus

Monitoring Tool-Mysqlreport

The various status commands listed above can be conveniently viewed in real time. If long-term monitoring is required, tools are required to sort and summarize the information. Currently, mysql monitoring includes mysqlreport, mysqlsla, and innotop, as well as Mysql-mon developed within the company. Among them, mysqlreport and mysql-mon belong to the status statistics type.

Data source:

[Mysql] show global status

[Mysql] Show Global Variables

[Mysql] Show Full Processlist

[System] vmstat/iostat/mpstat

Due to the complexity of the preceding parameters, the monitoring tool extracts important information and displays it in a convenient way and records it in logs.

Mysqlreport is a tool that supports periodic report generation.

The following information can be obtained from the mysqlreport:

1. Overall data: including total read/write requests, total data volume received/sent, and number of slow queries

2. DMS (data operation description): Distribution of various data operations (such as select and update), query and sort count distribution

3. Cache: including the cache hit rate, cache operation count, and ratio (query hit rate, insert count, and modification count)

4. Tables: including the number of table locks, the number of opened table files, the distribution of temporary table types, and the maximum number of connections

5. InnoDB related: including innodb data content read/write count, data page creation/read/write count, data row operation count (add, delete, modify, query statement distribution)

References:

Http://wiki.babel.baidu.com/twiki/bin/view/Com/Ns/MysqlTool

Http://hackmysql.com/mysqlreport

Monitoring Tool-Mysqlsla

Mysqlreport can help us understand the health status of MySQL and what type of Query MySQL is processing most of the time, but when we investigate slow queries, I want to know what Query does MySQL actually spend on CPU computing time? This problem must be known from the MySQL Log.

The data sources of mysql SLA are three types of logs provided by mysql: Binary log, General Log, and Slow Log.

Statistics on slow SQL statements, including average execution time, lock wait time, total number of result rows, and total number of scanned rows. The result format is as follows:

Project Description

Count SQL Execution times and percentage of slow log Count

Time execution Time, including the total Time, average Time, minimum, maximum Time, and Time as a percentage of the total slow SQL Time

95% of Time removes the fastest and slowest SQL statements, and the coverage rate accounts for 95% of the SQL Execution Time.

Lock Time Wait Lock Time

95% of Lock 95% slow SQL wait Lock time

Number of Rows sent result Rows, including average, minimum, and maximum

Number of Rows scanned by Rows examined

Which Database does the Database belong?

Users user, IP address, percentage of SQL executed by all Users

SQL statement after abstract Query

Query sample SQL statement

References: http://hackmysql.com/mysqlsla

Monitoring Tool-innotop

Innotop is a monitoring tool for the innodb engine.

Unlike several appeal tools, this is a tool similar to top, which can display various parameter changes of innodb in real time.

What's more powerful is the ability to view the execution status and deadlock information of each thread during execution;

You can enter command line parameters to select different modes to view internal thread information.

References: http://code.google.com/p/innotop/

Http://wiki.babel.baidu.com/twiki/bin/view/Com/Ns/MysqlTool

Monitoring Tool-Mysql-mon

Mysql-mon is an internal application monitoring tool developed by encyclopedia. Developed in C language, the database load is almost 0. Can be used:

Database system status monitoring \ Number of connections by source monitoring \ data volume, index Volume monitoring \ field Range Monitoring Database Synchronization monitoring

References: http://wiki.babel.baidu.com/twiki/bin/view/Com/Main/Mysql-mon

Table compression tool-myisampack

Myisampack is a table compression tool that uses a high compression rate to compress tables of the MyIsam storage engine so that the compressed tables occupy much less disk space than the compressed tables. However, the compressed table is also called a read-only table and cannot be used for DML operations.

References: http://dev.mysql.com/doc/refman/5.0/en/myisampack.html

Management Tool-mysqlamdin

Mysqladmin is a client program that executes management operations. It can be used to check the server configuration and current status, and create and delete databases.

References: http://dev.mysql.com/doc/refman/5.1/en/mysqladmin.html

Log Management Tool-mysqlbinlog

Because the log files generated by the server are saved in binary format, mysqlbinlog log is used if you want to check the text format of these files.

References: http://dev.mysql.com/doc/refman/6.0/en/mysqlbinlog.html

Table maintenance tool-mysqlcheck

The Mysqlcheck client tool can check and repair myisam tables, and optimize and analyze tables. In fact, it inherits the functions of check, repair, analyze, and optimize in mysql.

References: http://dev.mysql.com/doc/refman/5.1/en/mysqlcheck.html

Data export tool-mysqldump

The Mysqldump client tool is used to back up databases or migrate data between different databases. The backup content contains the SQL statement used to create or load a table.

References: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Data Import tool-mysqlimport

Mysqlimport is a client import tool used to import text files exported after mysqldump and the-T option.

References: http://dev.mysql.com/doc/refman/5.1/en/mysqlimport.html

Table Hot Backup Tool-mysqlhotcopy

Mysqlhotcopy is a perl script that uses lock tables, flush tables, cp, or scp to quickly back up databases. The myisam data table is hot-backed only in linux/unix.

References: http://dev.mysql.com/doc/refman/5.1/en/mysqlhotcopy.html

Database Object viewing tool-mysqlshow

Mysqlshow is a client object viewing tool used to quickly find databases, tables in databases, columns or indexes in tables.

References: http://dev.mysql.com/doc/refman/5.1/en/mysqlshow.html

Error code viewing tool-perror

Various Errors may occur during mysql usage. Some of these errors are caused by the operating system and some are caused by improper use of the storage engine. All these errors have an integer error code. Perror is used to explain the detailed meaning of these error codes.

References: http://dev.mysql.com/doc/refman/5.0/en/perror.html

Text replacement tool-replace

Replace is a tool that comes with mysql to Replace strings in files. It is similar to sed in linux, but its usage is simpler and more flexible.