Taking Backup of MySQL database

MySQL is a very popular and widely used open source database. Most of the software engineers might have used mysql database atleast once. Keeping a backup of a database or migrating the tables/databases from one database server to another is a very important task. In mysql this task can be performed in a very simple way.

MySQL is providing a commandline utility to perform this operation. The command is mysqldump.

For getting more details about mysqldump, type the following command

>mysqldump --help

For taking the backup of a complete database, type the following command.

>mysqldump --databases [DB1 Name] ....[DBn Name] -u <username> -h <hostname> -p

This will print the entire database dump to the console. For storing the output of this in a file, do the following operation.

 >mysqldump --databases [DB1 Name] ....[DBn Name] -u <username> -h <hostname> -p >> dump.sql

Here the contents will be written to a file locally.

If you want to take the back up of all the contents in a mysql server, we can back up all databases.

The command to perform this operation is given below.

>mysqldump --all-databases -u <username> -h <hostname> -p >> dump.sql

In the above commands, <username> is the mysql username and <hostname> is the hostname or ip address of the machine where the mysql server is running.

Now your database is backed up. The next step is how to load this dump file to another mysql instance.

This is a very simple task.

• Copy the dump file to the machine where you are planning to perform the following operations
• Login to mysql instance with valid credentials
• In the mysql prompt, type the following command.

mysql> source <path to the dump file>

This will load the contents inside the dump file to the new mysql instance.

If you want to know more about the mysql dump file, open the file in a text file editor and read the contents. It contents the DDLs and DMLs that recreates your databases and tables.