【自分用】mysql でよく使うやつまとめ


# 現在のバージョン確認〜どこにあるか
```
Homebrewの場合。
brew info mysql

コマンドがどこにインストールされてるか。
which mysql
```

# インストール

#### 1. MySQLをインストール

    $ brew update
	$ brew install mysql
    
    バージョン指定
    $ brew install homebrew/versions/mysql56

#### 2. 自動起動設定

    $ ln -sfv /usr/local/opt/mysql@5.6/*.plist ~/Library/LaunchAgents
    $ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql@5.6.plist
    
#### 3. 自動起動設定（簡単設定）
homebrew-servicesを使う簡単な自動起動設定。

公式サイトはこちら。
https://github.com/Homebrew/homebrew-services

READMEに従ってインストール。

```
$ brew tap homebrew/services
```

MySQLを自動起動にする。

```
$ brew services start mysql

==> Successfully started `mysql` (label: homebrew.mxcl.mysql)
```

これだけ。

自動起動を止めたい時は、

```
$ brew services stop mysql

Stopping `mysql`... (might take a while)
==> Successfully stopped `mysql` (label: homebrew.mxcl.mysql)
```

一覧を取得したい。

```
$ brew services list

Name       Status  User   Plist
mysql      started ------ /Users/------/Library/LaunchAgents/homebrew.mxcl.mysql.plist
postgresql started ------ /Users/------/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
```

#  アンインストール系
#### 1. ターミナルを開く
コマンドプロンプトでの作業になります。

#### 2. `mysqldump`でデータベースのバックアップをとる
MySQL内の全データをファイルdump.sql(名前は自由)に出力。

    $ mysqldump -u root -p -x --all-databases > dump.sql

参考: [MySQLのバックアップ/復元(ダンプ/インポート/dump/import) 基本コマンド メモ - Qiita](http://qiita.com/morizyun/items/f45dc580bac0ebba79ab)

#### 3. MySqlのプロセスを確認

    $ ps -ax | grep mysql

    # /usr/local/Cellar/mysql/5.7.11/bin/mysqld の存在を確認

#### 4. MySQLの停止

    $ mysql.server stop

#### 5. HomebrewでMySQLをアンインストール

    $ brew remove mysql
    $ brew cleanup

#### 6. MySQL関連ファイルを削除
すでに存在していないこともあるので`ls`で確認しながら`rm`で削除。

    $ sudo rm /usr/local/mysql
    $ sudo rm -rf /usr/local/var/mysql
    $ sudo rm -rf /usr/local/mysql*
    $ sudo rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
    $ sudo rm -rf /Library/StartupItems/MySQLCOM
    $ sudo rm -rf /Library/PreferencePanes/My*

#### 7. MySQLの自動起動設定を解除

    $ launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

#### 8. MySQLの設定を削除

    $ vim /etc/hostconfig` 

    # MYSQLCOM=-YES- の行を削除

#### 9. MySQLの環境設定を削除

    $ rm -rf ~/Library/PreferencePanes/My*
    $ sudo rm -rf /Library/Receipts/mysql*
    $ sudo rm -rf /Library/Receipts/MySQL*
    $ sudo rm -rf /private/var/db/receipts/*mysql*

#### 10. PCを再起動
全てのMySQLプロセスを終了させます。

#### 11. `mysql`コマンドが使えないことを確認

    $ mysql -v

    # mysql: command not found

# 起動からログイン

* サーバーの起動
```
mysql.server start
```
* サーバーが動作しているか確認
```
mysql.server status
```
* ログイン


```
mysql -u root

```
* ログインできるユーザー、一覧表示


```
SELECT Host, User FROM mysql.user;
```

# 使用するデータベースやテーブル類

* データベース作成


```
CREATE DATABASE データベース名;
```

* データベース作成


```
DROP DATABASE データベース名;
```

* 現在使っているデータベース名


```
SELECT database();
```

* 使用するデータベースを選択
```
use データベース名;
```
* 選択しているデータベース名の確認
```
select database();
```
* 新しいデータベース作成
```
create database データベース名;
```
* プロセスリスト（処理中の接続）を表示する
```
show processlist;
```
* データベース一覧表示
```
SHOW DATABASES;
```
* データベース内のテーブル一覧を表示する
```
SHOW TABLES FROM データベース名;
```
* テーブルのカラム一覧表示
```
DESCRIBE user;
show columns from user;
```

## 使用するユーザーの詳細取得方法

* ユーザー一覧を取得


```
mysql.userとは？
デフォルトで存在する、mysqlデータベースに存在するuserというテーブル。

SELECT * FROM mysql.user;
↑だとuserのもつ全てのカラムを取得してくるので

SELECT Host, User FROM mysql.user;
↑基本はHost, User を指定して取得する部分を加工する。
```



# MySQL8.0 認証方式を変更する(Laravel5)

## 環境

```
$ mysql --version
mysql  Ver 8.0.11 for Linux on x86_64 (MySQL Community Server - GPL)
```

## エラー内容

`mysql` コマンドで直接データベースへログインできたのですが、Laravel マイグレーションを実行するとエラーとなりました。

```
$ php artisan migrate
   Illuminate\Database\QueryException  : SQLSTATE[HY000] [2054] The server requested authentication method unknownto the client (SQL: select * from information_schema.tables where table_schema = blog and table_name = migrations)

  at /home/vagrant/blog/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
    660|         // If an exception occurs when attempting to run a query, we'll format the error
    661|         // message to include the bindings with SQL, which will make this exception a
    662|         // lot more helpful to the developer instead of just the database's errors.
    663|         catch (Exception $e) {
  > 664|             throw new QueryException(
    665|                 $query, $this->prepareBindings($bindings), $e
    666|             );
    667|         }
    668|

  Exception trace:

  1   PDOException::("PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]")
      /home/vagrant/blog/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:68

  2   PDO::__construct("mysql:host=127.0.0.1;port=3306;dbname=blog", "vagrant", "P@assw0rd", [])
      /home/vagrant/blog/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:68
```

## 原因： MySQL8.0.4 デフォルトの認証方式変更

- **MySQL8.0.4以降** のログイン認証方式は `caching_sha2_password` がデフォルト
- PHPのMySQL接続ライブラリが `caching_sha2_password `　に未対応のため接続不可
- 解決策としては認証方式を `mysql_native_password` に戻す

[6.5.1.3 Caching SHA-2 Pluggable Authentication](https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html)

## 既存ユーザーの認証方式を確認

```
> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| vagrant          | 127.0.0.1 | caching_sha2_password |
| vagrant          | 192.168.% | caching_sha2_password |
| mysql.infoschema | localhost | mysql_native_password |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| root             | localhost | caching_sha2_password |
| vagrant          | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
```

## 対処法1: 既存ユーザーの認証方式を変更

```sql
> ALTER USER 'vagrant'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd';
> ALTER USER 'vagrant'@'192.168.%' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd';
> ALTER USER 'vagrant'@'localhost' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd';
> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd';
```

## 対処法2: 新規ユーザー作成時の認証方式を変更

`/etc/my.cnf` に追記

```
[mysqld]
default_authentication_plugin=mysql_native_password
```

最初にこれを設定しておくべきでした。。

## Sequel Proで接続できない問題

Sequel ProがMySQL8.0に対応していません！
執筆時のバージョンは1.1.2で1.2以降からMySQL8.0に対応する予定らしいです！

### 代替案

- **推奨** [TablePlusに乗り換える](https://tableplus.io)
- **推奨** [MySQL8.0.x に Sequel Pro で接続する](https://qiita.com/ucan-lab/items/b68db1db931c954da921)
- [MySQL 8.0がサポートされるまで待つ](https://github.com/sequelpro/sequelpro/issues/2699)

## 関連記事

- [docker-compose MySQL8.0 のDBコンテナを作成する](https://qiita.com/ucan-lab/items/b094dbfc12ac1cbee8cb)