一個應用的表 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 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> 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#

整個刪除操作並沒有想參考文章所提及的那樣快！可能和機器的配置有關！

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

相關文章