mysqld_multi 的使用方法
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实例已经关闭了。
同样的方法，我们还可以启停其他的实例。