LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices


Reply
  Search this Thread
Old 06-19-2017, 03:11 AM   #1
Doug Hutcheson
Member
 
Registered: Jun 2009
Location: Queensland
Distribution: Fedora 30; HP Pavilion 8Gb and Acer Aspire 16Gb; both Intel Core-i7
Posts: 285

Rep: Reputation: 22
How to import old MySQL data into mariadb?


I have several old databases recovered fom a machine that went belly-up some years ago. The data consist of table triples - eg:
tblCurrencyTransactions.frm
tblCurrencyTransactions.MYD
tblCurrencyTransactions.MYI

I have tried just putting these into the /usr/lib/mysql directory and starting mariadb. Through phpMySql I can see the tables, but any attempt to view the data returns the error "#1033 - Incorrect information in files 'tblCurrencyTransactions.frm'".

Is there any way I can massage these data into a format digestible by mariadb?

Thanks in advance for any help.
Cheers,
Doug
 
Old 06-19-2017, 05:31 AM   #2
jareklakman
LQ Newbie
 
Registered: Jun 2017
Posts: 14

Rep: Reputation: Disabled
Try "insert" them into old version of database (same that they was used), then make dump or other "export" to sql commands and run it with new engine.
 
Old 06-19-2017, 07:59 AM   #3
Habitual
LQ Veteran
 
Registered: Jan 2011
Location: Abingdon, VA
Distribution: Catalina
Posts: 9,374
Blog Entries: 37

Rep: Reputation: Disabled
/usr/lib/mysql/db_name/
 
Old 06-19-2017, 10:17 PM   #4
Doug Hutcheson
Member
 
Registered: Jun 2009
Location: Queensland
Distribution: Fedora 30; HP Pavilion 8Gb and Acer Aspire 16Gb; both Intel Core-i7
Posts: 285

Original Poster
Rep: Reputation: 22
Quote:
Originally Posted by Habitual View Post
/usr/lib/mysql/db_name/
Thanks Habitual: close, but no cigar this time:
Code:
MariaDB [phoenix]> SHOW TABLES;
+------------------------------+
| Tables_in_phoenix            |
+------------------------------+
| tblTransactionType           |
| tblTransactions              |
| tblXRefCustomerAnalysisCodes |
| tblXRefStockAnalysisCodes    |
| tbl_SiteParameters           |
| tbl_TillParameters           |
| tbl_TillPriceLevels          |
+------------------------------+
7 rows in set (0.00 sec)

MariaDB [phoenix]> select * from tblTransactionType;
ERROR 1033 (HY000): Incorrect information in file: './phoenix/tblTransactionType.frm'
MariaDB [phoenix]>
It looks as though I will need to track down a 2004-vintage version of MySql as jareklakman suggested.

Thanks for taking the time to reply, though - it is appreciated.
 
Old 06-19-2017, 10:19 PM   #5
Doug Hutcheson
Member
 
Registered: Jun 2009
Location: Queensland
Distribution: Fedora 30; HP Pavilion 8Gb and Acer Aspire 16Gb; both Intel Core-i7
Posts: 285

Original Poster
Rep: Reputation: 22
Quote:
Originally Posted by jareklakman View Post
Try "insert" them into old version of database (same that they was used), then make dump or other "export" to sql commands and run it with new engine.
I think you are right - now why did that not occur to me? "8-[

I will have an interesting time tracking down a 2004 version of MySql. Thank goodness for Google. "8-)
 
Old 06-20-2017, 12:06 AM   #6
Doug Hutcheson
Member
 
Registered: Jun 2009
Location: Queensland
Distribution: Fedora 30; HP Pavilion 8Gb and Acer Aspire 16Gb; both Intel Core-i7
Posts: 285

Original Poster
Rep: Reputation: 22
Quote:
Originally Posted by Doug Hutcheson View Post
I will have an interesting time tracking down a 2004 version of MySql.
Hmmmm ...

I calculated that the version I was using in 2004 was probably 4.0.x. I found a source archive at http://live.dadanini.at/mysql/downlo...mysql-4.0.html, which I duly downloaded, however it will not configure on my architecture:
Code:
checking LinuxThreads... Not found
configure: error: This is a linux system and Linuxthreads was not
found. On linux Linuxthreads should be used. So install Linuxthreads
(or a new glibc) and try again. See the Installation chapter in the
Reference Manual.
As I do not have a 386 machine to fall back on, methinks this is the end of the road for the whole idea. "8-[
 
