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!
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:
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!
Exist is your data solutions partner of choice!
Explore the next level of your digital transformation journey with big data and analytics. Let’s look at opportunities to better maximize your ROI by turning your data into actionable intelligence. Connect with us today, and we’ll proudly collaborate with you!