MariaDB

関連記事

• phpMyAdmin
• Adminer
• JDBC と MySQL
• Open Database Connectivity

MySQL は幅広く使われている、マルチスレッドの、マルチユーザー SQL データベースです。機能に関する詳細は 公式ホームページ を見て下さい。

ノート: 現在 MariaDB が Arch Linux における MySQL のデフォルトの実装になっています。全てのユーザーは MariaDB にアップグレードするのが推奨されています。Oracle MySQL は AUR に移動しました。アナウンスを参照してください。

インストール

Arch Linux で選ばれている MySQL の実装は MariaDB です。公式リポジトリから mariadb をインストールしてください。他の実装は:

• Oracle MySQL — Oracle Corporation による実装。

https://www.mysql.com/ || mysql^AUR

• Percona Server — Percona LLC による実装。

http://www.percona.com/software/percona-server/ || percona-server

ヒント:

• データベース (/var/lib/mysql) を btrfs ファイルシステムに配置する際は、データベースを作成する前にディレクトリの Copy-on-Write を無効にすることを考えるべきです: # chattr +C /var/lib/mysql。
• ZFS ファイルシステム上にデータベースを保存する場合、データベースを作成する前に ZFS#データベース を読んで下さい。

mariadb をインストールしたら、 mariadb.service を起動する前に以下のコマンドを実行してください:

# mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

警告: セキュリティ上の理由から、systemd サービスファイルには ProtectHome=true が含まれており /home, /root, /run/user のファイルを MariaDB から使えないようにしています。datadir は MariaDB からアクセスできる場所を指定して、mysql ユーザーとグループが所有するようにしてください。サービスファイルを編集することで挙動は変更できます [1]。

そして、mariadb.service を起動 (必要であれば有効に) します。

ヒント: /var/lib/mysql 以外のディレクトリをデータディレクトリとしたい場合、/etc/mysql/my.cnf の [mysqld] セクションで datadir=<YOUR_DATADIR> と設定してください。

さらに MySQL を安全にするために以下のコマンドの実行を推奨します:

# mysql_secure_installation

データベースの管理を容易にする dbeaver, mysql-workbench, Adminer, phpMyAdmin などのフロントエンドをインストールしても良いでしょう。mysql-workbench は MariaDB と完全な互換性はありませんが、基本的な作業については問題ありません。

Oracle MySQL から MariaDB へのアップグレード

ノート: デーモンを再起動する前に /var/lib/mysql の次のファイルを削除する必要があるかもしれません: ib_logfile0, ib_logfile1, aria_log_control。

データベースを切り替えるときに環境を移行するには mysqld.service を停止して mariadb をインストールして mariadb.service を起動し次を実行してください:

# mysql_upgrade -u root -p

設定

MySQL サーバーを起動できたら、おそらく MySQL ユーザーとデータベースを管理するための root アカウントを追加したくなるはずです。上記のスクリプトの出力で説明されているように、手動または自動で設定を行えます。root アカウントのパスワードを設定するコマンドを実行するか、セキュアなインストールスクリプトを実行してください。

インストールの後は、好きなインタフェースを使って使用するための設定を行うことができます。例えば MySQL のコマンドラインツールを使って MySQL サーバーに root でログインすることが可能です:

$ mysql -u root -p

ユーザーを追加する

新しいユーザーの作成は2段階で行います: ユーザーを作って、そのユーザーに権限を与える。以下の例では、ユーザー monty をパスワード some_pass で作成し、データベース mydb への完全な権限を与えます:

$ mysql -u root -p

MariaDB> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
MariaDB> GRANT ALL PRIVILEGES ON mydb.* TO 'monty'@'localhost';
MariaDB> FLUSH PRIVILEGES;
MariaDB> quit

設定ファイル

MariaDB の設定オプションは以下のファイルから順番で読み込まれます (mysqld --help --verbose ):

/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

変更したい範囲にあわせて (システム全体かユーザー個別か)、適当なファイルを使って下さい。詳しくは KnowledgeBase の エントリ を見て下さい。

リモートアクセスを無効にする

デフォルトで MySQL サーバーはネットワークからアクセスできるようになっています。MySQL をローカルホストからしか使わない場合は、TCP ポート 3306 を使用しないようにすることでセキュリティを向上させることが可能です。リモート接続を拒否させるには、/etc/mysql/my.cnf にある次の行をアンコメントしてください:

skip-networking

この設定をしてもローカルホストからはログインできます。

自動補完を有効にする

ノート: この機能を有効にするとクライアントの初期化が長くなるかもしれません。

MySQL クライアントの補完機能はデフォルトでは無効になっています。システム全体で有効にするには /etc/mysql/my.cnf を編集して、no-auto-rehash を auto-rehash で置き換えてください。次に MySQL クライアントを実行した時に補完が有効になります。

UTF8MB4 を使う

警告: 文字セットを変更する前にバックアップを作成するようにしてください。

ノート: UTF8MB4 は Unicode を完全にサポートしているため UTF-8 よりも推奨されています [2] [3]。

