差分

このページの2つのバージョン間の差分を表示します。

--- memo:working [2018-10-23 17:47]
Decomo 削除
+++ — (現在)
@@ 行 1: / 行 1: @@
-====== FreeBSD 11にMariaDB 10.3をインストール ======
-===== 環境 =====
-===== インストール =====
-ports好きなのでportsから入れる。
-<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
-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>
-==== 以前のデータの移行 ====
-sudo mysql_upgrade -u ユーザー -p
-<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>
-==== 新規セットアップの場合 ====
-=== 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>
-=== ZFSの最適化 ===
-DBのデータ保存先がZFSの場合は、FSのrecordsizeプロパティを調整することで性能向上が図れるらしい。まぁ、個人用サーバでは目に見えるほどの違いは出なさそうだが、勉強も兼ねて設定してみる。
-^  データ種  ^  recordsize  ^  primarycache  ^  置き場  ^  備考  ^
-|MyISAM|  8k|all|zhome/ROOT/home/mysql/data||
-|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>
-=== my.cnfの作成 ===
-<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>
-=== MariaDBの初期化 ===
-mysq_install_dbコマンドでMariaDBを初期化する。
-<code>
-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の設定
-<code>
-mysql_enable="YES"
-mysql_dbdir="/usr/home/mysql/data/"
-</code>
-MariaDB起動
-<code>
-# service mysql-server start
-セキュリティ設定実行。
-<code>
-$ 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!
-</code>
-==== 試しに繋いでみる ====
-ついでに文字コード設定が正しく効いてるかも確認。
-<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/ |
-+--------------------------+----------------------------------+
-rows in set (0.001 sec)
-</code>