Automate mysql_secure_installation with echo command via a shell script

Question

I am trying to automate mysql_secure_installation script with automated response. My code is as follows :

echo "& y y abc abc y y y y" | ./usr/bin/mysql_secure_installation

The actual questions which i am automating are as follows:

Enter current password for root (enter for none): <enter>
Set root password? [Y/n] y
New password: abc
Re-enter new password: abc
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y

But it gives me an error "Sorry you cannot use an empty password here" but in the screen i used to press the return key for the first question.

A work around for that issue is i manually run all the queries which will be triggered during mysql_secure_install. It works as of now. Thanks. — cucucool, Jun 18 '14 at 16:38

score 101 · Answer 1 · answered Jan 03 '15 at 20:10

101

I stumbled upon this question but decided to run the queries manually through a Bash script:

#!/bin/bash

# Make sure that NOBODY can access the server without a password
mysql -e "UPDATE mysql.user SET Password = PASSWORD('CHANGEME') WHERE User = 'root'"
# Kill the anonymous users
mysql -e "DROP USER ''@'localhost'"
# Because our hostname varies we'll use some Bash magic here.
mysql -e "DROP USER ''@'$(hostname)'"
# Kill off the demo database
mysql -e "DROP DATABASE test"
# Make our changes take effect
mysql -e "FLUSH PRIVILEGES"
# Any subsequent tries to run queries this way will get access denied because lack of usr/pwd param

answered Jan 03 '15 at 20:10

Sleavely

1,654
2
11
18

12

To disallow remote login for root DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1'); – Tirsvad Dec 13 '15 at 10:59
1

For mariadb `mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;"` – MaXi32 Dec 26 '20 at 01:35
In case the test database doesn't exist, this will exit with a `0` return status so won't kill your script if you use `set -e`: `mysql -e "DROP DATABASE IF EXISTS test"` – Freedom_Ben Feb 21 '21 at 01:26
3

MariaDB 10.6 show this error **View 'mysql.user' references invalid table(s)** – vee Aug 14 '21 at 20:25
@vee That's because mysql.user is no longer a table, but a view. you should change the statement to `ALTER USER root@localhost IDENTIFIED BY 'strongpassword'`. – BUFU Oct 21 '22 at 15:57

score 63 · Answer 2 · answered Jan 26 '16 at 00:17

Since mysql_secure_installation is just a Bash script, just check out the raw source code as shown here. Look for the lines that read, do_query (note that extra space I placed after do_query; need to find queries versus the function) and then you can find these commands.

UPDATE mysql.user SET Password=PASSWORD('root') WHERE User='root';
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;

Note that for this example, I have the password being set to root but feel free to change that to match your setup needs. Anyway, take that simply pile of MySQL commands and save it in a file named mysql_secure_installation.sql.

With that done, just run the following command via script to secure the MySQL install:

mysql -sfu root < "mysql_secure_installation.sql"

The s silences errors and the f forces the commands to continue even if one chokes. The u relates to the username that immediately follows it which—in this case—is clearly root.

Run that in a deployment script where MySQL is installed initially without a password and you are all set to lock it down without any keyboard interaction.

PS: This script was put together to secure a MySQL installation on Ubuntu 14.04 which was installed with the export DEBIAN_FRONTEND=noninteractive set and the actual install command being set to sudo -E aptitude install -y --assume-yes -q mysql-server mysql-client. Doing that will cleanly install MySQL on Ubuntu without a password; which is nice for deployment scripts. This mysql -sfu root < "mysql_secure_installation.sql" just locks it all down in seconds after that install happens.

FYI, in mysql 8, the team rewrote `mysql_secure_installation` in C++. The source code is here, if you still want to use the approach of picking out the commands from there. The syntax for updating passwords changed: https://github.com/mysql/mysql-server/blob/7ed30a748964c009d4909cb8b4b22036ebdef239/client/mysql_secure_installation.cc — pietrorea, Oct 22 '21 at 18:23

score 31 · Answer 3 · answered Apr 28 '16 at 13:36

31

I just did this on CentOS 6.7 with the following:

