Importing MySQL tables from other database in live site with mysqldump can cause trouble?

Question

Scenario: I want to replicate MySQL tables from one database to other database.

Possible best solution: May be to use MySQL Replication feature. Current solution on what I'm working as workaround (mysqldump) because can't spend time to learn about Replication in current deadline.

So currently I'm using command like this:

mysqldump -u user1 -ppassword1 --single-transaction SourceDb TblName | mysql -u user2 -ppassword2 DestinationDB

Based on some tests, it seems to be working fine. While running above command, I run ab command with 1000 requests on destination site and tried accessing the site from browser also.

My concern is for destination live site on which we are importing data with whole table (which will internally drop existing table and create new one with new data).

Can I be sure that live site won't break while this process or is there any risk factor? If yes then can that be resolved?

Answer 1

If they are on the same server, I'd do the table 'copy/replicate' this way:

CREATE TABLE detinationDB.new_table like sourceDB.TblName;
INSERT INTO detinationDB.new_table SELECT * FROM sourceDB.TblName;
RENAME TABLE detinationDB.TblName TO detinationDB.DropMe, detinationDB.new_table TO detinationDB.TblName;
DROP TABLE detinationDB.DropMe;

This way, you avoid dropping the old table before making sure the new table is there.

This is good if you intend to keep booth DBs on the same server.