About the MySQL for Sitehost data backup and restoration policy

• Overview
• MySQL restoration policy
• Manually back up your MySQL for Sitehost schemas
  • Create a backup using MySQL Workbench
  • Create a backup from the command line
  • Load a MySQL dump from MySQL Workbench

Overview

MySQL for Sitehost uses the MySQL Enterprise Backup features on a nightly schedule. These backups are retained for 14 days for service-wide disaster recovery purposes.

MySQL restoration policy

If you need to back up or restore your data for any purpose (development, transference from production to test, etc.), you should take advantage of the functionality and tools that MySQL Workbench offers (for example, mysqldump or Data Export and Import Wizard).

The Database Administration team will perform MySQL data restoration in extremely limited cases, such as data loss due to SQL injection or unforeseen corruption. Data restoration services are performed on a case-by-case basis and are subject to review and approval by administrators. In all cases, data restoration is limited to the previous night's updates. Binary logs are not active, and it is not possible to provide point-in-time recovery. Data restoration may take up to 48 business hours to process.

To request data restoration, contact Tier 2. Include the date and time the incident requiring data recovery occurred and the type of incident that spurred your request. You may be asked to provide information about what steps you have taken to mitigate the need for future data restoration. Staff will forward your request to the Database Administration team, who will contact you with options or further questions they may have.

Departments and owners of sites who require 24x7 data backup and restoration services are encouraged to contact the Database Administration team to discuss options, which may include creating an Enterprise MySQL instance for just that site's content and on-call data recovery services. A fee may be associated with these services.

Manually back up your MySQL for Sitehost schemas

Create a backup using MySQL Workbench

1. Connect to your MySQL database.
2. Click Server on the main tool bar.
3. Select Data Export.
4. Select the tables you want to back up.
5. Under Export Options, select where you want your dump saved. By default, it will save to your Documents folder in a subfolder titled dumps.
6. Click Start Export.
   Note:

   You may get a message about a mismatch between your mysqldump.exe version and the MySQL Server version. You can update your local MySQL version or continue.
7. You now have a backup version of your site. Store this content securely in a manner consistent with your MySQL schema content's data classification.

Create a backup from the command line

1. Open a command or terminal window (CMD or PowerShell on Windows, or Terminal in macOS).
2. Run ssh username@ssh.sitehost.iu.edu "mysqldump -u root_user -h host -d database name --no-tablespaces -p" > path to result file.

   For example:

   ssh sitehost_user@ssh.sitehost.iu.edu "mysqldump -u account_root -h mysql.uits.iu.edu -d account_database --no-tablespaces -p" > result.sql

   The above example connects to the SSH server as sitehost_user, prompts for a password, and auto-sends a Duo push. Once the push is accepted, it prompts for the MySQL password for the account_root user on mysql.uits.iu.edu, and then writes the output to the result.sql file in the current directory.

Load a MySQL dump from MySQL Workbench

To load a MySQL dump file after a database failure or corruption:

1. Connect to your MySQL database.
2. Click Server on the main tool bar.
3. Select Data Import.
4. You should see a link to the default dump folder, typically your Documents folder in a subfolder titled dumps.
5. Click the ... and navigate to where your MySQL backup file is located, select the backup you want to load, and click OK.
6. The schema names in your dump should appear on the left-hand side, at the bottom. Select the schemas that need to be restored.
7. Select Start Import on the bottom right.