Backup and restore mysql database on Linux

Backup procedure is one of the most common and undisputed tasks for a system administrator or webmaster. There is no excuse for a professional IT to neglect this task whatever the server or cloud architecture is.

Websites or web applications store the data (the content) in database and files. Both of them needs to be saved for security reason in case of server failure, human error or perhaps a system hacking.

This simple tutorial will show you how to backup, schedule and restore MySQL database in Linux.

For greater availability and to prevent the loss of total data is necessary to move the backup files in other physical server or storage.

Backup MySQL database

The program mysqldump can be used to dump a database or a collection of databases for backup or transfer to another SQL server. In this tutorial we will use to backup a MySQL database.

mysqldump -u{username} -p{password} -h {host} {db-name} > {db-backup.sql}
  • bracket "{" and "}" are just explanatory, to define the start and begin of variable.
  • {username} MySQL username
  • {password} Password for the {username}. There is no space between the switch -p and {password}. If you don't specify the {password} and the {username} has no blank password, mysqldump will prompt you for {password}
  • {host} is the MySQL hostname or IP address. Without the switch -h the default host is localhost
  • {db-name} The name of database you want to backup
  • {db-backup.sql} The output file, a sql backup of your database. Is recommended to use {dbname}-{day}-{month}-{year}.sql (Or similar format) as output file for easy reading in case of restore because accidentally you may restore an old backup and replace new data. Of course checking the file creation date will avoid this but will be easy for you in manual restore using autocomplete name (with tab key). This naming logic will be very useful in scheduled backup.
To backup more than one database you have to use the option --databases and specify them separated with space.

mysqldump -u {username} -p{password} --databases {db-name-1} {db-name-2} > {db-backup.sql}
If you want to backup all databases you should use the option -all-databases. In this case you don't have to select all databases manually.

mysqldump -u {username} -p{password} --all-databases > {all-db-backup.sql}
If the database is very big you can compress the output of mysqldump using gzip and for output will have a gzip file.

mysqldump -u {username} -p{password} {db-name} | gzip -9 > {db-backup.sql.gz}
To extract the file from {db-backup.sql.gz} backup use this command:

gunzip {db-backup.sql.gz}
Schedule MySQL backup

Using the commands mentioned on this tutorial now is easy to schedule the backup in order to have an automatically backup procedure. This will save you time.

This is an example of midnight cron job:

crontab -e
0 0 * * * mysqldump -u {username} -p{password} --all-databases | gzip -9 > {full-path-backup-file-`date '+\%d-\%m-\%Y'`.sql.gz}
full-path-backup-file is the full path of backup folder, example:

On crontab script is necessary to escape the "%" character with "\"

The text inside `date '+%d-%m-%Y'` will interpreted as a Linux command.

#date '+%d-%m-%Y'

#date '+%d-%m-%Y'.sql.gz

In this example the actual backup file will be: /backups/db/my-web-app-20-09-2015.sql.gz

Using a password on the command line interface can be insecure.
You should set the login credentials in ~/.my.cnf file (With permissions 600)


Then you don't have to specify the password for mysqldump.

Restore MySQL database

To restore a database from backups you can use this command:

mysql -u {username} -p{password} {db-name} < {db-backup.sql}
If the backup database is compressed you have to un-compress it and then restore by using this command:

gunzip < {db-backup.sql.gz} | mysql -u {username} -p{password} {db-name}
To restore a database that already exist use mysqlimport command:

mysqlimport -u {username} -p{password} {db-name} {db-backup.sql}
As you see from the command there is no need for "<" character at this case.