Tag Archives: dbsake

Import/Export single MySQL table 

>> Extract single table from a running database
mysqldump dbname tableName > tableName.sql

>> Extract table from a MySQL dump:
> Option 1) Using 'sed'
sed -n "/^-- Table structure for table \`TABLE_NAME_HERE\`/,/^-- Table structure for table/p" DATABASEDUMP.SQL > TABLENAME-TABLE.sql

> Option 2) Using dbsake: https://github.com/abg/dbsake
cat file.sql | dbsake split-mysqldump -C /tmp/sql

>> Import the table:
# mysql -D dbname < tableName.sql

 

Sources:

http://blog.tsheets.com/2008/tips-tricks/extract-a-single-table-from-a-mysqldump-file.html

https://github.com/JoyceBabu/MySQL-Dump-Table-Extractor

http://blog.tsheets.com/2008/tips-tricks/extract-a-single-table-from-a-mysqldump-file.html#

https://github.com/abg/dbsake

Restore database from Holland XtraBackup using dbsake

How it works?

XtraBackup basically does a copy of ‘/var/lib/mysql’ – no dumps. So, it’s good for a full restore but a bit tricky for a specific db restore.
So, the following steps will create a second MySQL instance using dbsake utility (the version can be specified, but the below example uses the version currently running on the server), extracting only the specific backup (to save space) and from there, you will be able to extract the dump with the well known ‘mysqldump‘.

>> Download dbsake. Bear in mind it requires python2.6 which can be installed from IUS.
mkdir /tmp/dbsake
cd $_
curl -s http://get.dbsake.net > dbsake
chmod +x dbsake
./dbsake --version

>> Install pigz from EPEL repo
yum install pigz

>> Use dbsake to unpack Percona XtraBackup stream extracting only the DB required
screen -S xtrabackup_restore

./dbsake sandbox --sandbox-directory=/tmp/sandbox/ \
--data-source=/var/spool/holland/xtrabackup/newest/backup.tar.gz -t <database_name>.*

>> Start a dbsake instance and dump the required database.
/tmp/sandbox/sandbox.sh start
/tmp/sandbox.sh mysqldump <database_name> | gzip -1 ><database_name>.sql.gz

>> Clean up
/tmp/sandbox/sandbox.sh stop
rm -rf /tmp/sandbox