Migrating data from a third-party MySQL cluster
Note
Data migration to a third-party MySQL cluster is described in Migrating databases from Managed Service for MySQL to a third-party MySQL cluster.
To move data to a Managed Service for MySQL cluster, create a logical dump of the desired database and restore it to the target cluster. You can do this using two methods:
- Using the
mydumper
and themyloader
utilities . A database dump is created as a collection of files in a separate folder. - Using the
mysqldump
and themysql
utilities. A database dump is created as a single file.
Migration stages:
-
Create a dump of the database you want to migrate.
-
(Optional) Upload a dump to an intermediate virtual machine in Nebius AI.
Transfer your data to an intermediate VM in Compute Cloud if:
- Your Managed Service for MySQL cluster is not accessible from the internet.
- Your hardware or connection to the cluster in Nebius AI is not very reliable.
The larger the amount of data to be migrated and the required migration speed, the higher the virtual machine requirements: number of processor cores, RAM, and disk space.
If you no longer need the resources you created, delete them.
Getting started
Create the required resources:
-
Create a Managed Service for MySQL target cluster with any suitable configuration. In this case:
-
The MySQL version must be the same or higher than the version in the source cluster.
Transferring data with an increase in the MySQL major version is possible, but not guaranteed. For more information, see the MySQL documentation
.You cannot
perform migration while downgrading MySQL version. -
SQL mode must be the same as in the source cluster.
-
-
(Optional step) Create a VM based on Ubuntu 20.04 LTS with the following parameters:
-
Disks and file storages → Size: Sufficient to store both archived and unarchived dumps.
The recommended size is two or three times the total dump and dump archive size.
-
Network settings:
- Public IP: Select the checkbox.
-
Creating a dump
-
Switch the database to
read-only
mode to avoid losing data that can appear while creating the dump. -
Install the
mysqldump
utility in the source cluster as follows (Ubuntu):sudo apt update && sudo apt install mysql-client --yes
-
Create a database dump:
mysqldump \ --host=<source cluster master host FQDN or IP> \ --user=<username> \ --password \ --port=<port> \ --set-gtid-purged=OFF \ --quick \ --single-transaction \ <database name> > ~/db_dump.sql
If required, provide additional parameters in the create dump command:
--events
: If there are recurring events in your database.--routines
: If your database has stored procedures and functions.
For InnoDB tables, use the
--single-transaction
option to guarantee data integrity. -
In the dump file, change the table engine names to
InnoDB
:sed -i -e 's/MyISAM/InnoDB/g' -e 's/MEMORY/InnoDB/g' db_dump.sql
-
Archive the dump:
tar -cvzf db_dump.tar.gz ~/db_dump.sql
-
Switch the database to
read-only
mode to avoid losing data that can appear while creating the dump. -
Create a directory for the dump files:
mkdir db_dump
-
Install the
mydumper
utility in the source cluster as follows (Ubuntu):sudo apt update && sudo apt install mydumper --yes
-
Create a database dump:
mydumper \ --triggers \ --events \ --routines \ --outputdir=db_dump \ --rows=10000000 \ --threads=8 \ --compress \ --database=<database name> \ --user=<username> \ --ask-password \ --host=<source cluster master host FDQN or IP>
Where:
--triggers
: Trigger dump.--events
: Event dump.--routines
: Stored procedure and function dump.--outputdir
: Dump file directory.--rows
: Number of rows in table fragments. The smaller the value, the more files in a dump.--threads
: Number of threads used. The recommended value is equal to half the server's free cores.--compress
: Output file compression.
-
In the dump file, change the table engine names to
InnoDB
:sed -i -e 's/MyISAM/InnoDB/g' -e 's/MEMORY/InnoDB/g' `find /db_dump -name '*-schema.sql'`
-
Archive the dump:
tar -cvzf db_dump.tar.gz ~/db_dump
(Optional) Uploading a dump to a virtual machine in Nebius AI
-
Connect to an intermediate virtual machine over SSH.
-
Copy the archive containing the database dump to the intermediate virtual machine using the
scp
utility, for instance:scp ~/db_dump.tar.gz <VM username>@<VM public IP>:~/db_dump.tar.gz
-
Extract the dump from the archive:
tar -xzf ~/db_dump.tar.gz
Restoring data
Alert
For Managed Service for MySQL clusters, AUTOCOMMIT
This method is suitable if you created your dump with the mysqldump
utility.
-
Install the
mysql
utility on the host the dump is being restored from as follows (Ubuntu):sudo apt update && sudo apt install mysql-client --yes
-
Start the database restore from the dump:
-
If you restore a dump from the VM in Nebius AI:
mysql \ --host=c-<target cluster ID>.rw.mdb.nemax.nebius.cloud \ --user=<username> \ --port=3306 \ <database name> < ~/db_dump.sql
-
If you are restoring your dump from a host connecting to Nebius AI from the internet, obtain an SSL certificate and transmit the
--ssl-ca
and the--ssl-mode
parameters in the restore command:mysql \ --host=c-<target cluster ID>.rw.mdb.nemax.nebius.cloud \ --user=<username> \ --port=3306 \ --ssl-ca=~/.mysql/root.crt \ --ssl-mode=VERIFY_IDENTITY \ <database name> < ~/db_dump.sql
-
This method is suitable if you created your dump using the mydumper
utility and are using an intermediate virtual machine for the restore.
-
Install the
myloader
utility to the host that you are using to restore the dump as follows (Ubuntu):sudo apt update && sudo apt install mydumper --yes
-
Start the database restore from the dump:
myloader \ --host=c-<target cluster ID>.rw.mdb.nemax.nebius.cloud \ --directory=db_dump/ \ --overwrite-tables \ --threads=8 \ --compress-protocol \ --user=<username> \ --ask-password
You can get the cluster ID with a list of clusters in the folder.
Deleting created resources
Delete the resources you no longer need to avoid being charged for them:
- Delete the Managed Service for MySQL cluster.
- If you created an intermediate virtual machine, delete it.