I recently had to restore databases from a rough mysqldump backup in a piecemeal fashion. One necessity is to SET the environment correctly, lest some weird encoding issues happen when restoring the data, leading to failures.

A sed one-liner can help for this.

DBNAME=mydb
sed -n "/^-- Server version/,/^-- Current Database/p;/^-- Current Database.*${DBNAME}\`/,/^-- Current Database/{p}" mysqldump.sql > ${DBNAME}.sql

This extracts SQL from the initial header, to the first database, which contains all the sessions SETs. It then captures statements any time the target database is the current one. Note that this doesn’t restore the GRANTs.

Befor blindly piping the output SQL into mysql, one would be well advised to review the contents of the file, to ensure only the desired modifications are included.

I’ve been using Kodi (then XBMC) for more than a decade now (yup, “XB” did stand for X Box alright, but now LibreELEC on a WeTek Core). I’ve also had the library in MySQL for more than half of it. Across migrations, it had developed some quirky content, such as duplicate albums, and some rarities, such as this version of 21, by Adèle, where the description reminds us that her previous album, Ixnay on the Hombre, was only moderately successful on launch; go figure…

As suggested, pretty much everywhere, as the solution for duplicate content in Kodi, I first tried cleaning the library, repeatedly, to no avail. The duplicate albums were still there. One of their noticeable characteristics, though, was that there was always some copy of the album (and in Adèle’s case, the one following Ixnay), that did not have any associated tracks. This felt like it could be a good angle to help me clear those up. Enter some SQL.

Continue reading