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

Calculating average memory utilisation of a MySQL DB Instance

MySQL DB Instance Memory Usage Calculations : The following parameters to be considered to calculate average memory utilisation of a MySQL Database instance.

Global Parameters : These are the fixed sized pre allocated buffers created while bringing up the DB instance.

Performance_schema.memory
innodb_buffer_pool_size
innodb_additional_mem_pool_size ( depreciated since MySQL 5.6)
key_buffer_size
query_cache_size

Connection Based Parameters : The following are the connection specific buffers few of them gets created when the new connection to the DB created, and few of them will get created as per the requirement while processing the queries.

binlog_cache_size
binlog_stmt_cache_size
bulk_insert_buffer_size
innodb_log_buffer_size
join_buffer_size
max_allowed_packet
net_buffer_length
preload_buffer_size
query_prealloc_size
range_alloc_block_size
read_buffer_size
read_rnd_buffer_size
sort_buffer_size
thread_stack
transaction_prealloc_size

A) By Using the following query you can get the average memory usage of a running MySQL DB instance.

Pre requisites :

1) get the value of “performance_schema.memory” from the performance_schema status and replace the value with “performance_schema.memory” in the following query.
2) get the max value of (Threads_cached,Threads_connected) from the status and replace it with max(Threads_cached,Threads_connected).

Query :

select (( performance_schema.memory + @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size + @@key_buffer_size + @@query_cache_size ) + max(Threads_cached,Threads_connected) * (@@binlog_cache_size + @@binlog_stmt_cache_size + @@bulk_insert_buffer_size + @@innodb_log_buffer_size + @@join_buffer_size + @@max_allowed_packet + @@net_buffer_length + @@preload_buffer_size + @@query_prealloc_size + @@range_alloc_block_size + @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@thread_stack + @@transaction_prealloc_size)) as Total_Memory;

NOTE : The out put of above command resulting the value between "VSZ" and "RSS" from "ps auuwwx" command. 

 
B) By Using the following query you can get the maximum memory can be used by the MySQL DB instance while it is dealing with the maximum no of connections with most of the memory allocations.

Pre requisites :

1) get the value of “performance_schema.memory” from the performance_schema status and replace the value with “performance_schema.memory” in the following query.

Query :

select (( performance_schema.memory + @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size + @@key_buffer_size + @@query_cache_size ) + @@max_connections * (@@binlog_cache_size + @@binlog_stmt_cache_size + @@bulk_insert_buffer_size + @@innodb_log_buffer_size + @@join_buffer_size + @@max_allowed_packet + @@net_buffer_length + @@preload_buffer_size + @@query_prealloc_size + @@range_alloc_block_size + @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@thread_stack + @@transaction_prealloc_size)) as Total_Memory;

NOTE : The out put of above command resulting the value between "VSZ" and "RSS" from "ps auuwwx" command. 

 
NOTE : You might not get the exact memory utilisation of a DB instance, these queries are only to give you the average utilisation. You might need to find out couple of other parameters and ways to find out the exact utilisation of a DB instance.

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