LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Server (https://www.linuxquestions.org/questions/linux-server-73/)
-   -   How to import old MySQL data into mariadb? (https://www.linuxquestions.org/questions/linux-server-73/how-to-import-old-mysql-data-into-mariadb-4175608167/)

Doug Hutcheson 06-19-2017 03:11 AM

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

jareklakman 06-19-2017 05:31 AM

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.

Habitual 06-19-2017 07:59 AM

/usr/lib/mysql/db_name/

Doug Hutcheson 06-19-2017 10:17 PM

Quote:

Originally Posted by Habitual (Post 5724314)
/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.

Doug Hutcheson 06-19-2017 10:19 PM

Quote:

Originally Posted by jareklakman (Post 5724271)
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-)

Doug Hutcheson 06-20-2017 12:06 AM

Quote:

Originally Posted by Doug Hutcheson (Post 5724650)
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-[

Habitual 06-20-2017 10:56 AM

Quote:

Originally Posted by Doug Hutcheson (Post 5724647)
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?

Doug Hutcheson 06-20-2017 05:19 PM

Quote:

Originally Posted by Habitual (Post 5724826)
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

Habitual 06-20-2017 05:39 PM

Quote:

Originally Posted by Doug Hutcheson (Post 5724962)
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!

Doug Hutcheson 06-21-2017 01:28 AM

Quote:

Originally Posted by Habitual (Post 5724969)
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

jareklakman 06-21-2017 03:07 AM

make fresh backup now, resulting in sql commands, not bin files ;)
Good luck!

Habitual 06-21-2017 05:41 AM

Code:

mysqldump -uroot -p phoenix > /path/to/save/$(date +"%F")-phoenix.sql

Doug Hutcheson 06-21-2017 06:20 PM

Quote:

Originally Posted by jareklakman (Post 5725094)
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-)

Doug Hutcheson 06-21-2017 06:23 PM

Quote:

Originally Posted by Habitual (Post 5725123)
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.

Habitual 06-21-2017 07:59 PM

I cannot tell, sorry.


All times are GMT -5. The time now is 11:34 AM.