/etc/mysql/my.cnf に存在するメインの設定ファイルに以下の値を追加:

[client]
default-character-set = utf8mb4

[mysqld]
collation_server = utf8mb4_unicode_ci
character_set_server = utf8mb4

[mysql]
default-character-set = utf8mb4

変更を適用するには mariadb.service の再起動が必要です。

#データベースのメンテナンスを参照して最適化とデータベースの健康度チェックを行ってください。

文字数制限の増加

ノート: 文字数制限は使用している文字セットによって変わります [4] [5] [6]。

InnoDB の場合、以下のコマンドを実行することで文字数制限を高くすることができます:

mysql> set global innodb_file_format = BARRACUDA;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_file_per_table = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_large_prefix = ON;
Query OK, 0 rows affected (0.00 sec)

/etc/mysql/my.cfg に以下の行を追加することで常に高い文字数制限が使われるようになります:

[mysqld]
innodb_file_format = barracuda
innodb_file_per_table = 1
innodb_large_prefix = 1

mariadb.service を再起動して変更を適用してください。

テーブルを作成する際に以下のように ROW_FORMAT を追加します:

mysql> create table if not exists products (
   ->   day date not null,
   ->   product_id int not null,
   ->   dimension1 varchar(500) not null,
   ->   dimension2 varchar(500) not null,
   ->   unique index unique_index (day, product_id, dimension1, dimension2)
   -> ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.02 sec)

tmpdir に TMPFS を使う

MySQL が一時ファイルの保存に使用するディレクトリは tmpdir という名前が付いています。例えば、ディスクの膨大なソートを行うのに使われたり、内部的または明示的な一時ディレクトリのために使われます。

適切なパーミッションでディレクトリを作成:

# mkdir -pv /var/lib/mysqltmp
# chown mysql:mysql /var/lib/mysqltmp

mysql ユーザーとグループの id と gid を確認:

$ id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)

/etc/fstab ファイルに追加:

 tmpfs   /var/lib/mysqltmp   tmpfs   rw,gid=27,uid=27,size=100M,mode=0750,noatime   0 0

/etc/mysql/my.cnf ファイルの mysqld グループ下に追加:

 tmpdir      = /var/lib/mysqltmp

その後再起動するか ( shutdown mysql, mount the tmpdir, start mysql )。

タイムゾーンテーブル

タイムゾーンテーブルはインストールで作成されますが、ロードが自動では行われません。SQL クエリで CONVERT_TZ() を使う予定ならばロードする必要があります。

全てのタイムゾーンのタイムゾーンテーブルをロードするには:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

また、特定のタイムゾーンファイルを指定してテーブルをロードすることもできます:

$ mysql_tzinfo_to_sql <timezone_file> <timezone_name> | mysql -u root -p mysql

データベースのメンテナンス

メジャーリリース時のデータベースアップグレード

メジャーバージョンアップデート (例えば mariadb-10.1.10-1 から mariadb-10.1.18-1 にアップデート) をした後は、次のコマンドを実行して MySQL を再起動すると良いでしょう:

# mysql_upgrade -u root -p

データベースのチェック・最適化・修復

mariadb に付属している mysqlcheck を使うことで、シェルからデータベースのテーブルをチェック・最適化・修復することができます。詳しくは mysqlcheck の man ページを見てください。よく使われるコマンドは以下の通りです。

全てのデータベースの全てのテーブルをチェックするには:

$ mysqlcheck --all-databases -u root -p -c

全てのデータベースの全てのテーブルを解析するには:

$ mysqlcheck --all-databases -u root -p -a

全てのデータベースの全てのテーブルを修復するには:

$ mysqlcheck --all-databases -u root -p -r

全てのデータベースの全てのテーブルを最適化するには:

$ mysqlcheck --all-databases -u root -p -o

バックアップ

簡易バックアップとしてファイルにデータベースを出力することが可能です。以下のシェルスクリプトは、データベースのダンプが含まれた db_backup.gz ファイルを、スクリプトと同じディレクトリに作成します:

#!/bin/bash

THISDIR=$(dirname $(readlink -f "$0"))

mysqldump --single-transaction --flush-logs --master-data=2 --all-databases \
 | gzip > $THISDIR/db_backup.gz
echo 'purge master logs before date_sub(now(), interval 7 day);' | mysql

MySQL マニュアルにある公式の mysqldump の ページ も参照してください。

トラブルシューティング

MySQL デーモンが起動しない

MySQL が起動せずログファイルに何も出力されない場合、/var/lib/mysql や /var/lib/mysql/mysql ディレクトリ内のファイルのパーミッションを確認してください。ディレクトリ内にあるファイルの所有者が mysql:mysql ではないときは、次を実行してください:

# chown mysql:mysql /var/lib/mysql -R

上記に従ってもパーミッション問題が発生する場合は、my.cnf を /etc/ にコピーしてみて下さい:

# cp /etc/mysql/my.cnf /etc/my.cnf

そしてデーモンを起動してみましょう。

