MySQL归档脚本

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

#!/bin/bash
export PATH
source /etc/profile

# set -e
BACKUP_DIR="/nas/MySQLData/finance"
DATA_DIR="/nas/mysql_data"
START_CMD="systemctl start mysqld"
STOP_CMD="systemctl stop mysqld"
STATUS_CMD="systemctl status mysqld"
# Password and user in /etc/my.cnf


function log() {
  log_time=$(date "+%Y-%m-%d %H:%M:%S")
  echo "${log_time} $1"
}


function create_table() {
  table_name=$1
  if [ -z ${table_name} ];then
    log "table_name is null, exit"
    exit 1
  fi

  template_name=$(echo ${table_name} | awk -F "_2" '{print $1}')
  _date=$(echo ${table_name} |awk -F "${template_name}_" '{print $2}')
  year=$(echo ${_date:0:4})
  database_name="finance_${year}"

  log "template_name: ${template_name}, table_name: ${table_name} database_name: ${database_name}"
  
  ${START_CMD}
  mysql -e "SHOW DATABASES;" | grep ${database_name} > /dev/null 2>&1
  result=$(echo $?)

  if [ ${result} -ne 0 ];then
    log "CREATE DATABASE ${database_name} DEFAULT CHARACTER SET utf8mb4;"
    mysql -e "CREATE DATABASE ${database_name} DEFAULT CHARACTER SET utf8mb4;"
  fi
  
  log "USE ${database_name};CREATE TABLE ${table_name} LIKE finance_2019.${template_name};"
  mysql -e "USE ${database_name};CREATE TABLE ${table_name} LIKE ${template_name}" \
  && sleep 10 && mysql -e "USE ${database_name};ALTER TABLE ${table_name} DISCARD TABLESPACE;"
}


function main() {
  keyword=$1
  echo "keyword is ${keyword}"
  if [ -z ${keyword} ];then
    files=$(ls -trl ${BACKUP_DIR} | grep -P "\d+\.ibd$" | awk '{print $NF}')
  else
    files=$(ls -trl ${BACKUP_DIR} | grep -P "\d+\.ibd$" |grep ${keyword} | awk '{print $NF}')
  fi
  for file in ${files};do
    table_name=$(echo ${file} | awk -F ".ibd" '{print $1}')

    create_table ${table_name}
    result=$(echo $?)
    if [ ${result} -ne 0 ]; then
        log "create table ${table_name} failed"
        continue
    fi

    sleep 10

    log "Stop MySQL Service"
    ${STOP_CMD}
    _date=$(echo ${table_name} |awk -F "${template_name}_" '{print $2}')
    year=$(echo ${_date:0:4})
    database_name="sixunfinance_${year}"
    log "mv datafile ${BACKUP_DIR}/${file} to ${DATA_DIR}/${database_name}/${file}"
    mv ${BACKUP_DIR}/${file} ${DATA_DIR}/${database_name}/${file}
    log "Start MySQL Service"
    ${START_CMD}

    # Import Tablespace
    log "USE ${database_name};ALTER TABLE ${table_name} IMPORT TABLESPACE;" 
    mysql -e "USE ${database_name};ALTER TABLE ${table_name} IMPORT TABLESPACE;" 
    result=$(echo $?)
    if [ ${result} -ne 0 ]; then
      log "import table space ${table_name} failed, mv ibd file ${file} to ${BACKUP_DIR}"
      mv ${DATA_DIR}/${database_name}/${file} ${BACKUP_DIR}/${file}
      mysql -e "USE ${database_name};DROP TABLE ${table_name};"
    else
      echo "$(date '+%Y%m%d %H:%M:%S') ${database_name}/${table_name} ok" >> log.txt
    fi
  done
}

main $1

1

nohup ./migration.sh 2019&