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

2018-07-27 约 2036 字预计阅读 5 分钟

参考：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吧。

切换重点

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

教程摘要

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

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

详细步骤

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

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

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

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

2. 删除MySQL 8.0的符号链接

1
brew unlink mysql

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

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

和

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

之类的东西。

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
$ 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

或：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
$ 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

很简单，执行以下命令即可：

1
brew install mysql56

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

1
echo 'export PATH="/usr/local/opt/[email protected]/bin:$PATH"' >> ~/.zshrc

附录：Mac通过brew安装MySQL后详细配置过程

4. 分配MySQL 5.6链接

安装MySQL 5.6之后，执行以下命令：

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

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

5. 启动MySQL 5.6并检查版本

用brew启动

`1`	`brew services start [email protected]`

或

1
mysql.server start

检查版本：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
$ 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服务并保存数据

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

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
$ 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启动。

疑难解答

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

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

由于mysqld等没有动，似乎是权威，但解决不了。

（由于目录结构根据版本不同而不同，可能会影响权限以外的内容）

MySQL 5.6和MySQL 5.7（/usr/local/var/mysql/）的初始数据目录结构

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# 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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 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

目录