AgileApps Support Wiki

Difference between revisions of "Database Backup and Recovery"

From AgileApps Support Wiki
imported>Aeric
imported>Aeric
 
(24 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>
# Related accounts from <tt>db892085391.NETWORK_ACCOUNTS</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  db892085391 NETWORK_ACCOUNTS -t -w"number=1234" \
    > network_acc.sql
# mysqldump -u -p db892085391 NETWORK_8c5b349851c041a28d19277a12dee8f0 \
    -t -w"number=1234" > network_tenants.sql
</pre>
<li>If the tenant/customer is an MSP, dump tenant records as well:<pre>
# mysqldump -u -p  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 related tables:<ul>
<li><tt>relationals.NETWORK_DATABASES</tt></li> <li><tt>relationals.NETWORK_CUSTOMER</tt></li> <li><tt>relationals.NETWORK_USER_DIRECTORY</tt></li> <li><tt>db892085391.NETWORK_ACCOUNTS</tt></li>
<li><tt>db892085391.NETWORK_8c5b349851c041a28d19277a12dee8f0</tt></li>
</ul>
<pre>
# mysql –uroot –p relationals < network_customer.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>
</ol>
====Migrating a Tenant ====
When migrating tenant data from one platform instance to another, the first step is to make a copy of the tenant database using the Backup procedure, and then add it to the new instance using the Restore procedure.
{{Note|<br>It may be the case that there is an admin user in the old instance that does not exist in the new instance. In that case, the record created by that admin user in the Tenants object will not be displayed in the new instance, because it does not correspond to an active admin.
The patch, in that case, is to update the Tenant object record in the admin database (<tt>db892085391</tt>) so that the admin user ID is "3" (the system user):
<pre>
UPDATE db892085391.NETWORK_ACCOUNTS SET owner_id = '3' WHERE number = '1234';
UPDATE db892085391.NETWORK_8c5b349851c041a28d19277a12dee8f0 SET owner_id = '3'
    WHERE number = '1234';
</pre>}}


====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/aawiki-src/index.php?title=Database_Backup_and_Recovery&oldid=11749"