fKB6unfi

1#!/bin/bash
2
3# Uses xtrabackup or innobackupex backup directories to restore an entire database.
4# Author: Phil Buescher
5# License: None - I release all rights to this ugly script to whoever wants to use it for any purpose.
6# Requires: Valid xtrabackup/innobackupex restore point, logs applied/prepared.  Suggest having rsync installed.
7#    'mysqlfrm' from mysql utilities
8#    Testing extensively before trying on production (duh)
9#    All tables must be InnoDB - this does NOT handle MyISAM tables and it won't work if any exist in the backup dir.
10#    innodb_file_per_table=1 and innodb_import_table_from_xtrabackup=1
11#
12# Suggest you read up on mysqlfrm and its limitations. Suck to be missing foreign key constaints and all that fun stuff.
13# Running 5.6? See comments starting at 118 and add cfg files to the for loop if you like.
14#
15# Usage: inno_restore_database.sh <db_name_to_restore_to> <db_backup_directory>
16
17# Change the datadir if needed to the mysql datadir.
18datadir=/var/lib/mysql
19
20# Change the ports if needed.  myport is what the current running mysql server listens on.
21# mysqlfrmport is what the temporary server will listen on - just a port nothing else is listening on at the time.
22myport=3306
23mysqlfrmport=3310
24
25# Give username and password for a mysql user with global CREATE, DROP, ALTER - making variables so this can be scripted easier.
26# If you want to be prompted, comment the username and password. Otherwise uncomment and specify them.
27#username=dbrestoredude
28#password=dbrestoredudespasswd
29
30#######################################################################################
31
32# Gotta be root.
33if [ $UID -ne 0 ]; then echo "Run this as root" ; exit ; fi
34
35# check for arguments
36if [ ! $1 ] || [ ! $2 ] ; then
37    echo;echo "This restores one whole database made using innobackupex or xtrabackup,"
38    echo "    but ONLY if ALL tables are InnoDB/XtraDB - any MyISAM tables will break this!"
39    echo "Requires innodb_file_per_table and innodb_import_table_from_xtrabackup set to 1."; echo
40    echo "Syntax:  inno_restore_database.sh: <database> <restore_directory(backupdir)>"
41    echo "Example: inno_restore_database.sh: newdatabase /mnt/backups/1970-01-01_00-00-00/backupdatabase";echo
42    echo "Warning: THIS WILL OVERWRITE THE DATABASE YOU ARE RESTORING TO!"
43    echo "Ensure you set the datadir and mysqluser in the script!"
44    echo;exit
45fi
46
47database=$1
48restoredir=$2
49
50# Check for the mysqlfrm command
51which mysqlfrm > /dev/null 2>&1
52if [ $? -ne 0 ] ; then echo "Could not find the mysqlfrm utility. Install mysql-utilities."; exit ; fi
53
54# Check datadir for mysql install, figure mysql/user.frm should probably exist.
55if [ ! -f $datadir/mysql/user.frm ] ; then echo "MySQL datadir not correct" ; exit ; fi
56
57# Check the restore directory, looking for a cfg/exp/idb for each frm.
58if [ ! -f $restoredir/db.opt ] ; then echo "Restore directory invalid, couldn't find db.opt in it"; exit ; fi
59stoperror=0
60for restorename in $restoredir/*.frm
61do
62    chkname=$(echo $restorename|sed s/.frm$//)
63    for exten in cfg exp ibd
64    do
65        if [ ! -f $chkname.$exten ] ; then stoperror=1 ; fi
66    done
67done
68if [ $stoperror -eq 1 ] ; then
69    echo "Could not file valid restore directory files (need a cfg, exp and ibd for each frm)"
70    echo "Did you specify a valid database directory within a backup?"
71    echo "Did you prepare or apply-log to the backup directory?"
72    exit
73fi
74
75# Get username and password if the fields are blank.
76if [ -z $username ] ; then read -p "Username: " username ; fi
77if [ -z $password ] ; then read -s -p "Password: " password ; echo ; fi
78
79# Check mysql permissions for the given user.
80stoperror=0
81grants=$(mysql -B -u $username -p$password mysql -e "show grants for current_user"|grep 'ON *.* TO')
82if [ $? -ne 0 ] ; then exit ; fi
83if [[ $grants == *"ALL PRIVILEGES"* ]] ; then stoperror=1 ; fi
84if [[ $grants == *CREATE* ]] && [[ $grants == *DROP* ]] && [[ $grants == *ALTER* ]] ; then stoperror=1 ; fi
85if [ $stoperror != 1 ] ; then echo "User $username does not have global CREATE, DROP and ALTER" ; exit ; fi
86
87
88################################################################################
89# Okay, all the user-error logic I can think of is out of the way, finally time to script this.
90
91# drop database if exists
92mysql -B -u $username -p$password -e "DROP DATABASE IF EXISTS $database"
93
94# create database
95mysql -B -u $username -p$password -e "CREATE DATABASE $database"
96
97# get directory for the original DB name
98backupdb=$(find $restoredir -maxdepth 0 -type d -printf "%f\n" |cut -d '/' -f 1)
99
100# Import the table structure, create them all. Gotta massage the data a bit from mysqlfrm.
101echo "Importing create table statements from frm files..." ; echo
102mysqlfrm -q --user=root --server=$username:$password@localhost:$myport --port=$mysqlfrmport $restoredir |
103    grep -vE "^#|WARNING: Using a password on the command line interface can be insecure." |
104    sed s/^$/';'/ | sed s/^'CREATE TABLE `'$backupdb/'CREATE TABLE `'$database/ |
105    mysql -B -u $username -p$password
106echo "Table structure imported."
107
108# for each frm file...
109for frmname in $restoredir/*.frm
110do
111    tablename=$(find $frmname -printf "%f\n"|sed s/.frm$//)
112
113    # ALTER TABLE ... DISCARD TABLESPACE - junks those pesky datafiles we don't want.
114    mysql -B -u $username -p$password $database -e "ALTER TABLE $tablename DISCARD TABLESPACE"
115
116    # Copy each table's cfg/exp/ibd files to datadir and chown them.
117    # Note: I don't need cfg files, you might want to add them for 5.6.
118    # Not including them because mysql won't delete them on drop database.
119    for exten in exp ibd #cfg
120    do
121        # If they have rsync, let's use that to give them progress. I'm sure some of those datafiles are large.
122        which rsync > /dev/null 2>&1
123        if [ $? -eq 0 ]
124        then
125            rsync --progress $restoredir/$tablename.$exten $datadir/$database/$tablename.$exten
126        else
127            # Damn, I wish I knew the backslash trick earlier in my life to unset an alias, that's handy!
128            \cp -v $restoredir/$tablename.$exten $datadir/$database/$tablename.$exten
129        fi
130        chown $(find $datadir/$database/$tablename.frm -printf "%u.%g") $datadir/$database/$tablename.$exten
131    done
132    # ALTER TABLE ... IMPORT TABLESPACE
133    mysql -B -u $username -p$password $database -e "ALTER TABLE $tablename IMPORT TABLESPACE"
134done
135
136echo
137echo "All done.  Hopefully that worked, huh?  No promises."
138echo "You're probably missing foreign keys and such. Better check. Read the mysqlfrm docs."
139echo "Hopefully you imported into a test database first so you can check it, right?"