MySQL

MySQLのインストール

パッケージインストール

MySQL をインストールするには以下のコマンドを実行する。

$ sudo dnf install -y mariadb mariadb-server

MySQLサービスの自動起動設定

$ sudo chkconfig mysqld on
$ sudo chkconfig --list mysqld
mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off

Fedora, CentOS7

$ sudo systemctl enable mariadb.service

MySQL の設定

UTF-8を標準とするように設定する。

$ sudo nano /etc/my.cnf

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
collation-server = utf8mb4_unicode_520_ci
init-connect='SET NAMES utf8mb4'
character-set-server=utf8mb4

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

MySQLサービスの起動

MySQLデーモンを起動するには以下のコマンドを実行する。

$ sudo service mysqld start
MySQL データベースを初期化中:  Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h green.fireball.local password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
                                                           [  OK  ]
MySQL を起動中:                                            [  OK  ]

Fedora, CentOS7

$ sudo systemctl start mariadb.service

UTF-8 設定確認

$ mysql -u root

mysql> show variables like "char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

MySQL Secure Installation の実行

以下のコマンドを実行して MySQL の root パスワードを変更する。

$ mysql_secure_installation
～省略～
Enter current password for root (enter for none): Enter <- 入力
～省略～
Set root password? [Y/n] Enter <- 入力
New password: ******** <- root のパスワードを入力
Re-enter new password: ******** <- root の確認パスワードを入力
～省略～
Remove anonymous users? [Y/n] Enter <- 入力
～省略～
Disallow root login remotely? [Y/n] Enter <- 入力
～省略～
Remove test database and access to it? [Y/n] n <- 入力
～省略～
Reload privilege tables now? [Y/n] Enter <- 入力

ログイン確認

mariadb の root にてログインできることを確認する。

$ mysql -u root -p

Enter password: ******** <- root ユーザーのパスワードを入力
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 54
Server version: 10.11.6-MariaDB-log Alpine Linux

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> \q
Bye

UNIX ドメインソケット経由の接続エラー

UNIX ドメインソケット経由でローカルサーバーに接続できない場合は…🤪

$ mysql -u root -p

Enter password: 
ERROR 2002 (HY000): Can't connect to local server through socket '/var/lib/mysql/mysql.sock' (2)

同一コンピューター内なので UNIX ドメインソケット (mysql.sock) で通信しようとするが、Podman や Docker などのコンテナ仮想化では別マシンと通信するときと同様に TCP/IP ソケット通信でなければ接続できない。

-h ::1 オプションで IPv6/IPv4 ローカルホストアドレスを指定すると TCP/IP ソケット通信にできる😉
または、-P 3306 オプションでポート指定することでも同様に通信できる😊

$ mysql -u root -h ::1 -p
or
$ mysql -u root -h 127.0.0.1 -p
or
$ mysql -u root -P 3306 -p

Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 54
Server version: 10.11.6-MariaDB-log Alpine Linux

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> \q
Bye

参考: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) - Stack Overflow

データベース作成

以下のコマンドを実行して MySQL コマンドラインツールを実行する。

# mysql -u root -p
Enter password: ******** <- root ユーザーのパスワードを入力
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.3.11-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

現在存在しているデータベースを確認する。

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.004 sec)

データベースの作成を行う。

MariaDB [(none)]> CREATE DATABASE tomoyan_db;
Query OK, 1 row affected (0.001 sec)

キャラクタセットを指定する場合(MySQL 4.1以降)

CREATE DATABASE tomoyan_db CHARACTER SET utf8;

データベースが正しく作成されていることを確認する。

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| tomoyan_db         |
+--------------------+
4 rows in set (0.003 sec)

ユーザー作成

ユーザーを作成するには以下のコマンドを実行する。

MariaDB [(none)]> GRANT ALL PRIVILEGES ON tomoyan_db.* TO 'tomoyan'@'localhost' \
 IDENTIFIED BY 'password' WITH GRANT OPTION;
Query OK, 0 rows affected (0.003 sec)

ユーザーが正しく作成されたことを確認するために以下のSQLを実行する。

MariaDB [(none)]> SELECT host, user, password FROM mysql.user;
+-----------+---------+-------------------------------------------+
| host      | user    | password                                  |
+-----------+---------+-------------------------------------------+
| localhost | root    | *A8950ACBC0ABE4A58931119F34574629F62B4CE9 |
| 127.0.0.1 | root    | *A8950ACBC0ABE4A58931119F34574629F62B4CE9 |
| ::1       | root    | *A8950ACBC0ABE4A58931119F34574629F62B4CE9 |
| localhost | tomoyan | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-----------+---------+-------------------------------------------+
4 rows in set (0.003 sec)

