How to migrate to MySQL v8 on DigitalOcean Managed Databases

Introduction

Migrating a database is a critical part of infrastructure transitions. Whether moving from on-premise systems, virtual machines, or consolidating cloud resources, the goals are to reduce maintenance, improve reliability, and enable growth.

DigitalOcean’s Managed MySQL service with MySQL 8.0 offers an ideal destination for teams seeking managed operations without sacrificing control. MySQL 8 provides improved defaults, modern application support, and enhanced performance features.

Migrating to MySQL 8.0 can bring significant benefits, including improved performance, enhanced security, and better support for modern applications. Some of the key advantages of MySQL 8.0 include its default use of the utf8mb4 character set, which provides better support for Unicode characters, and its improved support for JSON data types. Additionally, MySQL 8.0 introduces a number of performance enhancements, such as improved indexing and query optimization. However, it’s essential to carefully evaluate the potential drawbacks of migration, including the need to address compatibility issues with existing applications and the potential for increased resource utilization.

This tutorial helps technical teams migrate to DigitalOcean Managed MySQL 8.0. It covers migrations from various sources, addressing compatibility, configuration, and proven strategies for a smooth transition. You’ll learn how to effectively plan and execute your database migration.

Prerequisites

Before beginning the migration, ensure you have the following:

• Admin access to both source and target databases.
• A DigitalOcean account with permissions to provision Managed Databases.
• Tools installed: (1) mysqldump, mysql, and/or mysqlsh (2) Secure shell access (if needed) to the source database.
• Firewall and networking configured to allow database access (especially for replication or direct connection)
• A tested data backup strategy.
• Basic MySQL knowledge (schemas, users, privileges, config).

Background

As teams modernize their infrastructure, migrating from self-managed databases to cloud-managed solutions has become increasingly common. The primary motivators include reducing operational overhead, improving scalability, enforcing better security practices, and gaining built-in backup and disaster recovery features.

MySQL 8.0 represents a major upgrade over earlier versions like 5.7, introducing features such as:

• Native JSON enhancements
• Window functions and CTEs
• Invisible indexes
• A transactional data dictionary
• Default utf8mb4 character set

These improvements make MySQL 8.0 well-suited for modern applications that require advanced querying, global character support, and better performance at scale.

DigitalOcean Managed Databases complement this by offering:

• Fully managed MySQL 8.0 clusters with fast setup.
• Automatic daily point-in-time recovery backups and high availability options.
• Enforced SSL and private networking.
• Built-in monitoring, scaling, and maintenance.

MySQL 8 and DigitalOcean together simplify database management, allowing development teams to prioritize product development over infrastructure concerns. This combination is ideal for teams seeking to delegate infrastructure management and concentrate on building applications and features.

Step 1 - Assess Compatibility

MySQL Version Compatibility

Before beginning your migration, it is important to verify the version of your source MySQL database. DigitalOcean Managed Databases currently support MySQL 8.0, which means that the source version must be equal to or older than MySQL 8.0, but not newer.

The supported version scenarios are:

• MySQL 5.6 or 5.7 to 8.0

This is a common migration path, especially for legacy applications. However, it requires thorough compatibility checks since MySQL 8 introduces breaking changes, deprecated features, and stricter defaults. Schema structure, authentication, character sets, and stored routines may all need adjustments before or after the migration.

• MySQL 8.0 to MySQL 8.0

This is the most straightforward and safest scenario. Migration between matching major versions avoids most compatibility issues, but you should still be aware of environmental differences (e.g., SSL enforcement, authentication plugins, and primary key requirements in DigitalOcean’s Managed Database setup).

• MySQL 8.1 or higher to MySQL 8.0

This path is not supported. Downgrades between major MySQL versions are not reliable and typically not possible using standard tools like mysqldump, mysqlpump, or replication. If your source database is on a newer version than 8.0, you’ll need to explore alternatives, such as exporting to a neutral format (like CSV or flat SQL dumps) and manually modifying incompatible structures.

To check your current MySQL version, run the following SQL command:

SELECT VERSION();

This will return the version string (e.g., 8.0.36 or 5.7.42). Ensure that the major version number is either 5.6, 5.7, or 8.0. If it’s higher, consider postponing the migration or deploying a target environment that matches the source version more closely.

If you need to upgrade your MySQL version, you can follow these steps:

