search
LoginSignup
16
Help us understand the problem. What are the problem?

More than 5 years have passed since last update.

posted at

updated at

MySQL-5.7 で mysql_secure_installation の自動化が楽になっていた

きっかけ

  • 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-5.7.16
$ 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-5.6.34
$ 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 スクリプトではなくなっていた。

mysql-5.7.16
$ 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
mysql-5.6.34
$ 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 で実行できるんじゃなかろうか。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
16
Help us understand the problem. What are the problem?