Percona-toolkit copy and monitor class operations for Mysql _mysql

Replication Class Tools
1. Pt-heartbeat
Function Introduction:
Monitor MySQL replication latency
Usage Introduction:
Pt-heartbeat [OPTION ...] [DSN]--update|--monitor|--check|--stop
Measure how much time is left behind the main MySQL or main PostgreSQL, and you can use this script to update the master or monitor replication, as shown in the following example.
Principle: Pt-heartbeat through real copy data to confirm MySQL and PostgreSQL replication latency, this avoids the replication mechanism dependence, thus can arrive accurate backward replication time, Consists of two parts: the first part of the--update thread in our Lord Pt-heartbeat updates a timestamp at a specified interval, The second part is the Pt-heartbeat--monitor thread or the--check line Chenglian to the heartbeat record (the previously updated timestamp) that is checked for replication, and compares the current system time to arrive at the time difference.
You can manually create heartbeat tables or add--create-table parameters and recommend using the memory engine. Table structure is:

CREATE TABLE Heartbeat (
 TS       varchar (num) NOT NULL,
 server_id    int unsigned not null PRIMARY KEY,
 file< C7/>varchar (255) default NULL,-  -show master STATUS
 position     bigint unsigned default NULL,--show Master ST ATUs
 relay_master_log_file varchar (255) DEFAULT NULL-  -show SLAVE STATUS
 exec_master_log_pos bigint Unsigned DEFAULT NULL-show SLAVE STATUS
);

Use examples:
Example 1: Create a background process to periodically update our Lord's Test Library Heartbeat table () By default is 1s, can be--interval specified, after execution will be a heartbeat table, test library for me to monitor the synchronization library:

pt-heartbeat-d test--update--user=root--password=zhang@123-h192.168.3.135--create-table–daemonize

Example 2: Monitoring the degree of lag of replication on the slave (will always be monitored):

pt-heartbeat-d Test--monitor--user=root--password=zhang@123-h192.168.3.92

Monitoring results are as follows:

0.00s [0.00s, 0.00s, 0.00s]
0.00s [0.00s, 0.00s, 0.00s] 0.00s [
0.00s, 0.00s, 0.00s] 0.00s [0.00s, 0.00s]
, 0.00s]
0.00s [0.00s, 0.00s, 0.00s]
0.00s [0.00s, 0.00s, 0.00s]

Example 3: Monitoring the degree of backwardness of replication on slave (monitoring exit at one time):

pt-heartbeat-d Test--check--user=root--password=zhang@123-h192.168.3.92

Example 4: Monitoring PostgreSQL need to add--dbi-driver Pg:

pt-heartbeat-d test--check--user=root--password=zhang@123-h192.168.3.92--dbi-driver Pg

2. Pt-slave-delay
Function Introduction:
Sets the specified time lag from the server to the primary server.
Usage Introduction:
Pt-slave-delay [OPTION ...] Slave-host [Master-host]
Principle: Set up from behind the main specified time by starting and stopping replication of SQL threads. The default is based on the location of the binary log from the relay log, so there is no need to connect to the primary server, if the IO process is not behind the main server too much, this check mode works well, if the network is smooth, general IO thread lag is usually the main millisecond level. is generally controlled by the--delay and--delay "+"--interval. --INTERVAL Specifies whether to check the frequency of starting or stopping a SQL thread from above, and the default is to check it 1 minutes.
Use examples:
Example 1: Make 1 minutes from behind, and test every 1 minutes, run for 10 minutes

Pt-slave-delay--user=root--password=zhang@123--delay 1m--run-time 10m--host=192.168.3.92

If you do not add the--run-time parameter, it will always execute.
Example 2: Causes 1 minutes from behind, and detects every 15 seconds, running for 10 minutes

Pt-slave-delay--user=root--password=zhang@123--delay 1m--interval 15s--run-time 10m--host=192.168.3.92

The results of the operation are as follows:

2012-05-20t16:34:50 slave running 0 seconds behind
2012-05-20t16:34:50 STOP slave until 2012-05-20t16:35:50 at master Position mysql-bin.000032/4392054
2012-05-20t16:35:05 Slave stopped at master position mysql-bin.000032/4397124
2012-05-20t16:35:20 slave stopped at master position mysql-bin.000032/4402194
2012-05-20t16:35:35 Stopped at master position mysql-bin.000032/4407264
2012-05-20t16:35:50 no new binlog events
2012-05-20t16 : 36:05 START SLAVE until master 2012-05-20t16:35:05 mysql-bin.000032/4397124

3. Pt-slave-find
Function Introduction:
Find and print MySQL all from server replication level relationships
Usage Introduction:
Pt-slave-find [OPTION ...] Master-host
Principle: Connect the MySQL master server and look for all its from, then print out all the hierarchical relationships from the server.
Use examples:
Example 1: Find the master server for 192.168.3.135 MySQL has all the hierarchical relationships from:

Pt-slave-find--user=root--password=zhang@123--host=192.168.3.135

