Migrating databases from Managed Service for MySQL to a third-party MySQL cluster
Note
Data migration from a third-party MySQL cluster is described in Migrating data from a third-party MySQL cluster.
To migrate a database deployed in a Managed Service for MySQL cluster to a third-party MySQL cluster:
- Transfer data.
- Disable data writes to the source database.
- Switch over the load to a third-party cluster.
Migration across versions is supported. The MySQL major version on a third-party cluster must be equal to or higher than the version in the Managed Service for MySQL cluster.
Getting started
Prepare the target cluster:
- Create a MySQL database
in any suitable configuration. - Make sure that you can connect to the target cluster hosts from the internet.
Additionally, to migrate data using external MySQL replication:
- Make sure all the source cluster's hosts are accessible by a public IP address so that the target cluster can connect to the source. To do this:
- Add hosts with public IP addresses.
- Delete hosts without public IP addresses.
- Install Managed Service for MySQL server SSL certificates on the target cluster's hosts. They are required to connect to the publicly available source cluster.
- Make sure that you can connect to the source cluster's hosts from the target cluster's hosts.
- Make sure that you can connect to the source cluster and the target cluster via SSL.
Transferring data using external replication
- Transfer a logical dump of the database.
- Configure the user in the source cluster to manage replication.
- Start replication in the target cluster.
- Monitor the migration process until it is complete.
- Finish the migration.
Transfer a logical dump of the database
A logical dump is a file with a set of commands running which one by one you can restore the state of a database. It is created using the mysqldump utility
-
Request the current position of the binary log to make sure that restoring the logical dump is consistent:
SHOW MASTER STATUS;
+-------------------------+----------+--------------+------------------+-----------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------------+----------+--------------+------------------+-----------------------------+ | mysql-bin-log-...000224 | 2058567 | | | d827098b-...00b86:1-1575866 | +-------------------------+----------+--------------+------------------+-----------------------------+ 1 row in set (0.00 sec)
Write down the
File
andPosition
values. You'll need them when you start replication. -
Create a dump of the source cluster database:
mysqldump \ --databases=<DB name> \ --routines \ --host=<FQDN of master host> \ --ssl-ca=<path to SSL certificate> \ --user=<username of DB owner> > <dump file>
Tip
Use a special FQDN that always points to the current master host of Managed Service for MySQL clusters.
-
Restore the database from the dump on the target cluster:
Connecting via SSLConnecting without using SSLmysql --host=<FQDN of master host> \ --user=<username> \ --password \ --port=3306 \ --ssl-ca=<path to certificate file> \ --ssl-mode=VERIFY_IDENTITY \ --line-numbers \ <DB name> < <dump file>
mysql --host=<FQDN of master host> \ --user=<username> \ --password \ --port=3306 \ --line-numbers \ <DB name> < <dump file>
-
Create a user with full access rights to the database being migrated in the target cluster:
CREATE USER '<username>'@'%' IDENTIFIED BY '<password>'; GRANT ALL PRIVILEGES ON <database name>.* TO '<username>'@'%';
Configure the user in the source cluster to manage replication
MySQL uses the master-replica
model when performing replication: the target cluster replicates the changes of the source cluster's binary log to its relay log. The host replica reproduces the changes from the relay log applying them to its own data.
To get binary log changes and manage the replication flow in the source cluster:
- Create a user.
- Assign the
ALL_PRIVILEGES
role to this user for the source cluster database. - Assign the
REPLICATION CLIENT
andREPLICATION SLAVE
global privileges to this user.
The target cluster will connect to the source cluster on behalf of this user.
Start replication in the target cluster
-
Change the target cluster's
/etc/mysql/my.cnf
configuration file to start replication:[mysqld] ... log_bin = mysql-bin server_id = 2 relay-log = /var/lib/mysql/mysql-relay-bin relay-log-index = /var/lib/mysql/mysql-relay-bin.index gtid-mode = on enforce-gtid-consistency = on
Where:
log_bin
: The name of the binary log in the target cluster.server_id
: The target cluster ID. The default value is1
. However, to run replication, make sure that the values of the source and target cluster IDs are different.relay-log
: The path to the relay log.relay-log-index
: The path to the relay log index.
Enable the
gtid-mode
andenforce-gtid-consistency options
to prepare for replication. In Managed Service for MySQL clusters, they are always activated. -
Restart the
mysql
service:sudo systemctl restart mysql
-
Connect to the target cluster on behalf of the user that is granted full access rights to the database being migrated.
-
Enable replication for this database and disable replication for service databases (they are replicated by default):
CHANGE REPLICATION FILTER REPLICATE_DO_DB=( <target cluster DB name> ), REPLICATE_IGNORE_DB=( sys, mysql, performance_schema, information_schema );
-
To assign a master for the target cluster, specify the parameters of the source cluster's master host:
Tip
Use a special FQDN that always points to the current master host of Managed Service for MySQL clusters.
CHANGE MASTER TO MASTER_HOST = '<FQDN of master host>', MASTER_USER = '<user for replication>', MASTER_PASSWORD = '<user password>', MASTER_LOG_FILE = '<File value from binary log position request>', MASTER_LOG_POS = <Position value from the binary log position request>, MASTER_SSL_CA = '<path to SSL certificate>', MASTER_SSL_VERIFY_SERVER_CERT = 0, MASTER_SSL = 1;
-
Start the relay log's replication:
START SLAVE;
This starts the process of migrating data from the source cluster's database to the target cluster's database.
Track the migration process
Use the command that returns the replication status:
SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: rc1a-hxk9audl2lsi53hc.mdb.nemax.nebius.cloud
Master_User: replica-my
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-log-rc1a-hxk9audl2lsi53hc-mdb-nemax-nebius-cloud.000225
Read_Master_Log_Pos: 1702815
Relay_Log_File: 6b6d647a39b6-relay-bin.000084
Relay_Log_Pos: 409
...
Field values show the replication status:
Slave_IO_State
andSlave_SQL_Running_State
: I/O state of the binary log and relay log streams. If replication is successful, both streams are active.Read_Master_Log_Pos
: The last position read from the master host log.Seconds_Behind_Master
: The replica's lag behind the master host (in seconds).Last_IO_Error
andLast_SQL_Error
: Replication errors.
For more information about replication status, see the MySQL documentation
Finish the migration
-
Remove the load from the source cluster and check that the application doesn't write data to the source cluster database. To do this, change the user-defined source cluster setting
MAX_UPDATES_PER_HOUR
to1
. -
Wait for the
Seconds_Behind_Master
metric value to decrease to zero. This means that all changes that occurred in the source cluster after creating the logical dump are transferred to the target cluster. -
Stop replication in the target cluster:
STOP SLAVE;
-
Switch over the load to the target cluster.
-
Remove the user managing replication on the source cluster.
-
Remove the user with full access rights to the migrated database on the target cluster if you no longer need this user.