[SOLVED] MySQL backup, what did I do wrong?
#1
So I recently had to reinstall my VPS with a new operating system: Ubuntu 16.04.

I had Debian Jessie on it, and I wanted a more fresh system, so reinstalled.

I take backup of mysql in this way: stop the mysql service (mariadb 10) and copy the /var/lib/mysql folder to the backup partition.

Then on the fresh system I installed the same, mariadb 10, and stopped it, and I overwrite the full /var/lib/mysql directory from the backup partition.

But now I have nearly all sql tables has some errors and data lost!

I run mysqlcheck to see the errors, and there are a lot of like this:

Code:
game.kapuk
note     : The storage engine for the table doesn't support repair
game.labels
note     : The storage engine for the table doesn't support repair
game.log                                          OK
game.logok
Warning  : Tablespace is missing for table 'game/logok'
Error    : Table 'game.logok' doesn't exist in engine
status   : Operation failed

So when I want to open these tables in phpmyadmin they are eirther missing or doesn't load at all.. Is there any way to fix this? And what did I do wrong?

I also tried to install on an other VPS the same old Jessie system, and restored the backup, but the same errors happened. Sad
#2
First of all please keep the old backup, between you do not need to back up that way!.It is complicated.Use the export option on PhPMyAdmin or use other backup tools.
Now run "mysql_upgrade" from shell(Just do a test, not expect more).
You may have to see innodb_force_recovery option and dumb the data if other methods does not work.
Quote:The storage engine for the table doesn't support repair
You have these tables on InnoDB.You have to change those tables to MyISAM for repair support.
Code:
ALTER TABLE table_name ENGINE=MyISAM;
Quote:Warning : Tablespace is missing for table 'game/ logok' Error : Table 'game.logok' doesn't exist in engine status : Operation failed
You are missing .idb file for the table
Code:
ALTER TABLE 'game.logo' DISCARD TABLESPACE
Have a look at
https://twindb.com/repair-corrupted-inno...ary-index/
Show support by wearing an Avatar or Signature or Custom Rank if you think freevps.us should not be closed.
Code:
[IMG]http://i65.tinypic.com/15z1afs.jpg[/IMG]
#SaveFreeVPS for the future
#3
(2016-05-03, 8:56:39 am)konorbi Wrote:  I take backup of mysql in this way: stop the mysql service (mariadb 10) and copy the /var/lib/mysql folder to the backup partition.

Well, I didn't want to say anything negative but...

It's basically all your own fault. This is not how you backup a database. If you don't know how to backup databases via CLI (mysqldump) then you can use software like phpMyAdmin or Adminer and their inbuilt database export functions. Otherwise you can use mysqldump to get a backup of the database in .sql format and then tar and gz the file to make it smaller. Later on either import it back through phpMyAdmin/Adminer or extract it and import it though mysql CLI.

What you did is not a way to backup databases at all. What you have now is a broken backup that is worth nothing and cannot be fixed anymore because the original MySQL server that might have had the files to fix this is already long gone.
#4
The problem was that I didn't write over the ibdata1 file. Now it is working and all tables are healthy.




Users browsing this thread: 1 Guest(s)