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.
First, retrieve 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,
.frm
and.idb
files
So let's rappatriate everything on my computer using scp
mkdir mysql
scp -r root@<ip>:/var/lib/mysql/* .
Then, retrieve mysql version
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 10.1.48-MariaDB
.
Launch mysql/mariadb on local
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.
Copy your old databases to the docker machine
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 .frm
and .idb
files.
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
Fix errors
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.
Export your data
Now you can safely export your data into a .sql
file and think about always setting up a backup system from now on!
Cheers
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.
Top comments (0)