How to back up and restore MySQL Database

Good day my dear Linux Yogi’s,

in today’s illustration I am going to show you how you can do a backup and restore your MySQL Databases on the command line interface. The CLI way is always good to know because you can build scripts and automate the process.

Let’s get started.

If you have a MySQL Server running you also have the program needed for the backup and restore. You will be using the mysqldump tool for the backup and mysql for the restore. The syntax is quiet simple.

mysqldump -u <username> -p <password> <Database_name> > backup_file_name.sql

Here are the option explained:

  • -u: this option passes on the username
  • -p: this option can pass on the password or if left empty it will ask for it
  • Database_name: this will be the name of the DB that you want to back up
  • > backup_file_name.sql: this will be the name of the backup file

Ok, lets say you have a database by the name of “user_login_activity” and your username for the database server is root with a password of “MyPassword” and you want to back it up to user_login_activity_backup.sql. The command you need to use looks like the following:

sudo mysqldump -u root -p MyPassword user_login_activity > user_login_activity_backup.sql

If the command was successful it will not return anything. Remember on Linux no news are good news. So lets just pretend that your database backup is huge like 500mb or bigger. You could use the commandline switch -c to compress the output but this is not so effective and only saves you a little bit of space. It would be more beneficial to pipe the output through a compression tool like gzip. The next example will just do that.

sudo mysqldump -u root -p MyPassword user_login_activity |gzip -9 > user_login_activity_backup.sql

Try this for your self and you will be impressed to see how much more this backup file will be compressed.

Okay now that you have your backup how about to restore it? Well the restore is quiet simple as well. You have to use the mysql command with the following syntax:

sudo mysql -u <username> -p <password> <database_name> < database_backup.sql

The explanation is very much the same.

  • -u: this is for the database server user name
  • -p: this is for the database server user’s password
  • <database_name>: this will specify the database name that the server should create
  • < database_backup.sql: this is the name for the backup file and also directs the input into the database.

Let’s pretend that you like to use the same information as above. The command would look like the following.

sudo mysql -u root -p MyPassword user_login_activity < user_login_activity_backup.sql

or the following command if you like to restore from a compressed gzip file.

sudo gunzip <user_login_activity_backup.sql.gz |mysql -u root -p MyPassword user_login_activity

Here you go. This concludes this illustration. I hope you like it and think it is useful. Please follow my blog and register to the forum to discuss further issues. If you like me write about something that working on drop me a line using my contact form.

thank you for reading until next time, Namaste my friends.