接続前なら

  $ mysql --version
  mysql  Ver 14.14 Distrib 5.7.21, for osx10.13 (x86_64) using  EditLine wrapper

接続後なら

  mysql> select version();
  +-----------+
  | version() |
  +-----------+
  | 5.7.21    |
  +-----------+
  1 row in set (0.01 sec)

mysqladminやstatusを使えばより詳細に色々分かる。

接続前

  $ mysqladmin version
  mysqladmin  Ver 8.42 Distrib 5.7.21, for osx10.13 on x86_64
  Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective
  owners.

  Server version        5.7.21
  Protocol version  10
  Connection        Localhost via UNIX socket
  UNIX socket       /tmp/mysql.sock
  Uptime:           4 min 53 sec

  Threads: 1  Questions: 2  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 0.006

接続後

  mysql> status
  --------------
  mysql  Ver 14.14 Distrib 5.7.21, for osx10.13 (x86_64) using  EditLine wrapper

  Connection id:        3
  Current database:
  Current user:     root@localhost
  SSL:          Not in use
  Current pager:        stdout
  Using outfile:        ''
  Using delimiter:  ;
  Server version:       5.7.21 Homebrew
  Protocol version: 10
  Connection:       Localhost via UNIX socket
  Server characterset:  utf8
  Db     characterset:  utf8
  Client characterset:  utf8
  Conn.  characterset:  utf8
  UNIX socket:      /tmp/mysql.sock
  Uptime:           6 min 14 sec

  Threads: 1  Questions: 7  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 0.018
  --------------

自分のローカル環境のmysqlのバージョンが5.6系だった為、5.7系にバージョンを上げてその際に起きたエラーと他のコマンド等の備忘録（Macでやってます）

既存のMysqlのデータのバックアップを取る。

まず最初にMysqlのデータベースをバックアップ。
今回は全てのデータベースをバックアップしたかったので

$ mysqldump -u root -x —all-databases > mysqlbk.sql

一応ファイルと中身があるか確認

$ ls mysqlbk.sql
$ cat mysqlbk.sql ←これはやらない方がいいと思う

mysqlの停止

$ mysql.server stop

ただしここでエラーが発生

The server quit without updating PID file (/usr/local/var/mysql/ここはユーザーのmacbook-proとか出ます.local.pid).

もろもろ調べてみました（この辺りの記事）
https://qiita.com/_natsu_no_yuki_/items/ae4c94187093e4ab3cdc
http://morototo.hatenablog.com/entry/2017/07/29/103204

とりあえず上の記事を参考にして

$ sudo rm -rf /usr/local/var/mysql
$ brew uninstall mysql
$ brew install mysql@5.7

再度試してみました

$ mysql.server start
...
The server quit without updating PID file (/usr/local/var/mysql/ここはユーザーのmacbook-proとか出ます.local.pid).

もしかしたらプロセスが残っているからなのではと思い

$ brew uninstall mysql@5.7
$ ps aux | grep mysql

これでmysql5.6で生きていたプロセスがあったので

$ kill -9 対象のPID

これで削除ののち

$ brew install mysql@5.7
$ echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.bash_profile
$ source ~/.bash_profile
$ mysql.server start
SUCCESS!

動いたので自動起動の設定も

$ ln -sfv /usr/local/opt/mysql@5.7/*.plist ~/Library/LaunchAgents
$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql@5.7.plist

バックアップの復元も

$ mysql -u root -p < mysqlbk.sql

$ mysql -u root -p
> show databases;

無事に全てのデータが格納されていました。
pidのエラーに関してはもう少しちゃんと調べれば簡単に解決できそうでした。

参考にした記事

https://qiita.com/mikakane/items/6857a4ae25ceaed4ee4e
https://qiita.com/dkjym/items/d2bfca70bc9b0c2ee9dc

やったこと

社内システムのDBをmysqldumpで定期的にダンプしている。このダンプは、ユーザーDBを含めた全てのDBをダンプしている。
それを使って検証用VMを立ち上げたいと思ったのがきっかけ。やったことは

• DBのダンプをSCPで転送
• mysql -u root -p < dump.sqlでそのまま書き込み

この2点。

この後、PHPのWebアプリをDocumentRootに転送した後、アクセスしてみると

Fatal error: Uncaught PDOException: SQLSTATE[HY000] [1045] Access denied for user

その後色々してみたけど、ユーザーでログインもできない状態。なにこれ怖い。

原因の推測と対応

まあどう考えても、横着して全部のDBを一気にインポートしたのが間違いだと思う。

そういうわけで、ひとまずユーザーを全て削除してみることに。

select Host, User, Password from mysql.user;でユーザーを取得して、root以外は全て消す。

その後、もともと設定していたユーザーを登録し直す

結果

直った！MySQLに勝った！

MySQLのユーザーを移行する場合は、userテーブルだけdumpして、SQL見ながら実行するのが一番堅実だと思います。

SELECT message, COUNT(*) AS COUNT
FROM chats
GROUP BY message
ORDER BY COUNT DESC

条件指定する場合

SELECT message, COUNT(*) AS COUNT
FROM chats
where created_at BETWEEN ('2018-12-01 00:00:00') AND ('2018-12-31 00:00:00')
GROUP BY message
ORDER BY COUNT DESC

余談：RailsのActiveRecordでやる場合

# ActiveRecord
Chat.select(:message,:created_at)
  .where(created_at: '2018-12-01'..'2018-12-31')
  .order('count_message desc')
  .group(:message)
  .count(:message)

結合して重複なしソート

SELECT user_status_id,SUM(score),facebook_id,fullname,phone,address
FROM results
INNER JOIN user_vote_product_profiles ON results.user_status_id = user_vote_product_profiles.id
GROUP BY user_status_id
ORDER BY SUM(score) DESC;

そっからランダム抽出

SELECT user_status_id,SUM(score),facebook_id,fullname,phone,address
FROM results
INNER JOIN user_vote_product_profiles ON results.user_status_id = user_vote_product_profiles.id
GROUP BY user_status_id
HAVING SUM(score) >= 100
ORDER BY RAND()
LIMIT 9;

今回はクイズアプリの成績上位者からランダムで景品の当選者を選抜するのにこれらSQLを使った

以上。

SELECT message, COUNT(*) AS COUNT
FROM chats
GROUP BY message
ORDER BY COUNT DESC

条件指定する場合

SELECT message, COUNT(*) AS COUNT
FROM chats
where created_at BETWEEN ('2018-12-01 00:00:00') AND ('2018-12-31 00:00:00')
GROUP BY message
ORDER BY COUNT DESC

余談：RailsのActiveRecordでやる場合

# ActiveRecord
Chat.select(:message,:created_at)
  .where(created_at: '2018-12-01'..'2018-12-31')
  .order('count_message desc')
  .group(:message)
  .count(:message)

結合して重複なしソート

SELECT user_status_id,SUM(score),facebook_id,fullname,phone,address
FROM results
INNER JOIN user_vote_product_profiles ON results.user_status_id = user_vote_product_profiles.id
GROUP BY user_status_id
ORDER BY SUM(score) DESC;

そっからランダム抽出

SELECT user_status_id,SUM(score),facebook_id,fullname,phone,address
FROM results
INNER JOIN user_vote_product_profiles ON results.user_status_id = user_vote_product_profiles.id
GROUP BY user_status_id
HAVING SUM(score) >= 100
ORDER BY RAND()
LIMIT 9;

今回はクイズアプリの成績上位者からランダムで景品の当選者を取得するのにこれらSQLを使った

以上。