とーますメモ

Ruby on Rails / Goなどの学習メモ

【Ansible】Ubuntu 18.04でのMySQL設定

Ansible MySQL Ubuntu

MySQLの設定ファイルであるmy.cnfの場所の確認方法

$ mysql --help | grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

左から順に読み込まれる。

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

by mysql --help

要は以下の順

① /etc/my.cnf
② /etc/mysql/my.cnf
③ ~/.my.cnf

ただし、同じ設定値が後続のファイルにある場合、後続の設定値で上書きされるので注意！
my.cnf の読み込む順番でハマったのでまとめる - Qiita
第31回 MySQLのオプションファイル my.cnfの豆知識［その1］：MySQL道普請便り｜gihyo.jp … 技術評論社
my.cnfに設定したsql_modeが効かなくて困った話

※1 設定ファイルを見つけるコマンド

$ sudo find / -name "*my*cnf"

※2 有効な設定オプション一覧を確認するコマンド

$ my_print_defaults mysqld


MySQLをインストール後、①と③は存在していないが②は存在している。
シンプルに②の内容を気にせず設定をするのであれば、①に新規で設定を書いてもよいが
自分の場合は、既にある設定をベースに、必要な設定を上書きする方式で設定を行いため
②のファイルを変更するやり方を選択する。

※ ちなみに~/.my.cnfについては以下のような情報を見つけた。

To keep the things simple place all custom server settings into the /etc/my.cnf file. Some user-specific options can be stored in the ~/.my.cnf where ~/ means "the homedir for the given system user". Also note the leading dot in the user-specific file name. That file is used for command-line client utilities like mysql or mysqldump only, not for server. Therefore the [mysqld] section can be completely omitted in the user-specific config file.

mysql - Where should I store a custom ".my.cnf" file? - Database Administrators Stack Exchange
要は、~/.my.cnfの設定は、mysqlやmysqldumpなどのコマンド使用時のみ有効であり、mysql全体の設定としては有効ではないとのことらしい。
そして設定の中で比重が多い[mysqld]の設定は、完全に除外されるとのこと。まあなぜこの設定が各ユーザごとに設定が可能なホームディレクトリに設定できるのかを
考えれば納得が行く。じゃあどういう場合に使用するのかというと、以下のリンクのようなIDやパスワードを省略してログインしたい場合などがある。
.my.cnf - mysql user & password


②のファイルの中身を見ると以下の記述がある。

#
# The MySQL database server configuration file.
#

.....

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

「!includedir」というディレクティブがあるが、これは指定されたディレクトリ内の.cnfファイルを設定ファイルとして読み込むという意味。
各ディレクトリを見たが、通常設定ファイルが書かれているファイルは「/etc/mysql/mysql.conf.d/mysqld.cnf」だった。
ただし設定を変更または追記するのに、このファイルを弄るのではなく、設定が後続の設定で上書きされる仕組みを活かし
「/etc/mysql/my.cnf」に追記する方式で設定を記述する。

tasks/main.yml

---
# @see https://github.com/bennojoy/mysql/blob/master/tasks/main.yml

# @see https://stackoverflow.com/questions/42267299/ansible-install-mysql-5-7-set-root-user-password?rq=1
- name: Specify MySQL root password before installing
  debconf: name='mysql-server' question='mysql-server/root_password' value='{{mysql_root_user_pass | quote}}' vtype='password'
  tags: mysql

- name: Confirm MySQL root password before installing
  debconf: name='mysql-server' question='mysql-server/root_password_again' value='{{mysql_root_user_pass | quote}}' vtype='password'
  tags: mysql

- name: Install the mysql packages in Debian derivatives
  apt:
    name: ['mysql-server']
    state: present
  tags: mysql

# select user,host,authentication_string from mysql.user;
#
# @see http://docs.ansible.com/ansible/latest/modules/mysql_user_module.html
# check_implicit_admin: Check if mysql allows login as root/nopassword before trying supplied credentials.
- name: remove anonymous users by empty name
  mysql_user: name='' host_all=yes login_user=root login_password={{ mysql_root_user_pass }} check_implicit_admin=yes state=absent

- name: Create the database's
  mysql_db: name={{ item.name }} login_user=root login_password={{ mysql_root_user_pass }} encoding=utf8 state=present
  with_items:
    - "{{ mysql_db }}"
  when: mysql_db|lower() != 'none'
  tags: mysql

- name: Create the database users
  mysql_user:
    name:                 "{{ item.name }}"
    password:             "{{ item.pass }}"
    priv:                 '{{ item.priv|default("*.*:ALL") }}'
    state:                present
    host:                 '{{ item.host | default("localhost") }}'
    login_password:       "{{ mysql_root_user_pass }}"
    check_implicit_admin: yes
  with_items:
    - "{{ mysql_users }}"
  when: mysql_users|lower() != 'none'
  tags: [mysql, mysql_user]

- name: Append my.cnf into {{ mysql_config_file }}
  blockinfile:
    dest: "{{ mysql_config_file }}"
    block: "{{ lookup('template', 'cnf.j2') }}"
    marker: "# {mark} ANSIBLE MANAGED BLOCK"
  notify: restart mysql
  tags: [mysql, mysql_config]

