MySQL Backup Using Shell Prompt

When we have a huge mysql database, often phpmyadmin proves to be of no use when it comes to taking database backups. This is when you can use your shell access to generate a backup and then download it without interruption.

Advantages of such technique is that, if the backup is huge, you can resume it later. Also this works faster as compared to taking backups in phpmyadmin. Moreover you can finally use your console provided with VPS if you haven’t yet used it.

So here we go,

Firstly open your shell prompt using any SSH client (e.g. Putty)

Now change your location to the directory in which you want the backup file to be generated.

Say for e.g.

cd /home/admin/nimishprabhu.com

Now, to take backup of your database, you need to know your mysql id and password.

Once you have reached the desired directory, fire the following command

mysqldump -u root -p "npblog" > backup.sql

In the above example,

root is the user

npblog is my “DATABASE” (not table) name.

and backup.sql is the database backup filename.

If you wish to directly store the file at the above location, you can also use the following command from any directory :

mysqldump -u root -p "npblog" > /home/admin/nimishprabhu.com/backup.sql

Once you fire the command, it will ask you for the password.

Type password and hit enter.

Wait for few minutes, this depends on the size of the database you are taking backup of.

Once done, you can check that the file backup.sql is generated.

Download it and store it for future use.

But generally if the database is too large people prefer compressed database backup.

You should use the following command for compressing the sql file before storing :

mysqldump -u root -p "npblog" | gzip > /home/admin/nimishprabhu.com/backup.sql.gz

You can then use your FTP client or directly use the browser to download the MySQL Backup generated using Shell Prompt in Linux.