Step by step process to upgrade mysql custom installation to MySQL 5.7

Prerequisites:

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.)

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    

     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

        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

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)

References :
1) https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

2) https://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html

3) https://dev.mysql.com/doc/refman/5.7/en/upgrading.html#upgrade-before-you-begin

4) https://dev.mysql.com/doc/refman/5.7/en/upgrading.html#upgrade-procedure-inplace

5) https://dev.mysql.com/doc/refman/5.7/en/server-side-help-support.html 

Not able to shutdown the DB instance using mysqld_multi from MySQL 5.6.27 due to "Access denied" issue

Problem Statement : Not able to shutdown the DB instance using mysqld_multi from MySQL 5.6.27 due to "Access denied" issue

[ mysql@localhost ~]$  /opt/product/mysql/mysql5627/bin/mysqld_multi --defaults-file=/mysqldata/configfiles/localhost_3307.cnf stop 3307

Stopping MySQL servers

[ mysql@localhost ~]$ Warning: Using a password on the command line interface can be insecure.

/opt/product/mysql/mysql5627/bin/mysqladmin: connect to server at 'localhost' failed

error: 'Access denied for user 'multi_admin'@'localhost' (using password: YES)'

Note: Shutdown userid is created with right credentials , and entered correctly in extra configuration file , but not able to shutdown the instance with error "Access Denied" for the shutdown userid.

mysql> select user,host,password,password('multipass') from mysql.user where user = 'multi_admin'\G

                 user: multi_admin

                 host: localhost

             password       : *4FD92416C02EC3BD04A97613DB2CB33717E99D0F

password('multipass'): *4FD92416C02EC3BD04A97613DB2CB33717E99D0F

mysql> show grants for multi_admin@localhost;

| GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY PASSWORD '*4FD92416C02EC3BD04A97613DB2CB33717E99D0F' |

[mysqld_multi]

mysqld             =/opt/product/mysql/mysql5627/bin/mysqld_safe

mysqladmin    =/opt/product/mysql/mysql5627/bin/mysqladmin

user                = multi_admin

password       = multipass

Solution : This is just a suggestion to circumvent the problem until there is a permanent fix .

Obviously everyone will start debugging mysqld_multi which is a perl script . 

When you do a diff between previous version, say 5.6.16 and current version 5.6.27 , you wont find any difference in this open text perl script.

Problem lies with the binary file my_print_defaults.

If you use the previous version [ 5.6.16 ] and run the "my_print_defaults" command on the extra configuration file, it prints the options you have entered correctly .

[ mysql@localhost ~]$ /opt/product/mysql/mysql5616/bin/my_print_defaults --defaults-file=/mysqldata/configfiles/localhost_3307.cnf mysqld_multi

--mysqld=/opt/product/mysql/mysql5627/bin/mysqld_safe

--mysqladmin=/opt/product/mysql/mysql5627/bin/mysqladmin

--password=multipass

--user=multi_admin

If you use the newer version [ 5.6.27 ] and run the "my_print_defaults" command on the extra configuration file, it prints the options you have entered correctly except for password where it masks any password you have given to "*****",  therein lies the problem which breaks the mysqld_multi.

[ mysql@localhost ~]$ /opt/product/mysql/mysql5627/bin/my_print_defaults --defaults-file=/mysqldata/configfiles/localhost_3307.cnf mysqld_multi

--mysqld=/opt/product/mysql/mysql5627/bin/mysqld_safe

--mysqladmin=/opt/product/mysql/mysql5627/bin/mysqladmin

--password=*****

--user=multi_admin

Fortunately my_print_defaults has an option -s, --show          Show passwords in plain text.

[ mysql@localhost ~]$ /opt/product/mysql/mysql5627/bin/my_print_defaults -s --defaults-file=/mysqldata/configfiles/localhost_3307.cnf mysqld_multi

--mysqld=/opt/product/mysql/mysql5627/bin/mysqld_safe

--mysqladmin=/opt/product/mysql/mysql5627/bin/mysqladmin

--password=multipass

--user=multi_admin

Option #1 : Pass the password in command line itself of mysqld_multi manually 

Option #2 : Alter the mysqld_multi where it calls my_print_defaults . 

Since mysqld_multi executes my_print_defaults internally and stores all the values passed in extra configuration file, depending on the option "-s" , 

password is passed to calling script either in plain text [ if "-s" option is used" or password is passed as masked "*****"  [if "-s" option is not used ]

Open "/opt/product/mysql/mysql5627/bin/mysqld_multi" , and make the following change in line #216

Before:

    Line # 216 :  my $com= join ' ', 'my_print_defaults', @defaults_options, $group;

After :

    Line # 216 :  my $com= join ' ', 'my_print_defaults -s', @defaults_options, $group;

Solved :

[ mysql@localhost ~]$ /opt/product/mysql/mysql5627/bin/mysqld_multi --defaults-file=/mysqldata/configfiles/localhost_3307.cnf stop 3307

Stopping MySQL servers

Warning: Using a password on the command line interface can be insecure.

151213 09:03:17 mysqld_safe mysqld from pid file /zyme_db1/mysql/3307/var/lib/mysql/mysql_3307.pid ended

MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers