Database Backup

MySQL Database Backup

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)