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