# mysql database corruption



## tony33 (Jul 27, 2020)

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 (Jul 28, 2020)

tony33 said:


> 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 (Jul 28, 2020)

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


----------



## tony33 (Jul 29, 2020)

Lamia said:


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


how would you restore that?


----------



## Lamia (Jul 29, 2020)

Are your DBs MYISAM or INNODB?


----------



## CyberCr33p (Jul 29, 2020)

tony33 said:


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



What was the exact error message?


----------



## janprzy (Jul 31, 2020)

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:


```
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.


----------



## tony33 (Aug 4, 2020)

Lamia said:


> 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:


----------



## tony33 (Aug 5, 2020)

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 (Aug 5, 2020)

You should probably read this:




__





						MySQL :: MySQL 8.0 Reference Manual :: 15.6.3.6 Moving Tablespace Files While the Server is Offline
					






					dev.mysql.com
				




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!


----------



## tony33 (Aug 5, 2020)

mark_j said:


> You should probably read this:
> 
> 
> 
> ...


what does innodb_force_recovery does? why do I have to export the bad database?


----------



## mark_j (Aug 5, 2020)

Read: https://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html


----------



## tony33 (Aug 6, 2020)

mark_j said:


> Read: https://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html


 I don't need to recover. I talked to someone on another forum.  I am told I just  need to reassign a new id for one of the database tables.
The error is just saying the both tables have the same id which is 1 which is why my server cannot start. I need to know the command
to tell mysql to change the tablespace id.


----------

