How to mirror MySQL databases

Mirroring MySQL databases with mysqldump
We will use mysqldump to synchronize your mirror and production server databases.

Before running mysqldump, you will need to create an empty MySQL database on mirror/destination server
Create a database user and assign it rights over the above database (If mirroring you can user the same name, credentials as on source/production server).
Login to destination server via SSH using a ssh client like Putty or WinSCP.
If using a control panel at source/production server, ensure Remote MySQL connections to source database are allowed (In cPanel add % at cPanel > Databases > Remote MySQL > Add an Access host)

Test MySQL connections (port 3306) source database are open (could be blocked at source or destination firewall). At SSH console window type

mysql -u<span style="color:#0000ff;"><em>productionDBname</em></span> -p -P 3307 -h <em><span style="color:#0000ff;">productionIP</span></em>

If you have large database or slow/poor network connection, in SSH console window run command
```
screen
```
Now in text editor build the sync command using the following syntax after replacing blue and green terms with actual values;

mysqldump -h productionIP -u productionMySQLuser -p'productionPassword' productionDBname | mysql -h mirrorIP -u mirrorMySQLuser -p'mirrorPassword' mirrorDBname
1. productionIP - the IP address of your production/source server
2. productionMySQLuser - the MySQL user on your production/source server
3. productionPassword - the password for your MySQL user on your production/source server
  (type your password immediately after typing the "-p" no spaces)
4. productionDBname - name of the production/source server database that you want to backup
5. mirrorIP - the IP address of your mirror/destination server
6. mirrorMySQLuser - the MySQL user on your mirror/destination server (you will need to create this user via your mirror CPanel)
7. mirrorPassword - the password for the MySQL user on your mirror/destination server
8. mirrorDBname - the database you are backing up to on your mirror/destination server (destination database will need to created before running mysqldump)
Finally in SSH console window with screen running, paste the sync command with actual values and press enter.

Once the process complete, your destination database will be a mirror of the source database.

Categories