Verifying Replication Consistency with Percona’s pt-table-checksum
Replication is an important concept for improving database performance and security. In this blog post, I would like to demonstrate how the consistency between a MySQL master and a slave can be verified. We will create two Docker containers, one for the master one for the slave.
Installing the Percona Toolkit
The Percona Toolkit is a collection of useful utilities, which can be obained for free from the company’s portal. The following commands install the prerequisits, download the package and eventually the package.
sudo apt-get install -y wget libdbi-perl libdbd-mysql-perl libterm-readkey-perl libio-socket-ssl-perl
wget https://www.percona.com/downloads/percona-toolkit/3.0.4/binary/debian/xenial/x86_64/\
percona-toolkit_3.0.4-1.xenial_amd64.deb
sudo dpkg -i percona-toolkit_3.0.4-1.xenial_amd64.deb
Setting up a Test Environment with Docker
The following command creates and starts a docker container. Note that these are minimal examples and are not suitable for a serious environment.
docker run --name mysql_master -e MYSQL_ALLOW_EMPTY_PASSWORD=true -d mysql:5.6 --log-bin \
--binlog-format=ROW --server-id=1```
Get the IP address from the master container:
Get the IP of the master
docker inspect mysql_master | grep IPAddress
“SecondaryIPAddresses”: null, “IPAddress”: “172.17.0.2"```
You can connect to this container like this and verify the server id:
stefan@Lenovo ~/Docker-Projects $ mysql -u root -h 172.17.0.2
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.35-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0,00 sec)
We repeat the command for the slave, but use a different id. port and name:
docker run --name mysql_slave -e MYSQL_ALLOW_EMPTY_PASSWORD=true -d mysql:5.6 --server-id=2```
For simplicity, we did not use Docker links, but will rather use IP addresses assigned by Docker directly.
## Replication Setup
First, we need to setup a user with replication privileges. This user will connect from the slave to the master.
On the host, interact with the master container
Get the IP address of the slave container
$ docker inspect mysql_slave | grep IPAddress “SecondaryIPAddresses”: null, “IPAddress”: “172.17.0.3”, “IPAddress”: “172.17.0.3”,
Login to the MySQL console of the master
Grant permissions
GRANT REPLICATION SLAVE ON . TO replication@‘172.17.0.3’ IDENTIFIED BY ‘SLAVE-SECRET’;
Get the current binlog position
mysql> SHOW MASTER STATUS; +——————-+———-+————–+——————+——————-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +——————-+———-+————–+——————+——————-+ | mysqld-bin.000002 | 346 | | | | +——————-+———-+————–+——————+——————-+ 1 row in set (0,00 sec)```
Now log into the slave container and add the connection details for the master:
## Connect to the MySQL Slave instance
$ mysql -u root -h 172.17.0.3
### Setup the slave
mysql> CHANGE MASTER TO
MASTER_HOST='172.17.0.2',
MASTER_PORT=3306,
MASTER_USER='replication',
MASTER_PASSWORD='SLAVE-SECRET',
MASTER_LOG_FILE='mysqld-bin.000002',
MASTER_LOG_POS=346;
Query OK, 0 rows affected, 2 warnings (0,05 sec)
### Start and check
mysql> start slave;
Query OK, 0 rows affected (0,01 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.2
Master_User: percona
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000002
Read_Master_Log_Pos: 346
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: mysqld-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Now our simple slave setup is running.
Get some test data
Lets download the Sakila test database and import it into the master. It will be replicated immediately.
wget http://downloads.mysql.com/docs/sakila-db.tar.gz
~/Docker-Projects $ tar xvfz sakila-db.tar.gz
mysql -u root -h 172.17.0.2 < sakila-db/sakila-schema.sql
mysql -u root -h 172.17.0.2 < sakila-db/sakila-data.sql```
Verify that the data is on the slave as well:
mysql -u root -h 172.17.0.3 -e “USE sakila;SHOW TABLES;” +—————————-+ | Tables_in_sakila | +—————————-+ | actor | | actor_info | | address | | category | | city | | country | | customer | … | store | +—————————-+
After our setup is completed, we can proceed with Percona pt-table checksum.
# Percona pt-table-checksum
The Percona pt-table-checksum tool requires the connection information of the master and the slave in a specific format. This is called the DSN (data source name), which is a coma separated string. We can store this information in a dedicated database called percona in a table called dsns. We create this table on the master. Note that the data gets replicated to the slave within the blink of an eye.
CREATE DATABASE percona; USE percona;
CREATE TABLE DSN-Table (
id int(11) NOT NULL AUTO_INCREMENT,
dsn varchar(255) NOT NULL,
PRIMARY KEY (id)
);
The next step involves creating permissions on the slave and the master!
GRANT REPLICATION SLAVE,PROCESS,SUPER, SELECT ON . TO ‘percona’@‘172.17.0.1’ IDENTIFIED BY ‘SECRET’; GRANT ALL PRIVILEGES ON percona.* TO ‘percona’@‘172.17.0.1’;```
The percona user is needed to run the script. Note that the IP address is this time from the (Docker) host, having the IP 172.17.0.1 by default. In real world scenarios, this script would either be run on the master or on the slave directly.
Now we need to add the information about the slave to the table we created. The Percona tool could also read this from the process list, but it is more reliable if we add the information ourselves. To do so, we add a record to the table we just created, which describes the slave DSN:
INSERT INTO percona.DSN-Table VALUES (1,'h=172.17.0.3,u=percona,p=SECRET,P=3306');```
The pt-table-checksum tool the connects to the master instance and the the slave. It computes checksums of all databases and tables and compares results. You can use the tool like this:
pt-table-checksum –replicate=percona.checksums –create-replicate-table –empty-replicate-table
–recursion-method=dsn=t=percona.DSN_Table -h 172.17.0.2 -P 3306 -u percona -pSECRET
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
09-10T10:13:11 0 0 0 1 0 0.020 mysql.columns_priv
09-10T10:13:11 0 0 3 1 0 0.016 mysql.db
09-10T10:13:11 0 0 0 1 0 0.024 mysql.event
09-10T10:13:11 0 0 0 1 0 0.014 mysql.func
09-10T10:13:11 0 0 40 1 0 0.026 mysql.help_category
09-10T10:13:11 0 0 614 1 0 0.023 mysql.help_keyword
09-10T10:13:11 0 0 1224 1 0 0.022 mysql.help_relation
09-10T10:13:12 0 0 585 1 0 0.266 mysql.help_topic
09-10T10:13:12 0 0 0 1 0 0.031 mysql.ndb_binlog_index
09-10T10:13:12 0 0 0 1 0 0.024 mysql.plugin
09-10T10:13:12 0 0 6 1 0 0.287 mysql.proc
09-10T10:13:12 0 0 0 1 0 0.031 mysql.procs_priv
09-10T10:13:12 0 1 2 1 0 0.020 mysql.proxies_priv
09-10T10:13:12 0 0 0 1 0 0.024 mysql.servers
09-10T10:13:12 0 0 0 1 0 0.017 mysql.tables_priv
09-10T10:13:12 0 0 1820 1 0 0.019 mysql.time_zone
09-10T10:13:12 0 0 0 1 0 0.015 mysql.time_zone_leap_second
09-10T10:13:12 0 0 1820 1 0 0.267 mysql.time_zone_name
09-10T10:13:13 0 0 122530 1 0 0.326 mysql.time_zone_transition
09-10T10:13:13 0 0 8843 1 0 0.289 mysql.time_zone_transition_type
09-10T10:13:13 0 1 4 1 0 0.031 mysql.user
09-10T10:13:13 0 0 1 1 0 0.018 percona.DSN_Table
09-10T10:13:13 0 0 200 1 0 0.028 sakila.actor
09-10T10:13:13 0 0 603 1 0 0.023 sakila.address
09-10T10:13:13 0 0 16 1 0 0.033 sakila.category
09-10T10:13:13 0 0 600 1 0 0.023 sakila.city
09-10T10:13:13 0 0 109 1 0 0.029 sakila.country
09-10T10:13:14 0 0 599 1 0 0.279 sakila.customer
09-10T10:13:14 0 0 1000 1 0 0.287 sakila.film
09-10T10:13:14 0 0 5462 1 0 0.299 sakila.film_actor
09-10T10:13:14 0 0 1000 1 0 0.027 sakila.film_category
09-10T10:13:14 0 0 1000 1 0 0.032 sakila.film_text
09-10T10:13:14 0 0 4581 1 0 0.276 sakila.inventory
09-10T10:13:15 0 0 6 1 0 0.030 sakila.language
09-10T10:13:15 0 0 16049 1 0 0.303 sakila.payment
09-10T10:13:15 0 0 16044 1 0 0.310 sakila.rental
09-10T10:13:15 0 0 2 1 0 0.029 sakila.staff
09-10T10:13:15 0 0 2 1 0 0.020 sakila.store
The result shows a difference in the MySQL internal table for permissions. This is obviously not what we are interested in, as permissions are individual to a host. So we rather exclude the MySQL internal database and also the percona database, because it is not what we are interested in. Also in order to test it the tool works, we delete the last five category assignments from the table with <span class="lang:default decode:true crayon-inline">mysql -u root -h 172.17.0.3 -e “DELETE FROM sakila.film_category WHERE film_id > 995;”</span> and update a row in the city table with
mysql -u root -h 172.17.0.3 -e “update sakila.city SET city='Innsbruck’ WHERE city_id=590;“```
Now execute the command again:
pt-table-checksum --replicate=percona.checksums --create-replicate-table --empty-replicate-table \
--recursion-method=dsn=t=percona.DSN_Table --ignore-databases mysql,percona -h 172.17.0.2 -P 3306 -u percona -pSECRET
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
09-10T10:46:33 0 0 200 1 0 0.017 sakila.actor
09-10T10:46:34 0 0 603 1 0 0.282 sakila.address
09-10T10:46:34 0 0 16 1 0 0.034 sakila.category
09-10T10:46:34 0 1 600 1 0 0.269 sakila.city
09-10T10:46:34 0 0 109 1 0 0.028 sakila.country
09-10T10:46:34 0 0 599 1 0 0.285 sakila.customer
09-10T10:46:35 0 0 1000 1 0 0.297 sakila.film
09-10T10:46:35 0 0 5462 1 0 0.294 sakila.film_actor
09-10T10:46:35 0 1 1000 1 0 0.025 sakila.film_category
09-10T10:46:35 0 0 1000 1 0 0.031 sakila.film_text
09-10T10:46:35 0 0 4581 1 0 0.287 sakila.inventory
09-10T10:46:35 0 0 6 1 0 0.035 sakila.language
09-10T10:46:36 0 0 16049 1 0 0.312 sakila.payment
09-10T10:46:36 0 0 16044 1 0 0.320 sakila.rental
09-10T10:46:36 0 0 2 1 0 0.030 sakila.staff
09-10T10:46:36 0 0 2 1 0 0.027 sakila.store
You see that there is a difference in the tables sakila.city and in the table sakila.film_category. The tool does not report the actual number of differences, but rather the number of different chunks. To get the actual differences, we need to use a different tool, which utilises the checksum table that the previous step created.
Show the differences with pt-tabel-sync
The pt-table-sync tool is the counter part for the pt-table-checksum util. It can print or even replay the SQL statements that would render the slave the same state again to be in sync with the master. We can run a dry-run first, as the tool is potentially dangerous.
pt-table-sync --dry-run --replicate=percona.checksums --sync-to-master h=172.17.0.3 -P 3306 \
-u percona -pSECRET --ignore-databases mysql,percona
# NOTE: --dry-run does not show if data needs to be synced because it
# does not access, compare or sync data. --dry-run only shows
# the work that would be done.
# Syncing via replication P=3306,h=172.17.0.3,p=...,u=percona in dry-run mode, without accessing or comparing data
# DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
# 0 0 0 0 Chunk 08:57:51 08:57:51 0 sakila.city
# 0 0 0 0 Nibble 08:57:51 08:57:51 0 sakila.film_category
With –dry-run, you only see affected tables, but not the actual data because it does not really access the databases tables in question. Use –print additionally or instead of dry-run to get a list:
pt-table-sync --print --replicate=percona.checksums --sync-to-master h=172.17.0.3 -P 3306 \
-u percona -pSECRET --ignore-databases mysql,percona
REPLACE INTO `sakila`.`city`(`city_id`, `city`, `country_id`, `last_update`) VALUES \
('590', 'Yuncheng', '23', '2006-02-15 04:45:25')
\ /*percona-toolkit src_db:sakila src_tbl:city ...
REPLACE INTO `sakila`.`film_category`(`film_id`, `category_id`, `last_update`) VALUES ...
REPLACE INTO `sakila`.`film_category`(`film_id`, `category_id`, `last_update`) VALUES ('997',...
REPLACE INTO `sakila`.`film_category`(`film_id`, `category_id`, `last_update`) VALUES ('998', '11 ...
REPLACE INTO `sakila`.`film_category`(`film_id`, `category_id`, `last_update`) VALUES ('999', '3', ...
REPLACE INTO `sakila`.`film_category`(`film_id`, `category_id`, `last_update`) VALUES ('1000', '5', ...
The command shows how we can rename back from Innsbruck to Yuncheng again and also provides the INSERT statements to get the deleted records back.When we replace –print with –execute, the data gets written to the master and replicated to the slave. To allow this, we need to set the permissions on the master
GRANT INSERT, UPDATE, DELETE ON sakila.* TO 'percona'@'172.17.0.1';
pt-table-sync --execute --replicate=percona.checksums --check-child-tables \
--sync-to-master h=172.17.0.3 -P 3306 -u percona -pSECRET --ignore-databases mysql,percona
REPLACE statements on sakila.city can adversely affect child table `sakila`.`address`
because it has an ON UPDATE CASCADE foreign key constraint.
See --[no]check-child-tables in the documentation for more information.
--check-child-tables error while doing sakila.city on 172.17.0.3
This error indicates that updating the city table has consequences, because it is a FK to child tables. In this example, we are bold and ignore this warning. This is absolutely not recommended for real world scenarios.
pt-table-sync --execute --replicate=percona.checksums --no-check-child-tables \
--no-foreign-key-checks --sync-to-master h=172.17.0.3 -P 3306 -u percona -pSECRET \
--ignore-databases mysql,percona
The command–no-check-child-tables ignores child tables and the command –no-foreign-key-checks ignores foreign keys.
Run the checksum command again to verify that the data has been restored:
pt-table-checksum --replicate=percona.checksums --create-replicate-table --empty-replicate-table \
--recursion-method=dsn=t=percona.DSN_Table --ignore-databases mysql,percona
-h 172.17.0.2 -P 3306 -u percona -pSECRET
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
09-10T11:24:42 0 0 200 1 0 0.268 sakila.actor
09-10T11:24:42 0 0 603 1 0 0.033 sakila.address
09-10T11:24:42 0 0 16 1 0 0.029 sakila.category
09-10T11:24:42 0 0 600 1 0 0.275 sakila.city
09-10T11:24:42 0 0 109 1 0 0.023 sakila.country
09-10T11:24:43 0 0 599 1 0 0.282 sakila.customer
09-10T11:24:43 0 0 1000 1 0 0.046 sakila.film
09-10T11:24:43 0 0 5462 1 0 0.284 sakila.film_actor
09-10T11:24:43 0 0 1000 1 0 0.036 sakila.film_category
09-10T11:24:43 0 0 1000 1 0 0.279 sakila.film_text
09-10T11:24:44 0 0 4581 1 0 0.292 sakila.inventory
09-10T11:24:44 0 0 6 1 0 0.031 sakila.language
09-10T11:24:44 0 0 16049 1 0 0.309 sakila.payment
09-10T11:24:44 0 0 16044 1 0 0.325 sakila.rental
09-10T11:24:44 0 0 2 1 0 0.029 sakila.staff
09-10T11:24:44 0 0 2 1 0 0.028 sakila.store
0 DIFFS, we are done!