2

What is a good practice to execute root operations on mysql databases in bash scripts?

For example, I want to create new database and grant privileges, I have to use:

mysql -u $ROOT -p"$ROOTPASS" -e "create database $DBNAME; GRANT ALL PRIVILEGES ON $DBNAME.* T $DBUSER@localhost IDENTIFIED BY $DBPASS";

But this requires storing mysql password for root user in the script itself. Is there any more secure way?
Should I create separate user for this? How to set up the permissions?

The separate thing is how to test if the operation was successful, and if not, revert back the changes which were successfull (transaction?)

Sfisioza
  • 592
  • 2
  • 8
  • 18

1 Answers1

4

Like many programs the MySQL (client) can and will read credentials from file instead of relying on commandline options or reading credentials from STDIN.

The recommended method is to store your credentials encrypted with the MySQL config Editor in ~/.mylogin.cnf

mysql_config_editor encrypts the .mylogin.cnf file so it cannot be read as clear text, and its contents when decrypted by client programs are used only in memory. In this way, passwords can be stored in a file in non-cleartext format and used later without ever needing to be exposed on the command line or in an environment variable. mysql_config_editor provides a print command that enables the user to display the file contents, but even in this case, password values are masked so as never to appear in a way that other users can see them.

Check the manual for the exact location, but alternatively you set up a file ~/.my.cnf which is only readable by your user ans store your cleartext password(s) there:

 [client]
 # The following password will be sent to all standard MySQL clients
 password="my_password"

Additionally see the manual section on password security

HBruijn
  • 77,029
  • 24
  • 135
  • 201
  • Note that `mysql_config_editor` requires mysql >= 5.6, which may be easily installed/upgraded using the [Official MySQL APT repo](http://dev.mysql.com/downloads/repo/apt/) – Sfisioza Sep 26 '14 at 10:27
  • See the follow up questions: http://serverfault.com/questions/632703/password-less-mysql-access-to-specified-user-script-only http://serverfault.com/questions/632719/allow-mysql-user-to-be-used-just-by-specified-user – Sfisioza Oct 01 '14 at 12:00