Can mysql restore a single table from a large mysqldump?
I have found two solutions, one using
grep -n "Table Structure" mydump.sql
# identify the first and last line numbers (n1 and n2) of desired table
sed -n n1,n2p mydump.sql > mytable.sql # (e.g. sed -n 48,112p)
and one using awk
awk '/Table Structure for table .table1./, /Table structure for table .cultivars./{print}' mydump.sql > mytable.sql
This approach does not get around the issue of having to import a large mysqldumpfile, but from within Mysql you can do the following:
restore entire dump
mysql fakedb < mydump.sql
delete contents of current table
mysql delete from production.target_table;
insert from backup table
insert into production.target_table select * from fakedb.targettable;
I have created both linux and windows scripts to restore the specific tables from the dump file:
linux (bash script):
#!/bin/bash
# Where to restore
db_host='localhost'
db_name='adhoctuts'
db_user='root'
db_pass='Adhoctuts2018#'
dump_file='/root/scripts/dump_ignore.sql'
# Associative table list array as source_table=>destination_table pairs
declare -A tbl_list=( ["tbl1"]="restored_tbl1" ["tbl2"]="restored_tbl2")
for tbl in "${!tbl_list[@]}"
do
echo "Restore $tbl to ${tbl_list[$tbl]}"
# extract the content between drop table and Table structure for, also replace the table name
sed -n -e '/DROP TABLE IF EXISTS `'$tbl'`/,/\/*!40000 ALTER TABLE `'$tbl'` ENABLE KEYS \*\/;/p' $dump_file > tbl.sql
sed -i 's/`'$tbl'`/`'${tbl_list[$tbl]}'`/g' tbl.sql
mysql -h $db_host -u $db_user -p"$db_pass" $db_name < tbl.sql
rm -f tbl.sql
done
windows script (bat):
%= Define the database and root authorization details =%
@ECHO OFF
SETLOCAL EnableDelayedExpansion
set db_host=192.168.70.138
set db_name=adhoctuts
set db_user=adhoctuts
set db_pass=Adhoctuts2018#
set dump_file=dump_ignore.sql
set tbl_cnt=2
set source_table[1]=tbl1
set destination_table[1]=restored_tbl1
set source_table[2]=tbl2
set destination_table[2]=restored_tbl2
set i=1
:loop
set src=!source_table[%i%]!
set dest=!destination_table[%i%]!
for /f "tokens=1 delims=[]" %%a in ('find /n "DROP TABLE IF EXISTS `%src%`"^<"%dump_file%"') do set /a start=%%a
for /f "tokens=1 delims=[]" %%a in ('find /n "ALTER TABLE `%src%` ENABLE KEYS"^<"%dump_file%"') do set /a end=%%a
(
for /f "tokens=1* delims=[]" %%a in ('find /n /v ""^<"%dump_file%"') do (
set "line=%%b "
IF %%a geq %start% IF %%a leq %end% ECHO( !line:%src%=%dest%!
)
)>"tbl.sql"
mysql -h %db_host% -u %db_user% -p"%db_pass%" %db_name% < "tbl.sql"
del /f "tbl.sql"
if %i% equ %tbl_cnt% goto :eof
set /a i=%i%+1
goto loop
here you define the tables you need to restore and under what name to restore. This the more general solution.
I have also created separate tutorial for MySQL selective/exceptional tasks. You may check if needed:
https://youtu.be/8fWQbtIISdc
https://adhoctuts.com/mysql-selective-exceptional-permissions-and-backup-restore/