记录一下日常工作中常用到的MySQL语句和一些配置等，方便日后查询

用户授权相关

创建用户

1
CREATE USER 'root'@'%' IDENTIFIED BY 'com.012';
1
CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'com.012';

修改密码

1
ALTER USER 'root'@'%' IDENTIFIED BY 'com.012';
1
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'com.012';

授权

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

授权的同时修改密码

1
Grant all privileges on *.* to 'root'@'%' identified by 'com.012' with grant option;

创建只读账号

1
GRANT SElECT ON *.* TO 'read_only'@'ip' IDENTIFIED BY "password"
1
2
3
4
5
CREATE USER 'read_only'@'ip' IDENTIFIED BY 'password';
GRANT SELECT ON *.* TO 'read_only'@'ip' WITH GRANT OPTION;

/* 删除权限 */
/* REVOKE all privileges ON *.* FROM 'read_only_user'@'ip'; */

库相关

建库

1
2
CREATE DATABASE `idp_app` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
CREATE DATABASE `idp_sdk` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

配置文件相关

apt安装的mysql配置文件在/etc/mysql/mysql.conf.d/mysqld.cnf

初始化密码

ubuntu系统通过apt安装的mysql，需要切换到root，然后执行mysql命令就可以登录（不用密码），对应的用户是root@localhost
当然也可以cat /etc/mysql/debian.cnf查看密码。

表名强制转换为小写

仅适用于MySQL 5.7 及以下版本，到了 8.0，只支持初始化时指定该参数，初始化之后，如果修改了该参数，启动就会报错。

配置文件在[mysqld]下面新增一行

1
2
[mysqld]
lower_case_table_names = 1

可以通过执行sql查看是否设置成功

1
show variables like 'lower_case_table_names';

binlog

查看binlog状态

1
2
3
4
5
6
7
8
9
10
11
12
MySQL [(none)] > show variables like 'log_bin%';
+---------------------------------+------------------------------------+
| Variable_name                   | Value                              |
+---------------------------------+------------------------------------+
| log_bin                         | ON                                 |
| log_bin_basename                | /data/mysql/binlog/mysql_bin       |
| log_bin_index                   | /data/mysql/binlog/mysql_bin.index |
| log_bin_trust_function_creators | OFF                                |
| log_bin_use_v1_row_events       | OFF                                |
+---------------------------------+------------------------------------+
5 rows in set (0.02 sec)

查看binlog模式

1
2
3
4
5
6
7
MySQL [(none)] > show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

binlog配置文件

1
2
3
4
5
6
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

排序

1
2
SELECT * from runoob_tbl ORDER BY submission_date ASC;  # 升序
SELECT * from runoob_tbl ORDER BY submission_date DESC;   # 降序

批量kill慢查询

1
select concat('KILL ',id,';') from information_schema.processlist where time > 5 AND COMMAND='Query';

复制输出的结果， 再执行