インフラ

MariaDB(MySQL)のインストール・設定方法

更新日

MariaDB(MySQL)とPostgreSQLのどちらを使うのかを決める

MariaDBとMySQLとPostgreSQLどれを選ぶべきか選択フローチャート」を参照して下さい。
その上でMariaDBをインストールすると決めたら読み進めて下さい。


そもそも自分でDBを設定・運用する事が妥当なのかの判断

データベースは、業務上ミッションクリティカルな部分であり、またサラリーマン的に働く場合には、その設定・運用・教育の人件費にもコストがかかると考えると、企業人として関わる場合には自分でインストール・設定・運用するよりも、サービス側にDB運用の多くを任せられる、DBAAS(Database As A Service)型のマネージドDBサービスを利用した方が、費用対効果的にも妥当となる事が多い。

ただ、個人の財布でサービスを運用する場合、月数千円でも節約したい、という気持ちになるのも妥当であり、その場合には、ようこそ、データベース運用者の道へ、という事になります。


MariaDBのインストール

OS付属のMariaDBをインストール

CentOSの公式からインストールするには以下の形で
dnfの部分はCentOS7以前ならyum、Ubuntu系ならapt-getに差し替え

sudo dnf -y install mariadb-server mariadb;

MariaDB公式の版をインストール

OS公式ではなく、MariaDB公式の方のを使う場合には、OSの他ソフトとの相性の問題が発生する可能性がありますが、MariaDB専用のサーバーにしようとしているのならば、問題はないでしょう。
又、一般的に、OS付属のMariaDBより、より新しい版が利用可能です。
MariaDb公式のmariadbをインストールするには
まず念の為にOS付属のmariadbが入っていたら削除するコマンドを打って

dnf remove mariadb*

それからyumのレポジトリでmariadb公式を有効にして

curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash;

それからインストール

dnf -y install MariaDB*;

バージョンを

mariadb --version

で確認


MariaDBの最低限の設定

自動起動&起動&セキュリティ設定を行うには

sudo systemctl start mariadb;
sudo systemctl enable mariadb.service;
sudo mysql_secure_installation;

MariaDB(MySQL)の基本的なコマンド

一番最初のデータベースヘの接続

mysql --user=... --database=... --host=... -p

データベースの作成

CREATE DATABASE データベース名 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ユーザーの作成と指定データベースへの権限付与

GRANT ALL ON $DABASENAME.* TO '$DBUSER'@'localhost' identified by "$DBUSERPASSWORD";
GRANT ALL ON $DABASENAME.* TO '$DBUSER'@'10.%' identified by "$DBUSERPASSWORD";
GRANT ALL ON $DABASENAME.* TO '$DBUSER'@'172.%' identified by "$DBUSERPASSWORD";
GRANT ALL ON $DABASENAME.* TO '$DBUSER'@'192.%' identified by "$DBUSERPASSWORD";

データベースのリスト

SHOW DATABASES

使用データベースの切り替え

use データベース名

データベースのテーブルのリスト

SHOW TABLES

サーバーで現在実行されているSQLの確認

SHOW PROCESSLIST

実行時間が長いSQLの実行の停止

kill プロセスID

サーバーとの接続の終了

exit

データベース全体をバックアップ

mysqldump --all-databases --single-transaction --quick --lock-tables=false > full-backup-$(date +%F).sql -u root -p

データベース全体をダンプしたファイルからリストア

mysql -u root -p < $DUMPED.sql

ユーザーの権限の同期には

flush privileges;

を打つ


ファイアーウォールの設定

各VPSがファイアーウォールを提供しているのなら各VPSのファイアーウォールの機能を、そうでなければ firewall-cmd でLinux自体にfirewallを設定する。

DBのポートはWebサーバーに対してだけあけ、sshログインも自分の管理するサーバー等、特定のIPアドレスからのみログインできるようにするのがセキュリティ上良い。

なお、セキュリティとスペックの分離上、WebサーバーとDBサーバーを置くサーバーは出来るのなら分けた方が良いが、コストの削減考えてWebサーバーと同一の所に置くのもあるかと思います。
ただ、その場合でも、DBのポートをインターネットに公開しないように注意(Webサーバーからローカルアクセスでのみアクセスできるように設定すべき)。


ベンチマークを取得する

dnf install -y sysbench;

ログインしてから

create database sbtest;
GRANT ALL ON sbtest.* TO 'sbtest'@'localhost' IDENTIFIED BY 'sbtest';

下準備

sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --table-size=1000000 --mysql-host=localhost --mysql-password=sbtest --time=60 --db-ps-mode=disable prepare;

テストの実行

sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --table-size=100000 --mysql-host=localhost --mysql-password=sbtest --time=60 --db-ps-mode=disable --threads=8 run;