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 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)
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.
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.
This work is licensed under a Creative Commons Attribution 4.0 International License.