MySQL8.0常用操作

全局配置

查看数据库的字符集

查看 MySQL 字符集

mysql> show variables like 'chara%';
+--------------------------+-----------------------------------------+
| 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/hadoop/mysql/share/charsets/ |
+--------------------------+-----------------------------------------+

utf8mb4 已成为 MySQL 8.0 的默认字符集。
查看数据库的编码格式

mysql> select schema_name,default_character_set_name from information_schema.schemata where schema_name = 'record';
+-------------+----------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME |
+-------------+----------------------------+
| record      | utf8                       |
+-------------+----------------------------+

查看数据表的编码格式

show create table <表名>;

修改数据库的编码格式

mysql>alter database <数据库名> character set utf8mb4;

修改数据表的编码格式

alter table <表名> character set utf8mb4;

修改字段编码格式

alter table <表名> change <字段名> <字段名> <类型> character set utf8mb4;

查看mysql的排序规则

mysql> show variables where Variable_name like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+

在MySQL 8.0.1及更高版本中将 utf8mb4_0900_ai_ci 作为默认排序规则。

用户管理

查看MYSQL数据库中所有用户

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

创建用户

create user 'root'@'%' identified with mysql_native_password by '123456';

修改密码

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '654321';

删除用户

drop user 'airflow'@'localhost';

权限管理

查看用户授权信息

show grants for 'hive'@'%';

授权

grant all privileges on *.* to 'root'@'%' with grant option;

with gran option表示该用户可给其它用户赋予权限，但不可能超过该用户已有的权限。
比如a用户有select,insert权限，也可给其它用户赋权，但它不可能给其它用户赋delete权限，除了select,insert以外的都不能。这句话可加可不加，视情况而定。
第一个*表示通配数据库，可指定新建用户只可操作的数据库
如：

grant all privileges on hive.* to 'hive'@'localhost';

第二个*表示通配表，可指定新建用户只可操作的数据库下的某个表
如：

grant all privileges on hive.指定表名 to 'hive'@'localhost';

all privileges 可换成select,update,insert,delete,drop,create等操作
如授权给’‘airflow’@’%'用户对airflow数据库下的mdm_user表拥有select权限

grant select on airflow.mdm_user to 'airflow'@'%';

撤销权限

用户有什么权限就撤什么权限

revoke all privileges on *.* from 'airflow'@'%';

修改用户的主机名

use mysql;
update user set user.host='10.61.151.%' where user.user='root' and user.host='%';

限制多个IP网段范围从远程访问

例如：限制2个IP网段（10.61.153.%，10.41.123.%）允许通过hive用户远程访问hive数据库

create user 'hive'@'10.61.153.%' identified by 'hive';
grant all privileges on hive.* to 'hive'@'10.61.153.%';

create user 'hive'@'10.41.123.%' identified by 'hive';
grant all privileges on hive.* to 'hive'@'10.41.123.%';

MySQL8.0开始分配权限不能带密码隐式创建帐号

权限修改生效

刷新权限的SQL命令:

FLUSH PRIVILEGES;

修改完权限以后,一定要刷新权限表,才能使权限生效!!!

函数

创建函数

DELIMITER $$
CREATE DEFINER=`xxx_user`@`%` FUNCTION `getWorkDay`(
	`beginday` DATETIME,
	`endday` DATETIME
) RETURNS int(11)
GEGIN
RETURN 
(SELECT COUNT(1) FROM working_calendar  WHERE `date` BETWEEN beginday AND endday AND is_work_day=1);
END $$
DELIMITER ;

查看MySQL自定义函数脚本内容

show create function 函数名称;

删除MySQL自定义函数

drop function 函数名称;

问题及解决

mysql远程连接报错

问题描述

ERROR 1129 (HY000): Host '10.168.7.210' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

问题原因
一般max_connect_errors = 10，如果某一客户端尝试连接此MySQL服务器，但是失败（如密码错误等等）10次，则MySQL会无条件强制阻止此客户端连接。
解决方案
方法1、重置此计数器的值，方法：重启MySQL服务器或者执行 mysql> flush hosts; 命令，当这一客户端成功连接一次MySQL服务器后，针对此客户端的max_connect_errors会清零。
方法2、或者调高错误连接的数量，具体方法如下
管理员账户连接mysql，执行如下命令

mysql> show variables like 'max_connect_errors';
mysql> set global max_connect_errors = 1000;  
mysql> flush hosts;

如果想长期生效，并修改my.cnf配置文件，默认情况下，my.cnf文件中可能没有配置global max_connect_errors，如果需要设置此数值，手动添加即可。
vi /etc/my.cnf

max_connect_errors = 1000