Mirroring MySQL databases with mysqldump
We will use mysqldump to synchronize your mirror and production server databases.
Once the process complete, your destination database will be a mirror of the source database.
- 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
- 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
- productionIP - the IP address of your production/source server
- productionMySQLuser - the MySQL user on your production/source server
- productionPassword - the password for your MySQL user on your production/source server
(type your password immediately after typing the "-p" no spaces)
- productionDBname - name of the production/source server database that you want to backup
- mirrorIP - the IP address of your mirror/destination server
- mirrorMySQLuser - the MySQL user on your mirror/destination server (you will need to create this user via your mirror CPanel)
- mirrorPassword - the password for the MySQL user on your mirror/destination server
- 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.