MySQL backup to a local computer
Share on Facebook

Remote MySQL back-up with optional scheduling and compression

This is written from the perspective of an Ubuntu Desktop user and the MySQL database is hosted on a server on which I have cPanel access.


The first thing you need is your external IP address. Your IP address is - make a note of this and bear in mind that your IP address may change. Mine has remained static for a couple of years.


Log in to your cPanel account. Click on the Remote MySQL link and add your IP address as an Access Host. While you are on cPanel, get the IP address of your account. It is in the left-side pane, towards the bottom. Make a note of that.


Back-up a MySQL database with no compression

On your computer, open a text editor such as Gedit and type the following, substituting the place-holders as necessary:


mysqldump -u [MySQL User] --password=[MySQL Password] -h [Your server's IP address] --databases [Database name] --lock-tables=false --result-file=[Path where you want to save the file]/[Database name]_`date +\%Y-\%m-\%d_\%H-\%M-\%S`.sql


I've run this command over a couple of lines on this page. In your editor, it will be one line.


Having typed the command, save the text file for future use. Assuming you have MySQL installed on your computer, paste it into a Terminal session (by using the Edit menu on the session window) and press Enter. After a pause, depending on how big your database is, a file containing the SQL statement needed to rebuild your database will be saved in the folder you specified.


Back-up a MySQL database with GZip compression

In this instance, type the command as before but miss off everything after --lock-tables=false. In other words, don't add the --result-file parameter. Instead, add this to the end of the command, leaving a single space and substituting the place-holders as before:


| gzip > [Path where you want to save the file]/[Database name]_`date +\%Y-\%m-\%d_\%H-\%M-\%S`.sql.gz


If you run this command in a terminal, the output will be "piped" to a compressed file.


Automate the process

The easiest way to use Crontab to schedule a process is to the install the Scheduled Tasks utility which is available from the Ubuntu repositories. This uses straight-forward English to append lines to your crontab-entries.


If you prepend 0 * * * * to your command, your MySQL database will be backed up every day at midnight. If you add >/dev/null 2>&1 to the end of your command, Crontab will not try to send you an email to say that it has done its work.





Primer, sealer, dust proofing coating
Larkrise Sealcote is a multi-purpose, clear,
water-based coating - Dilute 2:1 and solve a number of problems