技术的路上奔跑

入门

0%

mysql相关

发表于 更新于 分类于 ， Valine：
本文字数： 17k 阅读时长 ≈ 15 分钟

一、安装

1. windows

  • 下载msi安装包
  • 按照提示安装

1.1. mysqld的一些操作

1
2
3
4
5
6
7
8
:: 初始化配置，会创建data目录
mysqld --initialize-insecure

:::::::::: 服务相关 ::::::::::
:: 安装服务
mysqld --install
:: 卸载服务
mysqld --remove mysql

踩坑记

1) MYSQL报错 Failed to find valid data directory.

  • 先删除自己建立的data目录
  • 管理员使用cmd
1
2
3
4
5
6
7
8
9
10
11
:: 看服务中mysql注册的是什么服务，可能叫mysql80
net stop mysql
mysqld --remove mysql

:: 重新初始化配置
mysqld --initialize-insecure
:: 安装服务
mysqld --install

:: 启动服务
net start mysqld

二、配置

  • 推荐的一套配置
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
#
## my.cnf for 8.0版本
## author: yejr(yejinrong@zhishutang.com, http://imysql.com, QQ: 4700963)
##
## 叶金荣(yejr)
## 国内知名MySQL专家，MySQL布道师，Oracle MySQL ACE Director，腾讯云TVP成员。
## 微信公众：老叶茶馆(imysql_wx), 博客：https://imysql.com
## QQ群: 125572178
## 注意：个别建议可能需要根据实际情况作调整，请自行判断或联系我，本人不对这些建议结果负相应责任
## 本配置文件主要适用于MySQL 8.0版本
#
[client]
port	= 3306
socket	= /data/mysql/mysql.sock

[mysql]
prompt = "\u@mysqldb \R:\m:\s [\d]> "
no_auto_rehash

[mysqld]
user	= mysql
port	= 3306
#主从复制或MGR集群中，server_id记得要不同
#另外，实例启动时会生成 auto.cnf，里面的 server_uuid 值也要不同
#server_uuid的值还可以自己手动指定，只要符合uuid的格式标准就可以
server_id = 3306
basedir	= /usr/local/mysql
datadir	= /data/mysql
socket	= /data/mysql/mysql.sock
pid_file = mysqldb.pid
character_set_server = UTF8MB4
skip_name_resolve = 1
#若你的MySQL数据库主要运行在境外，请务必根据实际情况调整本参数
default_time_zone = "+8:00"
#启用admin_port，连接数爆满等紧急情况下给管理员留个后门
admin_address = '127.0.0.1'
admin_port = 33062

#performance setttings
lock_wait_timeout = 3600
open_files_limit    = 65535
back_log = 1024
max_connections = 512
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
thread_stack = 512K
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M

#log settings
log_timestamps = SYSTEM
log_error = /data/mysql/error.log
log_error_verbosity = 3
slow_query_log = 1
log_slow_extra = 1
slow_query_log_file = /data/mysql/slow.log
long_query_time = 0.1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_bin = /data/mysql/mybinlog
binlog_format = ROW
sync_binlog = 1 #MGR环境中由其他节点提供容错性，可不设置双1以提高本地节点性能
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
binlog_rows_query_log_events = 1
binlog_expire_logs_seconds = 604800
#MySQL 8.0.22前，想启用MGR的话，需要设置binlog_checksum=NONE才行
binlog_checksum = CRC32
gtid_mode = ON
enforce_gtid_consistency = TRUE

#myisam settings
key_buffer_size = 32M
myisam_sort_buffer_size = 128M

#replication settings
relay_log_recovery = 1
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 64 #可以设置为逻辑CPU数量的2倍
binlog_transaction_dependency_tracking = WRITESET
slave_preserve_commit_order = 1
slave_checkpoint_period = 2

#mgr settings
loose-plugin_load_add = 'mysql_clone.so'
loose-plugin_load_add = 'group_replication.so'
loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"
#MGR本地节点IP:PORT，请自行替换
loose-group_replication_local_address = "172.16.16.10:33061"
#MGR集群所有节点IP:PORT，请自行替换
loose-group_replication_group_seeds = "172.16.16.10:33061,172.16.16.11:33061,172.16.16.12:33061"
loose-group_replication_start_on_boot = OFF
loose-group_replication_bootstrap_group = OFF
loose-group_replication_exit_state_action = READ_ONLY
loose-group_replication_flow_control_mode = "DISABLED"
loose-group_replication_single_primary_mode = ON
loose-group_replication_communication_max_message_size = 10M
loose-group_replication_unreachable_majority_timeout = 30
loose-group_replication_member_expel_timeout = 5
loose-group_replication_autorejoin_tries = 288

