Solved

Problem restoring database between mysql versions

Posted on 2006-07-01
9
629 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
php mysql if then statement syntax 4 34
PHP loop not working 4 55
SQL inner join confusion 15 42
RDBMS and No sql database 4 43
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
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…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now