How to Get Data Out of MySQL & Into CockroachDB
Migrations are excruciating and are to be avoided whenever possible. Unfortunately, the advantages of modern database technology are forcing a lot of organizations running on traditional database solutions to consider migrations. In 2020 a lot of companies are migrating for the same reasons:
- They want to move to the cloud
- They need to scale out and shard an instance
- They are shifting to microservices or reworking an application
Manually sharding MySQL can be a nightmare and CockroachDB solves a lot of problems that manual sharding can create related to consistency, resiliency, locality, and scale. The result is that many companies have chosen to migrate from MySQL to CockroachDB and other cloud-native database products that automate sharding at the database layer.
Migration Challenges to Consider
Many of the complexities that make migrations painful can be addressed during the research stage, and will help an organization set itself up for a successful migration. Here are a few of the most common challenges that should be addressed ahead of time:
- How many SQL functions from MySQL will be available in the new database?
- How do the features compare? (Specifically, does the database have the management tools that make life easier.)
- What kind of performance can you expect?
- What are the unknowns? This is a tough one to address for obvious reasons. The best way to do it is to vet the efficacy of the support team at the database vendors you’re considering. Do they have a reputation for being extremely helpful and responsive? Are the existing Docs clear and comprehensive?
3 Steps for Migrating from MySQL to CockroachDB
This webinar will detail the nuance of each step required for migrating from MySQL to CockroachDB. After you’ve watched the webinar you can consult this docs page which offers commands to be copied and pasted.
MySQL Migration Step 1
Dump the MySQL Database: There is an option to dump an entire database or to dump one table at a time.
MySQL Migration Step 2
Host the Files where the cluster can access them: CockroachDB strongly recommends using cloud storage such as Amazon S3 or Google Cloud to host the data files for import.
MySQL Migration Step 3
Import the MySQL dump file: There are three options here depending on whether or not you want to import the entire database or just one table at a time:
- Import a full database dump
- Import a table from a full database dump
- Import a table from a table dump
If you have questions following the webinar you can join the conversation in the CockroachDB Community Slack channel to discuss your migration with CockroachDB users and engineers.