CODEWITHSUNDEEP
mysqlsql
toomanyairmiles
toomanyairmiles

Reputation: 6485

Synchronizing two mysql databases on different servers (same ISP)

I have very little experience with MySQL/SQL in general - total n00b really!

I have a live site and dev site which are both hosted by the same ISP but on different servers i.e. mysql1.foo.net and mysql2.foo.net.

I would like to figure out the the easyist possible way to sync the two databases - without having to export the whole thing erase the dev database and restore.

Upvotes: 2

Views: 1572

Answers (4)

crowley
crowley

Reputation: 100

I have seen this issue being solved by creating jobs that move only the new information from table to table nightly, or 15 minutes depending on whether it was a backup or a reporting server.

There were issues mainly due to how identity columns were being populated but aside from a design headache, a process like this one gives you good speed. It sounds like the two boxes are close to each other so data transfer rates should be fine.

I would solve it by creating an ssis job to shift things around, you can use a variety of tools but you have to make sure that there is something in your tables that you can use to identify what is new data. Time stamps can not be used as the process takes longer than a sec and that makes it unreliable, I would set up export columns, or if the tables are small enough where you indexes are less than 3 gb per table you can use these columns to achieve the task. Ultimately you know the data better than us so the optimal solution.

This way of doing it will be hard, but you will be saving tons of server time. plus it is a good way to version control your servers, as they will complain every time your datatypes are about to be truncated.

Upvotes: 0

David Stokes
David Stokes

Reputation: 120

Besides replication, you should look at MySQLdbcopy from the MySQl Utilities. Your databases may small enough to copy over with MySQLdbcopy and you could put it in a cron job for automation

Upvotes: 0

Peter Tillemans
Peter Tillemans

Reputation: 35341

I am afraid there is no "easy" solution. Except for rather smallish databases to dump production and restore on dev during the night. Small is defined as taking <12hrs for the backup restore operations, which is still very sizable.

Replication will probably not cut it since having a read-only copy is of limited value, and I assume you do not want changes on the dev db to propagate to prod.

Dump - Restore (if feasible) has advantages though - you test regularly that the backup actually works - you need to write and test regularly your schema migration scripts - you need to make them "hands-off" so they can run as a cron job

These practices make life for the IT operations a lot easier and is good risk mitigation to make customers sleep better.

On big databases I do not think there are easy (or cheap!) methods.

Upvotes: 1

Geoffrey
Geoffrey

Reputation: 11354

MySQL supports master+slave and master+master replication, your hosting provider will need to set this up for you however.

See here for more information if you are interested: http://www.howtoforge.com/mysql_master_master_replication

If you want to sync the two as a once off every now and then, just use:

mysqldump -h server1.example.com -u user -p password database | mysql -u user -p database

Upvotes: 0

Related Questions