FreeBSD 11にMariaDB 10.3をインストール [のふ処|NOFUDOKORO]

この文書は読取専用です。文書のソースを閲覧することは可能ですが、変更はできません。もし変更したい場合は管理者に連絡してください。
====== FreeBSD 11にMariaDB 10.3をインストール ======

===== 試した環境 =====

  * FreeBSD 11.2-RELEASE-p4
  * MariaDB 10.3.8

===== インストール =====

ports好きなのでportsから入れる。

FreeBSDのMySQL/MariaDBインストール事例でよく見かけるWITH_CHARSET, WITH_XCHARSETオプションは最早意味がないので付けない。参考：[[blog:2018:2018-10-19]]

<code bash>
$ sudo portmaster databases/mariadb103-server
</code>

そしたら「脆弱性が報告されてまっせ」と言われて失敗した。脆弱性大杉問題…。

<code>
===>  mariadb103-server-10.3.8_2 has known vulnerabilities:
mariadb103-server-10.3.8_2 is vulnerable:
MySQL -- multiple vulnerabilities
CVE: CVE-2018-3082
CVE: CVE-2018-3084
CVE: CVE-2018-2767
CVE: CVE-2018-3066
CVE: CVE-2018-3056
CVE: CVE-2018-3058
CVE: CVE-2018-3075
CVE: CVE-2018-3063
CVE: CVE-2018-3067
CVE: CVE-2018-3061
CVE: CVE-2018-3080
CVE: CVE-2018-3078
CVE: CVE-2018-3077
CVE: CVE-2018-3054
CVE: CVE-2018-3079
CVE: CVE-2018-3071
CVE: CVE-2018-3081
CVE: CVE-2018-3074
CVE: CVE-2018-3073
CVE: CVE-2018-3065
CVE: CVE-2018-3060
CVE: CVE-2018-3070
CVE: CVE-2018-0739
CVE: CVE-2018-3064
WWW: https://vuxml.FreeBSD.org/freebsd/909be51b-9b3b-11e8-add2-b499baebfeaf.html

1 problem(s) in the installed packages found.
=> Please update your ports tree and try again.
=> Note: Vulnerable ports are marked as such even if there is no update available.
=> If you wish to ignore this vulnerability rebuild with 'make DISABLE_VULNERABILITIES=yes'
*** Error code 1
</code>

ログにあるようにDISABLE_VULNERABILITIES=yesを付けて再度インストール実行。

<code bash>
$ sudo DISABLE_VULNERABILITIES=yes portmaster databases/mariadb103-server
</code>

===== 新規セットアップ =====

==== my.cnfのサンプルがない？ ====

