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

A series of magical operations

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

What do I do

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';
Copy the code

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.

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

Gridlock:

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 way to defragment the table is also blocked, 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, Restart mysql and find that disk space is free

 service mysql start
Copy the code

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

Fragmentation occurs in table storage. Every time a row is deleted, the space will become empty, and a large number of deletion operations in a period of time will make the space become more empty than the space used to store the list content. When performing insert operations, MySQL tries to use blank space, but if a blank space has not been occupied by the appropriate size of data, it still cannot be completely occupied, resulting in fragmentation. When MySQL scans data, it scans the upper limit of the list’s capacity requirements, that is, the portion of the region where data is written at its peak.

Advantages of debris removal:

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

Note: 1.MySQL does not recommend frequent (hourly or daily) defragmentation, usually only once a week or once a month, 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 from student, OPTIMIZE TABLE library. The local test takes 37 seconds.

self-test

Mysql > alter table datafree show table status from datafree;

I have sorted out dozens of BAT interview videos, including 50G videos on the path of architects, for my friends. Focus on [Java Technology select] public number reply [666] self-claim. I set up a technology exchange group with some friends to discuss technology and share technical information, aiming to learn and progress together. If you are interested, please join us!

Whether you are just starting out as a programmer or have a few years of experience, this interview outline will give you a lot of help