Need Help restoring mysql dump

[welkin]

Hi there,

i have a serious problem. I tried to update from 1.4.4 to 1.6 but the database has grown way to big ( around 40 gig ). I did a dump to restore the database if something fails. Well something did fail : i tried to switch to a distributed setup but the conversion of the database lasted over 12hours and then failed. Now i'm trying to restore the dump but it took already around 24hours and still is not ready ( no it's not stalled ). Does anyone know a possibility to drop certain tables from a dump? So i can drop all the history data and have only the configuration restored. Please help this is very urgent.

regards
welkin

[gjtje]

You could remove all lines containing the insert into history statement with sed.

[welkin]

well the idea of processing a 12Gig text file with "sed" sounds not that good to me. Today i started building up a completly new zabbix server. I have no idea about a reasonable size for a mysql dump. Is 12 GiG really that much? The server that tries to restore the dump is a dual intel xeon 3GHZ with 4GIG Ram and the restore is still running . I defently need to adjust my my.cnf and maybe splitting my dumps. Has anyone some backup hints?

regards
welkin

[ngarnier]

Export all tables except history* and trends*, create the schema, images and data with zabbix 1.4 scripts, import your dump without history and trends, execute migration script to 1.6 schema and start zabbix server.

Il do this to test 1.6 version with all my configuration without the history data. All were OK.

My dump were only 35 M with a 15 Go database.

[gjtje]

When you google on mysql import large database you get some hints to speed up the process but it will always take quite some time.

Maybe something for the future.
Separate the configuration from the data database, that way you can restore the configuration database, enable the system and import the history while it's running. A long import isn't really a problem but not having any monitoring for a long period is not so good.