pt-online-schema-change changes the primary key of the MySQL table-Mysql Tutorial-php.cn

pt-online-schema-change changes the primary key of the MySQL table

高洛峰

Release： 2016-10-29 10:53:23

Original

1908 people have browsed it

After the business has been running for a period of time, I find that the original primary key setting is not reasonable. At this time, I want to change the primary key. This kind of demand is still quite large in actual production.

Now, let’s take a look at how pt-online-schema-change solves this type of problem.

First, create a test table

create table t2(c1 int primary key, c2 int);

Construct test data

delimiter //
create procedure p1()
begin
  declare v1 int default 1;
  set autocommit=0;
  while v1 <=100000 do
    insert into test.t2(c1,c2) values(v1,v1+100);
    set v1=v1+1;
    if v1%1000 =0 then
      commit;
    end if;
  end while;
end //
delimiter ;
call p1;

Copy after login

Next, start using pt-online-schema-change to change the t2 table Change the primary key

1. Add unique key to column c1

# pt-online-schema-change --execute --alter "modify c1 int unique key" --print D=test,t=t2

At this time, the table structure of table t2 is as follows:

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `c1` int(11) NOT NULL DEFAULT &#39;0&#39;,
  `c2` int(11) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  UNIQUE KEY `c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
row in set (0.03 sec)

Copy after login

2. Delete the primary key on column c1

# pt-online-schema-change --execute --alter "drop primary key" --no-check-alter - -print D=test,t=t2

Note: To delete the primary key, you need to add the --no-check-alter option

At this time, the table structure of t2 is as follows:

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `c1` int(11) NOT NULL DEFAULT &#39;0&#39;,
  `c2` int(11) DEFAULT NULL,
  UNIQUE KEY `c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
row in set (0.05 sec)

Copy after login

3. Add the primary key on column c2

# pt-online-schema-change --execute --alter "modify c2 int primary key" --print D=test,t=t2

At this time, the table structure of t2 is as follows:

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `c1` int(11) NOT NULL DEFAULT &#39;0&#39;,
  `c2` int(11) NOT NULL,
  PRIMARY KEY (`c2`),
  UNIQUE KEY `c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
row in set (0.02 sec)

Copy after login

4. Delete column c1 unique key on

# pt-online-schema-change --execute --alter "drop key c1" --print D=test,t=t2

At this time, the primary key change of t2 is completed

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `c1` int(11) NOT NULL DEFAULT &#39;0&#39;,
  `c2` int(11) NOT NULL,
  PRIMARY KEY (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
row in set (0.02 sec)

Copy after login