Solve the problem of "Using a password on the command line interface..." in MySQL 5.6

Today, Lao Zuo used to use the mysqldump command when he was helping a netizen move to a website, but something unexpected happened“ Warning: Using a password on the command line interface can be insecure. "Of course, the database cannot be backed up. This problem should have occurred in MySQL 5.6+, which may be the protection mechanism used to ensure the security of the database.

If you encounter problems, you can solve them. Most of the websites you search in China are copied, but you can't explain them clearly. So you still look for foreigners' information. So Lao Zuo sorted out the following comprehensive methods and they are proved to be no problem.

The first method is to modify the database configuration file

1. We need to modify the database configuration file. This depends on our database configuration. Some are in/etc/my.cnf, and some are in/etc/my.conf

We need to add script in the [client] section:

host=localhost
User=database user
Password='Database password '

Here, the parameters should be modified to our own.

2. Export and import databases with commands

In fact, if we adopt“ Explain how to use the mysqldump command to backup and restore MySQL data "The methods described can also be used. Although there are still error prompts, the database can still be exported. You must be a detail seeker like Lao Zuo, and no problems can be found. But we can use the following commands to export and import without error prompts.

#Export Database

mysqldump --defaults-extra-file= /etc/my.cnf database > database.sql

#Import Database

mysql --defaults-extra-file= /etc/my.cnf database < database.sql

Here we can see that the above command is different from the previous fast import and import commands. We need to load our configured MYSQL configuration file. The red part should be modified according to our actual path. There is no error prompt for exporting backups and importing with such commands.

The second method is to use mysql_config_editor

1. Set encryption mode

mysql_config_editor set --login-path=local --host=localhost --user= db_user --password

The red part needs to be changed to our own database user name. After entering, we will be prompted to enter the database password, and we will continue to enter it.

2. Perform Backup

mysqldump -u db_user -p InsecurePassword my_database | gzip > backup.tar.gz

According to our data information, modify the user name and database password, and perform the backup. Laozuo test still has an error prompt, but the database can be backed up.

In short, as long as we implement the results, we can choose one of the above two methods. Of course, Lao Zuo has seen other methods, so we won't share them here. If you are interested, you can search for other solutions.