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文件中mysqldmysql下是否存在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会每秒执行一次刷新磁盘操作。

答案是不会,因为数据已经刷到操作系统的缓存了,后续操作系统会刷入磁盘。只有在机器突然断电情况下可能会丢数据。

posted @   余为民同志  阅读(143)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示