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