LoginSignup
7

More than 5 years have passed since last update.

MacのHomebrewでインストールするMySQLのデフォルトバージョンが5.7の環境で5.6に戻したいときの対処法

Posted at

はじめに

MacでHomeBrewを用いてMySQLをインストールしている環境でbrew updateを行った際、MySQLのバージョンが5.6から5.7にアップグレードされてしまった。
5.6と5.7でMySQLクライアントのライブラリ名が異なる関係でRailsアプリケーションが起動できない事象が発生するなど既存システムの保守に影響がでるため、5.6へのダウングレードを行う手順を以下にまとめる。

環境

  • OS
    • MacOS Sierra (10.12.6)
  • brew
    • Homebrew 1.5.3
    • Homebrew/homebrew-core (git revision 914a; last commit 2018-02-07)

手順

インストール状態を確認

% brew list | grep mysql
※インストールされていない場合何も表示されない。

すでに5.7がインストールされている場合は末尾に記載している完全削除の手順を用いてMySQLを削除する。

デフォルトのバージョンを確認

% brew info mysql
mysql: stable 5.7.21 (bottled), devel 8.0.3-rc
Open source relational database management system
https://dev.mysql.com/doc/refman/5.7/en/
Conflicts with:
  mariadb (because mysql, mariadb, and percona install the same binaries.)
  mariadb-connector-c (because both install plugins)
  mysql-cluster (because mysql, mariadb, and percona install the same binaries.)
  mysql-connector-c (because both install MySQL client libraries)
  percona-server (because mysql, mariadb, and percona install the same binaries.)
Not installed
From: https://github.com/Homebrew/homebrew-core/blob/master/Formula/mysql.rb
==> Dependencies
Build: cmake ✔
Required: openssl ✔
==> Requirements
Required: macOS >= 10.7 ✔
==> Options
--with-debug
    Build with debug support
--with-embedded
    Build the embedded server
--with-local-infile
    Build with local infile loading support
--with-memcached
    Build with InnoDB Memcached plugin
--with-test
    Build with unit tests
--devel
    Install development version 8.0.3-rc
==> Caveats
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start

5.6のMySQLをインストール

パッケージ名を確認

% brew search mysql
==> Searching local taps...
automysqlbackup                   mysql++                           mysql-connector-c                 mysql-sandbox                     mysql-utilities                   mysql@5.6
mysql                             mysql-cluster                     mysql-connector-c++               mysql-search-replace              mysql@5.5                         mysqltuner
==> Searching taps on GitHub...
caskroom/cask/mysql-connector-python     caskroom/cask/mysql-shell                caskroom/cask/mysql-utilities            caskroom/cask/navicat-for-mysql          caskroom/cask/sqlpro-for-mysql
==> Searching blacklisted, migrated and deleted formulae...

インストール

※パッケージ名は「mysql56」でも同じものがインストールされる。

% brew install mysql@5.6
Updating Homebrew...
==> Auto-updated Homebrew!
  1 ################################################################################
Updated 2 taps (caskroom/cask, homebrew/core).
==> Updated Formulae
amber                                               gxml                                                heroku                                              taisei

==> Downloading https://homebrew.bintray.com/bottles/mysql@5.6-5.6.39.sierra.bottle.tar.gz
Already downloaded: /Users/shungo/Library/Caches/Homebrew/mysql@5.6-5.6.39.sierra.bottle.tar.gz
==> Pouring mysql@5.6-5.6.39.sierra.bottle.tar.gz
==> /usr/local/Cellar/mysql@5.6/5.6.39/bin/mysql_install_db --verbose --user=shungo --basedir=/usr/local/Cellar/mysql@5.6/5.6.39 --datadir=/usr/local/var/mysql --tmpdir=/tmp
==> Caveats
A "/etc/my.cnf" from another install may interfere with a Homebrew-built
server starting up correctly.

MySQL is configured to only allow connections from localhost by default

To connect:
    mysql -uroot

This formula is keg-only, which means it was not symlinked into /usr/local,
because this is an alternate version of another formula.

If you need to have this software first in your PATH run:
  echo 'export PATH="/usr/local/opt/mysql@5.6/bin:$PATH"' >> ~/.zshrc

For compilers to find this software you may need to set:
    LDFLAGS:  -L/usr/local/opt/mysql@5.6/lib
    CPPFLAGS: -I/usr/local/opt/mysql@5.6/include


To have launchd start mysql@5.6 now and restart at login:
  brew services start mysql@5.6
Or, if you don't want/need a background service you can just run:
  /usr/local/opt/mysql@5.6/bin/mysql.server start
==> Summary
🍺  /usr/local/Cellar/mysql@5.6/5.6.39: 346 files, 154.2MB

PATHに5.6のMySQLのバイナリの配置場所を追加

※例はzshだが他のシェルでも同様に設定

% vim ~/.zshrc
################################################################################
# mysql settings.
################################################################################
export PATH=/usr/local/opt/mysql@5.6/bin:${PATH}
export DYLD_LIBRARY_PATH=/usr/local/opt/mysql@5.6/:${DYLD_LIBRARY_PATH}

% source ~/.zshrc

RailsのRakeコマンドを利用した場合のエラー例

参考までに5.6へのPATH設定がなされていない場合は以下のようなエラーが発生する。

