MySQL backup tutorial - More mysqldump usage examples
In this tutorial I will show you a short and simple way how to create a backup copy of your MySQL database using the mysqldump utility.
|Name:||MySQL backup tutorial|
|Product:||See complete product|
Bookmark MySQL backup tutorial
Step 2 - More mysqldump usage examples
MySQL backup tutorial
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:
- mysqldump -u user -ppassword -r c:\backup\myDb.sql myDb
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:
- mysqldump -u user -ppassword -r c:\backup\myDb.sql myDb user error
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:
- mysqldump -u user -ppassword -r c:\backup\myDb.sql --all-databases
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.
Previous Step of MySQL backup tutorial
Tags: mysql backup tutorial, mysql backup mysqldump, mysql backup, mysqldump backup, mysql, backup
|MySQL backup tutorial - Table of contents|
|Step 1 - MySQL backup with mysqldump|
|Step 2 - More mysqldump usage examples|