MariaDB/MySQL

Overview

This document provides a guide on migrating MariaDB and MySQL used in SCP V to SCP V2. Migration proceeds by first transferring all data from the Source DB to the Target DB, and then replicating changes from the Source DB to the Target DB using a replication method.
Since the Source DB replicates data via replication while in an online state, the downtime of the Source DB can be minimized during migration.

Preliminary work

Migration Procedure

1. ## Create target cluster

Create the Target DB according to the following procedure.

1. Click the All Services > Database > MariaDB (DBaaS) > Create MariaDB / Create MySQL menu. 1. Go to the Service Home page of MariaDB(DBaaS)/MySQL(DBaaS).
2. On the Service Home page, click the Create MariaDB (DBaaS)/MySQL (DBaaS) button. 2. Go to the MariaDB(DBaaS)/MySQL(DBaaS) creation page.
3. MariaDB(DBaaS)/MySQL(DBaaS) Creation page: enter the information required to create the service and select detailed options.
4. Enter the version and service information of the target DB. 4. When entering service information, the following constraints are checked.

Constraints (common to MariaDB, MySQL)

• Allocate disk space that is at least roughly twice the size of the data used by the Source cluster.
  • We need twice the actual usage, not based on the allocated disk.
• The information below for the Target cluster must be the same as the Source cluster.
  • Database version (Major, Minor version)
  • Port
  • Database name
  • DB character set (Collation)
  • Time zone (server Timzone)
• The target cluster must meet the following conditions.
  • Backup not configured
  • Audit Log not set

Constraints (MySQL)

• The information below for the Target cluster must be the same as the Source cluster.
  • Case-sensitive ‘Table’ input in the console

Constraints (MariaDB)

• The lower_case_table_names parameter setting must be the same for the Target cluster and the Source cluster.
• After the DB is created, it can be modified on the Parameter management page.

2. ## Check Source Cluster Permissions

To transfer data from the source DB and perform replication, the following permissions are required.

MariaDB/MySQL common

1. Check migration account permissions The query execution result must have all the permissions below set to ‘Y’.

   select select_priv
        , lock_tables_priv
        , show_view_priv
        , trigger_priv
        , event_priv
        , reload_priv
        , process_priv
        , show_db_priv
        , repl_client_priv
        , repl_slave_priv
     from mysql.user where user='Migration용 계정' and host='%'
   

   Alternatively, you can check the permissions granted to the current account using the command below.

   show grants for 'Migration용 계정'@'%';
   

Constraints Since the migration account’s privileges are verified with the condition host='%', the Host information must be set to '%' when creating the migration account.

2. Add account permissions for migration If there is a value other than Y among the account permissions performed in step 1, you must add the permission to that account.

   grant select, lock tables, show view, trigger, event, reload, process, show databases, replication client, replication slave on *.* to 'Migration용 계정'@'%';
   

3. ## Migration configuration

Once the target DB is created, you can perform the migration configuration after verifying the network connection.

Constraints

• The Target cluster(s) below cannot perform Migration configuration.
  • When the instance type is Replica.
  • When the Master cluster has a replica
  • When not in Running state
  • When performing a Migration configuration and before promoting the Master (in the case where the cluster type is Migration)
  • Audit Log, when backup is configured
    

On the detail page of the Target DB, you can start the configuration by clicking the Migration configuration menu at the top right.

1. All Services > Database > MariaDB (DBaaS) > MariaDB (DBaaS) Details Click the menu. 1. Go to the MariaDB(DBaaS)/MySQL(DBaaS) Details page.
2. MariaDB(DBaaS)/MySQL(DBaaS) Details Click the More button at the top right of the page. 2. More menus are displayed.
3. Click the Migration configuration menu. 3. After checking the message of the notification popup, click the Confirm button.
4. Enter the version and service information of the target DB. 4. When entering service information, the following constraints are verified.

• Source DB Database name : required value. * Start with an English letter and enter within 1–63 characters.
• Source DB IP : required value. * IP format (e.g. * Enter as 192.168.10.1).
• Source DB Port : required value. * The allowable range for Port input is 1024~65535.
• Source DB username: required value. * Enter starting with English, within 1 to 80 characters.
• Source DB password: required value. * Enter 8~30 characters, including letters, numbers, and special characters (excluding " and ‘).

Cancel

When the Cancel button is selected, the Migration configuration is canceled and you return to the previous page.

Connection check

After entering the information of the Source DB, click the Check Connection button to check constraints specified in this guide such as network, account permissions, and database information. If any constraint is not met, the connection check will fail, and you must review the error message and take corrective action. After successfully verifying the connection, if you modify the information for the Source DB, the Complete button is disabled again and the ‘Check Connection’ button becomes enabled.

Migration configuration completed

If the connection check succeeds, the Done button becomes active. When you click the Complete button, the data transfer from the Source DB begins, and after transferring all data once and successfully establishing the Replication connection, the migration configuration is completed.
This step may take several hours or more depending on the size of the data. During migration, the cluster status is shown as Migrating, and while Migrating you cannot perform modifications on the Target cluster. Clicking the Migration configuration complete button will delete all data currently present in the target DB.

Service status synchronization

When the Target cluster in Migrating state becomes Running, and the Master promotion button is enabled, the migration has been performed successfully.
To check the current replication status, go to the detail page of the Target DB, click the ‘Service Status Sync’ button at the top right, and then you can view the replication status.
For the replication status, please check the eventdata item in MariaDB(DBaaS) > MariaDB(DBaaS) List > MariaDB(DBaaS) Details > Job History > Click Job History > Job History Details.

4. ## Master promotion

After checking the replication status, click the Master promotion button to complete the migration.

1. All Services > Database > MariaDB(DBaaS) > MariaDB(DBaaS) Details Click the menu. 1. Navigate to the MariaDB(DBaaS)/MySQL(DBaaS) Details page of MariaDB(DBaaS)/MySQL(DBaaS).
2. MariaDB(DBaaS)/MySQL(DBaaS) Details Click the Master Promotion button at the top right of the page.
3. It changes to the Editing state, and when Master promotion is complete, it changes to the Running state.