To optimize the configuration of MySQL, you must first identify the performance bottleneck of MySQL. The report output by show status is the reference data used to calculate the performance bottleneck. Unlike show status, mysqlreport does not list data in a simple way. Instead, it integrates and computes the reference data and organizes them into optimization reference points, then, DBA can adjust the value of the optimization reference point and the measurement standard of the point. This articleArticleIt does not analyze the report meaning of mysqlreport, nor describe the calculation formula and principle of the optimization reference point. It only briefly describes the usage method. Further in-depth analysis. Mysqlreport homepage and
Web site: http://hackmysql.com/mysqlreport
Download: http://hackmysql.com/scripts/mysqlreport-3.5.tgz Mysqlreport installation is extremely simple, download the above documentation, use tar-xvf mysqlreport-3.5.tar decompress, in the current directory to generate a folder mysqlreport-3.5, mysqlreport under this folder is executableProgram.
Note: mysqlreport is a Perl script and uses DBI and DBD: mysql. It must be correctly installed before it can be used properly! Mysqlreport command line option Parameters Linux-8tpn:/home/Kevin/perl # Perl mysqlreport -- help mysqlreport v3.5 Apr 16 2008 mysqlreport makes an easy-to-read Report of important MySQL Status values. command line options (abbreviations work ): -- User user connect to MYSQL as user -- password pass use pass or prompt for MySQL user's password -- Host address connect to MySQL at address -- Port port connect to MySQL at port -- Socket socket connec T to MySQL at socket -- no-mycnf don't read ~ /. My. CNF -- infile File Read Status values from file instead of MySQL -- OUTFILE file write report to file -- Email Address Email report to address (doesn't work on Windows) -- flush-status issue flush status; after getting current values -- relative x generate relative reports. if X is an integer, reports are live from the MySQL server X seconds apart. if X is a list of infiles (file1 file2 etc .), reports are generated from the infiles in the order that they are given. -- Report-count n collect N number of live relative reports (default 1) -- detach fork and detach from Terminal (run in background) -- help prints this -- debug print debugging information visit http://hackmysql.com/mysqlreport for more information.
| Option |
Explanation |
| --User |
Username used to connect to MySQL |
| --Password |
Password used to connect to MySQL. If this option is displayed on the command line but no parameters are provided, mysqlreport will prompt you to enter the password after you press Enter. |
| --Host |
MySQL server address |
| --Port |
MySQL Server Development Port |
| --Socket |
Path of the local MySQL Unix domain Interface |
| --No-mycnf |
This option instructs mysqlreport not to read ~ /. My. CNF. By default, this file is read. --User and-Password Are Always overwritten from ~ /. Parameters obtained in my. CNF |
| --Infile |
Read data from the status file instead of from the server |
| -OUTFILE |
print the report and write it to the file specified by this option. The internal mechanism of mysqlreport always writes the report to a temporary file first, and then prints the content in the temporary file to the screen. If the--OUTFILE option is specified, the temporary file is copied to the specified file. If option--email is specified, the temporary file is deleted. |
| --Email |
Print the report and send the result to the specified email address. This option requires the Sendmail program in the/usr/sbin/directory, so it cannot be used on Windows. /Usr/sbin/sendmail can be symbolic links to Qmail, or any other MTA program that can simulate sendmail-t. The mail source is mysqlreport. The subject is MySQL Status Report on host. The host is the host name of mysqlreport. It may be the read-host value. The default value is localhost. |
| --Flush-status |
Run the flush status command after the report is printed. |
| -relative |
mysqlreport generally generates status reports since MySQL was started. You can specify the-relative option to generate relevant reports since the previous report. If the option parameter is an integer, the program generates a status report every second after the specified number of seconds, the number of reports is specified by the-Report-count option. One report is generated by default. For example, if the value of-relative is 60, two reports are generated: the first basic report is generated immediately, and the second report is generated 60 seconds later. The value in the second report is related to the previous one. For example, if the former has a total of 10.00k queries and receives a new 1.00k query at the 60-second interval, the total number of queries for the latter is 1.00k rather than 11.00k. If the option parameter is a list of local files (like the--infile option), the program generates status reports in sequence according to the file sequence in the parameter. Files in the file list should be separated by spaces. It is very important to sort files in sequence based on the file generation time. Files generated earlier should be placed before the list. The first file must contain system variables, such as key_buffer_size and table_cache. Each file can have multiple "show status" results. Note: status files generated by "mysqladmin-r-I n extended" cannot be used because the-R parameter of mysqladmin has generated relative status values. mysqlreport first writes the status report to a temporary file. If the-relative parameter is an integer, mysqlreport displays where it writes the file. Then you can directly view the contents of these files to observe the server status |
| -Report-count |
Generate N reports. This option is valid only when the-relative option is enabled at the same time. Mysqlreport will automatically generate n + 1 report: the first basic report and the subsequent n relevant reports |
| -Detach |
This option allows mysqlreport to derive a new process and continue running from the terminal. After a new process is derived, mysqlreport reports the temporary file to which the result is written. This option requires one of--OUTFILE or--email. If neither of these options is provided, the generated temporary file will be deleted because after a new process is derived, the result cannot be printed on the terminal screen. This option makes more sense if used with the-relative option, so that mysqlreport can report information regularly without manual intervention. For example, you can use the following command to allow mysqlrepot to generate a report every hour and send the result to the specified mailbox: Perl mysqlreport--relative = 3600--Detach--Email = host@domain.com. When I was a child, mysqlreport sent a report via email, deleted temporary files, and ended cleanly |
| --Help |
Print help information |
| --Debug |
Print debugging information |
Simple Example of mysqlreport
1. Connect to the remote database 192.168.12.14
Perl mysqlreport--host = 192.168.12.14--user = db_user--Password = db_user_password 2. Connect to the local database through the Local UNIX domain interface file/data/mysql_data/MySQL. Sock
Perl mysqlreport--user = root-Password = root_password--socket =/data/mysql_data/MySQL. Sock 3. Write the output report to/data/mysql_data/report/mysqlreport.txt.
Perl mysqlreport--user = root-Password = root_password--socket =/data/mysql_data/MySQL. Sock--OUTFILE =/data/mysql_data/report/mysqlreport.txt |
