5dUBNJ0U

1#!/bin/bash
2
3## not handling partitioned tables yet
4## not handling non-innodb tables
5## not handling innodb isl
6## not considering replication
7
8## schema must exist on destination, where the existing .ibd data file will be removed
9## one database at a time, though multiple copies of this script on different database is ok
10## tables must be ready via FLUSH TABLES FOR EXPORT or shutting down with innodb_fast_shutdown=0
11## target server must be InnoDB 5.6+
12
13creds=/root/.my.cnf # destination
14destdatadir=/var/lib/mysql/
15destmysqlduser=mysql # for chown
16
17cpormv=cp
18copyfiles=(.ibd .cfg)
19srcdatadir=/var/lib/mysql.old/
20
21database=mgtest
22# innodbtables can be blank to discover tables that exist in destdatadir
23#innodbtables=(t1 t3)
24
25
26### BEGIN
27
28
29## Check that script runs as root
30[ $EUID -eq 0 ] || { echo "$(date) Please su to root user before running $0 - quitting" >&2; exit 1; }
31
32## Set defaults, where possible
33[ -z ${cpormv} ] && cpormv=cp
34[ -z ${copyfiles} ] && copyfiles=(.ibd)
35[ -z ${database} ] && { echo "$(date) database not configured in $0 - quiting" >&2; exit 1; }
36
37## Check that supplied datadirs are directories
38[ -d ${destdatadir} ] || { echo "$(date) destdatadir ${destdatadir} is not a directory  - quiting" >&2; exit 1; }
39[ -d ${srcdatadir} ] || { echo "$(date) srcdatadir ${srcdatadir} is not a directory  - quiting" >&2; exit 1; }
40
41## Create list of tables if not supplied
42[ -z $innodbtables ] && innodbtables=($(mysql --defaults-file=${creds} -ss <<< "select t1.table_name from information_schema.tables t1 join information_schema.partitions t2 on t1.table_name=t2.table_name where t1.table_schema='${database}' and engine='innodb' and t2.partition_name is null"))
43
44
45echo "$(date) Data source ${srcdatadir}"
46echo "$(date) Data destination ${destdatadir}"
47echo "$(date) chown to ${destmysqlduser}"
48echo "$(date) Data will be sent with ${cpormv}"
49echo "$(date) Database ${database}"
50echo
51echo "$(date) innodbtables table list is:"
52echo "${innodbtables[@]}"
53
54read -p "Proceed y/[n]: " proceed
55proceed=${proceed:-n}
56[ ${proceed} = y -o ${proceed} = Y ] || { echo "$(date) User cancelled - quiting" >&2; exit 1; }
57
58## Check whether srcdatadir has open file handles
59fuser "${srcdatadir}" &> /dev/null
60srcdatadir_unused=$?
61
62## Force cp if srcdatadir in use
63if [ ${srcdatadir_unused} -ne 1 -a "${cpormv}" = "mv" ]; then
64 echo "${srcdatadir} in use and cpormv=cp requested, using mv instead"
65 cpormv=cp
66 read -p "Proceed y/[n]: " proceed
67 proceed=${proceed:-n}
68 [ ${proceed} = y -o ${proceed} = Y ] || { echo "$(date) User cancelled - quiting" >&2; exit 1; }
69fi
70
71
72## Loop innodbtables
73for table in ${innodbtables[@]}; do
74 if [ ${srcdatadir_unused} -ne 1 ]; then
75  [ -e  ${srcdatadir}/${database}/${table}.cfg ] || { echo "$(date) ${srcdatadir} in use and no cfg file found for ${database}.${table} - quiting" >&2; exit 1; }
76  echo "$(date) ${srcdatadir}/${database}/${table}.cfg exists, assuming FLUSH FOR EXPORT"
77 fi
78 [ -e ${destdatadir}/${database}/${table}.ibd ] || { echo "$(date) ${destdatadir}/${database}/${table}.ibd does not exist - quitting" >&2; exit 1; }
79 [ -e ${srcdatadir}/${database}/${table}.ibd ] || { echo "$(date) ${srcdatadir}/${database}/${table}.ibd does not exist - quitting" >&2; exit 1; }
80 echo "$(date) mysql --defaults-file=${creds}: alter table $database.$table discard tablespace"
81 mysql --defaults-file="${creds}" -e "set session foreign_key_checks=0; alter table \`$database\`.\`$table\` discard tablespace" || { echo "$(date) discard tablespace failed for ${database}.${table} - quiting" >&2; exit 1; }
82 for extension in "${copyfiles[@]}"; do
83   echo "$(date) ${cpormv} ${srcdatadir}/${database}/${table}${extension} ${destdatadir}/${database}/${table}${extension}"
84   if [ $extension = ".ibd" ]; then
85    ${cpormv} -n ${srcdatadir}/${database}/${table}${extension} ${destdatadir}/${database}/${table}${extension} || { echo "$(date) ${cpormv} ibd failed for ${database}.${table} - quiting" >&2; exit 1; }
86    echo "$(date) chown ${destmysqlduser}. ${destdatadir}/${database}/${table}${extension}"
87    chown ${destmysqlduser}. ${destdatadir}/${database}/${table}${extension} || { echo "$(date) chown ibd to ${destmysqlduser} failed for ${database}.${table} - quiting" >&2; exit 1; }
88   else
89    ${cpormv} -f ${srcdatadir}/${database}/${table}${extension} ${destdatadir}/${database}/${table}${extension} 2> /dev/null || echo "$(date) Ignoring failed ${cpormv} of ${database}/${table}${extension}"
90    echo "$(date) chown ${destmysqlduser}. ${destdatadir}/${database}/${table}${extension}"
91    chown ${destmysqlduser}. ${destdatadir}/${database}/${table}${extension} 2> /dev/null
92   fi
93 done
94 echo "$(date) mysql --defaults-file=${creds}: alter table $database.$table import tablespace"
95 mysql --defaults-file="${creds}" -e "set session foreign_key_checks=0; alter table \`$database\`.\`$table\` import tablespace" || { echo "$(date) discard tablespace failed for ${database}.${table} - quiting" >&2; exit 1; }
96 echo "$(date) COMPLETED: alter table $database.$table import tablespace"
97done