undrop-for-innodb

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

  1. undrop-for-innodb
  2. Merge into trunk
Proposed by Alexander Rubin
Status: Needs review
Proposed branch: lp:~alikrubin75/undrop-for-innodb/undrop-for-innodb
Merge into: lp:undrop-for-innodb
Diff against target: 378 lines (+362/-1)
2 files modified
c_parser.c (+1/-1)
recover.sh (+361/-0)
To merge this branch: bzr merge lp:~alikrubin75/undrop-for-innodb/undrop-for-innodb
Reviewer Review Type Date Requested Status
Aleksandr Kuzminsky Needs Fixing
Review via email: mp+226624@code.launchpad.net

Description of the change

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

To post a comment you must log in.
lp:~alikrubin75/undrop-for-innodb/undrop-for-innodb updated
18. By root <root@mysql-recovery>

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

Revision history for this message
Aleksandr Kuzminsky (akuzminsky) wrote :

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
Reply

Unmerged revisions

18. By root <root@mysql-recovery>

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

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

Merged from trunk, added test_mysql_connection to recover.sh

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

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

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
=== 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 @@
513513
514 if(deleted_records_only == 1){514 if(deleted_records_only == 1){
515 if (debug) printf("We look for deleted records only. Consider all pages are not valid\n");515 if (debug) printf("We look for deleted records only. Consider all pages are not valid\n");
516 return 0;516 // return 0;
517 }517 }
518 if (debug) printf("Checking a page\nInfimum offset: 0x%X\nSupremum offset: 0x%X\n", i, s);518 if (debug) printf("Checking a page\nInfimum offset: 0x%X\nSupremum offset: 0x%X\n", i, s);
519 p_prev = 0;519 p_prev = 0;
520520
=== 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 @@
1#!/bin/bash
2
3set -e
4mysql_user="`whoami`"
5#mysql_password=""
6#mysql_host="localhost"
7MYSQL="mysql "
8mysql_db=""
9work_dir="/tmp/recovery_$RANDOM"
10top_dir="`pwd`"
11tables=""
12mysql_tmp_socket="/tmp/mysqldr-$RANDOM.sock"
13VERBOSE=0
14USEFRM=0
15# Defaults
16Row_format_arg="-5"
17innodb_file_per_table="ON"
18innodb_data_file_path='ibdata1'
19### Functions
20
21function usage() {
22 printf "This tool assumes that a MySQL instance is running. This instance will be used to load the recovered database";
23 printf "Valid options are \n";
24 printf " \t -i mysql data d<i>r name; if omited the tool assume you want to recover from the running mysql\n"
25 printf " \t -d database name to extract \n"
26 printf " \t -t table_name(s), list table with 'table1 table2 tableN' \n"
27 printf " \t -v 1 verbose mode \n"
28 printf " \t -D recover deleted rows \n"
29 printf " \t -f 1 will use FRMREADER utility to parse FRM files instead of mysqldump. (EXPERIMENTAL) \n"
30 printf "recover.sh -v 1 -d sakila -t 'film payment' -D"
31 printf "\n"
32}
33
34function check_mysql_connection {
35 if [ "`$MYSQL -NB -e 'select 1'`" != "1" ]
36 then
37 echo "Can't connect to local mysql. Please add connection information to ~/.my.cnf"
38 echo "Example: "
39 echo "[client]"
40 echo "user=percona"
41 echo "password=s3cret"
42 echo ""
43 #on_error
44 exit 1
45 fi
46 $MYSQL -e "SELECT COUNT(*) FROM user" mysql >/dev/null
47 has_innodb=`$MYSQL -e "SHOW ENGINES"| grep InnoDB| grep -e "YES" -e "DEFAULT"`
48 if test -z "$has_innodb"
49 then
50 echo "InnoDB is not enabled on this MySQL server"
51 #on_error
52 exit 1
53 fi
54}
55
56function start_temp_mysql {
57 mysql_temp_datadir="`mktemp -d`"
58 mysql_start_timeout="300"
59 #mysql_user="nobody"
60
61 mkdir -p "$mysql_temp_datadir"
62 #chown -R $mysql_user "$mysql_temp_datadir"
63 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 &
64
65 while [ "`mysql -NB --socket=\"$mysql_tmp_socket\" -e 'select 1' 2>/dev/null`" != "1" ]
66 do
67 echo "Waiting till aux instance of MySQL starts"
68 sleep 1
69 mysql_start_timeout=$(($mysql_start_timeout - 1))
70 if [ $mysql_start_timeout -eq 0 ]; then echo "Can't start aux instance of MySQL. Exiting..."; exit ; fi
71 done
72}
73
74function shutdown_temp_mysql {
75 echo "Shutting down temp MySQL..."
76 mysqladmin --no-defaults -S $mysql_tmp_socket shutdown
77}
78
79function on_error {
80 if [ $? -ne 0 ]
81 then
82 echo ""
83 echo "Error occured!"
84 tail $work_dir/make.log
85 #tail $mysql_temp_datadir/error.log
86 #rm -vr $work_dir; rm -vr $mysql_temp_datadir;
87 echo "Exiting..."
88 #shutdown_temp_mysql
89 #rm -vr $mysql_temp_datadir;
90 fi
91 shutdown_temp_mysql
92 rm -vr $mysql_temp_datadir;
93}
94
95function init() {
96 # Check that the script is run from source directory
97 if ! test -f "$top_dir/stream_parser.c"
98 then
99 echo "Script $0 must be run from a directory with TwinDB InnoDB Recovery Tool source code"
100 exit 1
101 fi
102
103 echo -n "Testing MySQL connection... "
104 check_mysql_connection
105 echo "OK"
106
107 echo -n "Initializing working directory... "
108 if test -d "$work_dir"
109 then
110 echo "Directory $work_dir must not exist. Remove it and restart $0"
111 exit 1
112 fi
113
114 mkdir "$work_dir"
115 echo "Workdir=$work_dir"
116 cd "$work_dir"
117 #trap "if [ $? -ne 0 ] ; then rm -r \"$work_dir\"; fi" EXIT
118 echo "OK"
119
120 echo -n "Creating recovery database... "
121 $MYSQL -e "CREATE DATABASE IF NOT EXISTS ${mysql_db}_recovered"
122 $MYSQL_TMP -e "CREATE DATABASE IF NOT EXISTS ${mysql_db}_recovered"
123
124 echo -n "Building InnoDB parsers... "
125 cd "$top_dir"
126 make > "$work_dir/make.log" 2>&1
127 cd "$work_dir"
128 echo "OK"
129
130 # Get datadir
131 echo "mdd=$mdd"
132 if [ "$mdd" == "" ]
133 then
134 echo "Checking running mysql server for datadir..."
135 datadir="`$MYSQL -e "SHOW VARIABLES LIKE 'datadir'" -NB | awk '{ $1 = ""; print $0}'| sed 's/^ //'`"
136 innodb_file_per_table=`$MYSQL -e "SHOW VARIABLES LIKE 'innodb_file_per_table'" -NB | awk '{ print $2}'`
137 innodb_data_file_path=`$MYSQL -e "SHOW VARIABLES LIKE 'innodb_data_file_path'" -NB | awk '{ $1 = ""; print $0}'| sed 's/^ //'`
138 else
139 echo "Using $mdd as a datadir..."
140 datadir=$mdd
141 #innodb_file_per_table="ON"
142 #innodb_data_file_path=''
143 fi
144 if ! test -d "$datadir"
145 then
146 echo "$datadir does not exists! exiting..."
147 exit 1
148 else
149 echo "Using datadir = $datadir"
150 echo "InnoDB params: $innodb_file_per_table, $innodb_data_file_path"
151 fi
152
153}
154
155function get_row_format() {
156 # get row format
157 Row_format=`$MYSQL -NB -e "SHOW TABLE STATUS LIKE '$t'" ${mysql_db}| awk '{print $4}'`
158 is_56=`$MYSQL -NB -e "select @@version"| grep 5\.6`
159 if [ "$Row_format" == "Compact" ]
160 then
161 Row_format_arg="-5"
162 if ! test -z "$is_56"; then Row_format_arg="-6"; fi
163 else
164 Row_format_arg="-4"
165 fi
166 echo "... row format arg: $Row_format_arg"
167}
168
169function recover_innodb_dict() {
170 echo "Using datadir = $datadir"
171 echo "Splitting InnoDB tablespace into pages... "
172 old_IFS="$IFS"
173 IFS=";"
174 for ibdata in $innodb_data_file_path
175 do
176 ibdata_file=`echo $ibdata| awk -F: '{print $1}'`
177 "$top_dir"/stream_parser -f "$datadir/$ibdata_file"
178 done
179 IFS=$old_IFS
180 if [ $innodb_file_per_table == "ON" ] || [ $innodb_file_per_table == "1" ]
181 then
182 if [ "$tables" == "" ]
183 then
184 tables=`$MYSQL -NB -e "SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA='$mysql_db' and ENGINE='InnoDB'" information_schema`
185 fi
186 echo "--- Recovering tables $tables in $mysql_db"
187 for t in $tables
188 do
189 "$top_dir"/stream_parser -f "$datadir/$mysql_db/$t.ibd"
190 done
191 fi
192 echo "OK"
193
194 echo -n "Actual recovering of InnoDB dictionary... "
195 old_IFS="$IFS"
196 IFS=";"
197 for ibdata in $innodb_data_file_path
198 do
199 ibdata_file=`echo $ibdata| awk -F: '{print $1}'`
200 dir="pages-$ibdata_file"/FIL_PAGE_INDEX/`printf "%016d" 1`.page
201 mkdir -p "dumps/${mysql_db}_recovered"
202 if test -f "$dir"
203 then
204 "$top_dir"/c_parser -4Uf "$dir" -p "${mysql_db}_recovered" \
205 -t "$top_dir"/dictionary/SYS_TABLES.sql \
206 >> "dumps/${mysql_db}_recovered/SYS_TABLES" \
207 2>SYS_TABLES.sql
208 fi
209 dir="pages-$ibdata_file"/FIL_PAGE_INDEX/`printf "%016d" 3`.page
210 if test -f "$dir"
211 then
212 "$top_dir"/c_parser -4Uf "$dir" -p "${mysql_db}_recovered" \
213 -t "$top_dir"/dictionary/SYS_INDEXES.sql \
214 >> "dumps/${mysql_db}_recovered/SYS_INDEXES" \
215 2>SYS_INDEXES.sql
216 fi
217 done
218 IFS=$old_IFS
219
220 $MYSQL_TMP $mydebug -e "DROP TABLE IF EXISTS SYS_TABLES" ${mysql_db}_recovered
221 $MYSQL_TMP $mydebug -e "DROP TABLE IF EXISTS SYS_INDEXES" ${mysql_db}_recovered
222 # load structure
223 $MYSQL_TMP $mydebug ${mysql_db}_recovered < "$top_dir"/dictionary/SYS_TABLES.sql
224 $MYSQL_TMP $mydebug ${mysql_db}_recovered < "$top_dir"/dictionary/SYS_INDEXES.sql
225 # load data
226 $MYSQL_TMP $mydebug ${mysql_db}_recovered < SYS_INDEXES.sql
227 $MYSQL_TMP $mydebug ${mysql_db}_recovered < SYS_TABLES.sql
228 echo "OK"
229}
230
231function recover_tables() {
232 echo -n "Recovering tables... "
233 for t in $tables
234 do
235 # Create table structure
236 #echo "pages-$t.ibd/$t.sql"
237 if [ $USEFRM -eq 0 ]
238 then
239 mysqldump --skip-triggers --skip-add-drop-table --no-data ${mysql_db} $t > pages-$t.ibd/$t.sql
240 else
241 echo "Running mysqlfrm --diagnostic '$datadir/$mysql_db/$t.frm' ... "
242 $MYSQL_TMP -e "create database if not exists ${mysql_db}"
243 mysqlfrm -v --diagnostic $datadir/$mysql_db/$t.frm | egrep -v '^#' | $MYSQL_TMP # > pages-$t.ibd/$t.sql
244 mysqldump -S $mysql_tmp_socket --skip-triggers --skip-add-drop-table --no-data ${mysql_db} $t > pages-$t.ibd/$t.sql
245 fi
246 if [ "$i" == "" ]
247 then
248 get_row_format
249 else
250 Row_format_arg="-5"
251 fi
252 # Get index id
253 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`
254 echo "index_id=$index_id"
255 if [ $innodb_file_per_table == "ON" ] || [ $innodb_file_per_table == "1" ]
256 then
257 #"$top_dir"/c_parser $Row_format_arg -f "$datadir/$mysql_db/$t.ibd" \
258 set -x
259 "$top_dir"/c_parser $Row_format_arg -f "pages-$t.ibd/FIL_PAGE_INDEX/`printf '%016u' $index_id`.page" \
260 -p "${mysql_db}_recovered" \
261 -t "pages-$t.ibd/$t.sql" $UNDEL \
262 > "dumps/${mysql_db}_recovered/$t" 2> $t.sql
263 set +x
264 else
265 old_IFS="$IFS"
266 IFS=";"
267 for ibdata in $innodb_data_file_path
268 do
269 ibdata_file=`echo $ibdata| awk -F: '{print $1}'`
270 dir="pages-$ibdata_file"/FIL_PAGE_INDEX/0-$index_id
271 if test -d "$dir"
272 then
273 "$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
274 ecovered/$t" 2> $t.sql
275 fi
276
277 done
278 IFS="$old_IFS"
279 fi
280 done
281 echo "OK"
282}
283
284function load_to_mysql() {
285 echo -n "Loading recovered data into MySQL... "
286 for t in $tables
287 do
288 $MYSQL $mydebug -e "DROP TABLE IF EXISTS \`$t\`;CREATE TABLE $t like ${mysql_db}.$t" ${mysql_db}_recovered
289 $MYSQL -vvv ${mysql_db}_recovered < $t.sql > $t.load.log 2>&1
290 grep -A 4 'LOAD DATA' $t.load.log
291 done
292 echo "OK"
293}
294### Main
295
296if [ $# -lt 2 ] ; then
297 echo 'Too few arguments supplied'
298 usage
299 exit 1
300fi
301
302while getopts ":v:i:d:t:D:f" opt; do
303 case $opt in
304 v)
305 VERBOSE=1
306 ;;
307 i)
308 mdd=$OPTARG
309 ;;
310 d)
311 mysql_db=$OPTARG
312 if [ $VERBOSE -eq 1 ] ; then
313 echo "Database: $mysql_db"
314 fi
315 ;;
316 t)
317 tables=$OPTARG
318 if [ $VERBOSE -eq 1 ] ; then
319 echo "TABLES: $tables"
320 fi
321 ;;
322 D)
323 UNDEL=" -D "
324 echo "!!!UNDEL flag is set!!!"
325 if [ $VERBOSE -eq 1 ] ; then
326 echo "UNDEL flag is set"
327 fi
328 ;;
329 f)
330 USEFRM=1
331 if [ $VERBOSE -eq 1 ] ; then
332 echo "Will use FRMREADER utility to parse FRM files instead of mysqldump. (EXPERIMENTAL)"
333 fi
334 ;;
335 esac
336done
337
338echo "$v $mdd $mysql_db $tables"
339
340#check_mysql_connection
341
342trap on_error QUIT EXIT INT TERM ERR
343
344start_temp_mysql
345echo $mysql_tmp_socket
346MYSQL_TMP="mysql --no-defaults -S $mysql_tmp_socket "
347if [ $VERBOSE -eq 1 ] ; then
348 mydebug=" -vvv "
349fi
350
351init
352
353#echo "Starting recovering InnoDB system dictionary..."
354recover_innodb_dict
355
356recover_tables
357load_to_mysql
358cd "$top_dir"
359
360echo "Temp data is saved to $work_dir"
361echo "All DONE."

Subscribers

People subscribed via source and target branches