mac下升级mysql5.7为mysql8
最重要的一步,先把所有的数据库备份下来
参考《## 使用mysqldump的--all-databases参数完整备份数据库时如何排除掉某些不需要备份的库?》
完整删除原来的mysql5.7
- 打开控制台
- 备份数据
- 用这个命令查看mysql的进程:
ps -ax | grep mysql
- 停止&kill掉所有的mysql进程
- 用
brew
删除mysql@5.7
:brew remove mysql brew cleanup
- 删除文件:
sudo rm /usr/local/mysql sudo rm -rf /usr/local/var/mysql sudo rm -rf /usr/local/mysql* sudo rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist sudo rm -rf /Library/StartupItems/MySQLCOM sudo rm -rf /Library/PreferencePanes/My*
- 删除
mysql
的启动项:launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
- 删除配置文件(如果没有就跳过):
# Edit this file: /etc/hostconfig # Remove the line MYSQLCOM=-YES-
- 删除原mysql的一些设置:
rm -rf ~/Library/PreferencePanes/My* sudo rm -rf /Library/Receipts/mysql* sudo rm -rf /Library/Receipts/MySQL* sudo rm -rf /private/var/db/receipts/*mysql*
- 重启电脑并确认已经成功清空原
mysql
- 用
brew install mysql
安装新版的mysql8
- 安装完成后运行命令:
mysql.server start
启动mysql - 注意修改mysql的环境变量:
export PATH="/usr/local/opt/mysql/bin$PATH"
- 先修改
root
密码:mysqladmin -u root password 'yourpassword'
- 安全设置
mysql_secure_installation
安全设置示例:
Securing the MySQL server deployment**.**
Enter password **for** user root**:**
**VALIDATE** **PASSWORD** **COMPONENT** can be used to test passwords
and improve security**.** It checks the strength **of** password
and allows the users to **set** only those passwords which are
secure enough**.** Would you like to setup **VALIDATE** **PASSWORD** component**?**
Press y**|****Y** **for** Yes**,** any other key **for** **No****:** no
#这里我选了不安全密码强度验证插件
Using existing password **for** root**.**
Change the password **for** root **?** **(****(**Press y**|****Y** **for** Yes**,** any other key **for** No**)** **:** no
#这里我选了不修改root密码
**...** skipping**.**
By **default****,** a MySQL installation has an anonymous user**,**
allowing anyone to log into MySQL without having to have
a user account created **for** them**.** This is intended only **for**
testing**,** and to make the installation go a bit smoother**.**
You should remove them before moving into a production
environment**.**
Remove anonymous users**?** **(**Press y**|****Y** **for** Yes**,** any other key **for** No**)** **:** y
Success**.**
#这里我选择了移除匿名用户
Normally**,** root should only be allowed to connect **from**
**'localhost'****.** This ensures that someone cannot guess at
the root password from the network**.**
Disallow root login remotely**?** **(**Press y**|****Y** **for** Yes**,** any other key **for** No**)** **:** y
Success**.**
#这里我选择了禁用root账号远程登录访问
By **default****,** MySQL comes **with** a database named **'test'** that
anyone can access**.** This is also intended only **for** testing**,**
and should be removed before moving into a production
environment**.**
Remove test database and access to it**?** **(**Press y**|****Y** **for** Yes**,** any other key **for** No**)** **:** no
**...** skipping**.**
#这里我选择了不移除测试数据库
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately**.**
Reload privilege tables now**?** **(**Press y**|****Y** **for** Yes**,** any other key **for** No**)** **:** y
Success**.**
#这里我选择了重新加载权限表,因为我前面选择了禁用root账号远程登录访问
All done**!**
提示
brew安装后MYSQL的配置文件my.cnf位置为:/usr/local/etc/my.cnf
没有可以自己创建
数据目录为:/usr/local/var/mysql
常用命令
启动:sudo mysql.server start 或者 brew services start mysql
关闭:sudo mysql.server stop 或者 brew services stop mysql
重启:sudo mysql.server restart 或者 brew services restart mysql
连接:mysql -u root -p
开机启动:brew services start mysql
注意:使用brew services可以把mysql注册为开机自动启动的系统服务在后台常驻运行,此方法适用于任何使用Homebrew安装的服务
使用mysql自带的mysql.server启动,但电脑重启后mysql服务不会自动启动
解决错误
用Navicat Premium尝试连接刚安装的MYSQL服务报如下错误:
Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found
先修改my.cnf:
在[mysqld]块增加下面两行代码:
[caching_sha2_password]
default_authentication_plugin= mysql_native_password
重启MYSQL服务: sudo mysql.server restart
然后进入mysql:
执行命令:FLUSH PRIVILEGES;