一、前言
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来说,不是刚需,如果不感兴趣可以直接跳过这个章节
安全设置大致分为以下几个步骤/选项
- 密码强度验证插件
- 修改root账号密码
- 移除匿名用户
- 禁用root账户远程登录
- 移除测试数据库(test)
- 重新加载授权表
以上几个步骤/选项根据自己需要来即可。
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/artic...
- MySQL远程访问与bind-address问题
https://serverfault.com/quest...