Migrate a MySQL database to a new version and cloud provider

As a widely used open-source relational database management system (RDBMS), MySQL plays a crucial role in storing and retrieving data for various applications in production. However, migrating a MySQL database to a new server or a different version of MySQL can be a daunting task due to potential data loss, unexpected downtime, technical complexity, or compatibility issues.

In this tutorial, we'll migrate a MySQL database from an older version to a newer version, and migrate it across cloud providers as well. We'll cover the common steps and configurations required to do this.

The (hidden) challenges of database migration

Database migration is a complex and challenging process that requires careful planning and execution to ensure that it is done correctly and without any data loss, downtime, or security risks. Here are the five associated challenges:

1. Data Loss: If the migration process is not executed correctly, it can result in data loss, which can have severe consequences for the organization that relies on the database.

2. Downtime: During the migration process, the database may not be available for some time, which can result in downtime for the application. This can impact the user experience and cause financial losses for businesses that rely on the application.

3. Technical Complexity: Migrating a database requires technical expertise, and it involves several complex tasks such as creating backups, restoring the database, and updating the application's configuration.

4. Compatibility Issues: When migrating to a different system or platform, there may be compatibility issues with the application's current codebase, which may need to be resolved before the migration can be completed successfully.

5. Security Risks: Migrating a database can also introduce security risks if not done correctly, which can compromise the integrity and confidentiality of sensitive data.

The types of MySQL database migration

You can migrate MySQL databases using snapshots or logical replication. The snapshot method takes a backup of the database and restores it to the destination server, logical replication takes this a step further and also copies individual transactions across in real-time.

Snapshot is faster for initial synchronization, less resource-intensive, and easier to set up. However, it requires significantly more downtime, especially for larger servers.

Logical replication, on the other hand, is a powerful technique for replicating changes between databases and ensuring high availability. While it offers granular control over data replication, it may not be the most optimal choice for large-scale or complex database migrations. The migration process typically requires a complete transfer of data, and logical replication may not provide the same level of efficiency as other methods. However, in scenarios where ongoing operations need to be minimally disrupted during the migration, logical replication can offer the advantage of allowing continuous replication of transactions.

Overall, while snapshotting is useful for certain scenarios, such as one-time migrations or disaster recovery setups, logical replication might be a better choice for ongoing replication and database migrations, due to its efficiency, flexibility, and minimal impact on operations. It's worth noting that the Aiven platform offers a built-in database migration tool that uses logical replication to migrate data between MySQL instances.

Prerequisites

To follow along, you'll need:

• A Google Cloud account and gcloud CLI installed
• An Aiven account
• MySQL CLI client or MySQL Shell installed

Step 1: Plan the migration

In this tutorial, we'll migrate a MySQL 5.7 server to a MySQL 8.0 server. In my example, we'll use Google Cloud SQL as the source and Aiven for MySQL as the target. We'll use the built-in migration tool within Aiven console. If you're migrating to a different provider, check out MySQL migration tool - an open-source tool.

Here are the 7 things to remember before you start a MySQL migration:

1. The target MySQL service should not contain any data.
2. Superuser permission must be available on the source database.
3. Both databases should be publicly accessible.
4. Firewalls should be updated or disabled.
5. Remote connections should be enabled on the source database.
6. GTID should be enabled on the source database.
7. Logical replication privileges should be granted on the source database.

Step 2: Prepare the source database and create backup

If you already have a source MySQL database available, you can use that. If not, let's create a new MySQL database and populate it with some sample data. Since this is a demo database, we'll skip the part of database backup.

1. From your Google Cloud console, navigate to Cloud SQL and click CREATE INSTANCE.
2. Choose MySQL, use version 5.7, Sandbox as the environment, and your preferred cloud zone. I named this instance google-montreal-mysql57 but you can name it based on your preference.
3. Once your instance is created, navigate to the Databases section of your instance and click CREATE DATABASE. You can name this database old-mysql57-db. You also need a database user.
4. From the Users tab, click ADD USER ACCOUNT and create a new database user. Be sure to keep a note of the user password since you won't be able to view this again. I named this user test-dba-mysql.

Next, let's connect to this database and add some sample data. I'll use gcloud CLI to connect to Google Cloud SQL instance.

At first, you need to authenticate your gcloud CLI. You'll need to allow Google Cloud SDK access to your Google Cloud account.

gcloud auth login
Copy to clipboard

Ensure that you're using the correct project ID. You can use gcloud config set project PROJECT_ID to switch in case you're on a different project.

Next, you'll connect to the Google Cloud SQL instance using the newly created user. Remember to modify the instance name or database user name if those are different for your case.

gcloud sql connect google-montreal-mysql57 --user=test-dba-mysql
Copy to clipboard

Google Cloud allowlists your IP for incoming connection for 5 minutes. You'll need to enter the previously saved database user password at this point. Once you're connected to the database instance, select the intended database. Be sure to use the database name you chose.

USE OLD-MYSQL57-DB
Copy to clipboard

Now you can add some sample data to this database. You're free to generate this yourself or can copy of the following SQL statements:

-- Create the first table
CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(50) UNIQUE,
  phone VARCHAR(20),
  address VARCHAR(100)
);

-- Add sample data to the customers table
INSERT INTO customers (customer_id, name, email, phone, address)
VALUES
  (1, 'John Smith', 'john.smith@example.com', '555-1234', '123 Main St'),
  (2, 'Jane Doe', 'jane.doe@example.com', '555-5678', '456 Elm St'),
  (3, 'Bob Johnson', 'bob.johnson@example.com', '555-9012', '789 Oak St');

