How to take backup of MySQL database from linux console

It is good idea to take backup of your data, especially databases. In order to take a backup of MySQL database you can either use your root user (bad idea!) or create another user that will have just enough privileges to do the job. Once we make dump of MySQL database, we will use gzip utility to pack the database. Once you have file, you can either move it, copy it or just send it as an e-mail attachment. First, we will create a backup user. Fire up your console, log in to MySQL server and type the following command:

GRANT SELECT, LOCK TABLES, CREATE ROUTINE ON *.* TO 'BackupUser'@'localhost' IDENTIFIED BY 'BackupUserPassword'

Once user is created log off MySQL server and type in the following command:

mysqldump -u BackupUser -p BackupUserPassword database_name --routines | gzip > /path/to/backup/location/database_name.sql.gz

If you want to take backup of all databases running on you MySQL server, use the following command with “–all-databases” argument:

mysqldump -u BackupUser -p BackupUserPassword --all-databases --routines | gzip > /path/to/backup/location/database.sql.gz

Should you need to create backup files that includes date along file name, use the following argument in backup file name:

mysqldump -u BackupUser -p BackupUserPassword database_name --routines | gzip > /path/to/backup/location/database_name_`date +%d+m+Y`.sql.gz

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.