4. Pt-slave-restart
Function Introduction:
Monitor MySQL replication errors and attempt to reboot MySQL replication when replication stops
Usage Introduction:
Pt-slave-restart [OPTION ...] [DSN]
Monitors one or more MySQL replication errors and attempts to restart replication when stopped. You can specify skipped errors and run from to the specified log location.
Use examples:
Example 1: Monitoring 192.168.3.92 from, skipping 1 errors

Pt-slave-restart--user=root--password=zhang@123--host=192.168.3.92--skip-count=1

Example 2: Monitoring 192.168.3.92 from, skipping error code 1062 error.

Pt-slave-restart--user=root--password=zhang@123--host=192.168.3.92--error-numbers=1062

5. Pt-table-checksum
Function Introduction:
Check MySQL replication consistency
Usage Introduction:
Pt-table-checksum [OPTION ...] [DSN]
Working principle: Pt-table-checksum in the Lord execute check statements check the consistency of MySQL replication online, generate the Replace statement, and then pass the copy to the From, and then update the MASTER_SRC value via update. Determine whether replication is consistent by detecting values from the top this_src and MASTER_SRC.
Note: Run when you choose a business peak when you use it, because running can cause some of the table's records to be locked. Use--max-load to specify the maximum load condition, and if that load is reached, the pause runs. If inconsistent data is found, you can use the Pt-table-sync tool to fix it.
Note: Unlike the 1.0 version, the new version of Pt-table-checksum only needs to be performed on master.
Using the –explain parameter combined with the binary log to see how the script works, such as my test library has a table named Zhang, we grab the binary log to view the principle of the script:

REPLACE into ' Test ' checksums ' (db, TBL, Chunk, Chunk_index, Lower_boundary, Upper_boundary, this_cnt, THIS_CRC) SELECT ' Test ', ' Zhang ', ' 1 ', NULL, NULL, NULL, COUNT (*) as CNT, COALESCE (LOWER (CONV) (Bit_xor (CAST (CRC32 (' # ', ' id ', ' name ', CONCAT (ISNULL (' name '))] as UNSIGNED)), 0) as CRC from ' Test '. ' Zhang '/*checksum table*/;
UPDATE ' test '. ' checksums ' SET chunk_time = ' 0.000563 ', MASTER_CRC = ' 31012777 ', master_cnt = ' 4 ' WHERE db = ' test ' and TBL = ' Zhang ' and chunk = ' 1 '

From here you can clearly see the principle, the previous said, here is not to repeat.
Use examples:
Example 1: Compare Test database synchronization, and the results show all tables.

Pt-table-checksum--nocheck-replication-filters--databases=test--replicate=test.checksums-- Create-replicate-table--host=192.168.3.135--port 3306-uroot-pzhang@123

Parameter description: The first time to run the need to add--create-replicate-table parameters, if not add this will need to manually run the Add table structure of SQL, table structure SQL is as follows:

CREATE TABLE checksums (
  db       char () not   null,
  tbl      char (=) not   null,
  chunk     int Not     null,
  chunk_time   float      null,
  chunk_index  varchar (MB)   null,
  Lower_ Boundary text       null,
  upper_boundary text       null,
  THIS_CRC    char not   null,
  this _cnt    int not     null,
  MASTER_CRC   char ()     null,
  master_cnt   int       null,
  TS       timestamp not  NULL,
  PRIMARY KEY (db, TBL, chunk),
  INDEX ts_db_tbl (TS, DB, TBL)
) engine= InnoDB;

The--nocheck-replication-filters parameter is used because my MY.CNF is configured with parameters such as Replicate-ignore-db and replicate-wild-do-table. In addition, special attention needs to be paid to the database in which the executed checksums must be a synchronized database. I have been groping for a long time when I first started to use it, and there is no example on the website. Oh!
The results are as follows:

TS     ERRORS diffs ROWS CHUNKS skipped time TABLE
05-23t16:19:29 0  1    2  1 0 0.006
test.aaa 05-23t16 : 19:29 0  0    1  1 0 0.017 test.bbb
05-23t16:19:29 0 0 0 1  0 0.007 Test.category_part
05-23t16:19:31 0  0  233617  6 0 1.887 test.collect_data
05-23t16:19:34 0  0 250346  5 0 2.709 test.effective_user
05-23t16:19:34 0  1    1 1  0 0.008 test.heartbeat
05-23t16 : 19:39 0  0 1000000 0 5.353 Test.oss_pvinfo2

From the results, we can see that the TEST.AAA and test.heartbeat table diffs is not 0, then the two tables are not synchronized.

