Urgently modify the table storage engine in the production environment: Set MyISAM to InnoDB

Source: Internet
Author: User

Environment Description:
A friend company performs a database migration. Due to setup problems, all database table storage engines are set to MyISAM, based on business needs,
Some database tables must be: InnoDB; otherwise, transaction execution may fail and will not be effective. Therefore, change the database table storage engine from MyISAM to InnoDB,
There are more than 300 tables in the database, of which nearly 1/3 need to be modified to InnoDB, and the rest will continue to use the MyISAM engine.
Note: Currently, there is no InnoDB Engine table, and my. CNF is not properly configured, and relevant parameters are not properly configured.

Hardware environment:
Model: r710 CPU: 2 4 Core e5620 mem: 24g raid: 1 + 0 Disk: 6*15 K * 146G network: 2*1000 m

The SQL statement that automatically generates the modification and check actions:

SQL statement used to generate changes:
Select Concat ('alter table', table_name, 'Engine = InnoDB; ') from information_schema.tables where table_schema = 'db _ name' and 'engine' = 'myisam ';

The SQL statement used to generate the checklist:
Select Concat ('check table', table_name) from information_schema.tables where table_schema = 'db _ name ';

Due to the fact that the table name is not regular, you cannot narrow the scope by SQL statement conditions, so you have to manually edit it later. When the SQL statements for change and check are ready, you will soon start the modification process:

1. Stop the mysqld service
Mysqladmin-uroot-P ***-socket =/data/mysqldata/sock/MySQL. Sock Shutdown

2. modify the configuration of my. CNF File
Innodb_file_per_table
Innodb_data_file_path = ibdata1: 256 M: autoextend

Innodb_buffer_pool_size = 10g
Innodb_log_buffer_size = 8 m
Key_buffer_size = 2G

Innodb_log_file_size = 256 m
Innodb_log_files_in_group = 8

3. Move the generated ibdata and ib_logfile files to other directories first.
Note: Currently there are no tables using the InnoDB engine. If there is a need for additional processing, if the table space is still shared, it cannot be so dry.

4. Start the mysqld service
Mysqld_safe &

5. Modify parameters
Set global sort_buffer_size = 128*1024*1024;
Set global tmp_table_size = 128*1024*1024;
Set global read_buffer_size = 32*1024*1024;
Set global read_rnd_buffer_size = 32*1024*1024;

6. Execute the alter table statement

7. Execute the check statement

8. modify my. CNF Configuration
Sort_buffer_size = 2 m
Tmp_table_size = 8 m
Read_buffer_size = 2 m
Read_rnd_buffer_size = 1 m

9. set global variables
Set global sort_buffer_size = 2x1024*1024;
Set global tmp_table_size = 8x1024*1024;
Set global read_buffer_size = 2x1024*1024;
Set global read_rnd_buffer_size = 1*1024*1024;

10. Open Services, test and monitor MySQL logs and program logs

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.