Backing up and restoring your MySQL database

There are more than a few graphical tools to do this but I find the simple command mysqldump to be more than enough for this task.

I use mysqldump for backup and I use it to transfer up to date databases between my production and development servers.

What you need is a shell account on your web server or, if your MySQL configuration supports it you can run it from a workstation. It's generally a good idea to only let a user log in from localhost so that's the way I use it.

Creating the backup .sql file
To create the backup just execute the following command in the shell

That's it. This way the whole database dump gets redirected to a file called database.sql, you can of course use a more describing name for the file. Note, if you are working locally on the server you don't have to supply the -h switch since the command uses localhost by default. Now you can copy the file to safety or to a development server to be restored into development productions.
If you want to add drop table, it is a good idea if you already have a database in use in the development server and don't want to manually drop the tables, you can add the switch --add-drop-table to the command.
So let's take a look at restoring the database.

Restoring database from .sql file
To restore a database to a server we use the mysql command.

That's it. You should now have a working copy on the development or production server.

Further reading
Manual page for mysqldump
Manual page for mysql