一、前言

1、本教程主要内容

  • 适用Homebrew安装MySQL
  • MySQL 8.0 基础适用于配置
  • MySQL shell管理常用语法示例(用户、权限等)
  • MySQL字符编码配置
  • MySQL远程访问配置

2、本教程环境信息与适用范围

  • 环境信息
软件/环境 版本/说明
macOS macOS High Sierra
MySQL MySQL 8.0.12
  • 适用范围
软件 版本
macOS macOS
MySQL 8.0.x

二、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) : 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!

四、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