Database backup and restore in MySql….

Backup the database:…..

To take the backup first log on to mysql to make sure the database name that needs to be backup….
[root@prac ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.5.27 MySQL Community Server (GPL) by Atomicorp

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>
mysql> show databases;
+——————–+
| Database           |
+——————–+
| information_schema |
| mysql              |
| rajesh_wp          |
| test               |
| vtigercrm          |
+——————–+
5 rows in set (0.00 sec)

Now I have the list of database and from that I wanna take the backup of rajesh_wp database.

Now exit from mysql.
mysql> exit
Bye
[root@prac ~]#

[root@prac ~]# mysqldump -u root -p rajesh_wp > /root/d01/rajesh_wp_bkp.sql

It prompts for the password. Enter your password for mysql. Then simply prompts to next line if the database successfully got the backup as follows….

I have given the file as rajesh_wp_bkp.sql. Its up to you. File name and extension are don’t matter. But I have given extension as sql to identfy the file as a database file easily.

[root@prac ~]# mysqldump -u root -p rajesh_wp > /root/d01/rajesh_wp_bkp.sql
Enter password:
[root@prac ~]#

Now you can check the .sql file whether the database has been got the backup or not.

The startup lines of the backup file should be as follows

MySQL dump 10.13  Distrib 5.5.27, for Linux (i686)
—
— Host: localhost    Database: rajesh_wp
— ——————————————————
— Server version       5.5.27

You will find the database name which has been taken the backup.

This backup file can be used in case of database failure or dropped.

Restore the database:……..

To restore the database issue the following command…

[root@prac ~]# mysql -u root -p rajesh_wp < /root/d01/rajesh_wp_bkp.sql

If the restore has been completed successfully then it prompts for the next line as follows…
[root@prac ~]# mysql -u root -p rajesh_wp < /root/d01/rajesh_wp_bkp.sql
Enter password:
[root@prac ~]#
You can log on to mysql and check for the database which has been restored…

That’s all about backup and restore of mysql database.