mysql 超基本備忘録

# はじめに

過去に設定経験がある人向けの、MySQLの基本的操作の個人的な備忘録です。
いろいろなデータベースの構築を行う事があるため、よく混乱するのでごく簡単にまとめます。

# 環境

amazonLinux2 : 4.14.214-160.339.amzn2.x86_64 にあわせて追記修正

# インストール

* mariaDBとは共存できないので、インストールされていれば削除する。

```

# rm -rf /var/lib/mysql/
```
(CentOS7の場合)

* インストールするバージョンを確認

``` 
yum info mysql-community-server
```
(CentOS7の場合)

インストールしたいバージョンでなければ、該当バージョンのリポジトリをインストールする。 

(CentOS7に、MySQL-5.7を入れる場合)
``` 
yum localinstall http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm
``` 

* インストール実行

```
sudo yum install mysql-community-server
``` 

(Ubuntuの場合)
```
sudo apt-get install mysql-server
```

* 起動と停止

```
sudo systemctl enable mysqld.service    // 自動起動設定
sudo systemctl start mysqld.service     // 起動
sudo systemctl stop mysqld.service      // 停止
```

# MySQL基本設定

* ルートパスワードの設定

　Ubuntu-16.04 LTS 等では、aptでインストールする時にrootのパスワードを設定させられる。
もし root のパスワードが未設定ならば、なにはともあれ最優先で root のパスワードをつける。このときは
mysql_secure_installation で設定するのが一番手っ取り早い。

```
$ mysql_secure_installation
```

mysqlコマンドからNEWPASSWORDを設定する場合

```
$ mysql -h localhost -u root

mysql> update mysql.user set password=password('NEWPASSWORD') where user = 'root';
mysql> flush privileges;   
mysql> exit;
```

（参考）環境によっては予めパスワードが設定されている場合がある。たとえば、DBユーザrootのパスワードとして、ログインユーザrootの初期ログインパスワードが設定されている場合がある。

（追記）mysql-5.7の初期パスワードはインストール時にランダムで設定されているので、下のようにログから確認する

```
sudo grep 'temporary password' /var/log/mysqld.log
```

* 登録済ユーザ一覧取得

```
mysql> SELECT host,user FROM mysql.user;
```

* ユーザ追加

```
# 基本書式：GRANT 権限 ON *.* TO ユーザ名 IDENTIFIED BY 'パスワード'　WITH GRANT OPTION;
# 全権限ユーザの追加
mysql> GRANT ALL PRIVILEGES ON *.* TO [newuser]@[host] IDENTIFIED BY '[password]' WITH GRANT OPTION;
# リードオンリー（selectのみ）権限ユーザの追加
mysql? GRANT SELECT ON *.* TO <ユーザ名>@<接続元> IDENTIFIED BY '[passwowd]";
```

* ユーザ[user]の権限確認

```
mysql> SHOW GRANTS FOR [user];
```

* 文字コード設定
 
設定ファイル my.cnf のデフォルト文字コードを確認（下記はutf8にする場合）

```
[mysqld]
character_set_server=utf8  #mysqldセクションに追加
skip-character-set-client-handshake
collation-server = utf8_general_ci
init-connect = SET NAMES utf8

[client]
default-character-set=utf8  #clientセクションを追加

[mysqldump]
default-character-set=utf8  #mysqldumpセクションを追加
```



# MySQL基本操作

* データベースにアクセス

```
mysql -h HOST --port PORT -u USER -p DBName
```
HOST／PORTを省略したらlocalhost:3306 にアクセスする。パスワード設定していなければ –p はなしだが、この状態は非常に危険なのですぐに対処すること。
DBNameを省略するとデータベース指定なしで接続する。

* データベースのダンプ

```
$ mysqldump -h HOST -u USER -p DBName > dumpfile
```

  ・ DBName の代わりに –all-databases を指定すると全データベースをダンプする。
  ・-–no-data –skip-dump-date 定義のみを出力する。
  ・--routines (-R)でストアドルーチン (プロシージャーおよび関数) を出力に含める。
  ・ –no-data –skip-dump-date –no-create-info –routines でプロシージャのみを出力

* データベースのレストア

```
$ mysql -h HOST -u root -p DBName < dumpfile   # DNNameデータベースをリストア
$ mysql -h HOST -u root -p < dumpfile　        # 全データベースのバックアップを復元
```

外部参照キーを設定している場合は、mysqldumpで作ったファイルそのままではエラーになるので、一時的に外部参照キーを無視してダンプしたファイルを読み込む。

```
$ mysql -h HOST -u root -p DBName
MySQL>  SET foreign_key_checks = 0;
MySQL>  SOURCE dumpfile;
MySQL>  SET foreign_key_checks = 1;
```

* プロシージャ／ファンクション

```
mysql> show procedure status;   # ストアドプロシージャの一覧
mysql> show function status;    # ファンクションの一覧
```

# 文字コード設定変更

文字化けが発生した場合は、文字コード設定を確認する。

* 全体の文字設定を確認

```
mysql> show global variables like 'character%';
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+
```

utf8の場合はこのようになっていればOK。latin1等がある場合は /etc/my.cnf の設定を見直し、再起動で反映させる。

* データベースの文字コード設定を確認

データベースを指定し、クライアント、接続、データベースの文字コードを確認する。

```
mysql> use exampleDB;
mysql> show variables like "chara%";
       :
```

* データベースの文字コード設定の変更

```
ALTER DATABASE `exampleDB` default character set utf8mb4;
```

既に作成されているテーブルの文字コードも変更する必要があるので、状況次第ではデータベースごと再作成したほうが早い。

* テーブル/カラムの文字コードの確認

```
mysql> show create table exampleTable \G; 
```

# データベースの比較

mysqldiffコマンドでデータベースの構成が比較できる


$ mysqldiff -h
Usage: mysqldiff --server1=user:pass@host:port:socket --server2=user:pass@host:port:socket db1.object1:db2.object1 db3:db4


# データベースの初期化

バージョン依存の解決やMariaDBへの移行する場合などは、データベースを初期化したほうが早い場合がある。必要なバックアップを取得後、mysqlを停止し、以下のコマンドを実行。

~~~
cd /var/lib/mysql; rm -rf *   # mysql/ の中身を消す
sudo -u mysql mysql_install_db --datadir=/var/lib/mysql  # 初期化
~~~

# 参考

* https://dev.mysql.com/doc/refman/5.6/ja/adding-users.html
* https://dev.mysql.com/doc/refman/5.6/ja/charset-syntax.html