Prerequisites:
Steps to perform an in-place upgrade:
I> Change the software base directory to point to the latest version of mysql ( Ex : MySQL 5.6).
C> cd /apps/mysql/
C> rm -f mysql ( Removing the current base directory symbolic link.)
C> ln -s mysql-5.6.35-linux-glibc2.5-x86_64 mysql
1) All Previous versions of MySQL required to upgrade to
MySQL 5.6 before proceed with MySQL 5.7 upgrade.
2) DBAs are advised to convert accounts that use
themysql_old_password authentication plugin to
use mysql_native_password instead, because support
for mysql_old_password has been removed. For account upgrade
instructions, see Section 7.5.1.3, “Migrating Away from Pre-4.1
Password Hashing and the mysql_old_password Plugin”.
I> Use the following query to check the
no of users using old passwords and new passwords.
C> SELECT COUNT(*),IF(plugin = '',
IF(LENGTH(password) = 16, 'OLD',IF(password = '', 'NONE', 'NEW')), plugin)
format FROM mysql.user GROUP BY format;
3) Strict SQL mode for transactional storage engines (STRICT_TRANS_TABLES) is now enabled by
default. Make sure your previous MySQL version configured with STRICT_TRANS_TABLES otherwise test your
application with STRICT_TRANS_TABLES before proceed with
actual upgrade.
4) If the audit logs are enabled, rotate the audit log file manually before the upgrade activity. ( Audit log format has been changed in MySQL 5.7 hence manual audit log rotation is required.)
4) If the audit logs are enabled, rotate the audit log file manually before the upgrade activity. ( Audit log format has been changed in MySQL 5.7 hence manual audit log rotation is required.)
5) Before
performing an in-place upgrade, perform a clean shutdown by changing fast
shutdown configuration parameter. set global innodb_fast_shutdown=0;
Steps to perform an in-place upgrade:
NOTE
: Take full database instance backup before proceeding with upgrade activity.
1) Configure MySQL to perform a slow shutdown by
setting innodb_fast_shutdown to 0. Login to
database and run the following command.
C> SET GLOBAL
innodb_fast_shutdown=0;
2) Shut down the MySQL server.
C> /etc/init.d/mysqld
stop
3) Upgrade the
MySQL binaries or packages in place (replace the old binaries or packages with
the new ones).
NOTE : Assuming mysql installation directory as "/apps/mysql/" and "/apps/mysql/mysql/" as base directory and current base directory "/apps/mysql/mysql/" is pointing to "/apps/mysql/mysql-5.5.40".
I> Download required versions of MySQL software and install as per the requirement.
C> cd /apps/mysql/
C> wget https://cdn.mysql.com//Downloads/MySQL-5.6/mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz
C> tar -zxvf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz
NOTE : Assuming mysql installation directory as "/apps/mysql/" and "/apps/mysql/mysql/" as base directory and current base directory "/apps/mysql/mysql/" is pointing to "/apps/mysql/mysql-5.5.40".
I> Download required versions of MySQL software and install as per the requirement.
C> cd /apps/mysql/
C> wget https://cdn.mysql.com//Downloads/MySQL-5.6/mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz
C> tar -zxvf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz
I> Change the software base directory to point to the latest version of mysql ( Ex : MySQL 5.6).
C> cd /apps/mysql/
C> rm -f mysql ( Removing the current base directory symbolic link.)
C> ln -s mysql-5.6.35-linux-glibc2.5-x86_64 mysql
I> If the MySQL is running on MySQL5.5
with custom installation, make necessary changes to the file /etc/init.d/mysqld
if it is default installation as suggest in the bug report https://bugs.mysql.com/bug.php?id=84263
- $bindir/mysqld_safe
--datadir="$datadir" --pid-file="$mysqld_pid_file_path"
$other_args >/dev/null
&
+ bin/mysqld_safe
--datadir="$datadir" --pid-file="$mysqld_pid_file_path"
$other_args >/dev/null &
4) Start the DB
instance.
C> /etc/init.d/mysql
start
5) Run
mysql_upgrade.
C> /apps/mysql/mysql/bin/mysql_upgrade --socket=/tmp/mysql_3306.sock -u root -p 1>/tmp/1.out 2>/tmp/2.out
C> /apps/mysql/mysql/bin/mysql_upgrade --socket=/tmp/mysql_3306.sock -u root -p 1>/tmp/1.out 2>/tmp/2.out
I> Go through upgrade logs
(/tmp/1.out ) and make sure that all the tables have been upgraded and
compatible with MySQL new version, in case if you find any issues with the
tables take appropriate action by running either "repair table" or by
taking backup and restoring that particular tables.
I> Upgrade MySQL help tables.
C> mysql --socket=/tmp/mysql_3306.sock -u root -p < /apps/mysql/mysql//share/fill_help_tables.sql
I> Upgrade MySQL help tables.
C> mysql --socket=/tmp/mysql_3306.sock -u root -p < /apps/mysql/mysql//share/fill_help_tables.sql
6) Shut down and
restart the MySQL server to ensure that any changes made to the system tables
take effect.
C> /etc/init.d/mysqld restart
7) Go through the MySQL error logs and make sure that no error & warning messages after the upgrade. If any, take appropriate action to clear these errors / warnings.
8) Repeat steps 2 to 7 if you need to perform multiple version of upgrades ( Ex : MySQL 5.5 to MySQL 5.6 and from MySQL 5.6 to MySQL 5.7)
C> /etc/init.d/mysqld restart
7) Go through the MySQL error logs and make sure that no error & warning messages after the upgrade. If any, take appropriate action to clear these errors / warnings.
8) Repeat steps 2 to 7 if you need to perform multiple version of upgrades ( Ex : MySQL 5.5 to MySQL 5.6 and from MySQL 5.6 to MySQL 5.7)
References :
1) https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html
1) https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html
