Pass output from metadata call to MySQL

Question

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

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

score 0 · Answer 1 · edited Apr 17 '19 at 11:55

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

Pass output from metadata call to MySQL

1 Answers1