8

I have seen Cron sync mysql tables . But I cannot use replication. I used to use percona-toolkit and it worked perfectly. I could run the sync command on demand or just run it via cron. It would compare checksums on the two tables and do inserts, updates, deletes etc. However Perl(DBD::mysql) and MySQL have some incompatibilities in a new server I'm going to run this on and I cannot use pt-table-sync. Is there a similar solution which uses something other than Perl/DBD?

Edit : (more details, clarity)

  1. Both the source and target tables are live tables and in constant use. Hence a solution with the table not existing (eg. a DROP TABLE is done) would not be acceptable.
  2. Cannot use replication or any such modifications to the server itself. The solution has to work on the client side.
  3. In this particular scenario both the tables are in the same server, but different DBs (eg. db1.tbl db2.tbl). However, a solution which does not rely on this fact would definitely be a bonus
  4. Network latency is unlikely to be an issue. In this case the script runs on a server in the same datacenter.
  5. Cannot use Perl (incompatibility between Perl and MySQL - 64 bit vs 32 bit)
3
  • if you issue LOCK TABLES before dropping a table and recreating it, you should be safe as other threads wanting to read from this particular table would halt execution waiting for their lock to be granted. I would test it beforehand, though.
    – the-wabbit
    Feb 2, 2012 at 9:43
  • Is it possible that you could use pt-table-sync on another server? It doesn't need to be installed on the servers it's syncing; it can run on a third one.
    – user48802
    Feb 2, 2012 at 20:14
  • That's what am doing currently. But I don't have a server in the same datacenter and it's taking a lot of time because of the latency.
    – Anirudh
    Feb 3, 2012 at 13:18

3 Answers 3

13

use mysqldump --opt <database> <tablename> to create a dump of your table and feed it to your new server. As you apparently have access to the remote database via TCP/IP, you simply could use

mysqldump --opt --user=<youruser> --password=<yourpassword> -host <yourhost> \
<yourDB> <yourtable> | mysql -u <newserveruser> -p<password>

to connect to the remote database, dump it and feed the output into your new server.

If you did not have direct TCP/IP access to the remote database, you still could do pretty much the same by tunneling the data through SSH after setting up public key authentication:

ssh -C -l <remoteuser> <remoteserver> \
'mysqldump --opt --user=<youruser> --password=<yourpassword> <yourDB> <yourtable>' \
| mysql -u <newserveruser> -p<password>

See the documentation to mysqldump and the man page for SSH for more details.

If you need more bandwidth efficiency, consider creating a dump with mysqldump, storing it on the source server and using rsync for copying/updating the counterpart on the destination server before importing. As rsync will create rolling checksums on the source and the destination file, it likely will not need to transfer most of the dump's contents on subsequent runs.

There has been a mysqldump patch which was meant to use temporary tables when inserting rows and rename the table afterwards to the original table name to reduce lock time, but I would consider it experimental as it has unresolved issues and never made it into the main branch. See this discussion for patch code and details.

If you simply can't drop the table on the destination for whatever reasons, you might insert the dumped data into a new table (a quick & dirty but somewhat unsafe approach would pipe the mysqldump output to sed -e 's/mytable/newtable/g' before further piping to mysql) and then run an UPDATE / DELETE / INSERT cycle with a couple of JOINs like this (untested, do a sanity check):

/* set write lock on the table so it cannot be read while updating */
LOCK TABLES mytable WRITE;

/* update all rows which are present in mytable and newtable */
UPDATE mytable AS M LEFT JOIN newtable AS N ON M.primarykey = N.primarykey 
SET M.column1=N.column1, M.column2=N.column2 [...]
WHERE N.primarykey Is Not NULL;

/* delete all rows from mytable which are no longer present in newtable */
DELETE M FROM mytable AS M LEFT JOIN newtable AS N on M.primarykey = N.primarykey 
WHERE N.primarykey Is NULL;

/* insert new rows from newtable */
INSERT INTO mytable (primarykey, column1, column2, [...]) 
SELECT (N.primarykey, N.column1, N.column2, [...]) FROM mytable AS M 
RIGHT JOIN newtable AS N ON M.primarykey=N.primarykey WHERE M.primarykey Is NULL

/* release lock */
UNLOCK TABLES;

Note: of course, your database's data would be inconsistent while you are inserting/updating its data, but as long as you are not using transactions (not available for MyISAM tables), this would be the case no matter what you do - dropping and recreating the table would create temporary inconsistencies just as doing the update/delete/insert cycle would. This is due to the very nature of a non-atomic transactionless design of MyISAM.

5
  • This method would only do deletes and inserts. It would need to delete the current table on the target machine before inserting the new rows. This would mean that the table is incomplete/unstable while the sync is running. Both my source and target are in active use, I can't have any downtime. In a normal case scenario updates would be more useful, rather than a delete-insert
    – Anirudh
    Feb 2, 2012 at 7:26
  • 1
    @Anirudh this is one of the reasons why it pays off to state your complete requirements in the question. I've edited my answer to add what I think would be the appropriate approach - you may want to edit your question in order to get meaningful answers from other people. BTW: mysqldump does insert DROP TABLE statements for you already, no need to drop the table manually.
    – the-wabbit
    Feb 2, 2012 at 8:13
  • Sorry about that. Have edited question. Hope it's better now
    – Anirudh
    Feb 2, 2012 at 9:17
  • Proposed by @DigitalFiz: Deleting those records is a little more complicated that changing the SELECT to DELETE. Specifically you have to tell MySQL what table you want to delete the records from (i.e. M.*) otherwise it gets confused about what table to delete the records from.6 Oct 4, 2012 at 16:13
  • I corrected some minor SQL-related errors in the migration script. Tested them with MySQL v5.7.43 Dec 4, 2023 at 16:00
2

It sounds like you want something like rubyrep which can sync left or right and is configurable for what type of stuff you want synced either way. However I think it is database level and not table level. It might be a good starting point for modification into table-based sync.

Another option would be to use REPLACE INTO instead of dropping the table as shown in http://codeinthehole.com/writing/how-to-sync-a-mysql-table-between-two-remote-databases/

It sounds like you might not have access to the logs or I'd suggest getting commands out of the binary log.

1
  • rubyrep looks interesting. Apparently the list of tables to sync can be configured. Will definitely take a look. Thanks :)
    – Anirudh
    Feb 3, 2012 at 14:27
1

Have you tried using Triggers?

DELIMITER $$
CREATE TRIGGER sync_table1_insert
AFTER INSERT ON `table1` FOR EACH ROW
BEGIN
    INSERT INTO table2 (id, value) VALUES (NEW.id, NEW.value);
END;
$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER sync_table1_update
AFTER UPDATE ON `table1` FOR EACH ROW
BEGIN
    UPDATE table2 SET value = NEW.value WHERE id = NEW.id;
END;
$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER sync_table1_delete
AFTER DELETE ON `table1` FOR EACH ROW
BEGIN
    DELETE FROM table2 WHERE id = OLD.id;
END;
$$
DELIMITER ;
2
  • 1
    So if the table2 is on the other server, is it possible to use triggers?
    – shgnInc
    Jun 27, 2015 at 4:36
  • 1
    Had a similar problem and solved it this way: Using triggers to only sync the relevant tables to a second database (on the same server) which got replicated to the other server(s) and vice versa if needed. Aug 21, 2020 at 13:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .