Section 4: Database Platforms for Migration. Lesson 19.
MySQL is widely described as the world's most popular open-source relational database management system. It is used for web applications, enterprise systems, content management platforms (WordPress, Drupal) and much more. It is owned and developed by Oracle Corporation, and its site describes MySQL Enterprise as providing "the most comprehensive set of advanced features, management tools and technical support" for scalability, security and uptime.
Don't confuse the names: SQL is the language; Microsoft SQL Server and MySQL are two different products that both use it, each with its own dialect.
Why MySQL is popular
- Simple to install, learn and operate
- Strong performance for read-heavy web workloads
- Free and open source (Community Edition, GPL licence)
- The default back-end for many stacks: PHP (the LAMP stack), Java, Python, Node.js and most web frameworks
- Widely used by large, well-known applications and companies
- Available as a managed service everywhere: Amazon RDS and Aurora, Azure Database for MySQL, Google Cloud SQL, Oracle HeatWave
Downloads and tools
| Product | What it is | Cost |
|---|---|---|
| MySQL Community Server | The database server itself | Free (GPL) |
| MySQL Workbench | GUI for design, queries and migration | Free |
| MySQL Shell | Advanced command-line client with JS and Python modes | Free |
| MySQL Router | Routes app traffic to the right server in a cluster | Free |
| MySQL NDB Cluster | Distributed, high-availability edition | Free or commercial |
| MySQL Enterprise Edition | Server plus enterprise tools and Oracle support | Paid subscription |
| MySQL HeatWave | Oracle's managed cloud MySQL with analytics and generative AI | Pay-as-you-go, free trial |
For practice, the Community Server plus Workbench is enough. HeatWave can be tried free on Oracle Cloud Infrastructure (OCI) with an Always Free tier and a $300 credit for 30 days (check the current offer on Oracle's site).
Free and paid
The core product is free: Community Edition has no licence fee and no pay-as-you-go billing when you run it yourself. Costs appear only when you choose the Enterprise subscription, or a managed cloud service such as HeatWave, RDS or Cloud SQL, which bill by usage like any cloud database.
Data in MySQL
MySQL uses the same relational model covered earlier: tables, rows, columns and keys. It can hold all three kinds of data from lesson 17:
- Structured: regular typed columns (INT, VARCHAR, DECIMAL, DATETIME)
- Semi-structured: the JSON column type, with functions to query inside documents
- Unstructured: BLOB and TEXT columns, although large files usually belong in object storage with a path in the database
Migrating to and from MySQL
Common migrations into MySQL come from Microsoft SQL Server, Oracle and other cloud databases, usually to cut licence costs or to standardise on an open-source web stack. MySQL is also a frequent source when teams move to PostgreSQL or a managed cloud service.
Because MySQL sits behind so many applications, a migration is not only about moving data. The application must still work correctly afterwards: its connection strings, drivers, ORM settings and SQL all have to match the new database. Understanding how MySQL talks to applications is part of the job.
MySQL quirks to watch in a migration
- TINYINT(1) is used as a boolean; targets like PostgreSQL have a real BOOLEAN type.
- AUTO_INCREMENT becomes IDENTITY in SQL Server, or GENERATED AS IDENTITY in PostgreSQL.
- Zero dates such as 0000-00-00 are allowed in older modes and rejected by most other databases.
- The utf8 character set is really 3-byte; use utf8mb4 for full Unicode, including emoji.
- Backticks quote identifiers (`order`); SQL Server uses [order] and PostgreSQL uses "order".
- Case sensitivity of table names depends on the operating system and the lower_case_table_names setting.
-- Inventory a MySQL schema before migrating
SELECT table_name, engine, table_rows,
ROUND((data_length + index_length) / 1024 / 1024, 1) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'shop'
ORDER BY size_mb DESC;
-- Find columns that often need special handling
SELECT table_name, column_name, column_type
FROM information_schema.columns
WHERE table_schema = 'shop'
AND (column_type = 'tinyint(1)' OR data_type IN ('enum', 'set', 'json'));Migration tools: mysqldump and MySQL Shell dump utilities for exports; MySQL Workbench's Migration Wizard (from SQL Server, PostgreSQL, Access and others into MySQL); pgloader (MySQL to PostgreSQL); AWS DMS and Azure DMS for cloud moves.
MySQL vs Microsoft SQL Server
| Aspect | MySQL | SQL Server |
|---|---|---|
| Licence | Open source; paid Enterprise option | Proprietary; free Developer and Express editions |
| Strength | Simplicity, web workloads, cost | Enterprise features, BI, Windows integration |
| SQL dialect | MySQL SQL | T-SQL |
| Typical stack | LAMP, Node.js, Python | .NET, Power BI, Azure |
SQL Server is generally ahead on advanced enterprise capabilities, while MySQL wins on simplicity, cost and web-stack ubiquity. Both are reliable choices, and both appear constantly in migration projects.
Key takeaways
- MySQL is the most popular open-source RDBMS, owned by Oracle and free in its Community Edition.
- Its main tools are Community Server, Workbench, Shell, Router and NDB Cluster; Enterprise and HeatWave are the paid options.
- It powers PHP, Java, Python and Node.js applications, so migrations must keep those applications working.
- Watch MySQL quirks: TINYINT booleans, AUTO_INCREMENT, zero dates, utf8 versus utf8mb4, backtick quoting.
Next: 20. Working with Oracle Database.