All myslq tables share a tablespace, causing the ibdata1 file to be too large. the database crashes _ MySQL

All myslq tables share a tablespace, causing the ibdata1 file to be too large. the database crashes bitsCN.com.

Mysql is used in linux. because the mysql price configuration is not completed at the beginning of the database creation, all tables use one tablespace, as a result, the ibdata1 file changes to 30 GB in less than half a year, and the mysql service cannot be started. it should be in/etc/my. add innodb_file_per_table = 1 to the [mysqld] file of the cnf file, that is, the mysql table uses a separate tablespace.

Innodb_file_per_table = 1

Innodb_file_per_table = 0

When the value is 1, a separate tablespace is used. if the value is 0, a shared tablespace is used.

The mysql service was no longer available when the landlord found this problem, so the innodb_force_recovery = 6 command was added to the configuration file. Then, start the mysql service, back up the database, and create a new database. now the ibdata1 file is only a few MB.

# Innodb_force_recovery
# Six non-zero values can be set: 1 ~ 6. A large number contains the effects of all the preceding small numbers.
#1 (srv_force_ignore_0000upt): ignore the checked UPT page.
#2 (SRV_FORCE_NO_BACKGROUND): prevents the running of the main thread. if the main thread needs to execute the full purge operation, crash may occur.
#3 (SRV_FORCE_NO_TRX_UNDO): no transaction rollback is performed.
#4 (SRV_FORCE_NO_IBUF_MERGE): insert buffer merge is not performed.
#5 (SRV_FORCE_NO_UNDO_LOG_SCAN): If you do not view the Undo Log, the InnoDB storage engine considers uncommitted transactions as committed.
#6 (SRV_FORCE_NO_LOG_REDO): do not roll back

BitsCN.com