MySQL 8.0 General Availability was announced in April and it comes with a host of new features. The overview about the new features and improvements made in MySQL 8.0 can be found in the following blog.
The server can be upgraded by performing either an INPLACE upgrade or LOGICAL upgrade. The INPLACE upgrade involves shutting down the MySQL 5.7 server, replacing the old binaries with MySQL 8.0 binaries and then starting the MySQL 8.0 server on the old data directory. The LOGICAL upgrade involves exporting SQL from the MySQL 5.7 version using a backup or export utility such as mysqldump or mysqlpump, installing the MySQL 8.0 binaries, and then applying the SQL to the new MySQL version.
The INPLACE upgrade is faster than the LOGICAL upgrade since it does not require loading of the databases after installing MySQL 8.0 version. Also while loading the databases during LOGICAL upgrade, errors might be encountered due to the incompatibilities which would require modifying the exported SQL file.
This blog post describes the simple steps to be followed for performing an INPLACE upgrade to MySQL 8.0.
- The first step in upgrading to MySQL 8.0 is checking the upgrade preparedness of the existing MySQL 5.7 server. We wanted this process to be simple for users and hence introduced a Upgrade Checker utility that comes with the MySQL Shell 8.0. The blog on Upgrade Checker describes how simple it is to run the utility and check if there are any actions that needs to be taken prior to upgrade. Let me run the utility against a running MySQL 5.7 server before attempting an upgrade to MySQL 8.0.
12345678910111213141516171819202122232425262728npgopala@siv20$ ./mysqlsh root:@localhost:3307 -e "util.checkForServerUpgrade();"mysqlsh: [Warning] Using a password on the command line interface can be insecure.The MySQL server at localhost:3307 will now be checked for compatibility issues for upgrade to MySQL 8.0...MySQL version: 5.7.24 - Source distribution1) Usage of db objects with names conflicting with reserved keywords in 8.0No issues found2) Usage of utf8mb3 charsetNo issues found3) Usage of use ZEROFILL/display length type attributesNo issues found4) Issues reported by 'check table x for upgrade' commandNo issues found5) Table names in the mysql schema conflicting with new tables in 8.0No issues found6) Usage of old temporal typeNo issues found7) Foreign key constraint names longer than 64 charactersNo issues foundNo known compatibility errors or issues for upgrading the target server to MySQL 8 were found.npgopala@siv20$
The utility did not report any errors to be fixed. So I can proceed with the upgrade of the server to MySQL 8.0 by replacing the old binaries.
- At this point, take a backup of the data directory using a backup or export utility such as mysqldump or mysqlpump. It is important to protect your data by creating a backup of your current databases which should include the mysql system database and log files.
- MySQL 8.0 binaries can be downloaded from here and the MySQL 8.0 server can be started on the old data directory. I have started with the default configuration here:
1npgopala@siv20:~/workspace1/mysql-trunk/dbg-8.0$ ./runtime_output_directory/mysqld --basedir=/home/npgopala/workspace1/mysql-trunk/dbg-8.0 --datadir=/home/npgopala/data57 --log-error=/home/npgopala/workspace1/mysql-trunk/dbg-8.0/mysql1.log --socket=/home/npgopala/workspace1/mysql-trunk/dbg-8.0/mysql.sock --port=3307 --gdb --user=`whoami` --console --lc-messages-dir=./sql/share
- The server is up and running, now I run the ‘mysql_upgrade’ to check and upgrade all system tables and also to check the user tables.
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788npgopala@siv20$ ./runtime_output_directory/mysql_upgrade --socket=/home/npgopala/workspace1/mysql-trunk/dbg-8.0/mysql.sock --port=3307 --user=rootChecking if update is needed.Checking server version.Running queries to upgrade MySQL server.Upgrading system table data.Checking system database.mysql.columns_priv OKmysql.component OKmysql.db OKmysql.default_roles OKmysql.engine_cost OKmysql.func OKmysql.general_log OKmysql.global_grants OKmysql.gtid_executed OKmysql.help_category OKmysql.help_keyword OKmysql.help_relation OKmysql.help_topic OKmysql.innodb_index_stats OKmysql.innodb_table_stats OKmysql.ndb_binlog_index OKmysql.password_history OKmysql.plugin OKmysql.procs_priv OKmysql.proxies_priv OKmysql.role_edges OKmysql.server_cost OKmysql.servers OKmysql.slave_master_info OKmysql.slave_relay_log_info OKmysql.slave_worker_info OKmysql.slow_log OKmysql.tables_priv OKmysql.time_zone OKmysql.time_zone_leap_second OKmysql.time_zone_name OKmysql.time_zone_transition OKmysql.time_zone_transition_type OKmysql.user OKFound outdated sys schema version 1.5.0.Upgrading the sys schema.Checking databases.events.event_table OKevents.event_table_2 OKforeign_keys.t1 OKforeign_keys.t2 OKforeign_keys.t3 OKforeign_keys.t4 OKforeign_keys.t5 OKforeign_keys.t6 OKforeign_keys.t7 OKforeign_keys.t8 OKpartitions.p5_sub OKsp.t OKsys.sys_config OKtablespace.t1 OKtablespace.t2 OKtablespace.t3 OKtablespace.t4 OKtest.55_temporal OKtest.child OKtest.geom OKtest.jemp OKtest.jemp_myisam OKtest.opening_lines OKtest.parent OKtest.t_blackhole OKtest.t_blob OKtest.t_blob_myisam OKtest.t_compressed OKtest.t_compressed2 OKtest.t_compressed3 OKtest.t_dynamic OKtest.t_gen_stored OKtest.t_gen_stored_myisam OKtest.t_gen_stored_myisam2 OKtest.t_index OKtest.t_json OKtest.t_myisam_compressed OKtest.t_sc~!@#$%^&*( OKtest.vt2 OKtriggers.t1 OKtriggers.t2 OKview_with_column_names.t1 OKUpgrade process completed successfully.Checking if update is needed.npgopala@siv20$
The system tables have been upgrade and the user tables have been checked.
- Let me start a client connection and check if all my old databases are present.
123456789101112131415161718192021222324252627282930313233npgopala@siv20$ ./runtime_output_directory/mysql --socket=/home/npgopala/workspace1/mysql-trunk/dbg-8.0/mysql.sock --port=3307 --user=rootWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 15Server version: 8.0.12 Source distributionCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+------------------------+| Database |+------------------------+| events || foreign_keys || information_schema || mysql || partitions || performance_schema || sp || sys || tablespace || test || triggers || view_with_column_names |+------------------------+12 rows in set (0.00 sec)mysql>
- Shutdown the server and start the server. Check the log file to ensure there are no errors reported.
122018-06-06T09:11:28.167169Z 0 [System] [MY-010116] [Server] /home/npgopala/workspace1/mysql-trunk/dbg-8.0/runtime_output_directory/mysqld (mysqld 8.0.12) starting as process 271472018-06-06T09:11:30.261139Z 0 [System] [MY-010931] [Server] /home/npgopala/workspace1/mysql-trunk/dbg-8.0/runtime_output_directory/mysqld: ready for connections. Version: '8.0.12' socket: '/home/npgopala/workspace1/mysql-trunk/dbg-8.0/mysql.sock' port: 3307 Source distribution
This marks the completion of the INPLACE upgrade process. If the upgrade fails due to some errors, the server reverts all changes to the data directory. An attempt to upgrade again to MySQL 8.0 involves:
- If any redo logs are present in the data directory, remove all of them.
- Start the MySQL 5.7 server on the same data directory.
- Fix all the errors that were reported during upgrade.
- Perform an INPLACE upgrade to MySQL 8.0 again.
In my next blog, I will discuss about the possible issues that maybe encountered while performing upgrade to MySQL 8.0. Meanwhile please review the upgrade prerequisites and the changes affecting upgrade to MySQL 8.0 prior to upgrade.
A detailed description about the upgrade process can be found in the MySQL documentation. As always, Thank You for using MySQL!