Upgrading MySQL 5.7 to 8.0 on CentOS/RHEL

By Posted on

Upgrading MySQL 5.7 to 8.0 on CentOS/RHEL

Upgrading MySQL 5.7 to 8.0 on CentOS/RHEL

Upgrading major versions of MySQL can be a complex process due to potential compatibility issues and configuration changes between versions. This article provides a comprehensive guide to upgrading from MySQL 5.7 to 8.0 on CentOS/RHEL 7/8 servers. A meticulous approach is essential for a smooth transition and to avoid data loss or application downtime.

The key steps we will cover are:

  • Prerequisites: Ensuring your system meets the necessary requirements.
  • Backup and Preparation: Creating backups of your data and configuration.
  • Installing MySQL 8.0 Repositories: Setting up the repositories for MySQL 8.0.
  • Stopping MySQL 5.7 and Removing Packages: Properly stopping the old version and removing its packages.
  • Installing MySQL 8.0: Installing the new version.
  • Starting MySQL 8.0 and Running mysql_upgrade: Starting the new version and upgrading the data dictionary.
  • Updating Configuration Files: Updating the configuration files for MySQL 8.0.
  • Creating New User Accounts: Recreating user accounts if needed.
  • Cleaning Up Old Installation: Removing any remaining artifacts from the old installation.

Following this process will help ensure a smooth and successful upgrade to MySQL 8.0. This guide will take you through each of these steps in detail, providing the necessary commands and explanations to perform a safe and efficient upgrade.

Prerequisites

Before starting the upgrade process, make sure your system meets the following prerequisites:

  • Operating System: CentOS/RHEL 7 or 8.
  • MySQL 5.7: An existing MySQL 5.7 installation.
  • Root Access: Root or sudo privileges to execute administrative commands.
  • Sufficient Disk Space: Adequate disk space for backups and the new MySQL 8.0 installation.
  • Application Compatibility Check: Ensure your applications are compatible with MySQL 8.0. Review the MySQL 8.0 release notes for any potential compatibility issues.

Step 1 – Backup and Prepare

To avoid any data loss, it is crucial to make backups before starting the upgrade process. Here are the key backup steps:

Backup MySQL Data

Use mysqldump to create logical backups of all databases:

$ mysqldump --all-databases --routines --events > mysql_backup.sql

Store this backup file in a safe location off the server. This backup is a text-based representation of your database, allowing for restoration if something goes wrong during the upgrade process.

You can also take binary backups like file system snapshots or LVM snapshots as additional protection. Binary backups are faster but may be less flexible for restoring specific data.

Review and Backup Configuration

