Mac使用brew安装的MySQL版本切换

Author Avatar
Reborn 7月 27, 2018
  • 在其它设备中阅读本文章

参考:https://www.jianshu.com/p/f1a5a680b464

☆☆20180801更新:通过测试发现直接使用brew services start [email protected]即可切换至5.6版本使用,不需要brew unlink,是否可用请自行测试,测试前请备份好数据!!☆☆

背景

今天因为要写JavaEE大作业运行了一遍队友发过来的项目,却无法正常运行。经过一番追踪,发现是MySQL版本不对的问题,项目使用的MySQL版本是5.6(从使用的JDBC驱动就可以看出来)。本来想着更新JDBC驱动就可以解决,却发现Hibernate也要更新,还不清楚Hibernate支不支持8.0的MySQL,想了一下还是决定装多一个版本的MySQL吧。

切换重点

  1. /usr/local/var/mysql/通过切换每个版本来使用数据库设置和数据
  2. 使用brew unlinkbrew link切换活动版本的MySQL

教程摘要

下面我们来看一下大概流程:

  • 停止运行MySQL
  • 在设置和数据目录(usr/local/var/mysql)之间切换
  • brew unlink并brew link与切换活动的MySQL
  • 启动MySQL

详细步骤

本例以已安装MySQL 8.0再安装5.6 为例

1. 备份/usr/local/var/mysql/

由于存储了设置和DB数据,因此应该备份好,然后撤离。

 mv /usr/local/var/mysql /usr/local/var/mysql_80

2. 删除MySQL 8.0的符号链接

brew unlink mysql

解释:
这个符号链接 指的是诸如

/usr/local/bin/mysql -> ../Cellar/mysql/5.7.10/bin/mysql

/usr/local/lib/libmysqlclient.20.dylib -> ../Cellar/mysql/5.7.10/lib/libmysqlclient.20.dylib

之类的东西。

注意:
如果此符号链接存在,安装另一个版本将可能导致以下错误:

$ brew install mysql56
==> Reinstalling homebrew/versions/mysql56
==> Downloading https://homebrew.bintray.com/bottles-versions/mysql56-5.6.27.el_capitan.bottle.tar.gz
Already downloaded: /Library/Caches/Homebrew/mysql56-5.6.27.el_capitan.bottle.tar.gz
==> Pouring mysql56-5.6.27.el_capitan.bottle.tar.gz
Error: The `brew link` step did not complete successfully
The formula built, but is not symlinked into /usr/local
Could not symlink bin/innochecksum
Target /usr/local/bin/innochecksum
is a symlink belonging to mysql. You can unlink it:
  brew unlink mysql

To force the link and overwrite all conflicting files:
  brew link --overwrite mysql56

To list all files that would be deleted:
  brew link --overwrite --dry-run mysql56

或:

$ brew install mysql56
Updating Homebrew...
==> Auto-updated Homebrew!
Updated 1 tap (homebrew/cask).
No changes to formulae.

==> Downloading https://homebrew.bintray.com/bottles/[email protected]
######################################################################## 100.0%
==> Pouring [email protected]
==> /usr/local/Cellar/[email protected]/5.6.40/bin/mysql_install_db --verbose --user=xxx --basedir=/usr/local/Cellar/[email protected]/5.6.40 --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/[email protected]/bin:$PATH"' >> ~/.zshrc

For compilers to find this software you may need to set:
    LDFLAGS:  -L/usr/local/opt/[email protected]/lib
    CPPFLAGS: -I/usr/local/opt/[email protected]/include


To have launchd start [email protected] now and restart at login:
  brew services start [email protected]
Or, if you don't want/need a background service you can just run:
  /usr/local/opt/[email protected]/bin/mysql.server start
==> Summary
🍺  /usr/local/Cellar/[email protected]/5.6.40: 340 files, 154.0MB

3. 安装MySQL 5.6

很简单,执行以下命令即可:

brew install mysql56

安装后记得导入环境变量:

echo 'export PATH="/usr/local/opt/[email protected]/bin:$PATH"' >> ~/.zshrc
附录:Mac通过brew安装MySQL后详细配置过程

4. 分配MySQL 5.6链接

安装MySQL 5.6之后,执行以下命令:

brew unlink mysql && brew link [email protected] --force

注意:尽管可以通过执行链接来重新分配链接,但最好首先清除链接,因为发出大量日志很难发现错误。--force参数看具体情况使用,若brew link [email protected]不提示要强制链接的话就不需要该参数了。

5. 启动MySQL 5.6并检查版本

用brew启动

brew services start [email protected]

mysql.server start

检查版本:

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.40 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>

从日志可以确认MySQL 5.6启动。
如果版本中显示5.7,我们要检查日志等,因为有可能无法替换符号链接。

6. 停止MySQL 5.6服务并保存数据

$ mysql.server stop
Shutting down MySQL
. SUCCESS!
$ mv /usr/local/var/mysql /usr/local/var/mysql_56

7. 恢复最初安装的MySQL 5.7启动

$ mv /usr/local/var/mysql_80 /usr/local/var/mysql
$ brew unlink mysql56 && brew link mysql
Unlinking /usr/local/Cellar/mysql56/5.6.40... 99 symlinks removed
Linking /usr/local/Cellar/mysql/8.0.11... 79 symlinks created
$ mysql.server start
Starting MySQL
. SUCCESS!
$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 8.0.11 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>

从日志可以确认MySQL 8.0启动。

疑难解答

  1. /usr/local/var/mysql/为什么有必要切换
    如果至少启动一次8.0,如果使用相同的数据运行5.6,将出现以下错误:

     Starting MySQL
    . ERROR! The server quit without updating PID file (/usr/local/var/mysql/xxx.local.pid).
    

    由于mysqld等没有动,似乎是权威,但解决不了。
    (由于目录结构根据版本不同而不同,可能会影响权限以外的内容)

  2. MySQL 5.6和MySQL 5.7(/usr/local/var/mysql/)的初始数据目录结构

    # MySQL 5.6
    $ ls -l /usr/local/var/mysql
    -rw-rw----   1 xxx  admin        56  7 26 20:54 auto.cnf
    -rw-r-----   1 xxx  admin         0  7 26 20:50 binlog.index
    -rw-r-----   1 xxx  admin     10369  7 27 18:30 xxx.local.err
    -rw-rw----   1 xxx  admin         5  7 27 18:30 xxx.local.pid
    -rw-rw----   1 xxx  admin  50331648  7 27 18:30 ib_logfile0
    -rw-rw----   1 xxx  admin  50331648  7 26 20:46 ib_logfile1
    -rw-rw----   1 xxx  admin  12582912  7 27 18:30 ibdata1
    drwx------  81 xxx  admin      2754  7 26 20:46 mysql
    drwx------  55 xxx  admin      1870  7 26 20:46 performance_schema
    
    # MySQL 8.0
    $ ls -l /usr/local/var/mysql_80
    -rw-r-----    1 xxx  admin        56  7 26 19:44 auto.cnf
    -rw-r-----    1 xxx  admin       178  7 26 19:50 binlog.000001
    -rw-r-----    1 xxx  admin     11628  7 26 20:42 binlog.000002
    -rw-r-----    1 xxx  admin        32  7 26 19:51 binlog.index
    -rw-------    1 xxx  admin      1680  7 26 19:44 ca-key.pem
    -rw-r--r--    1 xxx  admin      1112  7 26 19:44 ca.pem
    -rw-r-----    1 xxx  admin      2783  7 26 20:42 xxx.local.err
    -rw-r-----    1 xxx  admin         6  7 26 19:51 xxx.local.pid
    -rw-r--r--    1 xxx  admin      1112  7 26 19:44 client-cert.pem
    -rw-------    1 xxx  admin      1676  7 26 19:44 client-key.pem
    -rw-r-----    1 xxx  admin      3346  7 26 20:42 ib_buffer_pool
    -rw-r-----    1 xxx  admin  50331648  7 26 20:42 ib_logfile0
    -rw-r-----    1 xxx  admin  50331648  7 26 19:44 ib_logfile1
    -rw-r-----    1 xxx  admin  12582912  7 26 20:42 ibdata1
    drwxr-x---    8 xxx  admin       272  7 26 19:44 mysql
    -rw-r-----    1 xxx  admin  25165824  7 26 20:19 mysql.ibd
    drwxr-x---  104 xxx  admin      3536  7 26 19:44 performance_schema
    -rw-------    1 xxx  admin      1680  7 26 19:44 private_key.pem
    -rw-r--r--    1 xxx  admin       452  7 26 19:44 public_key.pem
    -rw-r--r--    1 xxx  admin      1112  7 26 19:44 server-cert.pem
    -rw-------    1 xxx  admin      1676  7 26 19:44 server-key.pem
    drwxr-x---    3 xxx  admin       102  7 26 19:44 sys
    -rw-r-----    1 xxx  admin  10485760  7 26 20:42 undo_001
    -rw-r-----    1 xxx  admin  10485760  7 26 20:42 undo_002