donpick
asked on
How to start mysql without putting the password in the Windows batch file
Running MySql version 5.7 on Windows 7 pro. I am not a MySql expert.
Before I posted this question I searched Experts-Exchange for mysql_config_editor. No results were found.
I am trying to use mysql_config_editor . I have read the information in the v5.7 manual . The commands shown in the manual do not work.
First, the manual shows mysql_config_editor running from a shell command. I have found I have to manually start the mysql command line to get mysql_config_editor to run.
The manual says to print the information in mylogin.cnf I should type:
print –all
This does nothing. So I am confused and the MySql manual is not helping me.
Questions:
- I want to start mysql from a Windows batch file . I do not want to put the password in plain text in the batch file. The 5.7 manual says the password can be put into the mylogin.cnf file. May be this is the wrong thing to do. Where should I put the password ?
- I searched all of my drives. I cannot find a mylogin.cnf file. May be I’m trying to edit a nonexistent file. What is the name of this file in a Windows installation?
- In what file would you put the password?
Before I posted this question I searched Experts-Exchange for mysql_config_editor. No results were found.
I am trying to use mysql_config_editor . I have read the information in the v5.7 manual . The commands shown in the manual do not work.
First, the manual shows mysql_config_editor running from a shell command. I have found I have to manually start the mysql command line to get mysql_config_editor to run.
The manual says to print the information in mylogin.cnf I should type:
print –all
This does nothing. So I am confused and the MySql manual is not helping me.
Questions:
- I want to start mysql from a Windows batch file . I do not want to put the password in plain text in the batch file. The 5.7 manual says the password can be put into the mylogin.cnf file. May be this is the wrong thing to do. Where should I put the password ?
- I searched all of my drives. I cannot find a mylogin.cnf file. May be I’m trying to edit a nonexistent file. What is the name of this file in a Windows installation?
- In what file would you put the password?
David Johnson, CD
mysql runs as a service and doesn't need a password.
Adding to Dave's point, you need a password to administer the mysql instance
Where did you get the installer,
in a command window(elevated) run net start |F ind /i "mysql"
Do you receive an entry, Mysql Server?
This will confirm that you have mysql server installed as a service.
presumably the batch file that you are discussing deals with having it connect to the mysql server nad perform some data extraction/addition.
Depending within the batch file how you connect to the mysql server, mysql -u username --password=password databasename
and then pass it what you are...
note this way the password is exposed,.
Where did you get the installer,
in a command window(elevated) run net start |F ind /i "mysql"
Do you receive an entry, Mysql Server?
This will confirm that you have mysql server installed as a service.
presumably the batch file that you are discussing deals with having it connect to the mysql server nad perform some data extraction/addition.
Depending within the batch file how you connect to the mysql server, mysql -u username --password=password databasename
and then pass it what you are...
note this way the password is exposed,.
I agree with the above. I have MySQL running on a dozen machines her and it starts as a service without a password. A password is only need to connect a user to the MySQL server.
Since you mention mylogin.cnf I think you don't talk of the server/service itself, that only needs net start. You want a batch script to establish a connection via login-path as described here, for example:
https://opensourcedbms.com/dbms/passwordless-authentication-using-mysql_config_editor-with-mysql-5-6/
Or as MySQL describes it as a solution to batch file authentication without storing cleartext passwords here:
https://dev.mysql.com/doc/refman/5.7/en/mysql-config-editor.html
It is, however, as mentioned there, only obfuscation.
https://www.percona.com/blog/2016/09/07/get-passwords-plain-text-mylogin-cnf/
OK, that at least needs access to the MySQL installation and usage of my_print_defaults.
The usual security measures you have are:
1. Not storing the password, interactively asking for it.
Likely no option, when something has to run unattended
2. Running the batch on a secured server, thereby shift and solve the problem by the security of that system
3. Establishing a user with limited granted permissions, not needing to protect it, as it may only be able to read what everyone can read anyway. In detail, you can grant permissions on certain columns only or simply only grant permissions on views only querying insensitive columns.
Option 2 is the most common way of arguing with sufficient security. Just remember how any website lets some script make connections. You usually have credentials in a file outside of webroot, which makes that safe against HTTP requests trying to get the credentials file, but not at all safe once you have FTP access or hacked into the server. Once a hacker is there, he can own the MySQL process anyway, use procedures to reset the root password.
If you really need that batch to run on a client machine everyone can access, then Option 3 may be your solution or you go one round deeper, make use of an executable, which in turn makes use of the crypto API, which means not only securely storing a password, that might even go in the direction of using a certificate or even Authentication Plugins. At least for some of them, you'd need the Enterprise edition of MySQL, though.
If you stay with user/password authentication your executable making the connection getting this safely via crypto API will still have the password in memory in cleartext and a final concern then will remain taking a snapshot of the process, if you don't purge the memory used for the password, that'll not even need to make the snapshot at a crucial moment.
C#, for example, has the SecureString class for that matter, which is hardened against even that scenario.
So overall in short: The task to securely authenticate is a topic of its own. I just know the developer perspective, I'm not a security expert, even I'd consult someone about that, if it really is an important concern to you about FIPS compliance, for example.
Usage of a certificate is very safely authenticating a client, but also relies on the secured access of the cert .pem file. In the end you always need something depending on Windows security related to for example, only run with a specific system account, not in the users window session.
If I were you I'd opt for Option 2. Using the obfuscation mylogin.cnf means is optional in my eyes, not really a big advance for secure unattended MySQL usage.
In any database server, you typically define jobs for administrative things on the server-side and not start them from client sides with batch files.
Bye, Olaf.
https://opensourcedbms.com/dbms/passwordless-authentication-using-mysql_config_editor-with-mysql-5-6/
Or as MySQL describes it as a solution to batch file authentication without storing cleartext passwords here:
https://dev.mysql.com/doc/refman/5.7/en/mysql-config-editor.html
It is, however, as mentioned there, only obfuscation.
https://www.percona.com/blog/2016/09/07/get-passwords-plain-text-mylogin-cnf/
OK, that at least needs access to the MySQL installation and usage of my_print_defaults.
The usual security measures you have are:
1. Not storing the password, interactively asking for it.
Likely no option, when something has to run unattended
2. Running the batch on a secured server, thereby shift and solve the problem by the security of that system
3. Establishing a user with limited granted permissions, not needing to protect it, as it may only be able to read what everyone can read anyway. In detail, you can grant permissions on certain columns only or simply only grant permissions on views only querying insensitive columns.
Option 2 is the most common way of arguing with sufficient security. Just remember how any website lets some script make connections. You usually have credentials in a file outside of webroot, which makes that safe against HTTP requests trying to get the credentials file, but not at all safe once you have FTP access or hacked into the server. Once a hacker is there, he can own the MySQL process anyway, use procedures to reset the root password.
If you really need that batch to run on a client machine everyone can access, then Option 3 may be your solution or you go one round deeper, make use of an executable, which in turn makes use of the crypto API, which means not only securely storing a password, that might even go in the direction of using a certificate or even Authentication Plugins. At least for some of them, you'd need the Enterprise edition of MySQL, though.
If you stay with user/password authentication your executable making the connection getting this safely via crypto API will still have the password in memory in cleartext and a final concern then will remain taking a snapshot of the process, if you don't purge the memory used for the password, that'll not even need to make the snapshot at a crucial moment.
C#, for example, has the SecureString class for that matter, which is hardened against even that scenario.
So overall in short: The task to securely authenticate is a topic of its own. I just know the developer perspective, I'm not a security expert, even I'd consult someone about that, if it really is an important concern to you about FIPS compliance, for example.
Usage of a certificate is very safely authenticating a client, but also relies on the secured access of the cert .pem file. In the end you always need something depending on Windows security related to for example, only run with a specific system account, not in the users window session.
If I were you I'd opt for Option 2. Using the obfuscation mylogin.cnf means is optional in my eyes, not really a big advance for secure unattended MySQL usage.
In any database server, you typically define jobs for administrative things on the server-side and not start them from client sides with batch files.
Bye, Olaf.
My.cnf or my.ini are the common MySQL cobfiguration file.
Yes, arnold, but I don't know why you bring that up though, as they do not relate to the topic of login-paths, which are a feature by default going into mylogin.cnf. On Windows stored in %APPDATA%, as the MySQL manual states. It's not a file existing by default, but created when you make use of that login-path feature.
So far you all only thought about starting the MySQL service itself or configuring MySQL, setting root password, for example. The mentioning of mylogin.cnf and mysql_config_editor point out the question really is about connecting to MySQL via a batch file, unattended by a user, but for security concerns not storing the password. You can of course continue to insist donpick asked "hos to start mysql...".
Anyway, if you google the most common such task, perhaps, creating a backup regularly, the solutions provided often really just put the user and password into the script. It's only a partial issue, when these scripts are stored and schedule on the server.
MySQL Workbench Enterprise Edition also has Enterprise management options allowing cloud backups, for example. But that again is the enterprise league.
Bye, Olaf.
So far you all only thought about starting the MySQL service itself or configuring MySQL, setting root password, for example. The mentioning of mylogin.cnf and mysql_config_editor point out the question really is about connecting to MySQL via a batch file, unattended by a user, but for security concerns not storing the password. You can of course continue to insist donpick asked "hos to start mysql...".
Anyway, if you google the most common such task, perhaps, creating a backup regularly, the solutions provided often really just put the user and password into the script. It's only a partial issue, when these scripts are stored and schedule on the server.
MySQL Workbench Enterprise Edition also has Enterprise management options allowing cloud backups, for example. But that again is the enterprise league.
Bye, Olaf.
Reading your comment and then reviewing the original, wondered whether the person was referring to the MySQL conf file..
ASKER
Apparently my question is not clear. Let me rephrase it.
- I have MySql successfully running on a Windows 7 Pro computer as a service.
- I want to frequently import data to a MySql table.
- I will be using a batch file to start the import routine. My understanding is mysqlimport must connect to MySql so it can perform the import. This means mysqlimport must somehow log in to MySql . I do not want to put the MySql. password in the batch file.
- I understand there are option files where I can put the password.
My question:
- What option file should I create to contain the password?
- What should be the name of this option file on a Windows pc?
- What directory should this option file be put?
- What connection parameters should be put in the mysqlimport batch file so it can read the password and log in?
This should be a simple thing to do . I imagine many MySql users must have done this before
- I have MySql successfully running on a Windows 7 Pro computer as a service.
- I want to frequently import data to a MySql table.
- I will be using a batch file to start the import routine. My understanding is mysqlimport must connect to MySql so it can perform the import. This means mysqlimport must somehow log in to MySql . I do not want to put the MySql. password in the batch file.
- I understand there are option files where I can put the password.
My question:
- What option file should I create to contain the password?
- What should be the name of this option file on a Windows pc?
- What directory should this option file be put?
- What connection parameters should be put in the mysqlimport batch file so it can read the password and log in?
This should be a simple thing to do . I imagine many MySql users must have done this before
the options available to you are unavoidably require the provision of a password.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello Olaf: Good ideas. Thank you.