MySQL database copy/sync in less than 6 hours per table?
I've been trying to use Percona Toolkit's pt-table-sync tool to synchronize a test database against a production one, but it's just too damned slow.
A single, albeit-large table (880,000 rows) took over six hours to sync. I literally don't have the days that it would take to use this approach. (It also routinely causes a slave to stop syncing with its master, when I use it as a source. But I have to do that, because it brings the production database to its knees.) My original process consisted of doing a mysqldump on the host, splitting the file into per-table files (for restartability), then reloading the tables one at a time using a script, but (for various purely political reasons that need not be discussed here), the remote server no longer has sufficient disk-space to hold these dumps. However, when it worked, it did the complete job in a few hours. So, I basically need a script that will do table-copies between databases. (Dropping and rebuilding the tables in the remote, please). And, let it be one that I do not have to write! :) I'd like for it to be able to know that if the number-of-rows are the same already, the table should not be automatically copied. What might you suggest? |
I don't have script for you but it seems very simple.
Assuming: The "remote" server is your target The local server is your source You don't want to store the dumps on the remote Then a few commands like this would produce your script: Code:
tables="footable bartable baztable" 900,000 records is nothing for MySQL, provided you don't have multi-megabytes of blobs. Imports of several GB and 9,000,000 record take a few minutes on a 1 CPU VPS for me. If your tables contain relations to other tables be careful with the per-table copying. jlinkels |
Quote:
That said, if you want to know if the number of rows is the same, you could just do a fairly simple script, and do a "select max(RecordID)" (or whatever you have as your primary key/record ID/SOMETHING that increments), on both tables and compare them. If less, dump..if not, carry on. |
I'm basically looking for a simple script – e.g. "if the number of records is the same, skip this table, and if the number of records has simply increased, copy the new ones."
I could write the damn thing, of course, but I'm a lazy sot. :D |
Even a sample script requires testing and debugging so I am giving you the idea:
Code:
$remoterows=$(echo "SELECT COUNT(*) FROM $remotetable" | mysql -h remotehost -s -q remotedb) |
All times are GMT -5. The time now is 10:06 AM. |