yugabytedb philippines

YugabyteDB Migration Strategy

While there have been many who began their journey to relational databases with the simple and popular MySQL, the evolution of business use cases involving more than read optimization and the need for more performant, full-fledged, read/write-optimized OLTP systems have given rise to a widespread migration from MySQL to Postgres.

Along with this, the transition from monolithic to cloud-native has also paved the way for distributed SQL systems that allow for read/write functionality in every node of the database cluster (while maintaining ACID-compliance across all nodes) and cloud-agnostic deployments of these nodes across geographic zones and regions. This is the future of the database, a future where reliability, accessibility, and scalability are built into the product. The future of the database is YugabyteDB.
 

From MySQL to YugabyteDB—fast!

The method that we will be using to migrate a MySQL database to YugabyteDB is through the use of pgloader, a very reliable tool for migrating from MySQL (even SQL Server) to Postgres. We will first migrate the MySQL database to a Dockerized Postgres instance using Dockerized pgloader.

Once the MySQL database has been migrated to Postgres, we will then use the ysql_dump utility that comes with every installation of YugabyteDB to dump the Postgres database into a YugabyteDB-friendly format. This is one of the very useful traits of ysql_dump: it ensures that your Postgres dump can be fully restored in a YugabyteDB instance.

After getting the dump, we will restore this dump in the blank YugabyteDB database that we’ve created beforehand, thereby completing the migration from MySQL to YugabyteDB!

Steps

1. Get the Postgres Docker container

docker run -e POSTGRES_HOST_AUTH_METHOD=trust -p 5432:5432 -d postgres:11

2. Create the MySQL database counterpart in Dockerized Postgres

CREATE DATABASE <db name>;

3. Run Dockerized pgloader to load from MySQL to Dockerized Postgres

docker run --rm --name pgloader dimitri/pgloader:latest pgloader --debug mysql://<user name>:<password>@<ip address of MySQL DB server>:3306/<source database name> postgresql://postgres@<ip address of Dockerized Postgres>:5432/<destination database name>

*If a user error is encountered, make sure the user and IP address combination indicated in the error is created in the MySQL source and has access to the databases to be migrated.”

4. Since pgloader creates a Postgres schema using the database name and puts the tables there, we can change the schema name to “public”

DO LANGUAGE plpgsql
     $body$
     DECLARE
     l_old_schema NAME = '<schema name>';
     l_new_schema NAME = 'public';
     l_sql TEXT;
     BEGIN
     FOR l_sql IN
     SELECT
          format('ALTER TABLE %I.%I SET SCHEMA %I', n.nspname, c.relname, l_new_schema)
     FROM pg_class c
          JOIN pg_namespace n ON n.oid = c.relnamespace
     WHERE
     n.nspname = l_old_schema AND
     c.relkind = 'r'
     LOOP
     RAISE NOTICE 'applying %', l_sql;
     EXECUTE l_sql;
     END LOOP;
     END;
     $body$;

5. In this example, we will be using Dockerized Yugabyte as the destination (also applies to other form factors)

a. 1-node cluster with no persistence: 

docker run -d --name yugabyte  -p7000:7000 -p9000:9000 -p5433:5433 -p9042:9042 yugabytedb/yugabyte:latest bin/yugabyted start --daemon=false

b. With persistence:

docker run -d --name yugabyte  -p7000:7000 -p9000:9000 -p5433:5433 -p9042:9042 -v ~/yb_data:/home/yugabyte/var yugabytedb/yugabyte:latest bin/yugabyted start --daemon=false

6. Go inside the Yugabyte container

a. To access the interactive terminal of the container:

docker exec -it <yugabyte container id> /bin/bash

b. Go to the bin directory:

cd /home/yugabyte/postgres/bin

c. Make sure destination database exists in YugabyteDB:

CREATE DATABASE <destination yugabytedb name>;

d. Dump the database in the Postgres container:

./ysql_dump -h <ip address of Postgres container> -U postgres -d <database name of postgres db> -p 5432 -f <dump name>.sql

e. Restore the Postgres dump in the blank database in the YugabyteDB instance:

./ysqlsh -p 5433 -d <database name of destination yugabyte db> -f <dump name>.sql

And there you have it! You have successfully migrated your MySQL database to the future of the database. You have migrated to YugabyteDB!