きっかけ
- mysql_secure_installation の自動化がしたくって何となく
--help
打ったらめっちゃオプションが増えててびっくりした。 - Web で検索すると mysql_secure_installation の中身を調べるケースがまだ多かったので、折角だから書いてみた。
結論
忙しい人向け。
MySQL 5.7.4 以降は mysql_secure_installation に--password (-p)
や--use-default (-D)
が使えて自動化が楽になりました。
下記の2コマンドで自動化可能。
$ mysqladmin -p{old-password} password {new-password}
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
※ mysqladmin でのパスワード変更コマンドは 5.7 までとされているので、今後は--skip-grant-tables
で root パスワードをリセットするやり方に切り替えた方が良さ気。(後で書くかも)
あとは mysql_secure_installation に No-interactive なオプションを与えて実行するだけ。
$ mysql_secure_installation -p{new-password} -D
mysql_secure_installation: [Warning] Using a password on the command line interface can be insecure.
Securing the MySQL server deployment.
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
MySQL 5.7 の mysql_secure_installation は色々なオプションが増えていた
これが MySQL 5.7 で--help
を叩いた結果。ご覧の通り--password
などの見慣れたオプションがある。
そして、--use-default
なんて便利そうなものがあるではないですか。
$ mysql_secure_installation --help
mysql_secure_installation Ver 5.7.16, for Linux on x86_64
Copyright (c) 2013, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
MySQL Configuration Utility.Usage: mysql_secure_installation [OPTIONS]
-?, --help Display this help and exit.
-h, --host=name Connect to host.
-p, --password[=name]
Password to connect to the server. If password is not
given it's asked from the tty.
-P, --port=# Port number to use for connection or 0 for default to, in
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/services, built-in default (3306).
--protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
-S, --socket=name Socket file to be used for connection.
--ssl-mode=name SSL connection mode.
--ssl Deprecated. Use --ssl-mode instead.
(Defaults to on; use --skip-ssl to disable.)
--ssl-verify-server-cert
Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.
--ssl-ca=name CA file in PEM format.
--ssl-capath=name CA directory.
--ssl-cert=name X509 cert in PEM format.
--ssl-cipher=name SSL cipher to use.
--ssl-key=name X509 key in PEM format.
--ssl-crl=name Certificate revocation list.
--ssl-crlpath=name Certificate revocation list path.
--tls-version=name TLS version to use, permitted values are: TLSv1, TLSv1.1
-u, --user=name User for login if not root.
-D, --use-default Execute with no user interactivity
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
The following groups are read: mysql_secure_installation mysql client
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit.
--no-defaults Don't read default options from any option file,
except for login file.
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#
Also read groups with concat(group, suffix)
--login-path=# Read this path from the login file.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
host localhost
port 0
socket (No default value)
ssl TRUE
ssl-verify-server-cert FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-crl (No default value)
ssl-crlpath (No default value)
tls-version (No default value)
user root
use-default FALSE
特に欲しいと思っていた--password
(-p
)と、--use-default
(-D
)があるのを発見。
これで mysql_secure_installation の中身が何をやってるんだとか調べる必要がなくなって一手間減った感じです。
なお、試しに MySQL 5.6 で同じように叩いたら有無を言わさずスクリプトが走った...
$ mysql_secure_installation --help (feature/deploy) <U>
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
ちょっと調べてみた
5.7 から mysql_secure_installation が単なる Perl スクリプトではなくなっていた。
$ file /usr/bin/mysql_secure_installation
/usr/bin/mysql_secure_installation: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped
$ file /usr/bin/mysql_secure_installation
/usr/bin/mysql_secure_installation: a /usr/bin/perl script text executable
マニュアルを見ると 5.7.2 で色々と増え、5.7.4 で待望の--use-default (-D)
が増えているではないですか。
MySQL :: MySQL 5.7 Reference Manual :: 5.4.4 mysql_secure_installation — Improve MySQL Installation Security
https://dev.mysql.com/doc/refman/5.7/en/mysql-secure-installation.html
公式の対応もかなり割りと前なので、これからは安心して無停止な mysql_secure_installation を自動化に組み込めますね!
気になったので後で調べたいメモ
- そもそも mysql_secure_installation も skip-grant-tables で実行できるんじゃなかろうか。