【MySQL系列】常规运维操作

---

前言

记录 MySQL 在日常运维中可能会使用到的命令、Sql语句。

---

一、常用命令行

1.备份

全库备份

mysqldump \
  	-h MYSQL_IP \
  	-u MYSQL_ADMIN_USER \
  	-p MYSQL_ADMIN_PASSWORD \
  	--socket /var/run/mysql/default.mysql.socket \
  	--skip-opt \
  	--create-options \
  	--single-transaction \
  	-q \
  	--no-autocommit \
  	-R \
  	--triggers \
  	--default-character-set=utf8 \
    --all-databases | gzip > /dbbak/all_db_$(date +%Y%m%d_%H%M%S).gz;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

单库备份

mysqldump \
  	-h MYSQL_IP \
  	-u MYSQL_ADMIN_USER \
  	-p MYSQL_ADMIN_PASSWORD \
  	--socket /var/run/mysql/default.mysql.socket \
  	--skip-opt \
  	--create-options \
  	--single-transaction \
  	-q \
  	--no-autocommit \
  	-R \
  	--triggers \
  	--default-character-set=utf8 \
    --events 库名| gzip > /dbbak/库名_$(date +%Y%m%d_%H%M%S).gz;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

参数介绍

• -h：–host=<主机名/IP地址>用于指定连接的数据库所在的主机名或IP地址。
• -u: --user=<用户名>用于指定登录MySQL的用户名。
• -p: --password[=<密码>]用于指定登录MySQL用户的密码。如果不指定密码，将会提示输入密码。
• –socket：指定连接mysql的socket文件位置，默认路径/tmp/mysql.sock
• –skip-opt：禁用–opt选项.（–opt等同于–add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys 该选项默认开启）
• –create-options：在CREATE TABLE语句中包括所有MySQL特性选项。
• –single-transaction：该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和–lock-tables 选项是互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用–quick 选项。
• -q：不缓冲查询，直接导出到标准输出。默认为打开状态，使用–skip-quick取消该选项。
• –no-autocommit：使用autocommit/commit 语句包裹表
• -R：导出存储过程以及自定义函数。
• –triggers：导出触发器。该选项默认启用，用–skip-triggers禁用它
• –default-character-set：设置默认字符集，默认值为utf8
• –events：导出事件
• –all-databases：导出所有的库里面的数据。

2.备份恢复

# 3. 还原 testdb 数据库
gunzip /dbbak/库名_$(date +%Y%m%d_%H%M%S).gz

mysql \
  	-h$BK_MYSQL_IP \
  	-u$BK_MYSQL_ADMIN_USER \
  	-p$BK_MYSQL_ADMIN_PASSWORD \
  	--socket /var/run/mysql/default.mysql.socket \
  	库名 < /data/dbbak/库名_$(date +%Y%m%d_%H%M%S)
1
2
3
4
5
6
7
8
9

二、常用 SQL

0. 创建 MySQL 用户, 并设置密码为 MYSQL2023.
CREATE USER 'user1'@'localhost' IDENTIFIED BY ' MYSQL2023.';
    -- 注：'user1'@'localhost' 和 'user1'@'127.0.0.1' 会被识别为两个不同的用户
    
1.修改用户密码
ALTER USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpassword'

2. 查看所有用户信息
SELECT user,host FROM mysql.user;

3. 删除用户
DROP USER 'user1'@'localhost';

4. 创建密码为  MYSQL2023. 的用户 user1，并赋予其对所有数据库有所有的操作权限（ALL)
-- 注意mysql8创建用户和授权需要分开执行
GRANT ALL ON *.* TO 'user1'@'%' IDENTIFIED BY ' MYSQL2023.' WITH GRANT OPTION;
FLUSH PRIVILEGES;	-- 刷新权限

5. 创建密码为  MYSQL2023. 的用户 user2，并赋予其对所有数据库只有只读权限（SELECT)
GRANT SElECT ON *.* TO 'user2'@'%' IDENTIFIED BY " MYSQL2023.";
FLUSH PRIVILEGES;	-- 刷新权限

6. 查看 user1 的权限
SHOW GRANTS FOR 'user1'@'%';

7. 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS dbname DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

8. 查看所有数据库
SHOW DATABASES;

9. 查看数据库的建库语句
SHOW CREATE DATABASE dbname;

10. 查看某表的创建语句
SHOW CREATE TABLE tablename;

11. 查看所有数据库大小
SELECT
	table_schema AS '数据库',
	SUM(table_rows) AS '记录数',
	SUM(TRUNCATE(data_length/1024/1024/1024, 2)) AS '数据容量(GB)',
	SUM(TRUNCATE(index_length/1024/1024/1024, 2)) AS '索引容量(GB)'
FROM
	information_schema.tables
GROUP BY
	table_schema
ORDER BY
	SUM(data_length) DESC,
	SUM(index_length) DESC;

