Source DB and Target DB connection settings

Available in VPC

Check the settings of the Source DB and Target DB, and set connection information for database connection before migration. Select which of the following cases is suitable for the situation you want to migrate and check how to set it up.

• Migration in the same VPC: if the source DB exists in a VPC in NAVER Cloud Platform and the target DB is created in the same VPC
• Migration to the target DB in Public Subnet: if the source DB and the target DB have different domains, and the target DB is created on Public Subnet
• Migration to the target DB in Private Subnet/Public Subnet: when the domains of the source DB and the target DB are different, and the target DB is created in a Private Subnet or created in a Public Subnet but not assigned a public domain

Preparations

Proceed with the following preparations in order before connecting Source DB and Target DB.

1. Create Target DB in a VPC where Source DB is created in NAVER Cloud Platform.

   • Target DB should have the same type of DBMS as Source DB.
   • The same DBMS major version as the source DB is recommended.
   • For more information about how to create Target DB, see Getting started with Cloud DB for MySQL.

2. Check the binary log settings of Source DB for Source DB replication.

   • Required field: binary log activation
     • Check whether the log_bin value is displayed as ON
     • server_id value assignment
   • Recommendations: set the binary log storage period as more than 5 days
     • Set the value of the settings variable expire_logs_days or binlog_expire_logs_seconds
     • If the binary log storage period is shorter than the time elapsed for migration, the following error messages will be displayed when performing replication. In this case, you need to delete and recreate the migration after changing the source DB settings.
       Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

3. Check the Character Set/Collation and Definer account in the Source DB.

   • Cloud DB for MySQL supports [utf8, utf8mb4, euckr] Character Set only. If other settings are set on the source DB, proceed after changing them.
   • Check if the collation used in the Source DB is also supported by the Target DB.
     For example, the versions before 8.0 do not support the utf8mb4_0900_ai_ci.
   • The Definer account used on the source DB must be registered in Cloud DB for MySQL with the same account name and allowed host name in order to proceed with migration.
   • Source DB Character Set check query

    SELECT character_set_name
    FROM information_schema.TABLES T, information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA
    WHERE CCSA.collation_name = T.table_collation AND TABLE_SCHEMA NOT IN ( 'information_schema', 'mysql', 'performance_schema', 'sys' ) AND CCSA.character_set_name NOT IN ( 'utf8', 'utf8mb3', 'utf8mb4', 'euckr' );
   
    SELECT DEFAULT_CHARACTER_SET_NAME
    FROM information_schema.SCHEMATA T
    WHERE SCHEMA_NAME NOT IN ( 'information_schema', 'mysql', 'performance_schema', 'sys' ) AND DEFAULT_CHARACTER_SET_NAME NOT IN ( 'utf8', 'utf8mb3', 'utf8mb4', 'euckr');
   

   • Source DB Collation check query

   SELECT DISTINCT (table_collation) as table_collate FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys'); 
   SELECT DISTINCT (COLLATION_CONNECTION) as table_collate FROM information_schema.views WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys'); 
   SELECT DISTINCT (COLLATION_CONNECTION) as table_collate FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys'); 
   SELECT DISTINCT (DATABASE_COLLATION) as table_collate FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys'); 
   SELECT DISTINCT (COLLATION_CONNECTION) as table_collate FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');
   SELECT DISTINCT (DATABASE_COLLATION) as table_collate FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');
   SELECT DISTINCT (DEFAULT_COLLATION_NAME) as table_collate FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');
   

   • Source DB Definer account check query

   SELECT DEFINER FROM information_schema.ROUTINES
   WHERE ROUTINE_SCHEMA NOT IN ( 'information_schema', 'mysql', 'performance_schema', 'sys' ) AND SECURITY_TYPE = 'DEFINER';
   
   SELECT DEFINER FROM information_schema.VIEWS
   WHERE table_schema NOT IN ( 'information_schema', 'mysql', 'performance_schema', 'sys' ) AND SECURITY_TYPE = 'DEFINER' ;
   

4. (When migrating from MySQL 5.7 to 8.0) check the sql_mode.

   • The sql_mode='NO_AUTO_CREATE_USER' setting, which is supported by MySQL 5.7 and MariaDB 10.1.7 and later versions as the default value, is not supported starting with MySQL 8.0.
   • If the relevant settings are applied to the Source DB, please see the following and take proper actions and then proceed to the migration.
   • sql_mode settings check query

   SELECT ROUTINE_SCHEMA, ROUTINE_NAME, SQL_MODE
   FROM information_schema.routines
   WHERE ROUTINE_SCHEMA NOT IN ('sys','mysql');
   
   SELECT TRIGGER_SCHEMA, TRIGGER_NAME, sql_mode
   FROM information_schema.triggers
   WHERE TRIGGER_SCHEMA NOT IN ('sys','mysql');
   
   SELECT @@sql_mode ; 
   

   • How to take actions for issues of compatibility with sql_mode

    - Source DB > In [DB Config management], set to remove the sql_mode='NO_AUTO_CREATE_USER'
    - Source DB > Delete the Procedure, Function, and Trigger and then re-create  
    1. Create the sql files with the phrases of drop and create only for the Procedure, Function, and Trigger
       $ mysqldump -u {user name} -p -h {Source DB host name} --set-gtid-purged=OFF --routines --triggers  --no-create-info --no-data --no-create-db --add-drop-trigger {user DB} > backup.sql
    2. Within the backup.sql file, remove all the phrases of NO_AUTO_CREATE_USER
       ex) SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'  => SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'
    3. Apply
       $ mysql -u {user name} -p -h {Source DB host name} {user DB} < backup.sql          
   

