MYSQL database safe

[TiCeVi]

I had an raspberry pi running a server with MYSQL. The pi stoped functioning but I could safe the SD card. Is it possible to safe a sql file without starting a server?

[Judahnator]

Do you mean "save"? instead of "safe"?

 

If so, then yes. If the data is still intact then it shouldnt be hard to grab.

[Neil]

"saving" the data depends on a number of factors. It should be possible, but it's also possible the tables are corrupt and require repair.

I can walk you through the repair if that's the case.

Anyways, you'll want to copy the ENTIRETY of /var/lib/mysql/ in order to have any chance of recovery.

[tomix1024]

I guess no (or at least i guess it will be harder than just run a mysql server and dump the data from there).

 

the data actually stored in the /var/lib/mysql/[database] directory depends on your storage engine (most common one's are InnoDB and MyISAM), maybe there are tools available that can read these files.

at least every table gets (on my server) it's own file...

 

as said, the most easy way to recover your data would be to reinstall the pi, install mysql server and restore the /var/lib/mysql directory from your backup and restart the mysql server.

[burnttoastnice]

On my Pi I use SQLite database files. I keep them on external storage since they get modified often, and it also makes it easier to manage backups in case of a system failure.

 

In future, you should use a program such as WinDiskImager to take images of your Pi's SD card on a regular basis - so if the Pi does fail and take the SD card with it - you'll be up and running on another one in seconds.

[TiCeVi]

Thanks for all the help. The sd is still functioning. And I found the directory but I can't copy it because of some rights the owner is 'RealtimeKit'

[TiCeVi]

On my Pi I use SQLite database files. I keep them on external storage since they get modified often, and it also makes it easier to manage backups in case of a system failure.

In future, you should use a program such as WinDiskImager to take images of your Pi's SD card on a regular basis - so if the Pi does fail and take the SD card with it - you'll be up and running on another one in seconds.

I have an image of the old pi but now with the pi b+ doesn't boot. With the old image

[Neil]

You can use sudo to copy the directory.

[Neil]

I guess no (or at least i guess it will be harder than just run a mysql server and dump the data from there).

the data actually stored in the /var/lib/mysql/[database] directory depends on your storage engine (most common one's are InnoDB and MyISAM), maybe there are tools available that can read these files.

at least every table gets (on my server) it's own file...

as said, the most easy way to recover your data would be to reinstall the pi, install mysql server and restore the /var/lib/mysql directory from your backup and restart the mysql server.

Innodb's logfiles aren't stored in the individual db folder, so he'd need the binary log from /var/lib/mysql

[tomix1024]

Innodb's logfiles aren't stored in the individual db folder, so he'd need the binary log from /var/lib/mysql

That should be true, but i wasn't talking about any logfiles, but about these binary as you were talking about, too .

They might depend on the storage engine, but maybe not (it would at least make sense to me).

[TiCeVi]

You can use sudo to copy the directory.

Yeah already tried that but even in the terminal it says I couldn't enter

[burnttoastnice]

I have an image of the old pi but now with the pi b+ doesn't boot. With the old image

 

Oh. You'll need to get your hands on an old Pi then do rpi-update to upgrade the kernel and 'firmware' to run on newer Pis. There might be ways to fix it without getting an old pi however, since the kernel & boot commands are on the windows-readable fat32 partition.

[Neil]

That should be true, but i wasn't talking about any logfiles, but about these binary as you were talking about, too .

They might depend on the storage engine, but maybe not (it would at least make sense to me).

The binary log files are necessary to repair databases. If you delete the innodb logs the tablespaces are unrecoverable.

[TiCeVi]

You can use sudo to copy the directory.

i could now copy the mysql folder. what should i do now?

 

thanks for the help

[Neil]

Well make sure you have a copy of it. Install mysql. Start mysql with that directory as the data directory (I think it's the -D option but I'm on mobile)

Then mysqldump the table, and import it into the main mysql instance.

[TiCeVi]

Great. I figured it out already. I also had to change some of the permission with the chown command. After this I could copy the database folder in to the new MYSQL and phpmyadmin setup on my Ubuntu system. Now I could export the table as .sql file and import on the Pi system.

Thanks for all the great advise and help[emoji2]