For database backup, the mysqldump utility can be used. The mysqldump utility creates a SQL file that includes a set of commands to create database, drop tables, create tables, and insert statements for copying data into tables. As a default, mysqldump stores databases, tables and triggers, but not procedures. To store procedures together, please use –routine option.
To back up a database:
mysqldump -u[id] -p[password] --database [database] --routine > [outputfile].sql
mysqldump -u[id] -p[password] [database] --routine > [outputfile].sql
To back up some specific tables:
mysqldump -u[id] -p[password] [database] [table1] [table2] --routine > [outputfile].sql
For example, a database (nexoss_test) is dumped into a file (nexoss_test_backup.sql). The nexoss_test database includes one table (oss_test):
[ossadmin@labrat8-c62-64 ~]$ mysqldump -unexoss -pnexoss –database nexoss_test > nexoss_test_backup.sql
[ossadmin@labrat8-c62-64 ~]$ ls -alh
-rw-r+++--. 1 ossadmin oss 1.2M Jan 7 21:08 nexoss_test_backup.sql
To restore this dump file:
mysql -uroot -p[password] < [outputfile].sql
Or
mysql -uroot -p[password] -D[database] < [outputfile].sql
This operation drops the current table and creates the table with the backup file. For example, nexoss_test database is newly created at a remote machine (labrat3-c62-64) using the dump file.
[ossadmin@labrat3-c62-64 ~]$ mysql -uroot –p[rootpassword] < nexoss_test_backup.sql
To check the dumped database:
[ossadmin@labrat3-c62-64 ~]$ mysql -unexoss -pnexoss -Dnexoss_test
mysql> show tables;
++++++++++++++++++++++--+
| Tables_in_nexoss_test |
++++++++++++++++++++++--+
| oss_test |
++++++++++++++++++++++--+
1 row in set (0.00 sec)
mysql> select count(*) from oss_test;
++++++++++-+
| count(*) |
++++++++++-+
| 1170 |
++++++++++-+
1 row in set (0.00 sec)