• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Trying to restore a MySQL database from backup

 
Mike London
Ranch Hand
Posts: 1227
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a simple MySQL database with some image data in it.

For whatever reason, the database seems to have become corrupted on the server so I needed to remove it and overwrite it with a current backup - the actual data directory for that database.

However, the issue I'm having is that after I copy the database to the "data" directory in MySQL (/usr/local/mysql/data), set the permissions, fix the "root" ownership back to _mysql, MySQL says it can't find the table.."The table does not exist."

Looking in the directory beneath the database (in MySQL's data directory), I can see the .frm and related files for the table.



I've tried rebooting and such, but MySQL doesn't want to see that table.

Therefore, I'm wondering what the best way is to copy a database and have MySQL actually recognize it.

I'm using MySQL 5.7.11.

Would appreciate any suggestions.

Thanks in advance,

-- mike
 
Roel De Nijs
Sheriff
Posts: 10665
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mike London wrote:Would appreciate any suggestions.

This SO article provides a few suggestions to copy a MySQL database. It seems you can only copy individual table files if you are using the MyISAM storage engine. So if you are using another storage engine, you might need a combination of them: first create the backup database, copy all backup data to this database and then use mysqldump to create a backup of the Customer table.

Hope it helps!
Kind regards,
Roel
 
Roel De Nijs
Sheriff
Posts: 10665
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mike London wrote:Would appreciate any suggestions.

And here you'll find the MySQL documentation about Database Backup Methods. It even has a section about what to do if a MyISAM table has become corrupt...
 
Mike London
Ranch Hand
Posts: 1227
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Mike London wrote:Would appreciate any suggestions.

And here you'll find the MySQL documentation about Database Backup Methods. It even has a section about what to do if a MyISAM table has become corrupt...


Yes, thanks, saw that already.

I'm already using Navicat, but it appears to have a bug when you dump the SQL tables. I've reported that issue. See my image below about "getevents".

The MySQL dump features works OK, but trying to re-import a dump file which had images in blob fields did not work (outside of Navicat).

I think I may need to re-install MySQL since that installation and how I moved my old data might be missing something.

Strange is that it works for the most part, but, in Navicat, I cannot create a user. I get an error that the "Plugin is not loaded." followed by some long Alphanumeric number. This may be a password OLD vs. NEW issue. I'm not sure, but it appears that in MySQL 5.7.11, OLD_PASSWORD() has been removed and was already deprecated previously.

(See plugin not loaded screenshot--this happens whether I'm in Navicat or not.)

-----

It will be a pain to mysqldump all 10 or so databases, re-install, and "hope" I can restore them successfully.

I've attached a couple screenshots.

Thanks,

- mike
getevents.png
[Thumbnail for getevents.png]
Trying to set user privledges in navicat (some MySQL tables report as "error" in table check)
plugin not loaded.png
[Thumbnail for plugin not loaded.png]
seems like a new vs. old password issue perhaps, not sure.
 
Roel De Nijs
Sheriff
Posts: 10665
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mike London wrote:It will be a pain to mysqldump all 10 or so databases, re-install, and "hope" I can restore them successfully.

Best of luck! I'll keep my fingers crossed Keep us posted on your progress or any possible new issues.
 
Mike London
Ranch Hand
Posts: 1227
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Mike London wrote:It will be a pain to mysqldump all 10 or so databases, re-install, and "hope" I can restore them successfully.

Best of luck! I'll keep my fingers crossed Keep us posted on your progress or any possible new issues.


One of the issues is that on my new 5.7.11 MySQL installation, I appear to be missing tables:


-----

These "cost" tables, for example, show up in the mysql database, however, so I'm guessing they're somehow corrupted?


Not exactly sure how to fix this. Ran the basic fix command, but it didn't fix it. The table shows up, but has zero bytes.

I'm concerned if I re-run the MySQL installer, I might zap all my tables somehow or create a new directory and I'd need to spend additional hours moving tables again.

-----

Still trying to figure out the other plug-in not loaded issue. Will post back.

Thanks,

- mike
 
Mike London
Ranch Hand
Posts: 1227
6
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I ended up re-installing MySQL and that fixed everything. Not sure how the mysql database itself had corrupted tables from the last install, but it's all working now.

- mike
 
Roel De Nijs
Sheriff
Posts: 10665
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mike London wrote:I ended up re-installing MySQL and that fixed everything. Not sure how the mysql database itself had corrupted tables from the last install, but it's all working now.

Glad to hear re-installing MySQL did the trick Have a cow for coming back and sharing your solution.
 
Mike London
Ranch Hand
Posts: 1227
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Mike London wrote:I ended up re-installing MySQL and that fixed everything. Not sure how the mysql database itself had corrupted tables from the last install, but it's all working now.

Glad to hear re-installing MySQL did the trick Have a cow for coming back and sharing your solution.


Thanks for the cow!

I think what happened is that when I copied the data directory directly to the new 5.7.11 data folder from the 5.6 data folder, and that caused a problem. I believe that MySQL changed how they handle passwords (OLD_PASSWORD() now seemingly gone, for example). Possibly something else changed too. So, installing from scratch after first doing a MYSQLDUMP of all the databases was the way to go. How the three fields got corrupted in the mysql table remains a mystery, but all's good for now.

Just a little extra follow up there.

- mike
 
Roel De Nijs
Sheriff
Posts: 10665
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mike London wrote:I think what happened is that when I copied the data directory directly to the new 5.7.11 data folder from the 5.6 data folder, and that caused a problem. I believe that MySQL changed how they handle passwords (OLD_PASSWORD() now seemingly gone, for example). Possibly something else changed too.

It seems there are a bunch of incompatible changes when upgrading to MySQL 5.7 (and some of them are indeed password related).
 
Mike London
Ranch Hand
Posts: 1227
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Mike London wrote:I think what happened is that when I copied the data directory directly to the new 5.7.11 data folder from the 5.6 data folder, and that caused a problem. I believe that MySQL changed how they handle passwords (OLD_PASSWORD() now seemingly gone, for example). Possibly something else changed too.

It seems there are a bunch of incompatible changes when upgrading to MySQL 5.7 (and some of them are indeed password related).


Definitely what bit me I guess.

I usually forget to do the mysqldump before upgrading mysql only to see that my old data is not migrated. Shouldn't be a surprise, but how often do you upgrade to a new version? Then, I resort to all the "cp -R" commands, etc.. In this case, that method didn't work due to the other changes you noted.

Thanks again!

- mike
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic