View source for Database Backup and Recovery

=== Database Backup and Recovery ===

MySQL can be backed up using the <tt>mysqldump</tt> command - http://dev.mysql.com/doc/refman/{{MySqlVersion}}/en/mysqldump.html

{{Tip| The replicated database server should be used for backups.<br/> To set up for it, see [[Configuring MySQL to Run on a Separate Server]]}}

====Standard Database Backup====
;Dump the database:
:{|
<pre>
mysqldump –uroot –pxxx –all-databases –quick –routines –result-file=dumpfile.sql
</pre>
|}

;Dump the database at regular intervals, using a Linux <tt>cron</tt> job:
:*Put the <tt>mysqldump</tt> command in a shell script - say, <tt>xyz.sh</tt>
:*Setup cron job e.g. every day at 6 pm
::{|
<pre> 
0 18 * * * /yourscriptlocation/xyz.sh > /somedirectory/xyz.out 2>&1
</pre>
|}
:* Use <tt>tar</tt> 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:
# Drop all the databases.
# Restart <tt>mysqld</tt>
# Start the restore process and run in background:
::{|
<pre>
mysql –uroot –pxxxx < dumpfile.sql > dumpfile.out 2>&1 &
</pre>
|}

;Restoring a single database or table from a dump file:
* Drop the old database/table:
::{|
<pre>
mysql –uroot –pxxxx $database < dumpfile.sql > dumpfile.out 2>&1 &
</pre>
|}

====Backup and Restore for a Single Tenant==== 

The items to include are:
# The Tenant's database
# Records related to this Tenant from <tt>relationals.NETWORK_DATABASES</tt>, <tt>relationals.NETWORK_CUSTOMER</tt>, and <tt>relationals.NETWORK_USER_DIRECTORY</tt>
# Records related to this Tenant from <tt>db892085391</tt>
# Tenant's documents from <tt>documents/</tt> and <tt>public_documents/</tt> 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: 

<ol>
<li>Dump the customer database.<pre>
# mysqldump  -u –p  -R db1234 > db1234.sql
</pre>
<li>Dump records related to this customer:<pre>
# 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
</pre>
<li>If the tenant/customer is an MSP, dump their service configuration records as well:<pre>
# mysqldump -u -p  --complete-insert  db892085391 NETWORK_ISV_SETTINGS  -t -w"id=1234" \
    > network_isv_settings.sql
</pre>
</ol>

=====Restore Tenant Data=====

Restore the data for customer #1234 from the dump files created above:
<ol>
<li>Create the database for the tenant if it doesn’t exist.<pre>
mysql> create database db1234;
</pre>
</li>
<li>Restore the data.<pre>
# mysql –uroot –p  db1234 < db1234.sql
</pre>
</li>
<li>Restore customer records from the dumped tables:
<pre>
# 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
</pre>
(If you haven’t made changes to the <tt>relationals</tt> and <tt>db892085391</tt> databases, you can safely choose not to restore the records from the related tables.)
</li>
<li>Set the admin user ID to "3" (the system user) in the tenant records: 
<pre>
UPDATE db892085391.NETWORK_ACCOUNTS SET owner_id = '3' WHERE number = '1234';
UPDATE db892085391.NETWORK_8c5b349851c041a28d19277a12dee8f0 SET owner_id = '3' 
    WHERE number = '1234';
</pre>
</li>
<li>If the tenant/customer is an MSP, restore their service configuration records, as well:
<pre>
# mysql –uroot –p db892085391 < network_isv_settings.sql
</pre>
</li>
</ol>

====Learn More====
For more detailed information on database backup and recovery in MySQL, see:
:* http://dev.mysql.com/doc/refman/{{MySqlVersion}}/en/mysqldump.html
:* [http://www.mydigitallife.info/2007/07/21/how-to-backup-and-restore-export-and-import-mysql-databases-tutorial http://www.mydigitallife.info/2007/07/21/]<br/>[http://www.mydigitallife.info/2007/07/21/how-to-backup-and-restore-export-and-import-mysql-databases-tutorial how-to-backup-and-restore-export-and-import-mysql-databases-tutorial]

[[Category:Installation]]