mysql5.5 and mysq 5.6 to disable the innodb engine
- 2020-06-15 10:23:01
- OfStack
Today found to have a backup mysql abnormal data folder, 1 check found to be more than 3 files: ibdata1 ib_logfile0 ib_logfile1, 18 m, the former two 5 m, after it was time to move from mysql5. 0 to 5.5, and 5.5 closed innodb start up, so I just open the innodb, because innodb will increase the default these data file and log files, lead to bigger. I tried to set the size of the data file, and it told me that 10m was too large, so I explored ways to turn off innodb.
Look at the log found that because the mysql program upgrade, need to run mysql_upgrade upgrade 1 mysql database, this is relatively simple, and mysql command usage is the same, run one time on ok. Then I found that innodb could not be turned off. Strangely, I found that es23EN5.5 used innodb by default, so I could not simply turn it off. I also needed to set 1 to use myisam by default.
Restart mysql and delete the 3 annoying files.
MySQL 5.6 INNODB is banned
INNODB, developed after MySQL was acquired by ORACLE, supports advanced functions such as transactions and row-level locking. However, not everyone needs INNODB. For most people, the previous MYISAM engine is enough.
In the previous MySQL, you could usually set it this way:
However, in the latest MySQL5.6, it is impossible to start with this setting. Another setting should be added:
Not only that, you also need to add the following configuration, otherwise the program will be easy to exit:
From http: / / docs. oracle. com/cd/E17952_01 / refman - 5.6 - en/innodb - turning - off. html
In addition, MYSQL 5.6 USES more physical memory than 5.5, and the virtual memory usage is similar to 5.5 (5.5 is also a big virtual memory consumer). The performance is about 30% better than 5.5 (according to the official documentation, no specific tests were performed).
Look at the log found that because the mysql program upgrade, need to run mysql_upgrade upgrade 1 mysql database, this is relatively simple, and mysql command usage is the same, run one time on ok. Then I found that innodb could not be turned off. Strangely, I found that es23EN5.5 used innodb by default, so I could not simply turn it off. I also needed to set 1 to use myisam by default.
default-storage-engine=MYISAM
innodb=OFF
Restart mysql and delete the 3 annoying files.
MySQL 5.6 INNODB is banned
INNODB, developed after MySQL was acquired by ORACLE, supports advanced functions such as transactions and row-level locking. However, not everyone needs INNODB. For most people, the previous MYISAM engine is enough.
In the previous MySQL, you could usually set it this way:
default-storage-engine=MYISAM
skip-innodb
However, in the latest MySQL5.6, it is impossible to start with this setting. Another setting should be added:
default-tmp-storage-engine=MYISAM
Not only that, you also need to add the following configuration, otherwise the program will be easy to exit:
loose-innodb-trx=0
loose-innodb-locks=0
loose-innodb-lock-waits=0
loose-innodb-cmp=0
loose-innodb-cmp-per-index=0
loose-innodb-cmp-per-index-reset=0
loose-innodb-cmp-reset=0
loose-innodb-cmpmem=0
loose-innodb-cmpmem-reset=0
loose-innodb-buffer-page=0
loose-innodb-buffer-page-lru=0
loose-innodb-buffer-pool-stats=0
loose-innodb-metrics=0
loose-innodb-ft-default-stopword=0
loose-innodb-ft-inserted=0
loose-innodb-ft-deleted=0
loose-innodb-ft-being-deleted=0
loose-innodb-ft-config=0
loose-innodb-ft-index-cache=0
loose-innodb-ft-index-table=0
loose-innodb-sys-tables=0
loose-innodb-sys-tablestats=0
loose-innodb-sys-indexes=0
loose-innodb-sys-columns=0
loose-innodb-sys-fields=0
loose-innodb-sys-foreign=0
loose-innodb-sys-foreign-cols=0
From http: / / docs. oracle. com/cd/E17952_01 / refman - 5.6 - en/innodb - turning - off. html
In addition, MYSQL 5.6 USES more physical memory than 5.5, and the virtual memory usage is similar to 5.5 (5.5 is also a big virtual memory consumer). The performance is about 30% better than 5.5 (according to the official documentation, no specific tests were performed).