Migration in the same VPC

The following describes how to set connection information if the source DB exists in a VPC in NAVER Cloud Platform and the target DB is created in the same VPC.
Proceed with the following steps in order.

• 1. Setting Target DB connection
• 2. Setting Source DB connection

1. Setting Target DB connection

Set the ACG’s Outbound rules applied to the target DB server to allow access from the target DB to the source DB.

To set ACG rules, follow these steps:

1. Check ACG applied to the target DB server in the details on the target DB.
2. Go to the ACG page by clicking in the ACG field.
3. Click the ACG applied to the target DB and then click the [Set ACG] button.
4. Click the [Outbound] tab.
5. Enter the following rules, and then click the [Add] button.
   • Protocol: select TCP
   • Destination: enter the IP address range of a subnet where the source DB is created (<example> 10.0.0.0/24)
   • Allowed port: input source DB access port
6. Click the [Apply] button.

2. Setting Source DB connection

You need to set the source DB connection information. Set the ACG rules and ACL applied to the source DB.

Set ACG on the Source DB server

• You should set the firewall for the source DB server (ACG)

• for access from the target DB.
  Edit ACG’s inbound rules applied to the source DB server to allow the following traffic. (See ACG.)

  • Protocol: TCP
  • Access source: IP address range of a subnet where the target DB is created (<example> 10.0.0.0/24)
  • Allowed port: target DB access port

• For the following case, set the outbound to the source DB.

  • NAVER Cloud Platform servers for which the source DB uses NACL policy, not ACG
  • For servers other than NAVER Cloud Platform server

Creating a source DB account and setting ACL

Create the following account and add it to ACL to connect the source DB and the target DB. (See Manage DB user.)

• The account’s access IP (Host): IP address range of a subnet where the target DB is created
• Account’s DB permissions
  • Set to include permissions for database backup and recovery
  • See Minimum permissions required for migration
• Password: be sure to use a password created in the mysql_native_password format (see MySQL start/stop and access procedures)

Migration to the target DB in Public Subnet

The following describes how to set connection information if the source DB and the target DB have different domains and the target DB is created on a public subnet.
Proceed with the following steps in order.

• Assign the public domain to the target DB and the source DB. (See Manage public domain.)
• 1. Setting Target DB connection
• 2. Setting Source DB connection

1. Setting Target DB connection

Set the ACG’s Outbound rules applied to the target DB server to allow access from the target DB to the source DB.

To set ACG rules, follow these steps:

1. Check ACG applied to the target DB server in the details on the target DB.
2. Go to the ACG page by clicking in the ACG field.
3. Click the ACG applied to the target DB and then click the [Set ACG] button.
4. Click the [Outbound] tab.
5. Enter the following rules, and then click the [Add] button.
   • Protocol: select TCP
   • Destination: enter the public domain IP address on the source DB (<example> 101.101.220.90/32)
   • Allowed port: input source DB access port
6. Click the [Apply] button.

2. Setting Source DB connection

You need to set the source DB connection information. Set the ACG rules and ACL applied to the source DB.

Set ACG on the Source DB server

• You should set the firewall for the source DB server (ACG) for access from the target DB. Edit ACG’s inbound rules applied to the source DB server to allow the following traffic.

  • Protocol: TCP
  • Access source: public domain IP address allocated to target DB (<example> 101.101.220.246/32)
  • Allowed port: target DB access port

• For the following case, set the outbound to the source DB.

  • NAVER Cloud Platform servers for which the source DB uses NACL policy, not ACG
  • For servers other than NAVER Cloud Platform server

Creating a source DB account and setting ACL

Create the following account and add it to ACL to connect the source DB and the target DB.

• Account’s access IP (Host): IP address of a public domain assigned to the target DB
• Account’s DB permissions
  • Set to include permissions for database backup and recovery
  • See Minimum permissions required for migration
• Password: be sure to use a password created in the mysql_native_password format (see MySQL start/stop and access procedures)

Migration to the target DB in Private Subnet/Public Subnet

The following describes how to set connection information if the source DB and the target DB have different domains and the target DB is created on a private subnet.
Even if the target DB is created as Public Subnet, it is possible to proceed with the following method.
Proceed with the following steps in order.

