Our partner, Arcion Labs, walks you through a step-by-step process of migrating your data from MySQL to SingleStoreDB using their Change Data Capture (CDC) platform.
Organizations increasingly rely on data-driven strategies to drive growth and beat the competition — and the first hurdle is often siloed data. Traditionally, databases used to store this data and relational parameters in different tables. But as business needs evolve and data volume continues to grow, data analytics become critical — and the role of databases becomes increasingly significant in achieving business critical KPIs.
As organizations realize the importance of immediate, actionable analytics, they continue to seek a big data platform that provides robustness to their applications, while ensuring it can scale to meet the emerging suite of web and mobile applications. These applications also deal with unstructured data sources a lot more than traditional applications.
Solving for Modern Application Challenges
NoSQL databases were designed to solve this challenge for modern applications, allowing users to store massive workloads without adhering to a set schema — and still continue to build high performance applications. But the biggest challenge with NoSQL databases is the architectural complexity and additional know-how required to work on them.
They also lack the ability to establish relations between data, leading users to seek the confluence of traditional RDBMS convenience and NoSQL performance. A new genre of databases brought the best of two worlds to the data and developer community. These new databases are SQL compliant, making it easier for users to apply their legacy knowledge of RDBMS technology and SQL query, while exploiting the performance of NoSQL databases for any unstructured data source.
Organizations have warmed up to these databases and SingleStoreDB is a popular database in this category. Its proprietary design ensures high performance at larger workloads, making it the perfect choice for organizations with massive operational data seeking real-time data analytics.
In this blog, we will walk you through a step-by-step process of migrating your data from a SQL server database (MySQL) to SingleStoreDB. These steps can be replicated to a large extent to migrate data from any SQL- server based database to SingleStoreDB.
What Is MySQL?
A popular open-source relational database, MySQL (owned by Oracle Corporation) is SQL-based and supports Online Transaction Processing (OLTP) operations. It operates under the open-source GNU General Public License, giving users the rights to use and modify according to the terms of the license.
MySQL uses the client-server model, which is extremely popular among users given its flexibility. Developers can choose any programming language or driver of their choice to connect to the database and start data processing.
It is also part of the LAMP stack, another popular software stack used by several web applications and services. Oracle provides enterprise versions of MySQL for specialized applications. However, open-source MySQL is in itself extremely reliable, efficient and great for enterprise applications.
MySQL: Key Features
As discussed earlier, these are the key features of MySQL:
Ease of use
- Easy installation process
- Supports multiple operating systems and a wide range of applications
- Database administrators do not need to acquire new skills
Security
- Proven and tested
- Built-in encryption, access and privilege management
Scalability
- Native replication architecture for high scalability
- Scale-on-demand available on Amazon RDS
High Availability
- Highly available architecture supports data loss prevention
- Disaster recovery
- Full replication for downstream systems
Performance
- High on reliability and performance
- Consistent and compliant data using Atomicity, Consistency, Isolation and Durability (ACID)
What Is SingleStoreDB?
SingleStoreDB is a real-time, distributed SQL database. Its unified architecture supports both analytical (OLAP) and transactional (OLTP) processing, offering two modes of deployment:
- SingleStoreDB Self-Managed for manual deployment on your hardware and database management.
- Singlestore Helios, a fully managed version where you can choose hosting with most major cloud providers.
SingleStoreDB: Key Features
Let’s look at SingleStore’s key features:
Speed
- Extremely low latency operations due to rapid data ingestion and querying capabilities.
- High concurrency
- Efficient data storage and retrieval using row and column store patterns
Scalability
- Compute separate from storage
- Horizontal scaling for application data
- Cost-effective scaling with high performance
Security
- Secure by design
- End-to-end encryption
- Built-in authentication and authorization
- Proven validations for enterprise compliance through certifications (ISO/IEC 27001 and SOC Type 2)
Load Data From MySQL to SingleStoreDB Using Arcion
Arcion is a cloud-native Change Data Capture (CDC) platform. Zero data loss and extremely low latency data pipelines are unique to Arcion. It offers both self-managed and cloud based versions for more flexibility to the user. In this example, let’s walk through the migration steps using Arcion Cloud.
Log in to Arcion Cloud to set up the replication pipeline. This enables the CDC.
Select the replication mode and write mode. Arcion supports the following replication modes:
- Snapshot
- Full (with CDC)
Select Full.
There are two write modes:
- Replacing
- Truncating
Select Truncating.
Assign a name to the replication. In this example, we call it “MySQL to SingleStore.”
Click Next. The Source screen opens. Click Create New.
Select MySQL and click Continue.
Add the following MySQL instance information:
- Connection Name
- Host
- Port
- Username
- Password
Leave the remaining as default. Use the MySQL instance Username and Password.
Click Save Connection. Click Sync Connector to extract the MySQL schema. This connects Arcion to MySQL, and downloads the schema.
On completion, you get the retrieved schema. Click Continue.
The source data is now configured. Click Test Connection.
On a successful test, click Done.
After this successful test, click Continue to Destination to set up the destination database.
On the Destination screen, click New Connection to set up the SingleStoreDB connector.
Select SingleStore as the Connection Type; click Continue.
Enter these connection details:
- Connection Name
- Host
- Port
- Username
- Password
Leave the rest as default. Use the SingleStore instance username and password created earlier for authentication.
Click Continue to create the connection.
Click Continue to Filter to begin pipeline filter configuration. Click Select All. This replicates the source to the destination.
Click Start Replication.
Snapshot mode
The initial data is loaded into SingleStoreDB. The data pipeline will continue to run (shown on the screen as RUNNING), capturing and applying those modifications to the destination. The row replication rate is zero until the source has new data. The Phase of the pipeline changes to Change Data Capture from Loading Snapshot Data.
Arcion identifies the data we add to the MySQL instance and replicates to SingleStoreDB in real time.
CDC mode
Simplify migrations with CDC. CDC provides zero downtime migrations and ensures data integrity in both source and target databases at all times.
Benefits of Using Arcion
Arcion makes the data migration process from MySQL to SingleStoreDB seamless, delivering:
Sub-second latency
- Highly scalable supporting massive transactional workloads at ultra-low latency data pipelines
- CDC solution that scales both horizontally and vertically by virtue of its end-to-end, multi-threaded architecture
- Maximum throughput achieved through patent pending process parallelization technique
100% agentless
- Only CDC vendor to provide a fully agentless CDC
- Support the most popular enterprise databases
- Zero data loss, high-performance, and built-in security
Data consistency guaranteed
- Transactional integrity
- Built-in automatic data validation ensuring data integrity
- Zero data loss
Automatic schema conversion & evolution
- Out-of-the-box support for schema changes
- No data loss and unplanned downtimes
- No reloading needed so no additional compute resources required
- Cost effective
Conclusion
In this article, we covered the steps to migrate your data from MySQL to SingleStoreDB. We also saw how Arcion Cloud, a no-code solution can be an effective and efficient tool for this process. If you need an on-premise solution, try Arcion Self-managed. No matter the deployment model, Arcion CDC brings you the highest level of reliability, flexibility and scalability as a data migration and replication tool.
Interested in testing it for yourself? Get started with a free trial of SingleStoreDB and Arcion Cloud.
More from SingleStore & Arcion
