Practical MySQL & MariaDB

MySQL 5.7 vs 8.0 Differences: Features, Migration Steps, and Upgrade Best Practices

Advanced Topics
目次

1. Introduction

Overview of MySQL

MySQL is one of the most widely used open-source relational database management systems (RDBMS) in the world. It is an essential component of web applications and enterprise systems, trusted by many engineers and database administrators. Among its many strengths, upgrading MySQL plays a critical role not only in improving system stability and performance but also in enabling the use of new features.

Why Focus on the Differences Between MySQL 5.7 and 8.0?

There are many differences between MySQL 5.7 and 8.0, the two major mainstream versions. Understanding these differences is extremely important when selecting the appropriate version or planning a migration. For example, MySQL 8.0 changes the default character set and improves performance, while also deprecating or removing certain features that require attention during migration.

This article explains the key differences and new features between MySQL 5.7 and 8.0, along with migration considerations and practical advice. It also includes an FAQ section addressing common questions. This guide is designed to provide valuable insights for developers and database administrators considering a MySQL upgrade.

2. Key Differences Between MySQL 5.7 and 8.0 (Beginner Overview)

Change in Default Character Set

In MySQL 5.7, the default character set was “latin1”, but in 8.0 it has been changed to “utf8mb4”.
utf8mb4 fully supports emojis and special characters, significantly improving character set compatibility. This provides a safer and more reliable environment for international websites and applications.

Example:

-- Emoji data that may cause an error in MySQL 5.7
INSERT INTO test_table (text_column) VALUES ('😊');

In MySQL 8.0, emoji data like this can be stored without issues.

New Features Added

MySQL 8.0 introduces many new features that improve efficiency for developers and database administrators. Below are some of the most important enhancements.

  1. Enhanced JSON Functionality
  • Searching and manipulating JSON data has become more intuitive.
  • Example: Easily extract specific values from JSON data.
    sql SELECT JSON_EXTRACT(json_column, '$.key') FROM test_table;
  1. Introduction of Window Functions
  • Window functions have been added to streamline data analysis.
  • Example: Query to calculate sales rankings for each customer
    sql SELECT customer_id, sales, RANK() OVER (ORDER BY sales DESC) AS rank FROM sales_table;

Deprecated and Removed Features

In MySQL 8.0, several features have been deprecated or removed. Below are some examples.

  • Removal of query_cache
    Deprecated to improve performance. Instead, it is recommended to manage caching through indexing strategies or at the application level.
  • End of Support for Legacy Authentication Methods
    Older authentication mechanisms were removed to enhance security.

Performance Improvements

MySQL 8.0 significantly improves query processing speed and index management. These enhancements greatly benefit environments handling large volumes of data.

Examples:

  • Index Management via Heat Maps: Efficiently remove rarely used indexes.
  • InnoDB Optimization: Faster transaction processing.
-- Example of index optimization
ALTER TABLE sales_table ADD INDEX (sales_amount);

3. Deep Dive Into Differences Between MySQL 5.7 and 8.0 (Intermediate/Advanced)

Details of the Default Character Set

In MySQL 8.0, the default character set was changed to utf8mb4. This greatly improves internationalization support. For example, multi-byte character support required for storing emojis and special characters is enabled by default.

Practical Impact:

  • Character set compatibility: When migrating from 5.7 to 8.0, if the existing database uses latin1 or another character set, you should consider whether to change the character set during migration.
  • Performance impact: Using utf8mb4 can make certain string comparisons more efficient, but you should verify performance impact in advance for large-scale databases.
-- Example: Convert to utf8mb4
ALTER TABLE sample_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Enhanced JSON Functionality

MySQL 5.7 introduced the JSON data type for the first time, and MySQL 8.0 adds even more powerful capabilities. This makes it easier to manage and manipulate structured data, improving application development efficiency.

Key Improvements:

  1. JSON_TABLE Function
    You can transform JSON data into a tabular format. This allows complex JSON structures to be queried and manipulated more easily.
   SELECT * FROM JSON_TABLE(
       '[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]',
       '$[*]' COLUMNS (
           id INT PATH '$.id',
           name VARCHAR(50) PATH '$.name'
       )
   ) AS jt;
  1. Index Optimization
    You can create indexes on JSON data, improving query performance.
   CREATE INDEX json_index ON test_table (JSON_EXTRACT(json_column, '$.key'));

Performance and InnoDB Improvements

MySQL 8.0 includes significant optimization of the InnoDB engine. This improves transaction throughput and enables more efficient processing of large datasets.

Main Improvements:

  1. Introduction of the Doublewrite Buffer
    Crash resilience is improved and disk I/O efficiency is enhanced.
  2. Persistent Metadata
    Table definitions and index information are stored on disk, improving performance after restarts.

Example:

-- Example of index optimization
ALTER TABLE sales_table ADD INDEX (sales_amount);

Introduction of Window Functions

MySQL 8.0 adds window functions, making data analysis easier. With window functions, you can efficiently implement advanced operations such as aggregation and ranking.

Common Use Cases:

  • Calculate cumulative sales per customer
  • Compute rankings and ordering