• Assign the public domain to the source DB. (See Manage public domain.)
• 1. Setting Target DB connection
• 2. Setting Source DB connection

1. Setting Target DB connection

Set the target DB connection information. Proceed with the following steps in order.

• 1. Create NAT Gateway
• 2. Connect route table
• 3. Set ACG of the target DB server

1. Create NAT Gateway

Create a NAT Gateway to connect the target DB server.

To create a NAT gateway, follow these steps:

1. From the NAVER Cloud Platform console, click Services > Networking > VPC in order.
2. Click the Subnet Management menu.
3. Click the [Create Subnet] button.
4. Create a subnet for Public NatGateway.
   
5. Click the NAT Gateway menu.
6. Click the [Create NAT Gateway] button.
7. Create a Public NAT Gateway.
   Enter the NAT Gateway name and then select VPC and Subnet where the target DB is created.
   
8. Click the [Create] button.
9. Check the IP address of the NAT Gateway created.

2. Connect route table

Create the route table and add NAT Gateway to the routing rules. Apply the route table to the subnet where the target DB exists.

To apply a route table, follow these steps:

1. From the NAVER Cloud Platform console, click Services > Networking > VPC in order.
2. Click the Route table menu.
3. Click the [Create route table] button.
4. Enter the route table name and select VPC and Subnet support types.
   • VPC: select a VPC where the target DB is created
   • Supported subnet type:
     • Select public with Public NatGateway if the Target DB is Private Subnet
     • Select public if Target DB is Public Subnet
5. Click the [Create] button.
6. Click the route table created and then click the [Set route] button.
7. Enter the following information and then click the [Create] button.
   • Destination: enter the public domain IP address assigned to the source DB (<example> 101.101.220.90/32)
   • Target type: select NATGW
   • Target name: select the NAT Gateway created in 1. Create NAT Gateway
8. Click the [OK] button.
9. Click Route table and then click the [Set related subnet] button.
10. Select a subnet where the NatGateway is created in Subnet name and then click the [Create] button.
11. Click the [OK] button.

3. Set ACG of the target DB server

Set the ACG’s Outbound rules applied to the target DB server to allow access from the target DB to the source DB.

To set ACG rules, follow these steps:

1. Check ACG applied to the target DB server in the details on the target DB.
2. Go to the ACG page by clicking in the ACG field.
3. Click the ACG applied to the target DB and then click the [Set ACG] button.
4. Click the [Outbound] tab.
5. Enter the following rules, and then click the [Add] button.
   • Protocol: select TCP
   • Destination: enter the public domain IP address on the source DB (<example> 101.101.220.90/32)
   • Allowed port: input source DB access port
6. Click the [Apply] button.

2. Setting Source DB connection

You need to set the source DB connection information. Set the ACG rules and ACL applied to the source DB.

Set ACG on the Source DB server

• You should set the firewall for the source DB server (ACG) for access from the target DB. Edit ACG’s inbound rules applied to the source DB server to allow the following traffic.

  • Protocol: TCP
  • Access source: IP address of NAT Gateway created in 1. Create NAT Gateway
  • Allowed port: target DB access port

• For the following case, set the outbound to the source DB.

  • NAVER Cloud Platform servers for which the source DB uses NACL policy, not ACG
  • For servers other than NAVER Cloud Platform server

Creating a source DB account and setting ACL

Create the following account and add it to ACL to connect the source DB and the target DB.

• Account’s access IP (Host): IP address of NAT Gateway created in 1. Create NAT Gateway
• Account’s DB permissions
  • Set to include permissions for database backup and recovery
  • See Minimum permissions required for migration
• Password: be sure to use a password created in the mysql_native_password format (see MySQL start/stop and access procedures)

Minimum permissions required for migration

• Execute the following command if the source DB is in GTID mode.

[When the Target DB is below 8.0.32 ]

GRANT PROCESS, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO '[User Name]'@'[HOST IP]';
GRANT SELECT ON mysql.* TO '[User Name]'@'[HOST IP]';
GRANT SELECT, SHOW VIEW, LOCK TABLES, TRIGGER ON [User DB].* TO '[User Name]'@'[HOST IP]';

[When the Target DB is equal to or higher than 8.0.32 ]

GRANT {RELOAD|FLUSH_TABLES}, PROCESS, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO '[User Name]'@'[HOST IP]';
GRANT SELECT ON mysql.* TO '[User Name]'@'[HOST IP]';
GRANT SELECT, SHOW VIEW, LOCK TABLES, TRIGGER ON [User DB].* TO '[User Name]'@'[HOST IP]';

• Execute the following command if the source DB is not in GTID mode.

GRANT RELOAD, PROCESS , SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO '[User Name]'@'[HOST IP]';
GRANT SELECT ON mysql.* TO '[User Name]'@'[HOST IP]';
GRANT SELECT, SHOW VIEW, TRIGGER ON [User DB].* TO '[User Name]'@'[HOST IP]';