mysql,设置tmp目录与免除配置文件登录

2021/12/7 19:19:27

编程Tag： 配置文件 mysql 06 37 12 2021 tmp 21 28168

本文主要是介绍mysql,设置tmp目录与免除配置文件登录，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

问题原因：公司需要把业务log库数据同步到 logcenter 使用了一个php脚本同步数据与结构，因为日积月累一个表达到了52G 这时候alert

就出现了问题因为tmp临时表的路劲配置到了根盘但是根盘只有30G可以使用这时候mysql后台就已开始频繁的报错磁盘空间不够用了

因为是脚本同步所以一直在执行最后mysql 宕机了，具体情况按照步骤说明

mysql 宕机

mysql 报错磁盘空间

2021-12-06 20:33:04 7fa04cace7002021-12-06 20:33:04 8890 [ERROR] InnoDB: Failure of system call pwrite(). Operating system error number is 28.
InnoDB: Error number 28 means 'No space left on device'.
InnoDB: Some operating system error numbers are described at

关闭数据库连接脚本释放一下

重启数据库报错

2021-12-06 21:37:01 28168 [ERROR] /opt/mysql/bin/mysqld: unknown variable 'default-character-set=utf8'
2021-12-06 21:37:01 28168 [ERROR] Aborting

2021-12-06 21:37:01 28168 [Note] Binlog end
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'partition'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_METRICS'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_CMPMEM'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_CMP'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_LOCKS'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'INNODB_TRX'
2021-12-06 21:37:01 28168 [Note] Shutting down plugin 'InnoDB'
2021-12-06 21:37:01 28168 [Note] InnoDB: FTS optimize thread exiting.
2021-12-06 21:37:01 28168 [Note] InnoDB: Starting shutdown...
2021-12-06 21:37:03 28168 [Note] InnoDB: Shutdown completed; log sequence number 88150785189005
2021-12-06 21:37:03 28168 [Note] Shutting down plugin 'BLACKHOLE'
2021-12-06 21:37:03 28168 [Note] Shutting down plugin 'ARCHIVE'
2021-12-06 21:37:03 28168 [Note] Shutting down plugin 'MRG_MYISAM'
2021-12-06 21:37:03 28168 [Note] Shutting down plugin 'MyISAM'
2021-12-06 21:37:03 28168 [Note] Shutting down plugin 'MEMORY'
2021-12-06 21:37:03 28168 [Note] Shutting down plugin 'CSV'
2021-12-06 21:37:03 28168 [Note] Shutting down plugin 'sha256_password'
2021-12-06 21:37:03 28168 [Note] Shutting down plugin 'mysql_old_password'
2021-12-06 21:37:03 28168 [Note] Shutting down plugin 'mysql_native_password'
2021-12-06 21:37:03 28168 [Note] Shutting down plugin 'binlog'
2021-12-06 21:37:03 28168 [Note] /opt/mysql/bin/mysqld: Shutdown complete

解决思路
1. 开始以为是alert 之后崩溃后导致数据错误进行查看磁盘存储的数据没发现什么问题
2. 查看报错是配置错误使用 --no-defaults 参数启动成功
3. 查看alert表无异常确认是mysql配置文件有问题 (上面错误日志已经报出来了 )
直接修改mysql配置文件之后重启成功

修改查看设置mysql tmp 目录

#查看tmpdir路径
show variables like 'tmpdir';

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tmpdir        | /data/mysql/tmp |
+---------------+-----------------+
1 row in set (0.00 sec)

修改配置文件 tmpdir = /data/mysql/tmp (主意创建目录并且赋予权限)
重启mysql tmpdir 修改成功 alert 临时表开始走 data的硬盘

目前没有搜到热修改tmpdir的方法，知道的大佬可以告知一下

这篇关于mysql,设置tmp目录与免除配置文件登录的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

mysql,设置tmp目录与免除配置文件登录

相关编程文章