Technology Sharing | MySQL Avoiding Annoying Warnings When Scripting

Author: Yang Taotao

Senior database expert, specializing in MySQL research for more than ten years. Good at MySQL, PostgreSQL, MongoDB and other open source databases related to backup and recovery, SQL tuning, monitoring operation and maintenance, high-availability architecture design, etc. Currently working in Aikesheng, providing MySQL-related technical support and MySQL-related course training for major operators and banking and financial enterprises.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

When a customer wrote the early database security specification, let us help to provide a detailed description document on how to operate MySQL on the Linux Shell side more safely. One of them is how to block the annoying warning information output when calling various MySQL command line tools under Linux Shell, such as the following:

 root@ytt-ubuntu18:/home/ytt# mysql -uytt -proot -e "select version()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| version() |
+-----------+
| 8.0.29    |
+-----------+

Actually this is a very old question! Just search on Baidu, there are various solutions, but they are not very perfect.

Such alarm information is very unfriendly to the output of command execution results, so how do we block it? Below I will list a few methods I can think of for reference.

1. Give the user an empty password (not recommended)

Giving a user an empty password can block the warning message, but it is extremely insecure, similar to the --initialize-insecure option when the MySQL service is initialized.

 root@ytt-ubuntu18:/home/ytt# mysql -u ytt_no_pass -e "select user()"
+-----------------------+
| user()                |
+-----------------------+
| ytt_no_pass@localhost |
+-----------------------+

2. Add username and password to different blocks of configuration file (not recommended)

MySQL configuration files include my.cnf, mysql.cnf, mysqld.cnf, etc. Just add the corresponding user name and password under different blocks in these configuration files.

 root@ytt-ubuntu18:/home/ytt# cat /etc/mysql/conf.d/mysql.cnf
[mysql]
prompt=mysql:\d:\v>
user=ytt
password=root
port=3340
[mysqldump]
user=ytt
password=root
port=3340
   
[mysqladmin]
user=ytt
password=root
port=3340

The content under the [mysql] block above means that it is valid for the mysql command line, the content under the [mysqldump] block means that it is valid for the mysqldump tool, and the content under the [mysqladmin] block means that it is valid for the mysqladmin tool.

Or write it simply and add it to [client] , which means it will take effect on all clients. Note that only shared parts can be added here.

 root@ytt-ubuntu18:/home/ytt# cat /etc/mysql/conf.d/mysql.cnf
[mysql]
prompt=mysql:\d:\v>
[client]
user=ytt
password=root
port=3340

Since these blocks are set for the client, there is no need to restart the MySQL service, and they will take effect immediately.

 root@ytt-ubuntu18:/home/ytt# mysql -e "select user()"
+---------------+
| user()        |
+---------------+
| ytt@localhost |
+---------------+

3. Set MySQL environment variables (not recommended)

MySQL has some built-in environment variables that take effect for all clients. The official list of environment variables is as follows:

https://dev.mysql.com/doc/refman/8.0/en/environment-variables.html

Set the required environment variables for the current user, and then call the command line tool. For example, set the password environment variable MYSQL_PWD, the traditional TCP port environment variable MYSQL_TCP_PORT and so on.

 root@ytt-ubuntu18:/home/ytt# export MYSQL_PWD=root MYSQL_TCP_PORT=3340 
   
root@ytt-ubuntu18:/home/ytt# mysql -uytt -e "select user()"
+---------------+
| user()        |
+---------------+
| ytt@localhost |
+---------------+

This method is also not recommended, the environment variable MYSQL_PWD is easily obtained by other users. For example, the value of MYSQL_PWD can be easily obtained by using the ps command directly.

User 1 executes the following command:

 root@ytt-ubuntu18:/home/ytt# mysql -uytt -e "select sleep(1000)"

User 2 can print out the values of environment variables MYSQL_PWD and MYSQL_TCP_PORT by executing ps aex:

 root@ytt-ubuntu18:/home/ytt# ps aex| grep MYSQL_PWD| grep -v 'grep'
7592 pts/0    S+     0:00 mysql -uytt -e select sleep(1000) LS_COLORS=rs=0:... MYSQL_PWD=root ...MYSQL_TCP_PORT=3340 ...

4. Shield standard error output and redirect to an empty device file (recommended)

 root@ytt-ubuntu18:/home/ytt# mysql -uytt -proot -P3340 -e"select version()"  2>/dev/null
+-----------+
| version() |
+-----------+
| 8.0.29    |
+-----------+

This uses the characteristics of the Linux system itself to redirect the MySQL error message, where the number 2 represents the file descriptor of the error output; /dev/null represents an empty device. That is to say, the error message of executing this command is redirected to the empty device instead of the standard output, so that the warning message can be masked in disguise.

5. Use the mysql_config_edit tool to generate different login_paths (recommended)

mysql_config_edit is a tool officially released by MySQL, which is specially designed to deal with such problems that user passwords must be exposed. It can be set once and used safely for many times.

The usage is as follows: set a login_path, the name is user_ytt, and the password can be entered as prompted.

 root@ytt-ubuntu18:/home/ytt# mysql_config_editor set -G user_ytt -S /var/run/mysqld/mysqld.sock -u ytt -p
Enter password:

Next, invoke any MySQL command-line tool with the --login-path option.

 root@ytt-ubuntu18:/home/ytt# mysql --login-path=user_ytt -e 'select user()'
+---------------+
| user()        |
+---------------+
| ytt@localhost |
+---------------+
   
root@ytt-ubuntu18:/home/ytt# mysqladmin  --login-path=user_ytt ping
mysqld is alive

The mysql_config_editor tool also has a disadvantage: the same login_path cannot be shared with all system users, and other users have to re-add their own login_path to use it normally.

6. Use Unix socket plugin (recommended, local only)

The auth_socket plugin only authorizes authentication based on the local OS login username and the local linux socket file. For example, modify the user ytt@localhost plugin to auth_socket:

 mysql> alter user ytt@localhost identified with auth_socket ;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> \q
   Bye

Switch to OS user ytt:

 root@ytt-pc-big:/home/ytt# su ytt
   
   ytt@ytt-pc-big:~$ mysql -e "select user(),current_user()"
   +---------------+----------------+
   | user()        | current_user() |
   +---------------+----------------+
   | ytt@localhost | ytt@localhost  |
   +---------------+----------------+

A word of caution is needed here: For security, user permissions for operating MySQL must be assigned as needed.