Yesterday using the command found that the disk usage is 100%, there are tens of megabytes left.

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. 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. [image-upload failed…(image-202ebD-1624943747050)]

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 debris removal:

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

Pay attention to

1.MySQL officials recommend not to frequently (hourly or daily) defragment, generally need to defragment once a week or once a month according to the actual situation (we are now at 4am every month to clean all instances of MySQL table fragmentation).

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;

If you still encounter any problems about MySQL that cannot be solved, please refer to this MySQL learning notes I compiled, which recorded some solutions to problems encountered by myself and my colleagues and friends. I hope it will be helpful to you. Of course, private messages are also welcome to communicate with you.

Finally, how about a “like” and a “follow”?

end