How To Backup And Restore MySQL With Mysqldump

mysqldump

What is Mysqldump?

Mysqldump is an excellent tool for backing up the contents of your databases to a file. This file can then be moved around, stored offsite and and restored with ease.

Backing Up MySQL

Backup A Single Database

To backup a single database you need to have the sql login for a user that has access to that database. In our example we will be using root.

The format of the command is:

mysqldump -u root -p[root_password] [database_name] > filename.sql

If we wanted to backup a database called ‘wordpress’ and save it to a file named “wordpressdump.sql”, we would use the command

mysqldump -u root -psomepassword wordpress > wordpressdump.sql

Make sure you do not forget the > symbol, as that is what redirects the output of the command into the file.

Backup Multiple Databases

Instead of running the mysqdump command multiple times to create separate files, it is possible to backup multiple databases in a single go.
The format of the command is:

mysqldump -u root -p[password] --databases [list of databases separated by spaces] > outfilefilename.sql

To see a list of tables that are available to backup you can run the commands:

mysql -u root -p<rootpassword>

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| stuff              |
| mysql              |
| wordpress          |
+--------------------+
4 rows in set (0.00 sec)

So if we wanted to backup both the wordpress and stuff databases we would run

mysqldump -u root -pmypassword --databases wordpress stuff > wordpressstuffdbs.sql

Now a full dump of both databases should be in your output file.

Backup All Databases

Now some people have a large number of databases and do not want to bother specifying every single one. It is possible to tell mysqldump to simply dump every database.

The format of this is:

mysqldump -u root -p[password] --all-databases > all-databases.sql

Backup Single Table

While it is less common, it is possible to have mysqldump backup only a single table out of a database

The format of this is

mysqldump -u root -p[passsword] [databasename] [tablename] > outputfile.sql

If we wanted to backup the a database called “stuff”, but only the table “cool_stuff”, we would run the command

mysqldump -u root -pmypassword stuff cool_stuff > cool-stuff-table.sql

Restoring MySQL

Restoring A Single Database

If you backed up a single MySQL database to a file and want to restore/import that into mysql, we have to make sure that the database we want to restore into is created first.

In this case lets assume we want to restore our “wordpress.sql” file into a wordpress database.

mysql -u root -p<password>
mysql> create database wordpress;
Query OK, 1 row affected (0.02 sec)
mysql>exit

This will make sure the database has been created. The format to actually do the restore is:

mysql -u root -p[password] [database_name] < dumpfilename.sql

Note how the < symbol changed directions and is now pointing into the command as it sends the data into mysql.

So to restore our wordpress.sql file into the empty wordpress database we created, we would run:

mysql -u root -pmypassword wordpress < wordpress.sql

Now the entire contents of the database that was dumped into the wordpress.sql file should be in your wordpress database.

Restoring Multiple Databases

Restoring a single database is nice, but what if you took the approach of dumping multiple or all the databases to a single file. Restoring those is just as easy. It follows the format of:

mysql -u [username] -p [password]  < [dumpfile].sql

There is no need to create a database before doing it this way. So to restore our “all-databases.sql” dump file we would run:

mysql -u root -p mypassword < all-databases.sql

Once you run this command all the databases you backed up into the single dump file should now be in your system.

Conclusion

After reading this guide you should understand how to backup your databases to a file. This can be a great tool for migrating your systems to a new server or backing up your databases in case of server failure. If you do choose to uses this as a backup method we highly recommend you backup to an offsite server to avoid loosing your backups if you lose your main server.

CC BY 4.0 This work is licensed under a Creative Commons Attribution 4.0 International License.

Alex Wacker has written 16 articles

I am the founder and owner of Subnet Labs LLC. Impact VPS is one of our VPS brands. Linux, virtualizaton and the internet amaze me and I enjoy learning new things every day about them.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>