一次Mysql服务不断重启排查，原因竟然是它_(mysqld 5，2024年最新超通俗解析

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新软件测试全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上软件测试知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip1024b （备注软件测试）

正文

[root@localhost data]# sync
[root@localhost data]# echo 1 > /proc/sys/vm/drop_caches ;
[root@localhost data]# free -m
total used free shared buff/cache available
Mem: 2124 254 1784 9 84 1748
Swap: 0 0 0

可以看到目前剩余的内存为1784M，mysql数据库占用内存主要有2大块
第一：buffer pool占用，
第二：初始化连接占用的内存

在这里设置mysql的buffer pool为1500M，会话的参数设置如下

read_buffer_size = 32M
read_rnd_buffer_size = 32M

sort_buffer_size = 32M
tmp_table_size = 32M
max_heap_table_size=32M
join_buffer_size=32M

然后5个连接，开始做大查询操作，没过多久，mysql进程就因为OOM被kill了

Aug 31 05:37:40 localhost kernel: Out of memory: Kill process 2534 (mysqld) score 658 or sacrifice child
Aug 31 05:37:40 localhost kernel: Killed process 2534 (mysqld), UID 1001, total-vm:1825792kB, anon-rss:654388kB, file-rss:0kB, shmem-rss:0kB

mysql守护进程就开始启动mysql服务

/u02/mysql/bin/mysqld_safe: 行 198: 2534 已杀死 nohup /u02/mysql/bin/mysqld --defaults-file=/u02/conf/my3308.cnf --basedir=/u02/mysql --datadir=/u02/data/3308 --plugin-dir=/u02/mysql/lib/plugin --user=mysql --log-error=/u02/log/3308/error.log --open-files-limit=65535 --pid-file=/u02/run/3308/mysqld.pid --socket=/u02/run/3308/mysql.sock --port=3308 < /dev/null > /dev/null 2>&1
2020-08-30T21:37:40.375749Z mysqld_safe Number of processes running now: 0
2020-08-30T21:37:40.407781Z mysqld_safe mysqld restarted
2020-08-30T21:37:40.666886Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-08-30T21:37:40.667059Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2020-08-30T21:37:40.667112Z 0 [Note] /u02/mysql/bin/mysqld (mysqld 5.7.26-log) starting as process 2954 …
2020-08-30T21:37:40.782412Z 0 [Warning] InnoDB: Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
2020-08-30T21:37:40.782684Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-08-30T21:37:40.782729Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-08-30T21:37:40.782754Z 0 [Note] InnoDB: Uses event mutexes
2020-08-30T21:37:40.782772Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-08-30T21:37:40.782788Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-08-30T21:37:40.782841Z 0 [Note] InnoDB: Adjusting innodb_buffer_pool_instances from 8 to 1 since innodb_buffer_pool_size is less than 1024 MiB
2020-08-30T21:37:40.784518Z 0 [Note] InnoDB: Number of pools: 1
2020-08-30T21:37:40.784865Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-08-30T21:37:40.789314Z 0 [Note] InnoDB: Initializing buffer pool, total size = 512M, instances = 1, chunk size = 128M
2020-08-30T21:37:40.834948Z 0 [Note] InnoDB: Completed initialization of buffer pool
2020-08-30T21:37:40.843612Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-08-30T21:37:40.859028Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2020-08-30T21:37:40.863176Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 5707394229
2020-08-30T21:37:40.863221Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 5707394238
2020-08-30T21:37:40.863231Z 0 [Note] InnoDB: Database was not shutdown normally!
2020-08-30T21:37:40.863239Z 0 [Note] InnoDB: Starting crash recovery.
2020-08-30T21:37:40.901955Z 0 [Note] InnoDB: Last MySQL binlog file position 0 43848, file name binlog.000025
2020-08-30T21:37:41.075805Z 0 [Note] InnoDB: Removed temporary tablespace data file: “ibtmp1”
2020-08-30T21:37:41.075860Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-08-30T21:37:41.075952Z 0 [Note] InnoDB: Setting file ‘/u02/log/3308/iblog/ibtmp1’ size to 12 MB. Physically writing the file full; Please wait …
2020-08-30T21:37:41.254016Z 0 [Note] InnoDB: File ‘/u02/log/3308/iblog/ibtmp1’ size is now 12 MB.
2020-08-30T21:37:41.255390Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2020-08-30T21:37:41.255421Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2020-08-30T21:37:41.256171Z 0 [Note] InnoDB: Waiting for purge to start
2020-08-30T21:37:41.307237Z 0 [Note] InnoDB: 5.7.26 started; log sequence number 5707394238
2020-08-30T21:37:41.308291Z 0 [Note] Plugin ‘FEDERATED’ is disabled.
2020-08-30T21:37:41.310625Z 0 [Note] InnoDB: Loading buffer pool(s) from /u02/log/3308/iblog/ib_buffer_pool
2020-08-30T21:37:41.310785Z 0 [Note] InnoDB: Buffer pool(s) load completed at 200831 5:37:41 (/u02/log/3308/iblog/ib_buffer_pool was empty)
2020-08-30T21:37:41.314568Z 0 [Note] Recovering after a crash using /u02/log/3308/binlog/binlog
2020-08-30T21:37:41.314730Z 0 [Note] Starting crash recovery…
2020-08-30T21:37:41.314842Z 0 [Note] Crash recovery finished.
2020-08-30T21:37:41.346280Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2020-08-30T21:37:41.346337Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2020-08-30T21:37:41.349079Z 0 [Warning] CA certificate ca.pem is self signed.
2020-08-30T21:37:41.349341Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2020-08-30T21:37:41.350297Z 0 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3308
2020-08-30T21:37:41.350399Z 0 [Note] - ‘0.0.0.0’ resolves to ‘0.0.0.0’;
2020-08-30T21:37:41.350475Z 0 [Note] Server socket created on IP: ‘0.0.0.0’.
2020-08-30T21:37:41.376794Z 0 [Note] Failed to start slave threads for channel ‘’
2020-08-30T21:37:41.397237Z 0 [Note] Event Scheduler: Loaded 0 events
2020-08-30T21:37:41.397480Z 0 [Note] /u02/mysql/bin/mysqld: ready for connections.
Version: ‘5.7.26-log’ socket: ‘/u02/run/3308/mysql.sock’ port: 3308 Sour

正在连接的会话自动中断

ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

在配置mysql参数，一定要考虑以下3个因素
1.业务连接预期总数
2.会话初始化内存
3.buffer pool缓冲器大小

下面先用sql查询一下会话的内存总大小和数据库buffer pool大小

mysql> select (@@innodb_buffer_pool_size+@@innodb_log_buffer_size+@@key_buffer_size) / 1024 /1024 AS MEMORY_MB;
±-------------+
| MEMORY_MB |
±-------------+
| 584.00000000 |

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注软件测试）

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！
件测试）**
[外链图片转存中…(img-aQdufcTY-1713184493349)]

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！