How to backup and restore your MySQL database using command line mysqldump?

MySQL is a powerful database engine (from my side of view ;-)) But it also might have several problem. Ie: a corrupted table after database crash.

Here the simple way to backup your MySQL database or table from command line.

Let’s start…

To backup:

Login to your server via SSH.

I’m usually using the following format:
mysqldump -u [username] -p [database_name] [table_name] > [backup_filename]
It will then ask for [username] password.

To backup a database:
mysqldump -u myuser -p myuser_db > myuser_db.sql

To backup a specific table:
mysqldump -u myuser -p myuser_db sometable > myuser_db_sometable.sql

After executing the command it’ll ask for password. This is not your server username password but MySQL username password.

The resulted file is a SQL file, we can do import it directly to MySQL.

Optional: you can compress the file to save bandwidth if you need to transfer it.
Compress with zip:
zip -9 myuser_db_sometable.zip myuser_db_sometable.sql

The “-9” option is for compress better, means highly compressed. If time is the essence, use “-1” option, it compress faster — it also means, the resulted file size will probably bigger than “-9” option. To store only — without compressing, use “-0” option.

To import:

Login to your server via SSH.

Optional: If the backup file is compressed with zip.
Uncompress it.
unzip myuser_db_sometable.zip

Login to your MySQL console:
mysql -u myuser -p
It’ll ask for myuser password, type it and hit enter.

Select a database:
use myuser_db;

Import the backup file:
\. myuser_db_sometable.sql

Exit MySQL console:
\q

To get help in MySQL console, type:
\h

Good luck!

Tagged with 
About sepedatua
I am nothing special, of this I am sure. I am a common man with common thoughts and I’ve led a common life. There are no monuments dedicated to me and my name will soon be forgotten, but I’ve loved another with all my heart and soul, and to me, this has always been enough.

Leave a Reply