Example 2: Compare Test database synchronization to show only tables with inconsistent data (add--replicate-check-only parameter, this value to view checksums data does not compare tables again. Therefore, you need to run the above comparison statement before running this statement, otherwise it will not show the latest changes. ）。

Pt-table-checksum--nocheck-replication-filters--databases=test--replicate=test.checksums--replicate-check-only --lock-wait-timeout=120--host=192.168.3.135--port 3306--user=root--password=zhang@123

The results are as follows:

Differences on Localhost.localdomain
TABLE CHUNK cnt_diff crc_diff
chunk_index lower_boundary upper_boundary TEST.AAA 1 1 1 
test.heartbeat 1 0 1

As you can see from the results, only two tables with different steps are displayed.

6. Pt-table-sync
Function Introduction:
Efficient synchronization of MySQL table data
Usage Introduction:
Pt-table-sync [OPTION ...] DSN [DSN ...]
Principle: Always in the Lord to perform changes in the data, and then sync to the from, not directly changed to the data from the Lord, the implementation of changes in the Lord is based on our current data, will not change the Lord's data. Be careful to back up your data before you use it to avoid loss of data. It is best to change to--print or--dry-run to see what data is changed before executing execute.
Use examples:
Example 1: Synchronize 3.135 of the test library's AAA table to 192.168.3.92, you can use the--execute parameter to--print to see what will change, then the host must be master, otherwise it will be out of error.

Pt-table-sync--execute--user=root--password=zhang@123 h=192.168.3.135,d=test,t=aaa h=192.168.3.92

Example 2: Synchronize the main test database to 192.168.3.92 so that you have the same data from the top.

Pt-table-sync--execute--sync-to-master--user=root--password=zhang@123 h=192.168.3.92--database test

Example 3: Synchronize only the specified tables

Pt-table-sync--execute--sync-to-master--user=root--password=zhang@123 h=192.168.3.92,d=test,t=aaa

Example 4: Data synchronization based on pt-table-checksum results

Pt-table-sync--execute--replicate test.checksums--user=root--password=zhang@123 

Example 5: Consistent with the data from the master according to the Pt-table-checksum

Pt-table-sync--execute--replicate test.checksums--user=root--password=zhang@123--sync-to-master H=192.168.3.92,D =test,t=aaa

Monitoring Tools
1. Pt-deadlock-logger
Function Introduction:
Information on extracting and recording MySQL deadlock
Usage Introduction:
Pt-deadlock-logger [OPTION ...] Source_dsn
Collect and save the most recent deadlock information on MySQL, you can print the deadlock information directly and store the deadlock information in the database, and the deadlock information includes the server where the deadlock occurred, the time of the most recent deadlock, the deadlock thread ID, the transaction ID of the deadlock, how long the transaction took place at the time of the deadlock, and so much information. See the example below for details.
Use examples:
Example 1: Print the deadlock information for local MySQL

Pt-deadlock-logger--user=root--password=zhang@123 H=localhost–print

Example 2: Log The local MySQL deadlock information to the database table and print it out

Pt-deadlock-logger--user=root--password=zhang@123 h=localhost--print d=test,t=deadlocks

2. Pt-fk-error-logger
Function Introduction:
Extract and record MySQL foreign key error message
Usage Introduction:
Pt-fk-error-logger [OPTION ...] Source_dsn
Extract and save the most recent foreign key error message from the MySQL database by show INNODB status. You can use parameter control to print error messages directly or to store error messages in a table in a database.
Use examples:
I always reported the following error while running on the server:

Use the uninitialized value in concatenation (.) or string At/usr/bin/pt-fk-error-logger line 2045

I suspect there is a problem with this program, go back to a version to try or debug the program.

3. Pt-mext
Function Introduction:
View information about multiple samples of show GLOBAL status in parallel.
Usage Introduction:
Pt-mext [OPTIONS]--COMMAND
Principle: Pt-mext Executes the command you specify, and each time a row of results is read, the contents of the blank row are saved to a single temporary file, and finally the results are viewed in parallel with the temporary files.
Use examples:
Example 1: Perform a show GLOBAL STATUS every 10s and merge the results together to view

Pt-mext--Mysqladmin EXT-UROOT-PZHANG@123-I10-C3

4. Pt-query-digest
Function Introduction:
Analyze the query execution log and produce a query report for MySQL, PostgreSQL, memcached filtering, playback, or conversion statements.
Usage Introduction:
Pt-query-digest [OPTION ...] [FILE]
Parsing and analyzing MySQL log files
Use examples:
Example 1: Analyzing a local slow query file

Pt-query-digest--user=root--password=zhang@123/data/dbdata/localhost-slow.log

Example 2: Re-review the full query log and save the results to Query_review, note that the table structure of the Query_review table must be built first, and the table structure is as follows:

CREATE TABLE Query_review (
  checksum   BIGINT UNSIGNED not null PRIMARY KEY,
  fingerprint TEXT not NULL,
  Sam ple    TEXT not NULL,
  first_seen  datetime,
  last_seen  datetime,
  reviewed_by VARCHAR (20),
  reviewed_on DATETIME,
  comments   TEXT
);

The order is as follows:

Pt-query-digest--user=root--password=zhang@123--review h=localhost,d=test,t=query_review/data/dbdata/ Localhost-slow.log

5. Pt-trend
Function Introduction:
Data points in a set of time series are counted.
Usage Introduction:
Pt-trend [OPTION ...] [FILE ...]
Read a slow query log, and output statistics. You can also specify multiple files. Read the information directly from the standard input if you do not specify a file.
Use examples:
Example 1: Read local slow query log and output statistics

Pt-trend/data/dbdata/localhost-slow.log