课程频道导航
课程分类导航
当前位置：首页 > 操作系统 > MacOs

macOS下MySQL 8.0 安装与配置教程

  • 适用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

<code>/usr/bin/ruby -e <span class="hljs-string">"<span class="hljs-variable">$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
</span></span></code>

2、Homebrew安装与启动MySQL服务

  • 安装mysql
<code>brew install mysql
</code>
  • 配置并启动MySQL服务
<code>brew tap homebrew/services
brew services start mysql
</code>

3、修改root密码

<code>mysqladmin -u root password <span class="hljs-string">‘yourpassword‘
</span></code>

4、MySQL安装测试

  • 查看MySQL版本
<code><span class="hljs-comment">#查看MySQL版本
mysql -V

<span class="hljs-comment">#输出示例
mysql  Ver 8.0.12 <span class="hljs-keyword">for osx10.13 on x86_64 (Homebrew)
</span></span></span></code>
  • MySQL shell测试
<code><span class="hljs-comment">#进入MySQL shell
mysql -u root -p

<span class="hljs-comment">#成功进入会输出以下信息
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 12
Server version: 8.0.12 Homebrew

<span class="hljs-comment">#查看数据库
mysql> show databases;

<span class="hljs-comment">#退出
mysql> <span class="hljs-built_in">exit;
</span></span></span></span></span></code>

三、MySQL安全设置

1、MySQL 8 安全设置介绍

MySQL 8 新增了安全设置向导，这对于在服务器部署MySQL来说，简化了安全设置的操作，非常棒，不过对于macOS来说，不是刚需，如果不感兴趣可以直接跳过这个章节

安全设置大致分为以下几个步骤/选项

  1. 密码强度验证插件
  2. 修改root账号密码
  3. 移除匿名用户
  4. 禁用root账户远程登录
  5. 移除测试数据库（test）
  6. 重新加载授权表

以上几个步骤/选项根据自己需要来即可。

2、MySQL 8 安全设置示例

  • 进入安全设置
<code>mysql_secure_installation
</code>

-设置示例

<code>Securing the MySQL server deployment.

Enter password <span class="hljs-keyword">for user root:

VALIDATE PASSWORD COMPONENT can be used to <span class="hljs-built_in">test passwords
and improve security. It checks the strength of password
and allows the users to <span class="hljs-built_in">set only those passwords <span class="hljs-built_in">which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y <span class="hljs-keyword">for Yes, any other key <span class="hljs-keyword">for No: no
<span class="hljs-comment">#这里我选了不安全密码强度验证插件

Using existing password <span class="hljs-keyword">for root.
Change the password <span class="hljs-keyword">for root ? ((Press y|Y <span class="hljs-keyword">for Yes, any other key <span class="hljs-keyword">for No) : no
<span class="hljs-comment">#这里我选了不修改root密码
 ... skipping.

By default, a MySQL installation has an anonymous user,
allowing anyone to <span class="hljs-built_in">log into MySQL without having to have
a user account created <span class="hljs-keyword">for them. This is intended only <span class="hljs-keyword">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 <span class="hljs-keyword">for Yes, any other key <span class="hljs-keyword">for No) : yes
Success.
<span class="hljs-comment">#这里我选择了移除匿名用户


Normally, root should only be allowed to connect from
<span class="hljs-string">‘localhost‘. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y <span class="hljs-keyword">for Yes, any other key <span class="hljs-keyword">for No) : yes
Success.
<span class="hljs-comment">#这里我选择了禁用root账号远程登录访问

By default, MySQL comes with a database named <span class="hljs-string">‘test‘ that
anyone can access. This is also intended only <span class="hljs-keyword">for testing,
and should be removed before moving into a production
environment.

Remove <span class="hljs-built_in">test database and access to it? (Press y|Y <span class="hljs-keyword">for Yes, any other key <span class="hljs-keyword">for No) : no
 ... skipping.
 <span class="hljs-comment">#这里我选择了不移除测试数据库

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y <span class="hljs-keyword">for Yes, any other key <span class="hljs-keyword">for No) : yes
Success.
<span class="hljs-comment">#这里我选择了重新加载权限表，因为我前面选择了禁用root账号远程登录访问

All <span class="hljs-keyword">done!
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

四、MySQL shell管理语法示例

1、数据库相关语法示例

<code><span class="hljs-comment">#创建数据库
mysql> CREATE DATABASE mydb;

<span class="hljs-comment">#查看所有数据库
mysql> SHOW DATABASES;

