Mysql – How to restore a specific table from a big thesqldump file

MySQLmysqldumprestore

From a very big backup file (created by mysqldump) I need to bring back only one (or a few) specific table(s), without loading all tables.

Are there any fast alternatives?

Best Answer

You could achieve that by two steps :

Step 1 : Locate position of your table(or tables) and it (them) in a file :

 grep -n "Table structure" your_dump_file > list_tables_NUM.TXT

This will create a list of all tables ranked by position, example :

cat list_tables_NUM.TXT
...
41743:-- Table structure for table `table_X`
41770:-- Table structure for table `table_Y`    
42780:-- Table structure for table `table_Z`
...

Based-on that result you will create a secondary file containing that specific table, let's assume that you need to restore only table_X and table_Y:

Step 2 : Extract from position 41743 to 42780 :

sed -n '41743,42780 p' your_dump_file > dump_table_XY.sql

At the final, you may find your tables (structure+data) in the dump_table_XY.sql

Tested for MySQL/Mariadb in Centos7

Related Solutions

Mysql – Quick MySQL Backup (1 file per table)

Last time, I answered your question by suggesting you perform a well-scripted parallel dump of all tables.

Since the tables are lopsided in size, parallel table dumps are not much help for the 20GB and 13GB tables. What can be done? You must try something out of the ordinary. The good thing here is that all the tables are MyISAM.

Have you ever considered running an rsync? How do you do that?

EXAMPLE

You have /var/lib/mysql as your datadir for a remote production database

Perform an rsync on /var/lib/mysql on prod server (IP Address 10.1.2.30) to /backups on a backup server. Just run a script something like this on the backup server:

INIT_TS=0
COMP_TS=0
DIFF_TS=61
RSYNC_THRESHOLD=60
DBSERVER_IP=10.1.2.30
FOLDER_TO_BACKUP=/var/lib/mysql
mkdir /backups/backup_temp

while [ ${DIFF_TS} -gt ${RSYNC_THRESHOLD} ]
do
    INIT_TS=`date +%s`
    rsync -arv user01@${DBSERVER_IP}:${FOLDER_TO_BACKUP}/ /backups/backup_temp
    CURR_TS=`date +%s`
    (( DIFF_TS = CURR_TS - PREV_TS ))
done

MYSQL_CONN="-h${DBSERVER_IP} -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 3
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

rsync -arv user01@${DBSERVER_IP}:/var/lib/mysql/ /backups/backup_temp

BACKUP_TIMESTAMP=`date +"%Y%m%d%H%M%S"`
mv /backups/backup_temp /backups/${BACKUP_TIMESTAMP}

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

Here is what this basic backup script does

Step 01) rsync production's /var/lib/mysql (from remote server) to /backups on local server
Step 02) If running time of rsync is greater than 60 seconds, repeat Step 01
Step 03) Close all tables and issue global read lock
Step 04) Perform final rsync (SELECTs allowed only)
Step 05) Release global read lock

Notice the following:

the only mysql interaction is
- issuing the global read lock
- checking for the process ID that is holding the global read lock
- releasing the global read lock
FOLDER_TO_BACKUP is the datadir. If you want to backup a specific database (say mydb), set FOLDER_TO_BACKUP to /var/lib/mysql/mydb
Actual downtime is from the acquisition of the SLEEP_ID to the processing killing of the SLEEP_ID

Give it a Try !!!

UPDATE 2012-02-02 11:47 EDT

The above script secures mysqld in such a way that it takes a disklevel copy of the database while allowing mysqld to perform INSERTs, UPDATEs, and DELETEs during the copy. This is necessary since the MyISAM Storage Engine does not have a natural mechanism for allowing a copying of itself. Even powerful products like MySQL Enterprise Backup and Percona XtraBackup perform full table locking on MyISAM tables just like this. My personal preference is to script it so I can see it, feel it, taste it, and touch it and just know for myself that MyISAM is fully protected.

The advantage of using my script does stems from the fact that I shorten the length of downtime. In the context of this anwser, downtime is really the period where the mysqld process will only allow SELECTs to all MyISAM tables during the final rsync.

An additional benefit is that all MyISAM tables copied will all have the same point-in-time should you wish to restore all MyISAM tables as of the same point-in-time. That point-in-time will be based, not on the start of the backup process, but when the global read lock as acquired.

If you need to copy only specific tables and you can guarantee no DB Connection will touch it, only then can you toss aside any failsafes and copy any MyISAM table to your heart's content.

UPDATE 2012-02-03 11:47 EDT

If the length of time the rsync takes concerns you, here is an additional suggestion:

You should also make nightly backups of the binary logs. By having nightly copies of binary logs, you would essentially have the incremental backups via those binary logs.

