Migrating CDB MySQL Data

Available in VPC

SQL to Hadoop (Sqoop) is a tool that supports efficient data transfer between relational databases and Hadoop.

You can import data stored in RDBMS to HDFS for analysis. Conversely, you can export analysis results to RDBMS, and you can also transfer data to HDFS, Hive, HBase, and Accumulo.

This guide introduces how to migrate data from NAVER Cloud Platform's Cloud DB for MySQL to Cloud Hadoop using Sqoop.

For information on using Sqoop and its commands, see Sqoop user guides.

Preliminary task

Create Object Storage.
- For more information on how to create Object Storage, see Object Storage user guide.
Create a Cloud Hadoop cluster.
- For more information about how to create a Cloud Hadoop cluster, see Getting started guide.
Create Cloud DB for MySQL.
- For more information on how to create Cloud DB for MySQL, see Getting started.

Data migration using Sqoop

Sqoop clients are installed by default on master node and data node servers, but not on edge node servers. You can proceed with migration without any separate installation.
After accessing the server using the Access a cluster node with SSH guide, follow these steps in order:

1. Create a Sqoop account to access Cloud DB for MySQL

Use the Manage DB user menu in Cloud DB for MySQL console to create an account for Cloud DB for MySQL by entering the configuration information.

You can view the private IP of Cloud Hadoop servers in the Hosts menu of Ambari UI.

Example: DB configuration information

User_ID: mig_acct
Host (IP): IP range of the Cloud Hadoop server.
- Example: 10.10.10.%
Password: Set as desired.

2. Set ACG

Set ACG so that the Cloud Hadoop Master Node 2 server can access Cloud DB for MySQL.

Add the Cloud Hadoop Master Node 2 server IP to the access source in the Cloud DB ACG settings.

3. Create a sample table and data

Enter the example sample table and data into the created Cloud DB for MySQL.

Create a database for testing in the Cloud DB for MySQL console.
{height="" width="70%"}
{height="" width="70%"}

Note

The MySQL access commands are as follows:
If you get a syntax error when creating the table, check the parts with the single quotes (' ').

mysql -h [CDB MySQL domain] -u [Cluster admin account name] -p

mysql> show databases;

mysql> use migdb;

CREATE TABLE `mig_test_tb` (
`col1` int(11) NOT NULL,
`col2` char(2) NOT NULL,
`col3` char(15) DEFAULT NULL,
`col4` char(4) DEFAULT NULL,
`col5` char(1) DEFAULT NULL,
PRIMARY KEY (`col1`,`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into mig_test_tb values
(1,'a','mig-test1','run','y'),(2,'a','mig-test1','init','n'),
(3,'b','mig-test1','run','y'),(4,'b','mig-test1','run','y'),
(5,'c','test-mysql','init','n'),(6,'c','test-mysql','run','n'),
(7,'d','test-mysql','init','n'),(8,'d','test-mysql','run','y'),
(9,'e','sqoop-test','run','n'),(10,'e','sqoop-test','run','y'),
(11,'f','sqoop-test','run','n'),(12,'f','sqoop-test','init','y'),
(13,'g','mysql-sqoop','run','n'),(14,'g','mysql-sqoop','init','y'),
(15,'h','mysql-sqoop','run','y'),(16,'h','mysql-sqoop','init','n'),
(17,'i','sqp-to-my','run','n'),(18,'i','sqp-to-my','init','n'),
(19,'j','sqp-to-my','init','y'),(20,'j','sqp-to-my','init','y'),
(21,'k','my-mig-sq','run','n'),(22,'k','my-mig-sq','run','y'),
(23,'i','my-mig-sq','run','y'),(24,'i','my-mig-sq','run','n');

4. Migrate data with the Sqoop command

Create a database for migration in Hive.
- You can also create it in the Hue UI. For more information on using Hive, see Hive user guides.
```
 CREATE DATABASE migdb;
```

Use the import-all-tables option in Sqoop CLI to migrate data to Hive. For the jdbc information, use the private domain and port of Cloud DB for MySQL.

Insecure Hadoop
sudo su - run the [user account] commands to switch to the user account.

sudo su - test
sqoop import-all-tables --connect jdbc:mysql://test-db.mysql.domain.com:3306/migdb --username mig_acct -P --hive-import

Secure Hadoop
Run the sudo su - hive command to switch to a user account and perform Kerberos authentication.

sudo su - hive

kinit -kt /etc/security/keytabs/hive.service.keytab  hive/<FQDN>@USER.GUIDE

sqoop import-all-tables --connect jdbc:mysql://test-db.mysql.domain.com:3306/migdb --username mig_acct -P --hive-import