#innodb settings
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 45875M
innodb_buffer_pool_instances = 4
innodb_data_file_path = ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit = 1 #MGR环境中由其他节点提供容错性，可不设置双1以提高本地节点性能
innodb_log_buffer_size = 32M
innodb_log_file_size = 1G #如果线上环境的TPS较高，建议加大至1G以上，如果压力不大可以调小
innodb_log_files_in_group = 3
innodb_max_undo_log_size = 4G
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_open_files = 65535
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_online_alter_log_max_size = 4G
innodb_print_ddl_logs = 1
innodb_status_file = 1
#注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log_error文件增长较快
innodb_status_output = 0
innodb_status_output_locks = 1
innodb_sort_buffer_size = 67108864
innodb_adaptive_hash_index = OFF
#提高索引统计信息精确度
innodb_stats_persistent_sample_pages = 500

#innodb monitor settings
innodb_monitor_enable = "module_innodb"
innodb_monitor_enable = "module_server"
innodb_monitor_enable = "module_dml"
innodb_monitor_enable = "module_ddl"
innodb_monitor_enable = "module_trx"
innodb_monitor_enable = "module_os"
innodb_monitor_enable = "module_purge"
innodb_monitor_enable = "module_log"
innodb_monitor_enable = "module_lock"
innodb_monitor_enable = "module_buffer"
innodb_monitor_enable = "module_index"
innodb_monitor_enable = "module_ibuf_system"
innodb_monitor_enable = "module_buffer_page"
#innodb_monitor_enable = "module_adaptive_hash"

#pfs settings
performance_schema = 1
#performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'

[mysqldump]
quick

1. 配置查看

  • mysql默认配置在/etc/my.cnf
  • 查看当前mysql的配置使用命令
1
2
3
4
5
6
7
8
9
mysql> show variables like '%sort_buffer_size';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size        | 524288  |
+-------------------------+---------+
3 rows in set (0.00 sec)

2. 配置说明

2.1. mysqld配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[mysqld]
# 写入数据库时innodb引擎的大小，调大可以加快写入引擎的速度
innodb_sort_buffer_size = 512K
# 单条sql语句的排序使用内存，跟引擎无关，只对查询生效
sort_buffer_size = 512K

########## innodb相关配置 ##########
# 死锁检测，会自动检测死锁并执行回滚，不开启会等待超时，如果超时也没开就卡死
innodb_deadlock_detect = ON
# 启动标准监控，会打印日志到log_error变量指定的文件中，监视活动事务持有的表锁、行锁；事务锁等待；线程信号量等待；文件IO请求；buffer pool统计信息；InnoDB主线程purge和change buffer merge活动。
innodb_status_output = ON
# 同上添加额外的锁信息
innodb_status_output_locks = ON
# 将每一次死锁的信息都输出到日志中，仅输出 LATEST DETECTED DEADLOCK 部分
innodb_print_all_deadlocks = ON

三、命令

1. 查看相关

1.1. 查看历史执行的sql语句记录

查看当前日志配置

  • log_output是FILE，会记录在general_log_file里面
  • log_output是TABLE，会记录在mysql.geral_log表里面
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> show variables like '%general_log%';
+------------------+---------------------------+
| Variable_name    | Value                     |
+------------------+---------------------------+
| general_log      | OFF                       |
| general_log_file | /var/lib/mysql/sql.log |
+------------------+---------------------------+
2 rows in set (0.00 sec)

mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

打开日志

1
2
3
4
5
6
mysql> set global log_output = 'FILE,TABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> set global general_log = 'ON';
Query OK, 0 rows affected (0.00 sec)

查看文件

1
2
3
4
5
6
7
8
=> tail -f /path/to/log
2022-02-19T03:15:02.002365Z     941903 Prepare  SELECT * FROM `uem_app_wp` WHERE (`app_type`=?) AND (updated_at < ?) AND (`state` IN (?,?))
2022-02-19T03:15:02.002444Z     941903 Execute  SELECT * FROM `uem_app_wp` WHERE (`app_type`=1) AND (updated_at < '2022-02-19 11:14:02') AND (`state` IN ('2','3'))
2022-02-19T03:15:02.003244Z     941903 Close stmt
2022-02-19T03:15:02.286509Z     941910 Connect  sdpSys@localhost on sdp using SSL/TLS
2022-02-19T03:15:02.287029Z     941910 Query    select @@version_comment limit 1
2022-02-19T03:15:02.287341Z     941910 Query    select count(distinct name, domain) from user_online_status_v2
2022-02-19T03:15:02.288005Z     941910 Quit

查看数据库

1
2
mysql> select * from mysql.general_log order by event_time desc limit 5\G;
...

1.2. 查看建表语句

1
mysql> show create table test_table;

1.3. 查看数据库表都有哪些索引

1
2
3
4
5
6
7
8
9
mysql> show index from resource;
+----------+------------+-------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name          | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| resource |          0 | PRIMARY           |            1 | id              | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| resource |          0 | resource_name     |            1 | name            | A         |          24 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| resource |          1 | resource_group_id |            1 | group_id        | A         |           3 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+-------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
15 rows in set (0.29 sec)

2. json操作

2.1. 查询

  • ->使用双引号包裹
  • ->>不使用双引号
1
2
3
4
5
6
mysql> select id, config -> '$.config.url' as requestUrl, config ->> '$.config.url' as requestUrl1 from auth_plugin_template where id='8d5cdeb4-e2db-11ea-9a8c-fefcfead2706';
+--------------------------------------+-------------------------------+-----------------------------+
| id                                   | requestUrl                    | requestUrl1                 |
+--------------------------------------+-------------------------------+-----------------------------+
| 8d5cdeb4-e2db-11ea-9a8c-fefcfead2706 | "https://qyapi.weixin.qq.com" | https://qyapi.weixin.qq.com |
+--------------------------------------+-------------------------------+-----------------------------+

2.2. 更新

1
mysql> update tab_json set data = json_set(data, "$.passcode", "654567") where id = 1;

3. 内置数据库

3.1. information_schema

1) tables表

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
mysql> select * from information_schema.tables\G;
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: sdp                -- 数据库名
     TABLE_NAME: device_approval    -- 数据表名
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 65536
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2022-03-04 15:50:48
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_bin
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT: 授信终端用户申请表
1 rows in set (0.02 sec)


4. 基本操作

4.1. 数据库操作

  • 语法
1
2
3
CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>]
[[DEFAULT] COLLATE <校对规则名>];

4.2. 数据表操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 创建数据表
CREATE TABLE `emm_data` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `path` varchar(512) COLLATE utf8mb4_bin NOT NULL,
  `size` int unsigned NOT NULL,
  `modify` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `emm_data_path` (`path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

-- 删除数据表
drop table emm_data

-- 对某个表加一列
alter table log add column logDate datetime default NULL comment '日志时间' after id;

---------- 事务操作 ----------
-- 启动事务
start transaction;
-- 回滚事务
rollback work;
-- 提交事务
commit work;

1) 索引

1
2
3
4
-- 创建索引，多个列就是联合索引
mysql> ALTER TABLE table_name ADD INDEX index_name(column_name,...)
-- 删除索引
mysql> ALTER TABLE table_name DROP INDEX index_name;

2) 查看列详细信息

1
mysql> SHOW FULL COLUMNS FROM table_name WHERE Field='column_name';

示例

1
2
3
4
5
6
7
8
mysql> SHOW FULL COLUMNS FROM resource WHERE Field='name';
+-------+-----------+----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type      | Collation      | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+-----------+----------------+------+-----+---------+-------+---------------------------------+---------+
| name  | char(255) | gbk_chinese_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+-----------+----------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.01 sec)

5. 数据库基本类型

类型描述
date日期，精确到天
datetime日期，精确到秒
text纯文本数据，无需限制长度，最大65535，不能设置默认值
char定长，不足会补空格
varchar起始位结束位占3个字节，可以存放65532字节的数据，变长，不需要不会申请空间

6. 列求和

1
mysql> select sum(size) from emm_data where id > '5';

7. 添加函数索引

  • 可以对数据库的列添加函数索引，注意函数外面的括号不能少
1
mysql> CREATE INDEX gbk_name_idx ON resource ((CONVERT(name USING gbk)));

8. 添加计算列

  • 添加某一列的数据跟随另一列进行计算得到，计算式放as后面
1
mysql> alter table resource add column name_gbk varchar(128) character set gbk as (convert(name using gbk));

9. 管理操作

9.1. 设置用户密码

1
mysql> alter user 'root'@'localhost' identified by '123456';

9.2. 创建用户

1
2
3
4
# 创建允许所有地址登陆的用户
mysql> create user 'test' identified by '123456';
# 创建仅允许本机登陆的用户
mysql> create user 'test'@'localhost' identified by '123456';

9.3. 赋予用户权限

1
2
3
4
# 授予某个表的查询和删除权限
mysql> GRANT SELECT, DELETE ON database_name.table_name TO 'test'@'localhost';
# 授予所有表的所有权限
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost';

10. mysql本身的命令

1
mysql -h <远程服务器IP地址> -P <端口号> -u <用户名> -p

四、慢查询

MySQL慢查询，一口从天而降的锅！

慢查询就是执行很慢的查询语句，超过配置的时间。一条慢查询可能导致阻塞其他查询语句，造成cpu猛增，影响系统的正常运行

1. 配置

1.1. 查看当前配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 慢查询日志记录开关和位置
mysql> show variables like "%slow_query%";
+-----------------------------+-----------------------------------+
| Variable_name               | Value                             |
+-----------------------------+-----------------------------------+
| slow_query_log              | ON                                |
| slow_query_log_file         | /hislog/log/mysql/mysqld_slow.log |
+-----------------------------+-----------------------------------+
7 rows in set (0.00 sec)
# 慢查询时间阈值
mysql> show variables like "%long_query%";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

1.2. 配置文件

1
2
3
4
[mysqld]
slow_query_log=ON
slow_query_log_file=/hislog/log/mysql/mysqld_slow.log
long_query_time=5

2. 慢查询日志解读

  • 一条慢查询语句的格式
1
2
3
4
5
6
7
8
9
10
11
12
13
# Time: 2023-09-22T02:45:24.805459Z
# User@Host: controller[controller] @ localhost [127.0.0.1]  Id: 563817
# Query_time: 5.969860  Lock_time: 0.000514 Rows_sent: 99967  Rows_examined: 199934
SET timestamp=1695350718;
SELECT resource.id AS id,
                UNIX_TIMESTAMP(resource.created_at) AS created_at,
                UNIX_TIMESTAMP(resource.updated_at) AS updated_at,
                IFNULL(baseline_permission_resource.status, 0) AS baseline_status
                FROM resource
                LEFT JOIN
                baseline_permission_resource ON resource.id = baseline_permission_resource.resource_id
                WHERE resource.status = 1
                ORDER BY CONVERT(resource.name USING gbk) ASC;
  • Query_time: 查询时间
  • Lock_time: 锁定时间
  • Rows_sent: 发送的行数
  • Rows_examined: 检查的行数

3. 分析sql语句

  • 在sql语句前面加上explain，查看执行计划

五、性能和配置调优

1. explain 分析sql语句

1
2
3
4
5
6
7
8
mysql> explain select * from resource order by id asc;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+-------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | resource | NULL       | index | NULL          | PRIMARY | 144     | NULL | 76807 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)

1.1. 各字段分析

  • id: 查询序列号，体现优先级，id越大优先级越高，越先被执行
  • select_type: 查询类型
  • table: 表名
  • partitions: 分配到的分区
  • type: 访问类型
  • possible_keys: 可能使用到的索引
  • key: 实际使用到的索引
  • key_len: 索引字段长度
  • ref: 列和索引的比较
  • rows: 扫描的行数
  • filtered: 过滤的比例
  • Extra: 执行情况描述和说明

1) type 访问类型

  • ALL: 全表扫描
  • index: 只遍历索引树
  • range: 只检索给定范围的行，使用一个索引来遍历行
  • ref: 遍历索引树时，对一个或多个列进行引用
  • eq_ref: 对于每个索引键，表中只有一条记录与之匹配
  • const: 表示常量，一般做为查询条件
  • system: 表示系统表

2) Extra 执行情况描述和说明

  • Using index: 查询的数据被索引覆盖，并且where筛选的是前导列，使用索引查找就可以直接找到符合条件的数据，无需回表
  • Using where: 说明mysql将在存储引擎检索行后再进行过滤，没有用到索引，回表查询
  • Using temporary: 对查询结果排序时使用了一个临时表
  • Using filesort: 对结果使用一个外部索引排序，而不是按照索引次序从表中读取行，一般是order by用了其他数据
  • Using index condition: 查询的列不全在索引中，where条件是一个前导列范围
  • Using where;Using index: 查询的列被索引覆盖，where筛选条件也是索引列之一，但不是索引的前导列或出现其他影响了使用索引的情况（如存在范围筛选条件）。此情况下意味着无法直接通过索引查找来查询到符合条件的数据，影响并不大

2. 死锁

六、攻防

1. 无密码登陆数据库

  • 先使用管理员打开cmd，停止mysql服务
  • 执行命令
1
mysqld --skip-grant-tables --skip-networking
  • 窗口不关，打开新的窗口输入命令
1
mysql -uroot -p
  • 直接回车即可进入mysql

2. 查看其他用户密码

  • 先使用root登陆mysql
1
2
3
4
5
6
7
8
9
10
11
-- 进入到mysql表
mysql> use mysql
-- 查看用户表
mysql> select User,authentication_string from user where user='mysql.sys';
+------+------------------------------------------------------------------------+
| User | authentication_string                                                  |
+------+------------------------------------------------------------------------+
| root | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
+------+------------------------------------------------------------------------+
1 row in set (0.00 sec)

小技巧和踩坑记

1. 表情符号导致无法插入数据库

  • utf8无法支持表情符号，需要将数据库转到utf8mb4
1
ALTER TABLE mirror_user CHANGE name name varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;