Solved

Problem restoring database between mysql versions

Posted on 2006-07-01
9
634 Views
Last Modified: 2012-06-21
Recently, our FC4 system crashed, but we were able to create a complete tar backup while in recovery mode.  We then installed FC5 on a new system.  Everything is working except for the mysql databases.  

Here is what we did:
- After installing mysql on FC5, started mysqld.  It worked fine.
- Tarred up /var/lib/mysqld to save the db
- deleted everything in /var/lib/mysqld
- untarred the /var/lib/mysqld files from the crashed system

Now when I try to start the db,  the log shows the following:
060701 12:34:00  mysqld started
060701 12:34:00  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Warning: a page in the doublewrite buffer is not within space
InnoDB: bounds; space id 0 page number 50661, page 0 in doublewrite buf.

.............

Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbf9a3528, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8172be3
0x1f2420
0x83ae0d0
0x83ae0d0
0x83a34c1
0x839e42c
0x8393724
0x83939b0
0x8394a84
0x831908a
0x822c8e4
0x821c643
0x8171c56
0x8175325
0x225724
0x80eebf1
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
060701 12:34:01  mysqld ended

Any thoughts on what I can do to receover my databases?

Thanks for your help.

Norm
0
Comment
Question by:npollock
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 Comments
 
LVL 5

Expert Comment

by:Z03niE
ID: 17029582
How did you actually backup the database.
Is it using MySQLDump or scripting backup like the ordinary MySQL backup procedure or other method ?

I f you are trying to copy InnoDB database from the the database file on the crashed computer into the new computer then you are going nowhere.
0
 

Author Comment

by:npollock
ID: 17030384
The system was in a crashed state, so we rebooted from a Linux CD in crash recovery mode.  We then tarred up the db on disk.  That is what I restored.  Can a mysql db be restored from a tar backup?

Thanks

Norm
0
 

Author Comment

by:npollock
ID: 17033467
Ok, I finally found the answer to this on the web.  Instead of restoring the entire mysql directory, I restored the sub directories under mysql and the database now starts.

Norm
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

Author Comment

by:npollock
ID: 17033586
I jumped the gun and still need a solution.  I restored the database and ran the following commands:
mysql> use wikidb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_wikidb |
+------------------+
| archive          |
| categorylinks    |
| externallinks    |
| hitcounter       |
| image            |
| imagelinks       |
| interwiki        |
| ipblocks         |
| job              |
| logging          |
| math             |
| objectcache      |
| oldimage         |
| page             |
| pagelinks        |
| querycache       |
| recentchanges    |
| revision         |
| searchindex      |
| site_stats       |
| templatelinks    |
| text             |
| trackbacks       |
| transcache       |
| user             |
| user_groups      |
| user_newtalk     |
| validate         |
| watchlist        |
+------------------+
29 rows in set (0.00 sec)

mysql> select * from user;
ERROR 1146 (42S02): Table 'wikidb.user' doesn't exist

I get this error for every table.  Can anyone help me out?

Thanks

Norm
0
 
LVL 5

Expert Comment

by:Z03niE
ID: 17034615
If you are using InnoDB, take a look at this website, it might help.


http://dev.mysql.com/doc/connector/j/en/innodb-backup.html
0
 

Author Comment

by:npollock
ID: 17037273
I don't think we are using innodb, not even sure what it is.  I wonder if the problem is that FC5 has a newer version of mysql.  If so, how can I convert the FC4 mysql db to the FC5 db version?

Thanks

Norm
0
 
LVL 5

Accepted Solution

by:
Z03niE earned 500 total points
ID: 17039803
To find out wether your database is InnoDB or not from the MySQL command Line or any MySQL manager you are using do this :

show table status from `UrDatabase`.

See the engine field,what kind of engine you are using, is it InnoDB or MyISAM.
Usually people use these 2 common engine.

If you are using MyISAM, you can do restore database as easy as you mentioned above, no matter if you are using different version of the MySQL database.
The structure of MyISAM is usually like :

\\MySQL instalation directory\data\Database name

inside it you'll find your tables phisical files like :
table1.frm
table1.MYD
table1.MYI

In MyISAM all you have to do is copy the whole directory "Database Name" into a new engine to move the datbase.But in InnoDB it's not that easy.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question