Migrate from Legacy to New MySQL for Webserve service

On this page:


Overview

Note:
The term 'database' has been changed to 'schema' in new versions of MySQL. Although 'database' and 'schema' can be used interchangeably, the term 'database' will be phased out by developers. To reduce confusion and maintain continuity with documentation hosted at IU and elsewhere, this document and future MySQL documents will use the new term exclusively.

The New MySQL for Webserve service at Indiana University is designed to provide better MySQL support for Webserve users and eliminate the need for individual administration of MySQL database instances. IU group account owners can create and manage MySQL accounts automatically and get nearly instant access to the new environment.

The migration process takes place from the new environment using stored procedures that were developed for that purpose. It is imperative that users be familiar with how the new environment works and what tools are available for data and user management.

For more about the New MySQL for Webserve environment, including recommended connection methods and guidelines using stored procedures, see:

Prior to migration

Note:

Wordpress users:

For help navigating the special challenges you will face in this migration, see Migrate your Legacy MySQL-based Wordpress site to New MySQL.

Before starting the migration process, you should familiarize yourself with your site's code and identify any MySQL connection strings as well as schema (database) names and users that may exist in your chosen schema. Webserve administrators have created a shell script you can run via SSH in Webserve and Webtest to find code references in your site to the Legacy MySQL for Webserve service. To use this script, run the following command at the prompt:

  show_mylegacy_refs

This command will return a list of all files and locations where the Legacy MySQL for Webserve servers are referenced in your site's code. Review all the indicated files for updates you need to make to successfully migrate to the New MySQL environment.

For instructions on SSHing into Webserve using the most common clients, see:

You should also be aware of the following:

  • All your current schemas will be renamed according to the new prefix_schema naming convention, where prefix is the name of your group account. For more, see New MySQL account naming conventions.
  • Empty schemas (those with 0 tables) or schemas with corrupted data will not be migrated.
  • Account owners will be able to label schemas as "not needed". This label will exclude those schemas from the migration process. For more, check the help for the migrationSetSchemaNoNeed procedure.
  • The names of users will also need to conform to the new prefix_suffix naming convention. If the new user name is longer than 32 characters, it will be shortened. The new name is provided in the output of migrationGetSchemaInfo API procedure.
  • Finally, it is very important that you understand the conditions that may prevent schemas and users from migration. Be sure to check the migration status tables, particularly the column with the code descriptions, provided in the help for the migrationListAllSchemas and migrationGetSchemaInfo procedures.

Begin migration

Note:

Once migration completes, your Legacy MySQL for Webserve account will enter a read-only state for 24 hours before being disabled. You will need to immediately update your site to connect to the New MySQL for Webserve environment.

If you need to restore access to the data stored on Legacy MySQL for Webserve, contact Web Services Support. Although you may start your migration at any time, Web Services Support is only available from 8am to 5pm, Monday to Friday and will not be able to assist with any migration challenges you face outside of that time.

To migrate from Legacy MySQL to New MySQL for Webserve, first create your New MySQL for Webserve accounts:

  1. Go to Enterprise Web Technical Services. (Log in using IU CAS and Duo two-step authentication.)
  2. Click Linux Account Management.
  3. For the account you want to migrate, select Manage.

    Example showing the 'Manage' link for an account

  4. On the "Manage Account" page, under the "MySQL" section, click Create account:

    The 'Create account' link under the 'MySQL' section

  5. At the bottom of the next page, click Create Account (you may need to scroll through a lot of text to reach it):

    The 'Create account' button for creating a New MySQL for Webserve account

You will use the following stored procedures for migration:

  • api.migrationListAllSchemas(); - Retrieves a list of schemas you own on Legacy MySQL along with a status and brief description of migration readiness.
  • api.migrationGetSchemaInfo(); - Provides detailed information on each schema, such as configuration information, status in Legacy MySQL production or test, the new schema's name once migrated to New MySQL, and current users created for that schema.
  • api.migrationSetSchemaNoNeed(); - Allows you to mark a schema as no longer needed. This flags the schema and it will be skipped during the migration.
  • api.migrationStartMigration(); - Allows you to schedule migration for a particular schema, provide an email for notifications once the migration starts, completes, and the completion status (successful or not). You also have the option to specify NOW as a start time, which will allow you to start the migration as soon as possible. You may wish to schedule your migration times so that they complete when Web Services Support is available, in case you have any questions.
  • api.migrationCancelScheduledMigration(); - Allows you to cancel a MySQL migration that is scheduled in the future. If the migration is already in process, it cannot be canceled.

As with all the API procedures, you can obtain information about any of these procedures and examples by typing:

CALL API.HELP('name_of_procedure');

For example, to get information about the migrationListAllSchemas procedure you will type:

CALL API.HELP('migrationListAllSchemas');

In order to obtain general information about the environment:

CALL API.HELP('me');

For more about using stored procedures, see Working with MySQL API procedures and Calling stored procedures in the SQL Editor.

The migration process is done one schema at a time and is determined by the account owner when calling the API.migrationStartMigration procedure. The length of time it takes to migrate depends on the complexity and content of the schema. Once the migration has completed, you will need to update your site's code to connect to the New MySQL for Webserve servers using the updated user and schema name.

If you are using a content manager such as WordPress, Joomla!, Drupal, or others, you may need to look at the developer documentation for those services for assistance with how to migrate from one MySQL instance to another.

Re-enable Legacy MySQL for Webserve

If you encounter an issue while migrating and need to temporarily re-enable Legacy MySQL for Webserve, you may do so by contacting the Support Center Tier 2 - Web Services Support team. This team is only available during normal business hours.

Request an extension

If you need to request an extension to keep your Legacy MySQL for Webserve service active beyond May 31, 2018, you must submit your request to the Web Services Support team. Requests for extension will not be accepted before May 4, 2018. Include a reason for the extension along with your plans to complete the migration by the end of the extension. If granted an extension, you will have until July 31, 2018 to complete your migration.

This is document apjp in the Knowledge Base.
Last modified on 2018-05-21 14:55:28.

  • Fill out this form to submit your issue to the UITS Support Center.
  • Please note that you must be affiliated with Indiana University to receive support.
  • All fields are required.

Please provide your IU email address. If you currently have a problem receiving email at your IU account, enter an alternate email address.

  • Fill out this form to submit your comment to the IU Knowledge Base.
  • If you are affiliated with Indiana University and need help with a computing problem, please use the I need help with a computing problem section above, or contact your campus Support Center.

Please provide your IU email address. If you currently have a problem receiving email at your IU account, enter an alternate email address.