|
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方式所安装，未做特定设置，基本应该为缺省设定。
|
