Step 1 - MySQL backup with mysqldump

I don't want to collect all of the reasons why a backup is important. If you are reading this page you probably know them.

MySQL backup solutions:

There are many ways how you can create a backup with MySQL. Here is a list of the most important backup groups:

• Logical vs. physical backup
• Online vs. offline backup
• Local vs. remote backup
• Full vs. incremental backup

Besides this the backup possibilities depend on what kind of database engine you use. In this tutorial I only demonstarte mysqldump as it is more general than the others and can backup all kind of tables.

Using mysqldump to backup your data:

Mysqldump creates an sql file as the result of the backup. Later you simply can execute this file to recreate the MySQL database.

You can backup only some tables, the complete database or all available databases depending on how you call mysqldump. The most simple way to backup a single MySQL database is the following:

Code: 
mysqldump -r c:\backup\myDb.sql myDb
Database F1

This code generates the backup of the myDb database in the c:\backup directory

Step 2 - More mysqldump usage examples

The example on the previous page was quite simple. But what if you want to backup only one table, or all of the databases at once, or you have restricted access to the server?

Backup database with username and password:

If you are logged in as a normal user and want to backup a database with an other username and password you need to extend the options list in your mysqldump call. You can define the MySQL user with the -u option and the password with -p. In case of password definition you can write your password in the command as pure text or use an interactive backup. If you want to put the password into the command line then you must write your password right after the -p option without any space like this: -pmypass. If you write -p mypass then mysqldump will ask for your password and try to backup mypass as database.

So to backup our myDb database with username and password is the following:

Code: 
mysqldump -u user -ppassword -r c:\backup\myDb.sql myDb
Database F1

Backup only some tables: 

An other common wish is to export only some tables instead of the complete database. It is quite easy with mysqldump. You simply have to list the tables you want to export after the database name like here:

Code: 
mysqldump -u user -ppassword -r c:\backup\myDb.sql myDb user error
Database F1

This command exports the user and the error tables from the myDb database.

Backup all databases:

The last method I want to present in this tutorial is the backup of all available databases at once. Fortunately this is really very easy task. Simply use the --all-databases option as here:

Code: 
mysqldump -u user -ppassword -r c:\backup\myDb.sql --all-databases
 
Database F1

Final words:

There are a lot of other options you can use during your backup. You can find them on the mysqldump man page. Besides this as mysqldump generates text files it is not as fast as backup solution based on binary file copy.

discuss this topic to forum