Shulou(Shulou.com)06/01 Report--

This article is about how to deal with disk space problems caused by ibdata1 and mysql-bin in Linux operation and maintenance. I think it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it.

When we first learn Linux operation and maintenance, we may encounter various failures or problems. If we want to become veteran drivers in the industry, we need to keep summing up and accumulating. Here is a list of common problems and solutions shared by our predecessors in Linux operation and maintenance. Let's learn them together:

Problem: 2.51 disk space alarm. After checking, it is found that ibdata1 and mysql-bin logs take up too much space (among them, ibdata1 exceeds 120g, MySQL bin exceeds 80g)

Reason: ibdata1 is a storage format. In the INNODB type data state, ibdata1 is used to store the data and indexes of files, while the table files in the folder of the library name are just structures.

The innodb storage engine has two ways to manage table spaces, which are:

1) sharing tablespaces (which can be split into multiple small tablespace files), which is the method used by most of our databases at present

2) independent tablespaces, each table has a separate tablespace (disk file)

For the two management methods, each has its own advantages and disadvantages, as follows:

① shared tablespaces:

Advantages: tablespaces can be divided into multiple files and stored on different disks (tablespace file size is not limited by table size, and a table can be distributed over out-of-sync files).

Disadvantages: all data and indexes are stored in one file, then as the data increases, there will be a large file. Although a large file can be divided into multiple small files, multiple tables and indexes are mixed and stored in the table space. In this way, there will be a lot of gaps in the table space after a large number of deletions are done on a table. In the case of shared tablespace management, once the tablespace is allocated, it cannot be retracted. When the operation table space of temporary indexing or creating a temporary table is expanded, there is no way to shrink that part of the space even by deleting the related table.

② independent tablespaces: set in configuration file (my.cnf): innodb_file_per_table

Features: each table has its own independent tablespace; the data and indexes of each table will exist in its own tablespace.

Advantages: the disk space corresponding to the tablespace can be reclaimed (the Drop table operation automatically reclaims the tablespace, if the table after deleting a large amount of data can use: alter table tbl_name engine=innodb; to retract the unused space.

Disadvantages: if the single table increases too much, such as more than 100G, the performance will also be affected. In this case, files can be separated if shared tablespaces are used, but there is also a problem. If the scope of access is too large, it will also access multiple files, which will also be slow. If you use independent tablespaces, you can consider using partitioned tables to alleviate the problem to some extent. In addition, when independent tablespace mode is enabled, the setting of the innodb_open_files parameter needs to be adjusted reasonably.

Resolve:

1) the ibdata1 data is too large: you can only export the sql statement to build the database through dump, and then rebuild the method.

2) mysql-bin Log is too large:

① manually deletes:

Delete a log: mysql > PURGE MASTER LOGS TO 'mysql-bin.010'

Delete the log from a certain day: mysql > PURGE MASTER LOGS BEFORE '2010-12-22 1314

② sets only N-day bin-log logs in / etc/my.cnf

Expire_logs_days = 30 / / the number of days automatically deleted by Binary Log

After reading the problem shared by your predecessors, quickly take a small notebook and write it down so that you can handle it when you encounter problems.

The above is how to deal with the disk space problems caused by ibdata1 and mysql-bin in Linux operation and maintenance. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.