mysqld_multi 的使用方法:

官方文檔：https://dev.mysql.com/doc/refman/5.7/en/mysqld-multi.html ? 【文檔有些問題，按照它的這個配置，mysqld_multi無法關閉實例】

mysqld_multi無法關閉實例的解決方法：

https://bugs.mysql.com/bug.php?id=77227

I have the same problem. After adding some traces in mysqld_multi, I have found the problem : since 5.6.25, my_print_defaults no longer returns password in readable form (we have to use the "--show" option to obtain it). mysqld_multi call ?my_print_default to obtain the user and password to use with mysqladmin.

Also, to be able to stop instances with mysqld_multi, I have made a modification in the "defaults_for_group" function of mysqld_multi. I have changed the following line :

my $com= join ' ', 'my_print_defaults', @defaults_options, $group;

with :

my $com= join ' ', 'my_print_defaults -s', @defaults_options, $group;

直接貼操作筆記：mkdir?/data/mysql

cd?/data/mysql

mkdir?3306/{data,tmp}?-pv

mkdir?3307/{data,tmp}?-pv

/etc/my.cnf 配置文件：[client]

port????????????=?3306

socket??????????=?/tmp/mysql.sock

user?=?root

[mysql]

no-auto-rehash

#safe-updates

prompt="[\\d]?>?"

[mysqld_multi]

mysqld?????=?/usr/local/mysql/bin/mysqld_safe

mysqladmin?=?/usr/local/mysql/bin/mysqladmin

log?=?/var/log/mysqld_multi.log

user???????=?multi_admin

password???=?123456

[mysqld]

character-set-server?=?utf8

default_storage_engine?=?InnoDB

transaction_isolation??=?READ-COMMITTED

skip_name_resolve?=?ON

skip_external_locking

max_connections?=?1500

sort_buffer_size?=?512K

read_buffer_size?=?512K

read_rnd_buffer_size?=?512K

join_buffer_size?=?256K

thread_stack?=?256K

binlog_cache_size?=?2M

connect_timeout?=?20

wait_timeout?=?14400

interactive_timeout?=?14400

net_write_timeout?=?180

lock_wait_timeout?=?120

thread_cache_size?=?64

open_files_limit?=?65535

innodb_open_files?=?4000

skip-innodb_adaptive_hash_index

query_cache_type?=?OFF

query_cache_size?=?0

expire_logs_days?=?5

sync_binlog?=?1

innodb_support_xa?=?1

relay-log-purge=1

relay_log_info_repository?=?TABLE

relay_log_recovery?=?ON

master_info_repository?=?TABLE

slave_net_timeout?=?30

skip-slave-start?=?ON

log_slave_updates?=?ON

innodb_use_native_aio?=?ON

innodb_file_per_table??=?ON

innodb_flush_log_at_trx_commit?=?1

innodb_flush_method=O_DIRECT

innodb_max_dirty_pages_pct????=?50

innodb_lock_wait_timeout??????=?50

innodb_stats_persistent?=?ON

innodb_stats_persistent_sample_pages?=?64

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld3306]

socket?????=?/tmp/mysql.sock3306

port???????=?3306

pid-file???=?/data/mysql/3306/data/mysql.pid3306

datadir????=?/data/mysql/3306/data

tmpdir?????=?/data/mysql/3306/tmp/

user???????=?root

server-id?=?111

log-bin?=?mysql-bin

binlog_format?=?ROW

max_binlog_size?=?256M

log_bin_trust_function_creators?=?ON

[mysqld3307]

socket?????=?/tmp/mysql.sock3307

port???????=?3307

pid-file???=?/data/mysql/3307/data/mysql.pid3307

datadir????=?/data/mysql/3307/data

tmpdir?????=?/data/mysql/3307/tmp/

user???????=?root

server-id?=?222

log-bin?=?mysql-bin

binlog_format?=?ROW

max_binlog_size?=?256M

log_bin_trust_function_creators?=?ON

初始化實例的時候，我們先造2個模板文件/data/3307.cnf /data/3306.cnf 寫上3307和3306實例需要的配置文件(或者不要模板文件直接初始化也行，但是不建議這么做)，然后初始化:

/usr/local/mysql/scripts/mysql_install_db?--defaults-file=/data/3307.cnf?--user=mysql?--basedir=/usr/local/mysql?--datadir=/data/mysql/3307/data

/usr/local/mysql/scripts/mysql_install_db?--defaults-file=/data/3306.cnf?--user=mysql?--basedir=/usr/local/mysql?--datadir=/data/mysql/3306/data

啟動2個實例mysqld_multi?report

mysqld_multi?start?3307

mysqld_multi?start?3306

# 創建mysql關閉用的賬號：mysql?-uroot?-S??/tmp/mysql.sock3307

CREATE?USER?'multi_admin'@'localhost'?IDENTIFIED?BY?'123456';

GRANT?SHUTDOWN?ON?*.*?TO?'multi_admin'@'localhost';

mysql?-uroot?-S??/tmp/mysql.sock3306

CREATE?USER?'multi_admin'@'localhost'?IDENTIFIED?BY?'123456';

GRANT?SHUTDOWN?ON?*.*?TO?'multi_admin'@'localhost';

操作至此，我們mysqld_multi能啟動mysql實例了，但是卻無法關閉實例。原因如下：

# 輸出明細(注意對比下面2個的輸出結果)#?my_print_defaults?mysqld_multi?mysql3307

--mysqld=/usr/local/mysql/bin/mysqld_safe

--mysqladmin=/usr/local/mysql/bin/mysqladmin

--log=/var/log/mysql/mysqld_multi.log

--user=multi_admin

--password=*****

#??my_print_defaults?mysqld_multi?mysql3307?-s

--mysqld=/usr/local/mysql/bin/mysqld_safe

--mysqladmin=/usr/local/mysql/bin/mysqladmin

--log=/var/log/mysql/mysqld_multi.log

--user=multi_admin

--password=123456???????----?可看到這里密碼能顯示出來了

vim /usr/local/mysql/bin/mysqld_multi ? +216 ? , 在my_print_defaults 后面加一個 -s 然后保存退出。

my $com= join ' ', 'my_print_defaults -s', ?@defaults_options, $group;

然后， 執行下：

mysqld_multi stop 3307

mysqld_multi report

可以看到3307實例已經關閉了。

同樣的方法，我們還可以啟停其他的實例。