Database Backup and Recovery

Database Backup and Recovery

MySQL can be backed up using the mysqldump command - http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Tip: The replicated database server should be used for backups. To set up for it, see Configuring MySQL to Run on a Separate Server

Standard Database Backup

Dump the database

mysqldump –uroot –pxxx –all-databases –quick –routines –result-file=dumpfile.sql

Dump the database at regular intervals, using a Linux cron job

• Put the mysqldump command in a shell script - say, xyz.sh
• Setup cron job e.g. every day at 6 pm

 
0 18 * * * /yourscriptlocation/xyz.sh > /somedirectory/xyz.out 2>&1

• Use tar to compress the resulting dump file, to save space.
• Maintain daily backups for ten days or so, to reclaim the space they use.
• Maintain monthly backup. Save the dump of last day of the month, for example, and retain the dump file for a year or so.
• Store long-term backups offsite.

Standard Database Restore

Restoring all data from a dump file

1. Drop all the databases.
2. Restart mysqld
3. Start the restore process and run in background:

mysql –uroot –pxxxx < dumpfile.sql > dumpfile.out 2>&1 &

Restoring a single database or table from a dump file

• Drop the old database/table:

mysql –uroot –pxxxx $database < dumpfile.sql > dumpfile.out 2>&1 &

Backup and Restore for a Single Tenant

The items to include are:

1. The Tenant's database
2. Records related to this Tenant from relationals.NETWORK_DATABASES, relationals.NETWORK_CUSTOMER, and relationals.NETWORK_USER_DIRECTORY
3. Related accounts from db892085391.NETWORK_ACCOUNTS
4. Tenant's documents from documents/ and public_documents/ folders in the file system.

Back Up Tenant Data

Let’s say you want to back up data for a customer (tenant) whose account number is ‘1234’. Here is the process:

1. Dump the customer database.

   # mysqldump  -u –p  -R db1234 > db1234.sql
   

2. Dump records related to this customer:

   # mysqldump -u -p --complete-insert relationals NETWORK_CUSTOMER \
       -t -w"id=1234" > network_customer.sql
   
   # mysqldump -u -p  --complete-insert relationals NETWORK_USER_DIRECTORY \
       -t -w"customer_id=1234" > network_userdir.sql
   
   # mysqldump -u -p  --complete-insert relationals NETWORK_DATABASES \
       -t -w"id=1234" > network_databases.sql
   
   # mysqldump -u -p  db892085391 NETWORK_ACCOUNTS -t -w"number=1234" \
       > network_acc.sql
   

3. If the tenant/customer is an MSP, dump tenant records as well:

   # mysqldump -u -p  db892085391 NETWORK_ISV_SETTINGS  -t -w"id=1234" \
       > network_isv_settings.sql
   

Restore Tenant Data

Restore the data for customer #1234 from the dump files created above:

1. Create the database for the tenant if it doesn’t exist.

   mysql> create database db1234;
   

2. Restore the data.

   # mysql –uroot –p  db1234 < db1234.sql
   

3. Restore customer records from the related tables:
   • relationals.NETWORK_DATABASES
   • relationals.NETWORK_CUSTOMER
   • relationals.NETWORK_USER_DIRECTORY
   • db892085391.NETWORK_ACCOUNTS

   # mysql –uroot –p relationals < network_customer.sql
   

   (If you haven’t made changes to the relationals and db892085391 databases, you can safely choose not to restore the records from the related tables.)

Learn More

For more detailed information on database backup and recovery in MySQL, see:

• http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
• http://www.mydigitallife.info/2007/07/21/
  how-to-backup-and-restore-export-and-import-mysql-databases-tutorial