How to Backup MySQL Database

Taking regular backup of database is very important part of project development. Here's how we can take a backup of database using terminal. To do this we'll be using mysqldump command available with MySQL. Syntax:

$ mysqldump -u[username] -p[pass] -h[hostip] [dbname] > [backupfile.sql]

In above command,

-u specifies username to connect to MySQL,
-p as user password,
-h (optional, if on same machine) specific IP or hostname where MySQL is setup,
dbname is database to take backup,
backupfile.sql is your backup file name with .sql as ext.

Note: Replace values inside [] with actual value, such as [username] will be your MySQL username. Usage: To take backup of database named as "drupal" with root user, we need to run following command:

$ mysqldump -uroot -p drupal > drupal_dbbackup.sql

After running this command, it will prompt for password (notice the password doesn't exists in command), enter the password for root user and press enter again. If the user doesn't have the password, we can skip the -p parameter in command. Read part 2 of How to Backup MySQL Database series.

Reference: