OS: Cent OS 6.3

DB: 5.5.14

看到一個帖子，在伺服器上安裝了oracle和mysql資料庫，mysql資料庫忘記開啟innodb_file_per_table，導致插入測試資料後磁碟空間爆滿，mysql啟動報錯，並且無法啟動。

1、可以直接刪除ibdata檔案後重建mysql資料庫，but，裡面還有一些有用資料，因此無法直接刪除ibdata進行操作，只能另謀出路。
2、Linux預設會保留5%的空間，用tune2fs命令

模擬：

伺服器上也有oracle和mysql資料庫，用dd直接把磁碟空間撐爆

dd if=/dev/zero of=/export/test bs=50MB count=2000

[root@db01 export]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
50G 48G 0 100% /
tmpfs 7.7G 0 7.7G 0% /dev/shm
/dev/sda1 485M 32M 429M 7% /boot

[root@db01 ~]# service mysqld stop
Shutting down MySQL............................................ SUCCESS!

[root@db01 ~]# service mysqld start
Starting MySQL.................................................................................................................................................................................................................................................................................................................................無法正常啟動

資料庫忘記設定innodb_fiile_per_table，其它庫中又有有用資料，無法直接刪除ibdata重新配置

在Linux系統中，預設會保留5%的空間

[root@db01 log]# tune2fs -m 1 /dev/mapper/VolGroup-lv_root
tune2fs 1.41.12 (17-May-2010)
Setting reserved blocks percentage to 1% (131072 blocks)

檢視磁碟空間：

[root@db01 log]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
50G 48G 783M 99% /
tmpfs 7.7G 0 7.7G 0% /dev/shm
/dev/sda1 485M 32M 429M 7% /boot

空間由原來的100%變成了99%，嘗試啟動mysql資料庫
[root@db01 ~]# service mysqld start
Starting MySQL SUCCESS!

[root@db01 ~]# ps -ef | grep mysql
root 31405 1 3 00:37 pts/2 00:00:00 /bin/sh /export/servers/mysql/bin/mysqld_safe --datadir=/export/servers/mysql/data --pid-file=/export/servers/mysql/tmp/mysql.pid
mysql 32200 31405 63 00:37 pts/2 00:00:01 /export/servers/mysql/bin/mysqld --basedir=/export/servers/mysql --datadir=/export/servers/mysql/data --plugin-dir=/export/servers/mysql/lib/plugin --user=mysql --log-error=/export/servers/mysql/log/error.log --open-files-limit=65535 --pid-file=/export/servers/mysql/tmp/mysql.pid --socket=/export/servers/mysql/tmp/mysql.sock --port=3306
root 32218 27979 0 00:37 pts/2 00:00:00 grep mysql

[root@db01 bin]# ./mysql -uroot -S /export/servers/mysql/tmp/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.14-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| test |
| yoon |
+--------------------+
6 rows in set (0.05 sec)

mysql>

最後透過mysqldump備份資料庫，刪除ibdata重新配置mysql，並設定innodb_file_per_table 為 on

tune2fs用法：
1、tune2fs --help
2、找度娘

Linux磁碟空間爆滿，MySQL無法啟動

相關文章