差分
このページの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-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:// | ||
- | |||
- | 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 | ||
- | </ | ||
- | |||
- | ログにあるようにDISABLE_VULNERABILITIES=yesを付けて再度インストール指示。 | ||
- | |||
- | <code bash> | ||
- | $ sudo DISABLE_VULNERABILITIES=yes portmaster databases/ | ||
- | </ | ||
- | |||
- | |||
- | ==== 以前のデータの移行 ==== | ||
- | |||
- | 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 | ||
- | mysql.columns_priv | ||
- | mysql.db | ||
- | mysql.event | ||
- | mysql.func | ||
- | mysql.gtid_slave_pos | ||
- | mysql.help_category | ||
- | mysql.help_keyword | ||
- | mysql.help_relation | ||
- | mysql.help_topic | ||
- | mysql.host | ||
- | mysql.index_stats | ||
- | mysql.innodb_index_stats | ||
- | mysql.innodb_table_stats | ||
- | mysql.plugin | ||
- | mysql.proc | ||
- | mysql.procs_priv | ||
- | mysql.proxies_priv | ||
- | mysql.roles_mapping | ||
- | mysql.servers | ||
- | mysql.table_stats | ||
- | mysql.tables_priv | ||
- | mysql.time_zone | ||
- | mysql.time_zone_leap_second | ||
- | mysql.time_zone_name | ||
- | mysql.time_zone_transition | ||
- | mysql.time_zone_transition_type | ||
- | mysql.transaction_registry | ||
- | mysql.user | ||
- | Phase 2/7: Installing used storage engines... Skipped | ||
- | Phase 3/7: Fixing views | ||
- | Phase 4/7: Running ' | ||
- | Phase 5/7: Fixing table and database names | ||
- | Phase 6/7: Checking and upgrading tables | ||
- | Processing databases | ||
- | ampache | ||
- | ampache.access_list | ||
- | (中略) | ||
- | information_schema | ||
- | nextcloud | ||
- | nextcloud.oc_accounts | ||
- | (中略) | ||
- | performance_schema | ||
- | school | ||
- | school.student | ||
- | world | ||
- | world.city | ||
- | world.country | ||
- | world.countrylanguage | ||
- | Phase 7/7: Running 'FLUSH PRIVILEGES' | ||
- | OK | ||
- | </ | ||
- | |||
- | ==== 新規セットアップの場合 ==== | ||
- | |||
- | === my.cnfのサンプルがない? === | ||
- | |||
- | portsから入れると、my.cnfやお馴染のサンプルファイルmy-medium.cnfやmy-default.cnfなどは一切インストールされないようだ。[[https:// | ||
- | |||
- | とはいえ、DBのデータ置き場を変更するのでmy.cnfは作りますけどね。 | ||
- | |||
- | なおmy.cnf置き場は''/ | ||
- | <code bash> | ||
- | $ mysqladmin --help | ||
- | mysqladmin | ||
- | 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: | ||
- | / | ||
- | (略) | ||
- | </ | ||
- | |||
- | |||
- | === ZFSの最適化 === | ||
- | |||
- | DBのデータ保存先がZFSの場合は、FSのrecordsizeプロパティを調整することで性能向上が図れるらしい。まぁ、個人用サーバでは目に見えるほどの違いは出なさそうだが、勉強も兼ねて設定してみる。 | ||
- | |||
- | ^ データ種 | ||
- | |MyISAM| | ||
- | |InnoDB(データ)| | ||
- | |InnoDB(ログ)| | ||
- | |||
- | <code bash> | ||
- | # zfs create zhome/ | ||
- | # zfs create -o recordsize=16k | ||
- | # zfs create -o recordsize=128k -o primarycache=metadata zhome/ | ||
- | $ zfs get recordsize | ||
- | NAME PROPERTY | ||
- | zhome/ | ||
- | zhome/ | ||
- | zhome/ | ||
- | zhome/ | ||
- | |||
- | chown -R mysql:mysql / | ||
- | </ | ||
- | |||
- | === my.cnfの作成 === | ||
- | |||
- | < | ||
- | [mysqld] | ||
- | character-set-server = utf8mb4 | ||
- | |||
- | # File-Per-Tableモードではデータ置き場を指定しても意味がない | ||
- | # | ||
- | innodb_log_group_home_dir = / | ||
- | |||
- | [client] | ||
- | default-character-set = utf8mb4 | ||
- | </ | ||
- | |||
- | === MariaDBの初期化 === | ||
- | |||
- | mysq_install_dbコマンドでMariaDBを初期化する。 | ||
- | |||
- | < | ||
- | sudo mysql_install_db --user=mysql --basedir=/ | ||
- | 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/ | ||
- | OK | ||
- | |||
- | To start mysqld at boot time you have to copy | ||
- | support-files/ | ||
- | |||
- | |||
- | PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER ! | ||
- | To do so, start the server, then issue the following commands: | ||
- | |||
- | '/ | ||
- | '/ | ||
- | |||
- | Alternatively you can run: | ||
- | '/ | ||
- | |||
- | which will also give you the option of removing the test | ||
- | databases and anonymous user created by default. | ||
- | strongly recommended for production servers. | ||
- | |||
- | See the MariaDB Knowledgebase at http:// | ||
- | MySQL manual for more instructions. | ||
- | |||
- | You can start the MariaDB daemon with: | ||
- | cd '/ | ||
- | |||
- | You can test the MariaDB daemon with mysql-test-run.pl | ||
- | cd '/ | ||
- | |||
- | Please report any problems at http:// | ||
- | |||
- | The latest information about MariaDB is available at http:// | ||
- | You can find additional information about the MySQL part at: | ||
- | http:// | ||
- | Consider joining MariaDB' | ||
- | https:// | ||
- | </ | ||
- | |||
- | === 起動とセキュリティ設定 === | ||
- | |||
- | 上記メッセージのとおり、初期化直後は管理者ユーザーのパスワードが未設定だったり、他にもセキュリティ上よろしくない所がある。素直にmysql_secure_installationする。MariaDBが動いてる必要がある。 | ||
- | |||
- | rc.confの設定 | ||
- | < | ||
- | mysql_enable=" | ||
- | mysql_dbdir="/ | ||
- | </ | ||
- | |||
- | MariaDB起動 | ||
- | < | ||
- | # service 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' | ||
- | 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. | ||
- | production environment. | ||
- | |||
- | Remove anonymous users? [Y/n] | ||
- | ... Success! | ||
- | |||
- | Normally, root should only be allowed to connect from ' | ||
- | 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 ' | ||
- | access. | ||
- | 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: | ||
- | Welcome to the MariaDB monitor. | ||
- | 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 ' | ||
- | |||
- | MariaDB [(none)]> | ||
- | +--------------------------+----------------------------------+ | ||
- | | Variable_name | ||
- | +--------------------------+----------------------------------+ | ||
- | | character_set_client | ||
- | | character_set_connection | utf8mb4 | ||
- | | character_set_database | ||
- | | character_set_filesystem | binary | ||
- | | character_set_results | ||
- | | character_set_server | ||
- | | character_set_system | ||
- | | character_sets_dir | ||
- | +--------------------------+----------------------------------+ | ||
- | 8 rows in set (0.001 sec) | ||
- | </ | ||
- | |||