- name: Create slow query log file (if configured).
  command: "touch {{ mysql_slow_query_log_file }}"
  args:
    creates: "{{ mysql_slow_query_log_file }}"
    warn: false
  when: mysql_slow_query_log_enabled
  tags: [mysql, mysql_config]

- name: Set ownership on slow query log file (if configured).
  file:
    path: "{{ mysql_slow_query_log_file }}"
    state: file
    owner: mysql
    group: mysql
    mode: 0640
  when: mysql_slow_query_log_enabled
  tags: [mysql, mysql_config]

- name: Start the mysql services
  service: name=mysql state=started enabled=yes
  tags: mysql

# @see: https://qiita.com/park-jh/items/34e6434d71e685a48f07#mysqld---initialize-insecure%E3%81%AE%E5%A0%B4%E5%90%88
- name: run mysql_secure_installation
  command: mysql_secure_installation -u root -p'{{ mysql_root_user_pass }}' -D
  changed_when: false
  tags: mysql

defaults/main.yml

---

# @see: https://qiita.com/mamy1326/items/9c5eaee3c986cff65a55
# @see: https://github.com/geerlingguy/ansible-role-mysql

mysql_config_file: /etc/mysql/my.cnf

mysql_sql_mode: ''

# Slow query log settings.
mysql_slow_query_log_enabled: true
mysql_slow_query_log_file: /var/log/mysql-slow.log
mysql_slow_query_time: "2"



# Memory settings (default values optimized ~512MB RAM).
mysql_key_buffer_size: "256M"
mysql_max_allowed_packet: "64M"
mysql_table_open_cache: "256"
mysql_sort_buffer_size: "1M"
mysql_read_buffer_size: "1M"
mysql_read_rnd_buffer_size: "4M"
mysql_myisam_sort_buffer_size: "64M"
mysql_thread_cache_size: "8"
mysql_query_cache_type: "0"
mysql_query_cache_size: "16M"
mysql_query_cache_limit: "1M"
mysql_max_connections: "151"              # default: 151
mysql_tmp_table_size: "16M"
mysql_max_heap_table_size: "16M"
mysql_group_concat_max_len: "1024"
mysql_join_buffer_size: "262144"



# InnoDB settings.
mysql_innodb_file_per_table: "1"
# Set .._buffer_pool_size up to 80% of RAM but beware of setting too high.    ex) server memory: 8G => 6G
mysql_innodb_buffer_pool_size: "1600M"
# Set .._log_file_size to 25% of buffer pool size. but maximum size is 64M    ex) innodb_buffer_pool_size: 6G => 1.25G
mysql_innodb_log_file_size: "64M"
mysql_innodb_log_buffer_size: "16M"
mysql_innodb_flush_log_at_trx_commit: "1"
mysql_innodb_lock_wait_timeout: "50"

templates/cnf.j2

[mysqld]
{% if mysql_sql_mode %} sql_mode = {{ mysql_sql_mode }} {% endif %}

{% if mysql_slow_query_log_enabled %}
# Slow query log configuration.
slow_query_log = 1
slow_query_log_file = {{ mysql_slow_query_log_file }}
long_query_time = {{ mysql_slow_query_time }}
{% endif %}



# Memory settings.
# key_buffer_size = {{ mysql_key_buffer_size }}
# max_allowed_packet = {{ mysql_max_allowed_packet }}
# table_open_cache = {{ mysql_table_open_cache }}
# sort_buffer_size = {{ mysql_sort_buffer_size }}
# read_buffer_size = {{ mysql_read_buffer_size }}
# read_rnd_buffer_size = {{ mysql_read_rnd_buffer_size }}
# myisam_sort_buffer_size = {{ mysql_myisam_sort_buffer_size }}
# thread_cache_size = {{ mysql_thread_cache_size }}
# query_cache_type = {{ mysql_query_cache_type }}
# query_cache_size = {{ mysql_query_cache_size }}
# query_cache_limit = {{ mysql_query_cache_limit }}
# max_connections = {{ mysql_max_connections }}
# tmp_table_size = {{ mysql_tmp_table_size }}
# max_heap_table_size = {{ mysql_max_heap_table_size }}
# group_concat_max_len = {{ mysql_group_concat_max_len }}
# join_buffer_size = {{ mysql_join_buffer_size }}



# InnoDB settings.
innodb_file_per_table = {{ mysql_innodb_file_per_table }}
innodb_buffer_pool_size = {{ mysql_innodb_buffer_pool_size }}
innodb_log_file_size = {{ mysql_innodb_log_file_size }}
innodb_log_buffer_size = {{ mysql_innodb_log_buffer_size }}
innodb_flush_log_at_trx_commit = {{ mysql_innodb_flush_log_at_trx_commit }}
innodb_lock_wait_timeout = {{ mysql_innodb_lock_wait_timeout }}


[参考]
How do I identify the MySQL my.cnf file?