Solved

Problem restoring database between mysql versions

Posted on 2006-07-01
9
630 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
  • 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
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!

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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