MySql教程

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

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

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

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

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

          

  1. mysql 宕机
    1. 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
  2. 关闭数据库连接脚本释放一下
  3. 重启数据库报错 
    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
  4. 解决思路
    1. 开始以为是alert 之后崩溃后导致数据错误 进行查看磁盘 存储的数据没发现什么问题
    2. 查看报错是配置错误使用 --no-defaults 参数启动成功
    3. 查看alert表无异常确认是mysql配置文件有问题 (上面错误日志已经报出来了 )
  5. 直接修改mysql配置文件之后重启成功
  6. 修改查看 设置mysql tmp 目录

    1.  

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

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tmpdir        | /data/mysql/tmp |
+---------------+-----------------+
1 row in set (0.00 sec)
    2. 修改配置文件 tmpdir = /data/mysql/tmp   (主意创建目录并且赋予权限)
    3. 重启mysql  tmpdir 修改成功 alert 临时表开始走 data的硬盘

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

 

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