MySQL训练营-准备阶段
系列介绍:学习丁奇老师的MySQL训练营的学习笔记
安装Docker
为了实操老师课中的内容,且方便安装以及切换版本,在自己的linux小主机上使用docker搭建环境进行测试。
Docker安装教程,参考:https://www.cnblogs.com/lqqgis/p/18276118
安装后docker ps
命令报错:permission denied while trying to connect to the Docker daemon socket at unix:///var/run/docker.sock: Get "http://%2Fvar%2Frun%2Fdocker.sock/v1.47/containers/json": dial unix /var/run/docker.sock: connect: permission denied
,需要把当前用户加入分组,参考:https://blog.csdn.net/feinifi/article/details/105511075
docker安装成功:
编译TPCH
下载参考:https://blog.csdn.net/nyyngh/article/details/123681297
编译参考:https://blog.csdn.net/Fei20140908/article/details/120733081,需要注意的是DATABASE=MYSQL
生成测试数据./dbgen -s 1
(生成1G的数据):
配置docker镜像源
参考:https://www.coderjia.cn/archives/dba3f94c-a021-468a-8ac6-e840f85867ea
使用docker-compose启动mysql
现在需要启动mysql的目录创建以下目录,并赋予权限:
mkdir conf data logs mysql-files test
chmod 777 data logs mysql-files test
创建my.cnf
文件:
cd conf
vim my.cnf
文件内容:
[mysqld]
user=mysql
# 表示允许任何主机登陆MySQL
bind-address = 0.0.0.0
port=3306
default-storage-engine=INNODB
#character-set-server=utf8
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
secure-file-priv=
log_error=/var/log/mysql/error.log
general_log_file=/var/log/mysql/query.log
slow_query_log_file=/var/log/mysql/slow_query.log
log_bin=/var/log/mysql/mysql-bin
local_infile=1
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
local_infile=1
新建docker-compose.yaml
文件:
version: "3"
services:
mysql:
image: mysql:8.0.18
container_name: mysql8
restart: always
environment:
MYSQL_ROOT_PASSWORD: 123456
ports:
- "3306:3306"
command:
--default-authentication-plugin=mysql_native_password
--lower_case_table_names=1
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
volumes:
- /etc/localtime:/etc/localtime
- ./data:/var/lib/mysql
- ./conf/my.cnf:/etc/mysql/my.cnf
- ./logs:/var/log/mysql
- ./mysql-files:/var/lib/mysql-files
- ./test:/opt/test
使用docker-compose启动:
docker compose up -d
生成数据并导入mysql
生成总量10G的数据,使用编译好的TPCH工具生成10G数据:
将生成的*.tbl
文件移动到,docker-compose.yaml
同级中的test
目录下:
yuyang@yuyang-MS-7D97:~/Documents/study/mysql/tpch/TPC-H V3.0.1/dbgen/data$ ll
total 10969068
drwxrwxr-x 2 yuyang yuyang 4096 12月 3 21:46 ./
drwxrwxr-x 9 yuyang yuyang 4096 11月 19 22:22 ../
-rw-rw-r-- 1 yuyang yuyang 244847642 12月 3 21:46 customer.tbl
-rwxrwxr-x 1 yuyang yuyang 127376 11月 19 22:23 dbgen*
-rw-rw-r-- 1 yuyang yuyang 11815 11月 19 22:23 dists.dss
-rw-rw-r-- 1 yuyang yuyang 7775727688 12月 3 21:46 lineitem.tbl
-rw-rw-r-- 1 yuyang yuyang 2224 12月 3 21:46 nation.tbl
-rw-rw-r-- 1 yuyang yuyang 1749195031 12月 3 21:46 orders.tbl
-rw-rw-r-- 1 yuyang yuyang 1204850769 12月 3 21:46 partsupp.tbl
-rw-rw-r-- 1 yuyang yuyang 243336157 12月 3 21:46 part.tbl
-rw-rw-r-- 1 yuyang yuyang 389 12月 3 21:46 region.tbl
-rw-rw-r-- 1 yuyang yuyang 14176368 12月 3 21:46 supplier.tbl
yuyang@yuyang-MS-7D97:~/Documents/study/mysql/tpch/TPC-H V3.0.1/dbgen/data$ mv *.tbl /home/yuyang/Documents/study/mysql/docker-compose/test
使用docker exec -it ${hash} bash
进入docker容器,mysql -uroot -p
连接数据库。
使用dbgen
下的dss.ddl
中的建表语句,创建数据库表:
CREATE TABLE NATION ( N_NATIONKEY INTEGER NOT NULL,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY INTEGER NOT NULL,
N_COMMENT VARCHAR(152));
CREATE TABLE REGION ( R_REGIONKEY INTEGER NOT NULL,
R_NAME CHAR(25) NOT NULL,
R_COMMENT VARCHAR(152));
CREATE TABLE PART ( P_PARTKEY INTEGER NOT NULL,
P_NAME VARCHAR(55) NOT NULL,
P_MFGR CHAR(25) NOT NULL,
P_BRAND CHAR(10) NOT NULL,
P_TYPE VARCHAR(25) NOT NULL,
P_SIZE INTEGER NOT NULL,
P_CONTAINER CHAR(10) NOT NULL,
P_RETAILPRICE DECIMAL(15,2) NOT NULL,
P_COMMENT VARCHAR(23) NOT NULL );
CREATE TABLE SUPPLIER ( S_SUPPKEY INTEGER NOT NULL,
S_NAME CHAR(25) NOT NULL,
S_ADDRESS VARCHAR(40) NOT NULL,
S_NATIONKEY INTEGER NOT NULL,
S_PHONE CHAR(15) NOT NULL,
S_ACCTBAL DECIMAL(15,2) NOT NULL,
S_COMMENT VARCHAR(101) NOT NULL);
CREATE TABLE PARTSUPP ( PS_PARTKEY INTEGER NOT NULL,
PS_SUPPKEY INTEGER NOT NULL,
PS_AVAILQTY INTEGER NOT NULL,
PS_SUPPLYCOST DECIMAL(15,2) NOT NULL,
PS_COMMENT VARCHAR(199) NOT NULL );
CREATE TABLE CUSTOMER ( C_CUSTKEY INTEGER NOT NULL,
C_NAME VARCHAR(25) NOT NULL,
C_ADDRESS VARCHAR(40) NOT NULL,
C_NATIONKEY INTEGER NOT NULL,
C_PHONE CHAR(15) NOT NULL,
C_ACCTBAL DECIMAL(15,2) NOT NULL,
C_MKTSEGMENT CHAR(10) NOT NULL,
C_COMMENT VARCHAR(117) NOT NULL);
CREATE TABLE ORDERS ( O_ORDERKEY INTEGER NOT NULL,
O_CUSTKEY INTEGER NOT NULL,
O_ORDERSTATUS CHAR(1) NOT NULL,
O_TOTALPRICE DECIMAL(15,2) NOT NULL,
O_ORDERDATE DATE NOT NULL,
O_ORDERPRIORITY CHAR(15) NOT NULL,
O_CLERK CHAR(15) NOT NULL,
O_SHIPPRIORITY INTEGER NOT NULL,
O_COMMENT VARCHAR(79) NOT NULL);
CREATE TABLE LINEITEM ( L_ORDERKEY INTEGER NOT NULL,
L_PARTKEY INTEGER NOT NULL,
L_SUPPKEY INTEGER NOT NULL,
L_LINENUMBER INTEGER NOT NULL,
L_QUANTITY DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
L_DISCOUNT DECIMAL(15,2) NOT NULL,
L_TAX DECIMAL(15,2) NOT NULL,
L_RETURNFLAG CHAR(1) NOT NULL,
L_LINESTATUS CHAR(1) NOT NULL,
L_SHIPDATE DATE NOT NULL,
L_COMMITDATE DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT CHAR(25) NOT NULL,
L_SHIPMODE CHAR(10) NOT NULL,
L_COMMENT VARCHAR(44) NOT NULL);
导入数据:
load data local infile '/opt/test/part.tbl' into table part fields terminated by '|';
load data local infile '/opt/test/region.tbl' into table region fields terminated by '|';
load data local infile '/opt/test/nation.tbl' into table nation fields terminated by '|';
load data local infile '/opt/test/customer.tbl' into table customer fields terminated by '|';
load data local infile '/opt/test/supplier.tbl' into table supplier fields terminated by '|';
load data local infile '/opt/test/lineitem.tbl' into table lineitem fields terminated by '|';
load data local infile '/opt/test/partsupp.tbl' into table partsupp fields terminated by '|';
load data local infile '/opt/test/orders.tbl' into table orders fields terminated by '|';
若导入时报错:
mysql> load data local infile '/opt/test/part.tbl' into table part fields terminated by '|'; ERROR 1148 (42000): The used command is not allowed with this MySQL version
检查my.cnf
文件中mysqld
和mysql
下是否存在local_infile=1
的设置。
大查询会不会打爆 MySQL 服务器内存?
假设一个表有 100G 的表 t, 执行 select * from t,语句执行期间需要占用服务端 10G 的内存吗?
A. 需要 B. 不需要
不会,因为存储引擎读取到数据后会丢在net buffer
中,而后存储引擎会通过替换策略将该部分内存释放用于读取新数据。
但是执行:mysql -h127.0.0.1 -uroot -e "select * from t"
为什么会使mysqld
服务OOM
因为client会将查询结果缓存,待数据全部查上来后,用于显示。期间会挤占mysql服务端的内存,使mysqld
发生OOM
。(这一部分存疑,因为没有复现)
我们可以在刚刚的docker-compose.yaml
中添加,对mysql的内存占用进行限制然后测试:
deploy:
resources:
limits:
memory: 1024M
我们刚刚导入了的TPCC表中lineitem表最大,可以用其做测试:mysql -uroot -p123456 -Dmysql -e "select * from lineitem"
,但是没有出现mysqld
进程挂掉情况,只出现mysql
客户端客户端被kill
情况,与上述理论分析不一致:
root@a16f2d8fa251:/var/log# mysql -uroot -p123456 -Dmysql -e "select * from lineitem"
mysql: [Warning] Using a password on the command line interface can be insecure.
Killed
但是加上了--quick
参数,确实能达到查一部分显示一部分的效果。
mysql kill connection
例1
session1 | session2 |
---|---|
select sleep(100); | |
kill connection id |
按老师所述,默认行为--reconnect
会让session1
自动重连,并执行完sleep
语句。实际测试发现:
mysql> select sleep(100);
ERROR 2013 (HY000): Lost connection to MySQL server during query
在另一个终端kill掉连接时,执行的语句也停止了。
通过查询资料:
如果您使用
KILL
命令来终止一个MySQL
会话,那么所有由该会话启动但尚未完成的操作都会被立即停止。即使启用了--reconnect
,这也无法阻止KILL
操作的效果;被杀死的会话中的任何未完成操作都将被终止。这个地方应该是老师讲错了。就算客户端能重连上来,在服务端执行kill的时候相关的资源应该被释放掉了,不可能能继续执行语句。
例2
shell | session2 |
---|---|
mysql -uroot -p123456 -Dmysql -e "select sleep(100)" | |
kill connection id |
这个例子和上一不同的在于使用-e
直接执行,测试结果和上面的一样:
root@ada1ad5677eb:/# mysql -uroot -p123456 -Dmysql -e "select sleep(100)"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
例3
shell | shell |
---|---|
mysql -uroot -p123456 -Dmysql -e "select sleep(100)" | |
ps -ef | grep mysql and kill pid |
mysql client 没有现象,直接退出,这个描述正确吗?
A. 正确 B. 错误
是正确的,客户端进程被直接kill
掉
能在mysql的error.log
中看到:
2024-12-04T13:14:57.149639Z 8 [Note] [MY-010914] [Server] Aborted connection 8 to db: 'mysql' user: 'root' host: 'localhost' (Got an error writing communication packets).
要看到这个日志需要修改error.log
的级别:log_error_verbosity=3
,可以看到日志级别为Note
,可知对MySQL
来说这个日志不是特别重要。
kill -9 mysqld 会不会丢数据
参数设置:sync_binlog=100/innodb_flush_log_at_trx_commit=2
sync_binlog可选参数:0/1/N,代表多少个事物写入操作系统缓存后,强制同步到磁盘。0代表不同步,1代表每个,N代表N个
innodb_flush_log_at_trx_commit可选参数:0/1/2
- 0:日志缓存区将每隔一秒写到日志文件中,并且将日志文件的数据刷新到磁盘上。该模式下在事务提交时不会主动触发写入磁盘的操作。
- 1:每次事务提交时MySQL都会把日志缓存区的数据写入日志文件中,并且刷新到磁盘中,该模式为系统默认。
- 2:每次事务提交时MySQL都会把日志缓存区的数据写入日志文件中,但是并不会同时刷新到磁盘上。该模式下,MySQL会每秒执行一次刷新磁盘操作。
答案是不会,因为数据已经刷到操作系统的缓存了,后续操作系统会刷入磁盘。只有在机器突然断电情况下可能会丢数据。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战