What are the plans for MySQL to add fields online?
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!
