データベース

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

更新日

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

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

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

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

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

MySQLのインストール

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

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

sudo dnf -y install mysql-server mysql;

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

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

dnf remove mysql*;

加えて、CentOS8の場合は

dnf module disable mysql;

CentOS7以下の場合は
/etc/yum.repos.d/CentOS-Base.repo

[base]と[updates]のセクションに

exclude=mariadb*
exclude=mysql*

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

それからyumのレポジトリでmysql公式を有効にする

wget https://dev.mysql.com/get/mysql80-community-release-el8-1.noarch.rpm;
yes | sudo dnf install mysql80-community-release-el8-1.noarch.rpm;

それからインストール

yes | sudo dnf install mysql-server;

バージョンを

mysql --version;

で確認

MySQLの最低限の設定

rootのパスワードを確認

grep 'A temporary password is generated' /var/log/mysqld.log | tail -1
root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

と書かれているのなら、パスワードは空という事

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

sudo systemctl start mysqld;
sudo systemctl enable mysqld;
sudo mysql_secure_installation;

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

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;
CREATE USER 'sbtest'@'localhost' IDENTIFIED BY 'sbtest2020desu';
GRANT ALL ON `sbtest`.* TO 'sbtest'@'localhost';

下準備

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:                            199864
        write:                           57101
        other:                           28551
        total:                           285516
    transactions:                        14275  (237.85 per sec.)
    queries:                             285516 (4757.32 per sec.)
    ignored errors:                      1      (0.02 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0122s
    total number of events:              14275

Latency (ms):
         min:                                    7.36
         avg:                                   33.63
         max:                                  109.99
         95th percentile:                       44.98
         sum:                               480023.84

Threads fairness:
    events (avg/stddev):           1784.3750/6.71
    execution time (avg/stddev):   60.0030/0.00

Mysqlでの開発

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(size)
BINARY(size)
BIT(size)
BLOB(size)
BOOL
BOOLEAN
CHAR(size)
DATE
DATETIME(fsp)
DEC(size, d)
DECIMAL(size, d)
DOUBLE PRECISION(size, d)
DOUBLE(size, d)
ENUM(val1, val2, val3, …)
FLOAT(p)
FLOAT(size, d)
INT(size)
INTEGER(size)
LONGBLOB
LONGTEXT
MEDIUMBLOB
MEDIUMINT(size)
MEDIUMTEXT
SET(val1, val2, val3, …)
SMALLINT(size)
TEXT(size)
TIME(fsp)
TIMESTAMP(fsp)
TINYBLOB
TINYINT(size)
TINYTEXT
VARBINARY(size)
VARCHAR(size)
YEAR