Storage file issues under MySQL

Ibdata1 & mysql-bin Problem: Disk space alert, found that ibdata1 and Mysql-bin logs occupy too much space (where ibdata1 exceeds 120g,mysql-bin by more than 80G)
Cause: IBDATA1 is the storage format, in InnoDB type data state, IBDATA1 is used to store the data and index of the file, and the library name of the folder of the table file is only the structure.
The InnoDB storage engine has two types of table-space management, namely:
1) shared table space (can be split into a number of small tablespace files), this is the majority of our current database use method;
2) stand-alone table space, each table has a separate tablespace (disk file)
For the two ways of management, each has its merits and demerits, as follows:
① Shared table spaces:
Pros: You can divide a tablespace into multiple files on separate disks (the tablespace file size is not limited by the size of the table, and a table can be distributed across files that are out of sync).
Disadvantage: All data and indexes are stored in a file, then as the data increases, there will be a large file, although a large file can be divided into several small files, but multiple tables and indexes in the table space mixed storage, so that if a table after a large number of deletions, there will be a large number of gaps in the table space. For shared table space management, once the tablespace is allocated, it cannot be retracted. When a temporary index is created or the table space for creating a temporary table expands, it is not possible to remove the associated table or to shrink that part of the space.
② standalone tablespace: Set in configuration file (my.cnf): innodb_file_per_table
Features: Each table has a self-contained table space, and the data and indexes for each table exist in the table space themselves.
Advantage: The disk space corresponding to the tablespace can be retracted (the Drop table operation automatically reclaims the tablespace if the table can be passed after deleting large amounts of data: ALTER TABLE Tbl_name ENGINE=INNODB;
Disadvantage: If the single table is increased too large, such as more than 100G, performance will be affected. In this case, if you use shared tablespace, you can separate files, but there is also a problem, if the scope of access is too large to access multiple files, it will be slower. If you use a stand-alone table space, consider using partitioned tables to mitigate the problem to some extent. In addition, when you enable standalone tablespace mode, you need to adjust the settings of the Innodb_open_files parameter reasonably.
Solve:
1) ibdata1 data is too large: only through dump, to export the SQL statement to build the database, and then rebuild the method.
2) Mysql-bin log too large:
① Manual removal:
Delete a log: Mysql>purge MASTER LOGS to ' mysql-bin.010′;
Delete a day before the log: Mysql>purge MASTER LOGS before ' 2010-12-22 13:00:00′;
② in/etc/my.cnf to save only n days of Bin-log logs
Expire_logs_days =//binary log automatically deleted days MySql ibdata1 file

