Migrating MySQL Databases: A Comprehensive Guide

The need for database migration may arise in several situations, such as:

changing your working platform or provider,
modernizing and restoring IT infrastructure,
introducing new developments into the existing system,
combining several different programs (in case of a merger of companies, branches, etc.).

In this article, we will consider all the options for MySQL database migration, how the process is carried out, and how to prepare for migrating the data.

General terms

Data is a format for storing information electronically (files, catalogs) on local and cloud media.
Information system is a program for storing, processing and retrieving data.
Database, DB is a data set belonging to a particular enterprise, department or project. This includes information entered by users, tables, and other objects. The database also stores reports, multimedia, text files, service reminders, etc.
Database Management System (DBMS) is a software product created specifically for editing and managing databases. Examples of DBMS are MySQL, PostgreSQL, and others.
Database Schema is a database structure in the language supported by a particular DBMS. It includes both typical objects, such as tables and relations within them, as well as views, indexes, and other user information.
Table is a structured way of displaying values stored in a database. It includes columns and rows that have a specific purpose.
Data migration. The term has a dual purpose. 1. A mass transfer from a source to a receiving database. Once completed, the original system is usually discontinued as a working system. 2. A database conversion with a change in the database, either upgrade or downgrade (db_upgrade or db_downgrade).
Source system (historical system) is the database from which business and service information is to be copied.
Receiver system is the database to which the information will be transferred during the migration process.
Source data is the information obtained from the historical system, for example, in an Excel file (XLS).
Data transformation is converting the source structure into the format of the receiving database following its typical information storage template.
Data for loading is the information prepared for loading into the target database.
Data templates for loading describe the information storage structure in which the information is to be loaded into the receiver system.
Database version is the current or previous state of the database structure with a certain number, linked to a specific program release.

Reasons for database migration

The IT technologies are constantly improving. At least once every couple of years, new products radically different from the previous ones enter the market. The latter partly complicates maintenance, increases the cost of IT maintenance, and introduces certain limitations, which leads to the need for modernization with database migration. Developers additionally "incentivize" this approach by phasing out support for old products.

Here is a brief list of when migration is necessary:

Launching a new site.
Increasing the security of the IT infrastructure.
Reducing the cost of the information system maintenance.
Introducing fundamentally new technologies.

Types of data migration

The choice of MySQL database migration option depends on your current business objectives. Examples:

Information migration. Data stored in the source database is converted to a different one supported by the destination database. This includes digitizing paper documents to digitize archives, applying encryption algorithms.
Introduction of new programs. Transition to new releases of programs with preservation of previous settings, to fundamentally new programs with transfer from the old software.
Database migration. Moving a database while maintaining consistency. For example, when moving local IT infrastructure to cloud environment, when import substitution according to the current regulations.
Release migration. Updating the database structure to the current version so that it corresponds to both the program and the tools of third-party services. The procedure is performed in reverse order, with the release number decreasing.

Regardless of the migration type, the procedure is performed manually or automatically. The choice is up to the user, but partly it depends on objective factors. For example, if the product is no longer supported, automation is practically unrealistic or will be expensive. It is easier to manually find the necessary numbers, unload them from the old database, and load them into the new one.

Typical processes in database migration

Let's consider two options in more detail: database migration to a new software and versioned migration, when the application is regularly adjusted, often without stopping its work.

What you will need when migrating the entire database

The process is divided into two stages: preparation and migration itself, which also involves working in several steps.

Organizational part

