sql-migration-tutorial-capella.adoc

Migrating your Data from MySQL to Capella

Using MySQL as a starting point, this guide demonstrates how to migrate your existing data from SQL tables to a Couchbase Capella instance.

Introduction

In this tutorial, you will make use of cbimport to migrate an example test database from MySQL to Couchbase Capella.

Prerequisites

Before you make a start, you will need a Capella instance to run the tutorial. If you do not currently have an instance, then you can create a Capella trial here ⇒ Capella Trial

You will also need to download and install the Server Development Tools package, which includes the cbimport command line application. You will find installation instructions for the Server Development Tools package here ⇒ Server Development Tools package

If you’re running through the examples, then you will also need an existing MySQL installation with the preexisting table structure defined in the following section.

Important

This tutorial makes use of the MySQL JSON functions that were introduced in version 5.7.22. Make sure you have installed MySQL version 5.7.22 or later.

partial$migration-tutorial/student-database-example.adoc

partial$migration-tutorial/extract-sql-data.adoc

Create your cluster, bucket, scope, and collections

Working on your Capella instance, you must first create the cluster (or use an existing cluster if you have one available). We will assume that you are creating a cluster from scratch. You will also need to create the bucket, scope, and collections to accept the student data.

---

Create the cluster.

1. Sign in to your Capella instance. You will be presented showing the operational clusters along with the project that the cluster is attached to.

2. Press the btn:[Create Cluster] button.

3. Now create a new project with a name of your choice.

4. You will now be given the opportunity to create a new cluster which will be attached to your project. For this exercise, we’re going to create a new project for our data migration, so click on the link to take you to the Project list.

5. Press the btn:[Create Project] button on the top right of the list. This will take you to a dialog from where you can fill in the name of your new project.

6. Enter a name for your project, then press btn:[Create Project]

7. Capella will create a new project for you and then switch back to the Project List. You can navigate to your project by clicking on the link in the top left of the screen.

   Tip	You can also navigate to your project by finding it in the Project List and clicking on its link.

8. On your project page, click on btn:[Create Cluster]

9. Select your cluster options (you may use the free option if it’s available), then press btn:[Create Cluster].
   After a short interval, your new cluster will be provisioned.

---

Create a bucket, scope, and collection for each cluster

Now that we have created the cluster, we need to add a location to hold the migrated data.

1. Click on the name of your cluster from the Operational Clusters page. This will take you to the Cluster details page.

2. From here, click on the btn:[Import Data] button. This will take you to the menu:Data Tools[] page.

3. Select btn:[Load with cbimport] in the central panel.

4. Click on the btn:[+Create] button in the panel on the left. You will now be presented with a dialog for creating a bucket.

5. Fill in student-bucket for the name of your bucket.

6. Fill in art-school-scope for the scope.

7. Fill in student-record-collection for the collection inside the scope.

8. Click on btn:[Create]

9. We still need to create another collection to hold the course records, so press btn:[+Create] again.

10. Make sure that you are adding to an existing bucket (student-bucket) and scope (art-school-scope). Now add another collection: course-record-collectiomn.

---

Allow your IP Address

Before running cbimport from your local machine, you need to add your machine’s IP address to Capella’s allowed list. Navigate to your cluster’s security settings and add your IP to the "Allowed IP addresses" list.

1. Click on menu:Settings[] from the top level menu.

2. From the left-hand menu, select menu:Networking[Allowed IP Addresses]

3. Click the btn:[+ Add Allowed IP] button.

4. From the menu:Allowed IP[] screen, click on btn:[Add Current IP address], then click btn:[Add Allowed IP]

---

Set up cluster access credentials

When running cbimport, authentication credentials are required, which you can set up from menu:Security[] in Capella.

1. From the menu:Settings[] page, select menu:Security[Cluster Access]

2. Click on the btn:[+ Create Cluster Access] button.

3. Type import into the Cluster Access Name field with.

4. Add a password.

5. In the Bucket Level Access section, select student-bucket for the bucket, All Scopes for the scope, and Read/Write for Access:

6. Click on btn:[Create Cluster Access] when you’re done.

---

Download your security certificate

To run cbimport, you will need to supply the security certificate for your Capella instance, which you can download from the menu:settings[] page.

1. From the top menu, click on menu:Settings[]

2. Now, from the menu:Cluster Settings[] screen, click on menu:Security[Security Certificates] in the left-hand menu.

3. On the menu:Security Certificate[] page, click on btn:[Download] to download your security certificate for the local machine.

   Tip	Make a note of the location where you stored it.

4. Return to the menu:Data Tools[] page by clicking on the link in the top level menu.

Generate and execute your cbimport command.

The data tooling page can generate a correctly formatted cbimport command line based on your import data. All you need to do is copy the command to your command line environment and fill in details such as your access password and the location of your security certificate.

---

Create the cbimport command.

1. From the menu:Data Tools[] page, click on btn:[Load with cbimport] in the central panel.

2. Locate the courses JSON file you created in the [extract-sql-data] section, then drag it into the Upload Sample File section in the central panel.

   Tip	Alternatively, you can click on Choose a file and load the file directly from the file chooser.

3. In the Choose your target section, select the student-bucket, art-school-scope, and the course-record-collection.

4. For Document keys, select Field, then pick the course-id field from the dropdown list.

---

The Copy generated command field will contain the cbimport statement you will need to execute the import:

cbimport command

cbimport json --format lines --cluster couchbases://cb.wrrmzje92urkkyn.customsubdomain.nonprod-project-avengers.com --username <<username>> --password '<<password>>' --bucket "student-bucket" --scope-collection-exp "art-school-scope.course-record-collection" --dataset 'file://<<path>>/courses.json' --generate-key '%`course-id`%' --cacert <<path to downloaded cert file>>

Before you can run the command, you will need to fill in the placeholders with information from your cluster:

• Username and password. These are the details you created in the Set up cluster access credentials section.

• the full path to your input source (courses.json)

• the full path to your downloaded security certificate, which you downloaded in the Download your security certificate section.

Your command should resemble the following:

./cbimport json --format lines \
--cluster couchbases://cb.wrrmzje92urkkyn.customsubdomain.nonprod-project-avengers.com \
--username import --password 'Password' \
--bucket "student-bucket" --scope-collection-exp "art-school-scope.course-record-collection" \
--dataset 'file:///Users/test/courses.json' \
--generate-key '%`course-id`%' \
--cacert /Users/test/Cluster-1-root-certificate.txt

You can repeat the same process to generate and run a cbimport command for the students.json file. Remember to:

1. Change the --scope-collection-exp parameter to point to art-school-scope.student-record-collection.

2. Set the --dataset parameter to point to the students.json file.

3. Set the --generate-key parameter to '%`course-id`%'

Check your data

You can use Capella’s menu:Data Tools[] to check your data has been imported correctly.