Old 06-20-2017, 10:56 AM   #7
Habitual
LQ Veteran
 
Registered: Jan 2011
Location: Abingdon, VA
Distribution: Catalina
Posts: 9,374
Blog Entries: 37

Rep: Reputation: Disabled
Quote:
Originally Posted by Doug Hutcheson View Post
Thanks Habitual: close, but no cigar this time:
Little bit more to it than that, I'm afraid.
The meta-permissions and properties for the phoenix db may be missing from the mysql db also.
You could try copying the entirety of the old /var/lib/mysql/ to the new /var/lib/mysql/
Without the "overhead" for the phoenix db, it's a crap shoot.

Can you desc anything on the phoenix db?
Can you export it from there? mysqldump it?
 
Old 06-20-2017, 05:19 PM   #8
Doug Hutcheson
Member
 
Registered: Jun 2009
Location: Queensland
Distribution: Fedora 30; HP Pavilion 8Gb and Acer Aspire 16Gb; both Intel Core-i7
Posts: 285

Original Poster
Rep: Reputation: 22
Quote:
Originally Posted by Habitual View Post
You could try copying the entirety of the old /var/lib/mysql/ to the new /var/lib/mysql/
Without the "overhead" for the phoenix db, it's a crap shoot.
Hmmm ... good thought. I will trawl through my remaining backups from that era and see if I have /var/lib/mysql somewhere.

Thanks for the suggestion "8-)

Cheers,
Doug
 
Old 06-20-2017, 05:39 PM   #9
Habitual
LQ Veteran
 
Registered: Jan 2011
Location: Abingdon, VA
Distribution: Catalina
Posts: 9,374
Blog Entries: 37

Rep: Reputation: Disabled
Quote:
Originally Posted by Doug Hutcheson View Post
Hmmm ... good thought. I will trawl through my remaining backups from that era and see if I have /var/lib/mysql somewhere.

Thanks for the suggestion "8-)

Cheers,
Doug
Welcome, but I mis-spoke, the mysql db itself is in /var/lib/mysql/mysql/
Additionally, you could attempt a table repair on the new://var/lib/mysql/phoenix/
That may give you more than a generic "Incorrect information in file" blurb.
But I reckon it is progress!
 
Old 06-21-2017, 01:28 AM   #10
Doug Hutcheson
Member
 
Registered: Jun 2009
Location: Queensland
Distribution: Fedora 30; HP Pavilion 8Gb and Acer Aspire 16Gb; both Intel Core-i7
Posts: 285

Original Poster
Rep: Reputation: 22
Quote:
Originally Posted by Habitual View Post
the mysql db itself is in /var/lib/mysql/mysql/
By an amazing stroke of luck, I found a 2004 backup an a set of DVDs of which the other three had become unreadable! I copied var/lib/mysql content over my current version and, equally amazingly, the data were legible and mariadb was happy to play with them.

Thanks again for the help and for putting me on the right path. LQ rules - again!

Cheers,
Doug
 
Old 06-21-2017, 03:07 AM   #11
jareklakman
LQ Newbie
 
Registered: Jun 2017
Posts: 14

Rep: Reputation: Disabled
make fresh backup now, resulting in sql commands, not bin files
Good luck!
 
Old 06-21-2017, 05:41 AM   #12
Habitual
LQ Veteran
 
Registered: Jan 2011
Location: Abingdon, VA
Distribution: Catalina
Posts: 9,374
Blog Entries: 37

Rep: Reputation: Disabled
Code:
mysqldump -uroot -p phoenix > /path/to/save/$(date +"%F")-phoenix.sql
 
Old 06-21-2017, 06:20 PM   #13
Doug Hutcheson
Member
 
Registered: Jun 2009
Location: Queensland
Distribution: Fedora 30; HP Pavilion 8Gb and Acer Aspire 16Gb; both Intel Core-i7
Posts: 285