% rake db:create
rake aborted!
LoadError: dlopen(/Users/shungo/.rbenv/versions/2.2.2/gemsets/sample_gemset/extensions/x86_64-darwin-13/2.2.0-static/mysql2-0.3.20/mysql2/mysql2.bundle, 9): Library not loaded: libmysqlclient.18.dylib
  Referenced from: /Users/shungo/.rbenv/versions/2.2.2/gemsets/sample_gemset/extensions/x86_64-darwin-13/2.2.0-static/mysql2-0.3.20/mysql2/mysql2.bundle
  Reason: image not found - /Users/shungo/.rbenv/versions/2.2.2/gemsets/sample_gemset/extensions/x86_64-darwin-13/2.2.0-static/mysql2-0.3.20/mysql2/mysql2.bundle
/Users/shungo/.rbenv/versions/2.2.2/gemsets/sample_gemset/gems/mysql2-0.3.20/lib/mysql2.rb:31:in `require'
/Users/shungo/.rbenv/versions/2.2.2/gemsets/sample_gemset/gems/mysql2-0.3.20/lib/mysql2.rb:31:in `<top (required)>'
/Users/shungo/.rbenv/versions/2.2.2/gemsets/sample_gemset/gems/bundler-1.16.1/lib/bundler/runtime.rb:81:in `require'
/Users/shungo/.rbenv/versions/2.2.2/gemsets/sample_gemset/gems/bundler-1.16.1/lib/bundler/runtime.rb:81:in `block (2 levels) in require'
/Users/shungo/.rbenv/versions/2.2.2/gemsets/sample_gemset/gems/bundler-1.16.1/lib/bundler/runtime.rb:76:in `each'
/Users/shungo/.rbenv/versions/2.2.2/gemsets/sample_gemset/gems/bundler-1.16.1/lib/bundler/runtime.rb:76:in `block in require'
/Users/shungo/.rbenv/versions/2.2.2/gemsets/sample_gemset/gems/bundler-1.16.1/lib/bundler/runtime.rb:65:in `each'
/Users/shungo/.rbenv/versions/2.2.2/gemsets/sample_gemset/gems/bundler-1.16.1/lib/bundler/runtime.rb:65:in `require'
/Users/shungo/.rbenv/versions/2.2.2/gemsets/sample_gemset/gems/bundler-1.16.1/lib/bundler.rb:114:in `require'
/Users/shungo/__work__/sassor/development/github/ssr-sv-footballfish/api/config/application.rb:7:in `<top (required)>'
/Users/shungo/__work__/sassor/development/github/ssr-sv-footballfish/api/Rakefile:4:in `<top (required)>'
(See full trace by running task with --trace)

MySQLのプロセスが/user/local/var/mysqlへ書き込みできるように所有者、グループを変更

% ls -ld /usr/local/var/mysql
drwxr-xr-x  8 shungo  admin  272  2  8 18:11 /usr/local/var/mysql

% ls -l /usr/local/var/mysql
total 221184
-rw-rw----   1 shungo  admin  50331648  2  8 18:11 ib_logfile0
-rw-rw----   1 shungo  admin  50331648  2  8 18:11 ib_logfile1
-rw-rw----   1 shungo  admin  12582912  2  8 18:11 ibdata1
drwx------  81 shungo  admin      2754  2  8 18:11 mysql
drwx------  55 shungo  admin      1870  2  8 18:11 performance_schema
drwx------   2 shungo  admin        68  2  8 18:11 test

% sudo chown -R _mysql:_mysql /usr/local/var/mysql

MySQLサーバ起動

% sudo mysql.server start
Password:
Starting MySQL
.Logging to '/usr/local/var/mysql/skymac.local.err'.
 SUCCESS!

% ps -ef | grep mysql
    0 77005     1   0  6:21PM ttys005    0:00.03 /bin/sh /usr/local/Cellar/mysql@5.6/5.6.39/bin/mysqld_safe --datadir=/usr/local/var/mysql --pid-file=/usr/local/var/mysql/skymac.local.pid
   74 77110 77005   0  6:21PM ttys005    0:00.55 /usr/local/Cellar/mysql@5.6/5.6.39/bin/mysqld --basedir=/usr/local/Cellar/mysql@5.6/5.6.39 --datadir=/usr/local/var/mysql --plugin-dir=/usr/local/Cellar/mysql@5.6/5.6.39/lib/plugin --user=mysql --log-error=skymac.local.err --pid-file=/usr/local/var/mysql/skymac.local.pid
  501 77217 75709   0  6:21PM ttys005    0:00.00 grep --color=auto mysql

アクセス確認

% mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.39 Homebrew

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.

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> quit
Bye

その他注意点

5.6ではNULL許可されていないカラムに値なしでInsertしようとするとエラーが発生する。
Default値を設定することでも解消できるが、デフォルトで有効になっている「STRICT_TRANS_TABLES」を解除する方法もある。

% vim /usr/local/opt/mysql@5.6/my.cnf
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
↓
sql_mode=NO_ENGINE_SUBSTITUTION

設定変更後プロセスを再起動する。

参考

Mysqlの完全削除
https://community.jaspersoft.com/wiki/uninstall-mysql-mac-os-x
https://gist.github.com/vitorbritto/0555879fe4414d18569d

共有ライブラリパスの通し方
https://ameblo.jp/dumbobambino/entry-11824988849.html

デフォルト値がないカラムにinsertする際に値がない場合に登録できるようにする
http://sawara.me/mysql/1239/

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
7