1. Backup your database: Before upgrading, ensure you have a complete backup of your database to prevent data loss in case something goes wrong during the upgrade process.
2. Stop the MySQL service: Stop the MySQL service to prevent any new connections or transactions during the upgrade.
3. Upgrade MySQL: Use your distribution’s package manager to upgrade MySQL. For example, on Ubuntu or Debian, you can use apt update && apt full-upgrade. On Red Hat or CentOS, use yum update mysql-server.
4. Start the MySQL service: Once the upgrade is complete, start the MySQL service again.
5. Run the MySQL upgrade script: After starting the service, run the MySQL upgrade script to ensure all system tables are updated to the new version format. This script is usually located at /usr/bin/mysql_upgrade.
6. Verify the upgrade: After the upgrade script completes, verify that the MySQL version has been successfully updated by running SELECT VERSION(); again.

Remember to test your application thoroughly after the upgrade to ensure compatibility with the new MySQL version.

Removed and Deprecated Features

MySQL 8.0 removes several legacy features and behaviors that were present in older versions, particularly in 5.6 and 5.7. If your source database uses any of these features, the migration may fail or produce unexpected behavior.

Some common issues include:

• The PASSWORD() function has been removed and must be replaced with ALTER USER ... IDENTIFIED BY.
• The query_cache_size and related configuration options are no longer supported.
• The utf8 character set is now a deprecated alias for utf8mb3 and utf8mb4 is the new default for full UTF-8 support.
• Display width for numeric types (e.g., INT(11)) is ignored.
• The default authentication plugin is now caching_sha2_password instead of mysql_native_password.
• System variables like log_warnings, innodb_large_prefix, and innodb_file_format have been removed.
• The NO_AUTO_CREATE_USER SQL mode has been removed.
• Stricter GROUP BY behavior requires all selected columns to be aggregated or functionally dependent.
• Engine-specific features and syntax for MyISAM or legacy InnoDB may no longer be supported.
• ZEROFILL and display width for temporal types such as TIMESTAMP are ignored.
• Index prefix length rules are stricter, especially for TEXT and BLOB columns.
• Changes to information_schema tables may break existing monitoring or automation scripts.
• New reserved keywords like CUME_DIST, RANK, and PERSIST can conflict with existing schema names.
• Spatial indexes now require a valid SRID to be specified.

To identify potential upgrade problems, use:

mysqlcheck --check-upgrade --all-databases --auto-repair

This will highlight tables and features that may not work in MySQL 8.0, allowing you to resolve them before migration.

Character Set & Collation

MySQL 8.0 defaults to the utf8mb4 character set and utf8mb4_0900_ai_ci collation, both of which are more standards-compliant and support a broader range of Unicode characters.

If your current database uses older character sets (such as latin1, utf8, or utf8mb3), migrating may result in:

• Incompatibilities in sorting and filtering due to changed collation rules
• Errors with index sizes, especially if maximum row size limits are exceeded
• Unexpected changes in character interpretation (e.g., accented characters)

Additionally, changing the character set (including converting from utf8 or utf8mb3 to utf8mb4) will likely affect the storage footprint of your tables and their associated indexes. The “mb4” in utf8mb4 stands for “multi-byte 4”, meaning each character can require up to 4 bytes of storage, compared to 3 bytes in utf8mb3. This increase in storage can have implications on both disk space and performance, especially for large datasets.

To maintain application behavior after migration, consider explicitly setting character sets and collations on your target database. You can also convert existing tables to use utf8mb4_general_ci if backward compatibility is important.

Reserved Keywords & Identifier Conflicts

MySQL 8.0 introduces new SQL keywords such as RANK, WINDOW, and CUME_DIST. If any of your column names, table names, or other identifiers use these words without backticks, you could encounter syntax errors after migration.

To detect potential naming conflicts, run:

SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE column_name IN ('rank', 'window', 'groups', 'row_number', 'system');