mysql データベースの権限テーブルより権限の再読み込みを行う。
```
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.002 sec)
```

ユーザーのパスワード変更

　ユーザーのパスワードを変更するには以下のコマンドを実行する。

MariaDB [(none)]> SET PASSWORD FOR tomoyan@"localhost"=PASSWORD('newpassword');
Query OK, 0 rows affected (0.001 sec)

ユーザー削除

ユーザーをテーブルから削除する。

MariaDB [(none)]> DROP USER 'tomoyan'@'localhost';
Query OK, 0 rows affected (0.004 sec)

操作対象データベースの変更

MariaDB [(none)]> USE tomoyan_db;
Database changed

SQLファイルの実行

$ mysql -u user -p[password] [database] < sqlfile.sql

MariaDB [(none)]> USE tomoyan_db;
Database changed
MariaDB [tomoyan_db]> SOURCE filename;

データベースの削除

MariaDB [(none)]> DROP DATABASE tomoyan_db;
Query OK, 0 rows affected (0.012 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.003 sec)

テーブルのコピー

MySQL サーバ 5.1 では、Sybase SQL 拡張機能である SELECT INTO TABLE はサポートされていない。
以下のようにスキーマコピーとデータコピーで対応する。

MariaDB [(none)]> CREATE TABLE table_name_yyyymmdd LIKE table_name;
MariaDB [(none)]> INSERT INTO table_name_yyyymmdd SELECT * FROM table_name;

ダンプファイルの作成方法(バックアップ)

全てのデータベースをダンプする場合

$ mysqldump -u root -p[password] -x --all-databases > alldatabase_datas.dump

データベース名を指定してダンプする場合

$ mysqldump -u root -p[password] [database] > database_datas.dump

テーブル名を指定してデータのみダンプする場合(DROP & CREATE & INSERT)

$ mysqldump -u root -p[password] [database] [table1] [table2...] > table_datas.sql

テーブル名を指定してデータのみダンプする場合(INSERT)

$ mysqldump -u root -p[password] -t [database] [table1] [table2...] > table_datas.sql

テーブル名を指定してXMLをエクスポートする場合

$ mysqldump -u root -p[password] -X [database] [table1] [table2...] > table_datas.sql

ダンプファイルの復元方法

全てのデータベースを復元する場合

$ mysql -u root -p[password] < alldatabases.dump

データベース名を指定して復元する場合

$ mysql -u root -p[password] [データベース名] < データベース名.dump

phpMyAdminのインストール

　以降の説明では、LAMP 環境と epel リポジトリの設定が済んでいることを前提としている。これらの条件を満たすためには、予め以下の二つの手順を実行しておくこと。

　CentOS で RPM Fusion を利用する
　 CentOS による LAMP(Apache, MySQL, PHP) 環境構築

パッケージのインストール

以下のコマンドを実行すると epel リポジトリより phpMyAdmin パッケージがインストールされる。
```
# yum install phpMyAdmin
```
インストールが完了すると、以下のパスに導入されている。
```
/usr/share/phpMyAdmin
```
Apache に対しての設定も、以下のファイルが httpd.conf に Include されることによって行われる。
```
/etc/httpd/conf.d/phpmyadmin.conf
```

phpMyAdmin.conf 初期設定は以下の内容となっており、ローカルからのアクセスのみ許可している。

Alias /phpMyAdmin /usr/share/phpMyAdmin
Alias /phpmyadmin /usr/share/phpMyAdmin
<Directory /usr/share/phpMyAdmin/>
   order deny,allow
   deny from all
   allow from 127.0.0.1
   allow from ::1
</Directory>
<Directory /usr/share/phpMyAdmin/libraries>
    Order Deny,Allow
    Deny from All
    Allow from None
</Directory>

このままの設定で外部から利用するのが良いので、putty などを利用して SSH のトンネル設定で以下のようにポートフォワードの設定を行う。
```
L8081 localhost:80
※ローカル 8081 ポートへの通信を リモートの localhost:80 へ転送する。
```
putty で接続して以下の URL にアクセスすることで利用できる。
```
http://localhost:8081/phpmyadmin
```

参考文献

Quick 'n' Easy LAMP Server for Centos/RHEL
MySQLクイック・リファレンス
 MySQLのユーザ管理
 急いでいる人のためのMySQLのユーザ権限付与講座 : sanonosa　システム管理コラム集
 MySQLでテーブルのコピーを簡単に作る方法 | おいぬま日報

目次