MySQL Modify table structure tool Onlineschemachange usage experience

Onlineschemachange is the Facebook open source online modification table structure of the tool, the specific principle here not much to say, interested students can look at the official documents: https://github.com/facebookincubator/ Onlineschemachange/wiki

Here the main introduction of the use of the situation in the migration, the first official website of the OSC tool does not support master-slave synchronization, when the test is on the library test, and the production environment is a master-slave, the results in the main library directly run the OSC, you can see the following output:

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M01/9F/87/wKioL1meQPOhsJAqAAGVGMl6L2E154.png-wh_500x0-wm_ 3-wmp_4-s_2367765212.png "style=" Float:none; "title=" 1.png "alt=" Wkiol1meqpohsjaqaagvgml6l2e154.png-wh_50 "/>

You can see the main library running basically normal, the table structure is also modified, and there is no lock table affecting the online normal business

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M00/00/D7/wKiom1meQP_z2rlhAAFn9NXYzOE021.png-wh_500x0-wm_ 3-wmp_4-s_259049560.png "style=" Float:none; "title=" 2.png "alt=" Wkiom1meqp_z2rlhaafn9nxyzoe021.png-wh_50 "/>

Found after the master-slave inconsistency of the troubleshooting:

View the Binlog event to see which transaction is causing the master-slave inconsistency

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M02/9F/87/wKioL1meQPXQpJx9AADVi-YrJ5w970.png-wh_500x0-wm_ 3-wmp_4-s_787060525.png "style=" Float:none; "title=" 3.png "alt=" Wkiol1meqpxqpjx9aadvi-yrj5w970.png-wh_50 "/>

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M01/00/D7/wKiom1meQQCQ0opqAAELNZmIBiE751.png-wh_500x0-wm_ 3-wmp_4-s_3712155576.png "style=" Float:none; "title=" 4.png "alt=" Wkiom1meqqcq0opqaaelnzmibie751.png-wh_50 "/>

Repeated viewing and checking of transactional SQL with Binlog synchronization errors

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M00/9F/87/wKioL1meQPWiNBYOAAEvFrHl9u0352.png-wh_500x0-wm_ 3-wmp_4-s_3008917686.png "style=" Float:none; "title=" 5.png "alt=" Wkiol1meqpwinbyoaaevfrhl9u0352.png-wh_50 "/>

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M02/00/D7/wKiom1meQQLBwX22AAAuGG__4k0644.png-wh_500x0-wm_ 3-wmp_4-s_1641456706.png "style=" Float:none; "title=" 9.png "alt=" Wkiom1meqqlbwx22aaaugg__4k0644.png-wh_50 "/>

Depending on the state of the library, it is __osc_chg_orders that the temporary table does not exist causing the synchronization to occur.

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M02/9F/87/wKioL1meQPfTd2qfAADA48EmyAk899.png-wh_500x0-wm_ 3-wmp_4-s_3841781812.png "style=" Float:none; "title=" 10.png "alt=" Wkiol1meqpftd2qfaada48emyak899.png-wh_50 "/>

Then found online said skip the error transaction can be resumed synchronization, carried out

Slave stop; SET GLOBAL sql_slave_skip_counter = 1; Slave start; Show Slave Status\g

After skipping an error transaction, check again and find that each time it is stuck without this __osc_chg_orders temp table

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M02/9F/87/wKioL1meQPfDY5nlAADQ0y2Dz1U042.png-wh_500x0-wm_ 3-wmp_4-s_1444486491.png "style=" Float:none; "title=" 11.png "alt=" Wkiol1meqpfdy5nlaadq0y2dz1u042.png-wh_50 "/>

