ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【MySql】M-M 架构下的 DDL 一则

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

原创 Linux操作系统 作者:杨奇龙 时间:2012-04-06 17:19:30 0 删除 编辑
        一个应用的表 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7510185