|
MySQL中有多個位置可以配置my.cnf來存放配置內容，這篇文章以具體的例子來說明不同位置的配置文件的優(yōu)先順序,。
環(huán)境準備
環(huán)境準備可參看：
注：已有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的配置文件
| 序號 | 目錄 | 文件名 | 優(yōu)先順序說明 |
|---|
| 1 | /usr/local/mysql/ | my.cnf | 優(yōu)先度最低（驗證發(fā)現無法起效） | | 2 | /etc/ | my.cnf | 優(yōu)先度比1高 | | 3 | /etc/mysql/ | my.cnf | 優(yōu)先度比2高 | | 4 | ~/ | .my.cnf | 優(yōu)先度比3高 | | 5 | 指定目錄 | 指定文件名稱 | defalts-file選項指定名稱，優(yōu)先度比4高 | | 6 | - | -命令行參數方式傳入 | 優(yōu)先度比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的輸入全部出錯的情況下,，命令行方式仍然能夠正確登錄,，說明會優(yōu)先確認命令行的參數。所以命令行參數的優(yōu)先度高于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方式的優(yōu)先度
使用如下示例可以看到,，defaults-file中使用的錯誤的用戶名和密碼的情況下,，只要-u和-p的內容正確，就會登錄,，說明defaults-file的優(yōu)先度低于直接使用參數命令行的方式,。
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方式的優(yōu)先度高于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的優(yōu)先度
1-3的內容均為錯誤，將~/.my.cnf內容設定正確,，能夠登錄說明其他錯誤內容均未被使用,，所以此配置文件的優(yōu)先度高于其他三個。
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的優(yōu)先度
1-2的內容均為錯誤,，將~/.my.cnf內容注釋掉,，僅僅將/etc/mysql/my.cnf設定正確，能夠登錄說明其他錯誤內容均未被使用,，所以此配置文件的優(yōu)先度高于其他兩個,。
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的優(yōu)先度
/usr/local/mysql/etc/my.cnf的內容設定為錯誤信息，將3-4的內容注釋掉,，僅僅將/etc/my.cnf設定正確,，能夠登錄說明其他錯誤內容均未被使用，所以此配置文件的優(yōu)先度高于/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的內容注釋掉，發(fā)現無法正常登錄。
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$
在驗證中發(fā)現,，似乎無法正常起效,。本文使用的環(huán)境為確認macOS上使用brew isntall mysql方式所安裝，未做特定設置,，基本應該為缺省設定,。
|
