How to take backup of MySQL database from the Linux console

It is a good idea to take backup of your data, especially databases. In order to take a backup of the MySQL database, you can either use your root user (a bad idea!) or create another user that will have just enough privileges to do the job. Once we make a dump of the MySQL database, we will use gzip utility to pack the database. Once you have the 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 the MySQL server and type the following command:

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

Once the 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 your 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 include the date along with file name, use the following argument in the backup file name:

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