mysqld_multi测试

这篇具有很好参考价值的文章主要介绍了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 文章来源地址https://www.toymoban.com/news/detail-727989.html

到了这里,关于mysqld_multi测试的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包