pkgによるインストール
FreeBSDのバージョンが古いと言われたが、ミスマッチを無視することができるようなので無視して続行。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
$ sudo pkg install mysql57-server Updating FreeBSD repository catalogue... Fetching meta.conf: 100% 163 B 0.2kB/s 00:01 Fetching packagesite.txz: 100% 6 MiB 1.5MB/s 00:04 Processing entries: 0% Newer FreeBSD version for package libbigwig: To ignore this error set IGNORE_OSVERSION=yes - package: 1104001 - running kernel: 1102000 Ignore the mismatch and continue? [y/N]: y Processing entries: 100% FreeBSD repository update completed. 29008 packages processed. All repositories are up to date. Updating database digests format: 100% The following 8 package(s) will be affected (of 0 checked): New packages to be INSTALLED: cyrus-sasl: 2.1.27_1 libedit: 3.1.20210216,1 libevent: 2.1.12 liblz4: 1.9.3,1 mysql57-client: 5.7.34 mysql57-server: 5.7.34 perl5: 5.32.1_1 protobuf: 3.14.0,1 Number of packages to be installed: 8 The process will require 286 MiB more space. 35 MiB to be downloaded. Proceed with this action? [y/N]: y [1/8] Fetching mysql57-server-5.7.34.txz: 100% 14 MiB 2.5MB/s 00:06 [2/8] Fetching perl5-5.32.1_1.txz: 100% 15 MiB 3.1MB/s 00:05 [3/8] Fetching protobuf-3.14.0,1.txz: 100% 3 MiB 2.9MB/s 00:01 [4/8] Fetching libevent-2.1.12.txz: 100% 320 KiB 327.7kB/s 00:01 [5/8] Fetching libedit-3.1.20210216,1.txz: 100% 138 KiB 141.0kB/s 00:01 [6/8] Fetching mysql57-client-5.7.34.txz: 100% 2 MiB 1.1MB/s 00:02 [7/8] Fetching cyrus-sasl-2.1.27_1.txz: 100% 964 KiB 986.7kB/s 00:01 [8/8] Fetching liblz4-1.9.3,1.txz: 100% 131 KiB 134.1kB/s 00:01 Checking integrity... done (0 conflicting) [1/8] Installing perl5-5.32.1_1... [1/8] Extracting perl5-5.32.1_1: 100% [2/8] Installing protobuf-3.14.0,1... [2/8] Extracting protobuf-3.14.0,1: 100% [3/8] Installing libevent-2.1.12... [3/8] Extracting libevent-2.1.12: 100% [4/8] Installing libedit-3.1.20210216,1... [4/8] Extracting libedit-3.1.20210216,1: 100% [5/8] Installing cyrus-sasl-2.1.27_1... *** Added group `cyrus' (id 60) *** Added user `cyrus' (id 60) [5/8] Extracting cyrus-sasl-2.1.27_1: 100% [6/8] Installing liblz4-1.9.3,1... [6/8] Extracting liblz4-1.9.3,1: 100% [7/8] Installing mysql57-client-5.7.34... [7/8] Extracting mysql57-client-5.7.34: 100% [8/8] Installing mysql57-server-5.7.34... ===> Creating groups. Creating group 'mysql' with gid '88'. ===> Creating users Creating user 'mysql' with uid '88'. ===> Creating homedir(s) [8/8] Extracting mysql57-server-5.7.34: 100% ===== Message from cyrus-sasl-2.1.27_1: -- You can use sasldb2 for authentication, to add users use: saslpasswd2 -c username If you want to enable SMTP AUTH with the system Sendmail, read Sendmail.README NOTE: This port has been compiled with a default pwcheck_method of auxprop. If you want to authenticate your user by /etc/passwd, PAM or LDAP, install ports/security/cyrus-sasl2-saslauthd and set sasl_pwcheck_method to saslauthd after installing the Cyrus-IMAPd 2.X port. You should also check the /usr/local/lib/sasl2/*.conf files for the correct pwcheck_method. If you want to use GSSAPI mechanism, install ports/security/cyrus-sasl2-gssapi. If you want to use SRP mechanism, install ports/security/cyrus-sasl2-srp. If you want to use LDAP auxprop plugin, install ports/security/cyrus-sasl2-ldapdb. ===== Message from mysql57-client-5.7.34: -- This is the mysql CLIENT without the server. for complete server and client, please install databases/mysql57-server ===== Message from mysql57-server-5.7.34: -- Initial password for first time use of MySQL is saved in $HOME/.mysql_secret ie. when you want to use "mysql -u root -p" first you should see password in /root/.mysql_secret MySQL57 has a default /usr/local/etc/mysql/my.cnf, remember to replace it wit your own or set `mysql_optfile="$YOUR_CNF_FILE` in rc.conf. |
インストール直後の主要ファイルの存在状況は以下の通り。
/usr/local/etc/mysql/my.cnf → 存在$HOME/.mysql_secret → 存在しない/root/.mysql_secret → 存在しない/usr/local/libdata/ldconfig/mysql57-server → 存在/usr/local/share/mysql/mysql.server → 存在
MySQLサーバー起動
/etc/rc.confに以下の1行を追加してvagrant reload
|
1 |
mysql_enable="YES" |
サーバーの実行状況を確認。
|
1 2 |
$ sudo /usr/local/etc/rc.d/mysql-server status mysql is running as pid 1909. |
サーバーを再起動する場合。
|
1 |
sudo /usr/local/etc/rc.d/mysql-server start/restart |
サーバーを起動すると.mysql_secretファイルが作成される。
初回ログインとパスワード変更
初期パスワードの確認
rootユーザーの初期パスワードは/root/.mysql_secretファイルに書かれている。
|
1 2 3 |
$ sudo cat /root/.mysql_secret # Password set for user 'root@localhost' at 2021-06-09 13:34:04 jN<&;9LMQr)E <<これが初期パスワード |
初回ログイン
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
$ mysql -u root -p Enter password:<<確認したパスワードを入力 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.34-log Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@localhost [(none)]> |
パスワードを変更
SET PASSWORDクエリーでパスワードを変更。
|
1 2 |
oot@localhost [(none)]> SET PASSWORD FOR root@localhost=password('新しいパスワード'); Query OK, 0 rows affected, 1 warning (0.00 sec) |
日本語の設定
さくらサーバーでの日本語の扱い
インストールしたままの状態だと、ロケールやエンコーディングの設定が日本語の扱いに適していないので変更する必要がある。さくらサーバーのMySQLでの扱いは以下のようになっている。
OSのロケール
ロケールは日本(ja_JP)で文字コードはUTF-8で設定されている。
|
1 2 3 4 5 6 7 8 9 10 |
% locale LANG=ja_JP.UTF-8 LC_CTYPE="ja_JP.UTF-8" LC_COLLATE="ja_JP.UTF-8" LC_TIME="ja_JP.UTF-8" LC_NUMERIC="ja_JP.UTF-8" LC_MONETARY="ja_JP.UTF-8" LC_MESSAGES="ja_JP.UTF-8" LC_ALL= |
MySQLの文字コード
systemがutf8、databaseとserverがutf8mb4で設定されている。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> SHOW VARIABLES LIKE 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | ujis | | character_set_connection | ujis | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | ujis | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) |
MySQLの照合順序
databaseとserverの照合順序はutf8mb4_general_ciに設定されている。
|
1 2 3 4 5 6 7 8 9 |
mysql> SHOW VARIABLES LIKE 'col%'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | ujis_japanese_ci | | collation_database | utf8mb4_general_ci | | collation_server | utf8mb4_general_ci | +----------------------+--------------------+ 3 rows in set (0.01 sec) |
Vagrantの設定
さくらサーバー上のMySQLの設定に合わせる。ロケール、文字コード、照合順序の詳細については以下を参照。
ロケール設定
/etc/profileに以下2行を追加してvagrantをリロード。
|
1 2 |
export LC_ALL=ja_JP.UTF-8 export LANG=ja_JP.UTF-8 |
※’=’の前後にはスペースを入れない。
＜変更後のロケール＞
|
1 2 3 4 5 6 7 8 9 |
$ locale LANG=ja_JP.UTF-8 LC_CTYPE="ja_JP.UTF-8" LC_COLLATE="ja_JP.UTF-8" LC_TIME="ja_JP.UTF-8" LC_NUMERIC="ja_JP.UTF-8" LC_MONETARY="ja_JP.UTF-8" LC_MESSAGES="ja_JP.UTF-8" LC_ALL=ja_JP.UTF-8 |
文字コードと照合順序の設定
/usr/local/etc/mysql/my.cnfの[mysql]セクションと[mysqld]セクションに各々以下を追加してMySQLをリロード(sudo /usr/local/etc/rc.d/mysql-server restart)。
|
1 2 3 4 5 6 7 |
[mysql] ..... default-character-set = utf8 ..... [mysqld] character-set-server = utf8mb4 collateion-server = utf8mb4_general_ci |
＜変更後の文字コード＞
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
root@localhost [(none)]> show variables like 'char%'; +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/local/share/mysql/charsets/ | +--------------------------+----------------------------------+ 8 rows in set (0.00 sec) |
＜変更後の照合順序＞
|
1 2 3 4 5 6 7 8 9 |
root@localhost [(none)]> show variables like 'col%'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | utf8_general_ci | | collation_database | utf8mb4_general_ci | | collation_server | utf8mb4_general_ci | +----------------------+--------------------+ 3 rows in set (0.00 sec) |