修改过目录怎么恢复怎么恢复正常(目录设置错了,如何还原)

　　一个跑了3年的管理系统，年后突然有人报错说登录的时候提示无用户，于是排查发现Web端连接数据库异常，于是查数据库的服务器发现磁盘空间满了，而占用最大的就是Mysql数据库的日志目录，于是清理了Mysql的日志目录恢复了正常，处理过程还原模拟如下：

[root@mysql1 ~]# df -h文件系统 容量 已用 可用 已用% 挂载点devtmpfs 2.0G 0 2.0G 0% /devtmpfs 2.0G 0 2.0G 0% /dev/shmtmpfs 2.0G 12M 2.0G 1% /runtmpfs 2.0G 0 2.0G 0% /sys/fs/cgroup/dev/mapper/centos-root 17G 17G 0G 100% //dev/sda1 1014M 141M 874M 14% /boottmpfs 394M 0 394M 0% /run/user/0二、删除多余的日志文件2.1、确认日志文件并删除2.1.1、登陆Mysql并 查看当前使用的日志文件。

mysql> show binary logs;+----------------------+-----------+| Log_name | File_size |+----------------------+-----------+| master-rs-bin.000001 | 1236 || master-rs-bin.000002 | 177 || master-rs-bin.000003 | 177 || master-rs-bin.000004 | 177 || master-rs-bin.000005 | 177 || master-rs-bin.000006 | 177 || master-rs-bin.000007 | 177 || master-rs-bin.000008 | 177 || master-rs-bin.000009 | 177 || master-rs-bin.000010 | 177 || master-rs-bin.000011 | 177 || master-rs-bin.000012 | 177 || master-rs-bin.000013 | 177 || master-rs-bin.000014 | 177 || master-rs-bin.000015 | 177 || master-rs-bin.000016 | 154 |+----------------------+-----------+16 rows in set (0.00 sec)2.1.2、查看Mysql当前正在使用的日志文件名称

mysql> show master status; #确认当前使用的日志名称为master-rs-bin.000016+----------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+----------------------+----------+--------------+------------------+-------------------+| master-rs-bin.000016 | 154 | rongshu | | |+----------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)2.1.3、删除日志文件

　　我们确认Mysql当前使用的日志文件名称为master-rs-bin.000016，所以在删除时应避免删除master-rs-bin.000016文件。

mysql> purge binary logs to 'master-rs-bin.000016'; #删除除master-rs-bin.000016文件以外的日志文件；Query OK, 0 rows affected (0.00 sec)mysql> show binary logs; #重新查看只剩下一个master-rs-bin.000016文件了；+----------------------+-----------+| Log_name | File_size |+----------------------+-----------+| master-rs-bin.000016 | 154 |+----------------------+-----------+1 row in set (0.00 sec)2.2、慢查询日志文件删除

　　在删除了日志文件之后查看了一下日志文件的目录，发现还有一个mysql1-slow.log文件居然有19G的大小，由于系统跑了很长时间了，果断删除文件，操作如下。

mysql> show variables like '%slow%';+---------------------------+--------------------------------+| Variable_name | Value |+---------------------------+--------------------------------+| log_slow_admin_statements | OFF || log_slow_slave_statements | OFF || slow_launch_time | 2 || slow_query_log | ON #为开启状态 || slow_query_log_file | /var/lib/mysql/mysql1-slow.log |+---------------------------+--------------------------------+5 rows in set (0.00 sec)2.2.2、关闭慢查询日志

mysql> set global slow_query_log=0; #关闭慢查询Query OK, 0 rows affected (0.00 sec)mysql> show variables like '%slow%'; #确认已关闭+---------------------------+--------------------------------+| Variable_name | Value |+---------------------------+--------------------------------+| log_slow_admin_statements | OFF || log_slow_slave_statements | OFF || slow_launch_time | 2 || slow_query_log | OFF || slow_query_log_file | /var/lib/mysql/mysql1-slow.log |+---------------------------+--------------------------------+5 rows in set (0.01 sec)2.2.3、指定新的慢查询日志文件路径

mysql> set global slow_query_log_file='/var/lib/mysql/mysql1-new-slow.log.log'; #修改文件Query OK, 0 rows affected (0.00 sec)mysql> show variables like '%slow%'; #确认修改成功+---------------------------+----------------------------------------+| Variable_name | Value |+---------------------------+----------------------------------------+| log_slow_admin_statements | OFF || log_slow_slave_statements | OFF || slow_launch_time | 2 || slow_query_log | OFF || slow_query_log_file | /var/lib/mysql/mysql1-new-slow.log.log |+---------------------------+----------------------------------------+5 rows in set (0.00 sec)2.2.4、开启慢查询日志并确认正常

mysql> set global slow_query_log=1; #设置开启Query OK, 0 rows affected (0.01 sec)mysql> show variables like '%slow%'; #确认已开启+---------------------------+----------------------------------------+| Variable_name | Value |+---------------------------+----------------------------------------+| log_slow_admin_statements | OFF || log_slow_slave_statements | OFF || slow_launch_time | 2 || slow_query_log | ON || slow_query_log_file | /var/lib/mysql/mysql1-new-slow.log.log |+---------------------------+----------------------------------------+5 rows in set (0.00 sec)#模拟一个慢查询语句确认日志读写正常mysql> select sleep(5) as a, 1 as b;2.2.5、删除之前的慢查询文件

　　注意：如果有其他空间足够的服务器，请在删除之前备份，本例为未备份直接删除。

[root@mysql1 ~]# cd /var/lib/mysql/[root@mysql1 mysql]# rm -rf mysql1-slow.log三、系统的优化3.1、配置Mysql日志文件定时清理3.1.1、方法一：无需重启，Mysql服务的配置方式

#查询当前保留的天数，默认为0即不自动删除；mysql> show variables like '%expire%';+--------------------------------+-------+| Variable_name | Value |+--------------------------------+-------+| disconnect_on_expired_password | ON || expire_logs_days | 0 |+--------------------------------+-------+2 rows in set (0.00 sec)#设置自动删除时间为30天，具体日志要保留的时间需以所在环境的要求决定；mysql> set global expire_logs_days = 30;Query OK, 0 rows affected (0.00 sec)#查询确认修改正常mysql> show variables like '%expire%';+--------------------------------+-------+| Variable_name | Value |+--------------------------------+-------+| disconnect_on_expired_password | ON || expire_logs_days | 30 |+--------------------------------+-------+2 rows in set (0.00 sec)3.1.2、方法二：需重启，修改Mysql服务配置文件的方式

[root@mysql1 mysql]# vi /etc/my.cnf#添加如下一行配置expire_logs_days=10#重启Mysql服务systemctl restart mysqld或service mysqld restart3.2、关闭未走索引查询语句的慢查询日志

#查询log_queries_not_using_indexes的当前状态是否关闭mysql> show variables like '%indexes%';+----------------------------------------+-------+| Variable_name | Value |+----------------------------------------+-------+| log_queries_not_using_indexes | ON || log_throttle_queries_not_using_indexes | 0 |+----------------------------------------+-------+2 rows in set (0.00 sec)#关闭log_queries_not_using_indexesmysql> set global log_queries_not_using_indexes = 'off';Query OK, 0 rows affected (0.00 sec)#确认已关闭mysql> show variables like '%indexes%';+----------------------------------------+-------+| Variable_name | Value |+----------------------------------------+-------+| log_queries_not_using_indexes | OFF || log_throttle_queries_not_using_indexes | 0 |+----------------------------------------+-------+2 rows in set (0.00 sec)