6

What is the best way to restore a single table from a large (~5GB) database mysqldump file?


update: I have found solutions (posted below) using command line tools to parse the table, but is there a way to do this with mysqlimport?

  • Create a new user with access to only that table and then restore it using the newly created user with --force parameter. – shantanuo Mar 13 '12 at 1:42
3

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/

9

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
  • Many sysadmins that have to manhandle mysql thrive on stuff like this. +1 !!! – RolandoMySQLDBA Mar 9 '12 at 22:34
  • Neither of these work. The sed solution yields an error: sed: -e expression #1, char 6: unknown command: ',', and the awk solution yields an empty file. – CaptSaltyJack Jun 19 '13 at 16:11
  • 1
    Correct sed command is: sed -n n1,n2p mydump.sql > mytable.sql (e.g. sed -n 48,112p) – CaptSaltyJack Jun 19 '13 at 16:19
  • @CaptSaltyJack thanks for the clarification I have updated my answer – David LeBauer Jun 19 '13 at 16:22
  • FYI -- at least with mysql Ver 14.14 Distrib 5.6.14 -- the correct grep string is actually "Table structure" (lowercase s), also fwiw, I used ack instead of grep (requires you to create an empty file so you can ack 2 separate files in order to get the line numbers displayed), and it took 69 seconds vs 866 with grep on a 52gb database dump (I had to benchmark it after I realized it was much faster). – Kem Mason Nov 22 '14 at 5:16
6

This approach does not get around the issue of having to import a large mysqldumpfile, but from within Mysql you can do the following:

  1. restore entire dump

     mysql fakedb < mydump.sql
    
  2. delete contents of current table

     mysql
     delete from production.target_table;
    
  3. insert from backup table

    insert into production.target_table select * from fakedb.targettable;
    
  • 1
    For step1, make sure DROP DATABASE is not included. Otherwise, your way works for smaller databases. +1 !!! – RolandoMySQLDBA Mar 9 '12 at 22:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.