Automate mysql_secure_installation with echo command via a shell script
I stumbled upon this question but decided to run the queries manually through a Bash script:
#!/bin/bash# Make sure that NOBODY can access the server without a passwordmysql -e "UPDATE mysql.user SET Password = PASSWORD('CHANGEME') WHERE User = 'root'"# Kill the anonymous usersmysql -e "DROP USER ''@'localhost'"# Because our hostname varies we'll use some Bash magic here.mysql -e "DROP USER ''@'$(hostname)'"# Kill off the demo databasemysql -e "DROP DATABASE test"# Make our changes take effectmysql -e "FLUSH PRIVILEGES"# Any subsequent tries to run queries this way will get access denied because lack of usr/pwd param
Since mysql_secure_installation is just a Bash script, just check out the raw source code as shown here. Look for the lines that read, do_query (note that extra space I placed after do_query; need to find queries versus the function) and then you can find these commands.
UPDATE mysql.user SET Password=PASSWORD('root') WHERE User='root';DELETE FROM mysql.user WHERE User='';DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');DROP DATABASE IF EXISTS test;DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';FLUSH PRIVILEGES;Note that for this example, I have the password being set to root but feel free to change that to match your setup needs. Anyway, take that simply pile of MySQL commands and save it in a file named mysql_secure_installation.sql.
With that done, just run the following command via script to secure the MySQL install:
mysql -sfu root < "mysql_secure_installation.sql"The s silences errors and the f forces the commands to continue even if one chokes. The u relates to the username that immediately follows it which—in this case—is clearly root.
Run that in a deployment script where MySQL is installed initially without a password and you are all set to lock it down without any keyboard interaction.
PS: This script was put together to secure a MySQL installation on Ubuntu 14.04 which was installed with the export DEBIAN_FRONTEND=noninteractive set and the actual install command being set to sudo -E aptitude install -y --assume-yes -q mysql-server mysql-client. Doing that will cleanly install MySQL on Ubuntu without a password; which is nice for deployment scripts. This mysql -sfu root < "mysql_secure_installation.sql" just locks it all down in seconds after that install happens.