That being said, you could set up a weekly or monthly run of the backup script. Recovery to a specific point-in-time would be your responsibility from there, but you would have every piece of data necessary to do so.

Sql-server – Backing up file groups with restore to earlier backup

What is the backup and restore method? It is called Piece Meal Restore and unrestored filegroups can be restored at a later time.

Is it possible to restore only the lamb filegroup? Yes it is possible to restore only the lamb filegroup.

Below will show you - how you can do it.

Create a database called "FGTest"

create database [FGTest] on primary (
name = N'FGTest'
,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\FGTest.mdf'
,SIZE = 3072 KB
,FILEGROWTH = 1024 KB
)
,FILEGROUP [lamb] (
name = N'lamb'
,-- fileGroup1
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\FGTest_2.ndf'
,SIZE = 3072 KB
,FILEGROWTH = 1024 KB
)
,FILEGROUP [lamb1] (
name = N'lamb1'
,-- fileGroup2
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\FGTest_3.ndf'
,SIZE = 3072 KB
,FILEGROWTH = 1024 KB
) LOG on (
name = N'FGTest_log'
,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\FGTest_log.ldf'
,SIZE = 1024 KB
,FILEGROWTH = 10 %
)

Now create tables on different file groups - lamb and lamb1

create table TAB1 (
TAB1_ID int IDENTITY(1, 1)
,TAB1_NAME varchar(100)
,constraint PK_TAB1 primary key (TAB1_ID)
) on lamb -- Filegroup we created.
go

create table TAB1_lamb1 (
TAB1_ID int IDENTITY(1, 1)
,TAB1_NAME varchar(100)
,constraint PK_TAB1_lamb1 primary key (TAB1_ID)
) on lamb1 -- 2nd Filegroup we created.
go

Take a Base backup

-- Take a base backup
BACKUP DATABASE [FGTest] to 
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_basebackup.bak'
with init, stats = 10
go

Now insert some values .... You can load a CSV at this point

INSERT INTO FGTest..TAB1(TAB1_NAME)
select ('TAB1')
union all
select ('TAB2')

backup filegroup lamb -- with 2 records

-- backup filegroup lamb - with 2 records

BACKUP DATABASE [FGTest] FILEGROUP = N'lamb' 
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_2Records.bak' 
WITH  INIT,  NAME = N'FGTest-Full Filegroup Backup', 
STATS = 10
GO

-- log backup filegroup lamb - with 2 records

BACKUP LOG [FGTest] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_2records.trn' WITH INIT

Now insert few more values in lamb and lamb1 file groups

INSERT INTO FGTest..TAB1(TAB1_NAME)
select ('TAB3')
union all
select ('TAB4')

INSERT INTO FGTest..TAB1_lamb1(TAB1_NAME)
select ('TAB3')
union all
select ('TAB4')

Now backup lamb and lamb1 filegroups along with the T-logs

-- -- backup filegroup lamb - with 4 records

BACKUP DATABASE [FGTest] FILEGROUP = N'lamb' 
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_4Records.bak' 
WITH  INIT,  NAME = N'FGTest-Full Filegroup Backup', 
STATS = 10
GO

-- log backup filegroup lamb - with 4 records

BACKUP LOG [FGTest] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_4records.trn' WITH INIT 


-- -- backup filegroup lamb1 - with 2 records

BACKUP DATABASE [FGTest] FILEGROUP = N'lamb1' 
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_lamb1.bak' 
WITH  INIT,  NAME = N'FGTest-Full Filegroup Backup', 
STATS = 10
GO

-- log backup filegroup lamb1 - with 2 records

BACKUP LOG [FGTest] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_lamb1_log.trn' WITH INIT

enter image description here

Now comes the restore Part :

Restore the database with PARTIAL and NORECOVERY

 use master
 go

 -- restore the base backup with PARTIAL and NORECOVERY

 restore database [FGTest]
 filegroup = 'Primary'
 from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_basebackup.bak'
 with REPLACE, PARTIAL, NORECOVERY
 go

Now Restore the lamb filegroup along with the T-log

 RESTORE DATABASE [FGTest] FILE = N'lamb' FROM  
 DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_2Records.bak' 
 WITH  FILE = 1, REPLACE,  STATS = 10
 GO

 RESTORE LOG [FGTest] FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_2records.trn'
 WITH FILE = 1, NORECOVERY

-- since you want to restore the database to 1st backup and you already restored the sequence, its time to bring the database back online with 2 records.

restore database [FGTest] with recovery enter image description here

Since you have not brought the other file group online, when you try to query TAB1_lamb1 table residing on lamb1 filegroup, the query processor will throw an error.