Mysql – Passing passwords more securely to thesql utilities on the command line

command lineMySQLpasswordSecurity

This question is related to another question I subsequently asked here: Using the output from mysqldiff.exe to transform database2 to match database1.

I have been trying to use mysqldiff.exe, part of MySQL Utilities group of tools. The idea is to identify schema differences between two databases, or other contained objects (tables, routines, triggers, etc).

Basically this command:

mysqldiff.exe --server1=root:password@localhost:3306 
--server2=root:password@localhost:3306 db1:db2

seems to work, though I'm yet to fully grasp how to use this program's output for the purpose of transforming the one db to match the other! I guess I might have to ask that question differently further down the line.

My immediate issue is:

This utility screams: # WARNING: Using a password on the command line interface can be insecure., yet I am unable to find a means to avoid spilling out my passwords out there! There is no such option from the available help.

With regular MySQL binaries (mysqld.exe, mysql, mysqladmin, etc), I am able to use --login-path=xxxx, having configured the credentials using mysql_config_editor.exe. With Mysql Utilities, how can I solve this issue?

PS: I realize that my question's tags are a bit imprecise, but I stuff like "mysql-utilities" and "mysqldiff" are not available!

Best Answer

You can put the password in the my.cnf (or my.ini for Windows) and then login without specifying the password "over the wire". Obviously you want to keep that file secure, but in any decent setup, this should not be a problem.

LOGGING IN

[pol@localhost inst]$ ./bin/mysql --defaults-file=./my.cnf -S ./mysql.sock -u root  -- **NOTE: no password is needed here.**
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.22-log Source distribution

Copyright (c) 2000, 2014, 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>

CONTENTS OF MY.INI OR MY.CNF

[client]
port        = 3306
socket      = /home/pol/Downloads/software/mysql/5.6/inst/mysqld.sock
host            = 127.0.0.1
user        = pol 
password    = dba   --  **<<-----NOTE password in client section**

PASSWORD NOT VISIBLE

mysql> \! ps -ef |  grep mysql
pol       2173  2122  0 Apr19 pts/0    00:00:19 ./bin/mysqld --defaults-file=./my.cnf
pol       9294  2197  0 00:09 pts/1    00:00:00 ./bin/mysql --defaults-file=./my.cnf -S ./mysql.sock -u root   **NOTE - no password visible "over the wire"
pol       9360  9294  0 00:16 pts/1    00:00:00 sh -c  ps -ef |  grep mysql
pol       9362  9360  0 00:16 pts/1    00:00:00 grep mysql

Related Solutions

MySql command line cyrillic Iinsert result is corrupted

By default MySQL server is using latin1 character set for each incoming connection. Latin1, as you might know, does not support cyrillic symbols.

The simplest solution is to switch so called 'connection character set' by running SET NAMES 'utf8'; in the beginning of each connection.

For example, this query should work:

SET NAMES 'utf8';

CREATE TABLE `category` (
  `catName` varchar(40) NOT NULL DEFAULT '',
  PRIMARY KEY (`catName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into category(catName) values ('привет русско-говорящему серверу');

To learn about character sets in MySQL and how to make the above change permament see here.

Mysql – Test MySQL credentials from Linux command line

@Phil's Answer and @Mr.Brownstone's Answer should suffice for your question, so +1 for both of them.

For the following, let's assume you are logging in with username myuser

Once you have connected to mysql, you should run the following query:

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate in MySQL
CURRENT_USER() reports how you were allowed to authenticate in MySQL

Sometimes, they are different. This may give you insight into why you are allowed to login to mysql.

Here is another query you need to run:

SELECT CONCAT('''',user,'''@''',host,'''') dbuser,password
FROM mysql.user WHERE user='myuser';

This will show you the ways in which you are allowed to login as myuser.

If you see 'myuser'@'localhost', then you can authenticate from within the DB Server.

If you see 'myuser'@'127.0.0.1' and do not see 'myuser'@'localhost', then you can authenticate from within the DB Server again but you must specify the --protocol=tcp from the command line.

If you see 'myuser'@'%', then you can do remote logins from any server.

If you see 'myuse'r@'10.20.30,%', then you can do remote logins only the from 10.20.30.% netblock.

Once you see what 'mysql.user' has for your user, you may want to allow or restrict myuser from logggin in one way and not the other.

If you simply want to check if the password for myuser is whateverpassword, you can do the following:

SELECT COUNT(1) Password_is_OK FROM mysql.user
WHERE user='myuser'
AND password=PASSWORD('whateverpassword');

You can check from the command line as follows:

PASSWORDISOK=`mysql -uroot -p... -ANe"SELECT COUNT(1) Password_is_OK FROM mysql.user WHERE user='myuser' AND password=PASSWORD('whateverpassword')"`

If you are not root and want to test myuser only, you can do this:

PASSWORDISOK=`mysqladmin -umyuser -pwhateverpassword ping | grep -c "mysqld is alive"`

If you get 1, password for myuser is verified as good.

Best Answer

Related Solutions

MySql command line cyrillic Iinsert result is corrupted

Mysql – Test MySQL credentials from Linux command line

Related Question