Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Automating mysql_secure_installation
#!/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
@jchiavaro
Copy link

jchiavaro commented Aug 15, 2018

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"

@click0
Copy link

click0 commented Sep 11, 2018

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"

@neoacevedo
Copy link

neoacevedo commented Nov 14, 2018

Where the $MYSQL variable comes from?

@fjarrett
Copy link

fjarrett commented Aug 21, 2019

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

@flawiddsouza
Copy link

flawiddsouza commented Dec 22, 2019

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
")

@b23prodtm
Copy link

b23prodtm commented Mar 3, 2020

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

@IgorDePaula
Copy link

IgorDePaula commented Apr 28, 2020

How I know that is secure?

@b23prodtm
Copy link

b23prodtm commented May 19, 2020

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

@faisalmasood
Copy link

faisalmasood commented Sep 1, 2021

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

@faisalmasood
Copy link

faisalmasood commented Sep 2, 2021

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

@mattbrannon
Copy link

mattbrannon commented Oct 29, 2021

@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.

@faisalmasood
Copy link

faisalmasood commented Oct 29, 2021

@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}" 

@alphanetEX
Copy link

alphanetEX commented Apr 20, 2022

@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

@faisalmasood
Copy link

faisalmasood commented Apr 20, 2022

Manny thanks, it works on Debian 10 buster

@alphanetEX
Glad it worked for you.

@faisalmasood

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment