【MySql】M-M架構下的DDL一則

北在南方發表於2016-04-13
MySql架構
        一個應用的表 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#
整個刪除操作 並沒有想參考文章所提及的那樣快！可能和機器的配置有關！


相關文章