Determine the strategy. It is important to choose in advance the technology by which the migration of the MySQL database will take place. The stage includes determining the rules for opening access to employees related to the procedure. Each participant must understand what is required of them. It is also desirable to add testing to the strategy, after the migration is completed, in order to identify errors and correct them before they begin to create problems.
Assemble a working group. It includes specialists familiar with the operation of the "old" system (historical) and the new program. It is better to assign monitoring to individual employees. The latter is mandatory even if the migration procedure is fully automated.
Make a plan. It includes the amount of information to be transferred, the date of its transfer, testing, and the final implementation of the new software. This does not exclude the possibility of adjusting the plan based on the migration results, emerging errors, appearance of other inputs.
List the data to be migrated. What do you want to copy? Classifiers, balances, turnovers, transactional and reference information.
Discuss methods and criteria for quality control. It is important to check the integrity and correctness of the migrated data, the absence of unreasonable duplicates, the presence of full consistency, links provided by the structure of the base-receiver.
Determine the method of database rollback. In case of serious errors, you may have to temporarily return to the old database to avoid downtime. Discuss the return mechanism in advance.

Technological part

Prepare data loading templates, file loader. They include a description of fields to be loaded, rules for loading a table based on the structure of the receiver database. They also specify how to transform data if transformation is required.
Identify data sources. At this stage, determine what information will be migrated, where, in what databases, and in what format it is located. The list of sources should be thought through carefully to avoid losing important information.
Unload the source data. The speed of unloading depends on the amount of information, sometimes on its type, on whether conversion is required, whether reading errors occur, leading to repetitive reading. In practice, it is common to do test uploads of a small part of the database for preliminary evaluation.
Data comparison. This stage is mandatory, its results guarantee the identity of the information in the source database and the upload file. It will take time approximately equal to the third step. Depending on the tasks, normalize table fields, remove duplicates, etc. at the same time.
Prepare transformation rules. It includes writing scripts that automatically, according to pre-created templates, transform the structure into a given format.
Load data into the receiver base. This step includes a series of test and final migrations or "main" migration at once, if the user is sure of the quality. For example, when the procedure is performed for the second or more times with the same tools.
Verify data. In the last step, check the source database and the destination base to exclude inconsistency, correct probable defects, reduce the database size, and exclude duplicates.

Data version migration

Database version migration is used when developing software or new releases of existing programs to avoid desynchronization if different programmers create separate modules. It's also used when creating a new release in several stages, with periodic rollback to previous versions. The main idea of versioned migration is to set a rule: any SQL query is executed only once, because some changes often depend on others.

During migration, "atypical" errors occur. For example, we may assume that null rows can be deleted, but they are sometimes a part of the mandatory database structure. That is why it is better to provide the rule of storing "null" string fields right away:

Regardless of the current value, set the field type to Nullable.
Replace all empty strings of the processed database with NULL.
Adjust the code so that the program reacts to reading NULL without errors.

Remember that when updating only an application without a database, an error of inserting a NULL value into the Not Nullable field will appear sooner or later.

In practice, several methods of version migration are used - incremental, idempotent changes, likening the database structure to the source code, etc. There are tools for migration: Migrator.NET, ECM7.Migrator, Active Record Migrations and a dozen more. Regardless of the choice, the following principles are adhered to everywhere:

It is important that any version of the database is upgradable to any of the existing ones.
The same applies to the set of SQL queries, due to which the migration takes place.
It is recommended to be able to create a database from scratch with up-to-date data at any time.
It is desirable to minimize manual editing of files when merging branches.
Rollback to one of the previous releases is as easy as an upgrade.

Preparing a database migration plan

Migration is carried out according to a clear plan so that the process is organized and uninterrupted. Lack of planning often leads to unforeseen downtime, serious errors in business processes, and sometimes even loss of critical data. It looks like an unfinished program with regular "glitches" and failures. Therefore, a plan is mandatory.

Conduct an audit in advance and determine the current quality, format, and privacy policy. The plan also includes monitoring of probable changes that migration sometimes leads to. This approach allows you to identify potential problems, adjust the work based on real inputs.

The planning also includes the assignment of responsible employees - for the transfer, for the individual steps of the process. It is desirable to separate the team of specialists from the operations team and at the same time maintain interaction for feedback and new inputs.

Database migration example

Before starting the migration, make sure that the receiver system supports the version of the database to be migrated. You should also provide access to the source and target objects in advance. Information from the official guide "SQL Server to MySQL Migration Assistant" will help in the process.

Preparing for migration

