首页 » MySQL » 如何在mysql脚本中不使用明文密码?

如何在mysql脚本中不使用明文密码?

之前在如何在Oracle 19c expdp/impdp 脚本中不使用密码? 一文记录过在Oracle备份的shell脚本中不配置明文密码,MySQL同样可以使用mysql_config_editor工具生成存储加密登陆的方式,使用的模糊处理可防止密码以明文形式显示,并提供 通过防止无意中泄露密码来衡量安全性。登录路径文件必须可读和可写到当前 用户,其他用户无法访问。否则,mysql_config_editor忽略它,并且客户端 程序也不使用它。

mysql_config_editor如何工作?
该实用程序是MySQL安装中包含的一个小程序,用于管理连接到不同MySQL服务器或不同帐户的凭据。它加密凭据信息并将其存储在主目录中调用的文件中。在Linux平台~/.mylogin.cnf。
描述如何登录MySQL帐户的每组凭据称为“登录路径”login-path。这些通常指定帐户的用户名和密码,并且可以另外存储有关如何连接到相应MySQL服务器的相关信息,例如MySQL正在侦听的主机名和端口。MySQL 客户端和工具会自动配置为使用文件中的信息来帮助登录 MySQL 服务器。如果登录路径未定义某些值,MySQL 客户端和工具将改用其配置的默认值。

创建新的登录路径来定义凭据
我们可以通过使用该工具设置新的登录路径来开始。mysql_config_editor

[root@oel7db1 ~]# mysql_config_editor --help
mysql_config_editor  Ver 8.0.20-commercial for Linux on x86_64 (MySQL Enterprise Server - Commercial)
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.

[root@oel7db1 ~]# mysql_config_editor set --login-path=local_root --user=root  --host=localhost --port=3306 --password
Enter password:
[root@oel7db1 ~]# ls -lart

...
-rw-------   1 root   root    72781 Dec 27 07:26 .mysql_history
dr-xr-x---. 14 root   root     4096 Dec 27 07:26 .
-rw-------   1 root   root      156 Dec 27 07:28 .mylogin.cnf

[root@oel7db1 ~]# strings .mylogin.cnf
k,z}

[root@oel7db1 ~]# hexdump .mylogin.cnf
0000000 0000 0000 1902 091a 0e05 0e02 1d10 041b
0000010 1b01 1c13 081c 1d1e 0010 0000 28ba 45f0
0000020 14e9 d5d1 1f71 f806 486d 6be0 0010 0000
0000030 d463 80e7 f241 f7ba 0d9c cd4c d64d 9598
0000040 0020 0000 c56b af32 2c6b 7d7a 2906 0de6
0000050 68e3 9624 8965 5c4d a592 21c5 012e e0e0
0000060 8619 3282 0020 0000 c023 a0a0 4b2a b538
0000070 c594 0636 d203 e8dd 55b6 bc4d c605 cd66
0000080 79b8 9344 d555 d9c7 0010 0000 2cc6 34be
0000090 06ae f598 50df 2da1 ca7c 26e2
000009c
[root@oel7db1 ~]# 

使用登录路径登录

[root@oel7db1 ~]# mysql --login-path=local_root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.20-commercial MySQL Enterprise Server - Commercial

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_root@localhost [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| anbob              |
| dba                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
7 rows in set (0.00 sec)


[root@oel7db1 ~]# mysqldump --login-path=local_root test1 |gzip > test1.backup.gz
[root@oel7db1 ~]# ls -lrt

...
-rw-r--r--   1 root   root      710 Dec 27 07:39 test1.backup.gz
[root@oel7db1 ~]# gunzip test1.backup.gz
[root@oel7db1 ~]# 
[root@oel7db1 ~]# cat test1.backup
-- MySQL dump 10.13  Distrib 8.0.20, for Linux (x86_64)
--
-- Host: localhost    Database: test1
-- ------------------------------------------------------
-- Server version       8.0.20-commercial

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `test1`
--

DROP TABLE IF EXISTS `test1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test1` (
  `id` int NOT NULL,
  `tcol1` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

删除连接信息

您可以使用子命令删除与登录路径关联的连接信息。提供将允许定位适当的条目。mysql_config_editor remove –login-path
例如,要删除登录路径的连接信息,可以键入:local_root

[root@oel7db1 ~]# mysql_config_editor remove --login-path=local_root
[root@oel7db1 ~]# mysql_config_editor print --all
[root@oel7db1 ~]#

打赏

对不起,这篇文章暂时关闭评论。