0

Reference: How to pass password to mysql command line

I want to grab returned metadata from an AWS EC2 instance and set a new mysql password based on this data.

I need assistance with the script which will run as first boot.

#!/bin/bash  
output=$(ec2metadata --instance-id); Used for storing EC2 ID.   

mysql -u root -pOldPassword  
UPDATE mysql.user  

Now pass instance-id and use it to set a new password for mysql root.

SET authentication_string = PASSWORD('$output'), password_expired = 'N'
WHERE User = 'root' AND Host = 'localhost';

FLUSH PRIVILEGES;

EXIT
  • 1
    do you just need something like: iid=$(ec2metadata --instance-id) ? – Jeff Schaller May 25 '17 at 16:17
  • Yes, I have gotten that far thanks; however, my issue remains how to pass that back MySQL. Right now, I have it output=$(ec2metadata --instance-id) then I use a my.cnf to set MySQL credentials to allow bash: UPDATE mysql.user SET authentication_string = PASSWORD($output), but it fails here as I don't know the correct syntax for MySQL to accept a bash variable. – drew_satellite May 26 '17 at 19:25
  • could you edit your post with the current situation? comments are hard to keep track of / read. – Jeff Schaller May 26 '17 at 19:34
  • @Jeff, updated for post for clarity. Logic is correct. I omitted the requirement to use my.cnf. The issue appears to be how the mysql line is formatted with quotes. I was able to test a working solution, and will post the solution when I can confirm. Thanks! – drew_satellite May 27 '17 at 22:47
0

Bash do not expand variables in single quotes. Apart from that, you can run multiple SQL queries, or a single one if you prefer to, by using the <<EOF...EOF construction. The code below worked for me

#!/bin/bash                                                                                                                                 

newpass="newPassword"

mysql -u root -poldPassword <<EOF                                                                                                                  
UPDATE mysql.user SET Password=PASSWORD("$newpass") WHERE User='root';                                                                      
FLUSH PRIVILEGES;                                                                                                                           
QUIT                                                                                                                                        
EOF

You may run the properly UPDATE command according to your MySQL version. I was using 5.6.35. In case you are running MySQL 5.7 only, use the following UPDATE query instead of that shown above:

UPDATE mysql.user SET authentication_string=PASSWORD("$newpass") WHERE User='root';

You can find more information in the MySQL official docs: https://dev.mysql.com/doc/refman/5.7/en/update.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.