How to backup MySQL with "mysqldump"
MySQL is a free, open source database management system. It is used widely in the Internet. Some famous companies, such as Yahoo!, choose MySQL as their databases. Most of web hosting companies provide MySQL and there are many free scripts like WordPress, Joomla that use MySQL. As it is widely used in the Internet and the data is important, it is a very useful skill to master how to backup your MySQL database.
There are several methods to backup MySQL. The most general (maybe easiest) is using "mysqldump". Mysqldump is a MySQL backup utility which is included in MySQL package. It is a command line tool and can backup tables or databases. To explain how to use mysqldump in detail, I will take an example.
I installed WordPress, a blog script, in my computer. It uses a MySQL database called "wordpress". It has 10 tables. See below:
Command line window
Now there are 2 posts in the "wp_posts" table. We don't care about the contents of this table. We just need to know it contains all the posts. Let's check it in browser.
WordPress blog posts
You see two posts and now WordPress works well. So it's a good chance to backup the database. Let's continue. In the command line window (Windows command line window, not the MySQL terminal window. In Linux or Unix, it is called "shell") type the following command:
mysqldump -u root -p wordpress wp_posts > D:\wordpress_posts.sql
"-u root -p" means accessing MySQL database as "root", the super user account and need to enter the password. It will backup all the contents in "wp_posts" table to the file D:\wordpress_posts.sql. It just contains lots of SQL statements which can rebuild the table. You can use your favorite text editor to open it and to see what is in it. Next, let me damage the table using "truncate" in MySQL terminal window:
"truncate table" is an SQL statement that can empty all the contents in a table. In addition, "truncate table" isn't equal to "drop table". The latter one delete the whole table including the table itself. After drop a table, you won't find it with "show tables". However, after "truncate table" you can still see it in "show tables" but the table is empty. Now let's come back to our topic. Refresh the browser and you will see the posts have been lost:
Corrupted table
Question: How to backup the whole database?
Answer: Just omit the "table_name" argument. For example, mysqldump -u root -p wordpress > D:\wordpress.sql
Question: How to backup all the databases in MySQL?
Answer: Use "--all-databases". For example, mysqldump -u root -p --all-databases > D:\db.sql
Restoring
Now let's to restore it. Enter the following command in the command line window (or shell):
mysql -u root -p wordpress < D:\wordpress_posts.sql
This will restore the "wp_posts" table. You need to specify the database name. Here, it is "wordpress". The following command restore the whole database:
mysql -u root -p wordpress < D:\wordpress.sql
If you want to restore all the databases (you used "--all-databases" argument to generate a backup file before), you don't need to specify the database name.