/var/lib/mysql/hostname.err に以下のメッセージが表示される場合:

[ERROR] Can't start server : Bind on unix socket: Permission denied
[ERROR] Do you already have another mysqld server running on socket: /var/run/mysqld/mysqld.sock ?
[ERROR] Aborting

おそらく /var/run/mysqld のパーミッションが原因です。

# chown mysql:mysql /var/run/mysqld -R

mysqld を実行すると以下のエラーが表示される場合:

Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist

/usr ディレクトリから以下のコマンドを実行してデフォルトのテーブルをインストールしてください:

# cd /usr
# mysql_install_db --user=mysql --ldata=/var/lib/mysql/

MariaDB 10.1.16 からデフォルトではデータディレクトリを /home や /root、あるいは /run/user などにできないようになっています。データディレクトリを移動するか、あるいは mariadb.service を編集して以下の行を:

ProtectHome=true

以下のように置き換えてください:

ProtectHome=false

MySQL が起動しないために mysql_upgrade を実行できない

MySQL をセーフモードで実行して:

# mysqld_safe --datadir=/var/lib/mysql/

次を実行してみて下さい:

# mysql_upgrade -u root -p

root パスワードをリセットする

mariadb.service を停止。次のコマンドを実行:

# mysqld_safe --skip-grant-tables &

mysql サーバーに接続。次のコマンドを実行:

# mysql -u root mysql

root パスワードを変更:

mysql> use mysql;
mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit

mariadb.service を起動。

全てのテーブルをチェック・修復する

全てのデータベースの全てのテーブルをチェック・自動修復 (詳細):

# mysqlcheck -A --auto-repair -u root -p

全てのテーブルを最適化する

テーブルエラーを自動的に修正して、全てのテーブルを強制的に最適化する。

# mysqlcheck -A --auto-repair -f -o -u root -p

ZFS での実行時に OS エラー 22

ZFS を使っていて、以下のエラーが表示される場合:

InnoDB: Operating system error number 22 in a file operation.

/etc/mysql/my.cnf の mysqld セクションに次の行を追加して aio_writes を無効にする必要があります:

[mysqld]
...
innodb_use_native_aio = 0

ただし、上の問題のせいでインストール後のスクリプトが失敗する場合、MySQL/MariaDB が無効状態になっている可能性があります。この状態から復旧するには、以下を実行してください:

rm -rf /var/lib/mysql/*
mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
chown -R mysql:mysql /var/lib/mysql &>/dev/null
/usr/bin/systemd-tmpfiles --create mysql.conf

これで MySQL/MariaDB が正しくインストールされるはずです。

CLI でログインできないが、phpmyadmin は問題なく動作する

長い (>70-75) パスワードを使っているとこの問題が発生することがあります。 5.5.36 現在、何らかの理由で、mysql CLI は readline モードで長い文字列を扱えなくなっています。 そのため、長いパスワードを使う場合は input モードを使うことを推奨します:

$ mysql -u <user> -p
Password:

もしくは短いパスワードに変えてください。

ノート: mysql コマンドに引数としてパスワードを指定することでログインすることは可能です。

警告: 以下のコマンドを実行するとパスワードがログに保存されるなどして漏洩する危険性があります。緊急の場合にのみ使ってください。そしてすぐにパスワードを変えてください。

$ mysql -u <user> -p"<some-very-strong-password>"

MySQL のバイナリログがディスク容量を逼迫させている

デフォルトで、mysqld はバイナリログファイルを /var/lib/mysql に作成します。マスターサーバーのレプリケーションやデータの復旧などにログは役立ちますが、バイナリログはディスク容量を逼迫させる可能性もあります。レプリケーションやデータの復元機能を使わない場合、/etc/mysql/my.cnf で以下の行をコメントアウトすることでバイナリログを無効化できます:

#log-bin=mysql-bin
#binlog_format=mixed

以下のようにログファイルの容量を制限することもできます:

expire_logs_days = 10
max_binlog_size  = 100M

また、以下のコマンドを使って /var/lib/mysql のバイナリログを消去することでディスク領域を確保することができます:

# mysql -u root -p "PASSWORD" -e "PURGE BINARY LOGS TO 'mysql-bin.0000xx';"

OpenRC で MySQL を起動できない

OpenRC で MySQL を使うには MySQL の設定ファイル (/etc/mysql/my.cnf) の [mysqld] セクションに以下の行を追加する必要があります:

user = mysql
basedir = /usr
datadir = /var/lib/mysql
pid-file = /run/mysqld/mysql.pid

以下のコマンドで MySQL を起動できるはずです:

# rc-service mysql start

Specified key was too long

#文字数制限の増加を参照してください。

参照

• MariaDB 公式ウェブサイト
• MariaDB knowledge Base
• MySQL ドキュメント
• Apache HTTP Server - Apache HTTP Server に関する ArchWiki の記事
• PHP - PHP に関する ArchWiki の記事。
• MySQL のパフォーマンスチューニングスクリプトとノウハウ