Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the scheme for adding fields online for MySQL?". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Option 1: conduct alter table operations during the business trough:

For example, at 1: 00 or 2: 00 a.m.:

Mysql > ALTER TABLE `test`ADD `new_ column`int not null default 0 comment 'text length'

Scenario 2: create temporary tables, add fields, insert old data, insert incremental data, and rename table names

1)。 Create a temporary table

Mysql > create table test_tmp like test

2)。 Add a field to a temporary table

Mysql > ALTER TABLE `test_ tmp` ADD `new_ column`int not null default 0 comment 'text length'

3)。 Insert old data into a temporary table

Mysql > INSERT INTO test_tmp (id,name) (SELECT id,name from test)

4)。 Incremental data insertion:

Mysql > INSERT INTO test_tmp (id,name) (SELECT id,name from test WHERE `id` > (SELECT MAX (`id`) from `test_ tmp`))

5)。 Rename the table name:

Mysql > rename table test TO test_2018

Mysql > rename table test_tmp TO test

Solution 3: implement it with third-party open source tools. For specific implementation steps, please see the link:

Oak-online-alter-table:

Https://wing324.github.io/2015/10/13/oak-online-alter-table%E5%B7%A5%E5%85%B7/

Pt-osc:

Https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

Gh-ost:

Http://www.cnblogs.com/jiangwenju/p/5846743.html

Facebook osc (Online Schema Change for MySQL):

Https://github.com/facebookincubator/OnlineSchemaChange

Http://cenalulu.github.io/mysql/open_source_osc/

Scenario 4: add fields in the slave library, and then switch between mysql master and slave

Note: if the master-slave copy log format is in row format, the new column must be at the end of the table.

This is the end of the content of "what are the plans for MySQL to add fields online". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.