在mysql运维过程中,我们经常需要指定mysql、mysqldump等命令的主机、端口、用户名、密码等参数,每次这样指定有些繁琐,有没有办法基于配置文件读取这些信息呢?其实,这个问题,mysql官方已经帮我们想到了,我们只要基于mysql_config_editor去为特定的环境配置即可。接下来,我们举一个例子给大家。
例:基于用户名root、密码***、主机127.0.0.1、端口号3306去访问mysql服务器。
lwk@qwfys:~$ mysql_config_editor set --login-path=client --host=127.0.0.1 --user=root --password --port=3306
Enter password:
lwk@qwfys:~$
已经配置好,我们可以试一下。
lwk@qwfys:~$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.28 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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> exit
Bye
lwk@qwfys:~$
可见是没有问题的,我们查看一下刚才的配置
lwk@qwfys:~$ mysql_config_editor print --all
[client]
user = root
password = *****
host = 127.0.0.1
port = 3306
lwk@qwfys:~$
另外,该工具会在~/目录中生成一个名为.mylogin.cnf的二进制文件。我们可以用如下命令检测到。
lwk@qwfys:~$ ls -la ~/.mylogin.cnf
-rw------- 1 lwk lwk 136 Oct 29 10:24 /home/lwk/.mylogin.cnf
lwk@qwfys:~$
是不是很方便呢?
到这里,很多小伙伴们肯定会说,这有什么呀,如果我有多个mysql服务器实例要连接的时候,你也能解决吗?其实,这个问题,mysql官方也帮我们想到了,刚才我们在创建配置文件的时候用到了参数--login-path=client
指定了一个client,那么这个client是什么呢,我个人的理解是连接配置的别名,如果有多个mysql服务器要连接的时候,我们只要取不同的别名即可。
那么,怎么用呢?其实,很简单,在使用mysql命令行命令的时候,我们只要将已经配置好的别名告诉mysql命令行即可。
lwk@qwfys:~$ mysql --login-path=client
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.28 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>
假如,我们在配置的时候别名叫做abc,这里给mysql命令行的参数值也为abc即可。是不是很简单呢?
接下来,我们简要看一下mysql_config_editor的帮助文档。
- 命令概览
lwk@qwfys:~$ mysql_config_editor --help
mysql_config_editor Ver 1.0 Distrib 5.7.32, for Linux on x86_64
Copyright (c) 2012, 2020, 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.
MySQL Configuration Utility.
Usage: mysql_config_editor [program options] [command [command options]]
-#, --debug[=#] This is a non-debug version. Catch this and exit.
-?, --help Display this help and exit.
-v, --verbose Write more information.
-V, --version Output version information and exit.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
verbose FALSE
Where command can be any one of the following :
set [command options] Sets user name/password/host name/socket/port
for a given login path (section).
remove [command options] Remove a login path from the login file.
print [command options] Print all the options for a specified
login path.
reset [command options] Deletes the contents of the login file.
help Display this usage/help information.
lwk@qwfys:~$
- set
lwk@qwfys:~$ mysql_config_editor set --help
mysql_config_editor Ver 1.0 Distrib 5.7.32, for Linux on x86_64
Copyright (c) 2012, 2020, 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.
MySQL Configuration Utility.
Description: Write a login path to the login file.
Usage: mysql_config_editor [program options] [set [command options]]
-?, --help Display this help and exit.
-h, --host=name Host name to be entered into the login file.
-G, --login-path=name
Name of the login path to use in the login file. (Default
: client)
-p, --password Prompt for password to be entered into the login file.
-u, --user=name User name to be entered into the login file.
-S, --socket=name Socket path to be entered into login file.
-P, --port=name Port number to be entered into login file.
-w, --warn Warn and ask for confirmation if set command attempts to
overwrite an existing login path (enabled by default).
(Defaults to on; use --skip-warn to disable.)
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
host (No default value)
login-path client
user (No default value)
socket (No default value)
port (No default value)
warn TRUE
lwk@qwfys:~$
lwk@qwfys:~$ mysql_config_editor print --help
mysql_config_editor Ver 1.0 Distrib 5.7.32, for Linux on x86_64
Copyright (c) 2012, 2020, 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.
MySQL Configuration Utility.
Description: Print the contents of login file in unencrypted form.
Usage: mysql_config_editor [program options] [print [command options]]
--all Used with print command to print all login paths.
-?, --help Display this help and exit.
-G, --login-path=name
Name of the login path to use in the login file. (Default
: client)
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
all FALSE
login-path client
lwk@qwfys:~$
- reset
lwk@qwfys:~$ mysql_config_editor reset --help
mysql_config_editor Ver 1.0 Distrib 5.7.32, for Linux on x86_64
Copyright (c) 2012, 2020, 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.
MySQL Configuration Utility.
Description: Empty the contents of the login file. The file is created
if it does not exist.
Usage: mysql_config_editor [program options] [reset [command options]]
-?, --help Display this help and exit.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
lwk@qwfys:~$
- remove
lwk@qwfys:~$ mysql_config_editor remove --help
mysql_config_editor Ver 1.0 Distrib 5.7.32, for Linux on x86_64
Copyright (c) 2012, 2020, 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.
MySQL Configuration Utility.
Description: Remove a login path from the login file.
Usage: mysql_config_editor [program options] [remove [command options]]
-?, --help Display this help and exit.
-h, --host Remove host name from the login path.
-G, --login-path=name
Name of the login path from which options to be removed
(entire path would be removed if none of user, password,
host, socket, or port options are specified). (Default :
client)
-p, --password Remove password from the login path.
-u, --user Remove user name from the login path.
-w, --warn Warn and ask for confirmation if remove command attempts
to remove the default login path (client) if no login
path is specified (enabled by default).
(Defaults to on; use --skip-warn to disable.)
-S, --socket Remove socket path from the login path.
-P, --port Remove port number from the login path.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
host FALSE
login-path client
password FALSE
user FALSE
warn TRUE
socket FALSE
port FALSE
lwk@qwfys:~$
参考文献