How to Backup and Restore MySQL Database Using mysqldump

Nowadays we store our web applications data in databases. Do you backup your databases? t is very important to make regular backups of your data to protect it from loss. I store all my web application data in to MySQL database, so i going t show how to backup and restore the data in your MySQL database.

There are 2 way to perform backup and restore MySQL database. The easier way to perform backup and restore MySQL databases is use phpMyAdmin. phpMyAdmin is open source GUI tool to manage MySQL databases. phpMyAdmin allows users to save database dump as file and import it. But it probably won’t be a good idea if your database size too huge.

Another method to perform backup and restore MySQL database is using mysqldump command. I prefer use mysqldump command to perform backup and restore MySQL database cause it easy to use it. I can done the backup and restore by just enter 2 command.

Using mysqldump, you can backup a local database and restore it on a remote database at the same time, using a single command.

How to backup MySQL database

You can use mysqldump to create a simple backup of your database using the following syntax.

mysqldump -u [username] -p[password] [databasename] > [backupfilename.sql]
    • [username] – this is your database username
    • [password] – this is the password for your database
    • [databasename] – the name of your database
    • [backupfilename.sql] – the file to which the backup should be written.

Example:-

A) Backup single database, eg: database name->techkaki

mysqldump -u techkakiadmin -ptechkaki123 techkaki > techkaki.sql

B) Backup multiple database, database name: sugarcrm, wordpress

mysqldump -u webuser -pwebpassword –databases sugarcrm wordpress > multidb.sql

C) Backup all the databases

mysqldump -u root -pMysqlpassword –all-databases > all-databases.sql

 

How to restore MySQL database

It is very easy to restore MySQL database. Just refer following command syntax

mysql -u [username] -p[password] [db_to_restore] < [backupfile.sql]

Example:-

A) Restore database called techkaki

mysql -u root -pMysqlpassword techkaki <  techkaki.sql

In this tutorial, i just show the simple step to perform MySQL backup and restore. If you have any advanced syntax to perform MySQL database backup and restore, please share with me by leave your comment here.

Final word, please backup your database before the disaster happen. 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *