Skip to main content

Migrate data from MySQL to Managed MySQL using Trino

You can migrate tables from one or more MySQL databases to a MySQL semi-sync or MySQL sync Managed Database using Cloud for Trino. This method is suitable if you need to migrate individual tables, or filter or transform data during migration.

The server with the source database and the MySQL cluster with the target database connect to Cloud for Trino as two catalogs using the MySQL connector. Each database is displayed in the catalog as a schema. Trino reads data from the source database and writes it to the target database in a single SQL query, without intermediate files or dumps.

When migrating data using Trino, limitations apply.

Limitations

Data migration guarantee

Cloud for Trino is in public preview:

  • the product is provided without an SLA;
  • service availability is not guaranteed;
  • product malfunctions are possible.

Back up the source database before migrating critical data

Data types

Trino supports basic MySQL data types: numbers, DECIMAL, string and binary types, ENUM, JSON, DATE, TIME, DATETIME, and TIMESTAMP. Columns with other data types (for example, SET, YEAR, and spatial types) are not supported by Trino by default, and a SELECT * query will not return them. If a table contains columns with unsupported data types, specify only supported columns in INSERT and SELECT queries — the rest will receive default values. For more information, see the Type mapping section of the Trino documentation.

If you need to migrate columns of all types or an entire database along with views, procedures, and triggers, use an SQL dump. For more information, see the Migrate MySQL databases to MySQL semi-sync Managed Databases and Migrate MySQL databases to MySQL sync Managed Databases guides.

Time zones

Trino passes its session time zone to MySQL. If time zone tables are not loaded on the source server, queries to TIMESTAMP columns may fail with the error Unknown or incorrect time zone. Load time zone tables onto the source server using the utility mysql_tzinfo_to_sql

Database objects

Trino migrates table data only. Manually create views, stored procedures, functions, triggers, events, and users in the target cluster

MySQL sync with replicas

All tables must have a primary key and the InnoDB storage engine. We recommend changing the structure of large tables after migration using the pt-online-schema-change utility; for details, see the Modifying table structure guide

Data consistency

Each table is read at a specific point in time. If you do not stop writing to the source database during migration, the DBMS will continue write operations to the tables, which will cause some data to be inconsistent. Stop writing to the source database during data migration or re-migrate changed data before switching services

Number of connections

In MySQL Managed Databases, the number of connections depends on the node RAM size — 50 connections per 1 GB. Take this limit into account when migrating multiple tables in parallel

Data migration over the internet

When migrating data over the internet, migration speed depends on the bandwidth between pools. Traffic during data transfer is encrypted, but the MySQL cluster's public floating IP address is accessible from the internet — if necessary, restrict access with an allowlist of IP addresses and disable the public IP address after migration if it is no longer needed.

If you want to migrate data from Trino to a MySQL Managed Database via a private subnet, the target MySQL cluster and the Trino cluster must be in the same pool and same private subnet

Migrate data

  1. Prepare the server with the source MySQL database.
  2. Prepare the target MySQL cluster.
  3. Create a Trino cluster with catalogs.
  4. Connect to the Trino cluster.
  5. Check the catalogs.
  6. Migrate data using Trino.
  7. Check the result.
  8. Switch services.

1. Prepare the server with the source MySQL database

To protect data from modification in the source database, Trino will connect to it as a dedicated read-only user. The source database must use MySQL 5.7 or higher.

  1. Optional: to protect data transferred over the internet, enable TLS on the source server.

  2. Connect to the source MySQL database as a user with privileges to create users.

  3. Create a new user and grant them read-only permissions using the SELECT privilege:

    CREATE USER '<user_name>'@'%' IDENTIFIED BY '<password>';
    GRANT SELECT ON <source_database>.* TO '<user_name>'@'%';

    Specify:

    • <user_name> — username;
    • <password> — user password;
    • <source_database> — name of the database from which data needs to be migrated.

    If you need to migrate data from multiple databases, grant the SELECT privilege on each one.

  4. Make sure MySQL accepts connections on the external interface: in the server configuration, the value of the bind_address parameter must contain the public IP address of the server or 0.0.0.0. Parameter changes take effect only after restarting MySQL.

  5. Make sure the server is accessible from the internet via a public IP address or DNS address.

  6. If traffic filtering is used on the server, allow incoming connections to the MySQL port (usually 3306) in the firewall or cloud provider security groups.

2. Prepare the target MySQL cluster

For your information

Cloud for Trino is available only in the ru-7 pool. If you want to migrate data from Trino to a MySQL Managed Database via a private subnet, create a MySQL cluster in the ru-7 pool and use the same subnet for both MySQL and Trino clusters.

If the MySQL and Trino clusters are in different pools, Trino will only be able to connect to MySQL over the internet via a public floating IP address.

  1. Create a MySQL semi-sync cluster or MySQL sync cluster. When creating a cluster:

    • if you want to migrate data via a private subnet, select the location — pool ru-7;

    • select a MySQL version not lower than the version of the source database;

    • select a node configuration with disk size sufficient for the amount of data to be migrated.

  2. Create the database into which you want to migrate data.

  3. If you need to migrate data from multiple source databases, repeat step 2 for each one.

  4. Create a user.

  5. Grant user access to the created databases.

  6. If you want to migrate data over the internet, attach a public floating IP address to the cluster node. For public floating IP addresses, the master discovery mechanism is used: the address moves to the new node along with the master role.

3. Create a Trino cluster with catalogs

Use the Create Trino cluster instructions. When creating a cluster:

  • in the Network block:

    • if the target MySQL cluster is created in the ru-7 pool, select the target cluster subnet;

    • to connect to Trino from the internet, select the External GUI access checkbox;

  • in the Catalogs block, add two catalogs with the MySQL connector type for the source and target databases. When adding catalogs, use the connection parameters:

ParameterCatalog of the server with the source databaseCatalog of the target cluster
HostPublic IP address or DNS address of the server

  • if the MySQL cluster is in the ru-7 pool — DNS address of the master node of the cluster, you can copy it in the Control panel: in the top menu, click Products → Managed Databases → Active tab → cluster page → Connection tab → Connection addresses block → node row → in the DNS address column, click . The address is bound to the master node role and will not change if a replica becomes the master;

  • if the MySQL cluster is not in the ru-7 pool — public IP address of the cluster, you can copy it in the Control panel: in the top menu, click Products → Managed Databases → Active tab → cluster page → Connection tab → Connection addresses block → node row → in the IP address column, click next to the public IP address

PortPort of the source database, usually 3306
  • 3306 — for MySQL semi-sync;
  • 6033 — for MySQL sync (connection via ProxySQL)
UsernameUsername you created when preparing the server with the source MySQL database in step 2Username you created when preparing the target MySQL cluster in step 4
PasswordPassword of the user you created when preparing the server with the source MySQL database in step 2

Password of the user you created when preparing the target MySQL cluster in step 4. Once the cluster is created, the password cannot be viewed in the Control panel, but it can be changed

4. Connect to the Trino cluster

You can connect to the cluster via the Trino GUI, the Trino CLI console client, and DBeaver.

  1. In the Control panel, in the top menu, click Products and select Trino.

  2. In the cluster card, in the GUI column, click .

  3. Enter the login — admin.

  4. Enter the administrator password — set when creating the cluster. Once the cluster is created, the password cannot be viewed in the Control panel, but it can be changed.

  5. Click Log In.

5. Check the catalogs

List the catalogs, schemas, and tables of the source database and the schemas of the target database:

SHOW CATALOGS;
SHOW SCHEMAS FROM <source_catalog>;
SHOW TABLES FROM <source_catalog>.<source_database>;
SHOW SCHEMAS FROM <target_catalog>;

Specify:

  • <source_catalog> — name of the catalog of the server with the source database;
  • <source_database> — source database name;
  • <target_catalog> — name of the catalog of the target cluster.

Both created catalogs should appear in the catalog list, tables of the source database should be in the <source_catalog>.<source_database> schema, and the schema of the target database should be in the <target_catalog> catalog.

6. Migrate data using Trino

The CREATE TABLE ... AS SELECT query creates a table without a primary key, indexes, and default values, and data cannot be written to such a table in a MySQL sync cluster with replicas. Therefore, you first need to create tables in the target database with the same structure as in the source database, and then migrate data into them. We recommend executing standard MySQL queries against the target database via the system.execute procedure — in this case, separate access to the cluster is not required.

  1. Get the source table structure:

    SHOW CREATE TABLE <source_database>.<source_table>;

    Specify:

    • <source_database> — source database name;
    • <source_table> — source table name.

    Copy the query result.

  2. Create the table in the target database:

    CALL <target_catalog>.system.execute(query => '
    <create_table_query>
    ');

    Specify:

    • <target_catalog> — name of the catalog of the target cluster;
    • <create_table_query> — structure of the source table that you retrieved and copied in step 1.

    If there are single quotes in the table structure (for example, in DEFAULT values), change them to double single quotes: ''. If tables have foreign keys, create and populate tables in order of dependencies: first the referenced tables, then the referencing ones.

  3. If you are using a MySQL sync cluster with replicas, disable writing via a temporary table:

    SET SESSION <target_catalog>.non_transactional_insert = true;

    Specify <target_catalog> — name of the catalog of the target cluster.

    By default, Trino first writes data to a temporary table without a primary key and only then moves it to the target table. In a MySQL sync cluster with replicas, data cannot be written to a table without a primary key.

  4. Optional: if you are using a MySQL semi-sync cluster, disable writing via a temporary table to speed up data migration:

    SET SESSION <target_catalog>.non_transactional_insert = true;

    Specify <target_catalog> — name of the catalog of the target cluster.

  5. Migrate data:

    INSERT INTO <target_catalog>.<target_database>.<target_table>
    SELECT * FROM <source_catalog>.<source_database>.<source_table>;

    Specify:

    • <target_catalog> — name of the catalog of the target cluster;
    • <target_database> — target database name;
    • <target_table> — target table name;
    • <source_catalog> — name of the catalog of the server with the source database;
    • <source_database> — source database name;
    • <source_table> — source table name.

    The output will show the number of written rows. The AUTO_INCREMENT counter in the target table will continue after the maximum migrated value.

  6. If an error occurred while writing data to the target table, it will contain only part of the rows. To migrate all data again:

    6.1. Clear the target table:

    TRUNCATE TABLE <target_catalog>.<target_database>.<target_table>;

    Specify:

    • <target_catalog> — name of the catalog of the target cluster;
    • <target_database> — target database name;
    • <target_table> — target table name.

    6.2. Repeat step 5.

  7. Repeat steps 1–6 for each table in each database.

7. Check the result

  1. Compare the number of rows in the source and target tables:

    SELECT
      (SELECT count(*) FROM <source_catalog>.<source_database>.<source_table>) AS source_rows,
      (SELECT count(*) FROM <target_catalog>.<target_database>.<target_table>) AS target_rows;

    Specify:

    • <source_catalog> — name of the catalog of the server with the source database;
    • <source_database> — source database name;
    • <source_table> — source table name;
    • <target_catalog> — name of the catalog of the target cluster;
    • <target_database> — target database name;
    • <target_table> — target table name.

  2. Compare control values — for example, the sum and maximum value of a numeric column:

    SELECT 'source' AS side, sum(<column>), max(<column>) FROM <source_catalog>.<source_database>.<source_table>
    UNION ALL
    SELECT 'target', sum(<column>), max(<column>) FROM <target_catalog>.<target_database>.<target_table>;

    Specify:

    • <column> — numeric column used to compare control values;
    • <source_catalog> — name of the catalog of the server with the source database;
    • <source_database> — source database name;
    • <source_table> — source table name;
    • <target_catalog> — name of the catalog of the target cluster;
    • <target_database> — target database name;
    • <target_table> — target table name.

  3. List the columns of the target table:

    SHOW COLUMNS FROM <target_catalog>.<target_database>.<target_table>;

    Specify:

    • <target_catalog> — name of the catalog of the target cluster;
    • <target_database> — target database name;
    • <target_table> — target table name.

  4. Compare the target table columns obtained in step 3 with the structure of the source table obtained during data migration in step 1. Columns with data types that Trino does not support are not visible in catalogs — compare them directly with the source table structure.

8. Switch services

  1. Switch services to the target cluster — for details, see the instructions on Connecting to MySQL semi-sync cluster or Connecting to MySQL sync cluster.

  2. If you attached a public floating IP address to the target MySQL cluster and it is no longer needed, detach it.

  3. If the Trino cluster is no longer needed, delete it.

  4. If the server with the source MySQL database is no longer needed, delete it.