\u5b98\u65b9\u624b\u518c\u4ecb\u7ecd\uff1a<\/p>
http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/tablespace-copying.html<\/p>
http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-transportable-tablespace-examples.html<\/p>
\u5b9e\u9a8c\u73af\u5883:<\/p>
\u963f\u91cc\u4e91\u4e3b\u673a\uff08\u53cc\u68384G\uff0c\u666e\u901a\u4e91\u76d8\uff0c2\u4e2a\u8282\u70b9\u90fd\u662fyum\u5b89\u88c5\u7684mysql-community-server5.6? \uff09<\/p>
db1.table1\u7684\u5927\u5c0f\uff08ibd\u6587\u4ef6\u5927\u5c0f 1.2GB \u5907\u4efd\u51fa\u7684sql\u6587\u4ef6\u5927\u5c0f\u4e3a750MB\uff09<\/p>
test1\u8282\u70b9\u4e0a:<\/strong><\/p> > use db1; test2\u8282\u70b9\u4e0a\uff1a<\/strong><\/p> > use db1; \u4e0a\u9762\u7684\u8fd9\u79cd\u65b9\u6cd5\u5bfc\u6570\u636e\u5168\u90e8\u8fc7\u7a0b\u82b1\u8d39\u4e863\u5206\u949f\u5427\u3002<\/p> \u800c\u540c\u6837\u7684\u6570\u636e\uff0c\u6d4b\u8bd5\u4f7f\u7528sql\u6587\u4ef6\u5bfc\u5165\u7684\u82b1\u8d39\u7684\u65f6\u95f4\uff1a? 10\u5206\u949f\u3002? \u4e8c\u8005\u9ad8\u4e0b\u7acb\u5224\u3002<\/p> \u9644\u540e\u6765\u5199\u7684\u4e00\u4e2a\u4f20\u8f93\u5e76\u5bfc\u5165\u8fdc\u7a0b\u4e3b\u673a\u7684\u811a\u672c\uff1a<\/strong><\/p> #!\/bin\/bash
> FLUSH TABLE table1 FOR EXPORT;
?
# cd \/var\/lib\/mysql\/db1
# scp table1.ibd table1.cfg root@test2:\/root\/? # \u56e0\u4e3a\u662f\u5185\u7f51\u4f20\u8f93\uff0c\u56e0\u6b64\u8fd9\u6b65\u82b1\u8d39\u4e0d\u52301\u5206\u949f\u7684\u65f6\u95f4
?
> UNLOCK tables;<\/p>
> truncate table table1;
> alter table table1 discard tablespace;
?
# cp \/root\/table1.* \/var\/lib\/mysql\/db1\/
# chown mysql.mysql \/var\/lib\/mysql\/db1\/ -R
?
> alter table table1 import tablespace;? # \u8fd9\u4e00\u6b65\u82b1\u8d39\u4e8645s
?
> select count(*) from table1;? \u53ef\u4ee5\u770b\u5230\u6570\u636e\u5168\u90e8\u5bfc\u5165\u4e86<\/p>
# Description: \u7b80\u5355\u7c97\u66b4\u7684\u5c06\u8868\u7a7a\u95f4\u6587\u4ef6\u4f20\u8f93\u5230\u5176\u4ed6\u8282\u70b9,\u5e76\u5b9e\u73b0\u5bfc\u5165\u64cd\u4f5c,\u7ebf\u4e0a\u4f7f\u7528\u51fa\u95ee\u9898\u540e\u679c\u81ea\u884c\u627f\u62c5
# Date: 2017\/01\/08
# Author: Lee
?
LOG=\"\/tmp\/trans.log\"
?
LOCAL_USER='root'
LOCAL_PASS='123456'
?
DATADIR=\"\/var\/lib\/mysql\/\"
MYSQL='\/usr\/bin\/mysql'
?
# step1 \u4f20\u8f93*.ibd *.cfg\u6587\u4ef6\u5230\u5176\u4ed6\u8282\u70b9
# \u53c2\u6570\u8bf4\u660e\uff1a
# $1 \u6570\u636e\u5e93\u540d\u79f0
# $2 \u8868\u540d\u79f0
# $3 \u76ee\u6807\u4e3b\u673a\u7684\u5730\u5740
if [ $# -eq 3 ]; then
? $MYSQL -u$LOCAL_USER -p$LOCAL_PASS -e \"use $1; FLUSH TABLE $2 FOR EXPORT; system scp $DATADIR$1\/$2.ibd $3:\/root; system scp $DATADIR$1\/$2.cfg $3:\/root; UNLOCK TABLES;\" > \/dev\/null 2>&1
else
? echo -e \"\\n\\033[31mUsage: sh $0 \u6570\u636e\u5e93\u540d \u8868\u540d \u8fdc\u7a0b\u4e3b\u673aIP\\033[0m\\n\" && exit 1
fi
?
if [ $? -eq 0 ]; then
? echo -e \"\\033[32m \u4f20\u8f93\u5230\u8fdc\u7a0b\u4e3b\u673a\u6210\u529f? $(date +\"%F %T\") \\033[0m\" | tee -a $LOG
else
? echo -e \"\\033[32m \u4f20\u8f93\u5230\u8fdc\u7a0b\u4e3b\u673a\u5931\u8d25? $(date +\"%F %T\") \\033[0m\" | tee -a $LOG && exit 10
fi
?
# step2 ssh \u8fde\u63a5\u5230\u8fdc\u7a0b\u8282\u70b9\uff0c\u5c06\u62f7\u8d1d\u7684\u6570\u636e\u5bfc\u5165\u6570\u636e\u5e93
REMOTE_USER=\"root\"
REMOTE_PASS=\"123456\"
MYSQL='\/usr\/bin\/mysql'
?
ssh root@$3 \"$MYSQL -u$REMOTE_USER -p$REMOTE_PASS -e 'truncate table $1.$2; alter table $1.$2 discard tablespace;system cp \/root\/$2.* \/var\/lib\/mysql\/$1\/;system chown mysql.mysql -R \/var\/lib\/mysql;alter table $1.$2 import tablespace;'\" > \/dev\/null 2>&1
?
if [ $? -eq 0 ]; then
? echo -e \"\\033[32m \u5bfc\u5165\u5230\u8fdc\u7a0b\u4e3b\u673a\u6210\u529f? $(date +\"%F %T\") \\033[0m\" | tee -a $LOG
else
? echo -e \"\\033[32m \u5bfc\u5165\u5230\u8fdc\u7a0b\u4e3b\u673a\u5931\u8d25? $(date +\"%F %T\") \\033[0m\" | tee -a $LOG
fi<\/p>