portsから入れると、my.cnfやお馴染のサンプルファイルmy-medium.cnfやmy-default.cnfなどは一切インストールされないようだ。[[https://mariadb.com/kb/en/library/configuring-mariadb-with-mycnf/|MariaDBの公式サイト]]によれば「If no my.cnf file is found, the default values are used for all variables.」とのことなので、my.cnfがなくても特に問題はなさそうだ。

とはいえ、DBのデータ置き場を変更するのでmy.cnfは作りますけどね。

なおmy.cnf置き場は''/usr/local/etc/my.cnf''である。mysqladmin --help コマンドで確認することもできる。
<code bash>
$ mysqladmin --help
mysqladmin  Ver 9.1 Distrib 10.3.8-MariaDB, for FreeBSD11.2 on amd64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Administration program for the mysqld daemon.
Usage: mysqladmin [OPTIONS] command command....

Default options are read from the following files in the given order:
/usr/local/etc/my.cnf ~/.my.cnf　←★これ
（略）
</code>

==== my.cnfの作成 ====

DBのキャラセットは絵文字も扱えるutf8mb4とし、InnoDBのログファイルの置き場も指定する。この辺はお好みで。

<code>
[mysqld]
character-set-server = utf8mb4

# File-Per-Tableモードではデータ置き場を指定しても意味がない
#innodb_data_home_dir = /usr/home/mysql/innodb
innodb_log_group_home_dir = /usr/home/mysql/innodb-logs

[client]
default-character-set = utf8mb4
</code>

==== ファイルシステムの作成と最適化 ====

DBのデータ保存先がZFSの場合は、FSのrecordsizeプロパティを調整することで性能向上が図れるらしい。File-Per-Tableモード
ではストレージエンジンごとのファイル置き場を厳密に分けられない、かつ、個人用途では目に見えるほどの違いは出なさそうだが、勉強も兼ねて設定してみる。

^  データ種  ^  recordsize  ^  primarycache  ^  置き場  ^  備考  ^
|MyISAM|  8k|all|zhome/ROOT/home/mysql/data|InnoDBデータ置き場と共用なので設定不可|
|InnoDB（データ）|  16k|metadata|zhome/ROOT/home/mysql/data||
|InnoDB（ログ）|  128k|metadata|zhome/ROOT/home/mysql/innodb-logs||

<code bash>
# zfs create zhome/ROOT/home/mysql
# zfs create -o recordsize=16k  -o primarycache=metadata zhome/ROOT/home/mysql/data
# zfs create -o recordsize=128k -o primarycache=metadata zhome/ROOT/home/mysql/innodb-logs
$ zfs get recordsize
NAME                              PROPERTY    VALUE    SOURCE
zhome/ROOT/home                   recordsize  128K     default
zhome/ROOT/home/mysql             recordsize  128K     default
zhome/ROOT/home/mysql/data        recordsize  16K      local
zhome/ROOT/home/mysql/innodb-logs recordsize  128K     local

# chown -R mysql:mysql /usr/home/mysql
</code>

==== MariaDBの初期化 ====

''mysq_install_db''コマンドでDBを初期化する。引数は以下の通り。

|--user|DBの実行ユーザー|
|--basedir|DBのバイナリがインストールされているディレクトリ(/usr/local/bin)の親ディレクトリを指定|
|--datadir|DBのデータ置き場を指定|

<code bash>
$ sudo mysql_install_db --user=mysql --basedir=/usr/local --datadir=/usr/home/mysql/data
This probably means that your libc libraries are not 100 % compatible
with this binary MariaDB version. The MariaDB daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MariaDB privileges !
Installing MariaDB/MySQL system tables in '/usr/home/mysql/data' ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system


PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

'/usr/local/bin/mysqladmin' -u root password 'new-password'
'/usr/local/bin/mysqladmin' -u root -h Freyja.he.decomo.info password 'new-password'

Alternatively you can run:
'/usr/local/bin/mysql_secure_installation'

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/usr/local' ; /usr/local/bin/mysqld_safe --datadir='/usr/home/mysql/data'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/local/mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/
</code>

==== 起動とセキュリティ設定 ====

上記初期化メッセージのとおり、初期化直後は管理者ユーザーのパスワードが未設定だったり、他にもセキュリティ上よろしくない所がある。素直に''mysql_secure_installation''を実行するが、まずはMariaDBを起動する。

rc.confの設定。DB置き場を変更したいので、mysql_dbdirでパスを指定。
<code>
mysql_enable="YES"
mysql_dbdir="/usr/home/mysql/data/"
</code>

MariaDB起動
<code>
# service mysql-server start
</code>

セキュリティ設定実行。冒頭で「それぞれの項目を良く読め」と書かれてるが、基本は全て「Y」でOK。
<code>
# 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): 
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] 
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] 
 ... 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] 
 ... Success!

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] 
 - 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] 
 ... 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!
</code>

==== 試しに繋いでみる ====

DBが正しく動いてるか実際に繋いでみる。ユーザーはrootで、パスワードは先ほどのセキュリティスクリプトで設定したものを入れる。ついでに文字コード設定が正しく効いてるかも確認。

<code>
# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.8-MariaDB FreeBSD Ports

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like "char%";
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8mb4                          |
| character_set_connection | utf8mb4                          |
| character_set_database   | utf8mb4                          |
| character_set_filesystem | binary                           |
| character_set_results    | utf8mb4                          |
| character_set_server     | utf8mb4                          |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.001 sec)
</code>

===== 以前のデータの移行 =====

以前のバージョンからの更新の場合、データの移行を行う。インストールまで出来たら''mysql_upgrade''コマンドを実行する。

<code>
$ sudo mysql_upgrade -u root -p
Enter password:
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.index_stats                                  OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.transaction_registry                         OK
mysql.user                                         OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names
Phase 6/7: Checking and upgrading tables
Processing databases
ampache
ampache.access_list                                OK
（中略）
information_schema
nextcloud
nextcloud.oc_accounts                              OK
（中略）
performance_schema
school
school.student                                     OK
world
world.city                                         OK
world.country                                      OK
world.countrylanguage                              OK
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK
</code>