Mysql – Change MySQL root password with bash script

bashMySQL

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.

Best Answer

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