The main MySQL config files that may need updates after the upgrade are:

  • /etc/my.cnf
  • /etc/my.cnf.d/*

Review these files and back them up to a safe location. Pay particular attention to any custom settings like log file paths, memory allocation, etc. These settings will need to be reapplied to the new MySQL 8.0 configuration.

After the upgrade, we will need to merge any needed custom configs back into the new files.

Record All MySQL Users

The upgrade could potentially affect user accounts and permissions. Capture a list of all current users by running:

mysql> SELECT User, Host FROM mysql.user;

This will let you recreate or fix any users if necessary after the upgrade. This is important because the authentication methods have changed in MySQL 8.0, and some older authentication plugins might not be supported.

Test and Validate Replication (if used)

If using MySQL replication, make sure replication is healthy and all servers are fully in sync before starting the upgrade.

Perform some test writes and reads on the master and slaves to validate. Resolve any replication issues before proceeding. Upgrading a master server with replication issues can lead to data inconsistencies.

Step 2 – Install MySQL 8.0 Repos

Now we are ready to setup the new MySQL 8.0 repositories. This will let us install the latest MySQL 8.0 releases using yum.

First install the MySQL GPG key:

$ rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2023

This allows RPM packages from MySQL to be authenticated. This ensures that the packages you are installing are genuine and haven’t been tampered with.

Next install the MySQL community repo package:

$ yum install https://repo.mysql.com/mysql80-community-release-el7-3.noarch.rpm

This will setup the MySQL 8.0 yum repo configuration. This command adds a new repository to your system, allowing you to install packages from the MySQL community repository.

Refresh your repo data:

$ yum makecache

Your CentOS/RHEL system can now install MySQL 8.0.

Step 3 – Stop MySQL 5.7 and Remove Packages

Now we need to properly stop MySQL 5.7 and remove the old package files.

Stop MySQL Service

Stop the MySQL service:

$ systemctl stop mysqld.service

Verify it shut down properly:

$ systemctl status mysqld.service

Remove Old MySQL Packages

Next use yum to remove just the MySQL 5.7 packages:

$ yum remove mysql-community-client-5.7* mysql-community-common-5.7* mysql-community-libs-5.7* mysql-community-server-5.7*

This will leave config files in place, which we will clean up later. This command removes the core MySQL 5.7 packages without affecting your data directory.

Step 4 – Install MySQL 8.0

With the old version removed, we can now install MySQL 8.0:

$ yum install mysql-community-server

This will pull in the latest MySQL 8.0 server, client, common libraries, and tools.

When the install finishes, MySQL 8.0 is now in place but not fully configured yet.

Step 5 – Start MySQL 8.0 and Run mysql_upgrade

The next steps are to start MySQL 8.0 and run mysql_upgrade to make any needed changes for the new version.

Start MySQL

Start the new MySQL 8.0 service:

$ systemctl start mysqld.service

Verify it started successfully:

$ systemctl status mysqld.service

The service should be running and you can now connect to it locally.

Run mysql_upgrade

The mysql_upgrade tool will check your databases and make any needed adjustments like changing the data dictionary format. This is a key part of the upgrade process.

Connect to MySQL and run it:

$ mysql_upgrade -u root -p

Enter your root password when prompted. This will run through any updates needed to your data for 8.0 compatibility. This tool updates the system tables and ensures that your data is compatible with the new version.

When it finishes run this to verify the data dictionary is updated:

mysql> SHOW VARIABLES LIKE 'dd_version';

It should show a value like ‘2’ for MySQL 8.0.

Review Error Log

Check over the last MySQL error log at /var/log/mysqld.log for any issues reported during startup or mysql_upgrade.

Resolve any errors before continuing. This is crucial for identifying and addressing any potential problems that may have occurred during the upgrade.

At this point MySQL 8.0 should be running successfully.

Step 6 – Update Configuration Files

Next we need to update any configuration files and settings for MySQL 8.0.

Update my.cnf

The main my.cnf file is likely outdated and missing new 8.0 specific options.

Create a new default version:

$ mysqld --initialize --user=mysql --basedir=/usr --datadir=/var/lib/mysql

This will generate a new my.cnf under /var/lib/mysql with 8.0 defaults. This command initializes the data directory and creates a new my.cnf file with the default settings for MySQL 8.0.

Copy it to /etc replacing the old file:

$ cp /var/lib/mysql/my.cnf /etc/my.cnf

Open up this new /etc/my.cnf file and re-apply any custom settings you previously had, such as:

  • innodb_buffer_pool_size
  • log_bin
  • server-id

Save the updated file when finished.

Update App Armor Settings

If using AppArmor security, it needs updated settings for MySQL 8.0:

$ aa-disable /etc/apparmor.d/usr.sbin.mysqld

Then update it:

$ echo "alias /etc/apparmor.d/usr.sbin.mysqld -> /etc/apparmor.d/disable/" | tee -a /etc/apparmor.d/usr.sbin.mysqld

This disables the old AppArmor rules and will use the defaults.

Restart MySQL and Validate Config

Restart MySQL to load the new my.cnf settings:

$ systemctl restart mysqld.service

Check that custom settings have applied correctly:

mysql> SHOW VARIABLES;

Verify your memory, storage engine, and other settings are as expected.

Also check that:

mysql> SELECT @@global.innodb_default_row_format;

Shows ‘dynamic’ which is required for MySQL 8.0.

Adjust my.cnf further if any settings did not apply correctly.

Step 7 – Create New User Accounts

If you had any custom user accounts in MySQL 5.7, they will need recreated in 8.0.

Check the list of users you captured earlier, and recreate each account using new CREATE USER syntax:

# Create app user
mysql> CREATE USER 'myapp'@'localhost' IDENTIFIED BY 'password';
# Grant permissions
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON myapp.* TO 'myapp'@'localhost';

Test connecting with each recreated user to validate permissions are working.

You may also want to force password expiration with:

mysql> ALTER USER 'user'@'host' PASSWORD EXPIRE;

This will require users to reset their password on first login.

Step 8 – Cleanup Old Installation

Once the upgrade is complete, we can remove any remaining artifacts from the old MySQL 5.7 installation:

Remove Log Files

Clear out old log files from 5.7:

$ rm /var/log/mysqld.log*

Remove Config Files

Remove any old unused 5.7 config files:

$ rm /etc/my.cnf.d/*mysql57*

Remove Data Directories

If not reusing existing data dirs, remove the 5.7 data directory:

$ rm -rf /var/lib/mysql57

This helps eliminate any confusion over multiple data directories.

Restart and Monitor MySQL

Restart MySQL one last time:

$ systemctl restart mysqld.service

Monitor logs and performance for a while to ensure stable behavior.

The upgrade to MySQL 8.0 is now fully complete!

Conclusion

Upgrading from MySQL 5.7 to 8.0 involves careful planning, preparation, and testing. The key steps are:

  • Backing up data and configuration files.
  • Installing the MySQL 8.0 repository.
  • Stopping the MySQL 5.7 service and removing its packages.
  • Installing MySQL 8.0.
  • Running the mysql_upgrade tool.
  • Updating configuration files.
  • Recreating user accounts.
  • Cleaning up the old installation.

Following this process methodically helps avoid issues and ensure a smooth transition to the latest MySQL version. Testing thoroughly validates that applications have compatibility with new behaviors. This guide offers a proven strategy for upgrading MySQL 5.7 to 8.0 on CentOS/RHEL.

Your MySQL infrastructure is now upgraded with the latest features and improvements in MySQL 8.0! The process of upgrading MySQL 5.7 to 8.0 on CentOS/RHEL can be daunting, but this guide provides a structured approach.

Alternative Solutions for Upgrading MySQL 5.7 to 8.0

While the previously described method provides a direct upgrade path, two alternative solutions can be considered depending on specific requirements and infrastructure:

1. In-Place Upgrade with MySQL Shell (Logical Upgrade)

This approach leverages the MySQL Shell’s upgrade checker and logical dump/restore capabilities for a more controlled and potentially less disruptive upgrade.

Explanation:

The MySQL Shell provides utilities to assess the compatibility of your existing MySQL 5.7 instance with MySQL 8.0. It identifies potential issues like deprecated features or incompatible data types. After addressing these issues, the MySQL Shell can perform a logical dump of your data, which can then be restored into a fresh MySQL 8.0 instance. This method allows for granular control over the upgrade process and minimizes downtime by allowing you to prepare the new MySQL 8.0 environment in parallel.

Steps:

  1. Install MySQL Shell on the MySQL 5.7 server:

    yum install mysql-shell

  2. Connect to the MySQL 5.7 instance using MySQL Shell:

    mysqlsh --uri root@localhost:3306

  3. Run the Upgrade Checker:

    util.checkForServerUpgrade();

    This will provide a detailed report of any compatibility issues. Address these issues before proceeding.

  4. Create a logical dump of the data:

    util.dumpInstance('/path/to/dump/location');

  5. Install MySQL 8.0 on a new server or in a separate environment. Follow the installation steps outlined in the original guide.

  6. Restore the dump into the new MySQL 8.0 instance:

    util.loadDump('/path/to/dump/location');

  7. Perform post-upgrade checks and adjustments: This includes verifying data integrity, updating application connection strings, and adjusting configuration settings.

Code Example (MySQL Shell):

The example code is embedded in the steps above, showcasing the use of util.checkForServerUpgrade(), util.dumpInstance(), and util.loadDump() functions within the MySQL Shell.

2. Blue/Green Deployment

This strategy involves creating a completely new MySQL 8.0 environment (the "green" environment) alongside the existing MySQL 5.7 environment (the "blue" environment). Data is replicated from the blue environment to the green environment. Once the green environment is fully synchronized and tested, traffic is switched over to the green environment, making it the new production environment.

Explanation:

Blue/Green deployments offer the lowest downtime and highest level of safety. The "blue" environment continues to serve production traffic while the "green" environment is being built and tested. Replication ensures that the data in the green environment is up-to-date. The switchover is a simple DNS change or load balancer configuration, minimizing the impact on users. If any issues arise after the switchover, traffic can be quickly rolled back to the blue environment. This is the safest way for upgrading MySQL 5.7 to 8.0 on CentOS/RHEL.

Steps:

  1. Provision a new server or environment for MySQL 8.0 (the "green" environment). Follow the installation steps outlined in the original guide.

  2. Configure replication from the MySQL 5.7 server (the "blue" environment) to the MySQL 8.0 server (the "green" environment). This involves configuring binary logging on the blue server and setting up the green server as a replication slave.

  3. Monitor the replication process to ensure that the green environment is fully synchronized with the blue environment.

  4. Test the applications against the green environment to verify compatibility and performance.

  5. Perform the switchover by changing the DNS records or load balancer configuration to point to the green environment.

  6. Monitor the green environment closely after the switchover to ensure that everything is working as expected.

  7. If any issues arise, quickly roll back to the blue environment by reverting the DNS or load balancer configuration.

Code Example (Replication Configuration):

On the MySQL 5.7 (Blue) Server (my.cnf):

[mysqld]
log-bin=mysql-bin
server-id=1

On the MySQL 8.0 (Green) Server (MySQL Shell):

dba.configureReplicationSource('root@blue_server:3306');
dba.createReplicationUser();
dba.startReplication();

These alternative solutions offer different trade-offs in terms of complexity, downtime, and risk. Choosing the right approach depends on your specific needs and constraints. Consider the size of your database, the criticality of your applications, and your tolerance for downtime when making your decision. Understanding the process for upgrading MySQL 5.7 to 8.0 on CentOS/RHEL is critical.

Related posts: