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

since MySQL server has a dependency on it.

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

mysql-libs

. We can do so by installing the following command -

$ yum install -y mysql-libs

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

mysql-devel

to include the MySQL header files required for the compilation of your code. The same can be done by

$ 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

to read and populate the global and user-specific variables during startup. This

my.cnf

is usually located in the /etc/ or can be found through this answer.

In case of any errors, it will be logged directly on the server. They will most probably be the configuration errors, which can easily be removed by simple google without breaking a sweat.

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

If the following output is displayed,

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

Then it means that the MySQL has been initialized with no password and you can simply login by typing-

$ 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

through yum. The Path of the log file can be seen from the

 /etc/my.cnf

file.

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'

in the bold case is the name of the user we want to create in MySQL's mysql database.

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

such as:

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

and these parameters will cause an error. Whichever parameters result in the popping of an error, just comment it or look for its alternative in the MySQL 8 Documentation.

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

[mysqld]

space in

 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

file. So, for instance, if there are a total of 3 users accessing MySQL, then it would be wise to set up

max_connection = 3000

and limiting each

 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

is nothing as compared to a

WRITE

operation as the

WRITE

operation irrespective of

SELECT

operation should be consistent and complete.

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.