Original Poster
Rep: Reputation: 22
Quote:
Originally Posted by jareklakman View Post
make fresh backup now, resulting in sql commands, not bin files
Good luck!
Thanks for the suggestion. I tried that via phpMyAdmin and the process grew to consume all memory after a long time and I had to kill the process. Memory failed to free, so it called for a reboot. After reboot, phpMyAdmin reported all sorts of errors, so I removed mariadb and reinstalled it, which gave me a working mariadb but back to being unable to read the old data!

I will start again this morning after a strong, hot coffee. "8-)
 
Old 06-21-2017, 06:23 PM   #14
Doug Hutcheson
Member
 
Registered: Jun 2009
Location: Queensland
Distribution: Fedora 30; HP Pavilion 8Gb and Acer Aspire 16Gb; both Intel Core-i7
Posts: 285

Original Poster
Rep: Reputation: 22
Quote:
Originally Posted by Habitual View Post
Code:
mysqldump -uroot -p phoenix > /path/to/save/$(date +"%F")-phoenix.sql
Thanks Habitual. My attempt to do this via phpMyAdmin was a minor disaster, so I will try your command line method this morning. I will keep you up to date with progress - or otherwise.

By the way, can you tell from the below, what version of MySql these files 'belong' to?
Code:
[root@womble lib]# file mysql_old/mysql/*
mysql_old/mysql/columns_priv.frm: MySQL table definition file Version 7, type MYISAM, MySQL version 0
mysql_old/mysql/columns_priv.MYD: data
mysql_old/mysql/columns_priv.MYI: MySQL MyISAM index file Version 1, 5 key parts, 0 unique key parts, 1 keys, 6 records, 0 deleted records
mysql_old/mysql/db.frm:           MySQL table definition file Version 7, type MYISAM, MySQL version 0
mysql_old/mysql/db.MYD:           data
mysql_old/mysql/db.MYI:           MySQL MyISAM index file Version 1, 4 key parts, 0 unique key parts, 2 keys, 10 records, 0 deleted records
mysql_old/mysql/func.frm:         MySQL table definition file Version 7, type MYISAM, MySQL version 0
mysql_old/mysql/func.MYD:         empty
mysql_old/mysql/func.MYI:         MySQL MyISAM index file Version 1, 1 key parts, 0 unique key parts, 1 keys, 0 records, 0 deleted records
mysql_old/mysql/host.frm:         MySQL table definition file Version 7, type MYISAM, MySQL version 0
mysql_old/mysql/host.MYD:         data
mysql_old/mysql/host.MYI:         MySQL MyISAM index file Version 1, 2 key parts, 0 unique key parts, 1 keys, 4 records, 0 deleted records
mysql_old/mysql/tables_priv.frm:  MySQL table definition file Version 7, type MYISAM, MySQL version 0
mysql_old/mysql/tables_priv.MYD:  data
mysql_old/mysql/tables_priv.MYI:  MySQL MyISAM index file Version 1, 5 key parts, 0 unique key parts, 2 keys, 6 records, 0 deleted records
mysql_old/mysql/user.frm:         MySQL table definition file Version 7, type MYISAM, MySQL version 0
mysql_old/mysql/user.MYD:         data
mysql_old/mysql/user.MYI:         MySQL MyISAM index file Version 1, 2 key parts, 0 unique key parts, 1 keys, 12 records, 0 deleted records
[root@womble lib]#
Cheers,
Doug.

Last edited by Doug Hutcheson; 06-21-2017 at 06:28 PM.
 
Old 06-21-2017, 07:59 PM   #15
Habitual
LQ Veteran
 
Registered: Jan 2011
Location: Abingdon, VA
Distribution: Catalina
Posts: 9,374
Blog Entries: 37

Rep: Reputation: Disabled
I cannot tell, sorry.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql import without overwriting existing data surferboy Linux - General 1 12-19-2012 08:51 AM
Import data from MySQL to MSSQL xeon123 Linux - General 2 05-06-2010 11:32 AM
import data back to mysql alaios Linux - General 2 02-27-2007 08:52 AM
import data to mysql server alaios Linux - Software 1 05-25-2005 12:37 AM
mysql data import bijuhpd Linux - Newbie 1 03-05-2005 08:01 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Server

All times are GMT -5. The time now is 06:36 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration