85

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.

Improve this question
1
  • 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. Commented Jun 18, 2014 at 16:38

17 Answers 17

Reset to default
105

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

To disallow remote login for root DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
For mariadb mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;"
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"
MariaDB 10.6 show this error View 'mysql.user' references invalid table(s)
@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'.
65

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.

Improve this answer

1 Comment

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: github.com/mysql/mysql-server/blob/…
36

I just did this on CentOS 6.7 with the following:

mysql_secure_installation <<EOF

y
secret
secret
y
y
y
y
EOF
Improve this answer

7 Comments

Excellent answer. This technique is great-- it works for fdisk as well. Clean and easy to read.
Convenient sure, but not maintainable as reading it can't tell you what's going on.
Nice answer for Centos 6, but alas it did not work for me on 7.
Doesn't work on Ubuntu 16.04, mysql_secure_installation keeps asking for the root password.
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.
|
28

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

Improve this answer

1 Comment

Please update the "except" link, the link results in: Error two or more packages specified (expect wheezy)
8

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
Improve this answer

2 Comments

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)
This flag does not exist for my installation of mariadb (Ver 15.1 Distrib 10.5.15-MariaDB, for debian-linux-gnu (aarch64)).
7

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
Improve this answer

Comments

6 
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

Improve this answer

Comments

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
Improve this answer

Comments

2

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.
Improve this answer

Comments

2

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
Improve this answer

Comments

1

You could try this:

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

4 Comments

Remove the "." (dot) in "./usr/bin/mysql_secure_installation" and it should work.
Doesn't work on Ubuntu 16.04, mysql_secure_installation keeps asking for the root password.
Replace ./usr/bin/mysql_secure_installation with sh /usr/bin/mysql_secure_installation if the period doesn't work for you.
@ssc run mysql_secure_installation as root
0

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
Improve this answer

Comments

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
Improve this answer

Comments

0

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)

Improve this answer

Comments

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.

Improve this answer

Comments

0

In case you are looking to complete it with default options, --use-default can be used

mysql_secure_installation --use-default

Ref - https://dev.mysql.com/doc/refman/8.0/en/mysql-secure-installation.html

You can change root password with below command before running above command:

mysql -e "UPDATE mysql.user SET Password = PASSWORD('YOUR_PASSWORD_HERE') WHERE User = 'root'"
Improve this answer

Comments

0

You can use \n as enter:

echo '\n y root root y y y y' | mysql_secure_installation

That do:

Enter current password for root (enter for none): <enter>
Set root password? [Y/n] y
New password: root
Re-enter new password: root
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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.