Example: Calculate cumulative sales

SELECT customer_id, sales, SUM(sales) OVER (PARTITION BY customer_id ORDER BY sales_date) AS cumulative_sales
FROM sales_table;

Deprecated Features and Migration Considerations

MySQL 8.0 removes or deprecates several features. Understanding these changes is important to avoid migration issues.

  • Removed features
  • query_cache: Removed for performance improvements.
  • Legacy authentication methods: Removed to improve security.
  • Migration considerations
  • You should identify queries and application components that rely on deprecated features in advance and evaluate alternatives.

4. Migration Considerations

Check Compatibility

Before migrating, it is important to review incompatible changes and deprecated features in MySQL 8.0. Pay close attention to the following points.

  1. Changes in reserved words
    MySQL 8.0 introduces new reserved words that may conflict with existing column or table names. You should review and adjust them beforehand.
    Example: GROUPS and WINDOW became reserved words in 8.0.
   -- Example rename to avoid conflicts
   ALTER TABLE example_table RENAME COLUMN groups TO user_groups;
  1. Use of deprecated features
    Queries or settings that worked in 5.7 may be deprecated or removed in 8.0. For example, query_cache is removed in 8.0. Consider using application-level caching as an alternative.
  2. Character set and collation changes
    In MySQL 8.0, the default character set is utf8mb4. If existing tables use latin1 or other character sets, compatibility issues may occur during migration.
   -- Example: Change character set
   ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Importance of Data Backups

There is a risk of data loss or corruption during migration. Therefore, be sure to take backups in advance.

Recommended backup methods:

  • Using mysqldump
  mysqldump -u root -p --all-databases > backup.sql
  • Physical backups (for example, using Percona XtraBackup).

After creating a backup, restore it in the target environment and test to ensure the data migrates correctly.

Validate in a Test Environment

Always perform migration work in a test environment first, not directly in production. In the test environment, follow these steps to identify issues.

  1. Restore data
    Restore the backup to the test environment and simulate the migration process.
  2. Verify application behavior
    After migration, confirm the application works correctly on MySQL 8.0. Pay particular attention to SQL compatibility and performance.
  3. Load testing
    Run load tests that mimic real traffic to identify performance bottlenecks in the upgraded database.

Create a Migration Plan

A detailed plan is essential for a successful migration. Use the steps below as a reference when creating your plan.

  1. Analyze the current state
  • Review the current MySQL configuration and usage patterns.
  • Check for deprecated features and reserved word conflicts.
  1. Prepare migration tools
  • Use the official MySQL mysql_upgrade tool to perform the database upgrade smoothly.
  1. Migrate in stages
  • Start with the development environment, then proceed to staging, and finally production.
  1. Post-migration optimization
  • Rebuild indexes and perform performance tuning.
   ANALYZE TABLE my_table;
   OPTIMIZE TABLE my_table;

Troubleshooting

If issues occur during migration, resolve them using the methods below.

  1. Check error logs
    Review MySQL logs to identify the root cause.
   tail -f /var/log/mysql/error.log
  1. Use compatibility settings
    In MySQL 8.0, you can temporarily adjust sql_mode to maintain compatibility.
   SET sql_mode='NO_ENGINE_SUBSTITUTION';
  1. Use official documentation
    Refer to the official MySQL upgrade guide and FAQ to find solutions.

5. Migration Procedure Guide

Preparation for Migration

  1. Check the Current Version
    Before migrating, verify the current MySQL version. This helps when using the mysql_upgrade tool and checking compatibility.
   mysql --version
  1. Identify Deprecated Features
    Check for deprecated features or configurations that may affect the migration.
    Create a checklist based on the official “Upgrading to MySQL 8.0” guide.
  2. Back Up Data
    To ensure data safety, perform a complete backup.
    Recommended methods include:
  • Using the mysqldump command:
    bash mysqldump -u root -p --all-databases > backup.sql
  • Physical backup (for example, using Percona XtraBackup).

After creating the backup, restore it in the target environment and test to confirm that the data migrates correctly.

Migration Steps

  1. Install MySQL 8.0
    Install MySQL 8.0 on the target server.
    Installation procedures vary by operating system. Below is an example for Ubuntu:
   sudo apt update
   sudo apt install mysql-server
  1. Review Configuration Files
    Check the my.cnf (or my.ini) file and update the settings.
  • Remove deprecated options
  • Set the new character set (utf8mb4)
   [mysqld]
   character-set-server=utf8mb4
   collation-server=utf8mb4_unicode_ci
  1. Import Data
    Use the backup file to import the data.
   mysql -u root -p < backup.sql
  1. Run the mysql_upgrade Tool
    After upgrading to MySQL 8.0, execute the mysql_upgrade tool to update the database to the latest internal format.
   mysql_upgrade -u root -p

Post-Migration Optimization

  1. Rebuild Indexes
    Rebuild indexes to optimize them for the new InnoDB engine.
   ALTER TABLE table_name ENGINE=InnoDB;
  1. Verify Query Performance
    Test major application queries and adjust indexes or configurations as needed.
   EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';
  1. Monitor Logs
    Monitor error logs and slow query logs for several days after migration to detect issues early.
   tail -f /var/log/mysql/error.log

