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 a MySQL user on your mirror server, and you will need to create empty databases with the same names as the databases on your production server.


Once you have setup your mirror server databases, log into your mirror via ssh, and run the following command to sync a database:


mysqldump -h productionIP -u productionMySQLuser -pproductionPassword productionDBname | mysql -h mirrorIP -u mirrorMySQLuser -pmirrorPassword mirrorDBname

productionIP – the IP address of your production server
productionMySQLuser – the MySQL user on your production server
productionPassword – the password for your MySQL user on your production server (be sure to type your password immediately after typing the “-p” — no spaces)
productionDBname – name of the production server database that you want to backup
mirrorIP – the IP address of your mirror server
mirrorMySQLuser – the MySQL user on your mirror server (you will need to create this user via your mirror CPanel)
mirrorPassword – the password for the MySQL user on your mirror server
mirrorDBname – the database you are backing up to on your mirror server (this database will need to be named the same as the database on your production server, and you will need to create this database before running mysqldump)