mysql_secure_installation <<EOF

y
secret
secret
y
y
y
y
EOF

answered Apr 28 '16 at 13:36

TSchroeder

351
3
3

Excellent answer. This technique is great-- it works for fdisk as well. Clean and easy to read. – David Betz Aug 09 '16 at 20:44
8

Convenient sure, but not maintainable as reading it can't tell you what's going on. – bschlueter Nov 22 '16 at 23:54
Nice answer for Centos 6, but alas it did not work for me on 7. – John Elion Apr 05 '17 at 12:38
3

Doesn't work on Ubuntu 16.04, `mysql_secure_installation` keeps asking for the root password. – ssc Dec 27 '17 at 10:42
Works on centos7 for me. Thanks – Chaim Geretz Jul 26 '18 at 21:04
Why simulate stdin, when you can perform the exact queries that the script executes? This solution is bad practice. Readers of your script have no idea what the script does. – raw-bin hood Sep 25 '18 at 05:34
Worked for me on Centos 7.5. Note the first blank line is necessary, as this is the response to "Enter current password for root (enter for none):" – Burrito Feb 21 '19 at 00:26
This worked for me, however, the trick was to first make sure the service was running, in my case, MariaDB: `sudo service mariadb start` and then using `sudo` before calling the `mysql_secure_installation` command, otherwise it wouldn't go through, as seen [here](https://stackoverflow.com/a/39539376/13177011). – Raphael Setin Jun 12 '23 at 22:18

Volodymyr Chumak · Accepted Answer · 2020-12-25T12:50:09.360

27

You can try to use expect, that automates interactive applications. Look at this automating mysql_secure_installation or at my modification.

edited Dec 25 '20 at 12:50

answered Aug 12 '14 at 19:33

Volodymyr Chumak

756
6
12

2

Please update the "except" link, the link results in: Error two or more packages specified (expect wheezy) – Book Of Zeus Jul 31 '19 at 22:17

score 7 · Answer 5 · edited Jul 29 '22 at 01:14

Here is an automated script for a fresh MySQL 5.7 installation based on @Giacomo1968's answer. Works fine on CentOS 7.5.1804.

yum localinstall -y https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
yum install -y mysql-community-server

# start mysql service
service mysqld start

# get Temporary root Password
root_temp_pass=$(grep 'A temporary password' /var/log/mysqld.log |tail -1 |awk '{split($0,a,": "); print a[2]}')

echo "root_temp_pass:"$root_temp_pass

# mysql_secure_installation.sql
cat > mysql_secure_installation.sql << EOF
# Make sure that NOBODY can access the server without a password
UPDATE mysql.user SET Password=PASSWORD('yourrootpass') WHERE User='root';
# Kill the anonymous users
DELETE FROM mysql.user WHERE User='';
# disallow remote login for root
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
# Kill off the demo database
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
# Make our changes take effect
FLUSH PRIVILEGES;
EOF

mysql -uroot -p"$root_temp_pass" --connect-expired-password <mysql_secure_installation.sql

score 6 · Answer 6 · answered Aug 06 '19 at 18:04

sudo mysql -e "SET PASSWORD FOR root@localhost = PASSWORD('123');FLUSH PRIVILEGES;" 

printf "123\n n\n n\n n\n y\n y\n y\n" | sudo mysql_secure_installation

Enter current password for root (enter for none)? (I have 123 set for root)

Switch to unix_socket authentication? n

Change the root password? n

Remove anonymous users? n

Disallow root login remotely? y

Remove test database and access to it? y

Reload privilege tables now? y

Version: mysql Ver 15.1 Distrib 10.4.6-MariaDB, for osx10.14 (x86_64) using readline 5.1

score 6 · Answer 7 · answered Oct 22 '21 at 19:57

It's not necessary to use expect or to fish the SQL commands out of the source code (although if you want to, the C++ file you are looking for is here: https://github.com/mysql/mysql-server/blob/7ed30a748964c009d4909cb8b4b22036ebdef239/client/mysql_secure_installation.cc)

If you are happy with the defaults in mysql_secure_installation (the most secure option is always the default) then you can use the --use-default option to skip most of the interaction. mysql_secure_installation will still ask you for a root password interactively if one is not set, so you can just script that away by setting it before calling mysql_secure_option.

Here's an example:

mysql -u root <<EOF
SET PASSWORD FOR root@localhost = '${ROOT_PASSWORD}';
FLUSH PRIVILEGES;
EOF

mysql_secure_installation -u root --password="${ROOT_PASSWORD}" --use-default

IMO this best and most concisely answers the OP's needs in a way that is not kludgy (i.e. the 'echo' part mentioned in the title) — jbobbins, Apr 10 '22 at 00:36
This flag does not exist for my installation of mariadb (Ver 15.1 Distrib 10.5.15-MariaDB, for debian-linux-gnu (aarch64)). — BUFU, Oct 21 '22 at 15:51

score 3 · Answer 8 · answered Jun 03 '22 at 21:05

3

I had the same problem. Replacing the echo command to use -e and \n seems to have fixed it.

echo -e "\ny\ny\nabc\nabc\ny\ny\ny\ny\n" | ./usr/bin/mysql_secure_installation

answered Jun 03 '22 at 21:05

SwiftNinjaPro

787
8
17

Martin Bramwell · Answer 9 · 2020-04-23T17:02:21.403

Just tested this on Ubuntu Bionic 18.04LTS

Step #1

export MYPWD="D33Ps3CR3T";
export NEWPWD="D33P3Rs3CR3T";

Step #2

# First time **ever**
sudo mysql_secure_installation 2>/dev/null <<MSI

n
y
${MYPWD}
${MYPWD}
y
y
y
y

MSI

# Did it work?
mysql -u root -p${MYPWD} -e "SELECT 1+1";
# -------

Step #3

# Every subsequent time
sudo mysql_secure_installation 2>/dev/null <<MSI2
${MYPWD}
n
y
${NEWPWD}
${NEWPWD}
y
y
y
y

MSI2

# Just in case (optional) ....
sudo service mysql restart

# Did it work?
mysql -u root -p${NEWPWD} -e "SELECT 1+1";

You should be able to cut'n paste steps #2 & #3 directly into a terminal, after editing the before and after passwords from step #1.

Notes