Testing and Validation

After migration, perform the following checks to ensure proper operation.

  1. Application verification
    Confirm that the application works correctly and that all queries behave as expected.
  2. Load testing
    Simulate real-world traffic to validate performance and stability.
  3. Data integrity verification
    Ensure that data remains intact after migrating from 5.7 to 8.0.
    Pay special attention to character set and collation-related issues.

What to Do If Problems Occur

If issues arise during or after migration, consider the following solutions.

  1. Restore from Backup
    If the issue cannot be resolved, consider rolling back to the previous environment using your backup.
  2. Refer to Official Support
    Consult the official MySQL upgrade guide and support resources for troubleshooting guidance.
  3. Share Error Details
    Posting detailed error information in forums or developer communities may help you find solutions.

6. FAQ (Frequently Asked Questions)

Q1: When did support for MySQL 5.7 end?

A1: Official support for MySQL 5.7 ended in October 2023. After this date, security updates and bug fixes are no longer provided. It is strongly recommended to plan a migration to MySQL 8.0 as soon as possible.

Q2: Is migration to MySQL 8.0 mandatory?

A2: Migration is not strictly mandatory, but it is recommended for the following reasons:

  • Improved security and performance in MySQL 8.0.
  • New features such as enhanced JSON support and window functions increase development efficiency.
  • Reduced security risks due to the end of MySQL 5.7 support.

Q3: Is data compatibility preserved during migration?

A3: In general, data compatibility is maintained. However, caution is required in the following cases:

  • If the database uses latin1 or legacy collations, there is a risk of character encoding issues after migration.
  • If deprecated or removed features are used, errors or unexpected behavior may occur.

It is strongly recommended to verify everything in a test environment before migration.

Q4: Can I downgrade to the previous version after upgrading?

A4: Downgrading from MySQL 8.0 back to MySQL 5.7 is extremely difficult. Internal data structures changed in MySQL 8.0, and compatibility is not easily preserved. Always take a full backup before migrating to prepare for unexpected issues.

Q5: How long does migration take?

A5: Migration time depends on the database size and environment. Key factors include:

  • Database size (larger datasets take more time).
  • Server performance and network speed.
  • Time required for testing and troubleshooting.

Small databases may take a few hours, while large-scale systems may require several days.

Q6: What major changes in MySQL 8.0 require special attention?

A6: Several important changes include:

  • The default character set changed to utf8mb4, which may cause character-related issues during migration.
  • query_cache was removed, changing caching strategies.
  • New reserved words (e.g., GROUPS and WINDOW) may conflict with existing schema definitions.

Q7: Can performance decrease after migration?

A7: When migration is performed correctly, performance typically improves in MySQL 8.0. However, temporary performance degradation may occur in the following situations:

  • Improper index configuration.
  • Inefficient queries exposed after migration.
  • New configuration settings not properly optimized.

Rebuild indexes and verify query performance after migration.

Q8: What security enhancements are included in MySQL 8.0?

A8: MySQL 8.0 strengthens security with the following features:

  • Improved authentication: caching_sha2_password is the default authentication plugin, providing stronger security.
  • Extended data encryption: InnoDB tablespace encryption is supported.
  • Login attempt limits: You can configure limits on failed login attempts to prevent unauthorized access.

Q9: Should migration to MySQL 8.0 be outsourced?

A9: This depends on your database size and in-house expertise. Small environments can often be handled internally, but for large-scale systems or environments requiring high availability, hiring experts can reduce risk.

7. Conclusion

Benefits of Migrating to MySQL 8.0

  1. Improved Performance
  • Optimizations in the new InnoDB engine significantly enhance transaction processing and query execution speed.
  1. New Features
  • Enhanced JSON functionality and window functions streamline data processing and analysis.
  • The default character set change to utf8mb4 simplifies internationalization support.
  1. Stronger Security
  • Enhanced authentication and encryption mechanisms improve overall system security.

Key Migration Considerations

  • Check deprecated and removed features
  • Review elements such as query_cache and legacy authentication methods before migrating.
  • Character set issues
  • If using latin1 or other legacy character sets, encoding problems may occur after migration. Proper character set conversion is required.
  • Test in a staging environment
  • Simulate migration in a test environment before applying changes to production to resolve potential issues in advance.

Keys to a Successful Migration

  1. Thorough preparation
  • Analyze the current database state and identify incompatibilities or risks.
  • Create backups and verify restore procedures.
  1. Perform staged migration
  • Progress from development to staging and finally production, resolving issues at each stage.
  1. Optimize after migration
  • Rebuild indexes and fine-tune configurations to maximize database performance.

Future Outlook

MySQL 8.0 continues to evolve, and leveraging its latest features can further improve development efficiency and operational stability. In particular, the JSON data type and window functions can drive transformative improvements in many applications.

Through this article, you should now have a deeper understanding of migrating from MySQL 5.7 to 8.0, including specific steps and key considerations. Plan your migration carefully and take full advantage of the new capabilities offered by MySQL 8.0.