# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, 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): # <= root ユーザの既存パスワード：空エンター
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] # <= root ユーザのパスワード設定： Y 応答
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB 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? [Y/n] # <= 匿名ユーザの削除： 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? [Y/n] n # <= root のリモートログイン： n 応答（Y でもよい）
... skipping.
By default, MariaDB 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? [Y/n] # <= テストデータベースの削除： 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? [Y/n] # <= 特権情報のリロード： Y 応答
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
# mysql -u root -p
Enter password: # <= MariaDB root パスワード
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.1.23-MariaDB-9+deb9u1 Debian 9.0
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases; # <= 存在するデータベースを確認してみる
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> show engines; # <= 存在するストレージエンジンを確認してみる
+--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES |
| Mroonga | YES | CJK-ready fulltext search, column store | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
MariaDB [(none)]> exit
Bye
## This group is read by the client library# Use it for options that affect all clients, but not the server#[client]# Default is Latin1, if you need UTF-8 set this (also in server section)port= 3306
socket= /var/run/mysqld/mysqld.sock
default-character-set = utf8mb4
# Example of client certificate usage# ssl-cert=/etc/mysql/client-cert.pem# ssl-key=/etc/mysql/client-key.pem## Allow only TLS encrypted connections# ssl-verify-server-cert=on# This group is *never* read by mysql client library, though this# /etc/mysql/mariadb.cnf.d/client.cnf file is not read by Oracle MySQL# client anyway.# If you use the same .cnf file for MySQL and MariaDB,# use it for MariaDB-only client options[client-mariadb]
/etc/mysql/mariadb.conf.d/50-mysql-clients.cnf
12345678910111213141516171819202122232425262728
## These groups are read by MariaDB command-line tools# Use it for options that affect only one utility#[mysql]# Default is Latin1, if you need UTF-8 set this (also in server section)default-character-set = utf8mb4
no-auto-rehash
show-warnings
prompt=\u@\h:\d\_\R:\m:\\s>\_pager="less -n -i -F -X -E"[mysql_upgrade][mysqladmin][mysqlbinlog][mysqlcheck][mysqldump][mysqlimport][mysqlshow][mysqlslap]
# NOTE: This file is read only by the traditional SysV init script, not systemd.# MariaDB systemd does _not_ utilize mysqld_safe nor read this file.## For similar behaviour, systemd users should create the following file:# /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf## To achieve the same result as the default 50-mysqld_safe.cnf, please create# /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf# with the following contents:## [Service]# User=mysql# StandardOutput=syslog# StandardError=syslog# SyslogFacility=daemon# SyslogLevel=err# SyslogIdentifier=mysqld## For more information, please read https://mariadb.com/kb/en/mariadb/systemd/#[mysqld_safe]# This will be passed to all mysql clients# It has been reported that passwords should be enclosed with ticks/quotes# especially if they contain "#" chars...# Remember to edit /etc/mysql/debian.cnf when changing the socket location.socket= /var/run/mysqld/mysqld.sock
nice= 0
skip_log_error
syslog
## These groups are read by MariaDB server.# Use it for options that only the server (but not clients) should see## See the examples of server my.cnf files in /usr/share/mysql/## this is read by the standalone daemon and embedded servers[server]# this is only for the mysqld standalone daemon[mysqld]## * Basic Settings#user= mysql
pid-file = /var/run/mysqld/mysqld.pid
socket= /var/run/mysqld/mysqld.sock
port= 3306
basedir= /usr
datadir= /var/lib/mysql
tmpdir= /tmp
lc_messages= en_US # Default: en_USlc-messages-dir = /usr/share/mysql
skip-external-locking
performance_schema= OFF # Default: ONsql_mode=''default_storage_engine= InnoDB # Default: InnoDBskip-character-set-client-handshake
# Instead of skip-networking the default is now to listen only on# localhost which is more compatible and is not less secure.bind-address = 0.0.0.0 # 127.0.0.1## * Fine Tuning#key_buffer_size= 8M # Default: 8M (for MyISAM)myisam_sort_buffer_size= 8M # Default: 8M (for MyISAM)sort_buffer_size= 8M # Default: 2M (通常時:8M, ALTER TABLE 時:64M)read_buffer_size= 8M # Default: 128Kread_rnd_buffer_size= 8M # Default: 256Kjoin_buffer_size= 8M # Default: 128Kmax_allowed_packet= 128M # Default: 1Mthread_stack= 288K # Default: 192K(32bit), 288K(64bit)thread_cache_size=40# Default: 0 (max_connections の 1/3 程度?)table_open_cache=400# Default: 400 (同時接続数 * テーブル数?)table_definition_cache=400# Default: -1:autosized = 400 + (table_open_cache / 2tmp_table_size= 16M # Default: System 依存 (for Memory, <= max_heap_table_size)max_heap_table_size= 32M # Default: 16M (for Memory, >= tmp_table_size)bulk_insert_buffer_size= 64M
# This replaces the startup script and checks MyISAM tables if needed# the first time they are touchedmyisam_recover_options= BACKUP
max_connections=100# Default: 151max_connect_errors=100# Default: 100max_user_connections=0# Default: 0#table_cache = 64#thread_concurrency = 10wait_timeout=300# Default: 28800 (通常時:300, データインポート時:3600程度)interactive_timeout=3600# Default: 28800connect_timeout=30# Default: 10net_read_timeout=3600# Default: 30net_write_timeout=3600# Default: 60## * Query Cache Configuration#query_cache_limit= 8M # Default: 1Mquery_cache_size= 32M # Default: 0 (通常時: 32M, データインポート時: 0)## * Logging and Replication#log_output= FILE # Default: FILElog_warnings=1# Default: 1# Both location gets rotated by the cronjob.# Be aware that this log type is a performance killer.# As of 5.1 you can enable the log at runtime!#general_log_file = /var/log/mysql/mysql.log # デバッグ時に有効化#general_log = 1 # デバッグ時に有効化## Error log - should be very few entries.#log_error= /var/log/mysql/error.log
## Enable the slow query log to see queries with especially long duration#slow_query_log_file = /var/log/mysql/mariadb-slow.log # デバッグ時に有効化#slow_query_log = 1 # デバッグ時に有効化#long_query_time = 30 # デバッグ時に有効化#log_slow_rate_limit = 1000#log_slow_verbosity = query_plan#log-queries-not-using-indexes## The following can be used as easy to replay backup logs or for replication.# note: if you are setting up a replication slave, see README.Debian about# other settings you may need to change.server-id =102# レプリケーション時 (1 〜 2^32 - 1)log_bin= /var/log/mysql/mysql-bin.log
expire_logs_days= 10
max_binlog_size= 100M
binlog_format= MIXED
log_bin_trust_function_creators=1# Default: 0 (for TRIGGER)#binlog_do_db = include_database_name#binlog_ignore_db = exclude_database_name## * InnoDB## InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.# Read the manual for more InnoDB related options. There are many!innodb_data_file_path= ibdata1:1G #:autoextendinnodb_autoextend_increment=64# Default: 64innodb_file_per_table= 1
innodb_file_format= Barracuda # Default: Antelopeinnodb_file_format_max= Barracuda # Default: Antelopeinnodb_large_prefix=1# Default: 0innodb_buffer_pool_size= 768M # Default: 128M (innodb_log_files_in_group * innodb_log_file_size < innodb_buffer_pool_size)innodb_buffer_pool_instances=16# Default: autosized(32bit), 8(64bit) (innodb_buffer_pool_size >= 1G の場合にのみ有効)innodb_sort_buffer_size= 32M # Default: 1M (通常時:8M, ALTER TABLE 時:64M)innodb_thread_concurrency=4# Default: 0 (Defalut 推奨? CPU数 * ディスク数 * 2 が最適?)innodb_thread_sleep_delay=10000# Default: 10000 (単位:マイクロ秒)innodb_commit_concurrency=4# Default: 0 (Default 推奨?)innodb_write_io_threads=8# Default: 4 (1 - 64)innodb_read_io_threads=8# Default: 4 (1 - 64)innodb_log_buffer_size= 32M # Default: 8M (通常時:32M, データインポート時:256M程度)innodb_log_group_home_dir= /var/lib/mysql
innodb_log_files_in_group=2# Default: 2 (変更注意!)innodb_log_file_size= 256M # Default: 48M (変更注意!)# <= innodb_log_files_in_group innodb_log_file_size < innodb_buffer_pool_size# [変更方法](http://dev.mysq.com/doc/refman/5.6/ja/innodb-data-log-reconfiguration.html)innodb_max_dirty_pages_pct=90# Default: 75(%)# <= 小さい値：低速＆安定、大きい値：高速＆不安定(?)innodb_io_capacity=256# Default: 200 (100 - 2^64-1)innodb_io_capacity_max=512# Default: 200 (100 - 2^64-1)innodb_lru_scan_depth=2048# Default: 1024 (100 - 2^32-1(32bit))innodb_flush_method= O_DIRECT # Default: Not setinnodb_flush_log_at_trx_commit=1# Default: 1 (データインポート時: 2 or 0)innodb_doublewrite=1# Default: 1 (データインポート時: 0)## * Security Features## Read the manual, too, if you want chroot!# chroot = /var/lib/mysql/## For generating SSL certificates you can use for example the GUI tool "tinyca".## ssl-ca=/etc/mysql/cacert.pem# ssl-cert=/etc/mysql/server-cert.pem# ssl-key=/etc/mysql/server-key.pem## Accept only connections using the latest and most secure TLS protocol version.# ..when MariaDB is compiled with OpenSSL:# ssl-cipher=TLSv1.2# ..when MariaDB is compiled with YaSSL (default in Debian):# ssl=on## * Character sets## MySQL/MariaDB default is Latin1, but in Debian we rather default to the full# utf8 4-byte character set. See also client.cnf#character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
## * Event Scheduler#event_scheduler=1# Default: 0## * Unix socket authentication plugin is built-in since 10.0.22-6## Needed so the root database user can authenticate without a password but# only when running as the unix root user.## Also available for other users if required.# See https://mariadb.com/kb/en/unix_socket-authentication-plugin/# this is only for embedded server[embedded]# This group is only read by MariaDB servers, not by MySQL.# If you use the same .cnf file for MySQL and MariaDB,# you can put MariaDB-only options here[mariadb]# This group is only read by MariaDB-10.1 servers.# If you use the same .cnf file for MariaDB of different versions,# use this group for options that older servers don't understand[mariadb-10.1]