FreeBSD 11にMariaDB 10.3をインストール

sudo portmaster databases/mariadb103-server

===>  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

sudo DISABLE_VULNERABILITIES=yes portmaster databases/mariadb103-server --force-config

sudo mysql_upgrade -u ユーザー -p

$ 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
ampache.album                                      OK
ampache.artist                                     OK
ampache.broadcast                                  OK
ampache.catalog                                    OK
ampache.catalog_local                              OK
ampache.catalog_remote                             OK
ampache.channel                                    OK
ampache.clip                                       OK
ampache.daap_session                               OK
ampache.democratic                                 OK
ampache.image                                      OK
ampache.ip_history                                 OK
ampache.label_asso                                 OK
ampache.license                                    OK
ampache.live_stream                                OK
ampache.localplay_httpq                            OK
ampache.localplay_mpd                              OK
ampache.localplay_shoutcast                        OK
ampache.movie                                      OK
ampache.now_playing                                OK
ampache.object_count                               OK
ampache.personal_video                             OK
ampache.player_control                             OK
ampache.playlist                                   OK
ampache.playlist_data                              OK
ampache.preference                                 OK
ampache.rating                                     OK
ampache.recommendation                             OK
ampache.recommendation_item                        OK
ampache.search                                     OK
ampache.session                                    OK
ampache.session_remember                           OK
ampache.session_stream                             OK
ampache.share                                      OK
ampache.song                                       OK
ampache.song_data                                  OK
ampache.song_preview                               OK
ampache.stream_playlist                            OK
ampache.tag                                        OK
ampache.tag_map                                    OK
ampache.tag_merge                                  OK
ampache.tmp_browse                                 OK
ampache.tmp_playlist                               OK
ampache.tmp_playlist_data                          OK
ampache.tvshow                                     OK
ampache.tvshow_episode                             OK
ampache.tvshow_season                              OK
ampache.update_info                                OK
ampache.user                                       OK
ampache.user_catalog                               OK
ampache.user_flag                                  OK
ampache.user_follower                              OK
ampache.user_preference                            OK
ampache.user_pvmsg                                 OK
ampache.user_shout                                 OK
ampache.user_vote                                  OK
ampache.video                                      OK
ampache.wanted                                     OK
information_schema
nextcloud
nextcloud.oc_accounts                              OK
nextcloud.oc_activity                              OK
nextcloud.oc_activity_mq                           OK
nextcloud.oc_addressbookchanges                    OK
nextcloud.oc_addressbooks                          OK
nextcloud.oc_admin_sections                        OK
nextcloud.oc_admin_settings                        OK
nextcloud.oc_appconfig                             OK
nextcloud.oc_authtoken                             OK
nextcloud.oc_bruteforce_attempts                   OK
nextcloud.oc_calendarchanges                       OK
nextcloud.oc_calendarobjects                       OK
nextcloud.oc_calendarobjects_props                 OK
nextcloud.oc_calendars                             OK
nextcloud.oc_calendarsubscriptions                 OK
nextcloud.oc_cards                                 OK
nextcloud.oc_cards_properties                      OK
nextcloud.oc_comments                              OK
nextcloud.oc_comments_read_markers                 OK
nextcloud.oc_credentials                           OK
nextcloud.oc_dav_shares                            OK
nextcloud.oc_external_applicable                   OK
nextcloud.oc_external_config                       OK
nextcloud.oc_external_mounts                       OK
nextcloud.oc_external_options                      OK
nextcloud.oc_federated_reshares                    OK
nextcloud.oc_file_locks                            OK
nextcloud.oc_filecache                             OK
nextcloud.oc_files_trash                           OK
nextcloud.oc_flow_checks                           OK
nextcloud.oc_flow_operations                       OK
nextcloud.oc_group_admin                           OK
nextcloud.oc_group_user                            OK
nextcloud.oc_groups                                OK
nextcloud.oc_jobs                                  OK
nextcloud.oc_mimetypes                             OK
nextcloud.oc_mounts                                OK
nextcloud.oc_notifications                         OK
nextcloud.oc_notifications_pushtokens              OK
nextcloud.oc_oauth2_access_tokens                  OK
nextcloud.oc_oauth2_clients                        OK
nextcloud.oc_preferences                           OK
nextcloud.oc_privatedata                           OK
nextcloud.oc_properties                            OK
nextcloud.oc_schedulingobjects                     OK
nextcloud.oc_share                                 OK
nextcloud.oc_share_external                        OK
nextcloud.oc_storages                              OK
nextcloud.oc_systemtag                             OK
nextcloud.oc_systemtag_group                       OK
nextcloud.oc_systemtag_object_mapping              OK
nextcloud.oc_trusted_servers                       OK
nextcloud.oc_twofactor_backupcodes                 OK
nextcloud.oc_users                                 OK
nextcloud.oc_vcategory                             OK
nextcloud.oc_vcategory_to_object                   OK
performance_schema
school
school.student                                     OK
world
world.city                                         OK
world.country                                      OK
world.countrylanguage                              OK
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK

DBのデータ保存先がZFSの場合は、FSのrecordsizeプロパティを調整することで性能向上が図れるらしい。まぁ、個人用サーバでは目に見えるほどの違いは出なさそうだが、勉強も兼ねて設定してみる。

InnoDBの場合、データファイルは16kB、ログファイルは128kBブロック

zfs create -o recordsize=16k zhome/home/mysql zfs create -o recordsize=128k zhome/home/mysql/logs zfs get record size NAME PROPERTY VALUE SOURCE zhome/ROOT/home recordsize 128K default zhome/ROOT/home/mysql recordsize 16K local zhome/ROOT/home/mysql/logs recordsize 128K local

–datadir=/usr/home/mysql

mysq_install_dbコマンドで初期設定を行う。

mkdir /usr/home/

/home> sudo mysql_install_db

FATAL ERROR: Could not find ./bin/my_print_defaults

If you compiled from source, you need to either run 'make install' to copy the software into the correct location ready for operation. If you don't want to do a full install, you can use the –srcddir option to only install the mysql database and privilege tables

If you are using a binary release, you must either be at the top level of the extracted archive, or pass the –basedir option pointing to that location.

The latest information about mysql_install_db is available at https://mariadb.com/kb/en/installing-system-tables-mysql_install_db Decomo@Freyja /u/home> cd /usr/local/ Decomo@Freyja /u/local> mysql_install_db WARNING: The host 'Freyja.he.decomo.info' could not be looked up with ./bin/resolveip. 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 ! mkdir: ./data: Permission denied Fatal error Can't create database directory './data'

The latest information about mysql_install_db is available at

sudo mkdir -p /home/mysql/data
sudo chown -R mysql:mysql /home/mysql
sudo cp -p /usr/local/share/mysql/my-huge.cnf /usr/local/etc/my.cnf

最近（といってもここ数年）のマシンならmy-huge.cnfで問題ないと思われる。

データ置き場を変えてるので、mysql_dbdirの設定も忘れずに。

mysql_enable="YES"
mysql_dbdir="/usr/home/mysql/data/"

/usr/local/etc/my.cnfの各セクションに追加。

[client]
...
default-character-set=utf8

[mysqld]
...
character-set-server=utf8

$ sudo /usr/local/etc/rc.d/mysql-server start
$ sudo 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!

ついでに文字コード設定が正しく効いてるかも確認。

$ mysql -u root -p
Enter password: 
MariaDB [(none)]> show variables like "chara%";
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)