AgileApps Support Wiki Pre Release

Difference between revisions of "Database Backup and Recovery"

From AgileApps Support Wiki
imported>Aeric
imported>Aeric
 
(10 intermediate revisions by the same user not shown)
Line 1: Line 1:
=== Database Backup and Recovery ===
<includeonly>=== Database Backup and Recovery ===</includeonly>
 
MySQL can be backed up using the <tt>mysqldump</tt> command - http://dev.mysql.com/doc/refman/{{MySqlVersion}}/en/mysqldump.html
MySQL can be backed up using the <tt>mysqldump</tt> command - http://dev.mysql.com/doc/refman/5.1/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]]}}
{{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]]}}
Line 44: Line 43:
</pre>
</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====
====Learn More====
For more detailed information on database backup and recovery in MySQL, see:
For more detailed information on database backup and recovery in MySQL, see:
:* http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
:* 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]
:* [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]
<noinclude>


[[Category:Installation]]
[[Category:Installable Version]]
</noinclude>

Latest revision as of 11:58, 13 July 2017

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

Thumbsup.gif

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 &

Learn More

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

Retrieved from "https://agileappscloud.info/aadev-src/index.php?title=Database_Backup_and_Recovery&oldid=11749"