Data Migration to AWS RDS - 資料庫遷移總結

前言：

這篇主要記錄下遷移資料到RDS中的一些問題和解決方法，希望幫助後面的小夥伴提高效率。

Tips:

1、明确RDS雖然有副本，但恢複需要重建db_instance，這意味着要改URL，影響應用配置；

2、事先要确定好存儲空間是否夠用，避免導入一半遇到存儲問題；

3、導入的兩種方法，"mysql -e << a.sql" 和 “ source a.sql"兩種方式 ，兩種有些差别，遇錯中斷不中斷啥的，注意一下；

4、對于innodb，“select sum(table_rows) from tables where TABLE_SCHEMA='xsydb3'” 得到的是一個近似值，下面會分享一個按表統計記錄數的腳本；

5、重命名database是件很麻煩的事； 

6、中國區暫不支援SSE；

腳本1：db_count.sh

db=$1

com="mysql -hXXXX.XXXX.rds.cn-north-1.amazonaws.com.cn -uMMM -p $db "

sum=0

array=(`$com -e "show tables;"|grep -v Tables_in|awk -F "|" '{printf $1 " "}'`)

for i in "${array[@]}" 

do

    c=`$com -e "select count(*) from $i"|grep  -o '[0-9]\+'`

    sum=`expr $sum + $c`

    echo $i: $c

done

echo "total num of $db: $sum"

腳本2：db_import.sh

#!/bin/sh

#ALTER DATABASE  database_name CHARACTER SET utf8 COLLATE utf8_general_ci;

  db=$1

  dbfile=$2

  imeron=`date`

  dumpfile="/mnt/data/info/$dbfile"

  ddl="set names utf8; "

  #ddl="$ddl set global net_buffer_length=1000000;"

  #ddl="$ddl set global max_allowed_packet=1000000000; "

  ddl="$ddl SET foreign_key_checks = 0; "

  ddl="$ddl SET UNIQUE_CHECKS = 0; "

  ddl="$ddl SET AUTOCOMMIT = 0; "

  ddl="$ddl CREATE DATABASE IF NOT EXISTS $db; "

  ddl="$ddl ALTER DATABASE $db CHARACTER SET utf8 COLLATE utf8_general_ci; "

  ddl="$ddl USE $db; "

  ddl="$ddl source $dumpfile; "

  ddl="$ddl SET foreign_key_checks = 1; "

  ddl="$ddl SET UNIQUE_CHECKS = 1; "

  ddl="$ddl SET AUTOCOMMIT = 1; "

  ddl="$ddl COMMIT ; "

  echo "====$db=== Import started"

  mysql -hXXXXX.XXXX.rds.cn-north-1.amazonaws.com.cn -uUSER -p -e "$ddl"  

  # store end date to a variable

  imeron2=`date`

  echo "====$db=== Start import:$imeron"

  echo "====$db=== End import:$imeron2"

  com="mysql -hXXXXX.XXXX.rds.cn-north-1.amazonaws.com.cn -uUSER -p  $db "

  sum=0

  array=(`$com -e "show tables;"|grep -v Tables_in|awk -F "|" '{printf $1 " "}'`)

  for i in "${array[@]}" 

  do

      c=`$com -e "select count(*) from $i"|grep  -o '[0-9]\+'`

      sum=`expr $sum + $c`

      echo $i: $c

  done

  echo "total num of $db: $sum"

腳本3：db_rename.sh

#!/bin/bash

# Rename the database in RDS

# example: sh .r sales_db_sales sales_db_sales_001

db1=$1

db2=$2

com="mysql -hXXXXX.XXXX.rds.cn-north-1.amazonaws.com.cn -uUSER -p "

$com -e "CREATE DATABASE IF NOT EXISTS $db2 " 

array=(`$com $db1 -e "show tables;"|grep -v Tables_in|awk -F "|" '{printf $1 " "}'`) 

for table in "${array[@]}" 

do

    $com -e "RENAME TABLE \`$db1\`.\`$table\` to \`$db2\`.\`$table\`"

done

******* 愛分享 ******