How To Backup And Restore MySQL Databases?

MySQL has been progressing at a rapid rate and now stands firmly and confident before the products coming from the stable of big corporations like Microsoft, IBM, ORACLE and Cybase. Over the years, efforts have been made to incorporate all the important features into it that a popular and successful RDBMS has got. One such feature is taking backups of the databases and restoring them, some thing which is very important and extremely crucial, especially for a regular user to be able to protect their vital data in event of database getting crashed or destroyed due to some untoward incident. In case of every business, major or minor, data has become the most important entity, moreover maintaining the data and other information manually in physical files and folders is increasingly becoming a thing of the past and obsolete. All the information and data today is stored in digital format in highly sophisticated computer servers. So all those who are running MySQL servers should know how to take regular backups of their database and restore it whenever they need.

Backing up the data through MySQLdump utility

The most popular tool, normally used to take the backup of the MySQL databases is MySQLdump. In order to use this tool to take your back up, you must first login to the computer in which MySQL is running. If MySQL is located far from your system, then telnet utility can be used to remotely login to that computer.

Syntax of the MySQLdump is as follows:

mysqldump -u [Username] -p [password] [databasename] > [backup_name.sql]

Here username is the database username, password is the database password, databasename is the name of the database and backup_name.sql is the filename for the database backup.

Example: Let’s consider the example of a sample database called mydata and see how its backup is taken in some situations. Let’s also assume that the database username and password both for the mydata is root.

Take the complete backup of the database, mydata, including the entire data,

mysqldump -u root -p root mydata > mydata.sql

Taking the backup of the table structure of all the tables only, excluding data

mysqldump -u root -p root --no-data mydata > mydata.sql

Restoring the database

After backing up the data, its next important use is to restore the backed up data in your system whenever needed. This restoration of database backup can be done by executing a very simple command,

mysql - u [username] -p [password] databasename < backup_name.sql

Example: If we want to restore the backed up database file, mydata.sql into a sample database say mydata then following command should be executed:

mysql - u root -p root mydata < mydata.sql

So this is how you can take backups of your MySQL databases and keep yourself confident and assured in the event of any sudden database or system crash.

Author: ucavik