LinuxQuestions.org - [SOLVED] How to import old MySQL data into mariadb?

- 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.

Doug Hutcheson

06-21-2017 08:20 PM

Quote:

Originally Posted by Habitual (Post 5725382)

I cannot tell, sorry.

Thanks anyway. I will keep bashing away until I beat it into submission. "8-)

Doug Hutcheson

06-22-2017 03:07 AM

Quote:

Originally Posted by Doug Hutcheson (Post 5725396)

I will keep bashing away until I beat it into submission.

Well, I did a fresh installation of mariadb, copied the database directories from the archive into the current /var/lib/mysql and everything immediately worked! It seems the structure of the file triplets from the archive is still understood by the latest mariadb. I took the good advice above and immediately ran a dump from the command line, which proceeded without any problems and the resulting SQL files are as expected.

Many thanks for all the help.
Kind regards,
Doug

jareklakman

06-22-2017 03:42 AM

Your file listing give me an idea, that current MariaDB uses InnoDB as default.
Read here https://dev.mysql.com/doc/refman/5.7...ge-engine.html
and maybe here https://dev.mysql.com/doc/refman/5.7...databases.html
and maybe it helps you.

Habitual

06-22-2017 05:54 AM

Glad it worked out.

Doug Hutcheson

06-22-2017 06:11 PM

Quote:

Originally Posted by jareklakman (Post 5725532)

MariaDB uses InnoDB as default

Thanks for the links jareklakman. I will read and digest after another cup of coffee, but it looks like very useful information.
Kind regards,
Doug

Doug Hutcheson

06-22-2017 06:21 PM

Quote:

Originally Posted by Habitual (Post 5725586)

inxi -Fszr -c0 | nc termbin.com 9999

Hmmm ... my Fedora 25 has neither inxi nor termbin. If I were to install them and run your command, what would it do? I gather inxi reports hardware configuration, but why termbin?

If nothing else, you have given me something to research, but it would be good if you could give me a head start in understanding it. "8-)

Kind regards,
Doug.

Habitual

06-23-2017 03:42 AM

Quote:

Originally Posted by Doug Hutcheson (Post 5725885)

Using

Code:

inxi -Fsz -c0 | nc termbin.com 9999

produces this http://termbin.com/805w
termbin is a site, not a program.
inxi is the program. Where it is on your distro? IDK sorry.
Details on how, why, and what for termbin are at termbin.com. (not my site)
Links are easier on the board as some new members have trouble with [code][/code] markup(s) on textual output.
So asking them to paste a link is easier than trying to implement a "how to use [code][/code] markups" on vBulletin software.

Doug Hutcheson

06-23-2017 09:35 PM

Quote:

Originally Posted by Habitual (Post 5726009)

Using

Code:

inxi -Fsz -c0 | nc termbin.com 9999

produces this http://termbin.com/805w
termbin is a site, not a program.
inxi is the program. Where it is on your distro?

The light daws - somewhat. "8-)

I found inxi on my system - I must have mistyped the name before.

The output to my terminal, without piping to termbin, is as follows, so what is termbin needed for? "8-/

Code:

System:    Host: womble Kernel: 4.11.4-200.fc25.x86_64 x86_64 (64 bit) Desktop: N/A

          Distro: Fedora release 25 (Twenty Five)

Machine:  Device: laptop System: Acer product: Aspire E1-571 v: V2.15

          Mobo: Acer model: EA50_HC_CR v: Type2 - Board Version UEFI: Insyde v: V2.15 date: 03/11/2013

Battery    BAT1: charge: 37.4 Wh 100.0% condition: 37.4/44.0 Wh (85%)

CPU:      Quad core Intel Core i7-3632QM (-HT-MCP-) cache: 6144 KB 

          clock speeds: max: 3200 MHz 1: 1200 MHz 2: 1417 MHz 3: 1203 MHz 4: 1366 MHz 5: 1201 MHz 6: 1570 MHz

          7: 1207 MHz 8: 1293 MHz

Graphics:  Card: Intel 3rd Gen Core processor Graphics Controller

          Display Server: X.org 1.19.3 driver: N/A tty size: 172x44 Advanced Data: N/A for root

Audio:    Card Intel 7 Series/C216 Family High Definition Audio Controller driver: snd_hda_intel

          Sound: Advanced Linux Sound Architecture v: k4.11.4-200.fc25.x86_64

Network:  Card-1: Broadcom Limited NetLink BCM57785 Gigabit Ethernet PCIe driver: tg3

          IF: enp2s0f0 state: down mac: <filter>

          Card-2: Qualcomm Atheros AR9485 Wireless Network Adapter driver: ath9k

          IF: wlp3s0 state: up mac: <filter>

Drives:    HDD Total Size: 1000.2GB (19.6% used)

          ID-1: /dev/sda model: ST1000LM024_HN size: 1000.2GB

Partition: ID-1: / size: 50G used: 39G (82%) fs: ext4 dev: /dev/dm-0

          ID-2: /boot size: 976M used: 159M (18%) fs: ext4 dev: /dev/sda2

          ID-3: /home size: 859G used: 138G (17%) fs: ext4 dev: /dev/dm-2

          ID-4: swap-1 size: 8.32GB used: 0.00GB (0%) fs: swap dev: /dev/dm-1

RAID:      No RAID devices: /proc/mdstat, md_mod kernel module present

Sensors:  System Temperatures: cpu: 47.0C mobo: N/A

          Fan Speeds (in rpm): cpu: N/A

Info:      Processes: 352 Uptime: 2 days Memory: 5437.9/7799.4MB Client: Shell (bash) inxi: 2.3.8

Thanks for pointing me to yet another interesting piece of the Linux jigsaw.

Cheers,
Doug.

All times are GMT -5. The time now is 04:17 PM.