Online query to can ignore some of the table synchronization, in the library can ignore some of the table synchronization, in the main library Binlog a large number of temporary table transactions, start our manual one by one skip, found not feasible, manually executed 50 times after the discovery __osc_chg_ The data of the orders temporary table may be as large as the data of the original table, that is, 10 million, so we have to skip 10 million transactions, if we skip the 1kw transaction, also not, because there are in the line of normal execution of the transaction, here is found to ignore this table synchronization, Setting the Ignore from the library is skipping the transaction about the __osc_chg_orders temporary table in the Binlog, but Binlog will also record, if the main library is ignored is directly not recorded Binlog, from the library naturally can not sync to, here using from the library ignore Binlog this __ Osc_chg_orders Temp table, modify the configuration file:

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/00/D7/wKiom1meQQKRWn6WAAANtDZufE8358.png-wh_500x0-wm_ 3-wmp_4-s_2662506214.png "style=" Float:none; "title=" 12.png "alt=" Wkiom1meqqkrwn6waaantdzufe8358.png-wh_50 "/>

Ignoring the discovery can skip all __osc_chg_orders temporary table related error transactions, but also the transaction of error code 1032, and then follow this way to find, the problem you encounter others may have encountered in advance, found that there are ignored error code configuration:

Ignore after 1032 error, in the modification ignore 1032 error, problem resolution, synchronization normal

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M01/00/D7/wKiom1meQQOz8xREAAAMw81klLg477.png-wh_500x0-wm_ 3-wmp_4-s_4261544989.png "style=" Float:none; "title=" 13.png "alt=" Wkiom1meqqoz8xreaaamw81kllg477.png-wh_50 "/>

Reference Skip error code:

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/9F/87/wKioL1meQPjx3Ib1AAD4CMwXrgM853.png-wh_500x0-wm_ 3-wmp_4-s_1531190423.png "style=" Float:none; "title=" 14.png "alt=" Wkiol1meqpjx3ib1aad4cmwxrgm853.png-wh_50 "/>

Finally can only manually modify the table structure from the library, master-slave synchronization back to normal, to solve the problem, and then against the main library binlog transaction in the query from the library, the normal insert Binlog transaction, can be queried from the library, indicating that the normal transaction in Binlog is synchronized normal, Just skip the wrong transaction and ensure the data consistency between master and slave.

The simple summary from the library modification is as follows:

1, the use of skipping a single transaction method, found that the wrong transaction too much, the middle and mixed with the correct transaction, resulting in the inability to skip all the wrong transactions at once

2, ignoring the characteristics of the error transaction synchronization, found that all the problematic things are caused by synchronizing a temporary table, only need to skip the temporary table, so that you can skip most of the error transactions, the normal execution of the transaction will not be affected, will also be synchronized

3, using ignore synchronization error code to achieve, directly skip the same error code transactions, restore synchronization

4, compared to the main library binlog in the normal transaction is executed from the library, query in the main library Binlog normal insert transaction, in the library from the select corresponding to insert data, can be queried, that the normal transaction in which the Binlog synchronization to the slave library successfully, This solves the problem that the master never synchronizes!

---------------------OSC Bug Resolution----------------------------------------

One thing to note here is that binlog to use row mode

1. Solve the non-support Ip+port mode, the official website only supports socket, that is, only in the local database run, but most of the current is the cloud database, so only the socket connection mode can not meet the current needs, modify the source after supporting the Host+port way

2. Solve the problem of the master never agree, the official website only supports the case of the library, that is, the master-slave situation will only modify the main library table structure, and will not modify the table structure from the library, because the OSC will binlog the database before each modification, resulting in the temporary table can not be synchronized through Binlog to the slave library, Modify the source code so that it does not close the Binlog, solve the problem of the master never Sync

PS: The official website to do this may take into account that write Binlog performance will be degraded, it is true, but for the current YUNCU performance is basically no impact, you can modify the table structure, the test 10 million table modification table structure is about 10 minutes, high efficiency, and will not affect the continuous operation of the online business

official GitHub OSC: Https://github.com/facebookincubator/OnlineSchemaChange

modified git address: Https://github.com/lxshopping/OnlineSchemaChange   

This article is from the "Operation and maintenance of the Road" blog, please be sure to keep this source http://lxshopping.blog.51cto.com/4542643/1958935

MySQL Modify table structure tool Onlineschemachange usage experience