It is neccessary to take the backup of databases on regular interval, because incase of any emergency we can restore it. To achieve this follow the below steps.

Step 1: MySQL backup

Syntax

mysqldump -u [username] -p[password] databasename > target

where,

username – mysql username
password – mysql password
databasename – exact mysql database name
target – backup location

Taking backup of single database

[root@server ~]# mysqldump -u root -pwelcome employees > /opt/employees.sql

[root@server ~]# file /opt/employees.sql
/opt/employees.sql: ASCII text, with very long lines

[root@server ~]# ls -l /opt/employees.sql
-rw-r--r-- 1 root root 168372935 May 8 16:53 /opt/employees.sql

Taking backup of multiple database

[root@server ~]# mysqldump -u root -pwelcome --databases employees wordpress > /opt/emp_wordpress.sql

[root@server ~]# ls -l /opt
total 328912
-rw-r--r-- 1 root root 168372935 May 8 16:53 employees.sql
-rw-r--r-- 1 root root 168426484 May 8 16:53 emp_wordpress.sql
drwxr-xr-x. 2 root root 4096 Feb 22 2013 rh

Taking backup of all database

[root@server ~]# mysqldump -u root -pwelcome --all-databases > /opt/all_databases.sql

[root@server ~]# ls -l /opt
total 494024
-rw-r--r-- 1 root root 169071345 May 8 16:55 all_databases.sql
-rw-r--r-- 1 root root 168372935 May 8 16:53 employees.sql
-rw-r--r-- 1 root root 168426484 May 8 16:53 emp_wordpress.sql
drwxr-xr-x. 2 root root 4096 Feb 22 2013 rh

Step 2: MySQL Restore

Restore single database

Before restore a database, we should create an empty database on that machine.

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

[root@server ~]# mysql -u root -pwelcome empsample < /opt/employees.sql

Restore a single database from dump of all the databases

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

[root@server ~]# mysql -u root -pwelcome --one-database wordsample < /opt/all_databases.sql