<span class="hljs-comment">#使用数据并创建表
mysql> USE mydb;
mysql> CREATE TABLE <span class="hljs-built_in">test(id int,body varchar(100));

<span class="hljs-comment">#查看表
mysql> SHOW TABLES;
</span></span></span></span></span></code>

2、用户与访问授权语法示例

<code><span class="hljs-comment">#新建本地用户
mysql> CREATE USER <span class="hljs-string">‘test‘@<span class="hljs-string">‘localhost‘ IDENTIFIED BY <span class="hljs-string">‘123456‘;

<span class="hljs-comment">#新建远程用户
mysql> CREATE USER <span class="hljs-string">‘test‘@<span class="hljs-string">‘%‘ IDENTIFIED BY <span class="hljs-string">‘123456‘;

<span class="hljs-comment">#赋予指定账户指定数据库远程访问权限
mysql> GRANT ALL PRIVILEGES ON mydb.* TO <span class="hljs-string">‘test‘@<span class="hljs-string">‘%‘;

<span class="hljs-comment">#赋予指定账户对所有数据库远程访问权限
mysql> GRANT ALL PRIVILEGES ON *.* TO <span class="hljs-string">‘test‘@<span class="hljs-string">‘%‘;

<span class="hljs-comment">#赋予指定账户对所有数据库本地访问权限
mysql> GRANT ALL PRIVILEGES ON *.* TO <span class="hljs-string">‘test‘@<span class="hljs-string">‘localhost‘;

<span class="hljs-comment">#刷新权限
mysql> FLUSH PRIVILEGES;
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

3、授权相关语法示例

<code><span class="hljs-comment">#1、查看权限
SHOW GRANTS FOR <span class="hljs-string">‘test‘@<span class="hljs-string">‘%‘;

<span class="hljs-comment">#2、赋予权限
GRANT ALL PRIVILEGES ON *.* TO <span class="hljs-string">‘test‘@<span class="hljs-string">‘%‘;

<span class="hljs-comment">#3、收回权限
REVOKE ALL PRIVILEGES ON *.* FROM <span class="hljs-string">‘test‘@<span class="hljs-string">‘%‘;

<span class="hljs-comment">#4、刷新权限
FLUSH PRIVILEGES;

<span class="hljs-comment">#5、删除用户
DROP USER <span class="hljs-string">‘test‘@<span class="hljs-string">‘localhost‘;
</span></span></span></span></span></span></span></span></span></span></span></span></span></code>

五、字符编码配置

MySQL默认的编码不是utf8，为了兼容中文的存储，还是需要配置一下

1、 修改字符编码

<code><span class="hljs-comment">#修改配置文件
vi /usr/<span class="hljs-built_in">local/etc/my.cnf

<span class="hljs-comment">#修改1：增加client配置（文件开头，[mysqld]之前）
[client]
default-character-set=utf8mb4

<span class="hljs-comment">#修改2：增加mysqld配置（文件结尾，[mysqld]之后）
<span class="hljs-comment">#charset
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
</span></span></span></span></span></code>

2、重启生效

  • 重启MySQL服务
<code>mysql.server restart
<span class="hljs-comment">#也可以使用命令：brew services restart mysql
<span class="hljs-comment">#不过建议使用命令：mysql.server restart在出错时可以看到更准确完整的信息
</span></span></code>
  • 查看字符编码
<code><span class="hljs-comment">#进入MySQL shell
mysql -u root -p

<span class="hljs-comment">#查看字符编码
mysql>  show variables like <span class="hljs-string">‘%char%‘;
</span></span></span></code>

六、远程访问配置

MySQL默认绑定了ip：127.0.0.1。如果我们需要远程访问，去掉该配置即可

1、 修改ip绑定

<code><span class="hljs-comment">#修改配置文件
vi /usr/<span class="hljs-built_in">local/etc/my.cnf

<span class="hljs-comment">#注释掉ip-address选项
[mysqld]
<span class="hljs-comment"># Only allow connections from localhost
<span class="hljs-comment">#bind-address = 127.0.0.1
</span></span></span></span></span></code>

2、重启生效

  • 重启MySQL服务
<code>mysql.server restart
</code>

七、备注

相关阅读

  • 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

 

macOS下MySQL 8.0 安装与配置教程

标签：输出   hat   进入   授权   grant   出错   root账户   immediate   key   

本文系统来源：https://www.cnblogs.com/lonelyxmas/p/10159223.html


【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！

相关教程推荐

其他课程推荐