Checksumming mysql replicas over ssl

Question

After hours of struggling to understand the way percona toolkits (2.1) pt-table-checksum works, I try to try you.

Situation

• Running MySQL replication (Master-active:Master:passive)
• Slave user is required to connect via SSL
• pt-table-checksum can´t connect to slave

Problem

• pt-table-checksum doesn´t work for me with a SSL required mysql "checksum" user

The idea for a solution

• is to use pt-table-checksum on the active master,
• checking an via ssh tunnel locally connected MySQL-Slave,
• with a "checksum" user not required to use SSL

Questions

1. Is pt-table-checksum (2.1) really unable to connect via SSL?
2. How do I configure pt-table-checksum to connect (only) to an not internally configured slave
3. Could the DSNs option be the solution?
4. If so: I can´t figure out how it works. Could you put me on track?
5. Should I use an older version, or maatkit instead ? (because there I can configure master/slaves on the command line)
6. At all: What is the best practice to checksum replicas over insecure networks, with pt-table-checksum not working over SSL?

With hope:)

Answer 1

I've never tried master-master replication, but I have set up SSL connections with pt-table-checksum. Hopefully, my notes below may help answer some of your SSL-related questions.

In my case, I have master-slave replication set up using SSL connections and I'm using Percona Toolkit 2.1.2. Since the way to set it up was not obvious, I thought my notes might help save you some time and effort in your situation. Below is an example of how I got the SSL connections working for the pt-table-checksum connections to both the master and slave database servers.

The key is to pass the DSN information (containing the SSL settings) to the slave by explicitly passing the defaults-file (e.g., -F=/etc/my.cnf.percona) to the slave using the "--recursion-method dsn" setting. From what I've read on Percona's web site and by reviewing the pt-table-checksum code, the DSN information is not copied from one connection to the next in later versions of the Percona Toolkit (starting with 2.0, I believe). So, you have to set up the slave connection separately from the master.

Pass DSN information for the slave connection using:
--recursion-method dsn="D=percona,t=dsns,F=/etc/my.cnf.percona"

Assumptions:
a. The "percona" database account has proper privileges for Percona's pt-table-checksum. I recommend using "REQUIRE SSL" to ensure the account is required to use SSL connections.
b. The slave and master are set up to use SSL connections

Note: All of the steps below are done on the master.

(1) To pass the SSL settings for the DBI connection (i.e., the DSN config), create a separate my.cnf file specifically for the percona software.

    /etc/my.cnf.percona
    [client]
    ssl=1
    user=percona
    password=xxxxxxxxx
    ssl-capath=/etc/mysql/ca/crt

    The /etc/my.cnf.percona file will be used to set up the SSL connections to both the master and slave.

    Make sure to set the ownership & permissions on the file since it contains a password:
    chown root:root /etc/my.cnf.percona (if it's not already owned by root or a system account)
    chmod 0600 /etc/my.cnf.percona

(2) Test the /etc/my.cnf.percona settings

  
    # mysql --defaults-file=/etc/my.cnf.percona --host slave.domain.com
    mysql> \s
    --------------
    mysql  Ver 14.14 Distrib 5.5.23, for Linux (x86_64) using readline 5.1

    Connection id:      162
    Current database:   
    Current user:       [email protected]
    SSL:            Cipher in use is DHE-RSA-AES256-SHA
    Current pager:      stdout
    Using outfile:      ''
    Using delimiter:            ;
    Server version:     5.5.23-log MySQL Community Server (GPL)
    Protocol version:           10
    Connection:             slave.domain.com via TCP/IP
    Server characterset:    latin1
    Db     characterset:    latin1
    Client characterset:    utf8
    Conn.  characterset:    utf8
    TCP port:               3306
    Uptime:         2 days 2 hours 8 min 7 sec


    # mysql --defaults-file=/etc/my.cnf.percona --host master.domain.com
    mysql> \s
    --------------
    mysql  Ver 14.14 Distrib 5.5.23, for Linux (x86_64) using readline 5.1

    Connection id:      581433
    Current database:   
    Current user:       [email protected]
    SSL:            Cipher in use is DHE-RSA-AES256-SHA
    Current pager:      stdout
    Using outfile:      ''
    Using delimiter:            ;
    Server version:     5.5.23-log MySQL Community Server (GPL)
    Protocol version:           10
    Connection:             master.domain.com via TCP/IP
    Server characterset:    latin1
    Db     characterset:    latin1
    Client characterset:    utf8
    Conn.  characterset:    utf8
    TCP port:               3306
    Uptime:         9 days 3 hours 5 min 49 sec

(3) Set up the DSN table in the percona database on the master

    On the master:
    Create the percona database (if it does not already exist):

    mysql> create database percona;

    If the percona database already exists and you want to redo everything from scratch, drop the checksums & dsns tables if they already exist:

    mysql> drop table percona.checksums;  -- do this only if you are sure you want to start over & redo everything 

    mysql> drop table percona.dsns;   -- ok to drop this, we're recreating it in the next step 


    Create the dsns table in the percona database:


    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`)
        -> );

    Insert the slave info into the table:

     mysql> insert into dsns (dsn) values ("h=slave.domain.com");

(4) Run the pt-table-checksum and explicitly pass the defaults-file (-F=/etc/my.cnf.percona) to the slave using the "--recursion-method dsn" setting

    /usr/bin/pt-table-checksum -F /etc/my.cnf.percona h=master.domain.com --recursion-method dsn="D=percona,t=dsns,F=/etc/my.cnf.percona"

    master connection uses "-F /etc/my.cnf.percona h=master.domain.com"
    slave connection uses '--recursion-method dsn="D=percona,t=dsns,F=/etc/my.cnf.percona"'

This should create the checksums table in the percona database and connect to the slave (and master) using SSL connections.

Answer 2

I've never used the percona toolkit, but it seems to me that

(2) pt-table-checksum is not designed to "connect" to a slave; it's designed to run directly on some machine and generate checksums on that same machine (and optionally connect to the master machine).

(6) For files in general, the best practice to checksum replicas over unsecure networks is: Tunnel rsync over ssh. a b c d e (By "rsync" here, I mean the rsync protocol, as used internally by many utilities -- the rsync utility, duplicity, rsyncrypto, rdiff-backup, dirvish, etc.). I occasionally use rsync --dryrun which just does the checksum to tell me whether or not the backup/slave is identical to the master. But more often I run rsync without the --dryrun option. Without that option, if any differences are detected, rsync automatically goes on to bring the backup/slave up-to-date.

Alas, with databases with high write activity, the database image on disk (which is all that rsync can see) is often in an inconsistent state. To allow rsync to produce a useful backup/replica, we generally need to briefly shut down the database software on both ends of the link to get it to flush all the data in RAM to disk, do the rsync update, and then restart the database software. That's usually very fast, because usually the vast majority of the data is unchanged, and rsync only transfers the few parts of the data that have actually changed.

(As far as I can tell, the only advantage of database-aware utilities such as "pt-table-sync" over rsync is that those database-aware utilities apparently somehow do the synchronization to a consistent state without ever shutting down the database software).