-- Create the second table
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  order_date TIMESTAMP NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Add sample data to the orders table
INSERT INTO orders (order_id, customer_id, order_date, total)
VALUES
  (1, 1, '2022-01-01 12:00:00', 50.00),
  (2, 2, '2022-01-02 14:30:00', 100.00),
  (3, 1, '2022-01-03 10:00:00', 25.00),
  (4, 3, '2022-01-04 16:45:00', 75.00);

-- Create the third table
CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  price DECIMAL(10,2) NOT NULL
);

-- Add sample data to the products table
INSERT INTO products (product_id, name, price)
VALUES
  (1, 'Widget', 10.00),
  (2, 'Gadget', 20.00),
  (3, 'Doohickey', 5.00);
Copy to clipboard

One of the requirements of this migration is that GTID should be enabled on the source database. To check if GTID is enabled, execute:

show global variables like 'gtid_mode';
Copy to clipboard

If gtid_mode is ON, your configuration is Ok. Else, you might need to check if some backup configurations like PITR are disabled or not.

At this point, you can execute \q to disconnect the database connection.

Prepare the target database

The target database in my case is Aiven for MySQL running version 8.0. Assuming that you already have an Aiven account, navigate to Aiven console and click Create service. Select MySQL as the service, choose your preferred cloud and region, select a service plan (Hobbyist or Startup-4 will do), and give your MySQL service a name. I named my Aiven for MySQL service new-mysql80-db. Click Create service and the service creation process will go through rebuilding to running state.

Once new-mysql80-db service is running, you can check from the Overview page the service URI, hostname, port, and other connection details. If you have mysqlsh CLI tool installed, ensure that your connection to Aiven for MySQL works:

export NEW_DB_URI=YOUR_AIVEN_FOR_MYSQL_SERVICE_URI
mysqlsh --sql $NEW_DB_URI
Copy to clipboard

If the connection is successful, you can execute ctrl+D to close the connection.

Make a note of the hostname for new-mysql80-db service. You'll need it to find the IP address of the service next.

Step 3: Authorize network IP

By default, the Google Cloud SQL instance blocks all incoming IP address to the database instance. In order to establish a connection, you'll need to specify CIDR ranges to allow specific IP addresses in those ranges to access your instance. Since you only need to connect from one specific IP address (the source database), you can add its IP address to the list of authorized networks for the target database.

However, you only know the hostname for the Aiven for MySQL service. Use the hostname you noted down from the previous section to find out the IP address for your Aiven for MySQL service.

To find the IP address of your service, ping the hostname from the terminal:

ping YOUR_HOSTNAME_HERE
Copy to clipboard

The response will contain the IP address that corresponds to this hostname. Here's an example:

PING dewan-redis-demo-devrel-dewan.aivencloud.com (34.118.135.75): 56 data bytes
64 bytes from 34.118.135.75: icmp_seq=0 ttl=59 time=24.713 ms
Copy to clipboard

On your Google Cloud SQL instance, navigate to the Connections --> Networking tab, and click Add network. Add the IP address of your Aiven for MySQL service with a /32 CIDR notation. For example, if the IP address is 34.35.36.37, you'll add 34.35.36.37/32 in the Network field. You can, optionally, give this network a name. Once done, hit Save.

Remember, without this step, some source database might not allow incoming network connection from the target database.

Step 4: Start the database migration

From your Aiven for MySQL service's Overview tab, click on Set up migration and follow the steps. The first step is to read the guidelines for a successful database migration. For the validation step, add your source database hostname or IP, the port number, database user, and password. For my case, I used Google Cloud SQL and used the instance IP address instead of a hostname.

If you're receing the following error during the validation step of the migration, you might not have authorized the target database IP.

aiven_mysql_migrate.exceptions.EndpointConnectionException: Connection to source failed: (2003, "Can't connect to MySQL server on 'XX.XXX.XXX.XXX' (timed out)")
Copy to clipboard

Google Cloud SQL also has a handy tool to test service connectivity between the source and target databases under Connections --> CONNECTIVITY TESTS.

Once the validation step is successful, you can proceed to Start migration. While the database migration is in progress, here are some things to keep in mind:

• Do not write to any target database tables
• Do not manually change the source database's replication or GTID configuration
• Avoid any modifications that may disrupt the connection between the source and target databases, such as updating firewalls or trusted sources

Step 5: Test the migrated data

Let's connect to Aiven for MySQL, the target database.

mysqlsh --sql $NEW_DB_URI
Copy to clipboard

Ensure that old-mysql57-db is selected.

use old-mysql57-db;
Copy to clipboard

List tables to ensure all data is replicated.

show tables;
Copy to clipboard

You can take a step further and check each of the table to ensure all the rows got replicated correctly.

Step 6: Update app configuration and redirect traffic

Once the migration is completed, you'll see a message "Congratulations, migration is completed!". At this point, follow these steps:

1. Stop writes to source database
2. Update connection settings
3. Restrict public access
4. Close the connection (click Close connection on the wizard)
5. Restart all applications

Wrap up

In this tutorial, you learned about migrating a MySQL database from Google Cloud SQL to Aiven for MySQL. Although the sample databases might not be as large or complex like the ones in production, the underlying concepts of database migration still apply.

Oftentimes, database migration can seem like a daunting task, but with the right approach and tools, it can be done smoothly and efficiently. By following the best practices outlined in this tutorial, you can ensure that your migration is successful and that your data remains safe and secure throughout the process. It's important to test thoroughly and monitor the migration carefully to minimize the risk of data loss or downtime. Additionally, don't forget to update your application's connection strings and perform any necessary configuration changes to ensure a seamless transition to the new database.

If you have any questions, please don't hesitate to ask in the Aiven community forum.

We also recommend the following documentation:

• Aiven for MySQL
• Advanced parameters for Aiven for MySQL databases
• Migrate an Apache Kafka instance using MirrorMaker 2