Upgrading MySQL 5.5 to MySQL 8: A Step by Step Guide

It is one of the dogmas that upgrading freeware libraries (like MySQL, Gstreamer, or LibNice) to a higher version will always be a hassle. No one wants the pain as the appreciation is not much as compared to the efforts required. This article breaks such dogmas for MySQL, providing information to help upgrade MySQL 5.5 (one of the most stable releases) to MySQL 8.

However, if you want to learn some best approaches for handling load on your MySQL server or the benefits of updating your MySQL, then you can just skip this entire section and scroll down to the bottom of this article.

But, upgrading MySQL 8 along with making our application compatible with the upgraded version of the libraries of MySQL is a whole other business. It's easier said than done since it is a pretty tiring task as well. But, hey, someone's got to do it. So, let's get on with it.

On a freshly installed RHEL8, it would appear that these libraries of MySQL are preinstalled. If not, these can be installed through yum.

mysql-errmsg-8.0
mysql-server-8.0
mysql-common-8.0
mysql-8.0

However, if these libraries are missing, then it is advised that you install these libraries yourself. It would be better to check for

libproto-lite 

However, if we want to install the MySQL client, then we need to install

mysql-libs

$ yum install -y mysql-libs

If your code internally uses MySQL libraries, then you will also need to install

mysql-devel

$ yum install -y mysql-devel

Once MySQL is installed, then we need to start it to see if it is working. Just simply run the command -

$ service mysqld restart

For a more detailed output, type

$ journalctl -xe

MySQL internally uses

my.cnf 

my.cnf 

Logging into the MySQL Console -

Login is possible via a temporary password, which is dumped into the log file when we will attempt to log in into MySQL. The temporary password can just be extracted from your MySQL 8 log file.

$ mysql -uroot
$ grep 'temporary' /var/log/mysql/mysql.log
$ mysql -uroot -p<Temporary_Password>
>>>

Just change the password then using the method mentioned later down in the article.

However, on executing the command

$  grep -i "password" /var/log/mysql/mysqld.log

[Server] root@localhost is created with an empty password ! 

$ mysql
>>>

Now, you are probably in the MySQL console and if the above commands do not work, and you just simply can't log in to the console, then just reinstall the

 mysql-server 

 /etc/my.cnf

Creating Users in MySQL

>>> use mysql;
>>> insert into user (Host,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Reload_priv,Shutdown_priv, Process_priv,File_priv,Grant_priv,References_priv,Index_priv,Alter_priv,Show_db_priv,Super_priv,Create_tmp_table_priv,Lock_tables_priv,Execute_priv,Repl_slave_priv,Repl_client_priv,Create_view_priv,Show_view_priv,Create_routine_priv,Alter_routine_priv,Create_user_priv,Event_priv,Trigger_priv,Create_tablespace_priv,ssl_cipher,x509_issuer,x509_subject) values('%','

tom

','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','0x','0x','0x');

These extra 3 columns are mandatory when we are porting our user to MySQL 8. We can just ignore the rest of the newly added for now.

'tom' 

ssl_cipher  : 0x
x509_issuer : 0x
x509_subject: 0x

A more formatted view of the above query can be viewed as -

Set the password for the above-created user by -

$ service mysql restart
$ mysql -uroot mysql -e "SET PASSWORD FOR '<USER>'@'%' = '<PASSWORD>'"

It's better to set the native engine of MySQL now for our user. Just execute the following command after proper modification-

$ mysql -uroot mysql -e "ALTER USER '<USERNAME>'@'%' IDENTIFIED WITH mysql_native_password BY '<PASSWORD>'"
$ service mysql restart

That's it, once you have created the user, you can create and inject your own personal database without any problem.

$ mysql -u<USERNAME> -p<PASSWORD> <DB_NAME> < <SQL_FILE>.sql

Certain parameters of MySQL 5.5 have become redundant, which were used in

 my.cnf 

old_passwords=1
query_cache_limit = 4096M
query_cache_size = 4096M
query_cache_type = 1

Remove all the obsolete parameters from your my.cnf file or just find the proper replacement for them.

The easy way to identify is to just run MySQL with required

my.cnf

To run your MySQL as root, simply write the below-mentioned line into the

[mysqld] 

 my.cnf

user=root

Pro-TIP's :

Pro-tip: Please see that your table name does not clash with the MySQL Functions and Keywords. As during injecting, no failures will be prompted by the MySQL server. But, when you will try to execute the query, chances are it will definitely fail.

If you cannot find, what is wrong with your query, you might want to search your table name for a particular function existing in MySQL which can be looked at from their documentation page.

We were using a table named LAG in a legacy code, but while upgrading we were faced with a normal query failing multiple times. After debugging, we came to know that it is because of the LAG function now introduced in MySQL 8 which was lacking in MySQL 5.5

Pro-tip: It is always wise to switch your table engine from MyISAM to InnoDB. MyISAM uses complete table-level locking while executing a query whereas InnoDB uses row-level locking and making your multiple queries execute simultaneously making your performance better. Set your engine in MySQL using this link.

Pro-tip: If many users are accessing your MySQL with different applications, it is always advisable to separate them by creating multiple users and then limiting their total connections. The same can be achieved by making changes in

my.cnf

max_connection = 3000

 user (max_user_connection) =1000

Pro-tip: It has been observed multiple times that the query fails due to timeout when the database is operating under stress conditions. Due to this queries pile upon each other and make things worse. It is better to reduce the lock wait timeout (should be updated to 300), so that the retry rate will be faster and the database will not go into the hanged (or paused / stuck) state.

Pro-tip: It is always a magnificent idea to split your table into two. One table should consist of mainly the fields that you read from the database, and the other table should consist of the columns to which writing has to be done.

They could be linked with some key, but this will increase the overall performance of the application, as the operation that only requires reading the data from the database will never be halted, and write operations can go parallelly on the other table. Read operation or

SELECT 

WRITE 

WRITE 

SELECT 

Benefits of Upgrading to MySQL 8.

The most important benefit that I would like to convey is the support of asynchronous message handling of MySQL functions which was not there in the previous versions. One of the key factors that help in the load balancing and scaling is the asynchronous message handling capabilities of the application. It increases the load handling capabilities of the application to a greater extent.

One other reason is the better functions, better codebase, better error handling, and more bug fixes that come with the new release. That is a given positive. Besides, as newer versions of the operating systems are being developed day by day, the MySQL 5.5 libraries are becoming obsolete and support for them will also be denied after a given time.

It is always better to be updated with time than to be eliminated by time. This always pays off in the long run and future employees will bless you for it.

That's it. If I ever come to know about any newer development, I will be sure to update it in this Blog. In case you need any help, you can directly email me. Just because it's a drag, it doesn't mean that there is no need for it. Our application should always be compatible with the most updated technologies and trends.