はじめに
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/