If a root password has already been set step #2 will fail, so go to step #3
It's just a heredoc fed into the command
sudo is obligatory.
MSI has no particular meaning (it's collision avoidance; I use EOF elsewhere in the script)
MYPWD == NEWPWD is allowed
2>/dev/null hides the warning "stty: 'standard input': Inappropriate ioctl for device"
You can use &>/dev/null for fully silent mode.

score 2 · Answer 10 · edited Apr 22 '22 at 01:52

Works for AWS. Amazon Linux 2 AMI. Custom settings to start an instance (AWS User data):

#!/bin/bash            
sudo yum -y update &> /dev/null
wget https://repo.mysql.com/mysql80-community-release-el7-1.noarch.rpm &> /dev/null
sudo yum -y localinstall mysql80-community-release-el7-1.noarch.rpm
sudo yum -y install mysql-community-server &> /dev/null
sudo service mysqld start
   
# get Temporary root Password
root_temp_pass=$(sudo grep 'A temporary password' /var/log/mysqld.log |tail -1 |awk '{split($0,a,": "); print a[2]}')
echo "root_temp_pass: " $root_temp_pass
# mysql_secure_installation.sql
sudo cat > mysql_secure_installation.sql << EOF
# Make sure that NOBODY can access the server without a password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'yourrootpass';
# Kill the anonymous users
DELETE FROM mysql.user WHERE User='';
# disallow remote login for root
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
# Kill off the demo database
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
# Make our changes take effect
FLUSH PRIVILEGES;
EOF
        
sudo mysql -uroot -p"$root_temp_pass" --connect-expired-password <mysql_secure_installation.sql
sudo systemctl enable mysql

score 1 · Answer 11 · edited Jan 26 '16 at 00:10

1

You could try this:

echo -e "\ny\ny\nabc\nabc\ny\ny\ny\ny" | ./usr/bin/mysql_secure_installation

edited Jan 26 '16 at 00:10

Giacomo1968

25,759
11
71
103

answered Apr 17 '15 at 18:51

Riccardo

27
3

1

Remove the "." (dot) in "./usr/bin/mysql_secure_installation" and it should work. – mevdschee Jul 18 '17 at 14:00
3

Doesn't work on Ubuntu 16.04, `mysql_secure_installation` keeps asking for the root password. – ssc Dec 27 '17 at 10:44
Replace `./usr/bin/mysql_secure_installation` with `sh /usr/bin/mysql_secure_installation` if the period doesn't work for you. – Josh Correia Dec 23 '20 at 00:43
@ssc run mysql_secure_installation as root – prajwal Feb 27 '22 at 06:04

score 0 · Answer 12 · edited Feb 24 '19 at 18:56

I am using simple command to change root password after MySql installation ,But getting the Above error (signal 9 kill)

(FATAL: Chef::Exceptions::ChildConvergeError: Chef run process terminated by signal 9 (KILL)) Though the command works and password is changed the error is confusing.
script "change password" do
interpreter "bash"
user "root"
cwd "/tmp"
code <<-EOH
#MYSQL
root_temp_pass=$(grep 'A temporary password' /mysql/log/mysqld.log |tail -1 |awk '{split($0,a,": "); print a[2]}')

#Login as root change password
mysql -uroot -p"$root_temp_pass" -Be "ALTER USER 'root'@'localhost' IDENTIFIED BY 'Czt732ck#';" --connect-expired-password
EOH
end

score 0 · Answer 13 · edited Jul 25 '20 at 06:36

0

I use following lines. Works fine for AWS Linux AMI 2018

db_root_password=Password4root
cat <<EOF | mysql_secure_installation
y
0
$db_root_password
$db_root_password
y
y
y
y
y
EOF

edited Jul 25 '20 at 06:36

General Grievance

4,555
31
31
45

answered Jul 25 '20 at 01:18

Dev Bhusal

1
2

littlelion · Answer 14 · 2022-07-20T21:59:49.873

The top-voted solution is a bit of a hack, and is version, spin, and OS specific. Unfortunately the elegant solution (using --use-defaults) is not supported by the script shipped with RHEL9.

Here is a somewhat more compatible hack to extract the correct commands from the shipped script:

grep 'do_query ' /usr/bin/mariadb-secure-installation | \
    sed -e 's/ *do_query \"//' -e 's/\"$//' \
        -e "s/\$esc_pass/$db_admin_pass/" \
        -e 's/([^;])$/\\1;/' | \
    grep -v 'show create' | \
    mysql --user=$db_admin_user

The first 2 sed commands strip off the do_query call from each SQL command. The second line sets your new root password. The third line adds a trailing semicolon to any SQL commands not so terminated; this is broken in the script shipped with RHEL9

The command assumes you provide the variables $db_admin_user and $db_admin_pass

The code was pulled from my puppet manifest so command quoting may differ depending on how you use it.

The mysql command needs root privileges (which it should inherit from your automation engine)

score 0 · Answer 15 · answered Nov 05 '22 at 01:30

0

echo -e "${MARIADB_ROOT_PASSWORD}\nY\nn\nY\nn\nY\nY\n" | ./usr/bin/mysql_secure_installation

...did work for me, in my mariadb docker container. Without this call I could not get simple things like /etc/init.d/mariadb status running.

answered Nov 05 '22 at 01:30

Fusca Software

709
6
11

score -4 · Answer 16 · answered Jan 12 '18 at 06:55

-4

In Windows OS just search for 'mysql_secure_installation' application usually found in Drive:\MySQL_Install-DIR\bin\
By pressing WindowKey + R just run the command 'Drive:\MySQL_Install-DIR\bin\mysql_secure_installation'

When you run this command a window will pop up that will walk you through the process of securing your MySQL installation. That's it!

answered Jan 12 '18 at 06:55

Joe

23
5

4

It's not what OP asked for. – Houman Feb 04 '18 at 13:14

Automate mysql_secure_installation with echo command via a shell script

16 Answers16

Notes

Linked