Recently, I made a huge mistake on my server and ended up with a failed install and no backups.
I basically had plesk with several wordpress sites and by chance, I still had access to my data, even though it was not useable anymore.
So here is how I did to recover my files and be able to get back a .sql file with structure and data.
So all my mysql data is lying on my server in
/var/lib/mysql and it's organized in
ibdata1(one important file to keep)
- one folder per database name
- inside each of this folder,
So let's rappatriate everything on my computer using
mkdir mysql scp -r root@<ip>:/var/lib/mysql/* .
In order to be able to recreate the databases, we need to use the same version of mysql/mariadb
For this, launch
cat /var/lib/mysql/mysql_upgrade_info on the server.
I got something like
As we know the exact version we need, we will now use
docker to launch a mysql server of exactly this version.
So on your local, run:
docker pull mariadb:10.1.48 docker run -p 127.0.0.1:3307:3306 --name mariadbtest -e MYSQL_ROOT_PASSWORD=Password123! -d mariadb:10.1.48
For this, we launch docker on port 3307 just in case another mysql server is already running.
Now, we can login to the database by using our favorite mySQL explorer, for me, it's Sequel Pro
You can connect to the database using
- Host: 127.0.0.1
- username: root
- password: Password123!
- port: 3307
🎉 you got now the same version of mysql you used to have on your server.
So now, let's move each database inside docker to be able to access it:
docker cp dbname/ mariadbtest:/var/lib/mysql
dbname begin the folder holding the
From there, if you refresh your databases in Sequel Pro, you should see your database but an error occurs when you try to open any table.
Table 'dbname.mod844_icl_string_status' doesn't exist for example
In order to fix this, you need to copy the previous file we backed up:
docker cp ibdata1 mariadbtest:/var/lib/mysql
After that, restart your docker machine and you should be able to access your data using Sequel Pro.
Now you can safely export your data into a
.sql file and think about always setting up a backup system from now on!
My name is Martin Ratinaud and I am a full stack developer
I run a staking crypto comparator to help you find the best rates for any crypto.
And as a remote worker since 2016, I also want you to discover your sweet spot based on your hobbies, so that you can expatriate and live your best life.