How to backup and restore mysql database

how to take mysql database backup and restore it again.

mysqldump -u root -p database_name > database_backup_name.sql     – It is used to take mysql backup

mysql -u root -p database_name < database_backup_name.sql     – It is used to restore the mysql backup

Mysqldump – Backup Database

First login to mysql database and check what are all the databases available in it.

root@linuxinternetworks.com# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 62
Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)

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> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| knowledge_farm |
| mysql |
| performance_schema |
| phpmyadmin |
| test |
| testing |
| vanilla |
| wordpress |
+——————–+
9 rows in set (0.00 sec)

mysql> exit
Bye

These are all the databases available in my mysql. Let me take “wordpress” database here to back up.

root@linuxinternetworks.com# mysqldump -u root -p wordpress > wordpress_backup.sql
Enter password:
root@linuxinternetworks.com# du -sh wordpress_backup.sql
384K wordpress_backup.sql

Now the “wordpress” database has successfully backuped.

Mysql Database Restore

To restore the backuped database in your mysql, you must check whether the database name is already exist or not in your mysql. Here my target database name to restore in the database is “wprecovery”

root@linuxinternetworks.com# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 62
Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)

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> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| knowledge_farm |
| mysql |
| performance_schema |
| phpmyadmin |
| test |
| testing |
| vanilla |
| wordpress |
+——————–+
9 rows in set (0.00 sec)

mysql> exit
Bye

My target database name “wprecovery” is not availbale in the database, So I must create it before restoring.

mysql> create database wprecovery;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| knowledge_farm |
| mysql |
| performance_schema |
| phpmyadmin |
| test |
| testing |
| vanilla |
| wordpress |
| wprecovery |
+——————–+
10 rows in set (0.00 sec)

mysql> exit
Bye

Now I am going to restore the database.

root@linuxinternetworks.com# mysql -u root -p wprecovery < wordpress_backup.sql
Enter password:

Now check your database for your verification

mysql> use wprecovery;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+———————–+
| Tables_in_wprecovery |
+———————–+
| wp_commentmeta |
| wp_comments |
| wp_links |
| wp_options |
| wp_postmeta |
| wp_posts |
| wp_term_relationships |
| wp_term_taxonomy |
| wp_terms |
| wp_usermeta |
| wp_users |
+———————–+
11 rows in set (0.00 sec)

The database has successfully restored. :)

Advertisements

4 thoughts on “How to backup and restore mysql database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s