Backup MySQL DBs to a remote FTP Server

In this short tutorial, we’ll learn that how can we take the backup of MySQL database(s) and send it to the remote ftp server automatically. Click on this link to signup for your free account at DrivHQ : Free Account (You can use any other free ftp service and also recommend us).

In order to send the compressed DB backup to the remote ftp server, we need to make sure that gzip and ncftp are installed on our server. Use this command to install these packages:

sudo apt-get install ncftp gzip

Connect to the MySQL server using this command:

mysql -u root -p

Check the databases with the following command:

show databases;

Let’s say, we want to take the backup of TENDO database and at the same time want to upload it to the remote ftp server, this only command will take the backup of TENDO database,compress it by tar and gzip at the same time and will upload it to remote FTP server !

mysqldump --user=root --password=abcdef TENDO | gzip -9 | ncftpput -c -u arbab.nazar -p uvwxyz /TENDODB-`date +%d-%m-%Y`.sql.gz

root is the MySQL user with abcdef password, which will take the dump of TENDO database. arbab.nazar is a ftp user with uvwxyz password, which will connect to the

Note: If you want to take the backup of all the databases on the MySQL server then replace the TENDO with -A.

After the successful execution of the above command, check the ftp server:


Automatically take the backup:

To automatically take the backup of MySQL database, setup a cron job:

sudo crontab -e

Updated: In crontab entry, % sign is used to denote a new line. First % is special because it denotes the start of STDIN for the crontab entry’s command. After the first %, all other %s in a crontab entry indicate a new line. For this reason, when we’ll use the backup command in crontab to automate the process then we’ll get an error:

/bin/sh: 1: Syntax error: EOF in backquote substitution

To overcome this problem, we need to add backslash before % sign, like:

TENDODB-`date +\%d-\%m-\%Y`.sql.gz

So now write a crontab entry to run backup everyday at midnight:

@midnight mysqldump --user=root --password=abcdef TENDO | gzip -9 | ncftpput -c -u arbabnazar -p uvwxyz /TENDODB-`date +\%d-\%m-\%Y`.sql.gz

Hope this will help you!

Please Remember me in your prayers!

Enjoy :-)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: