pt-table-checksum connect to a slave without IP connection.

Polyglot Projects Percona Toolkit
1

Hi all, I’m pretty new to the MySQL world on Linux but I like it a lot !!

I have a little problem and I’m not sure how I can get to make the tools work, let me explain my setup …

We have 6 MySQL Masters server that are hosted with a public IP in 6 different places and we have 1 server at our office that acts as a Slave for ALL Masters in 6 different Dockers, so it’s like if we had 6 servers to act as Slave. The connection is made FROM the Slave TO the Master as we CAN’T connect to the IP of the SLAVES in their respective DOCKER … so we use the mysql.sock socket to connect to the MySQL in each DOCKER to send them MySQL commands and it’s working really well, the SLAVES are listening to the MASTERS and everything works but … I want to use pt-table-checksum to check data integrity on my slaves, but I cant connect to their IP as they are in DOCKERS, how can I connect to the slaves from the MASTERS if I can’t have an IP to give them ? Will I have to set a fixed IP on each DOCKER and then map a port for each MySQL DOCKER on the host where they are ? Someone have implemented that kind of setup before ? I have a mysql.sock for each of the MySQL in each Docker and I connect directly to the MySQL using these mysql.sock, can the Master connect that way too ?

The Percona tools are installed on all Masters and only on the host where the Slaves are, but not in each Docker … Can it work that way or not ? I can pass SQL commands directly inside the Dockers using the MySQL socket from the host to the MySQL inside the Dockers.

All helps will really be appreciated,

Thank you very much !!

2

Hi Eric,

Not sure If I understand your setup fully but did you tried --recursion-method of pt-table-checksum ? You can check for details here http://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html#cmdoption-pt-table-checksum–recursion-method

  • create a database percona and a table dsns, then insert a rows with the dsn of slaves, as follows:

mysql> create database percona;
mysql> create table percona.dsns (
id int(11) NOT NULL AUTO_INCREMENT,
parent_id int(11) DEFAULT NULL,
dsn varchar(255) NOT NULL,
PRIMARY KEY (id)
);
mysql> insert into percona.dsns (id,parent_id,dsn) values (1,0,‘h=<master_ip>,S=,u=,p=’);

  • then you run pt-table-checksum as follows:

$ pt-table-checksum h=<primary_master_ip>,P=,u=,p= --recursion-method=dsn=D=percona,t=dsns

Will I have to set a fixed IP on each DOCKER and then map a port for each MySQL DOCKER on the host where they are

If --recursion-method doesn’t work you may try with that option.

3

Hi and thank you for your answer mirfan,

I’m a little further in the configuration and I can now connect from the Master to the Slave like that :

mysql --host=[ip_to_the_slave’s_host] --port=3308 -upercona -p[percona_password]

And I have the slave’s mySQL prompt, so it’s not a network issue. And I have set it up that way :

On the slave’s host I have 5 Docker running mySQL to be the slaves of my 5 Master mySQL server hosted elsewhere, so I have configured the Docker’s host to pass the mySQL data to the slaves via a different port for each Docker (3308, 3309, 3310, 3311 and 3312) but INSIDE each Docker the mySQL is listening on the 3306 port, so they report 3306 as their port to the master (I’m doing some “static natted” port forwarding from the host to the dockers). Is there a way to tell the master to use the 33XX port instead of the default 3306 port ? Can we tell the tool to use the 33XX port instead of 3306 at the command line ?

When I try to run pt-table-checksum (I’m running the tool on the masters, do I have to run the tool on the slaves ?), the software check on the Master for some slave and it founds my slave, but on port 3306 like when I do the commande show slave hosts on a master :

mysql> show slave hosts;
±-----------±------±-----±------------+
| Server_id | Host | Port | Master_id |
±-----------±------±-----±------------+
| 2 | | 3306 | 1 |
±-----------±------±-----±------------+

And that’s the command I use :

[user@host]$ sudo pt-table-checksum --replicate-check-only -upercona -p[percona_password]

And I get this error :

Cannot connect to h=[host_ip],p=…,u=percona
Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.

So the tool look for the slave on the 3306 tcp port but like I said before, the 3306 port is INSIDE the docker, OUTSIDE it’s 3308 to 3312 ports so the tool cannot connect to the Slave to make it’s job. I found that I can use this command, but I don’t know how and where to use it : –report-port=[port_to_use] … And I did create the dsns table, but I didn’t know how to populate it with the correct information …

That’s what I did yet :

mysql>CREATE DATABASE percona;
mysql>USE percona;
mysql>CREATE TABLE dsns (
id int(11) NOT NULL AUTO_INCREMENT,
parent_id int(11) DEFAULT NULL,
dsn varchar(255) NOT NULL,
PRIMARY KEY (id) );

Now I will try to populate the table with VALUES like you told me up here, and I’ll post the result in another post later today.

But as for now, I’m stuck there … I can’t find a way to tell the master from the slaves to connect on a different port for the tool to work.

Thank you for your support !

Have a great day !

4

Hi,

Now I think I’m really near to find the solution to my problem … there’s what I have tested today and the results I had …

I did this command on my mySQL Master :

mysql>insert into percona.dsns (id,parent_id,dsn) values (1,0,‘h=<slave’s_host_ip>,P=3308,u=percona,p=’);

Then I tried this on the Master :

[user@host]$ sudo pt-table-checksum h=127.0.0.1,u=percona,p= --recursion-method=dsn=D=percona,t=dsns

And I got this error message that time, not the same as before so I’m guessing that the communication goes well … :smiley:

04-16T14:01:55 Replication filters are set on these hosts:
<docker_hostname>
binlog_do_db = ,,,,
Please read the --check-replication-filters documentation to learn how to solve this problem. at /usr/bin/pt-table-checksum line 9632.

So I think that the tool works on the communication level, but I have this new error to go through, any help will be really appreciated. I have 16 DB on this Master but only 5 needs to be replicate on the slave, as the others are for testing purpose only. Do I have to include the percona DB as well in my binlog_do_db option or this setup is not supported by Percona’s tool ?

Thank you for your answers !!

Have a wonderful day ! :slight_smile:

5

Hi Eric:
As per pt-table-checksum documentation, you can see replication filters can break the replication, or report false positives. You can check the documentation here:

[url]pt-table-checksum — Percona Toolkit Documentation

You can always use the regular expression option to avoid running checksum queries on filtered tables/databases when you run pt-table-checksum on your master database, that way you will run checksum queries only on those databases being replicated, and control data integrity on them.