recovering my links site's database

I have been running a links page where I add interesting links I come across. It’s been roughly 2 months since the inception.

I decided to use flask and mysql. I wrote it in an hour and then deployed it with docker. A big mistake I made was using the latest tag in mysql image!

internal server error

I was using --default-authentication-plugin=mysql_native_password flag. It lets you pass the environment variables for the mysql credentials.

Now, for some reason, they decided to remove that flag. And because of that, the db container stopped working. It was throwing unknown variable error:

I looked up in the github issues and found out that quite a lot of people were facing this issue.

The most obvious thing was to downgrade the version (8.3.0 was still supporting that flag), which I did. But mysql was like “I ain’t letting you off that easy”:

Someone commented that using --mysql-native-password=ON will fix the issue. And it sure did!

I was able to get the site back up. But I left the image tag to latest, again!

breaks again

After about a month, the mysql-native-password=ON stopped working as well. They completely removed that feature of using native password.

I do have backups of the last five days but the problem was that rollback to last non-breaking version wasn’t working.

So, I decided to find a way to start the container on a read mode without needing the authentication so I can export the data out of the container and then import it into a new container.

Sure enough, there’s a way to do that. Using –skip-grant-tables, you can skip the authentication process which was the only problem in this case.

After which, I tried to export all the tables to csv file. But you can’t just save that on any path.

This image had set secure_file_priv to /var/lib/mysql-files which means I can only save the output to that path.

I mounted that path in docker compose and then ran this query:

SELECT * FROM links
INTO OUTFILE '/var/lib/mysql-files/links.csv'

After that, I removed that old container and started a new one with version 8.3.0.

Now, all that’s left to do is to import the tables from csv:

LOAD DATA INFILE '/var/lib/mysql-files/links.csv'
INTO TABLE links

Now, it’s all good. I have pinned the version to 8.3.0 which is obviously a bad practice. I will update the flask app in the future so that I can start the database without needing that plugin.