姚宁的博客

mysql备忘录

mac卸载、安装

<!--如果是通过brew安装的-->
brew uninstall mysql

<!--pkg安装的参考如下-->
https://gist.github.com/vitorbritto/0555879fe4414d18569d

<!--brew 安装-->
brew install mysql

错误解决

遇到
The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
或者
Failed! Error: Table 'mysql.role_edges' doesn't exist
执行 
mysql_upgrade -u root -p 

遇到命令行可以登录GUI无法登录并提示
Authentication plugin 'caching_sha2_password' cannot be loaded  
执行
alter user 'user'@'localhost' identified with mysql_native_password by 'password';

配置Mysql安全向导

执行 mysql_secure_installation

同意安装插件
Would you like to setup VALIDATE PASSWORD plugin?
-y

设置密码等级
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG
-1

设置root用户密码
Please set the password for root here

移除匿名用户
Remove anonymous users
-y

禁用root用户远程登录
Disallow root login remotely
-y

移除测试数据库
Remove test database and access to it
-y

重新载入权限表
Reload privilege tables now
-y

重置安全向导
mysql -p localhost -u root -p
uninstall mysql_secure_installation

重启 启动 停止

brew services start mysql
brew services restart mysql
brew services stop mysql

安全设置

SHOW VARIABLES LIKE 'validate_password%';
...
validate_password_policy             | MEDIUM
...
代表的是密码等级 中级要求包含数字大小写及特殊字符;

因为是本地 不用太关注密码 可以改为LOW

set global validate_password_policy=0;

用户 权限

创建用户 work并设置密码为iwork996(这里的密码要符合安全向导中设置的密码强度)
CREATE USER work@localhost IDENTIFIED BY 'iwork996';

给与yaoning用户test1库的所有权限
GRANT ALL ON test1.* TO yaoning@localhost;

显示用户
select user, host from mysql.user;

显示用户表所有字段信息
show full columns from mysql.user;

删除用户work
delete from mysql.user where user='work';

创建数据库test1
CREATE DATABASE test1;

删除数据库test1
DROP DATABASE IF EXISTS test1;

显示所有的数据库
SHOW DATABASES;

选择名为mysql的库;
USE mysql;

显示当前库的所有表;
SHOW TABLES;

刷新权限;
FLUSH PRIVILEGES;

退出;
exit

修改支持Emoji表情存储

查看编码情况
show variables where variable_name like 'character%';
character_set_server、character_set_database 这两个值不为utf8mb4

查找my.cnf文件
sudo find / -name my.cnf
修改
default-character-set、character-set-server为utf8mb4
collation-server为utf8mb4_unicode_ci

重启mysql
mysql.server restart

修改对应的表、字段支持utf8mb4、建议使用Navicat图形化工具

重启后再次查看编码情况
+--------------------------+------------------------------------------------------+
| Variable_name            | Value                                                |
+--------------------------+------------------------------------------------------+
| character_set_client     | utf8mb4                                              |
| character_set_connection | utf8mb4                                              |
| character_set_database   | utf8mb4                                              |
| character_set_filesystem | binary                                               |
| character_set_results    | utf8mb4                                              |
| character_set_server     | utf8mb4                                              |
| character_set_system     | utf8                                                 |
| character_sets_dir       | /usr/local/Cellar/mysql/8.0.11/share/mysql/charsets/ |
+--------------------------+------------------------------------------------------+
即OK

my.cnf参考配置下载

常用命令

创建名为work的数据库
create database work;

执行外部sql文件
source /Users/yaoning/Documents/github/self/python-crawl-example/zhixiaoo.sql

图形化客户端

sequelpro
Navicat

参考资料

参考资料
Mysql创建新用户
mysql修改安全设置
mysql_secure_installation 设置