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!