12. 查看某个库中的表大小
SELECT
	table_schema AS '数据库',
	table_name AS '表名',
	table_rows AS '记录数',
	TRUNCATE(data_length/1024/1024/1024, 2) as '数据容量(GB)',
	TRUNCATE(index_length/1024/1024/1024, 2) as '索引容量(GB)'
FROM
	information_schema.tables
WHERE
	table_schema='表名称' /* 数据库名 */
ORDER BY
	table_rows DESC,
	index_length DESC;


13. 查询当前 MySQL 版本
SELECT @@VERSION;

14. 查询定义的packet大小
select @@max_allowed_packet;

15. 查看当前mysqld的所有参数，包括默认值
SHOW VARIABLES;

16. 查询当前 MySQL 实例的端口
SHOW VARIABLES LIKE 'port';

17. 查询 MySQL 实例的 socket 文件路径
SHOW VARIABLES LIKE 'socket';

18. 查看实例的数据路径
SHOW VARIABLES LIKE 'datadir';

19. 查看是否开启了慢查询日志, 以及慢日志的路径, ON代表开启
SHOW VARIABLES LIKE 'slow_query_log%';

20. 查看从服务器是否开启慢查询日志，ON代表开启
SHOW VARIABLES LIKE 'log_slow_slave_statements';

21. 查看慢查询时间，查询超过这个时间即被标记为慢查询
SHOW VARIABLES LIKE 'long_query_time';

22. 在线开启慢日志
SET GLOBAL slow_query_log=1;

23. 在线修改慢日志路径
SET GLOBAL slow_query_log_file='/tmp/slow.log';

24. 在线修改慢日志查询时间
SET GLOBAL long_query_time=0.2;		/* 需要重新登录命令行 */

25. 查看日志的输出格式
SHOW VARIABLES LIKE 'log_output';  /* FILE 或 TABLE */

26. 查看日志的时间信息
SHOW VARIABLES LIKE 'log_timestamps';  /* UTC时间或者SYSTEM时间 */

27. 查看是否开启 '将没有使用索引的SQL语句记录到慢查询日志中' 的功能
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

28. 限制每分钟内，在慢查询日志中，记录没有使用索引的次数
/* 一般与上面的参数一起使用, 避免日志快速增长。 */
SHOW VARIABLES LIKE 'log_throttle_queries_not_using_indexes';

29. 查看创建的临时表的存储引擎类型
SHOW VARIABLES LIKE "default%tmp%";

30. 查询log文件大小
SHOW VARIABLES LIKE 'innodb_log_file_size';

31. 查询页的大小
/*
	1) 一旦数据库通过innodb_page_size设置完成，则后续无法更改,
	2) innodb_page_size 是针对普通表的，压缩表不受限制。
*/
SHOW VARIABLES LIKE 'innodb_page_size';

32. 查看缓冲池的大小
/*
	1) 每次读写数据都是通过buffer pool,当buffer pool中没有所需的数据时，才去硬盘中获取。
	2) 该值设置的越大越好,buffer pool也是以页（page）为单位的，且大小和innodb_page_size一致。
*/
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

33. 在线调整innodb_buffer_pool_size
/* MySQL 5.7之前的版本，修改该值，需要重启 */
SET GLOBAL innodb_buffer_pool_size=2*1024*1024*1024;

34. 设置了多少个缓冲池
/* 设置多个instance可将热点打散，提高并发性能（建议设置成cpu 个数值） */
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';

35. 是否在停机时 dump 出 buffer pool数据
/*
	在MySQL 5.6 以后，可以在停机的时候dump出buffer pool的数据，然后在启动的时候Load进buffer pool。该功能可以在MySQL启动时自动预热，无需人工干预。
*/
SHOW VARIABLES LIKE 'innodb_buffer_pool_dump_at_shutdown';

36. 启动时加载dump的文件
/* 恢复到buffer pool中。dump的越多，启动的越慢 */
SHOW VARIABLES LIKE 'innodb_buffer_pool_load_at_startup';

37. 查看 dump 百分比
/* 是每个buffer pool文件，而不是整体 */
SHOW VARIABLES LIKE 'innodb_buffer_pool_dump_pct';

38. 查看隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';

39. 设置隔离级别
SEt transaction_isolation='read-committed';

40. 是否将死锁信息打印到err_log中
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

41. master thread 每秒刷新redo的buffer到logfile
/* 5.7版本可以设置刷新间隔时间, 默认是1秒。 */
SHOW VARIABLES LIKE "%innodb_flush_log_at_timeout%";

42. 查看binlog的类型
/*
	STATEMENT: 记录SQL语句
	ROW: 记录SQL语句操作的那些行（行的变化）
	MIXED: mixed 混合statement 和 Row 格式（不推荐）。
*/
SHOW VARIABLES LIKE 'binlog_format';

43. 查看timeout参数
SHOW VARIABLES LIKE "%timeout%"