If current conditions allow for a smooth migration, assess the database and related objects for readiness for migration. This procedure is possible by using the SSMA tool. The sequence of actions is as follows:

Open the SSMA software for MySQL.
Select the "Create Project" menu item in the "File" section.
Enter its name, where it will be located, specify the target object.
Set the "Transfer" parameter to SQL Server.
Use the "Connect to MySQL" window to connect to the MySQL server.
Select the database to be migrated.
Right-click on the "MySQL Metadata Explorer".
Select the "Create Report" tab (upper right corner).

This will generate a report containing statistics of conversions, errors or warnings that occur. The system will save this file in the SSMAProjects folder located in the current user's system directory. When viewed in Excel, the user will see the list of MySQL objects and actions to be converted during migration.

Now, we need to match the "default" data types and adjust them based on the requirements of the statement of work. The procedure looks like this:

Select "Project Parameters" from the Tools menu item.
Go to the "Type Matching" tab.
Perform the mapping by selecting the table in the MySQL Metadata Explorer.

Now, let's move on to the conversion of database objects. They should be taken from MySQL, "converted" into an analog for SQL Server, and loaded into SSMA metadata for MySQL. After that, you can view the contents through the "SQL Server Metadata Explorer". The SSMA product displays error messages (Output field), from which it is easy to judge whether a database conversion is required for the migration to be successful. If the procedure is required, follow the steps:

Open Connect to SQL Server.
Enter the credentials to connect to it.
Specify the target or new database.
Click "Connect."
In the "MySQL Metadata Explorer" area, use the right mouse button to select "Convert Schema".
When finished, compare the resulting data with the original data to make sure there are no problems.

If corrections are required, save the project offline with the "File>Save Project" command.

Migration

If there are no errors, proceed directly to migration. In practice, two options are used: client-side and server-side. In the first case, select "Client-side data migration subsystem" in the Project Options dialog box. In the second case, "Server-side data migration subsystem". If the target database is SQL Express Edition, only the first migration method is available.

If the server is involved, you will need to install the SSMA extension package for MySQL on the SQL Server instance and start the SQL Server agent service on it. Data migration is performed using a sequence of actions:

Publish the schema. Right-click on "SQL Server Metadata Explorer". There select "Synchronize with database". Result - the MySQL database will be published inside the SQL Server instance.
Reconcile the source and target project. In the same place as "Synchronize...", select "Transfer data". Check the boxes next to all items to migrate all tables.
Examine the report on the migrated information. Connect to the SQL Server instance using SQL Server Management Studio utility and check how the database migration went.

That's it, the information migration procedure is complete.

Migration using console commands

The procedure includes two stages - creating a database dump and its deployment to the cloud platform where we plan to host the system. Make sure that the local machine and the remote host support the MySQL version being used. Migration between different database releases is not guaranteed.

Creating SQL dump

Let's perform the task using the mysqldump utility:

mysqldump --user=<user_name> \
   --password=<password> \
   --host=<host> \
   --port=<port> \
   --set-gtid-purged=off \
   --no-tablespaces \
 <database_name> > dump.sql

Here, --set-gtid-purget=off means that replication does not use global GTIDs, and --notablespaces means that the dump of service information will be excluded, as it is not really needed.

Restoring the database from the dump

The mysql utility will help you to restore a SQL dump:

mysql --user=<user_name> \
   --password=<password> \
   --host=<host> \
   --port=6033 <database_name> < dump.sql

When using SSL, the list of commands will look like this:

mysql --user=<user_name> \
   --password=<password> \
   --host=<host> \
   --port=6033 \
   --ssl-ca=~/.mysql/root.crt \
   --ssl-mode=required <database_name> < dump.sql

Conclusion

Migration is a manageable process. With quality planning, the task will be realized without problems. In the opposite situation, errors and data loss are possible. Therefore, the approach to migration is as important as working with financial documents. Damage or loss of information may well lead to losses. But consistent execution (with preparation, templates, etc.) usually yields positive results immediately.

MySQL Database Migration