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. Records related to this Tenant from db892085391
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 to dump the customer database.

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

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  --complete-insert  db892085391 NETWORK_ACCOUNTS -t -w"number=1234" \
    > network_acc.sql

# mysqldump -u -p --complete-insert  db892085391 NETWORK_8c5b349851c041a28d19277a12dee8f0 \
    -t -w"number=1234" > network_tenants.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 dumped tables:

   # mysql –uroot –p relationals < network_customer.sql
   # mysql –uroot –p relationals < network_userdir.sql
   # mysql –uroot –p relationals < network_databases.sql
   # mysql –uroot –p db892085391 < network_acc.sql
   # mysql –uroot –p db892085391 < network_tenants.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.)

4. Set the admin user ID to "3" (the system user) in the tenant records:

   UPDATE db892085391.NETWORK_ACCOUNTS SET owner_id = '3' WHERE number = '1234';
   UPDATE db892085391.NETWORK_8c5b349851c041a28d19277a12dee8f0 SET owner_id = '3' 
       WHERE number = '1234';
   

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