MySQL upgrade from 5.7 to 8.0.21

In this post, we will see how to upgrade your MySQL database from 5.7 to 8.0.21 version

NOTE : If you are planning to upgrade in a Master-Slave Environment, please upgrade all Slave Environments first before upgrading Master Server database. Please stop the Slave process before starting the upgrade.

For more interesting posts on MySQL, please click on INDEX

Steps :

1. Download the latest MySQL 8.0.21 from the link
https://dev.mysql.com/downloads/mysql/
and then untar and create a soft link accordingly

cd /d02/mysql/product/
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.21-linux-glibc2.12-x86_64.tar.xz
tar xvf mysql-8.0.21-linux-glibc2.12-x86_64.tar.xz
ln -s mysql-8.0.21-linux-glibc2.12-x86_64 8.0.21

2. Take a valid backup of your database before proceeding with the upgrade steps

3. Change the base Directory in your .cnf file and make necessary changes in the file since some parameters of MySQL 5.7 aren’t supported in MySQL 8.0.21

# query_cache_size= 16M

[mysql]
port=3306
host=127.0.0.1
prompt="[\u@\h:\p]\_\d\_> "

[client]
port=3306
host=127.0.0.1

[mysqld]
port            = 3306
tmpdir          = /mysqldatabase/INFRA/tmp
socket          = /mysqldatabase/INFRA/admin/mysql.sock
default-authentication-plugin=mysql_native_password
collation-server = utf8mb4_general_ci
character-set-server = utf8mb4
tmpdir=/mysqldatabase/INFRA/tmp/
log_error_verbosity = 3
pid-file=/mysqldatabase/INFRA/data/mysql.pid

4. Login to mysql and check innodb_fast_shutdown parameter. (It should be 1, else need to reset)[login with out env file]

/d02/mysql/product/5.7.15/bin/mysql -h127.0.0.1 -P3306 -uroot -p

mysql> select @@innodb_fast_shutdown;
+------------------------+
| @@innodb_fast_shutdown |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

5. Shutdown the database.

[mysql@INFRA]$ /d02/mysql/product/5.7.15/bin/mysqladmin -h127.0.0.1 -P3306 -uroot -p shutdown

6. Go to 8.0.21 base directory and run upgrade.

[mysql@JACK ~]$ cd /d02/mysql/product/8.0.21/
[mysql@JACK 8.0.21]$ bin/mysqld --defaults-file=/mysqldatabase/INFRA/admin/my.cnf --upgrade=FORCE --daemonize --user=mysql
mysqld will log errors to /mysqldatabase/INFRA/data/INFRA.err
mysqld is running as pid 26074

7. Check the error log for more details

8. Login to mysql with new version. Check and validate.

[mysql@JACK 8.0.21]$ cd
[mysql@JACK ~]$ . INFRA.env
[mysql@JACK ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.21    |
+-----------+
1 row in set (0.00 sec)

mysql> \s
--------------
/d02/mysql/product/8.0.21/bin/mysql  Ver 8.0.21 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          11
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.21 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /mysqldatabase/INFRA/admin/mysql.sock
Binary data as:         Hexadecimal
Uptime:                 1 min 2 sec

Threads: 2  Questions: 12  Slow queries: 0  Opens: 2205  Flush tables: 4  Open tables: 26  Queries per second avg: 0.193
--------------

mysql> exit