Change MySQL root password with bash script

Question

I'm attempting to create a bash script to change the MySQL root password, below is the current snippet of code I am working with:

#!/bin/sh clear echo "Enter the current password for the root mysql account." read oldrootpass echo "Enter a new password for the root mysql account." read newrootpass mysql -u root -p$oldrootpass -Bse 'UPDATE user SET password=PASSWORD("$newrootpass") WHERE User="root"' 

The syntax above for changing the root password may not be correct, but the part I am stuck on is generating a one line command to work with mysql, if the command goes over 2 lines then the MySQL prompt is entered and the MySQL commands in the bash script are executed once the MySQL prompt has been closed with the 'exit' command.

My intention is to gather the relevant password information via the bash script, run a single command to change the root password and then return back to the bash script, or bash prompt is the script has ended.

Answer 1

If you're asking how to run more than one statement with a single mysql command, you can either simply separate them by semicolons:

$ mysql -e 'select 1; select 2' +---+ | 1 | +---+ | 1 | +---+ +---+ | 2 | +---+ | 2 | +---+ 

or you can create a file containing the SQL statements and run that:

$ mysql < change_password.sql 

Answer 2

Use mysqladmin instead of running a query in mysql.

mysqladmin -u root -p'$oldrootpass' password '$newrootpass'

should work but you might need to mess with the quotes a little.