Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have been looking for a tool to synchronize tables from 2 different databases and found pt-table-sync. I read the documentation and got confused: they mostly use examples referring to a replicated environment, but I thought the whole point of replication was to take care of the synchronization of data for you, hence my questions:

QUESTIONS

  1. What is the point of using pt-table-sync if the replication process is supposed to take care of the synchronization of data for you?

  2. Can I use pt-table-sync in a non-replicated environment (between 2+ hosts that have nothing to do with each other, is it the role of the pt-table-sync --execute host1 host2 host3 example given)?

  3. If I must use pt-table-sync in a replicated environment, can I do without the bin-logs on the master (there is an example talking about resolving differences discovered by pt-table-checksum so wondering if bin-logs are absolutely necessary)?

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

but I thought the whole point of replication was to take care of the synchronization of data for you

Yes, MySQL replication does try to synchronize a replicated database. However, MySQL replication is tricky and the replication can fail for various reasons. Replication errors in my experience are rare, but they do happen during unexpected server crashes, when users hit "Control-C" in the middle of a big insert on the master, etc. MySQL.com does not provide good tools to deal with many of these problems. Luckily, a few engineers such as Baron Schwartz (Original author of the Percona Toolkit (formerly known as Maatkit) have developed tools to make MySQL administration easier.

For example, I currently have a table with 50 million rows. A handful of rows are out of sync due to a server crash a few weeks ago. I need to discover which rows are out of sync, but that would be painful to do manually. I use pt-table-checksum to check for replication errors on the replica, and pt-table-sync to discover which rows are missing on the replica.

If you are considering MySQL replication, I highly recommend that you investigate and use the Percona Toolkit. If we had started off with the Percona Toolkit, the administration of our MySQL databases would have been much simpler.

I read the documentation and got confused:

The documentation for Percona Toolkit is written like a technical manual. It unfortunately does not do a good job at describing how to use the tools, how do they help you, etc. http://www.mysqlperformanceblog.com has some of this information, but it's largely focused on the Percona fork of MySQL (This is how they make a living), which requires the reader to do some translation.

share|improve this answer
    
Very re-assuring answer as you seem to be going in my way and put things in a very understandable fashion (Rolando is too technical for me :(). I'll definitely check out the percona toolkit and the blog as they seem to be great resources. –  user64204 Nov 20 '12 at 8:46
add comment

Answer to Question 1

MySQL Replication suffers from two major problems

  • MySQL Replication is Asynchronous. This may introduce replication delay. This manifests itself with communication problems between a Master and the Slave via the Slave I/O Thread. This may logically and numerically be seen in Seconds_Behind_Master.

  • Data Drift. This is a intermittent condition where a Master and Slave are simply out-of-sync because of factors outside the realm of MySQL Replication. For example, please note one way to better synchronize replication: use the option sync-binlog. When you set sync-binlog to 1, mysqld will perform a flush of the current binary log for every entry you record in the binary log. That can ridiculously slow down a Master. By default, sync-binlog is 0.

    • Here is a question: With sync-binlog=0, who is responsible for flushing the binary log to disk?
    • Answer (please sit down for this one): THE OPERATING SYSTEM !!!
    • With that answer, it puts the Slave as a terrible disadvantage because its I/O Thread is at the mercy of the Master's Operating System. When the Master's OS gets around to flushing the binary log changes to disk and the Slave's I/O Thread can detect the next incoming SQL statement, then the statement is shipped over the I/O Thread to the Slave.
    • Percona has a nice PDF on dealing with Data Drift

Answer to Question 2

The direct answer here is no because pt-table-sync was designed to detect the I/O thread of a Slave by means of the --sync-to-master option.

Answer to Question 3

The direct answer here is no because MySQL Replication demands to know

  • what is the current binary log on the Master? (this is Master_Log_File from SHOW SLAVE STATUS\G)
  • what is the latest position the Slave has read from Master's current binary log? (this is Read_Master_Log_Pos from SHOW SLAVE STATUS\G)

If you simply want your binary logs to get out of the way, you can do one of two things

  • OPTION 1 : On the Master, set expire-logs-days to 3 to keep the last 3 days worth of binary logs
    • Add expire-logs-days=3 in /etc/my.cnf
    • No restart required: Just run SET GLOBAL expire_logs_days = 3;
  • OPTION 2 : Run SHOW SLAVE STATUS\G on the Slave. Take the value of Relay_Master_Log_File. and use it to clear binary logs on the Master to up that log file.
    • Suppose you run SHOW SLAVE STATUS\G on the Slave
    • You get this Relay_Master_Log_File: mysql-bin.000035
    • Run this on the Master : PURGE BINARY LOGS TO 'mysql-bin.000035';

SUGGESTION

If you want to have more faith in pt-table-sync, try using the --print option and redirecting to a text file instead of the --execute option. This will generate the SQL that would normally execute on the Master. You could just run the SQL directly on that Slave thereafter. Think of it as a dress rehearsal for --execute.

share|improve this answer
    
Thanks Rolando for your answer. Here is my feedback: A1: "MySQL Replication is Asynchronous": isn't that affecting ps-sync-table too? (Q1 was about the benefits of ps-sync-table over replication). "Data Drift": OK A2: OK A3: "If you simply what binary logs to get out of the way": what do you mean there? –  user64204 Nov 19 '12 at 19:14
1  
For Answer #1, pt-table-sync tries coalesce the slave status to read a stable log file and position from the master such the seconds_behind_master is virtually zero when computing differences. For Answer #3, once slave read the Master's latest binary log, there is no need for any binary logs prior to it. You can have mysqld rotate out aged binary logs. The setting expire-logs-days simply sets the age at which binary logs are automatically erased. If you use the binary logs on the Master in the event of point-in-time recovery situations, you could keep as many days as needed. –  RolandoMySQLDBA Nov 19 '12 at 19:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.