MYSQL InnoDB If it is a shared tablespace, the ibdata1 file is getting bigger and larger, reaching more than 30 g, emptying some useless tables:
TRUNCATE TABLE xxx;
then optimize table xxx; No effect
Because the shared table space does not work.
MySQL ibdata1 store data, indexes, etc., is the most important data of MySQL.
If the data is not stored separately, the size of the file can easily be on the G, or even dozens of G. For some applications, this is not a good fit. So make this file smaller.
Unable to automatically shrink, must data export, delete ibdata1, then data import, compare cumbersome, so need to change each table separate file.
Workaround: The data file is stored separately (how the shared tablespace is changed to a separate tablespace file for each table).
The steps are as follows:
1) Backing up the database
Back up all databases, execute commands
#mysqldump-Q-uroot-ppassword--add-drop-table--all-databases >/home/backup/all.sql
After you finish this step, stop the database service.
#service mysqld Stop
2) Locate the My.ini or my.cnf file
Execute under Linux
#/usr/libexec/mysqld--verbose--help | Grep-a 1 ' Default options '
Default options is read from the following files in the given order:
/ETC/MYSQL/MY.CNF/ETC/MY.CNF ~/.my.cnf
The Windows environment allows you to:
Mysqld--verbose--help > Mysqlhelp.txt
Notepad mysqlhelp.txt
Find the default options inside, and you can see the order of finding My.ini to find the real directory
3) Modify the MySQL configuration file
Open My.ini or my.cnf file
Under [Mysqld], add the following configuration
Innodb_file_per_table=1
Verify that the configuration is in effect and you can restart MySQL to perform
#service mysqld Restart
#mysql-uroot-ppassword
Mysql> Show variables like '%per_table% ';
+-----------------------+-------+
| variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | On |
+-----------------------+-------+
1 row in Set (0.00 sec)
Mysql>
See if the innodb_file_per_table variable is on
4) Delete the original data file
Delete the original ibdata1 file and log file ib_logfile*, delete the application database folder under/var/lib/mysql directory (MySQL folder do not delete)
5) Restore the database
Start the database service
Enter MySQL Server from the command line
Restore all databases, execute commands
#service mysqld Start
#mysql-uroot-pocs After these steps, you can see that the new Ibdata1 file is only dozens of M, and the data and indexes become small IBD files for a single table, which are under the folder of the corresponding database.
# LL
Total 295028
drwx------2 mysql mysql 36864 Apr 14:16 glpi
drwx------2 mysql mysql 36864 Feb 13:45 glpi-1
-RW-RW----1 mysql mysql 10485760 Apr 14:27 ibdata1
-RW-RW----. 1 mysql mysql 270532608 Apr 14:14 ibdata1-1
-RW-RW----1 mysql mysql 5242880 Apr 14:27 ib_logfile0
-RW-RW----. 1 mysql mysql 5242880 Apr 14:14 ib_logfile0_bak
-RW-RW----1 mysql mysql 5242880 Apr 14:28 ib_logfile1
-RW-RW----. 1 mysql mysql 5242880 Apr 22:50 ib_logfile1_bak
drwx------2 mysql mysql 4096 Apr 14:16 mrbs
drwx------2 mysql mysql 4096 Apr 12:05 mrbs-1
DRWX------. 2 mysql mysql 4096 Apr 14:16 MySQL
srwxrwxrwx 1 mysql mysql 0 Apr 14:16 mysql.sock
drwx------2 mysql mysql 12288 Apr 14:16 ocsweb
drwx------2 mysql mysql 12288 Nov ocsweb-1
# ll Mrbs
Total 808
-RW-RW----1 MySQL mysql April 14:16 db.opt
-RW-RW----1 mysql mysql 10492 Apr 14:16 mrbs_area.frm
-RW-RW----1 mysql mysql 98304 Apr 14:16 mrbs_area.ibd
-RW-RW----1 mysql mysql 9264 Apr 14:16 mrbs_entry.frm
-RW-RW----1 mysql mysql 131072 Apr 14:16 mrbs_entry.ibd
-RW-RW----1 mysql mysql 9442 Apr 14:16 mrbs_repeat.frm
-RW-RW----1 mysql mysql 98304 Apr 14:16 mrbs_repeat.ibd
-RW-RW----1 mysql mysql 8888 Apr 14:16 mrbs_room.frm
-RW-RW----1 mysql mysql 114688 Apr 14:16 mrbs_room.ibd
-RW-RW----1 mysql mysql 8688 Apr 14:16 mrbs_users.frm
-RW-RW----1 mysql mysql 98304 Apr 14:16 mrbs_users.ibd
-RW-RW----1 mysql mysql 8658 Apr 14:16 mrbs_variables.frm
-RW-RW----1 mysql mysql 98304 Apr 14:16 mrbs_variables.ibd
-RW-RW----1 mysql mysql 8738 Apr 14:16 mrbs_zoneinfo.frm
-RW-RW----1 mysql mysql 98304 Apr 14:16 mrbs_zoneinfo.ibd
# ll Mrbs-1
Total 88
-RW-RW----1 MySQL mysql April 12:05 db.opt
-RW-RW----1 mysql mysql 10492 Apr 12:05 mrbs_area.frm
-RW-RW----1 mysql mysql 9264 Apr 12:05 mrbs_entry.frm
-RW-RW----1 mysql mysql 9442 Apr 12:05 mrbs_repeat.frm
-RW-RW----1 mysql mysql 8888 Apr 12:05 mrbs_room.frm
-RW-RW----1 mysql mysql 8688 Apr 12:05 mrbs_users.frm
-RW-RW----1 mysql mysql 8658 Apr 12:05 mrbs_variables.frm
-RW-RW----1 mysql mysql 8738 Apr 12:05 mrbs_zoneinfo.frm

Text Source: http://www.xiaobo.li/db/419.html

Storage file issues under MySQL