How To Sync A Mysql Databases Between Two Remote Databases (without Mysql Database Replication Technique )

I have two shared server (Server 1 and server 2) and one dedicated server.in the dedicated server i am running a script that will insert data in to server 1. Now i want to sync all

Solution 1:

As the question states to sync all data from one server to another, I think you can use a relatively simple solution, involving mysqldump.

I think you can do this all from the dedicated server:

mysqldump --user=<username> --password=<password> --host=<server1hostname> --port=<port> --add-drop-database <databasename> > dump.sql

Replace <username>, <password>, <port> and <server 1 hostname> with the connection details for server 1. Replace with the name of the database on server 1 you want to copy to server 2. If you want to copy all database, replace with the --all-databases option.

This will make a file called dump.sql in the current directory. You can then load this into server 2:

mysql --user=<username> --password=<password> --host=<server2hostname> --port=<port><databasename> < dump.sql

Replace <username>, <password>, <port> and <server 2 hostname> with the connection details for server 2.

This will take the dump.sql file, and load it into the database on server 2. This will drop the database on server 2 - so all existing data will be replaced with that in dump.sql.

Check the options to mysqldump (regarding drop databases, drop tables etc) and tailor the above commands to be suitable for your situation. I think, if you hook things up correctly, you could even bypass the intermediate file and connect the mysqldump on server 1 to mysql on server 2 using a socket.

To cover the 'automated' part of this question, you could run the above commands under cron and schedule them to run on the first day of every month at a suitable time.

Solution 2:

AS @MarkStreatfield already said, mysqldump is just for you. You don't need to be root to run it. However, you should keep in mind that while dump processing is in process there is a global lock on all tables, so your app is not fully functional at this moment. If you are passing mysqldump output directly to server2, it might take significant time so it can be a problem.

For backup purposes, it's better to dump to local sql or gz file. You can sync with S3/Dropbox or deploy dump at server2 later, without lock. E.g., dump to sql.gz file:

mysqldump -u <db_user> --password=<db_password> -h <host><db_name> | gzip > `date +mydbdump_%y_%m_%d.sql.gz`