If any results are returned, consider renaming the columns or wrapping them in backticks (`) in your queries to avoid breaking functionality.

Authentication Plugin Compatibility

In MySQL 8.0, the default authentication plugin is caching_sha2_password, which offers better security than the older mysql_native_password. However, not all client libraries, especially older MySQL clients or libraries in PHP, Java, and Python, support this newer plugin.

If your application fails to connect after migration, it may be due to this mismatch. You can either update your application to use a compatible client or change the plugin manually for affected users:

ALTER USER 'appuser'@'%' IDENTIFIED WITH mysql_native_password BY 'your_password';

It is recommended to validate all application connections in a staging environment before performing a production cutover.

Primary Key Requirement

DigitalOcean Managed MySQL Database enforces a requirement that every table must have a primary key. This is necessary to support replication, automatic failover, and data consistency guarantees in a clustered environment.

If your database contains tables without primary keys, the migration will fail unless you correct this beforehand. You can identify such tables using:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema')
  AND NOT EXISTS (
    SELECT 1 FROM information_schema.table_constraints
    WHERE constraint_type = 'PRIMARY KEY'
      AND table_name = tables.table_name
      AND table_schema = tables.table_schema
);

Be sure to add meaningful primary keys rather than relying on auto-increment columns alone, especially in multi-column indexes or junction tables.

SQL Modes and Strict Defaults

SQL modes in MySQL define how the server handles invalid data, deprecated behaviors, and standards compliance. MySQL 8.0 enables a much stricter set of SQL modes by default, including:

• STRICT_TRANS_TABLES
• ONLY_FULL_GROUP_BY
• NO_ZERO_DATE
• ERROR_FOR_DIVISION_BY_ZERO

These modes enforce data correctness but can break applications that previously relied on silent truncation or grouping by non-aggregated fields.

To see the SQL modes currently in use on your source database, run:

SELECT @@sql_mode;

You may want to configure the SQL mode on your new DigitalOcean cluster to match your application’s expectations, or update your queries and data validation logic to comply with stricter behavior.

Step 2 - Prepare the Source Database

Before starting your migration, it’s essential to ensure that your source MySQL database is properly configured, accessible, and ready for data transfer. Inadequate preparation can lead to failed migrations, data inconsistencies, or unexpected downtime during the cutover.

Enable Remote Connections on Source Database

Since the migration requires a remote connection between the source and the target managed database, you must configure the source MySQL server to accept external connections. By default, MySQL only allows local connections.

• Modify the MySQL Configuration File

  Open the MySQL configuration file (typically located at /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/my.cnf):

  sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
  

  Copy

• Update the bind-address Setting

  Find the line that says:

  bind-address = 127.0.0.1
  

  Copy

  And change it to:

  bind-address = 0.0.0.0
  

  Copy

  This allows MySQL to listen for incoming connections on all network interfaces.

• Restart MySQL to Apply Changes

  sudo systemctl restart mysql
  

  Copy

• Ensure Firewall and Network Access

  Confirm that your firewall or cloud security group rules allow inbound MySQL traffic from your IP or DigitalOcean’s outbound IPs.

Create a Dedicated Migration User

Create a MySQL user specifically for the migration process. This user needs sufficient privileges to read data and metadata across the relevant databases.

Run the following command:

CREATE USER 'migration_user'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT, SHOW VIEW, LOCK TABLES, REPLICATION SLAVE ON *.* TO 'migration_user'@'%';
FLUSH PRIVILEGES;

These permissions allow the user to read all necessary data and support logical replication if needed.

Grant Logical Replication Privileges

For migrations that involve logical replication or are initiated via DigitalOcean’s control panel, the user must have permissions on all relevant databases.

Grant privileges on specific databases:

GRANT ALL ON <database-name>.* TO 'migration_user'@'%';
FLUSH PRIVILEGES;

Replace <database-name> with the name of each database you intend to migrate.

If you’re using the DigitalOcean control panel:

• The user must have replication privileges on all databases.
• You can exclude specific databases by setting the ignore_dbs parameter.

<$.[note] Important: Revoke these privileges or remove the user after migration is complete to minimize access risks. <$>

Enable GTID for Live Replication-Based Migrations

If you plan to use external replication for a live, low-downtime migration, enable GTID (Global Transaction Identifiers) on the source MySQL server. GTID simplifies binary log tracking and ensures consistency.

• Edit your my.cnf file located at /etc/mysql/my.cnf, /etc/my.cnf, or /etc/mysql/mysql.conf.d/mysqld.cnf. Ensure it contains the following under the [mysqld] section:

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
log_bin = mysql-bin
server_id = 1

Make sure server_id is unique within your network to avoid replication conflicts.

• Restart MySQL:

sudo systemctl restart mysql

Step 3 - Choose a Migration Strategy

When selecting a migration strategy, consider factors such as database size, downtime tolerance, and tooling preferences. DigitalOcean Managed Databases accommodate various approaches, each tailored to specific scenarios.

For instance, small to medium-sized databases with some downtime tolerance might benefit from a dump and restore strategy, while larger databases or those requiring minimal downtime might opt for external replication. Additionally, tooling preferences, such as familiarity with mysqldump or mysqlpump, can influence the choice of migration method.

Dump and Restore (Offline Migration)

This is the most common and straightforward approach, best suited for small to medium-sized databases and scenarios where some downtime is acceptable. The process involves exporting a snapshot of the source database using mysqldump or mysqlpump, then importing it into the target DigitalOcean Managed Database.

While simple, this method requires downtime during both the export and import processes. It’s important to lock the source database (or stop write traffic) before taking the dump to avoid inconsistencies.

External Replication (Live Migration)

For production systems requiring minimal or zero downtime, DigitalOcean allows you to configure the managed database as a replica of your existing source. This method uses binary logs and GTID-based replication to keep the target database synchronized. You can learn more about this in How to migrate a MySQL database to DigitalOcean Managed Databases.

Once fully replicated, you can pause writes to the source, promote the target as the primary database, and point your application to the new host. This technique provides the smoothest cutover but requires a source setup with proper GTID, open ports, and binary logging.

Step 4 - Configure Your DigitalOcean Managed MySQL Cluster

Once your migration strategy is selected and your source is ready, it’s time to configure the target environment on DigitalOcean Managed Database.

Using the DigitalOcean Control Panel or API, create a Managed MySQL 8.0 Database cluster. During creation, you’ll choose cluster size, region, and whether to enable high availability or standby nodes.

After provisioning:

• Whitelist your source database IP in the trusted sources list to allow direct migration traffic.
• Create the destination databases and any necessary users. DigitalOcean requires you to recreate these manually or import them during migration.
• Retrieve your connection details (host, port, user, and password).
• Optionally, configure VPC networking if you want secure communication between your app and database within DigitalOcean’s private network.

Before migration, test connectivity using:

mysql -h <do-hostname> -P 25061 -u doadmin -p --ssl-mode=REQUIRED

This ensures the target is accessible and ready to receive data.

Step 5 - Migrate the Data

Now it’s time to move the actual data.

For dump-based migrations, export from the source:

mysqldump -u root -p --all-databases --routines --triggers --single-transaction > backup.sql

Then import to the target:

mysql -h <do-host> -P 25061 -u doadmin -p --ssl-mode=REQUIRED < backup.sql

For replication-based migration, use DigitalOcean’s UI or API to configure external source replication. You’ll input the source host, port, username, and password. Once replication is active and caught up, schedule a cutover by stopping writes to the source, finalizing replication, and switching your app to point to the new database.

<$.[note] Important: Regardless of the method, it’s recommended to test with a staging environment first. <$>

Step 6 - Post-Migration Checklist

After migration, it is essential to validate that everything works as expected before switching production traffic.

Begin by verifying data integrity. Compare row counts, sample records, and key queries between the source and target to ensure consistency. Then, test all application features, especially those involving complex queries, stored procedures, or user-defined functions.

Review and reapply any necessary user permissions, triggers, events, and foreign key constraints that may not have transferred. Also, make sure to revisit SQL modes, character sets, and other configurations to ensure alignment with your application requirements

Once satisfied, update your application’s connection string to point to the new cluster, and decommission the old environment after an observation period.

FAQs

1. What is the difference between MySQL 8.0 and MySQL 8.1?

MySQL 8.0 and MySQL 8.1 are both major releases of the MySQL database management system. MySQL 8.1 is a newer version with additional features, but DigitalOcean currently supports MySQL 8.0 for managed databases.

2. How long does the migration process typically take?

The migration duration depends on your database size and chosen method. Small databases (<1GB) typically take 15-30 minutes using mysqldump. Larger databases may take several hours, especially when using replication. Plan for additional time for testing and validation.

3. Can I migrate without downtime?

Yes, using replication-based migration allows for minimal downtime. The initial sync happens while your application runs, and you only need a brief maintenance window for the final cutover. Dump-based migrations require longer downtime during the export/import process.

4. What are common migration challenges?

Here are some common challenges and their solutions:

Version Compatibility Issues

When upgrading from MySQL 5.x to MySQL 8.0, it’s essential to review the MySQL 8.0 upgrade guide to understand the changes and plan your migration accordingly. Additionally, DigitalOcean provides a guide on upgrading MySQL that covers the process in detail.

To ensure a smooth upgrade, make sure to:

• Check the DigitalOcean MySQL documentation for any specific requirements or recommendations for your database instance.
• Review the MySQL 8.0 release notes to understand the new features and changes that may affect your application.

Character Set and Collation Mismatches

If you encounter character set and collation mismatches, you may need to convert your data to the appropriate character set and collation. The MySQL documentation provides guidance on this process.

DigitalOcean recommends using the utf8mb4 character set, which is the default character set for MySQL 8.0. You can configure your MySQL database to use this character set.

To convert your data, you can use the mysqldump command with the --default-character-set option. For example:

mysqldump -u root -p --default-character-set=utf8mb4 mydatabase > backup.sql

Authentication Plugin Differences

MySQL 8.0 introduces a new default authentication plugin, caching_sha2_password. If your application does not support this plugin, you can change the default plugin or update your application to support it.

DigitalOcean provides a guide on configuring MySQL authentication that covers the process in detail.

To change the default plugin, you can use the mysql command with the --default-authentication-plugin option. For example:

mysql -u root -p --default-authentication-plugin=mysql_native_password

Network Connectivity and Firewall Configuration

Ensure that your network allows connectivity to the new MySQL 8.0 instance. If you encounter firewall issues, review your firewall rules and update them as needed.

DigitalOcean provides a guide on configuring firewall rules that covers the process in detail.

To allow incoming traffic to your MySQL instance, you can use the ufw command to add a rule. For example:

ufw allow mysql

Large Database Sizes

For large databases, consider using mysqldump with the --single-transaction option to minimize downtime during the migration.

DigitalOcean recommends using the mysqldump command with the --single-transaction option to ensure consistency and minimize downtime. For example:

mysqldump -u root -p --single-transaction mydatabase > backup.sql

Application Code Compatibility

Review your application code for compatibility with MySQL 8.0 features. The MySQL 8.0 release notes provide details on new features and changes that may affect your application.

To ensure compatibility, review your application code and update it as needed to support MySQL 8.0 features.

Remember to test your migration thoroughly in a non-production environment before applying it to your production environment.

5. How do I handle stored procedures and triggers during migration?

Stored procedures and triggers can be included in mysqldump using the --routines and --triggers flags. However, you should review them after migration as syntax compatibility issues may arise, especially when upgrading from older MySQL versions.

What if my migration fails?

If migration fails:

1. Check the error logs for specific issues
2. Verify connectivity and permissions
3. Test with a smaller dataset first
4. Consider breaking the migration into smaller chunks
5. Ensure your source backup is valid and complete

Your source database remains unchanged during migration attempts, so you can safely retry.

6. How do I ensure data consistency after migration?

To verify data consistency:

1. Compare table row counts between source and target
2. Run checksums on important tables
3. Test critical queries and stored procedures
4. Verify foreign key relationships
5. Check application functionality thoroughly
6. Monitor for errors during the initial period after migration

7. What security considerations should I keep in mind?

Key security considerations include:

• Using SSL/TLS encryption for data transfer
• Implementing proper firewall rules
• Managing database user permissions
• Securing connection credentials
• Following the principle of least privilege
• Regular security audits post-migration

Conclusion

Migrating to DigitalOcean’s Managed MySQL 8.0 platform offers an opportunity to modernize your database infrastructure while reducing the cost and risk of manual operations. By following a structured preparation and execution process, you can ensure a smooth and reliable transition from any MySQL-compatible source, whether it’s on-premises, cloud-hosted, or a legacy system.

You have now learned how to assess compatibility, prepare your environment, select a migration strategy, and perform a successful cutover to DigitalOcean. With your data on a managed platform, you’ll benefit from automated backups, built-in security, high availability, and the performance of MySQL 8, all with minimal administrative overhead.

Migration is more than just moving data: it’s a chance to level up your system’s reliability, scalability, and maintainability for years to come.

Related Tutorials

• How to Create a Managed Database
• How to Connect to Managed Databases
• How to Back Up and Restore MySQL Databases
• Understanding Database Migration Strategies