Use the innodb_file_per_table parameter to support the MySQLInnoDB table data sharing space.

Reference: linuxfun. me? For those who have used MySQL in p1263, The MyISAM Table engine is the most frequently used at the beginning. The database of this engine creates three files: Table Structure, table index, and table data space. You can migrate a database directory to another database. However, when you use InnoDB

Reference: http://linuxfun.me /? P = 1263 if you have used MySQL, The MyISAM Table engine is the most commonly used at the beginning. The database of this engine creates three files: Table Structure, table index, and table data space. You can migrate a database directory to another database. However, when you use InnoDB

References:
Http://linuxfun.me /? P = 1263

If you have used MySQL, The MyISAM Table engine is the most frequently used at the beginning. The database of this engine creates three files: Table Structure, table index, and table data space. You can migrate a database directory to another database.
However, when you use InnoDB, everything changes. By default, InnoDB stores all the table data of the InnoDB engine in a shared space: ibdata1, which makes it uncomfortable. When adding or deleting databases, the ibdata1 file does not automatically contract, the backup of a single database will also become a problem. Data can only be exported using mysqldump and then imported to solve this problem.
In the MySQL configuration file [mysqld], add the innodb_file_per_table parameter to change InnoDB to an independent tablespace mode. Each table in each database generates a data space.

Independent tablespace
Advantages:
1. Each table has its own independent tablespace.
2. Data and indexes of each table are stored in its own tablespace.
3. A single table can be moved in different databases.
4. space can be recycled (table space cannot be recycled automatically in the drop/truncate table operation mode)
5. For tables that use independent tablespaces, no matter how they are deleted, the tablespace fragments will not seriously affect performance, and there is a chance to process them.

Disadvantages:
A single table is larger than a shared space.

Conclusion:
The shared tablespace has some advantages in the Insert operation, but it does not do well in other independent tablespaces.
When the independent tablespace is enabled, adjust the innodb_open_files parameter properly.

The following is a practical solution to the problem that the ibdata1 file is too large due to too many history records of the MySQL database for online Zabbix.
1. view the file size
$ Sudo cd/var/lib/mysql
$ Ls-lh

 total 14G-rw-r--r-- 1 root root 0 Dec 1 14:31 debian-5.1.flag-rw-rw---- 1 mysql mysql 5.0M Jan 17 21:31 ib_logfile0-rw-rw---- 1 mysql mysql 5.0M Jan 17 21:29 ib_logfile1-rw-rw---- 1 mysql mysql 14G Jan 17 21:31 ibdata1drwx------ 2 mysql root 4.0K Dec 1 14:31 mysql-rw-rw---- 1 root root 6 Dec 1 14:31 mysql_upgrade_infodrwx------ 2 mysql mysql 4.0K Jan 17 21:29 zabbix

The size of the shared table data space file ibdata1 has reached 14 GB.

Log on to MySQL and check which tables occupy space.
$ Mysql-uroot-p

 mysql > select table_name, (data_length+index_length)/1024/1024 as total_mb, table_rows from information_schema.tables where table_schema='zabbix';+-----------------------+---------------+------------+| table_name            | total_mb      | table_rows |+-----------------------+---------------+------------+| acknowledges          |    0.06250000 |          0 |....| help_items            |    0.04687500 |        103 || history               | 9678.00000000 |  123981681 || history_log           |    0.04687500 |          0 |...| history_text          |    0.04687500 |          0 || history_uint          | 5386.98437500 |   57990562 || history_uint_sync     |    0.04687500 |          0 |...| timeperiods           |    0.01562500 |          0 || trends                |   54.54687500 |     537680 || trends_uint           |  100.53125000 |    1035592 |...103 rows in set (1.46 sec)

As you can see, the records in the history Table have reached 9 GB and 123981681 million records, that is, 0.1 billion million records. At the same time, history_unit is also relatively large, reaching 5 GB and about records;
In addition, some data exists in trends and trends_uint.
Because the data volume is too large, it is basically impossible to delete the data in the normal way.
Because we automatically send data reports every day, we decided to directly use the truncate table method to quickly clear the data of these tables, and then use mysqldump to export data and delete the shared tablespace data files, re-import data.

2. Stop related services to avoid Data Writing
$ Sudo/etc/init. d/zabbix-server stop
$ Sudo/etc/init. d/apache2 stop

3. Clear historical data
$ Mysql-uroot-p

 mysql > use zabbix;Database changedmysql > truncate table history;Query OK, 123981681 rows affected (0.23 sec) mysql > optimize table history; 1 row in set (0.02 sec)mysql > truncate table history_uint;Query OK, 57990562 rows affected (0.12 sec) mysql > optimize table history_uint;1 row in set (0.03 sec)mysql > truncate table trends;Query OK, 537680 rows affected (0.04 sec) mysql > optimize table trends;1 row in set (0.02 sec)mysql > truncate table trends_uint; Query OK, 1035592 rows affected (0.02 sec)   mysql > optimize table trends_uint; 1 row in set (0.01 sec)           

4. Back up data
$ Mysqldump-uroot-p zabbix> ~ /Zabbix. SQL

5. Stop the database
$ Sudo stop mysql

6. Delete shared tablespace data files
$ Cd/var/lib/mysql
$ Rm ib *

7. added the innodb_file_per_table parameter.
$ Sudo vim/etc/mysql/my. cnf
Set in [mysqld]

 innodb_file_per_table=1

8. Start MySQL
$ Sudo start mysql

9. Check whether the parameters take effect
$ Mysql-uroot-p

 mysql> show variables like '%per_table%';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| innodb_file_per_table | ON |+-----------------------+-------+1 row in set (0.00 sec)

10. re-import data
$ Mysql-uroot-p zabbix <~ /Zabbix. SQL

11. Write a script to automatically clean up data every day and keep the data for 30 days.
$ Sudo vim/etc/cron. daily/clean_zabbix_olddata.sh

 #!/bin/bashDATE=`date -d "30 days ago"`CLOCK=`date +%s -d "${DATE}"`MYSQL="mysql -uroot -p zabbix"for TABLE in history trendsdo  $MYSQL -e "DELETE FROM ${TABLE} WHERE clock < ${CLOCK};"  $MYSQL -e "OPTIMIZE TABLE ${TABLE};"  $MYSQL -e "DELETE FROM ${TABLE}_uint WHERE clock < ${CLOCK};"  $MYSQL -e "OPTIMIZE TABLE ${TABLE}_uint;"done

12. Finally, restore the service process.
$ Sudo/etc/init. d/zabbix-server start
$ Sudo/etc/init. d/apache2 start

Original article address: The innodb_file_per_table parameter is used to support automatic shrinking of the data sharing space of the MySQL InnoDB table. Thank you for sharing it with the original author.