Backup and Restore MySQL database in Linux via terminal

Personally i think, that Linux terminal is the strongest application/tool whatever you name it. Yesterday i was restoring a MySQL database size of 5MB via MySQL Administrator application, and it crashed several times. Actually i use it because the easy way to have GUI with few clicks, but that doesn’t mean i never used terminal for such things. But this crashing made me think of this powerful tool to use and use it forever..

So lets have backup our database 1st via terminal.

1- The 1st thing you need to do is open terminal(konsole).

2- mysqldump will be used to backup the database we want to.

mysqldump -u Username -p  databasename > backup.sql

Username = this is your database username
databasename = the name of your database which you want to backup
backup.sql = the filename for your database backup

The text in red should be changed according to your specific MySQL/database details. You can also give path to the filename where you want to save the backup file like: /home/mian/backup.sql.

Now lets run it in real form:

mysqldump -u admin -p storedb > /home/mian/storedb.sql

By entering this command and hitting Enter key will ask you MySQL password for user admin. By doing so will backup storedb database to the path given.

Now lets restore storedb database.

1- I assume that terminal is open.

2- Run this command in terminal:

mysql -u admin -p storedb < /home/mian/storedb.sql

This command is not much different except we don’t have mysqldump this time. Which means we are not dumping any database, just to restore one. It will also ask for the password for user admin to proceed.

If you want to run bash script to do the job for you by cron at specific times, i had written this script in past, but didn’t get time to update it regularly, but still is enough to do the backup archives with no manual running from command line.