44. 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';

45. 查看 binlog 过期时间
show variables like 'expire_logs_days';

46. 修改 binlog 过期时间
set global expire_logs_days=7;

47. 显示表结构和列结构的命令
DESC dbname.tablename;

48. 显示正在执行的线程
SHOW PROCESSLIST;

49. 查看buffer pool的状态
SHOW ENGINE INNODB STATUS\\G;

50. 查看表的索引情况
SHOW INDEX FROM tablename;

51. 查看锁的信息
SELECT * FROM sys.innodb_lock_waits;

52. 查看 master 状态
SHOW MASTER STATUS\\G;	/*在主节点上执行*/

53. 查看所有的log文件
SHOW MASTER LOGS;		/*在主节点上执行*/

54. 查看 slave 状态
SHOW SLAVE STATUS\\G;	/*在从节点上执行*/

55. 在线清理 mysql-bin3306.000003 之前的日志
PURGE BINARY LOGS TO 'mysql-bin3306.000003';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217

三、MYSQL-增删改查语句

1.insert语句

-- 1. 字段和表中的顺序一致 ，DEFAULT代表使用该列的默认值
INSERT INTO tbl_name VALUES (值1), (值2), (DEFAULT);   
-- 2. 字段顺序打乱，字段与值一一对应
INSERT INTO tbl_name (字段3, 字段2, 字段1) VALUES (值3, 值2, 值1);
-- 3. set实现
INSERT INTO tbl_name set 字段1=值1，字段2=值2，字段3=值3
1
2
3
4
5
6

-- 如果主键冲突，则更新后面的字段
INSERT INTO tbl_name VALUES (值1), (值2), (值3) DUPLICATE KEY UPDATE 字段=值, …;
-- 如果主键冲突，则跳过该行插入
INSERT IGNORE INTO tbl_name ...
1
2
3
4

2.update语句

UPDATE tab_name SET 字段=值, 字段=值 WHERE 字段=值;
1

3.delete语句

DELETE FROM tab_name WHERE 字段=值;
-- 删除表中某一列值重复的项，只保留最小id的
DELETE  FROM tab_name a, tab_name b where a.id > b.id and a.字段=b.字段
1
2
3

4.select语句

执行顺序为：FROM > WHERE > GROUP BY > HAVING > ORDER BY > SELECT

SELECT 查询的内容 FROM tab_name WHERE 查询条件 GROUP BY 分组字段 HAVING 分组后的条件 ORDER BY 字段;
-- 连接查询
-- 拼接结果
union -- 对两个集合（结果）进行并集操作
union all  -- 可以有重复的并集
1
2
3
4
5

子查询：一个select查询的结果作为另一个查询的条件

SELECT * FROM tab_name1 where tid IN (SELECT id from tab_name2 WHERE xxx > x)
1

连接查询：通过某一字段将两表连接，通过不同的连接方式展示查询结果

-- 内连接：取两表交集
SELECT * FROM tab_name1 a  JOIN tab_name2 b ON a.aid = b.bid
-- 其实等同于
SELECT * FROM tab_name1 a ,tab_name2 b WHERE a.aid = b.bid
-- 外连接-左连接：以左表为主取值，查出的结果条数=左表的总条数
SELECT * FROM tab_name1 a LEFT JOIN tab_name2 b ON a.aid = b.bid
-- 外连接-右连接：以右表为主取值，查出的结果条数=右表的总条数
SELECT * FROM tab_name1 a RIGHT JOIN tab_name2 b ON a.aid = b.bid
1
2
3
4
5
6
7
8

四、MYSQL函数

-- 内置函数
abs(x)          -- 绝对值 
format(x, d)    -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
ceil(x)         -- 向上取整 
floor(x)        -- 向下取整
round(x)        -- 四舍五入去整
mod(m, n)       -- m%n 求余 
pi()            -- 获得圆周率
pow(m, n)       -- m^n
sqrt(x)         -- 算术平方根
rand()          -- 随机数

-- 时间相关
now(), current_timestamp();     -- 当前日期时间
current_date();                 -- 当前日期
current_time();                 -- 当前时间
date( yyyy-mm-dd hh:ii:ss );    -- 获取日期部分
time( yyyy-mm-dd hh:ii:ss );    -- 获取时间部分
-- 双分支 类似java的三元 expr为表达式
if(expr1,expr2,expr3)  -- expr1成立则返回expr2的值，反之返回expr3的值

-- 聚合函数
count()      -- 计数
sum()        -- 求和
max()        -- 最大值
min()        -- 最小值
avg()        -- 平均值

-- 分组 类似于java中stream的groupingby
group_concat([DISTINCT] column1 [ORDER BY column2 ASC\DESC] [SEPARATOR seq]);-- 将分组中column1这一列对应的多行的值 [去重] 按照column2 升序或者降序进行连接，其中分隔符为seq
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30