What happens if the mysql disk is full? I really met ~

Use the command to find that the disk usage is 100%, with tens of megabytes remaining.

# a series of divine operations

Back up the database, delete the instance, delete the database table, and restart the mysql service, but the disk space is not released.

#?

Online check a lot of resources, said to carry out disk fragmentation. The reason is that datafree takes up too much space. The details can be viewed through this SQL.

SELECT CONCAT (TRUNCATE (SUM (data_length) / 1024/1024, 2), 'MB) AS data_size, CONCAT (TRUNCATE (SUM (max_data_length) / 1024/1024, 2), 'MB) AS max_data_size, CONCAT (TRUNCATE (SUM (data_free) / 1024/1024, 2), 'MB) AS data_free, CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size FROM information_schema.tables WHERE TABLE_NAME = 'datainfo'; This is a later graph, and I didn't leave the previous graph, which showed that the data_free in each table was 20 grams.Copy the code

The recommended online approach is to fragment the form as follows.

ALTER TABLE datainfo ENGINE=InnoDB; ANALYZE TABLE datainfo; optimize table datainfo;Copy the code

# deadlock:

Check that the database version is 5.562 and inODB is not supported, or upgrade the database. The bad news is that the table has been deleted, but the disk space is still not free. So the fragmentation of the table to sort out the way to go, because the table is gone.

# Later divine operation

1. Run the following command to check the mysql installation location and configuration file location

mysql 1118 945 0 14:28 ? 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
Copy the code

2. Close mysql

service mysql stop
Copy the code

3. Delete the ibdata1 and ib_logFILE0 ib_logFile1 files in the datadir directory

​

4. Move the mysql startup parameters

mv /etc/my.cnf ./abc
Copy the code

5. Run the following command to restart mysql: service mysql start

Disk space is finally free

Next database Restore

1. Use navicate backup tool to back up the database

​

After the backup is successful, the PSC file is generated. 200409141055.psc

2. Create a database instance and set the database name and character set

​

3. Then restore the backup database and click Restore

​

After the first restore, it was found that the restored database table was successfully built, but there was no data in the table.

Later on the net search data discovery is, encounter error to stop. So change the configuration of the restore and restore again.

That’s how it was set up

​

Restore as a transaction and stop when an error occurs. Change the configuration

​

Re-restore, database data has, and verify that there is no problem.

# Problem solving

Causes of mysql fragmentation

(1) Fragmentation occurs in the storage of the table. Every time a row is deleted, the space will become empty, and a large number of deletion operations within a period of time will make the empty space become larger than the space used to store the list contents;

(2) When performing insert operations, MySQL will try to use blank space, but if a certain blank space has not been occupied by the appropriate size of data, it still cannot be completely occupied, thus forming fragments;

(3) When MySQL scans data, the objects it scans are actually the upper limit of the capacity requirement of the list, that is, the part at the peak position in the region where data is written;

# Advantages of removing debris:

Reduce I/O for accessing tables, improve mysql performance, and release table space to reduce disk space usage

Pay attention to

MySQL does not recommend frequent defragmentation (hourly or daily), but only weekly or monthly defragmentation depending on the actual situation (we are currently cleaning table fragments under all MySQL instances at 4am every month).

2, MySQL locks tables during OPTIMIZE TABLE run. Therefore, make sure to do this during times of low traffic.

3, clearing 1.05 million data of student, OPTIMIZE TABLE library. The local test takes 37 seconds.

# self-test

Mysql > alter table datafree show table status from datafree;