Submitted by rsecor on Fri, 10/23/2015 - 07:46
"Backup All Databases" causes messages with: "mysqlshow: [Warning] Using a password on the command line interface can be insecure."
Status:
Closed (fixed)
Web Hosting and Cloud Computing Control Panels
Comments
Submitted by rsecor on Fri, 10/23/2015 - 07:53 Comment #1
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid_mode'': Table 'performance_schema.session_variables' doesn't exist (1146)
Submitted by rsecor on Fri, 10/23/2015 - 08:03 Comment #2
As per http://blog.teamextension.com/mysqldump-couldnt-execute-select-gtid_mode...
"The solution is to add –set-gtid-purged=OFF in the mysqldump command. It should look something like 1 mysqldump -h dbHost -u dbuser dbName --set-gtid-purged=OFF"
^ This did not work (FYI).
Submitted by rsecor on Fri, 10/23/2015 - 08:33 Comment #3
Answer: When upgrading from 5.6 to 5.7 remember to run:
mysql_upgrade
Submitted by andreychek on Fri, 10/23/2015 - 10:18 Comment #4
Howdy -- we're glad you got that working now, thanks for sharing your solution!
Submitted by rsecor on Mon, 10/26/2015 - 07:38 Comment #5
The good news is that this change fixed the bad backups that were occurring.
The bad news is that we get an E-Mail on every dumped database with: "mysqlshow: [Warning] Using a password on the command line interface can be insecure."
As per "6.1.2.1 End-User Guidelines for Password Security" of the "MySQL 5.7 Reference Manual" (Located at: http://dev.mysql.com/doc/refman/5.7/en/password-security-user.html )
The username/password should be kept in a configuration file instead of put on the command-line (where it can be seen via a process check).
To change this it will likely be necessary to: (1) Have the username/password/etc be saved into a config file. (2) Have any MySQL related commands running to utilize that config file.
Submitted by rsecor on Mon, 10/26/2015 - 08:11 Comment #6
Just noticed that it not only backs up all the databases... but since it believes there was an error (from the password being on the command-line) it decides to start back up again. It just keeps backing up all the databases over and over again.
Submitted by JamieCameron on Mon, 10/26/2015 - 19:58 Comment #7
Webmin / Virtualmin will actually use an environment variable to pass in the password in almost all cases. The only exception is if the
rootuser has a.my.cnffile containing apasswordline, which cannot be overridden by an environment variable.Submitted by rsecor on Tue, 10/27/2015 - 08:36 Comment #8
NOTE: Some of the information was changed for security.
sh -c (/usr/bin/mysqldump -u root --password=PASSWORD --add-drop-table --single-transaction --quick --quote-names --routines DATABASE --events 2>&1 | bzip2 -c >\/backup\/2015102712\/DOMAIN.sql.bz2) 2>&1
^ This is what I am seeing.
I tried it in various combinations based on: (1) with and without a ".my.cnf" file under ~root (2) "Use MYSQL_PWD variable to pass password" set to Yes or No (3) "Administration Login" empty, set to root, or set to root manually in the config file (4) "Administration Password" empty, and set to the root database password
What am I missing?
Submitted by JamieCameron on Tue, 10/27/2015 - 23:04 Comment #9
Make sure that the user whose domain is being backed up also has no
.my.cnffile in the home directory.Submitted by rsecor on Wed, 10/28/2015 - 08:16 Comment #10
We did not try putting .my.cnf files in each domain home directory, yet, as everything seemed ok overnight.
We will monitor it and update if anything else happens.
If everything is ok we will close this ticket/report after about 3 days, if that is ok with everyone else.
Submitted by JamieCameron on Wed, 10/28/2015 - 17:45 Comment #11
Ok
Submitted by rsecor on Fri, 10/30/2015 - 10:38 Comment #12
All looks ok still. Thanks for the help.