Blog | Tutorial

MySQL backup across servers

I used to manually copy SQL backup file across from one server to another (usually by FTP). However, there is a simpler method to implement this using the Hostname parameter in the mysql command.
I use a command line mysql backup command from current server and then another command to transfer it remotely to new server.

The mysqldump command to copy the database on the current server. You require the database name, the username and password:

mysqldump --opt database -uusername -ppassword > database.sql

Once the above command has executed you can run this command to load the database into a remote server:

cat database.sql | mysql -hhostname -uusername -ppassword -Ddatabase

Place these commands in a shell script and run these daily or weekly to perform backups.

This assumes that the new server has been setup with the corresponding database.