Change the storage engine to InnoDB for the MySQL MyISAM table with a online record, and immediately lock the alter table.

A friend of the industry, Penguin, asked:

Sun Shine:I have an online database with about 600 million data. I want to change the storage engine to InnoDB. Now, the table has an average of 50 inserts per minute, and the modified table is immediately locked. Is there any good solution?

 

Huang Shu 11:27:31
It will definitely lock the table. Which time is your full-day business off-peak? LTER table tbname engine = InnoDB; is the full lock table Sun shine11: 28: 33
Relatively low data points on average Huang Shu () 11:28:57
In this case, is your mode ms or mm? If this is the case, you can first do it on one slave and then switch between the master and slave nodes. Huang Shu () 11:30:05
Does the online production database have a master-slave mechanism? Sun shine11: 30: 11
How long does it take to change the entire table?
Right. The next step is to copy the master node.

Huang Shu () 11:31:32
In this way, you can create a temporary table in the test database, and add the records to the temporary table. First, alter table tbname engine = InnoDB.

Sun shine11: 32: 15
I will try it. Is there any other good solution?

Huang Shu () 11:32:30
Yes. Use a temporary table.

Sun shine11: 33: 02
Okay. I'll try it. Thank you.

 

Huang Shu () 11:33:14
1. Create a temporary table on the test database. The table structure is the same as that recorded at, but engine = InnoDB
2. Import million records to the temporary table.
3. Create a trigger on the original table to synchronize data to the temporary table.
4. Observe for a while to check whether the data in the original table is consistent with that in the temporary table.
5. After the observation is complete, run rename table xxx. tbname to test. tbname_old_myisam; rename table test. tbname to XXX. tbname; bottom switch, of course, this process will have 2 seconds of down time

 

Sun shine11: 36: 33
Well, it will be okay in 2 seconds.

 

Huang Shu () 11:36:39
You only need 1 TPS. It is estimated that the down time of up to 2 seconds has almost no impact on you.

Sun shine11: 36: 50
Yes.

 

Huang Shu () 11:37:14
You can try this solution in the test environment first.

 

Sun shine11: 37: 35
Well, how can I back up and import a single table without locking the table?

Huang Shu () 11:55:17
MyISAM, copy the file directly, and then restart the database. copy the data file to the directory of the test database, and then restart the MySQL database. The test database contains data.

Huang Shu () 11:56:18
This is the most brutal practice

Sun shine11: 56: 33
Strong.

Huang Shu () 11:56:35
Also, use mysqldump and MySQL to import data.
Mysqldump-uadmin-p-p3307-h127.0.0.1 -- default-character-set = utf8 -- extended-insert = false OS ulc>/home/check/ULC. SQL export similar to this
I used to perform this operation in the production database and soon exported it. Remember to add the-D parameter, only export data, and not export the table structure.

Huang Shu () 12:00:43
Another solution is to write a trigger to the original table without importing or exporting the data, and update the data to the temporary table at the same time.

Sun shine12: 01: 39
I am not very familiar with it, that is, I have a table library named Ota, which means that otalog is unique in this table and then imported to another database named test,

Huang Shu () 12:01:58
Yes, the test database table engine = InnoDB

Sun shine12: 03: 03
Mysqldump-uadmin-p-p3307-h127.0.0.1 -- default-character-set = utf8 -- extended-insert = false OS ulc>/home/check/ULC. How can I modify SQL?

 

Huang Shu () 12:03:49
Mysqldump-uadmin-p-p3307-h127.0.0.1 -- default-character-set = utf8 -- extended-insert = false-d ota otalog>/home/check/ULC. SQL

Huang Shu () 12:04:59
-U is followed by the user name and-P is followed by the password

Sun shine12: 05: 11
Good.

Share, I feel that the answer is not very good in some places. You are welcome to discuss it together.

 

Summary:

1Master-slave or master-slave online, not Standalone

272000 records. If 50 records are inserted in one minute, the number of records increases by 24x60x50 =, and the number of records increases by per day, one-year growth of select 24*60*50*365 = records, 2 million records. I personally think we can consider the sharding or partitioning policy.

3The table name ends with a log. It is estimated that it is a log series business. You can consider storing it in MongoDB nosql to reduce the pressure on MySQL.