Skip to content

Instantly share code, notes, and snippets.

@Mins
Last active February 16, 2024 00:03
Star You must be signed in to star a gist
Save Mins/4602864 to your computer and use it in GitHub Desktop.
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
@faisalmasood
Copy link

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

@mattbrannon
Copy link

@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

@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

Manny thanks, it works on Debian 10 buster

@alphanetEX
Glad it worked for you.

@faisalmasood

@RussQuan
Copy link

RussQuan commented Sep 6, 2023

Amazing work!! thanks!

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