How to Create a mySQL Backup using Command Line

Whether you’re running a LAMP stack on Ubuntu or CentOS, it is usually very good practice to back up your MySQL databases to prevent data loss.

Here we will teach you how to do this with the help of the mysqldump command.

Your Ubuntu Server is now up and running with a LAMP stack in your data center, and on that server you setup yourself runs a database or maybe even a few databases for that matter. Things are getting exciting and then you remember. They must be backed up.

What you will need

If you already have MySQL up and running and all the necessary software is already installed, you are already on the right track.

In order to start this task of backup, you will need to make sure that you have the password for the MySQL user that has access permissions to the database(s) in question.

MySQL provides a great command line utility to take backups of your MySQL database. mysqldump command line utility is available with MySQL installation (bin directory) and can be used to achieve this.

Step by Step

Login to your server with root user
Use following command for taking backup of your MySQL database using mysqldump utility.

mysqldump –u[user name] –p[password] [database name] > [dump file]

Now to make sure the command was executed and the backup file was created using the command

ls –l

on the same path and you should see the database backup file.

Backing it all up

Don’t want to just backup one database? No Problem!

If you want to back up all of your databases, you can accomplish this with the –all-databases option, like so:

mysqldump -u USER -p --all-databases > /mnt/backups/all_databases.sql

And That’s It!

Congrats! You’ve successfully backed up your MySQL databases successfully. Now save them somewhere safe, and hopefully you’ll never need to revert them due to a failure.

serverhub