How to get backup or dump MySQL Database and Restore it.

To take a backup of Mysql database syntax of a command would be:

# mysqldump -u [username] –p [database_name] > [dump_file.sql]

The parameters of the syntax are as follows.
[username] : A valid MySQL username.
[password] : A valid MySQL password for the user.
[database_name] : A valid Database name you want to take backup.
[dump_file.sql] : The name of backup dump file you want to generate.
Suppose we have a database Employees.

[root@localhost ~]# mysqldump -uroot -p Employees > Employees_dump.sql
Enter password:***********
Backup Multiple databases:

Syntax:-
# mysqldump -u [username] –p –databases [ Db1 db2 db3…..] > [dump_file.sql]

[root@localhost ~]# mysqldump -uroot -p –databases emp1 emp2 emp3 > Emp_db_dump.sql
Enter password:***************
–database is a switch or option for selecting multiple dbs. and emp1,em2,emp are various databases in mysql.

Backup all databases:

[root@localhost ~]# mysqldump -uroot -p –all-databases > Emp_all_db_dump.sql
Enter password:*************
Backup a specified table in a database:

[root@localhost ~]# mysqldump -uroot -p Employees DEPT > DEPT_table_dump.sql
Enter password:*************

DEPT is a table of Employees Database
Restore Mysql Dtabase:

To restore a database in mysql you must create an empty database and restore the database with mysql command

[root@localhost ~]# mysql -uroot -p
Enter password:*************
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, 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>create database Employees;
Query OK, 1 row affected (0.00 sec)
mysql>quit
bye
[root@localhost ~]# mysql -uroot -p Employees < Emp_db_dump.sql
Enter password:*************
Restore a Database that already exist in Mysql:

If you want to restore a database that already exist in Mysql,then you need to use the Mysqlimport command

[root@localhost ~]# mysqlimport -uroot -p Employees < Emp_db_dump.sql
Enter password:*************

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s