This is the fifth day of my participation in the August More text Challenge. For details, see:August is more challenging

In the last article, how to optimize the slow SQL, improve the speed of the application, detailed please move to optimize the system slow query

Group_id = group_ID; group_id = group_id; group_ID = group_ID; If you directly run SQL ALTER TABLE h_APP_message ADD group_id bigint(20) on the client, the database will be blocked and take a long time, which directly affects the normal online use.

Generally, for the modification of a large amount of data, if the online concurrency is not very high, it can be manually processed. The methods are as follows:

  • First back up, back up, back up. Say the important things three times
  • Delete indexes from a table
  • Modify table structure
  • Repair data
  • Recovery index
  • The table structure is modified

Our processing method is to use the pt-online-Schame-change tool to modify the field of the formal environment online and add group_id.

Benefits of this tool:

Reducing the risk of master/slave delay can limit the speed and resources, and prevent MySQL from being overloaded during operationsCopy the code

Advice:

Do it when business is low to minimize the impactCopy the code

Alter table alter table

If a table has a large amount of data, modifying the table structure online affects the online environment and takes unpredictable timeCopy the code

Note:

You need to verify that the table must contain a primary key or unique index

The tool creates triggers, so there can be no triggers on the original table

Alter -foreign-keys-method for tables with foreign keys alter-foreign-keys-method

Principle:

  • First, it creates a new table with the exact same name, usually with the _new suffix
  • The change field operation is then performed on the new table
  • Then add three triggers, DELETE/UPDATE/INSERT, to the original table to execute the new table
  • Finally, copy the data from the original table to the new table, and then replace the original table

1. Back up data

Do a good backup, no matter how sure you are (in case it’s scary)

2. Install

Download the installation package:

Wget HTTP: / / https://downloads.percona.com/downloads/percona-toolkit/3.3.1/source/tarball/percona-toolkit-3.3.1.tar.gzCopy the code

Extract:

The tar - XVF percona toolkit - 3.3.1. Tar. GzCopy the code

Install some dependency packages:

yum install perl-DBIyum install perl-DBD-MySQLyum install perl-Time-HiResyum install perl-IO-Socket-SSLyum -y install perl-Digest-MD5
Copy the code

3. Test availability

Run the command in the bin directory of the decompressed package. Check whether the command is running properly

./pt-online-schema-change --help
Copy the code

4. Parameter Meanings

parameter meaning
–user= Connection user name
–password= Connect the password
–host= Connect the IP
P= port
–alter= A statement that executes a table change
D= The database library name
t= The table name of the table
–charset=utf8 Use UTF8 encoding, avoid Garbled Chinese
–no-check-alter The ALTER statement is not checked
–print Printing Operation Logs
–execute To actually alter the table structure, you must specify either -dry-run or -execute. They are mutually exclusive
– dry – run Create and modify a new table, but do not create triggers, copy data, or replace the original table. Don’t actually execute, work with –print to see the execution details

5. Specific operations

1. Add a field

If the execution fails, check the ALTER statement and avoid checking if it is correct--no-check-alter

./pt-online-schema-change --user=xxxx --password=xxxx --host=xxx.xxx.xxx.xxxx --alter "add column group_id bigint(20) not NULL default '0' comment 'test' " P=30306,D=h_pushcenter,t=h_message --charset=utf8 --no-version-check --print --execute
Copy the code

2. Modify the field

The SQL statement:

ALTER TABLE `h_message` MODIFY COLUMN `group_id` int(20) NOT NULL DEFAULT '1'; ALTER TABLE `h_message` MODIFY COLUMN `group_id` int(20) NOT NULL DEFAULT '1';Copy the code

Pt command:

--alter "MODIFY COLUMN group_id int(20) NOT NULL DEFAULT '1'"
Copy the code

3. Change the field name

The SQL statement:

ALTER TABLE `h_message` CHANGE column group_id group_id_0 bigint(20);
Copy the code

Pt command:

--alter "CHANGE group_id group_id_0 bigint(20)"
Copy the code

4. Add indexes

The SQL statement:

ALTER TABLE `h_message` ADD INDEX h_message_n1(group_id);
Copy the code

Pt command:

--alter "ADD INDEX h_message_n1(group_id)"
Copy the code

6. Operation logs

  • Create a new table with the new ending
Creating new table...
CREATE TABLE `h_pushcenter`.`_h_message_new` .....
Created new table h_pushcenter._h_message_new OK.
Copy the code
  • The new table performs the ALTER operation
Altering new table...
ALTER TABLE `h_pushcenter`.`_h_message_new` add column  group_id bigint(20) not NULL default '0'  comment 'test'
Altered `h_pushcenter`.`_h_message_new` OK.
Copy the code
  • Create three triggers on the original table
Creating triggers...
Event : DELETE
Event : UPDATE
Event : INSERT
Created triggers OK.
Copy the code
  • Copy data to the new table
Copying approximately 8187 rows...
Copied rows OK.
Copy the code
  • Rename the old and new tables, then replace and delete the old table
2021-05-19T10:33:08 Swapping tables...
RENAME TABLE `h_pushcenter`.`h_message` TO `h_pushcenter`.`_h_message_old`, `h_pushcenter`.`_h_message_new` TO `h_pushcenter`.`h_message`
2021-05-19T10:33:09 Swapped original and new tables OK.
2021-05-19T10:33:09 Dropping old table...
DROP TABLE IF EXISTS `h_pushcenter`.`_h_message_old`
2021-05-19T10:33:09 Dropped old table `h_pushcenter`.`_h_message_old` OK.
Copy the code
  • Delete trigger
2021-05-19T10:33:09 Dropping triggers...
DROP TRIGGER IF EXISTS `h_pushcenter`.`pt_osc_h_pushcenter_h_message_del`
DROP TRIGGER IF EXISTS `h_pushcenter`.`pt_osc_h_pushcenter_h_message_upd`
DROP TRIGGER IF EXISTS `h_pushcenter`.`pt_osc_h_pushcenter_h_message_ins`
2021-05-19T10:33:09 Dropped triggers OK.
Copy the code
  • Finished work

The above is the formal environment big data table structure modification operation, how do you deal with the big table structure modification? Welcome to leave a message to inform, there are questions welcome to leave a message to inform!