How to Backup your MySQL databases manually with mysqldump

MySQL provides us the tool required to do this: mysqldump. As the name implies, this can make a dump of one database or even to all the databases on the server. You can see all the options on its help manual or on the mysql site.

Basically this is used like:

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]

based on the fact that you need to backup one database, more than one, or all the databases. All the available options are well documented, so let me show you just a simple example how to backup and restore a single database. Let’s say that the database is called ‘wordpress‘ ;).

To backup this database and save it in a file we run:

mysqldump wordpress > wordpress.sql

I am not using any other options; just create a dump of the database.

Now let’s assume that we need to restore this backup. How do we do this? Well as simple as this:

mysql wordpress < wordpress.sql

This will run all the backup file against the existing database as a set of mysql commands (that is what the backup file really is).
Note: this will delete all the existing data in the tables of this database and replace them with the data from the saved file.

If you want to save all the databases existing on the server:

mysqldump -A > all_dbs.sql

Now, in the example from above I have assumed that you are able to connect directly to the mysql server, without the need to enter any other parameters (if you have configured properly your .my.cnf, or if the details are the default ones: localhost, root, no password – not recommended… you should have a password!). If this is not the case, add to the command line the usual mysql parameters (the ones that are needed of course):

 -h hostname -u username -p password

and the command will look like this:

mysqldump -h hostname -u username -p wordpress > wordpress.sql

This covers the basic usage of saving and restoring one mysql database with mysqldump. Let me know if you are using mysqldump in a special way and if you want to share this with other peoples.

• Bio
• Latest Posts

Gishore James

Manager Technology at Quikr

I am a highly successful, competent team member with a background of extensive hands-on project implementation experience. My success has been both internal to the companies to which I’ve contributed and external to clients and customers.Having the knowledge of setting up efficient & cost effective technology solutions, internet innovations, large scale IT implementations, helps me to find out the unique one in the way of my career.

Latest posts by Gishore James (see all)

• Microsoft Office for iPhone – Changing Strategy of Microsoft - June 16, 2013
• Guidelines for International websites from Google - June 13, 2013
• Google is to Change the rankings of smartphone search results - June 12, 2013

Backup, bases, command, configuration, configure, connection, create, databases, file, help, hostname, mysql, passwords, roots, server, servers, sites, table, tools, what

Tech Guru