mysqld_multi测试

mysqld_multi测试

mysql版本：5.7.25-log
在OS上分别安装了两套mysql，
data目录为/mysql/mysql3306、 /mysql/mysql3307 。
端口分别为3306 、3307 
配置文件为：
/mysql/mysql3306/my.cnf
/mysql/mysql3307/my.cnf

参考文档：
https://mysql.net.cn/doc/refman/8.0/en/mysqld-multi.html

--mysqld_multi的配置，在/etc/下建立my.cnf文件，内容如下。这里分别把3306和3307的配置参数粘贴到对应的[mysqld3306]、[mysqld3307]下，感觉比较麻烦

[mysqld_multi]
mysqld     = /mysql/mysql3306/bin/mysqld_safe
mysqladmin = /mysql/mysql3306/bin/mysqladmin
log        = /mysql/mysqld_multi.log
user       = root
#password   = mysql    <<<< mysql 8的写法 ，pass是mysql5.7的写法 
pass       = mysql     
 
[mysqld3306]
socket     = /tmp/mysql3306.sock
port       = 3306
pid-file   = /mysql/mysql3306/data/redhat762100.pid
datadir    = /mysql/mysql3306/data
language   = /mysql/mysql3306/share/english
user       = root
 
 
basedir=/mysql/mysql3306
slow_query_log=1
slow_query_log_file=/mysql/mysql3306/data/slow.log
log-error=/mysql/mysql3306/data/error.log
long_query_time=0.5
server-id=3306
# Disabling symbolic-links is recommended to prevent assorted security risks
log-bin=mysql3306-bin
character_set_server=utf8
binlog_format=row
gtid_mode=on
log_slave_updates=1
enforce_gtid_consistency=1
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
relay_log_recovery=1
 
rpl_semi_sync_master_enabled=ON
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
 
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
#innodb_buffer_pool_dump_now=1
#innodb_buffer_pool_load_now=1
 
#add 20230928 for mysqldump
secure_file_priv=
 
[mysqld3307]
socket     = /tmp/mysql3307.sock
port       = 3307
pid-file   = /mysql/mysql3307/data/redhat762100.pid
datadir    = /mysql/mysql3307/data
language   = /mysql/mysql3307/share/english
user       = root
 
 
 
basedir=/mysql/mysql3307
slow_query_log=1
slow_query_log_file=/mysql/mysql3307/data/slow.log
log-error=/mysql/mysql3307/data/error.log
long_query_time=0.5
server-id=3307
# Disabling symbolic-links is recommended to prevent assorted security risks
log-bin=mysql3307-bin
character_set_server=utf8
 
binlog_format=row
gtid_mode=on
log_slave_updates=1
enforce_gtid_consistency=1
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
relay_log_recovery=1
 
 
rpl_semi_sync_master_enabled=ON
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
relay_log_recovery=on
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
#innodb_buffer_pool_dump_now=1
#innodb_buffer_pool_load_now=1
 
#2019-09-27add
replicate-wild-ignore-table=test.t1
#20230928add for mydqldump
secure_file_priv=

-- 启动3306 ，发现3306启动了，3307没有启动 

[root@redhat762100 mysql3307]# mysqld_multi start  3306
[root@redhat762100 mysql3307]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is not running
[root@redhat762100 mysql3307]#

-- 启动3307，发现3307启动了。3306之前已经启动 

[root@redhat762100 mysql3307]# mysqld_multi start  3307
[root@redhat762100 mysql3307]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
[root@redhat762100 mysql3307]#

-- 查看mysqld_multi.log 日志 ，启动和关闭 

Starting MySQL servers
 
2023-10-04T09:35:25.811049Z mysqld_safe Logging to '/mysql/mysql3306/data/error.log'.
2023-10-04T09:35:25.845353Z mysqld_safe Starting mysqld daemon with databases from /mysql/mysql3306/data
mysqld_multi log file version 2.16; run: Wed Oct  4 17:35:37 2023
 
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is not running
mysqld_multi log file version 2.16; run: Wed Oct  4 17:35:59 2023
 
Starting MySQL servers
 
2023-10-04T09:36:00.302580Z mysqld_safe Logging to '/mysql/mysql3307/data/error.log'.
2023-10-04T09:36:00.354252Z mysqld_safe Starting mysqld daemon with databases from /mysql/mysql3307/data
mysqld_multi log file version 2.16; run: Wed Oct  4 17:36:12 2023
 
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
 
Stopping MySQL servers
 
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
2023-10-04T10:13:55.862406Z mysqld_safe mysqld from pid file /mysql/mysql3307/data/redhat762100.pid ended
2023-10-04T10:14:05.648488Z mysqld_safe mysqld from pid file /mysql/mysql3306/data/redhat762100.pid ended
mysqld_multi log file version 2.16; run: Wed Oct  4 18:14:09 2023
 
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
MySQL server from group: mysqld3307 is not running
mysqld_multi log file version 2.16; run: Wed Oct  4 18:56:42 2023

###测试，使用!include的方法，包含/mysql/mysql3306/my.cnf和/mysql/mysql3307/my.cnf的方法，是否可以（测试结果，不可以）

