This article will illustrate the migration methods from other databases to MySQL using the MySQL official tool: MySQL Workbench.
Updated by Dan Zeng on 2023/03/16
MySQL is a free open-source relational database management system widely deployed by global businesses since it is fast, scalable, efficient, and user-friendly. The database is made for handling large databases in highly demanding production environments.
MySQL provides complete and ever-evolving functions for connectivity, processing speed, and data security, like authentication and data encryption. So, it is a popular choice in all kinds of industries including cloud applications, e-commerce, SaaS, and social platforms.
Users of other databases may pull away from the original ones in favor of MySQL instead to cut costs or facilitate employees. This article will illustrate the migration methods from other databases to MySQL using the MySQL official tool: MySQL Workbench.
First of all, let’s touch on what is it and what can it do.
MySQL Workbench is a graphical tool that provides data modeling, SQL development, and extensive administrative tools developed by MySQL for server configuration, user administration, database backup, etc. It could be used on Windows, Linux, and Mac OS X.
Sybase Adaptive Server Enterprise 15.x and later;
Microsoft SQL Server 2000 and later;
Microsoft Access 2007 and later;
MySQL Server 5.6 and higher;
PostgreSQL 8.0 and later;
Generic databases;
SQL Anywhere;
SQLite.
Import and export mysqldump files;
Create, run, and optimize SQL queries;
Access to database schema and objects;
Configure servers and administer users;
Perform database backup and recovery;
View database health and provide improvement tools;
Highlight syntax, auto-complete, and reuse SQL snippets;
Manage MySQL environments and database connections;
Migrate Microsoft SQL Server, Microsoft Access, Sybase ASE, PostgreSQL, MySQL, and SQLite to MySQL.
Install MySQL Workbench on Windows, Linux, and macOS.
1. Install ODBC Drivers for third-party databases connection except for MySQL.
Install the driver on the system with MySQL Workbench installed.
Use the graphical interface provided by the operating systems to install the driver.
MySQL migration wizard will open the ODBC manager available for your system through Open ODBC Administrator button.
2. In MySQL Workbench, choose Database> Migrate to open the wizard. Click Start Migration to continue on the Overview page.
3. In the Source Selection page, choose the source database from the Database System drop-down and enter other connection details. Select Store connection for future usage as (self-defined) box to save the connection. Click Test Connection> Next.
4. In the Target Selection page, select the current MySQL connections here or create a new one by choosing Manage DB Connections> input details> Test Connection> Next.
5. The wizard will retrieve the schema list from both databases automatically, click Next after this.
6. In the Schemas Selection page, choose the target schema and mapping method and click Next.
7. The source data will then be reverse-engineered, click Next after this.
8. In the Source Objects page, you can select the discovered objects from the previous step to migrate, and click Next.
9. The conversion starts, view the logs and click Next to proceed.
10. In the Manual Editing page, click View on the top right to edit the settings. The option of Show Code and Messages is shown in every view to display generated MySQL code.
Migration Problems: an informational screen to show whether there are problems.
All Objects: check and modify the object definitions. Double-click on the row to rename.
Column Mappings: show and edit the mapping for all values.
11. In the Target Creation Options page, select how the schema will be created and click Next. See the progress in the Create Schema tab and the results in the Create Target Results tab. Click Next respectively.
12. Configure migration in the Data Transfer Setup page including Data Copy and Options. Click Next.
Online copy of table data to target RDBMS: copy data to the target.
Create a batch file to copy the data at another time: use MySQL connection to transfer the data (it can be dumped or saved as a backup).
Create a shell script to use native server dump and load abilities for fast migration: generate a Zip file with all data for local migration.
13. See the generation on the Bulk Data Transfer page, and click Next.
14. View the migration report, and click Finish.
Data migration between two databases can be challenging due to project complexity, time constraints, system compatibility, and unexpected downtime. To avoid such risks associated with the migration progress, database backup is useful in case things go awry and you have a data copy to fall back on.
Vinchin Backup & Recovery is one of the most reliable and flexible data backup solutions on the market that works with Oracle Database, MySQL, SQL Server, PostgreSQL, Postgres Pro, and MariaDB, NAS, physical servers, and 12 virtualizations.
This professional backup solution provides automated data backup, multiple backup types (full, incremental, and log backup for MySQL), data reduction technologies, and encrypted transmission to simplify the operation.
It also makes things easier while recovering the database to the original destination or a new target.
Download the Enterprise edition free trial for 60-day exploration now.
1. Select the backup source from the licensed database list.
2. Specify the backup destination.
3. Set up backup strategies.
4. Review and submit.
1. Select recovery resource from the backup list.
2. Choose a target instance as the recovery destination.
3. Override the original database or redirect to a new path.
4. Review and submit.
MySQL Workbench is a free-to-use database migration tool that enables smooth data migration from Microsoft SQL Server, Microsoft Access, Sybase ASE, PostgreSQL, MySQL, generic databases, and SQLite to MySQL.
This blog elaborates on the definition, features, and migration progress of MySQL Workbench, and reminds database users to perform complete and periodic backups for reducing data loss risks.
Share on:
