Join Us!

Spaces are allowed; punctuation is not allowed except for periods, hyphens, and underscores.
A valid e-mail address. All e-mails from the system will be sent to this address. The e-mail address is not made public and will only be used if you wish to receive a new password or wish to receive certain news or notifications by e-mail.
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Enter the characters shown in the image.


Enter your Project Envision username.
Enter the password that accompanies your username.
Request new password

Backup and Restore MySQL Database

The possibility to backup and restore database is crucial for every Database Administrator. Below article presents the usage of mysqldump command line program for making backups of mysql databases. The restore of such a backup is done by mysql client utility.


Using mysqldump you can backup just one specific database or you can backup all databases from MySQL server. For security purposes we should create a user who sole responsibility will be making backups. The required privileges are SELECT and LOCK TABLES on desired databases. CREATE USER command was added in MySQL 5.0.2 and it is used to create user we will use for making backups. Then we use grant command to assign necessary privileges to our user, The string on *.* (star dot star) is used to assign those privileges to our user for all databases on MySQL server.

mysql> create user backupuser@localhost identified by 'b@ckupP@ssw0rd';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select, lock tables on *.* to backupuser@localhost;
Query OK, 0 rows affected (0.01 sec)

To backup database called example we can use below command

mysqldump --opt -u backupuser -pb@ckupP@ssw0rd example > example.sql

The option –opt is a shortened for of following options:

--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.

--add-drop-table Add a DROP TABLE statement before each CREATE TABLE statement
--add-locks Surround each table dump with LOCK TABLES and UNLOCK TABLES statements
--create-options Include all MySQL-specific table options in the CREATE TABLE statements.
--disable-keys For each table, surround the INSERT statements with disable and  enable keys statements
--extended-insert Use multiple-row INSERT syntax that include several VALUES lists
--lock-tables Lock all tables before dumping them
--quick Retrieve rows for a table from the server a row at a time, which means without memory buffering.
--set-charset Add SET NAMES default_character_set to the output

There are many more options you can specify, for details check MySql Documentation or manual page for mysqldum (man mysqldump). As of MySQL 5.0 –opt is the default option.


To restore database backed up using mysqldump program we will use mysql client program. We usually restore databases as the user root, because permissions to drop objects that are being imported are required.

mysql -u root -pP@ssw0rd example < example.sql

If we want to restore our database to different database, for example to create a development database, we have to create the new database first. If we wont do it we the following error will be returned.

linq:~/test# mysql -u root -pP@ssw0rd example2 < example.sql ERROR 1049 (42000): Unknown database 'example2'

To successfully import our dump we must create example2 database, unless we are using –all-databases option or –databases, but this is a topic for another post.

linq:~/test# mysql -u root -pP@ssw0rd 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3311
Server version: 5.0.51a-24+lenny1 (Debian) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database example2;
Query OK, 1 row affected (0.00 sec) mysql> exit
linq:~/test# mysql -u root -prootP@ssw0rd example2 < example.sql