Why convert SQL Server to MySQL?
Microsoft SQL Server and MySQL are both commonly used databases by enterprises to manage data and support businesses. But sometimes, SQL Server users will want to convert databases from SQL Server to MySQL due to some reasons, such as:
- Save costs: MySQL for enterprise is more cost-effective than Microsoft. Whether for small deployment or large deployment, MySQL spends less than SQL Server.
- Open-Source Ecosystem: MySQL integrates well with open-source stacks like LAMP (Linux, Apache, MySQL, PHP/Python). So, developers have full access to the source code and can customize or optimize database behavior.
- Broader Compatibility: SQL Server supports Windows, Linux, and Docker containers, while MySQL runs seamlessly on Windows, macOS, Linux. In addition, Many cloud services, like AWS RDS, Google Cloud SQL, Managed DBs, offers native MySQL hosting.
- Simpler Administration: MySQL has lightweight management tools and simpler backup and restore processes, and can perform well in smaller or shared hosting environments.
No matter for what purpose, you can keep on reading, we will give you 4 methods to convert SQL Server database to MySQL.
Method 1. Convert SQL Server to MySQL using MySQL Workbench
MySQL workbench is a graphical tool for managing MySQL database. It has a built-in migration wizard that can convert SQL server databases to MySQL and help you handle schema and data transfer.
- Before getting started, please make sure:✎…
- Download and install MySQL Workbench 6.3 or higher version. You can go to Workbench download page to download and install it.
- Access to SQL Server database with a login that can read all table
- The SQL Server ODBC driver or SQL Server Native Client installed (required so MySQL Workbench can connect to SQL Server).
Step 1. Open MySQL Workbench. Go to Database > Migration Wizard.
Step 2. Then you will be guided through a series of screens. Choose Microsoft SQL Server as the source RDBMS.
Step 3. Click “New…” to create a source connection.
Step 4. Enter “Hostname”, “Port”, “Username”, “Password”. And test the connection, you may need to select the correct ODBC driver.
Step 5. Now, choose MySQL as the target RDBMs.
Step 6. Create a new target connection or select an existing one.
Step 7. Enter “Hostname”, “ Port”, “Username”, “Password”. And test connection.
Step 8. Then the wizard shows available SQL Server databases. Select one or more schemas to migrate according to your needs. Then click “Next”.
Step 9. MySQL Workbench will analyze the SQL Server schema by reading tables, views, procedures, and other objects. Then it will automatically convert SQL Server schema to MySQL syntax.
Step 10. Review the generated script, and you can edit mappings manually before proceeding. Click “Next”.
Step 11. Now you can let Workbench create Schema automatically or export script and review it manually first.
Then the wizard can copy the data directly from SQL Server to MySQL. Depending on the size, this can take a while.
After migration, open MySQL Workbench and browse the new schema. And check data integrity and test key queries or applications.
Method 2. Convert MS SQL Server to MySQL with MySQLyog
MySQLyog is another way to switch to MySQL from SQL Server, but it doesn’t natively perform schema conversion, you will need to export and import schema. Here are the detailed steps.
Step 1. Firstly, you will need to export the schema (structure only) from SQL Server. In SQL Server Management Studio (SSMS), right-click “Database” > “Tasks” > “Generate Scripts” > “Schema only”.
Step 2. Open the .sql file and manually edit it to fit MySQL syntax:
Remove or change:
-
- GO statements
- IDENTITY(1,1) → AUTO_INCREMENT
- [ColumnName] → `ColumnName`
- NVARCHAR(MAX) → TEXT
- BIT → TINYINT(1)
- DATETIME2 → DATETIME
- UNIQUEIDENTIFIER → CHAR(36)
Then Save as mysql_schema.sql
Step 3. In SQLyog, connect to your MySQL server and execute this .sql file: “File” > “Open SQL Script” > “Execute”
Step 4. Open ODBC Data Source Administration. Go to “System DSN” or “User DSN” > click “Add”. Choose “ODBC driver for SQL Server”.
Step 5. Choose database you want to migrate. Enter “Data Source Name”, “Server Name”, “Authentication”. And “Save”.
Step 6. Open SQLyog, go to “Tools” > “Import External Data” > “Import from ODBC Data Source”. Choose ODBC source, select SQL Server database and target MySQL database.
Step 7. Choose whether to create table automatically or to import only data into existing table. Then Start the import.
Wait for the migration to get finished.
Method 3. Transfer Data from SQL Server to MySQL Manually
Step 1. Also, you will need to go SQL Server Management Studio to export SQL Server schema: Generate Scripts > Select Database > Choose “Schema and Data“. Save as .sql file
Step 2. Then Convert SQL syntax. Replace data types, such as:
- BIT → BOOLEAN or TINYINT(1)
- DATETIME2 → DATETIME
- NVARCHAR(MAX) → TEXT
- UNIQUEIDENTIFIER → CHAR(36)
- MONEY → DECIMAL(19,4)
Replace identifiers:
- SQL Server uses [ColumnName]; MySQL uses backticks `ColumnName`.
- Remove GO statements (MySQL doesn’t use them).
- Adjust IDENTITY(1,1) → AUTO_INCREMENT.
Step 3. You can import the converted .sql script into MySQL:
mysql -u root -p your_database < converted_schema.sql
Step 4. Use bcp or SQL Server Import/Export Wizard to export CSVs, then:
- LOAD DATA INFILE ‘table.csv’
- INTO TABLE your_table
- FIELDS TERMINATED BY ‘,’
- ENCLOSED BY ‘”‘
- LINES TERMINATED BY ‘\n’
- IGNORE 1 ROWS;
Method 4. Convert SQL Server to MySQL using Professional Migration Solution (Recommended)
Alternatively, you can also turn to a professional database migration solution, such as i2Migration. It offers significant advantages over doing everything manually (via tools like MYSQL Workbench, SQLyog, or command), especially for larger, more complex environments.
There are the key advantages of i2Migration:
- Reduced downtime: Support convert Microsoft SQL Server to MySQL database online, so there is no business interruption.
- Easy to use: It migrates database without manual conversion or complicated command.
- Transactional Consistency: verify data integrity using checkpoint-based and rollback-validated sync validation.
- Automated & Error Reduction: Offers automated schema mapping, object analysis, and repointing reduce human error and save time.
- Efficient and secure data migration: Combines compression, AES/SM4 encryption, bandwidth control and parallel transmission to ensure fast, secure transfers over long distances.
You can click the download button to get a free trial:
Conclusion
In this post, we discussion how to convert SQL Server to MySQL databases using MySQL Workbench, MySQLyog, command and a professional database migration tool. You can choose a suitable method according to your needs.
By contrast, the professional data migration solution – i2Migration is much more suitable. It streamlines the process and much more suitable for most large database environments.
FAQs about SQL to MySQL conversion
Q: Can I migrate SQL Server databases to MySQL without downtime?
A: Yes. Professional migration tools such as i2Migration allow real-time replication, enabling minimal or zero downtime migrations.
Q: How long does a SQL Server to MySQL migration take?
A: Migration time depends on the database size, schema complexity, and chosen tool. Small databases may take minutes, while enterprise-scale migrations can take several hours or days.
Q: Can I convert SQL Server bak. file to MySQL Online?
A: No, you cannot directly convert a .bak (SQL Server backup) file to MySQL online, because .bak files are proprietary SQL Server backup formats, not standard SQL scripts or data dumps.