Percona-toolkit installation and introduction

MySQL database is one of the best lightweight and open-source databases. Its features, management, and robustness are quite different from those of Oracle. Therefore, many functions are derived from powerful third parties.

MySQL database is one of the best lightweight and open-source databases. Its features, management, and robustness are quite different from those of Oracle. Therefore, many functions are derived from powerful third parties.

MySQL database is one of the best lightweight and open-source databases. Its features, management, and robustness are quite different from those of Oracle. Therefore, there are many powerful third-party derivative products, such as percona-toolkit and XtraBackup. Percona-toolkit is a collection of advanced command line tools. It allows you to view summary information of the current service, disk detection, analysis of slow query logs, search for duplicate indexes, and implement table synchronization. This tool kit is rare for DBAs and O & M personnel. This article briefly describes how to install the tool and its Overview.

1. Main Features of percona-toolkit
Verify MySQL replication integrity by checking master and replica data consistency
Efficiently archive rows
Find duplicate indexes
Summarize MySQL servers
Analyze queries from logs and tcpdump
Collect vital system information when problems occur

2. installation requirements and steps
Download link:
Requirement
* Perl v5.8 or newer
* Bash v3 or newer
* Core Perl modules like Time: HiRes

# Perl -- version | head-2 # Check the perl version
# Bash -- version # Check the bash version

Quick installation steps (the process is omitted by default in the/usr/local/bin path)
# Tar zxvf percona-toolkit- .Tar.gz
# Cd percona-toolkit-
# Perl Makefile. PL (install perl Makefile. pl prefix =$ {HOME} in a non-default directory })
# Make
# Make test
# Make install

3. Introduction to main tools
If the path is not specified for non-source code installation or source code installation, all pt-related tools are located in the/usr/bin directory by default and start with pt.
To obtain help information about the command line, enter the command line and -- hlep at the shell prompt. For example:/usr/bin/pt-upgrade -- help
# Ls-hltr/usr/bin/pt -*
Pt-upgrade
# This command is mainly used to compare SQL Execution differences between different mysql versions. It is usually used for comparison before upgrade.
# The results, errors, and warnings of SQL files or separate SQL statements executed on each server are generated.

Pt-online-schema-change
# This function allows you to change the table structure online without locking the original table or blocking the DML operation of the original table.
# This feature is similar to Oracle's dbms_redefinition online redefinition table.

Pt-mysql-summary
# Generate a detailed configuration and sataus information for the connected mysql Server
# The configuration file of the current instance is also provided at the end

Pt-mext
# View the information of multiple samples of show global status in parallel.
# Pt-mext will execute the COMMAND you specified, read a row of results each time, save the content separated by empty rows to a temporary file, and finally view the results in parallel with these temporary files.

Pt-kill
# Kill the mysql statement that meets the specified conditions

Pt-ioprofile
# The principle of pt-ioprofile is to append a strace process to a pid for IO analysis.

Pt-fingerprint
# Used to generate a query fingerprint. This tool is used to generate queryID for SQL queries, and IDs in pt-query-digest.
# Similar to SQL _ID in Oracle, it involves variable binding and literal volume.

Pt-find
# Use and query the mysql table and execute the specified command, similar to the find command

Pt-fifo-split
# Simulate a cut file and pass it to the first-in-first-out queue through a pipe without actually cutting the file

Pt-deadlock-logger
# Monitors deadlocks on the mysql server and outputs them to log files. The logs contain detailed information such as the deadlock occurrence time, deadlock thread id, deadlock transaction id, and deadlock occurrence time.

Pt-archiver
# Archive the records of tables in the mysql database to another table or file
# This tool only archives old data and has almost no impact on OLTP queries of online data.
# You can insert data into other tables on another server or write data to a file to import data using the load data infile command.

Pt-agent
# A client proxy Tool Based on Percona Cloud

Pt-visual-explain
# Used to format the explain output

Pt-variable-advisor
# It is used to analyze some possible problems with mysql system variables. You can evaluate whether the relevant parameter settings are correct.

Pt-stalk
# It is used to collect information related to mysql database faults for subsequent diagnosis and handling.

Pt-slave-delay
# This parameter is used to set the time interval between the slave server and the master server.
# This command line sets a time lag from the specified time of the master by starting and stopping the replication SQL thread.

Pt-sift
# Used to browse the files generated by pt-stalk.

Pt-show-grants
# Output all user permissions of the current instance, which can be used to recreate users during database migration.

Pt-query-digest
# It is used to analyze slow query logs of the mysql server and format the output for viewing and analyzing.

Pt-pmp
# Execute the aggregated GDB stack trace for the query program. The advanced stack trace is used to summarize the tracing information.

Pt-index-usage
# Read query statements from log files and analyze how the current index is used.
# After the analysis is completed, a report about the index that has not been used for query is generated. This report can be used to analyze the report and consider removing useless indexes.

Pt-heartbeat
# Monitors the latency of the mysql replication architecture.
# It mainly updates a timestamp of the specified table through the -- update thread on the master database, check the timestamp of the master database update from the database -- monitor thread or -- check thread and compare it with the current system time to obtain the latency value.

Pt-fk-error-logger
# Record error messages related to foreign keys to logs or tables.

Pt-duplicate-key-checker
# This function is used to find duplicate indexes and Foreign keys from the mysql table. This tool will list duplicate indexes and Foreign keys.
# You can also generate the corresponding drop index statement.

Pt-diskstats
# Similar to iostat, this tool prints disk io statistics, but it is interactive and more detailed than iostat. Data collected from remote machines can be analyzed.

Pt-config-diff
# Compare mysql configuration files and server Variables
# At least two configuration sources need to be specified, which can be used for migration or comparison of pre-and post-upgrade configuration files

Pt-align
# Formatting output

Pt-slave-find
# Connect to the mysql master server and find all its slaves, and then print the hierarchical relationship of all slave servers.

Pt-table-checksum
# Used to verify the consistency of mysql replication.
# This tool is mainly used to efficiently find data differences. If there are differences, you can solve them through pt-table-sync.

Percona-toolkit: pt-summary, pt-mysql-summary, pt-config-diff, and pt-variable-advisor