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

This comment has been minimized.

Copy link

DarthCaniac commented May 7, 2014

This is absolutely brilliant!

@jportoles

This comment has been minimized.

Copy link

jportoles commented Sep 5, 2014

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

@programster

This comment has been minimized.

Copy link

programster commented Sep 13, 2014

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

This comment has been minimized.

Copy link

prameelab commented Nov 24, 2014

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

@parkervcp

This comment has been minimized.

Copy link

parkervcp commented Nov 25, 2014

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

@axl89

This comment has been minimized.

Copy link

axl89 commented Sep 15, 2015

Does not work in CentOS 6

@enoch85

This comment has been minimized.

Copy link

enoch85 commented Feb 1, 2016

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

@kvz

This comment has been minimized.

Copy link

kvz commented Oct 20, 2016

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

This comment has been minimized.

Copy link

suraj2410 commented Mar 24, 2017

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

@nmccready

This comment has been minimized.

Copy link

nmccready commented May 28, 2017

# 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
@mansouryaacoubi

This comment has been minimized.

Copy link

mansouryaacoubi commented Jun 6, 2017

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\"
@dx-zone

This comment has been minimized.

Copy link

dx-zone commented May 4, 2018

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"

@jchiavaro

This comment has been minimized.

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

This comment has been minimized.

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

This comment has been minimized.

Copy link

neoacevedo commented Nov 14, 2018

Where the $MYSQL variable comes from?

@fjarrett

This comment has been minimized.

Copy link

fjarrett commented Aug 21, 2019

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.