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.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.

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:

To get help in MySQL console, type:

Good luck!

No Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: