MySQL训练营-准备阶段

系列先容：学习丁奇老师的MySQL训练营的学习条记
  安装Docker

为了实操老师课中的内容，且方便安装以及切换版本，在本身的linux小主机上利用docker搭建环境举行测试。
Docker安装教程，参考：
安装后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，须要把当前用户加入分组，参考：
docker安装乐成：

编译TPCH

下载参考：
编译参考：
天生测试数据./dbgen -s 1(天生1G的数据)：

设置docker镜像源

参考：https://www.coderjia.cn/archives/dba3f94c-a021-468a-8ac6-e840f85867ea
利用docker-compose启动mysql

如今须要启动mysql的目次创建以下目次，并赋予权限：

1. mkdir conf data logs mysql-files test
2. chmod 777 data logs mysql-files test

复制代码

创建my.cnf文件：

1. cd conf
3. vim my.cnf

复制代码

文件内容：

1. [mysqld]
2. user=mysql
3. # 表示允许任何主机登陆MySQL
4. bind-address = 0.0.0.0
5. port=3306
6. default-storage-engine=INNODB
7. #character-set-server=utf8
8. character-set-client-handshake=FALSE
9. character-set-server=utf8mb4
10. collation-server=utf8mb4_unicode_ci
11. init_connect='SET NAMES utf8mb4'
12. secure-file-priv=
13. log_error=/var/log/mysql/error.log
14. general_log_file=/var/log/mysql/query.log
15. slow_query_log_file=/var/log/mysql/slow_query.log
16. log_bin=/var/log/mysql/mysql-bin
17. local_infile=1
18. [client]
19. default-character-set=utf8mb4
20. [mysql]
21. default-character-set=utf8mb4
22. local_infile=1

复制代码

新建docker-compose.yaml文件：

1. version: "3"
2. services:
3.   mysql:
4.     image: mysql:8.0.18
5.     container_name: mysql8
6.     restart: always
7.     environment:
8.       MYSQL_ROOT_PASSWORD: 123456
9.     ports:
10.       - "3306:3306"
11.     command:
12.       --default-authentication-plugin=mysql_native_password
13.       --lower_case_table_names=1
14.       --character-set-server=utf8mb4
15.       --collation-server=utf8mb4_general_ci
16.     volumes:
17.       - /etc/localtime:/etc/localtime
18.       - ./data:/var/lib/mysql
19.       - ./conf/my.cnf:/etc/mysql/my.cnf
20.       - ./logs:/var/log/mysql
21.       - ./mysql-files:/var/lib/mysql-files
22.       - ./test:/opt/test

复制代码

利用docker-compose启动：

1. docker compose up -d

复制代码

天生数据并导入mysql

天生总量10G的数据，利用编译好的TPCH工具天生10G数据：
将天生的*.tbl文件移动到，docker-compose.yaml同级中的test目次下：

1. yuyang@yuyang-MS-7D97:~/Documents/study/mysql/tpch/TPC-H V3.0.1/dbgen/data$ ll
2. total 10969068
3. drwxrwxr-x 2 yuyang yuyang       4096 12月  3 21:46 ./
4. drwxrwxr-x 9 yuyang yuyang       4096 11月 19 22:22 ../
5. -rw-rw-r-- 1 yuyang yuyang  244847642 12月  3 21:46 customer.tbl
6. -rwxrwxr-x 1 yuyang yuyang     127376 11月 19 22:23 dbgen*
7. -rw-rw-r-- 1 yuyang yuyang      11815 11月 19 22:23 dists.dss
8. -rw-rw-r-- 1 yuyang yuyang 7775727688 12月  3 21:46 lineitem.tbl
9. -rw-rw-r-- 1 yuyang yuyang       2224 12月  3 21:46 nation.tbl
10. -rw-rw-r-- 1 yuyang yuyang 1749195031 12月  3 21:46 orders.tbl
11. -rw-rw-r-- 1 yuyang yuyang 1204850769 12月  3 21:46 partsupp.tbl
12. -rw-rw-r-- 1 yuyang yuyang  243336157 12月  3 21:46 part.tbl
13. -rw-rw-r-- 1 yuyang yuyang        389 12月  3 21:46 region.tbl
14. -rw-rw-r-- 1 yuyang yuyang   14176368 12月  3 21:46 supplier.tbl
15. 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中的建表语句，创建数据库表：

1. CREATE TABLE NATION  ( N_NATIONKEY  INTEGER NOT NULL,
2.                             N_NAME       CHAR(25) NOT NULL,
3.                             N_REGIONKEY  INTEGER NOT NULL,
4.                             N_COMMENT    VARCHAR(152));
6. CREATE TABLE REGION  ( R_REGIONKEY  INTEGER NOT NULL,
7.                             R_NAME       CHAR(25) NOT NULL,
8.                             R_COMMENT    VARCHAR(152));
10. CREATE TABLE PART  ( P_PARTKEY     INTEGER NOT NULL,
11.                           P_NAME        VARCHAR(55) NOT NULL,
12.                           P_MFGR        CHAR(25) NOT NULL,
13.                           P_BRAND       CHAR(10) NOT NULL,
14.                           P_TYPE        VARCHAR(25) NOT NULL,
15.                           P_SIZE        INTEGER NOT NULL,
16.                           P_CONTAINER   CHAR(10) NOT NULL,
17.                           P_RETAILPRICE DECIMAL(15,2) NOT NULL,
18.                           P_COMMENT     VARCHAR(23) NOT NULL );
20. CREATE TABLE SUPPLIER ( S_SUPPKEY     INTEGER NOT NULL,
21.                              S_NAME        CHAR(25) NOT NULL,
22.                              S_ADDRESS     VARCHAR(40) NOT NULL,
23.                              S_NATIONKEY   INTEGER NOT NULL,
24.                              S_PHONE       CHAR(15) NOT NULL,
25.                              S_ACCTBAL     DECIMAL(15,2) NOT NULL,
26.                              S_COMMENT     VARCHAR(101) NOT NULL);
28. CREATE TABLE PARTSUPP ( PS_PARTKEY     INTEGER NOT NULL,
29.                              PS_SUPPKEY     INTEGER NOT NULL,
30.                              PS_AVAILQTY    INTEGER NOT NULL,
31.                              PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
32.                              PS_COMMENT     VARCHAR(199) NOT NULL );
34. CREATE TABLE CUSTOMER ( C_CUSTKEY     INTEGER NOT NULL,
35.                              C_NAME        VARCHAR(25) NOT NULL,
36.                              C_ADDRESS     VARCHAR(40) NOT NULL,
37.                              C_NATIONKEY   INTEGER NOT NULL,
38.                              C_PHONE       CHAR(15) NOT NULL,
39.                              C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
40.                              C_MKTSEGMENT  CHAR(10) NOT NULL,
41.                              C_COMMENT     VARCHAR(117) NOT NULL);
43. CREATE TABLE ORDERS  ( O_ORDERKEY       INTEGER NOT NULL,
44.                            O_CUSTKEY        INTEGER NOT NULL,
45.                            O_ORDERSTATUS    CHAR(1) NOT NULL,
46.                            O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
47.                            O_ORDERDATE      DATE NOT NULL,
48.                            O_ORDERPRIORITY  CHAR(15) NOT NULL,  
49.                            O_CLERK          CHAR(15) NOT NULL,
50.                            O_SHIPPRIORITY   INTEGER NOT NULL,
51.                            O_COMMENT        VARCHAR(79) NOT NULL);
53. CREATE TABLE LINEITEM ( L_ORDERKEY    INTEGER NOT NULL,
54.                              L_PARTKEY     INTEGER NOT NULL,
55.                              L_SUPPKEY     INTEGER NOT NULL,
56.                              L_LINENUMBER  INTEGER NOT NULL,
57.                              L_QUANTITY    DECIMAL(15,2) NOT NULL,
58.                              L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
59.                              L_DISCOUNT    DECIMAL(15,2) NOT NULL,
60.                              L_TAX         DECIMAL(15,2) NOT NULL,
61.                              L_RETURNFLAG  CHAR(1) NOT NULL,
62.                              L_LINESTATUS  CHAR(1) NOT NULL,
63.                              L_SHIPDATE    DATE NOT NULL,
64.                              L_COMMITDATE  DATE NOT NULL,
65.                              L_RECEIPTDATE DATE NOT NULL,
66.                              L_SHIPINSTRUCT CHAR(25) NOT NULL,
67.                              L_SHIPMODE     CHAR(10) NOT NULL,
68.                              L_COMMENT      VARCHAR(44) NOT NULL);

复制代码

导入数据：

1. load data local infile '/opt/test/part.tbl' into table part fields terminated by '|';
2. load data local infile '/opt/test/region.tbl' into table region fields terminated by '|';
3. load data local infile '/opt/test/nation.tbl' into table nation fields terminated by '|';
4. load data local infile '/opt/test/customer.tbl' into table customer fields terminated by '|';
5. load data local infile '/opt/test/supplier.tbl' into table supplier fields terminated by '|';
6. load data local infile '/opt/test/lineitem.tbl' into table lineitem fields terminated by '|';
7. load data local infile '/opt/test/partsupp.tbl' into table partsupp fields terminated by '|';
8. 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的内存占用举行限定然后测试：

1.     deploy:
2.       resources:
3.         limits:
4.           memory: 1024M

复制代码

我们刚刚导入了的TPCC表中lineitem表最大，可以用其做测试：mysql -uroot -p123456 -Dmysql -e "select * from lineitem"，但是没有出现mysqld历程挂掉环境，只出现mysql客户端客户端被kill环境，与上述理论分析不划一：

1. root@a16f2d8fa251:/var/log# mysql -uroot -p123456 -Dmysql -e "select * from lineitem"
2. mysql: [Warning] Using a password on the command line interface can be insecure.
3. Killed

复制代码

但是加上了--quick参数，确实能到达查一部分表现一部分的结果。
mysql kill connection

例1

session1session2select sleep(100);kill connection id按老师所诉，默认举动--reconnect会让session1自动重连，并实行完sleep语句。实际测试发现：

1. mysql> select sleep(100);
2. ERROR 2013 (HY000): Lost connection to MySQL server during query

复制代码

在另一个终端kill掉毗连时，实行的语句也克制了。
例2

shellsession2mysql -uroot -p123456 -Dmysql -e “select sleep(100)”kill connection id这个例子和上一差别的在于利用-e直接实行，测试结果和上面的一样：

1. root@ada1ad5677eb:/# mysql -uroot -p123456 -Dmysql -e "select sleep(100)"
2. mysql: [Warning] Using a password on the command line interface can be insecure.
3. ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query

复制代码

例3

shellshellmysql -uroot -p123456 -Dmysql -e “select sleep(100)”ps -ef | grep mysql and kill pid  mysql client 没有征象，直接退出，这个形貌准确吗?
  A. 准确 B. 错误
  是准确的，客户端历程被直接kill掉
能在mysql的error.log中看到：

1. 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
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会每秒实行一次革新磁盘操纵。
  

答案是不肯定，由于数据已经刷到操纵体系的缓存了，后续操纵体系会刷入磁盘。只有在呆板突然断电环境下大概会丢数据。