【MySql】M-M 架构下的 DDL 一则

        一个应用的表 auto_inrement 字段为int 类型的，当前已经用了完了！需要重建表结构，又因为生产环境是M-M 架构，为了不影响应用对所操作表的访问,先操作一个备库，执行切换，再操作另一个数据库！

注意 使用  set sql_log_bin=0; 防止对另外的master的影响！

大致的步骤如下：

1 连接备库 

  use monitor;

  set sql_log_bin=0;

2 创建临时表

 CREATE TABLE `rrd_value_tmp` (

  `value_id` bigint(20) unsigned  NOT NULL AUTO_INCREMENT,

  `state_id` int(11) NOT NULL,

  `row_no` int(11) NOT NULL,

  `value` double DEFAULT NULL,

  PRIMARY KEY (`value_id`),

  KEY `rrd_val_state_row_ind` (`state_id`,`row_no`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

3 插入数据

insert into rrd_value_tmp(state_id,row_no,value) select state_id,row_no,value from rrd_value; 

4 重命名表

rename table rrd_value to rrd_value_bak;

rename table rrd_value_tmp to rrd_value;

5 切换

#sh aurora.sh status 10.249.238.69

#sh aurora.sh takeover 10.249.238.69

#sh aurora.sh status 10.249.238.69

确定是否已经成功切换到备库 

对新的备库进行操作

1 连接备库 

  use monitor;

  set sql_log_bin=0;

2 创建临时表

 CREATE TABLE `rrd_value_tmp` (

  `value_id` bigint(20) unsigned  NOT NULL AUTO_INCREMENT,

  `state_id` int(11) NOT NULL,

  `row_no` int(11) NOT NULL,

  `value` double DEFAULT NULL,

  PRIMARY KEY (`value_id`),

  KEY `rrd_val_state_row_ind` (`state_id`,`row_no`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

2 插入数据

insert into rrd_value_tmp(state_id,row_no,value) select state_id,row_no,value from rrd_value; 

 

3 重命名表

rename table rrd_value to rrd_value_bak;

rename table rrd_value_tmp to rrd_value;

操作完成，开发验证应用重新可用，所以要将旧表删除,因为涉及的表比较大，采用了 《如何更快的删除大表》这篇文章

实现原理:巧用LINK(硬链接),就是利用OS HARD LINK的原理,当多个文件名同时指向同一个INODE时,这个INODE的引用数N>1, 删除其中任何一个文件名都会很快.

因为其直接的物理文件块没有被删除.只是删除了一个指针而已;当INODE的引用数N=1时, 删除文件需要去把这个文件相关的所有数据块清除,所以会比较耗时;

查看表的大小以及状态

mysql> select (data_length+index_length)/1024/1024/1024  from information_schema.tables where table_name='rrd_value_bak';

+-------------------------------------------+

| (data_length+index_length)/1024/1024/1024 |

+-------------------------------------------+

|                           20.113281250000 |

+-------------------------------------------+

mysql> show table status like 'rrd_value_bak' \G

*************************** 1. row ***************************

           Name: rrd_value_bak

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 324777861

 Avg_row_length: 44

    Data_length: 14360248320

Max_data_length: 0

   Index_length: 7236222976

      Data_free: 5242880

 Auto_increment: 665835601

    Create_time: 2012-04-06 13:16:19

    Update_time: NULL

     Check_time: NULL

      Collation: utf8_general_ci

       Checksum: NULL

 Create_options: 

        Comment: 

1 row in set (0.06 sec)

对此表的数据文件建立物理连接：

root@rac1# ll rrd_value_bak*

-rw-rw---- 1 mysql mysql        8672 Apr  6 13:16 rrd_value_bak.frm

-rw-rw---- 1 mysql mysql         8672 Apr  6 13:16 rrd_value_bak.frm

-rw-rw---- 1 mysql mysql  21906849792 Apr  6 14:53 rrd_value_bak.ibd

root@rac1# ln rrd_value_tmp.ibd rrd_value_bak.ibd.hdlk

建立好之后 node 显示为2！

root@rac1# ll rrd_value_bak*

-rw-rw---- 1 mysql mysql        8672 Apr  6 13:16 rrd_value_bak.frm

-rw-rw---- 2 mysql mysql 21906849792 Apr  6 14:54 rrd_value_bak.ibd

-rw-rw---- 2 mysql mysql 21906849792 Apr  6 14:54 rrd_value_bak.ibd.hdlk

root@rac1# mysql 

mysql> use monitor;

Database changed

mysql> set sql_log_bin=0;

Query OK, 0 rows affected (0.00 sec)

mysql> 

mysql> 

mysql> show table status like 'rrd_value_bak' \G

*************************** 1. row ***************************

           Name: rrd_value_bak

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 312084387

 Avg_row_length: 46

    Data_length: 14360248320

Max_data_length: 0

   Index_length: 7236222976

      Data_free: 5242880

 Auto_increment: 665835601

    Create_time: 2012-04-06 13:16:19

    Update_time: NULL

     Check_time: NULL

      Collation: utf8_general_ci

       Checksum: NULL

 Create_options: 

        Comment: 

1 row in set (0.08 sec)

mysql> drop table rrd_value_bak; <===drop 大表操作！

Query OK, 0 rows affected (19.46 sec) 耗费了19.46秒

mysql> exit

Bye

table删除了table，数据文件依然存在,还需要将数据文件删除：

root@rac1# ll rrd_value_bak*

-rw-rw---- 1 mysql mysql 21906849792 Apr  6 14:57 rrd_value_bak.ibd.hdlk 

root@rac1# rm rrd_value_bak.ibd.hdlk

rm: remove regular file `rrd_value_tmp.ibd.hdlk'? y

root@rac1#

整个删除操作 并没有想参考文章所提及的那样快！可能和机器的配置有关！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22664653/viewspace-720582/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22664653/viewspace-720582/