macOS下MySQL 8.0 安裝與配置教程

一、前言

1、本教程主要內容

• 適用Homebrew安裝MySQL
• MySQL 8.0 基礎適用於配置
• MySQL shell管理常用語法示例(用戶、許可權等)
• MySQL字元編碼配置
• MySQL遠程訪問配置

2、本教程環境信息與適用範圍

• 環境信息

• 適用範圍

二、MySQL安裝

1、Homebrew安裝

macOS下的Homebrew就相當於CentOS下的yum或者是Ubuntu下的apt-get

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

2、Homebrew安裝與啟動MySQL服務

• 安裝mysql

brew install mysql

• 配置並啟動MySQL服務

brew tap homebrew/services
brew services start mysql

3、修改root密碼

mysqladmin -u root password 'yourpassword'

4、MySQL安裝測試

• 查看MySQL版本

#查看MySQL版本
mysql -V

#輸出示例
mysql  Ver 8.0.12 for osx10.13 on x86_64 (Homebrew)

• MySQL shell測試

#進入MySQL shell
mysql -u root -p

#成功進入會輸出以下信息
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.12 Homebrew

#查看資料庫
mysql> show databases;

#退出
mysql> exit;

三、MySQL安全設置

1、MySQL 8 安全設置介紹

MySQL 8 新增了安全設置嚮導，這對於在伺服器部署MySQL來說，簡化了安全設置的操作，非常棒，不過對於macOS來說，不是剛需，如果不感興趣可以直接跳過這個章節

安全設置大致分為以下幾個步驟/選項

1. 密碼強度驗證插件
2. 修改root賬號密碼
3. 移除匿名用戶
4. 禁用root賬戶遠程登錄
5. 移除測試資料庫（test）
6. 重新載入授權表

以上幾個步驟/選項根據自己需要來即可。

2、MySQL 8 安全設置示例

• 進入安全設置

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) : yes
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) : yes
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) : yes
Success.
#這裡我選擇了重新載入許可權表，因為我前面選擇了禁用root賬號遠程登錄訪問

All done!

四、MySQL shell管理語法示例

1、資料庫相關語法示例

#創建資料庫
mysql> CREATE DATABASE mydb;

#查看所有資料庫
mysql> SHOW DATABASES;

#使用數據並創建表
mysql> USE mydb;
mysql> CREATE TABLE test(id int,body varchar(100));

#查看表
mysql> SHOW TABLES;

2、用戶與訪問授權語法示例

#新建本地用戶
mysql> CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';

#新建遠程用戶
mysql> CREATE USER 'test'@'%' IDENTIFIED BY '123456';

#賦予指定賬戶指定資料庫遠程訪問許可權
mysql> GRANT ALL PRIVILEGES ON mydb.* TO 'test'@'%';

#賦予指定賬戶對所有資料庫遠程訪問許可權
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test'@'%';

#賦予指定賬戶對所有資料庫本地訪問許可權
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost';

#刷新許可權
mysql> FLUSH PRIVILEGES;

3、授權相關語法示例

#1、查看許可權
SHOW GRANTS FOR 'test'@'%';

#2、賦予許可權
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%';

#3、收回許可權
REVOKE ALL PRIVILEGES ON *.* FROM 'test'@'%';

#4、刷新許可權
FLUSH PRIVILEGES;

#5、刪除用戶
DROP USER 'test'@'localhost';

五、字元編碼配置

MySQL預設的編碼不是utf8，為了相容中文的存儲，還是需要配置一下

1、 修改字元編碼

#修改配置文件
vi /usr/local/etc/my.cnf

#修改1：增加client配置（文件開頭，[mysqld]之前）
[client]
default-character-set=utf8mb4

#修改2：增加mysqld配置（文件結尾，[mysqld]之後）
#charset
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

2、重啟生效

• 重啟MySQL服務

mysql.server restart
#也可以使用命令：brew services restart mysql
#不過建議使用命令：mysql.server restart在出錯時可以看到更準確完整的信息

• 查看字元編碼

#進入MySQL shell
mysql -u root -p

#查看字元編碼
mysql>  show variables like '%char%';

六、遠程訪問配置

MySQL預設綁定了ip：127.0.0.1。如果我們需要遠程訪問，去掉該配置即可

1、 修改ip綁定

#修改配置文件
vi /usr/local/etc/my.cnf

#註釋掉ip-address選項
[mysqld]
# Only allow connections from localhost
#bind-address = 127.0.0.1

2、重啟生效

• 重啟MySQL服務

mysql.server restart

七、備註

相關閱讀

• MySQL中的utf8

http://www.infoq.com/cn/articles/in-mysql-never-use-utf8-use-utf8

• MySQL遠程訪問與bind-address問題

https://serverfault.com/questions/139323/how-to-bind-mysql-server-to-more-than-one-ip-address

本文首發於我的獨立博客：https://ken.io/note/macos-mysql8-install-config-tutorial