Backup and Restore MySql Database

MySql Provides the Basic backup using command mysqldump. This is powerful command and can take backup as a SQL CREATE TABLE and INSERT INTO TABLE commands. The output is in a text file and you can directly run this text file on the command MySql command prompt for restoring the database.

Backup Database :

Using mysqldump command is one of the way to take backup. This command runs through the windows command prompt or the Linux shell prompt. You can use the command as follows :

C:>\ mysqldump -p -u root --databases db_name > backup1.sql

$ mysqldump -p -u root --databases db_name > backup1.sql

This will prompt you for entering password for the user "root". After successful authentication and authorization the SQL file "backup1.sql" will be created in your C:\ .

While having this command as a backup tool, MYSQL provides the LOCK TABLES , UNLOCK TABLES , DISABLE KEYS and ENABLE KEYS command . This helps you while restoring database.

For Backing up specified tables only from database. Use following command.

C:>\ mysqldump -p -u root --databases db_name --table table_name1 table_name2 > backup1.sql

$ mysqldump -p -u root --databases db_name --table table_name1 table_name2 > backup1.sql

Restoring Database :

There are two ways of restoring database created with the backup command. One is to use command line utility and another is run SQL command.

Using Command Line :

For restoring The database using Command Line use following Command :

C:>\ mysql -p -u root db_name < backup1.sql

$ mysql -p -u root db_name < backup1.sql

Using SQL query :

For restoring using SQL query you will need to log in into MySQL database first.After logging into MySQL run following query :

mysql> source c:/abc.sql

Back Up Database as Compressed File (Linux):

Using command line power of Linux you can convert the sql file into a compresed file as the mysqldump generates the SQL file.

$ mysqldump -p -u root --databases db_name --table tblname | gzip > backup1.zip

Restoring Database from compressed file (Linux) :

You can restore the compressed file using following command.

$ gzip < backup1.zip | mysql -p -u root db_name