MySQLDump In Many Many Ways

Again, there is another need to blog this thing. I encountered this situations where dumping the database using default options does not fit the purpose, so today, we will be playing with mysqldump options for making MySQL backup.

Simple Backup

Using the simplest command to backup a MySQL database, it looks like this:

mysqldump db_name -u db_user -p > db_backup_date.sql

That will work for local databases. For remote database server, you may try to use SSH to remotely login and do the same command. However, it you really need to connect to a remote MySQL server, here is the typical command for mysqldump.

mysqldump db_name -h hostname -u db_user -p > filename.sql

A Little Bit Advanced

Working with PHP + MySQL projects everyday, there comes a time when you will do a lot of work on your MySQL databases, rather than your code. Like, backup here, restore there, and backup again. Things get complicated when you are dealing with multiple databases on several locations.

So, we are going to show some of the handy mysqldump options.

If you want to backup the database with only the selected tables, this is the typical command:

mysqldump db_name -u db_user -p –tables table1, table2 > filename.sql

That will save you a lot of time wasted for dumping the whole database instead of the selected tables. Things will go more complicated with database structure updates. You updated your database design from your development server, but you also need to update the live server with lots of existing data on it.

Because of that, we will do more cool and advanced options for mysqldump.

Finally, the more useful one

Honestly, this is some kind of summary of useful mysqldump options. So here we go:

-h is for hostname
-u is for user name
-p is for password, leave empty to prompt for password
–tables is for specifying the table names
–no-create-info is for dumping the data only
–no-data is for dumping the table structure only

And relying on the default settings, mysqldump will automatically put drop table if exists on your dump file. Below are the examples of those options.

mysqldump db_name -u db_user -p –no-create-into > data_only.sql
mysqldump db_name -u db_user -p –no-data > structure_only.sql
mysqldump db_name -u db_user -p –no-create-info –tables table1, table2 > table_data_only.sql

It is as simple as that, still many beginners are scared when they see simple commands and try to use GUI instead.

Restore Backup

Before we forget, we need to restore our backup sooner or later after we dumped it. The command is very simple:

mysql -D db_name -u db_user -p < backup_file.sql

There are more advanced options, but I listed the most useful for typical MySQL databases.