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