-- 使用下面的这种写法 ，这种写法 ，无论是mysqld_multi start 3307 还是mysqld_multi start 3307 ，都会发现mysqld_multi report后，都是启动的，但实际上只启动了3306
 

[root@redhat762100 etc]# more my.cnf
[mysqld_multi]
mysqld     = /mysql/mysql3306/bin/mysqld_safe
mysqladmin = /mysql/mysql3306/bin/mysqladmin
log=/mysql/mysqld_multi.log
user       = root
#password   = mysql
pass       = mysql
 
[mysqld3306]
#socket     = /tmp/mysql3306.sock
#socket     = /mysql/mysql3306.sock
#port       = 3306
pid-file   = /mysql/mysql3306/data/redhat762100.pid
#datadir    = /mysql/mysql3306/data
language   = /mysql/mysql3306/share/english
user       = root
 
!include /mysql/mysql3306/my.cnf
 
[mysqld3307]
#socket     = /tmp/mysql3307.sock
#socket     = /mysql/mysql3307.sock
#port       = 3307
pid-file   = /mysql/mysql3307/data/redhat762100.pid
#datadir    = /mysql/mysql3307/data
language   = /mysql/mysql3307/share/english
user       = root
 
 
!include /mysql/mysql3307/my.cnf
[root@redhat762100 etc]#

-- 查看进程，实际上只启动了3306 ，并没有3307的socket 和data 

[root@redhat762100 mysql3306]# ps -ef | grep 3307
root     106939      1  0 19:14 pts/2    00:00:00 /bin/sh /mysql/mysql3306/bin/mysqld_safe --pid-file=/mysql/mysql3307/data/redhat762100.pid --language=/mysql/mysql3307/share/english --user=root
root     107903 106939  3 19:14 pts/2    00:00:00 /mysql/mysql3306/bin/mysqld --basedir=/mysql/mysql3306 --datadir=/mysql/mysql3306/data --plugin-dir=/mysql/mysql3306/lib/plugin --user=root --language=/mysql/mysql3307/share/english --log-error=/mysql/mysql3306/data/error.log --pid-file=/mysql/mysql3307/data/redhat762100.pid --socket=/mysql/mysql3306.sock --port=3306
root     107935  59782  0 19:15 pts/1    00:00:00 grep --color=auto 3307
[root@redhat762100 mysql3306]# ps -ef | grep 3306
root      87838  87820  0 17:26 pts/0    00:00:00 tail -f /mysql/mysql3306/data/error.log
root     105651      1  0 19:09 pts/2    00:00:00 /bin/sh /mysql/mysql3306/bin/mysqld_safe --pid-file=/mysql/mysql3306/data/redhat762100.pid --language=/mysql/mysql3306/share/english --user=root
root     106615 105651  0 19:09 pts/2    00:00:00 /mysql/mysql3306/bin/mysqld --basedir=/mysql/mysql3306 --datadir=/mysql/mysql3306/data --plugin-dir=/mysql/mysql3306/lib/plugin --user=root --language=/mysql/mysql3306/share/english --log-error=/mysql/mysql3306/data/error.log --pid-file=/mysql/mysql3306/data/redhat762100.pid --socket=/mysql/mysql3306.sock --port=3306
root     106939      1  0 19:14 pts/2    00:00:00 /bin/sh /mysql/mysql3306/bin/mysqld_safe --pid-file=/mysql/mysql3307/data/redhat762100.pid --language=/mysql/mysql3307/share/english --user=root
root     107903 106939  2 19:14 pts/2    00:00:00 /mysql/mysql3306/bin/mysqld --basedir=/mysql/mysql3306 --datadir=/mysql/mysql3306/data --plugin-dir=/mysql/mysql3306/lib/plugin --user=root --language=/mysql/mysql3307/share/english --log-error=/mysql/mysql3306/data/error.log --pid-file=/mysql/mysql3307/data/redhat762100.pid --socket=/mysql/mysql3306.sock --port=3306
root     107940  59782  0 19:15 pts/1    00:00:00 grep --color=auto 3306
[root@redhat762100 mysql3306]#

--连接到mysql，只能连接到3306 ，无法连接到3307 

[root@redhat762100 mysql3306]# mysql -uroot -h127.0.0.1 -p -P3307
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)
[root@redhat762100 mysql3306]# mysql -uroot -h127.0.0.1 -p -P3306
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.25-log MySQL Community Server (GPL)
 
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
root@db 19:18:  [(none)]>

-- 当把/etc/my.cnf的参数中，include去掉，把3306和3307的参数，分别写在[mysqld3306]和[mysqld3307]下的时候，再次查看mysqld_multi report，发现起来的库是3306
再次印证了include的写法不成立 

[root@redhat762100 etc]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is not running
[root@redhat762100 etc]#

碰到的问题 ：
问题1 
root@localhost  无法访问 。
ALTER USER 'root'@'localhost' IDENTIFIED   BY  'mysql'

问题2 
mysqld_multi start  可以启动
mysqld_multi stop无法关闭，提示如下：

Stopping MySQL servers

mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'
mysqld_multi log file version 2.16; run: Wed Oct  4 18:06:32 2023

解决方法1  
mysqld_multi stop 3306 --password=mysql 

解决方法2  
[mysqld_multi] 中，是pass，而不是password  ，mysql 8.0是password  

end