Solved

Problem restoring database between mysql versions

Posted on 2006-07-01
9
632 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

749 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