We are really excited about MySQL 8.0 new features (https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html) and our consultants spend several hours weekly, testing new features and doing research on how best we can create value for our customers from having those in production. Being an pure-play MySQL consulting, support and remote DBA services company, We are fully accountable for our customer database infrastructure operations performance, scalability, high availability and reliability. As we are aggressive about gaining maximum results from MySQL 8 investments made by our customers, We are equally conservative (our customer data reliability is critical for us !) on adopting new features, until we are fully confident after several rounds of testing (at different scales on multiple platforms) and technical review (we engage both internal and external consultants for acceptance) and acceptance before deployment in production infrastructure. In the previous versions of MySQL, before every upgrade our consultants manually spend several hours testing compatibility but MySQL 8 made this simple by introducing “upgrade checker” javascript with MySQL Shell 8.0.11 , In this blog we are writing about “upgrade checker” utility and upgrade from MySQL 5.7 to MySQL 8.0 .
Using MySQL Shell 8.0.11 “upgrade checker”
Typical “upgrade checker” run will look similar to this:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
|
MySQL JS > util.checkForServerUpgrade("root@localhost:3306")
Please provide the password for 'root@localhost:3306': **********
The MySQL server at localhost:3306 will now be checked for compatibility issues for upgrade to MySQL 8.0...
MySQL version: 5.7.22-log - MySQL Community Server (GPL)
1) Usage of db objects with names conflicting with reserved keywords in 8.0
No issues found
2) Usage of utf8mb3 charset
Warning: The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support.
sakila.actor.first_name - column's default character set: utf8
sakila.actor.last_name - column's default character set: utf8
sakila.actor_info.first_name - column's default character set: utf8
sakila.actor_info.last_name - column's default character set: utf8
sakila.actor_info.film_info - column's default character set: utf8
sakila.address.address - column's default character set: utf8
sakila.address.address2 - column's default character set: utf8
sakila.address.district - column's default character set: utf8
sakila.address.postal_code - column's default character set: utf8
sakila.address.phone - column's default character set: utf8
sakila.category.name - column's default character set: utf8
sakila.city.city - column's default character set: utf8
sakila.country.country - column's default character set: utf8
sakila.customer.first_name - column's default character set: utf8
sakila.customer.last_name - column's default character set: utf8
sakila.customer.email - column's default character set: utf8
sakila.customer_list.name - column's default character set: utf8
sakila.customer_list.address - column's default character set: utf8
sakila.customer_list.zip code - column's default character set: utf8
sakila.customer_list.phone - column's default character set: utf8
sakila.customer_list.city - column's default character set: utf8
sakila.customer_list.country - column's default character set: utf8
sakila.customer_list.notes - column's default character set: utf8
sakila.film.title - column's default character set: utf8
sakila.film.description - column's default character set: utf8
sakila.film.rating - column's default character set: utf8
sakila.film.special_features - column's default character set: utf8
sakila.film_list.title - column's default character set: utf8
sakila.film_list.description - column's default character set: utf8
sakila.film_list.category - column's default character set: utf8
sakila.film_list.rating - column's default character set: utf8
sakila.film_list.actors - column's default character set: utf8
sakila.film_text.title - column's default character set: utf8
sakila.film_text.description - column's default character set: utf8
sakila.language.name - column's default character set: utf8
sakila.nicer_but_slower_film_list.title - column's default character set: utf8
sakila.nicer_but_slower_film_list.description - column's default character set: utf8
sakila.nicer_but_slower_film_list.category - column's default character set: utf8
sakila.nicer_but_slower_film_list.rating - column's default character set: utf8
sakila.nicer_but_slower_film_list.actors - column's default character set: utf8
sakila.sales_by_film_category.category - column's default character set: utf8
sakila.sales_by_store.store - column's default character set: utf8
sakila.sales_by_store.manager - column's default character set: utf8
sakila.staff.first_name - column's default character set: utf8
sakila.staff.last_name - column's default character set: utf8
sakila.staff.email - column's default character set: utf8
sakila.staff.username - column's default character set: utf8
sakila.staff.password - column's default character set: utf8
sakila.staff_list.name - column's default character set: utf8
sakila.staff_list.address - column's default character set: utf8
sakila.staff_list.zip code - column's default character set: utf8
sakila.staff_list.phone - column's default character set: utf8
sakila.staff_list.city - column's default character set: utf8
sakila.staff_list.country - column's default character set: utf8
3) Usage of use ZEROFILL/display length type attributes
Notice: The following table columns specify a ZEROFILL/display length attributes. Please be aware that they will be ignored in MySQL 8.0
sakila.customer.active - tinyint(1)
sakila.staff.active - tinyint(1)
4) Issues reported by 'check table x for upgrade' command
No issues found
5) Table names in the mysql schema conflicting with new tables in 8.0
No issues found
6) Usage of old temporal type
No issues found
7) Foreign key constraint names longer than 64 characters
No issues found
8) Usage of obsolete MAXDB sql_mode flag
No issues found
9) Usage of obsolete sql_mode flags
No issues found
10) Usage of partitioned tables in shared tablespaces
No issues found
11) Usage of removed functions
No issues found
No fatal errors were found that would prevent a MySQL 8 upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.
1
|
At the end, “upgrade checker” prints a summary and returns an integer value describing he severity of the issues found:
- 0 – no issues or only ones categorized as notice,
- 1 – No fatal errors were found, but some potential issues were detected,
- 2 – UC found errors that must be fixed before upgrading to 8.0.
Upgrade from MySQL 5.7 to MySQL 8.0
Step 1 – Uninstall MySQL 5.7
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# yum remove mysql-community-client.x86_64 mysql-community-common.x86_64 mysql-community-devel.x86_64 mysql-community-libs.x86_64 mysql-community-libs-compat.x86_64 mysql-community-server.x86_64
Loaded plugins: fastestmirror
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-client.x86_64 0:5.7.22-1.el7 will be erased
---> Package mysql-community-common.x86_64 0:5.7.22-1.el7 will be erased
---> Package mysql-community-devel.x86_64 0:5.7.22-1.el7 will be erased
---> Package mysql-community-libs.x86_64 0:5.7.22-1.el7 will be erased
---> Package mysql-community-libs-compat.x86_64 0:5.7.22-1.el7 will be erased
---> Package mysql-community-server.x86_64 0:5.7.22-1.el7 will be erased
--> Finished Dependency Resolution
Dependencies Resolved
=======================================================================================
Package Arch Version Repository Size
=======================================================================================
Removing:
mysql-community-client x86_64 5.7.22-1.el7 @mysql57-community 106 M
mysql-community-common x86_64 5.7.22-1.el7 @mysql57-community 2.5 M
mysql-community-devel x86_64 5.7.22-1.el7 @mysql57-community 21 M
mysql-community-libs x86_64 5.7.22-1.el7 @mysql57-community 9.4 M
mysql-community-libs-compat x86_64 5.7.22-1.el7 @mysql57-community 9.2 M
mysql-community-server x86_64 5.7.22-1.el7 @mysql57-community 743 M
Transaction Summary
=======================================================================================
Remove 6 Packages
Installed size: 892 M
Is this ok [y/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Erasing : mysql-community-devel-5.7.22-1.el7.x86_64 1/6
Erasing : mysql-community-server-5.7.22-1.el7.x86_64 2/6
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave
Erasing : mysql-community-client-5.7.22-1.el7.x86_64 3/6
Erasing : mysql-community-libs-compat-5.7.22-1.el7.x86_64 4/6
Erasing : mysql-community-libs-5.7.22-1.el7.x86_64 5/6
Erasing : mysql-community-common-5.7.22-1.el7.x86_64 6/6
Verifying : mysql-community-libs-compat-5.7.22-1.el7.x86_64 1/6
Verifying : mysql-community-common-5.7.22-1.el7.x86_64 2/6
Verifying : mysql-community-devel-5.7.22-1.el7.x86_64 3/6
Verifying : mysql-community-server-5.7.22-1.el7.x86_64 4/6
Verifying : mysql-community-client-5.7.22-1.el7.x86_64 5/6
Verifying : mysql-community-libs-5.7.22-1.el7.x86_64 6/6
Removed:
mysql-community-client.x86_64 0:5.7.22-1.el7
mysql-community-common.x86_64 0:5.7.22-1.el7
mysql-community-devel.x86_64 0:5.7.22-1.el7
mysql-community-libs.x86_64 0:5.7.22-1.el7
mysql-community-libs-compat.x86_64 0:5.7.22-1.el7
mysql-community-server.x86_64 0:5.7.22-1.el7
Complete!
[root@localhost ~]#
|
Step 2 – Install MySQL 8.0
|
1
2
3
4
5
6
7
8
9
10
11
|
[root@localhost MySQL8-Community-Edition]# rpm -ivh mysql-community-server-8.0.11-1.el7.x86_64.rpm mysql-community-client-8.0.11-1.el7.x86_64.rpm mysql-community-common-8.0.11-1.el7.x86_64.rpm mysql-community-devel-8.0.11-1.el7.x86_64.rpm mysql-community-libs-8.0.11-1.el7.x86_64.rpm mysql-community-libs-compat-8.0.11-1.el7.x86_64.rpm
warning: mysql-community-server-8.0.11-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-common-8.0.11-1.e################################# [ 17%]
2:mysql-community-libs-8.0.11-1.el7################################# [ 33%]
3:mysql-community-client-8.0.11-1.e################################# [ 50%]
4:mysql-community-server-8.0.11-1.e################################# [ 67%]
5:mysql-community-devel-8.0.11-1.el################################# [ 83%]
6:mysql-community-libs-compat-8.0.1################################# [100%]
[root@localhost MySQL8-Community-Edition]#
|
Step 3- Start MySQL 8.0
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
[root@localhost MySQL8-Community-Edition]# systemctl start mysqld
[root@localhost MySQL8-Community-Edition]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.11 MySQL Community Server - GPL
Copyright (c) 2000, 2018, 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>
|
Step 4 – Run “mysql_upgrade” , mysql_upgrade checks for all tables in all databases for incompatibilities with the current version of MySQL Server, it also upgrades the system tables so that you can take advantage of new privileges or capabilities that might have been added.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
|
[root@localhost MySQL8-Community-Edition]# mysql_upgrade -u root -p
Enter password:
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading system table data.
Checking system database.
mysql.columns_priv OK
mysql.component OK
mysql.db OK
mysql.default_roles OK
mysql.engine_cost OK
mysql.func OK
mysql.general_log OK
mysql.global_grants OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.password_history OK
mysql.plugin OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.role_edges OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Found outdated sys schema version 1.5.1.
Upgrading the sys schema.
Checking databases.
employees.departments OK
employees.dept_emp OK
employees.dept_manager OK
employees.employees OK
employees.salaries OK
employees.tab1 OK
employees.titles OK
sakila.actor OK
sakila.address OK
sakila.category OK
sakila.city OK
sakila.country OK
sakila.customer OK
sakila.film OK
sakila.film_actor OK
sakila.film_category OK
sakila.film_text OK
sakila.inventory OK
sakila.language OK
sakila.payment OK
sakila.rental OK
sakila.staff OK
sakila.store OK
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.
|
Success ?? , You have successfully completed upgrade from MySQL 5.7 to MySQL 8.0 .