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

This article mainly explains "the introduction of DDL operation of MySQL daily big table". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the introduction of DDL operation of MySQL daily big table".

Most alter table operations involve the process of lock-- > copy to new table-- > rename-- > unlock, which takes a long time to lock the table, and the process of alter table cannot be kill, and once executed, it cannot be reversed.

In MySQL5.5 and previous versions, it was difficult to perform Alter operations on large tables (over millions of records) in a running production environment. Because the table and lock table will be rebuilt, affecting the use of the user.

Starting with MySQL5.6, the Online DDL feature was introduced. He enhanced many kinds of Alter Table operations to avoid copying and locking tables, allowing select,insert,update,delete statements to be run while running Alter operations. So in the latest version, we can suppress file copying and locking by using the ALGORITHM and LOCK options.

But even in MySQL5.6, there are still some Alter operations (adding / deleting columns, adding / deleting primary keys, changing data types, etc.) that require table reconstruction.

Although mysql5.6 has greatly improved the operation of atler table, there are still many limitations and it is not recommended to use it directly online. If there is a need for DDL online, we recommend the following two ways:

1. Master-slave architecture polling for modification

2. Use the online modification tool online-schema-change

For the first approach, the premise is that your database schema is a cluster, and if not, there is no polling modification. The principle of modification is to use the master-slave service to get the downtime window and modify it without the perception of the application.

Today we focus on the second way, using third-party tools to realize the DDL operation of online large tables. In this way, the table is not locked when the alter operation changes the table structure, that is, the write and read operations are not blocked when the alter is executed.

How it works:

Create an empty table structure that is the same as the table you want to perform alter operation, perform table structure modification, and then copy the original data from the original table to the table after the table structure modification. When the data copy is completed, the original table will be removed and the original table will be replaced with the new table. The default action is to drop the original table. During the copy data process, any update operation in the original table will be updated to the new table, because the tool will create a trigger on the original table, and the trigger will update the contents updated on the original table to the new table. If the trigger is already defined in the table, the tool will not work.

Note:

1. The operating table must have a primary key or unique index or an error will be reported.

2. If the table has a foreign key, the tool will not execute unless you specify a specific value with-- alter-foreign-keys-method.

3. When the volume of business is large, the modification operation will wait for the final rename operation to be performed after no data modification. Therefore, when modifying the table structure, you should try to choose a more appropriate execution when the business is relatively idle, at least when the data operation on the table is relatively low.

4. Because there may be some risks, it is recommended to back up the data table before the operation, which can make the operation more safe and reliable.

5. If you are in the master-slave environment and do not care about the delay of the slave, you need to add the-recursion-method=none parameter. When you want to have as little impact on the service as possible, you need to add the-max-load parameter.

Environment building:

Installation depends on the environment

# # Install DBI

Wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz

Tar-zxvf DBI-1.625.tar.gz

Cd DBI-1.625

Perl Makefile.PL

Make

Make install

# # Install DBD::Mysql

Wget http://search.cpan.org/CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.023.tar.gz

Tar-zxvf DBD-mysql-4.023.tar.gz

Cd DBD-mysql-4.023

Perl Makefile.PL

Make

Make install

Install percona-toolkit:

# # Install percona-toolkit

Wget percona.com/get/percona-toolkit.tar.gz

Tar-zxvf percona-toolkit-2.2.16.tar.gz

Cd percona-toolkit-2.2.16

Perl Makefile.PL

Make

Make install

Common actions:

Add a field

[root@rac1 bin] #. / pt-online-schema-change-uroot-pxxx-- alter='add column col1_test int'-- execute DelimtestMagneTogeThis xxxxroomate

Modify field

[root@rac1 bin] #. / pt-online-schema-change-uroot-pxxx-- alter='MODIFY COLUMN col1_test TINYINT NOT NULL DEFAULT 0'--execute dazztedart tasking xxxroomate

Change the field name

[root@rac1 bin] #. / pt-online-schema-change-uroot-pxxx-- alter='CHANGE COLUMN col1_test address varchar (30)'--execute Dempest _

Delete a field

[root@rac1 bin] #. / pt-online-schema-change-uroot-pxxx-- alter='drop column address'--execute DelimtestReclamation tasking xxxroomate

Add Index

[root@rac1 bin] #. / pt-online-schema-change-uroot-pxxx-- alter='add key indx_test (col1_test)'--execute dazztec.

Delete index

[root@rac1 bin] #. / pt-online-schema-change-uroot-pxxx-- alter='DROP INDEX indx_test'-- execute DelimtestMagneTogeThis xxxxroomate

Thank you for your reading, the above is the content of "introduction to DDL operation of MySQL daily table". After the study of this article, I believe you have a deeper understanding of the introduction of DDL operation of MySQL daily table, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.