mysql database corruption

tony33

Active Member

Reaction score: 3
Messages: 110

I don't know how or what's going on. However, I get error messages mysql server won't start.

I went to var/db/mysql and renamed mysql to like mysql2 and reinstalled mysql server and client.

I generated a new mysql folder in /var/db/ My server would boot up and run. Now, I need to know what I need to do
to transfer the folders from mysql2 to mysql so that my database gets restored?

The errors I got before was about a file not matching the mysql config file settings.

I just need to know what files I need to copy over to restore my database.
 

jmos

Member

Reaction score: 64
Messages: 84

However, I get error messages mysql server won't start.
[…]
The errors I got before was about a file not matching the mysql config file settings.

I just need to know what files I need to copy over to restore my database.
…and what were your "error messages" exactly? Without it you won't be able to fix it. Best result in copying the database files could be that you get your error back.

Beside of SQLite, SQL databases are transported/backuped as SQL dumps - and not the files from "/var/db/…" (tried that long, long time ago and figured out "okay, this doesn't result in a working database"); So it's time to use your yesterdays SQL dump; How that has to be done depends on how your dump was exported, but basically (and if the dump contains one single database):
mysql [-u user] [-ppwasswd] database < dump.sql
 

Lamia

Aspiring Daemon

Reaction score: 151
Messages: 612

Be careful with the ib* files in the MySQL dir. You may still recover your data if they are intact.
 

janprzy

New Member

Reaction score: 1
Messages: 6

Did you recently install an update? I'm using mariadb 10.4 and experienced a similar issue after updating it about a month ago (don't remember the exact version, must've been 10.4.13_3 or something).

Apparently there's a bug regarding socket creation, the solution was pretty simple:

Code:
mkdir /var/run/mysql
chown mysql:mysql /var/run/mysql
I'm sure I got it from this forum, but I couldn't find the thread again.
 
OP
tony33

tony33

Active Member

Reaction score: 3
Messages: 110

Are your DBs MYISAM or INNODB?
I would think it's INNODB . How can I tell this? I see my config file showing settings for INNODB.

I took a screen shot here of the errors in log:

 
OP
tony33

tony33

Active Member

Reaction score: 3
Messages: 110

I have my original /var/db/mysql folder. The screenshot above is the errors I get when trying to start mysql using safe mode.

I used safe mode by reinstalling mysql and having it create a new /var/db/mysql folder. When I did this the system ran perfectly.
I had a root user created but no password. It then generated the default folder mysql with the default databases. Everything worked.
I then went into the config file and changed the database directory to the original one. I then tried starting mysql again and it closed.
I went to the logs and took a pic of the errors in my log . It's complaining that there's multiple files found for the same table space.
How do I fix this?
 

mark_j

Aspiring Daemon

Reaction score: 327
Messages: 641

OP
tony33

tony33

Active Member

Reaction score: 3
Messages: 110

You should probably read this:

Then add innodb_force_recovery = 1 to my.cnf
Restart mysqld, export the bad database, delete all the ib* files then comment out the innodb_force_recovery = 1
and restart mysqld.

Stop using innodb, go back to something more reliable like myISAM. (Joking). ;)
Better still, migrate to postgres!
what does innodb_force_recovery does? why do I have to export the bad database?
 
OP
tony33

tony33

Active Member

Reaction score: 3
Messages: 110

Top