Automating mysql_secure_installation

mysql_secure.sh

#!/bin/bash

aptitude -y install expect

// Not required in actual script
MYSQL_ROOT_PASSWORD=abcd1234

SECURE_MYSQL=$(expect -c "

set timeout 10
spawn mysql_secure_installation

expect \"Enter current password for root (enter for none):\"
send \"$MYSQL\r\"

expect \"Change the root password?\"
send \"n\r\"

expect \"Remove anonymous users?\"
send \"y\r\"

expect \"Disallow root login remotely?\"
send \"y\r\"

expect \"Remove test database and access to it?\"
send \"y\r\"

expect \"Reload privilege tables now?\"
send \"y\r\"

expect eof
")

echo "$SECURE_MYSQL"

aptitude -y purge expect

This is absolutely brilliant!

You can also just run the commands of the script and skip expect altogether (replace $rootpass):

mysql -u root <<-EOF
UPDATE mysql.user SET Password=PASSWORD('$rootpass') 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;
EOF

Thanks jportoles. For some reason that didn't work for me in a CentOS 6.5 bash script, but I achieved the same behaviour by using the following commands in a script:

mysqladmin -u root password "$DATABASE_PASS"
mysql -u root -p"$DATABASE_PASS" -e "UPDATE mysql.user SET Password=PASSWORD('$DATABASE_PASS') WHERE User='root'"
mysql -u root -p"$DATABASE_PASS" -e "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1')"
mysql -u root -p"$DATABASE_PASS" -e "DELETE FROM mysql.user WHERE User=''"
mysql -u root -p"$DATABASE_PASS" -e "DELETE FROM mysql.db WHERE Db='test' OR Db='test\_%'"
mysql -u root -p"$DATABASE_PASS" -e "FLUSH PRIVILEGES"

progtamster how does your code works can u mention .where we should mention mysql_secure_installation

prameelab he is actually performing all of the mysql commands that do the same thing as the secure_install would.

Does not work in CentOS 6

This needs to be updated for MySQL 5.7. The questions are different: https://gist.github.com/enoch85/9cf2389df2b14569f063

Had a slightly different use case where I'm installing MySQL 5.5 from source, and want to set the password. I boot the server temporarily with --skip-grant-tables to get all access, then run the expect to set the password, then kill it.

./bin/mysqld_safe \
  --defaults-file=/etc/mysql.cnf \
  --skip-grant-tables &
myPid=$!

echo "--> Wait 7s to boot up MySQL on pid ${myPid}"
sleep 7

echo "--> Set root password"
expect -f - <<-EOF
  set timeout 10
  spawn mysql_secure_installation
  expect "Enter current password for root (enter for none):"
  send -- "\r"
  expect "Set root password?"
  send -- "y\r"
  expect "New password:"
  send -- "${MYSQL_PASS}\r"
  expect "Re-enter new password:"
  send -- "${MYSQL_PASS}\r"
  expect "Remove anonymous users?"
  send -- "y\r"
  expect "Disallow root login remotely?"
  send -- "y\r"
  expect "Remove test database and access to it?"
  send -- "y\r"
  expect "Reload privilege tables now?"
  send -- "y\r"
  expect eof
EOF

echo "--> Kill MySQL on pid ${myPid}"
kill -9 ${myPid}

It looks like even fully unattended install for MySQL 5.7 and inserting root password during installation was not working.

Had to tweak my way to set to to default as root and then change in my latest LAMP + WORDPRESS unattended script.

You can check my repo

# for mysql 5.7
# first n is for validate password plugin removal and it could be y
 mysql_secure_installation <<EOF
n
somepass
somepass
y
y
y
y
y
EOF

You should change this:

expect \"Enter current password for root:\"
send \"$MYSQL\r\"

to this

expect \"Enter current password for root:\"
send \"$MYSQL_ROOT_PASSWORD\r\"

Thanks!

I took your idea and did the same for Centos 7.4 / MariaDb/MySQL 5.5 but I had to change some expected words though:

yum install -y expect

MYSQL_ROOT_PASSWORD="my_password_goes_here"

SECURE_MYSQL=$(expect -c "

set timeout 10
spawn mysql_secure_installation

expect "Enter current password for root (enter for none):"
send "$MYSQL\r"

expect "Set root password?"
send "y\r"

expect "New password:"
send "$MYSQL_ROOT_PASSWORD\r"

expect "Re-enter new password:"
send "$MYSQL_ROOT_PASSWORD\r"

expect "Remove anonymous users?"
send "y\r"

expect "Disallow root login remotely?"
send "y\r"

expect "Remove test database and access to it?"
send "y\r"

expect "Reload privilege tables now?"
send "y\r"
expect eof
")

echo "$SECURE_MYSQL"

Update for mysql 8 running on centos 7:
#!/usr/bin/env bash

MYSQL=$(grep 'temporary password' /var/log/mysqld.log | awk '{print $13}')
MYSQL_ROOT_PASSWORD="secret_password"

SECURE_MYSQL=$(expect -c "

set timeout 10
spawn mysql_secure_installation

expect "Enter password for user root:"
send "$MYSQL\r"

expect "Change the password for root ? ((Press y|Y for Yes, any other key for No) :"
send "y\r"

expect "New password:"
send "$MYSQL_ROOT_PASSWORD\r"

expect "Re-enter new password:"
send "$MYSQL_ROOT_PASSWORD\r"

expect "Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) :"
send "y\r"

expect "Remove anonymous users? (Press y|Y for Yes, any other key for No) :"
send "y\r"

expect "Disallow root login remotely? (Press y|Y for Yes, any other key for No) :"
send "y\r"

expect "Remove test database and access to it? (Press y|Y for Yes, any other key for No) :"
send "y\r"

expect "Reload privilege tables now? (Press y|Y for Yes, any other key for No) :"
send "y\r"
expect eof
")

echo "$SECURE_MYSQL"

Update for Percona 5.7 running on Debian 8.x:

[ ! -e /usr/bin/expect ] && { apt-get -y install expect; }
SECURE_MYSQL=$(expect -c "

set timeout 10
spawn mysql_secure_installation

expect \"Press y|Y for Yes, any other key for No: \"
send \"n\r\"
expect \"Change the password for root ? ((Press y|Y for Yes, any other key for No) : \"
send \"n\r\"
expect \"Remove anonymous users? (Press y|Y for Yes, any other key for No) : \"
send \"y\r\"
expect \"Disallow root login remotely? (Press y|Y for Yes, any other key for No) : \"
send \"y\r\"
expect \"Remove test database and access to it? (Press y|Y for Yes, any other key for No) : \"
send \"y\r\"
expect \"Reload privilege tables now? (Press y|Y for Yes, any other key for No) : \"
send \"y\r\"
expect eof
")

echo "$SECURE_MYSQL"

Where the $MYSQL variable comes from?

👏 thanks for this! worked perfectly with one modification Set root password?

Update for MariaDB 10.4 on Ubuntu 18.04

[ ! -e /usr/bin/expect ] && { apt-get -y install expect; }
SECURE_MYSQL=$(expect -c "

set timeout 10
spawn mysql_secure_installation

expect \"Enter current password for root (enter for none): \"
send \"n\r\"
expect \"Switch to unix_socket authentication \[Y/n\] \"
send \"n\r\"
expect \"Change the root password? \[Y/n\] \"
send \"y\r\"
expect \"New password: \"
send \"12345\r\"
expect \"Re-enter new password: \"
send \"12345\r\"
expect \"Remove anonymous users? \[Y/n\] \"
send \"y\r\"
expect \"Disallow root login remotely? \[Y/n\] \"
send \"y\r\"
expect \"Remove test database and access to it? \[Y/n\] \"
send \"y\r\"
expect \"Reload privilege tables now? \[Y/n\] \"
send \"y\r\"
expect eof
")

Need to add update repository cache (dockerized ubuntu)
[ ! -e /usr/bin/expect ] && apt-get update && apt-get -y install expect

How I know that is secure?

It's secure if you cannot access into the database with a minimal credential parameters, from client :

mysql <ENTER> 

it's possible if the mysql database wasn't granted to a user identified by a password.
To setup either use sh ./mysql-secure.sh or mysqladmin password and after this command it's done with a password:

mysql -uroot --password=12345

Is there an automated one for Ubuntu 20.04.3 with the latest MySQL version? The password option keeps throwing off. I'm using a different account than the root on system

I got it working on Ubuntu using:
debconf-set-selections

@faisalmasood Can you share what you did to make it work? I'm trying the same thing but still getting errors when I attempt to login with the new password.

@mattbrannon I used this in my version for the latest Ubuntu 20.04.3 and MySQL version

PASS_MYSQL_ROOT=`openssl rand -base64 12` # Save this password

# Set password with `debconf-set-selections` You don't have to enter it in prompt
sudo debconf-set-selections <<< "mysql-server mysql-server/root_password password ${PASS_MYSQL_ROOT}" # new password for the MySQL root user
sudo debconf-set-selections <<< "mysql-server mysql-server/root_password_again password ${PASS_MYSQL_ROOT}" # repeat password for the MySQL root user

# Other Code.....
sudo mysql --user=root --password=${PASS_MYSQL_ROOT} << EOFMYSQLSECURE
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;
EOFMYSQLSECURE

# Note down this password. Else you will lose it and you may have to reset the admin password in mySQL
echo -e "SUCCESS! MySQL password is: ${PASS_MYSQL_ROOT}" 

@mattbrannon I used this in my version for the latest Ubuntu 20.04.3 and MySQL version

PASS_MYSQL_ROOT=`openssl rand -base64 12` # Save this password

# Set password with `debconf-set-selections` You don't have to enter it in prompt
sudo debconf-set-selections <<< "mysql-server mysql-server/root_password password ${PASS_MYSQL_ROOT}" # new password for the MySQL root user
sudo debconf-set-selections <<< "mysql-server mysql-server/root_password_again password ${PASS_MYSQL_ROOT}" # repeat password for the MySQL root user

# Other Code.....
sudo mysql --user=root --password=${PASS_MYSQL_ROOT} << EOFMYSQLSECURE
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;
EOFMYSQLSECURE

# Note down this password. Else you will lose it and you may have to reset the admin password in mySQL
echo -e "SUCCESS! MySQL password is: ${PASS_MYSQL_ROOT}" 

Manny thanks, it works on Debian 10 buster

Manny thanks, it works on Debian 10 buster

@alphanetEX
Glad it worked for you.

@faisalmasood

Amazing work!! thanks!