Percona-Tookit工具包之pt-slave-find

Check
out the information on slaves.

  1 [root@zlm2 04:16:24 ~/sysbench-1.0/src/lua]
  2 #sysbench oltp_read_write.lua --mysql-host=192.168.1.101 --mysql-port=3306 --mysql-user=zlm --mysql-password=zlmzlm --mysql-db=sysbench --tables=1 --table-size=10000000 --mysql-storage-engine=innodb prepare
  3 sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
  4 
  5 Creating table 'sbtest1'...
  6 Inserting 10000000 records into 'sbtest1'
  7 FATAL: mysql_drv_query() returned error 1114 (The table 'sbtest1' is full) for query 'INSERT INTO sbtest1(k, c, pad) VALUES(5007652, '86766538515-66952496290-52637383744-17777505783-29917382722-84335317264-36504610739-38121689798-84219011968-91366385874', '49260963569-36115667021-10552248239-18067079911-61369170530'),(4980838, '54654185213-72432916652-68658723905-01905687478-16685611449-05853232015-51457796697-51819403372-03987006557-33172822697', '21128589837-49224626065-81618031353-10869323172-08902635377'),(4369690, '19346998374-76157809978-61295763131-66817961402-14155793123-89485702729-52643919933-37547938307-65477074083-42947658759', '48407396301-60012651284-13188093294-65156227928-43584415056'),(4980019, '70453863386-09471491416-32761370166-00808275356-79347375847-13151837625-86471458250-39802434455-19087793196-21946389164', '33264881266-44980565111-26757160090-86077734704-00045098929'),(4997783, '58799482920-02512874911-59302214059-00300283635-35103600246-26397800977-48778245550-17735174970-94350595573-16543920606', '23090791422-15031674988-38742619953-96110910554-14814557148'),(4996542, '02526238751-03201222067-54203245066-96888627735-91568973200-70159837175-05408478565-91790491503-65208127071-01416690185', '49867218536-45143413529-09551369789-59447916184-43851467884'),(5016589, '74498181715-26316533762-09669873675-78321331809-32282301673-46976991777-42037713521-13856469837-75059942943-62359417982', '17496574432-77585238967-84065009097-69416190626-30209633909'),(4994933, '71859258017-48270005429-16959639772-39718531870-78995154112-71510567312-49374130735-00512159149-93514688119-69243734179', '23940826906-68127513411-04170272492-93173723876-07024484156'),(5024203, '01612812086-88501262208-29821147265-27833323182-26465297118-67798980074-90884201988-32250326631-52571482980-27120623387', '32676071422-40807168337-23971263649-30743049902-70369274393'),(4162450, '82998769803-54845523696-25503288553-48015207428-64262352808-97661531385-74384257021-19880800516-86393457605-98507752399', '02796854361-18464776198-06166096636-53988338130-49624903182'),(4995444, '66477706887-39253236907-03545461775-18565950790-70596896493-96343527077-27384490842-67789533300-13229220655-04716282051', '93177901449-78610249192-43379190612-81683429736-42624094282'),(5014023, '00026114498-69425370048-46519646170-15138611515-26550011239-94540841776-52595856582-34808319037-14376366590-67052294983', '56614501773-95380362876-81940933213-48069389750-96896566709'),(5024261, '49006870590-81308017607-77748035537-80561018761-10903078210-73474291600-34790301926-33257007983-62587725038-62428934340', '19026821007-38039245675-09902375187-72268734965-98944641058'),(5024236, '10110443501-28024080051-93513912505-09437560828-34230239659-09523768623-23688243983-59021499845-46898378159-07771020838', '24997608907-31970368651-18989687877-75418790951-46453076386'),(5049980, '19429198356-67366238484-55380613354-35977328479-83088969020-14656225800-25216048033-45250663251-98396746788-21125067075', '71090143904-45428036794-81542588223-10154666659-75546296798'),(5471904, '75703864024-12238744221-17802200544-33445694372-23795426538-35115713085-36013837723-42595287843-50130126814-13689292194', '33729585352-83996219254-04756782645-33242342933-26629849933'),(5019788, '26736440482-52751355069-31401045555-46875459054-74471530241-80094262346-04701622365-49412060729-73926195317-10568100029', '94218352951-20508982927-74070061682-74623136939-21804519403'),(5021303, '68369236188-31813582854-91111181432-49899358000-41024394879-31154794562-68384652868-38150046317-87574937898-60778176522', '57835818598-56026673181-41314509499-62765615632-08938407644'),(5015123, '67124253129-45231369455-80389246483-30556743024-23531555287-47922585120-65610495252-46796320051-45675319664-05751522029', '17364348778-61672427639-08537678844-68212837883-00352572907'),(3676715, '05788144973-64579114586-22354309091-20803641999-44828403219-79251514115-97961584283-16337702597-51136491029-67923490682', '13053760861-60814974240-44320882636-73693584211-05836315795'),(5236763, '68612629880-77489830256-75974497057-76984695030-FATAL: `sysbench.cmdline.call_command' function failed: ./oltp_common.lua:230: db_bulk_insert_next() failed
  8 
  9 //Master
 10 [root@zlm2 04:33:54 ~/sysbench-1.0/src/lua]
 11 #df -h
 12 Filesystem               Size  Used Avail Use% Mounted on
 13 /dev/mapper/centos-root  8.4G  8.4G   68M 100% / //The disk space of master is full.
 14 devtmpfs                 488M     0  488M   0% /dev
 15 tmpfs                    497M     0  497M   0% /dev/shm
 16 tmpfs                    497M  6.6M  491M   2% /run
 17 tmpfs                    497M     0  497M   0% /sys/fs/cgroup
 18 /dev/sda1                497M  118M  379M  24% /boot
 19 none                      87G   80G  6.6G  93% /vagrant
 20 
 21 //Slave
 22 [root@zlm3 04:34:17 ~]
 23 #df -h
 24 Filesystem               Size  Used Avail Use% Mounted on
 25 /dev/mapper/centos-root  8.4G  8.4G   20K 100% / //The disk space of slave is full,too.
 26 devtmpfs                 488M     0  488M   0% /dev
 27 tmpfs                    497M     0  497M   0% /dev/shm
 28 tmpfs                    497M  6.5M  491M   2% /run
 29 tmpfs                    497M     0  497M   0% /sys/fs/cgroup
 30 /dev/sda1                497M  118M  379M  24% /boot
 31 none                      87G   80G  6.6G  93% /vagrant
 32 
 33 //Check the output logfile.
 34 [root@zlm2 04:18:21 ~]
 35 #tail monitor.log 
 36 1.00s [  6.40s,  1.65s,  0.55s ]
 37 0.00s [  6.26s,  1.65s,  0.55s ]
 38 0.99s [  6.13s,  1.65s,  0.55s ]
 39 1.99s [  6.00s,  1.66s,  0.55s ]
 40 2.99s [  5.86s,  1.67s,  0.56s ]
 41 3.99s [  5.73s,  1.68s,  0.56s ]
 42 4.99s [  5.59s,  1.70s,  0.57s ]
 43 6.00s [  5.46s,  1.72s,  0.57s ]
 44 4.01s [  5.29s,  1.73s,  0.58s ]
 45 4.99s [  5.16s,  1.75s,  0.58s ]
 46 
 47 //Long time later.
 48 
 49 [root@zlm2 04:59:24 ~]
 50 #tail monitor.log 
 51 1769.00s [ 1739.50s, 1619.50s, 1319.50s ]
 52 1769.99s [ 1740.50s, 1620.50s, 1320.50s ]
 53 1770.99s [ 1741.50s, 1621.50s, 1321.50s ]
 54 1771.99s [ 1742.50s, 1622.50s, 1322.50s ]
 55 1772.99s [ 1743.50s, 1623.50s, 1323.50s ]
 56 1773.99s [ 1744.50s, 1624.50s, 1324.50s ]
 57 1774.99s [ 1745.50s, 1625.50s, 1325.50s ]
 58 1775.99s [ 1746.50s, 1626.50s, 1326.50s ]
 59 1776.99s [ 1747.50s, 1627.50s, 1327.50s ]
 60 1778.00s [ 1748.50s, 1628.50s, 1328.50s ]
 61 
 62 [root@zlm2 05:02:54 ~]
 63 #tail monitor.log 
 64 2122.00s [ 2092.50s, 1972.50s, 1672.50s ]
 65 2123.00s [ 2093.50s, 1973.50s, 1673.50s ]
 66 2124.00s [ 2094.50s, 1974.50s, 1674.50s ]
 67 2125.00s [ 2095.50s, 1975.50s, 1675.50s ]
 68 2126.00s [ 2096.50s, 1976.50s, 1676.50s ]
 69 2127.00s [ 2097.50s, 1977.50s, 1677.50s ]
 70 2127.99s [ 2098.50s, 1978.50s, 1678.50s ]
 71 2129.00s [ 2099.50s, 1979.50s, 1679.50s ]
 72 2129.99s [ 2100.50s, 1980.50s, 1680.50s ]
 73 2131.00s [ 2101.50s, 1981.50s, 1681.50s ]
 74 
 75 (zlm@192.168.1.102 3306)[sysbench]>show slave status\G
 76 ERROR 2013 (HY000): Lost connection to MySQL server during query
 77 (zlm@192.168.1.102 3306)[sysbench]>show slave status\G
 78 ERROR 2006 (HY000): MySQL server has gone away
 79 No connection. Trying to reconnect...
 80 Connection id:    10
 81 Current database: sysbench
 82 
 83 //The slave has hung because of the lack of disk space.
 84 //The delay continuously increasing what can be seen in the logfile on master.
 85 
 86 #180719  4:31:55 server id 1013306  end_log_pos 2044211         Table_map: `sysbench`.`hb` mapped to number 108
 87 # at 2044211
 88 #180719  4:31:55 server id 1013306  end_log_pos 2044359         Update_rows: table id 108 flags: STMT_END_F
 89 ### UPDATE `sysbench`.`hb`
 90 ### WHERE
 91 ###   @1='2018-07-19T04:31:55.004000' /* VARSTRING(78) meta=78 nullable=0 is_null=0 */
 92 ###   @2=1013306 /* INT meta=0 nullable=0 is_null=0 */
 93 ###   @3='mysql-bin.000019' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
 94 ###   @4=2043294 /* LONGINT meta=0 nullable=1 is_null=0 */
 95 ###   @5=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
 96 ###   @6=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
 97 ### SET
 98 ###   @1='2018-07-19T04:31:55.004400' /* VARSTRING(78) meta=78 nullable=0 is_null=0 */
 99 ###   @2=1013306 /* INT meta=0 nullable=0 is_null=0 */
100 ###   @3='mysql-bin.000019' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
101 ###   @4=2043294 /* LONGINT meta=0 nullable=1 is_null=0 */
102 ###   @5=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
103 ###   @6=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
104 # at 2044359
105 #180719  4:31:55 server id 1013306  end_log_pos 2044386         Xid = 94022
106 COMMIT/*!*/;
107 
108 //The output above is the detail of updating heartbeat table "hb" on master.

 

Introduce

Examples

Check the details of differet
tables on slave(master won’t have these records).

 

 

Make data difference again by modify one record.

Example

 

Check data in table on slave.

Common
parameter

 

1 (root@localhost mysql3306.sock)[zlm]10:35:27>select * from mysql.tables_priv;                                                                        
2 +-----------+-------+---------------+------------+----------------------+---------------------+------------+-------------+
3 | Host      | Db    | User          | Table_name | Grantor              | Timestamp           | Table_priv | Column_priv |
4 +-----------+-------+---------------+------------+----------------------+---------------------+------------+-------------+
5 | localhost | mysql | mysql.session | user       | boot@connecting host | 2018-06-18 10:00:00 | Select     |             |
6 | localhost | sys   | mysql.sys     | sys_config | root@localhost       | 2018-06-13 04:11:40 | Select     |             |
7 +-----------+-------+---------------+------------+----------------------+---------------------+------------+-------------+
8 2 rows in set (0.00 sec)

 

 1 --check //Specify the check mode to slave delay.It only checks once then exits.
 2 --monitor //Specify the monitor mode to slave delay.
 3 --update //Specify the update mode on master.
 4 --check-read-only //It skips insert operations if the server is read-only status.
 5 --create-table //Create a heartbeat table.
 6 --table //Specify the heartbeat table(default "heartbeat").
 7 --daemonize //Run it as background process.
 8 --dbi-driver //Specify the drive of database(default "mysql",other value is "pg").
 9 --file //Specify a output file when using --monitor.
10 --log //Print all output to the file when using --daemonize
11 --master-server-id //Calculate delay from this master server id.
12 --interval //Specify the frequency to update or check the heartbeat table(default "1.0").
13 --run-time //Specify the period of time to run(default suffix is "s",if you set 60,it means 60s).
14 
15 Notice:
16 Specify at least one of --stop, --update, --monitor, or --check.
17 --update, --monitor, and --check are mutually exclusive.
18 --daemonize and --check are mutually exclusive.
1 (root@localhost mysql3306.sock)[zlm]09:30:03>select db,tbl,chunk,chunk_time,this_crc,this_cnt,master_crc,master_cnt,ts from checksums where this_cnt<>master_cnt;
2 +---------+-----------+-------+------------+----------+----------+------------+------------+---------------------+
3 | db | tbl | chunk | chunk_time | this_crc | this_cnt | master_crc | master_cnt | ts |
4 +---------+-----------+-------+------------+----------+----------+------------+------------+---------------------+
5 | mysql | proc | 1 | 0.001536 | 9e5a007c | 49 | 4e0f05d9 | 48 | 2018-06-22 09:29:17 |
6 | mysql | user | 1 | 0.001122 | 7de55b47 | 10 | 587dfc7 | 5 | 2018-06-22 09:29:17 |
7 | percona | checksums | 1 | 0.000952 | 22f7b633 | 25 | d162e2ce | 29 | 2018-06-22 07:31:42 |
8 +---------+-----------+-------+------------+----------+----------+------------+------------+---------------------+
9 3 rows in set (0.00 sec)

 

Introduce

 

 

*    pt-heartbeat is the very tool can be
used to monitor replication delay of MySQL or PostgreSQL.It provides a
way to update a master or to monitor a replica.If you do not speicify
the connection options,it will retrieve them in your default my.cnf
file.

*

1 [root@zlm2 10:35:09 ~]
2 #pt-table-sync --execute --replicate zlm.checksums --print --verbose h=192.168.1.101,P=3306,u=repl --ask-pass
3 Enter password for 192.168.1.101: 
4 # Syncing via replication P=3306,h=zlm3,p=...,u=repl
5 # DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
6 REPLACE INTO `mysql`.`tables_priv`(`host`, `db`, `user`, `table_name`, `grantor`, `timestamp`, `table_priv`, `column_priv`) VALUES ('localhost', 'sys', 'mysql.sys', 'sys_config', 'root@localhost', '2018-06-13 04:11:40', 'Select', '') /*percona-toolkit src_db:mysql src_tbl:tables_priv src_dsn:P=3306,h=192.168.1.101,p=...,u=repl dst_db:mysql dst_tbl:tables_priv dst_dsn:P=3306,h=zlm3,p=...,u=repl lock:1 transaction:0 changing_src:zlm.checksums replicate:zlm.checksums bidirectional:0 pid:4514 user:root host:zlm2*/;
7 #      0       1      0      0 Nibble    10:35:59 10:35:59 2    mysql.tables_priv
8 Unknown database 'percona' [for Statement "USE `percona`"] at line 2832 while doing percona.checksums on zlm3
9 #      0       0      0      0 0         10:35:59 10:35:59 1    percona.checksums

Procedure

 

 

Summary

 

*    pt-table-sync is a rather useful tool
because replication is commonly implemented everywhere now.On account of
replication delay or artificial error,slaves may turn out to be
inconsistent with master.It will help use efficiently solving the
problems.The machenism is to generate SQL statements and execute them on
the specified servers.Let’s see some details of it.

*

 1 [root@zlm1 08:41:57 ~]
 2 #pt-slave-find -h192.168.56.101 -P3306 -urepl -prepl4slave
 3 192.168.56.101
 4 Version         5.7.21-log
 5 Server ID       1013306
 6 Uptime          23:40:36 (started 2018-07-21T09:01:31) //It shows that the slave has been run almost 24 hours.
 7 Replication     Is a slave, has 0 slaves connected, is not read_only
 8 Filters         
 9 Binary logging  ROW
10 Slave status    0 seconds behind, running, no errors
11 Slave mode      STRICT
12 Auto-increment  increment 1, offset 1
13 InnoDB version  5.7.21
14 
15 [root@zlm1 08:42:07 ~]
16 #pt-slave-find -h192.168.56.102 -P3306 -urepl -prepl4slave
17 192.168.56.102
18 Version         5.7.21-log
19 Server ID       1023306
20 Uptime          08:21 (started 2018-07-22T08:33:54) //This was the newly added slave 10 minutes ago.
21 Replication     Is a slave, has 0 slaves connected, is not read_only
22 Filters         
23 Binary logging  ROW
24 Slave status    0 seconds behind, running, no errors
25 Slave mode      STRICT
26 Auto-increment  increment 1, offset 1
27 InnoDB version  5.7.21
1 pt-heartbeat [OPTIONS] [DSN] --update|--monitor|--check|--stop
 1 [root@zlm2 10:01:42 ~]
 2 #pt-table-checksum --no-check-binlog-format --replicate=zlm.checksums -h192.168.1.101 -P3306 -urepl --ask-pass
 3 Enter MySQL password: 
 4 Checking if all tables can be checksummed ...
 5 Starting checksum ...
 6             TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
 7 06-22T10:02:44      0      0        0          0       1       0   0.014 mysql.columns_priv
 8 06-22T10:02:44      0      0        2          0       1       0   0.016 mysql.db
 9 06-22T10:02:44      0      0        2          0       1       0   0.018 mysql.engine_cost
10 06-22T10:02:44      0      0        0          0       1       0   0.017 mysql.event
11 06-22T10:02:44      0      0        0          0       1       0   0.017 mysql.func
12 06-22T10:02:44      0      0       40          0       1       0   0.016 mysql.help_category
13 06-22T10:02:44      0      0      693          0       1       0   0.018 mysql.help_keyword
14 06-22T10:02:44      0      0     1406          0       1       0   0.017 mysql.help_relation
15 06-22T10:02:44      0      0      637          0       1       0   0.021 mysql.help_topic
16 06-22T10:02:44      0      0        0          0       1       0   0.018 mysql.ndb_binlog_index
17 06-22T10:02:44      0      0        1          0       1       0   0.016 mysql.plugin
18 06-22T10:02:44      0      0       48          0       1       0   0.018 mysql.proc
19 06-22T10:02:44      0      0        0          0       1       0   0.016 mysql.procs_priv
20 06-22T10:02:44      0      0        1          0       1       0   0.016 mysql.proxies_priv
21 06-22T10:02:44      0      0        6          0       1       0   0.014 mysql.server_cost
22 06-22T10:02:44      0      0        0          0       1       0   0.014 mysql.servers
23 06-22T10:02:44      0      0        2          0       1       0   0.016 mysql.tables_priv
24 06-22T10:02:44      0      0        0          0       1       0   0.016 mysql.time_zone
25 06-22T10:02:44      0      0        0          0       1       0   0.016 mysql.time_zone_leap_second
26 06-22T10:02:44      0      0        0          0       1       0   0.016 mysql.time_zone_name
27 06-22T10:02:44      0      0        0          0       1       0   0.016 mysql.time_zone_transition
28 06-22T10:02:44      0      0        0          0       1       0   0.016 mysql.time_zone_transition_type
29 06-22T10:02:44      0      0        5          0       1       0   0.016 mysql.user
30 06-22T10:02:44      0      0        6          0       1       0   0.015 sys.sys_config
31 06-22T10:02:44      0      0        1          0       1       0   0.015 zlm.test_ddl
32 06-22T10:02:44      0      0        2          0       1       0   0.015 zlm.test_ddl_no_pk
33 06-22T10:02:44      0      0        0          0       1       0   0.015 zlm.test_innodb
34 06-22T10:02:44      0      0        0          0       1       0   0.015 zlm.test_myisam

 

Execute pt-heartbeat to monitor delay of
slave.

 

1 pt-slave-find [OPTIONS] [DSN]
 1 [root@zlm2 03:52:42 ~]
 2 #pt-heartbeat --database=sysbench --create-table --table=hb --daemonize --log=/root/monitor.log -h192.168.1.102 -P3306 -uzlm -pzlmzlm --monitor
 3 
 4 [root@zlm2 03:54:02 ~]
 5 #ls -l|grep monitor
 6 -rw-r--r--   1 root root    2448 Jul 19 03:54 monitor.log
 7 
 8 [root@zlm2 03:54:24 ~]
 9 #cat monitor.log
10 *******************************************************************
11  Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
12  is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
13  possibly with SSL_ca_file|SSL_ca_path for verification.
14  If you really don't want to verify the certificate and keep the
15  connection open to Man-In-The-Middle attacks please set
16  SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
17 *******************************************************************
18   at /usr/bin/pt-heartbeat line 4438.
19 *******************************************************************
20  Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
21  is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
22  possibly with SSL_ca_file|SSL_ca_path for verification.
23  If you really don't want to verify the certificate and keep the
24  connection open to Man-In-The-Middle attacks please set
25  SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
26 *******************************************************************
27   at /usr/bin/pt-heartbeat line 4438.
28 
29 # A software update is available:
30 #   * The current version for Percona::Toolkit is 3.0.5
31 
32 0.00s [  0.00s,  0.00s,  0.00s ]
33 0.00s [  0.00s,  0.00s,  0.00s ]
34 0.00s [  0.00s,  0.00s,  0.00s ]
35 0.00s [  0.00s,  0.00s,  0.00s ]
36 0.00s [  0.00s,  0.00s,  0.00s ]
37 0.00s [  0.00s,  0.00s,  0.00s ]
38 0.00s [  0.00s,  0.00s,  0.00s ]
39 0.00s [  0.00s,  0.00s,  0.00s ]
40 0.00s [  0.00s,  0.00s,  0.00s ]
41 
42 [root@zlm2 03:54:32 ~]
43 #
44 
45 //The output will be write into the logfile sepcified by option "--log".
46 //The logfile will be continuously written unless you've specified th option "--run-time".
47 //It seems no delay between master and slave at all.We can use sysbench generate some transactions to observe the difference.

 

 

Preface

 

  • There’re merely several simple
    connection parameters you need to specify.
  • It’s
    convenient to get the information about slaves on master without
    executing any commands.
  • This
    tool can also be used to checkout the lag time of slaves.

 

 

*    pt-salve-find is a tool to find and
print the the hierarchy tree of MySQL slaves.It can provide us a simple
way to check out whether our slaves are working normally.

*

 

###Connect parameters.###
-h hostname
-P port
-u username
-p password
-S socket

###Object Parameters.###
-d databases
-t tables

###Frequently-used parameters.###
--execute -- Really make changes happen accoridng to the SQL statments.
--replicate -- sync differences just depend on checksums table generated by pt-table-checksum tool.
--sync-to-master -- only used to specify one slave to sync differences with master.
--replace -- Turn all the inser & update statments into repalce.

###Output parameters.###
--verbose -- Show details of SQL statements.
--print -- Print all the relevent differences.

###Other parameters.###
--dry-run -- Don't really change data at all.

    If we want to check out how many
slaves in our replication environment.We can execute “show processlit;”
on master.Whatif we’d like to check the detail of slaves.We have to
execute “show slave status\G” on slaves.