Merge lp:~alikrubin75/undrop-for-innodb/undrop-for-innodb into lp:undrop-for-innodb

[Alexander Rubin]

I would suggest adding my script recover.sh to the main branch after fixing https://bugs.launchpad.net/undrop-for-innodb/+bug/1339358

[lp:~alikrubin75/undrop-for-innodb/undrop-for-innodb]

18. By root <root@mysql-recovery> on 2014-09-23

New version of recover.sh, merged to v.23

[Aleksandr Kuzminsky (akuzminsky)]

See inline.
In principle, it's possible a deleted record is still a member of the records list in a page. If so, then the record will be recovered.
Sometimes InnoDB excludes deleted records form the list. I don't know what it depends on, just have seen that before.
So, if check_page() returns OK and the deleted record isn't part of the list it will be silently ignored. If check_page() returns Not OK then c_parser will produce junk records which can be filtered out with c_parser filters. This is better than silently ignoring the deleted records.

review: Needs Fixing

Unmerged revisions

18. By root <root@mysql-recovery> on 2014-09-23

New version of recover.sh, merged to v.23

17. By EC2 Default User <ec2-user@ip-10-164-164-213> on 2014-07-14

Merged from trunk, added test_mysql_connection to recover.sh

16. By EC2 Default User <ec2-user@ip-10-164-164-213> on 2014-07-09

Added recover.sh - simple script to recover the tables.
Modified c_parser.c for fixing https://bugs.launchpad.net/undrop-for-innodb/+bug/1339356 and https://bugs.launchpad.net/undrop-for-innodb/+bug/1339358

=== modified file 'c_parser.c'
--- c_parser.c	2014-07-17 22:31:04 +0000
+++ c_parser.c	2014-09-23 20:59:25 +0000
@@ -513,7 +513,7 @@
 
 	if(deleted_records_only == 1){
 		if (debug) printf("We look for deleted records only. Consider all pages are not valid\n");
-		return 0;
+		// return 0;
 		}
     if (debug) printf("Checking a page\nInfimum offset: 0x%X\nSupremum offset: 0x%X\n", i, s);
     p_prev = 0;

