Add InnoDB to installed MySQL example

Add InnoDB to installed MySQL

1. First determine if there are ha_innodb_plugin.so and ha_innodb.so two files under MySQL ' Plugin_dir ', if not found, in your MySQL compilation directory with the following directory file ( Built in InnoDB and Innodb_plugin)
Storage/innobase/.libs/ha_innodb.so
Storage/innodb_plugin/.libs/ha_innodb_plugin.so
You need to copy them (ha_innodb_plugin.so and ha_innodb.so) to the MySQL plugin directory (/usr/local/mysql/lib/mysql/plugin)



2. Install on MySQL command line

mysql> INSTALL PLUGIN InnoDB soname ' ha_innodb.so ';
Query OK, 0 rows affected (1.74 sec)




Increase the table space of the MySQL InnoDB

Innodb_data_file_path
The variable sets the location and size of the InnoDB data file. The specified file must be greater than 10M, and can be set greater than 4G if it is not restricted by system files. This variable is the core element of MySQL server capacity planning and performance scalability. The usual setting is to create a baseline size for the contents of a data directory, between 10M and 128M, and the second file to 10M and automatically expand. such as Innodb_data_file_path = Ibdata1:128m;ibdata2:10m:autoextend.

Innodb_file_per_table
There are three types of allocation and management table spaces:
1. The table space of the original partition is raw
2. shared File Table space
3. Separate table spaces for each table

The benefits of using innodb_file_per_table are:
1. Easy to manage data files if you use soft links to assign large tables to different partitions
2. Easy to monitor and solve the problem of IO Resource usage
3. Easy to repair and restore corrupted data
3.1 Independent of each other and will not affect other InnoDB tables
3.2 Export import is only for a single table, not for the entire shared table space
4. Resolution of a single file size limit
5. Easy to reclaim disk space for a large number of delete operations
6. Less fragmentation, easy to organize optimize table
7. Easy Security Audits
8. Easy to Backup

If you set innodb_file_per_table after the InnoDB table has been created, the data will not migrate to a separate tablespace, but rather a shared tablespace before the sequel uses it.
Only newly created tables are detached to their own table space files.
[Warning]innodb_file_per_table is a static variable to restart the MySQL service to take effect after the change. [/warning]

Add Table Space
When you do not use innodb_file_per_table and do not enable automatic expansion, the table space will be full as the data grows. In this case, additional table space needs to be added to expand the capacity. The method is as follows:
1. Stop MySQL Service
2. Back up the configuration file for easy problems.
3. Edit Innodb_data_file_path Value
Change according to your environment ibdata1: $size; Ibdatan: $size; Ibdatan: $size; The currently defined table space or default table space cannot be changed, otherwise startup fails, but additional table spaces can be added, and the Ibdatan sequence increments according to the current number, $size customization.
4. Start MySQL Service
6. See if MySQL error log is wrong


Add and resize InnoDB log files

Innodb_log_group_home_dir

The transaction data is stored in the InnoDB redo log file before the transaction is committed and written to a tablespace disk file. These logs are in the directory defined by the Innodb_log_group_home_dir variable, and we usually set this directory to be the same as the Innodb_data_home_dir variable. For best performance, it is recommended that you detach Innodb_data_home_dir and innodb_log_group_home_dir to separate physical storage arrays, which will ensure that IO resources do not conflict and allow the server to handle a large number of high concurrency connections.

Innodb_log_file_size
This option determines performance and is carefully set. The default setting is 5 m, which is difficult to meet the requirements in the production environment. The log file is initialized the first time the MySQL instance is started, and the file is rotated, so the rotation frequency of the log file can be judged according to the file modification time, the rotation frequency is too frequent, the log file is too small to enlarge.
The innodb_log_file_size setting size is usually determined by the innodb_buffer_pool_size. Variables that affect log file performance are innodb_log_buffer_size, ensuring that there is a large enough log buffer to hold dirty data before being written to the log file.
For smaller innodb_buffer_pool_size, it is recommended to set the same size. However, for the larger innodb_buffer_pool_size, this setting is not recommended, and there is a potential problem, which is that when MySQL is hung up, it takes a long time to recover the data, resulting in a lot of downtime. The recommended setting for official documents is innodb_buffer_pool_size/innodb_log_files_in_group.
[Warning]innodb_log_file_size is a static variable that needs to be changed and restarted in a "clean" manner, otherwise MySQL will not start up.) [/warning]

Innodb_log_buffer_size
This variable imports data into memory, reducing the amount of IO resource consumption. When a transaction is committed, the dirty data is saved and subsequently flushed to disk. When we adjust the innodb_buffer_pool_size size, innodb_log_buffer_size and innodb_log_file_size should also make corresponding adjustments.

Innodb_log_files_in_group
This variable controls the number of log files. The default value is 2. The log is written in a sequential fashion.
[Warning]innodb_log_files_in_group is a static variable that needs to be changed and restarted in a "clean" manner, otherwise MySQL will not start up.) [/warning]

To add and adjust the InnoDB log file steps:
1. Stop MySQL Service

#/etc/init.d/mysqld Stop
[WARNING] must be shut down properly. [/warning] 2. Enter the log directory according to the INNODB_LOG_GROUP_HOME_DIR variable

# Cd/data/mysql_data
3. Back up old log files, safekeeping for easy fallback

# MV Ib_logfile*/backup/
4. Adjust log file size or quantity as required and write to my.cnf file
Innodb_log_file_size the log file size.
Innodb_log_files_in_group adjust the number of log files.
The innodb_log_group_home_dir adjustment log file is located in the directory.
5. Restart the MySQL service
This process will create the log file according to the new log configuration. Also, look at the MySQL error log to monitor for errors