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

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*

加えて、CentOS8の場合は

dnf module disable mariadb;

CentOS7以下の場合は
/etc/yum.repos.d/CentOS-Base.repo
の
[base]と[updates]のセクションに

exclude=mariadb*

と加えて、CentOSのレポジトリの方からインストールされないようにする。

それから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 --user=root --host=localhost -p

と打ってログイン出来る事を確認しましょう。

MariaDBサーバーの文字コードをutf8mb4に変える

サーバーにログインして

show variables like 'char%'; show variables like 'collation%';

と打つと

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

という結果が出てきます。

絵文字が使えるUTF8を使いたい場合には
/etc/my.cnf.d/server.cnf
を編集して、以下の設定を書いて

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
collation-server = utf8mb4_unicode_ci
init-connect = 'SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'
character-set-server = utf8mb4

MariaDBを

systemctl restart mariadb

で再起動させて変更を反映させます。

またサーバーにログインして

show variables like 'char%'; show variables like 'collation%';

と打ってみると

+--------------------------+----------------------------+
| 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/share/mysql/charsets/ |
+--------------------------+----------------------------+

+--------------------------+----------------------------+
| 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/share/mysql/charsets/ |
+--------------------------+----------------------------+

と表示される内容が変わった事を確認できます。

尚、昔はサーバーの規模等に応じたConfigのテンプレートが付属していましたが、今はデフォルトの設定で基本的に良いよという事で、そうした例が入ったConfigファイル例は付属していません。

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

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 'sbtest2020desu';

下準備

sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --table-size=1000000 --mysql-host=localhost --mysql-password=sbtest2020desu --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=sbtest2020desu --time=60 --db-ps-mode=disable --threads=8 run;

結果

SQL statistics:
    queries performed:
        read:                            300328
        write:                           85808
        other:                           42904
        total:                           429040
    transactions:                        21452  (357.41 per sec.)
    queries:                             429040 (7148.11 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0189s
    total number of events:              21452

Latency (ms):
         min:                                    3.35
         avg:                                   22.38
         max:                                   97.82
         95th percentile:                       31.37
         sum:                               480074.75

Threads fairness:
    events (avg/stddev):           2681.5000/24.30
    execution time (avg/stddev):   60.0093/0.00

MariaDBでの開発

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

テーブルの作りの表示

SHOW CREATE TABLE テーブル名

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

SHOW PROCESSLIST

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

kill プロセスID

サーバーとの接続の終了

exit

データの型

公式の説明が英語なので英語のままで
BIGINT / Large integer
BINARY / Fixed-length binary byte string.
BIT / Bit field type
BLOB / Binary large object up to 65,535 bytes.
BOOLEAN / Synonym for TINYINT(1)
CHAR / Fixed-length string.
CHAR BYTE / Alias for BINARY.
DATE / The date type YYYY-MM-DD
DATETIME / Date and time combination displayed as YYYY-MM-DD HH:MM:SS.
DEC, NUMERIC, FIXED / Synonyms for DECIMAL
DECIMAL / A packed “exact” fixed-point number.
DOUBLE / Normal-size (double-precision) floating-point number
DOUBLE PRECISION / REAL and DOUBLE PRECISION are synonyms for DOUBLE.
ENUM / Enumeration, or string object that can have one value chosen from a list of values.
FLOAT / Single-precision floating-point number
INT / Integer from -2147483648 to 2147483647 signed
INTEGER / Synonym for INT
JSON Data Type / Compatibility data type that is an alias for LONGTEXT.
LONGBLOB / Long BLOB holding up to 4GB.
LONGTEXT / A TEXT column with a maximum length of 4,294,967,295 characters.
MEDIUMBLOB / Medium binary large object up to 16,777,215 bytes.
MEDIUMINT / Medium integer from -8388608 to 8388607 signed
MEDIUMTEXT / A TEXT column with a maximum length of 16,777,215 characters.
ROW / Data type for stored procedure variables.
SMALLINT / Small integer from -32768 to 32767 signed
TEXT / A TEXT column with a maximum length of 65,535 characters.
TIME / Time format HH:MM:SS.ssssss
TIMESTAMP / YYYY-MM-DD HH:MM:SS
TINYBLOB / Tiny binary large object up to 255 bytes.
TINYINT / Tiny integer, -128 to 127 signed
TINYTEXT / A TEXT column with a maximum length of 255 characters.
VARBINARY / Variable-length binary byte string.
VARCHAR / Variable-length string.
YEAR / Data Type A four-digit year

VARCHARとTEXTが使うディスク容量についての考え方

VARCHARは255迄は長さ+1byteを使うだけだが256以上は長さ+2byteを使う。
TEXTは長さ+2byteを使うのと、インデックスを張れないという点で異なる。
また、VARCHARは初期値を決めれるが、TEXTは初期値を定める事は出来ない。
その辺りを考慮して型を決めていく。