Learn to Upgrade MySQL on CentOS

MySQL is widely used and highly regarded in relational database management systems to:

- Ensure security

- Optimize performance

- Gain access to the latest features

Thus, regularly updating your MySQL is an essential task. 

This manual will lead you through the steps of updating MySQL on a CentOS system. It includes preparation, implementation, and post-upgrade duties.

Preparation

Before starting the upgrade process, it's essential to take several preparatory steps:

1) Backup Your Data

- Create a full backup of all your databases.

- Use mysqldump or a tool like Percona XtraBackup for this purpose.

Example: mysqldump -u root -p --all-databases > all_databases.sql

2) Check Current MySQL Version

- Run: mysql --version

- Note down the current version for reference.

3) Review Release Notes

- Check the MySQL documentation for any breaking changes or deprecated features in the new version.

- Ensure your applications are compatible with the new version.

4) Update CentOS

Run: sudo yum update

This ensures your system is up-to-date before the MySQL upgrade.

5) Check for Running Processes

- Identify any applications or scripts that are currently using MySQL.

- Plan for downtime and notify relevant stakeholders.

Upgrading MySQL

The upgrade process varies depending on your current MySQL and target versions. Here, we'll cover two common strategies:

Scenario 1: Minor Version Upgrade (e.g., 5.7.x to 5.7.y)

a) Stop MySQL Service

Run: sudo systemctl stop mysqld

b) Update MySQL Packages

Run: sudo yum update mysql-server

c) Start MySQL Service

Run: sudo systemctl start mysqld

d) Check New Version

Run: mysql --version

Verify that the version has been updated.

Scenario 2: Major Version Upgrade (e.g., 5.7 to 8.0)

a) Remove Existing MySQL Repository

Run: sudo rm -f /etc/yum.repos.d/mysql-community*

b) Add New MySQL Repository

Download the RPM for the new MySQL version from the official website.

Run: sudo rpm -Uvh mysql80-community-release-el7-3.noarch.rpm

c) Disable the Old MySQL Repository and Enable the New One

Run: sudo yum-config-manager --disable mysql57-community

Run: sudo yum-config-manager --enable mysql80-community

d) Update MySQL

Run: sudo yum update mysql-server

e) Start MySQL Service:

Run: sudo systemctl start mysqld

f) Secure MySQL Installation

Run: sudo mysql_secure_installation

Follow the prompts to set a root password and secure your installation.

Post-Upgrade Tasks

After successfully upgrading MySQL, perform these important post-upgrade tasks:

a) Check MySQL Status

Run: sudo systemctl status mysqld

Ensure the service is running without errors.

b) Verify Database Integrity

Run: mysqlcheck -u root -p --all-databases

This checks and repairs any tables if necessary.

c) Review and Update Configuration

Check /etc/my.cnf for any deprecated options.

Update configuration parameters based on the new version's recommendations.

d) Update MySQL User Privileges

Run: mysql_upgrade -u root -p

This updates the grant tables and checks all tables for incompatibilities.

e) Test Applications

Thoroughly test all applications that use MySQL to ensure compatibility.

Pay special attention to SQL queries that might be affected by changes in the new version.

f) Monitor Performance

Keep an eye on server performance post-upgrade.

Use tools like MySQLTuner to optimize your configuration if needed.

g) Update Backup Scripts

Ensure your backup scripts are compatible with the new MySQL version.

Test the data backup and restore process to verify data integrity.

Troubleshooting Common Errors

While upgrading, you may face certain problems. Listed below are some frequently encountered issues along with their respective resolutions:

a) Unable to Start MySQL:

Check error logs at /var/log/mysqld.log

Ensure proper permissions on the data directory

Verify configuration file syntax

b) Incompatible Data Files

Use mysql_upgrade tool to update system tables

In extreme cases, you might need to dump and reimport data

c) Slow Performance After Upgrade

Review and adjust your my.cnf configuration

Rebuild indexes for large tables

Update statistics for the query optimizer

d) Authentication Plugin Issues (common when upgrading to MySQL 8.0)

Update user authentication methods:

ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Best Practices

To ensure smooth MySQL upgrades in the future, consider these best practices:

a) Regular Backups

Implement a robust backup strategy. It should include regular full backups and incremental backups.

b) Test Upgrades in a Staging Environment

Always test the upgrade process on a copy of your production environment before applying it to live systems.

c) Keep Documentation Updated

Maintain detailed documentation of your MySQL setup, including custom configurations and critical queries.

d) Monitor for New Releases

Stay informed about new MySQL releases and security patches.

e) Plan for Scalability

Consider future growth when upgrading, and choose versions that support your long-term scalability needs.

Conclusion

Careful planning and execution are necessary for upgrading MySQL on CentOS. Sticking to this manual and following recommended procedures guarantees a seamless upgrade procedure, reducing downtime and potential problems. Remember that every installation of MySQL is different, so be sure to customize these steps to fit your environment and needs.