=== added file 'recover.sh'
--- recover.sh	1970-01-01 00:00:00 +0000
+++ recover.sh	2014-09-23 20:59:25 +0000
@@ -0,0 +1,361 @@
+#!/bin/bash
+
+set -e
+mysql_user="`whoami`"
+#mysql_password=""
+#mysql_host="localhost"
+MYSQL="mysql "
+mysql_db=""
+work_dir="/tmp/recovery_$RANDOM"
+top_dir="`pwd`"
+tables=""
+mysql_tmp_socket="/tmp/mysqldr-$RANDOM.sock" 
+VERBOSE=0
+USEFRM=0
+# Defaults
+Row_format_arg="-5"
+innodb_file_per_table="ON"
+innodb_data_file_path='ibdata1'
+### Functions
+
+function usage() {
+	printf "This tool assumes that a MySQL instance is running. This instance will be used to load the recovered database";
+	printf "Valid options are \n";
+	printf " \t -i mysql data d<i>r name; if omited the tool assume you want to recover from the running mysql\n"
+	printf " \t -d database name to extract \n"
+	printf " \t -t table_name(s), list table with 'table1 table2 tableN' \n"
+	printf " \t -v 1 verbose mode  \n"
+	printf " \t -D recover deleted rows  \n"
+	printf " \t -f 1 will use FRMREADER utility to parse FRM files instead of mysqldump. (EXPERIMENTAL)  \n"
+	printf "recover.sh -v 1 -d sakila -t 'film payment' -D" 
+	printf "\n"
+}
+
+function check_mysql_connection {
+	if [ "`$MYSQL -NB -e 'select 1'`" != "1" ] 
+	then
+		echo "Can't connect to local mysql. Please add connection information to ~/.my.cnf"
+		echo "Example: "
+		echo "[client]"
+		echo "user=percona"
+		echo "password=s3cret"
+		echo ""
+		#on_error
+		exit 1
+	fi
+	$MYSQL -e "SELECT COUNT(*) FROM user" mysql >/dev/null
+	has_innodb=`$MYSQL -e "SHOW ENGINES"| grep InnoDB| grep -e "YES" -e "DEFAULT"`
+	if test -z "$has_innodb"
+	then
+	        echo "InnoDB is not enabled on this MySQL server"
+		#on_error
+	        exit 1
+	fi
+}
+
+function start_temp_mysql {
+        mysql_temp_datadir="`mktemp -d`"
+        mysql_start_timeout="300"
+        #mysql_user="nobody"
+
+        mkdir -p "$mysql_temp_datadir"
+        #chown -R $mysql_user "$mysql_temp_datadir"
+        mysqld --no-defaults --sql-mode=''  --default-storage-engine=MyISAM  --default-tmp-storage-engine=MyISAM --skip-innodb --datadir="$mysql_temp_datadir" --socket="$mysql_tmp_socket" --user=$mysql_user --skip-networking --skip-grant-tables 1>/dev/null 2>/dev/null &
+
+        while [ "`mysql -NB --socket=\"$mysql_tmp_socket\" -e 'select 1' 2>/dev/null`" != "1" ]
+        do
+                echo "Waiting till aux instance of MySQL starts"
+                sleep 1
+                mysql_start_timeout=$(($mysql_start_timeout - 1))
+                if [ $mysql_start_timeout -eq 0 ]; then echo "Can't start aux instance of MySQL. Exiting..."; exit ; fi
+        done
+}
+
+function shutdown_temp_mysql {
+	echo "Shutting down temp MySQL..."
+	mysqladmin --no-defaults -S $mysql_tmp_socket shutdown
+}
+
+function on_error {
+        if [ $? -ne 0 ] 
+	then
+		echo ""
+        	echo "Error occured!"
+        	tail $work_dir/make.log
+        	#tail $mysql_temp_datadir/error.log 
+		#rm -vr $work_dir; rm -vr $mysql_temp_datadir;
+		echo "Exiting..."
+		#shutdown_temp_mysql
+        	#rm -vr $mysql_temp_datadir;
+	fi
+        shutdown_temp_mysql
+	rm -vr $mysql_temp_datadir;	
+}
+
+function init() {
+	# Check that the script is run from source directory
+	if ! test -f "$top_dir/stream_parser.c"
+	then
+	        echo "Script $0 must be run from a directory with TwinDB InnoDB Recovery Tool source code"
+	        exit 1
+	fi
+
+	echo -n "Testing MySQL connection... "
+	check_mysql_connection
+	echo "OK"
+	
+	echo -n "Initializing working directory... "
+	if test -d "$work_dir"
+	then
+	        echo "Directory $work_dir must not exist. Remove it and restart $0"
+	        exit 1
+	fi
+
+	mkdir "$work_dir"
+	echo "Workdir=$work_dir"
+	cd "$work_dir"
+	#trap "if [ $? -ne 0 ] ; then rm -r \"$work_dir\"; fi" EXIT
+	echo "OK"
+
+	echo -n "Creating recovery database... "
+	$MYSQL -e "CREATE DATABASE IF NOT EXISTS ${mysql_db}_recovered"
+	$MYSQL_TMP -e "CREATE DATABASE IF NOT EXISTS ${mysql_db}_recovered"
+
+	echo -n "Building InnoDB parsers... "
+	cd "$top_dir"
+	make  > "$work_dir/make.log" 2>&1
+	cd "$work_dir"
+	echo "OK"
+
+	# Get datadir
+	echo "mdd=$mdd"
+	if [ "$mdd" == "" ]
+	then
+		echo "Checking running mysql server for datadir..."
+		datadir="`$MYSQL  -e "SHOW VARIABLES LIKE 'datadir'" -NB | awk '{ $1 = ""; print $0}'| sed 's/^ //'`"
+		innodb_file_per_table=`$MYSQL  -e "SHOW VARIABLES LIKE 'innodb_file_per_table'" -NB | awk '{ print $2}'`
+		innodb_data_file_path=`$MYSQL  -e "SHOW VARIABLES LIKE 'innodb_data_file_path'" -NB | awk '{ $1 = ""; print $0}'| sed 's/^ //'`
+	else
+		echo "Using $mdd as a datadir..."
+		datadir=$mdd
+		#innodb_file_per_table="ON"
+		#innodb_data_file_path=''
+	fi
+	if ! test -d "$datadir" 
+	then
+		echo "$datadir does not exists! exiting..."
+		exit 1
+	else
+		echo "Using datadir = $datadir"
+		echo "InnoDB params: $innodb_file_per_table, $innodb_data_file_path"
+	fi
+	
+}
+
+function get_row_format() {
+	# get row format
+        Row_format=`$MYSQL -NB -e "SHOW TABLE STATUS LIKE '$t'" ${mysql_db}| awk '{print $4}'`
+        is_56=`$MYSQL -NB -e "select @@version"| grep 5\.6`
+        if [ "$Row_format" == "Compact" ]
+        then
+                Row_format_arg="-5"
+        if ! test -z "$is_56"; then Row_format_arg="-6"; fi
+        else
+                Row_format_arg="-4"
+        fi
+	echo "... row format arg: $Row_format_arg"
+}
+
+function recover_innodb_dict() {
+	echo "Using datadir = $datadir"
+	echo "Splitting InnoDB tablespace into pages... "
+	old_IFS="$IFS"
+	IFS=";"
+	for ibdata in $innodb_data_file_path
+	do
+	        ibdata_file=`echo $ibdata| awk -F: '{print $1}'`
+	        "$top_dir"/stream_parser -f "$datadir/$ibdata_file"
+	done
+	IFS=$old_IFS
+	if [ $innodb_file_per_table == "ON" ] || [ $innodb_file_per_table == "1" ]
+	then
+		if [ "$tables" == "" ]
+		then
+			tables=`$MYSQL -NB -e "SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA='$mysql_db' and ENGINE='InnoDB'" information_schema`
+		fi
+		echo "--- Recovering tables $tables in $mysql_db"
+	        for t in $tables
+	        do
+	                "$top_dir"/stream_parser -f "$datadir/$mysql_db/$t.ibd"
+	        done
+	fi
+	echo "OK"
+
+	echo -n "Actual recovering of InnoDB dictionary... "
+	old_IFS="$IFS"
+	IFS=";"
+	for ibdata in $innodb_data_file_path
+	do
+	        ibdata_file=`echo $ibdata| awk -F: '{print $1}'`
+	        dir="pages-$ibdata_file"/FIL_PAGE_INDEX/`printf "%016d" 1`.page
+	        mkdir -p "dumps/${mysql_db}_recovered"
+	        if test -f "$dir"
+	        then
+	                "$top_dir"/c_parser -4Uf "$dir" -p "${mysql_db}_recovered" \
+	            -t "$top_dir"/dictionary/SYS_TABLES.sql \
+	            >> "dumps/${mysql_db}_recovered/SYS_TABLES" \
+	            2>SYS_TABLES.sql
+	        fi
+	        dir="pages-$ibdata_file"/FIL_PAGE_INDEX/`printf "%016d" 3`.page
+	        if test -f "$dir"
+	        then
+	                "$top_dir"/c_parser -4Uf "$dir" -p "${mysql_db}_recovered" \
+	        -t "$top_dir"/dictionary/SYS_INDEXES.sql \
+	        >> "dumps/${mysql_db}_recovered/SYS_INDEXES" \
+	        2>SYS_INDEXES.sql
+	        fi
+	done
+	IFS=$old_IFS
+
+	$MYSQL_TMP $mydebug -e "DROP TABLE IF EXISTS SYS_TABLES" ${mysql_db}_recovered
+	$MYSQL_TMP $mydebug -e "DROP TABLE IF EXISTS SYS_INDEXES" ${mysql_db}_recovered
+	# load structure
+	$MYSQL_TMP $mydebug ${mysql_db}_recovered < "$top_dir"/dictionary/SYS_TABLES.sql
+	$MYSQL_TMP $mydebug ${mysql_db}_recovered < "$top_dir"/dictionary/SYS_INDEXES.sql
+	# load data
+	$MYSQL_TMP $mydebug ${mysql_db}_recovered < SYS_INDEXES.sql
+	$MYSQL_TMP $mydebug ${mysql_db}_recovered < SYS_TABLES.sql
+	echo "OK"
+}
+
+function recover_tables() {
+	echo -n "Recovering tables... "
+	for t in $tables
+	do
+	        # Create table structure
+	        #echo "pages-$t.ibd/$t.sql"
+	        if [ $USEFRM -eq 0 ]
+	        then
+	                mysqldump --skip-triggers --skip-add-drop-table --no-data ${mysql_db} $t > pages-$t.ibd/$t.sql
+	        else
+	                echo "Running mysqlfrm --diagnostic '$datadir/$mysql_db/$t.frm' ... "
+	                $MYSQL_TMP -e "create database if not exists ${mysql_db}"
+	                mysqlfrm -v --diagnostic $datadir/$mysql_db/$t.frm | egrep -v '^#' | $MYSQL_TMP # > pages-$t.ibd/$t.sql
+	                mysqldump  -S $mysql_tmp_socket --skip-triggers --skip-add-drop-table --no-data ${mysql_db} $t > pages-$t.ibd/$t.sql
+	        fi
+	        if [ "$i" == "" ]
+        	then
+			get_row_format
+		else
+			Row_format_arg="-5"
+		fi
+	        # Get index id
+	        index_id=`$MYSQL_TMP -NB -e "SELECT SYS_INDEXES.ID FROM SYS_INDEXES JOIN SYS_TABLES ON SYS_INDEXES.TABLE_ID = SYS_TABLES.ID WHERE SYS_TABLES.NAME= '${mysql_db}/$t' ORDER BY ID LIMIT 1" ${mysql_db}_recovered`
+	        echo "index_id=$index_id"
+	        if [ $innodb_file_per_table == "ON" ] || [ $innodb_file_per_table == "1" ]
+		then
+	                #"$top_dir"/c_parser $Row_format_arg -f "$datadir/$mysql_db/$t.ibd" \
+			set -x
+	                "$top_dir"/c_parser $Row_format_arg -f "pages-$t.ibd/FIL_PAGE_INDEX/`printf '%016u' $index_id`.page" \
+	                -p "${mysql_db}_recovered" \
+	                -t "pages-$t.ibd/$t.sql" $UNDEL \
+	                > "dumps/${mysql_db}_recovered/$t" 2> $t.sql
+			set +x
+	        else
+	                old_IFS="$IFS"
+	                IFS=";"
+	                for ibdata in $innodb_data_file_path
+	                do
+	                        ibdata_file=`echo $ibdata| awk -F: '{print $1}'`
+	                        dir="pages-$ibdata_file"/FIL_PAGE_INDEX/0-$index_id
+	                        if test -d "$dir"
+	                        then
+	                                "$top_dir"/c_parser $Row_format_arg -f "$dir" -p "${mysql_db}_recovered" -b "pages-$ibdata_file/FIL_PAGE_TYPE_BLOB" -t "pages-$t.ibd/$t.sql" $UNDEL >> "dumps/${mysql_db}_r
+	ecovered/$t" 2> $t.sql  
+	                        fi
+
+	                done
+	                IFS="$old_IFS"
+	        fi
+	done
+	echo "OK"
+}
+
+function load_to_mysql() {
+	echo -n "Loading recovered data into MySQL... "
+	for t in $tables
+	do
+    	    $MYSQL $mydebug -e "DROP TABLE IF EXISTS \`$t\`;CREATE TABLE $t like ${mysql_db}.$t" ${mysql_db}_recovered
+  	    $MYSQL -vvv ${mysql_db}_recovered < $t.sql > $t.load.log 2>&1
+	    grep -A 4 'LOAD DATA' $t.load.log
+	done
+	echo "OK"
+}
+### Main
+
+if [ $# -lt 2 ] ; then
+        echo 'Too few arguments supplied'
+        usage
+        exit 1
+fi
+
+while getopts ":v:i:d:t:D:f" opt; do
+  case $opt in
+    v)
+       VERBOSE=1
+       ;;
+    i)
+       mdd=$OPTARG
+       ;;
+    d)
+       mysql_db=$OPTARG
+       if [ $VERBOSE -eq 1  ] ; then
+           echo "Database: $mysql_db"
+       fi
+       ;;
+    t)
+       tables=$OPTARG
+       if [ $VERBOSE -eq 1  ] ; then
+           echo "TABLES: $tables"
+       fi
+       ;;
+    D)
+       UNDEL=" -D "
+	echo "!!!UNDEL flag is set!!!"
+       if [ $VERBOSE -eq 1  ] ; then
+           echo "UNDEL flag is set"
+       fi
+       ;;
+    f)
+       USEFRM=1
+       if [ $VERBOSE -eq 1  ] ; then
+           echo "Will use FRMREADER utility to parse FRM files instead of mysqldump. (EXPERIMENTAL)"
+	fi
+       ;;
+  esac
+done
+
+echo "$v $mdd $mysql_db $tables"
+
+#check_mysql_connection
+
+trap on_error QUIT EXIT INT TERM ERR
+
+start_temp_mysql
+echo $mysql_tmp_socket
+MYSQL_TMP="mysql --no-defaults -S $mysql_tmp_socket "
+if [ $VERBOSE -eq 1  ] ; then
+   mydebug=" -vvv "
+fi
+
+init
+
+#echo "Starting recovering InnoDB system dictionary..."
+recover_innodb_dict
+
+recover_tables
+load_to_mysql
+cd "$top_dir"
+
+echo "Temp data is saved to $work_dir"
+echo "All DONE."