I just learned about the script: /usr/bin/mysql_secure_installation. Very cool. Here are the commands that the script actually runs.
I am going to add these to my provisioning script (I already take care of the root password piece):
mysql -e “DROP DATABASE test;”
mysql -e “DELETE FROM mysql.user WHERE User=’root’ AND Host!=’localhost’;”
mysql -e “DELETE FROM mysql.user WHERE User=”;”
mysql -e “FLUSH PRIVILEGES;”
Hey, thanks for this. I found this page when looking for a way to secure mysql from a script, and this was the only solution I could think of.
I ran into a few problems, so I thought I’d let you know that the commands the script runs have been updated. They are now:
UPDATE mysql.user SET Password=PASSWORD(‘YOURPASSWORD_HERE’) WHERE User=’root’;
DELETE FROM mysql.user WHERE User=’root’ AND Host NOT IN (‘localhost’, ‘127.0.0.1’, ‘::1′);
DELETE FROM mysql.user WHERE User=”;
DELETE FROM mysql.db WHERE Db=’test’ OR Db=’test\\_%’
FLUSH PRIVILEGES;
Hope this helps someone else searching for a way to secure mysql in a non-interactive way.
Sorry, had an error in that paste (missing a semi-colon). Also, here’s the full command I run from the command line:
mysql -u root <<-EOFMYSQL
UPDATE mysql.user SET Password=PASSWORD('YOURPASSWORD_HERE') WHERE User='root';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;
EOFMYSQL