0

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?

Improve this question
7
  • Asked this question to stackoverflow but this place seems more suitable so closing this question there: stackoverflow.com/questions/32369038/…
    – Kulin Choksi
    Sep 3, 2015 at 8:00
  • are you sure you don't have "-h" option in your mysqldump... command?
    – mysql_user
    Sep 3, 2015 at 8:22
  • Yes, because I'm connecting to localhost (default) and both databases are on same MySQL server for now.
    – Kulin Choksi
    Sep 3, 2015 at 10:29
  • If they're on same "server" then replication is not the solution. Things other than that you can try using triggers (INSERT, UPDATE, DELETE) on main table targetting the destination table.
    – mysql_user
    Sep 3, 2015 at 10:31
  • Yeah, trigger was the first thought came in my mind but one of our team mate suggested that trigger would take quite a load on MySQL server if we want to replicate the changes to many (10 or more) databases and they are difficult to manage also.
    – Kulin Choksi
    Sep 3, 2015 at 10:49

1 Answer 1

Reset to default
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.

Improve this answer
2
  • I think, that's almost like mysqldump but mysqldump can manage transaction for source table and it seems that it's not breaking destination table (not sure about all scenarios and also not sure how good it could be with transactions) but with your solution, we need to manage transactions explicitly
    – Kulin Choksi
    Sep 3, 2015 at 15:46
  • The main statement here is INSERT INTO.... SELECT FROM, which by itself is one transaction. This is especially true if your table is InnoDB. I didn't get your concerns about transactions here
    – Jehad Keriaki
    Sep 3, 2015 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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