Restore single table from full MySQL database dump

Playing with data in databases is sometimes tricky but when you get down to it it’s just couple of lines on the command line. Sometime ago we switched from Piwik PRO to Matomo and of course we wanted to migrate logs. We couldn’t just use the full MySQL / MariaDB database dump and go with it as table names and the schema was different (Piwik PRO 3.1.1. -> Matomo 3.5.1). In short we needed to export couple of tables and rename them to match new instance similarly as discussed in Stack Overflow.

There’s a VisitExport plugin for Piwik/Matomo which lets you export and import log tables with PHP and JSON files but it didn’t seem usable approach for our use case with tables being 500 MB or so.

The more practical solution was to simply create a dump of the tables we wished to restore separately.

1. Dump specific table of the database

mysqldump -u 'user' -p'password' database mytable > mytable.sql

And as I was playing with Docker

docker exec mariadb-container bash -c 'mysqldump -uroot -p$MYSQL_ROOT_PASSWORD database mytable' > ./database_dump_mytable_`date +%F`.sql

2. Change the table name before importing

sed -e 's/`mytable`/`mytable_restored`/g' mytable.sql > mytable_restored.sql

3. Import the new table

mysql -u [user] -p'password' database < mytable_restored.sql

And with Docker

docker exec mariadb-container mysql -uroot -p'password' database < ./database_dump_mytable_`date +%F`.sql

Or you can dump the whole database and use sed to extract the tables having a prefix or a suffix

1. Dump the database

mysqldump -u 'user' -p'password' database > dump.sql

2. Extract table

sed -n -e '/DROP TABLE.*`mytable`/,/UNLOCK TABLES/p' dump.sql > mytable.sql

3. Change the table name before importing

sed -n -e 's/`mytable`/`mytable_restored`/g' mytable.sql > mytable_restored.sql

3. Import the new table

mysql -u [user] -p'password' database < mytable_restored.sql

And with good luck you have now exported single table from full MySQL / MariaDB database dump.