MySQL基础:配置文件的优先顺序

在这里插入图片描述
MySQL中有多个位置可以配置my.cnf来存放配置内容,这篇文章以具体的例子来说明不同位置的配置文件的优先顺序。

环境准备

环境准备可参看:

注:已有MySQL的可以跳过此步骤。

liumiaocn:~ liumiao$ sw_vers
ProductName:	Mac OS X
ProductVersion:	10.14
BuildVersion:	18A391
liumiaocn:~ liumiao$ 
liumiaocn:~ liumiao$ mysql --version
mysql  Ver 8.0.11 for osx10.13 on x86_64 (Homebrew)
liumiaocn:~ liumiao$ 

MySQL的配置文件

序号 目录 文件名 优先顺序说明
1 /usr/local/mysql/ my.cnf 优先度最低(验证发现无法起效)
2 /etc/ my.cnf 优先度比1高
3 /etc/mysql/ my.cnf 优先度比2高
4 ~/ .my.cnf 优先度比3高
5 指定目录 指定文件名称 defalts-file选项指定名称,优先度比4高
6 - -命令行参数方式传入 优先度比5高

事前准备

将上述1-4的配置文件中的用户名全部设定为root,密码全部设定错误,不输入用户名和密码方式的情况下无法登录到mysql控制台。

liumiaocn:~ liumiao$ for f in /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf; do echo "[$f]"; cat $f; echo; done
[/etc/my.cnf]
[client]
user=root
password=root123

[/etc/mysql/my.cnf]
[client]
user=root
password=root123

[/usr/local/mysql/etc/my.cnf]
[client]
user=root
password=root123

[/Users/liumiao/.my.cnf]
[client]
user=root
password=root123

liumiaocn:~ liumiao$ mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
liumiaocn:~ liumiao$

确认1: 命令行参数传入

在1-4的输入全部出错的情况下,命令行方式仍然能够正确登录,说明会优先确认命令行的参数。所以命令行参数的优先度高于1-4

liumiaocn:~ liumiao$ mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 8.0.11 Homebrew

Copyright (c) 2000, 2018, 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.

mysql> 

确认2: defaults-file方式的优先度

使用如下示例可以看到,defaults-file中使用的错误的用户名和密码的情况下,只要-u和-p的内容正确,就会登录,说明defaults-file的优先度低于直接使用参数命令行的方式。

liumiaocn:~ liumiao$ cat /tmp/my.cnf
[client]
user=root
password=root123
liumiaocn:~ liumiao$ mysql --defaults-file=/tmp/my.cnf -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 8.0.11 Homebrew

Copyright (c) 2000, 2018, 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.

mysql> 

在1-4的用户名/密码均不正确的情况下,defaults-file指定的内容正确即可登录,说明defaults-file方式的优先度高于1-4。

liumiaocn:~ liumiao$ cat /tmp/my.cnf
[client]
user=root
password=root
liumiaocn:~ liumiao$ mysql --defaults-file=/tmp/my.cnf
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 8.0.11 Homebrew

Copyright (c) 2000, 2018, 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.

mysql> 

确认3: ~/.my.cnf的优先度

1-3的内容均为错误,将~/.my.cnf内容设定正确,能够登录说明其他错误内容均未被使用,所以此配置文件的优先度高于其他三个。

liumiaocn:~ liumiao$ for f in /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf; do echo "[$f]"; cat $f; echo; done
[/etc/my.cnf]
[client]
user=root
password=root123

[/etc/mysql/my.cnf]
[client]
user=root
password=root123

[/usr/local/mysql/etc/my.cnf]
[client]
user=root
password=root123

[/Users/liumiao/.my.cnf]
[client]
user=root
password=root

liumiaocn:~ liumiao$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 8.0.11 Homebrew

Copyright (c) 2000, 2018, 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.

mysql> 

确认4: /etc/mysql/my.cnf的优先度

1-2的内容均为错误,将~/.my.cnf内容注释掉,仅仅将/etc/mysql/my.cnf设定正确,能够登录说明其他错误内容均未被使用,所以此配置文件的优先度高于其他两个。

liumiaocn:~ liumiao$ for f in /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf; do echo "[$f]"; cat $f; echo; done
[/etc/my.cnf]
[client]
user=root
password=root123

[/etc/mysql/my.cnf]
[client]
user=root
password=root

[/usr/local/mysql/etc/my.cnf]
[client]
user=root
password=root123

[/Users/liumiao/.my.cnf]
#[client]
#user=root
#password=root

liumiaocn:~ liumiao$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 8.0.11 Homebrew

Copyright (c) 2000, 2018, 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.

mysql> 

确认5: /etc/my.cnf的优先度

/usr/local/mysql/etc/my.cnf的内容设定为错误信息,将3-4的内容注释掉,仅仅将/etc/my.cnf设定正确,能够登录说明其他错误内容均未被使用,所以此配置文件的优先度高于/usr/local/mysql/etc/my.cnf。

liumiaocn:~ liumiao$ for f in /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf; do echo "[$f]"; cat $f; echo; done
[/etc/my.cnf]
[client]
user=root
password=root

[/etc/mysql/my.cnf]
#[client]
#user=root
#password=root

[/usr/local/mysql/etc/my.cnf]
[client]
user=root
password=root123

[/Users/liumiao/.my.cnf]
#[client]
#user=root
#password=root

liumiaocn:~ liumiao$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 8.0.11 Homebrew

Copyright (c) 2000, 2018, 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.

mysql> 

确认6: /usr/local/mysql/etc/my.cnf的确认

仅仅正确设定/usr/local/mysql/etc/my.cnf的内容设,将2-4的内容注释掉,发现无法正常登录。

liumiaocn:~ liumiao$ for f in /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf; do echo "[$f]"; cat $f; echo; done
[/etc/my.cnf]
#[client]
#user=root
#password=root

[/etc/mysql/my.cnf]
#[client]
#user=root
#password=root

[/usr/local/mysql/etc/my.cnf]
[client]
user=root
password=root

[/Users/liumiao/.my.cnf]
#[client]
#user=root
#password=root

liumiaocn:~ liumiao$ mysql
ERROR 1045 (28000): Access denied for user 'liumiao'@'localhost' (using password: NO)
liumiaocn:~ liumiao$ 

在验证中发现,似乎无法正常起效。本文使用的环境为确认macOS上使用brew isntall mysql方式所安装,未做特定设置,基本应该为缺省设定。

发布了920 篇原创文章 · 获赞 1260 · 访问量 389万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 数字20 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览