From: Rick James
Date: November 30 2010 1:17am
Subject: Re: Saving an old database from a dead server
List-Archive: http://lists.mysql.com/replication/2016
Message-Id: <4CF450BC.8010408@yahoo-inc.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Depending on what is in the dump, you may need to
   CREATE DATABASE databasename
first, then do
   mysql databasename <dumpfile

On 11/29/10 3:52 PM, Wagner Bianchi wrote:
> Great! Good job! See u!
>
> Best regards.
> --
> WB
>
>
> 2010/11/29 Magnus Ekhall<koma@stripped>
>
>> Yes, the backup file contains a lot of CREATE TABLEs and INSERTs, so it
>> seems like the db have MyISAM tables.
>>
>> I don't think there is a mixture. For each table in the database there
>> is a frm, MYD and a MYI. No extra files apart from a db.opt which just
>> contains some options.
>>
>> The size of the dump looks correct compared to a (much too old) backup I
>> made a year ago when the server was functioning correctly.
>>
>> Regards,
>> Magnus
>>
>>
>>
>>
>> mån 2010-11-29 klockan 18:28 -0200 skrev Wagner Bianchi:
>>> Well, that is a good explanation:
>>>
>>> =>  MYI, MYD and frm files are files used by MyISAM tables. The key is,
>> when
>>> you ran mysqldump, the backup file was filled by CREATE TABLEs and many
>>> INSERTs?
>>>
>>> If yes ->  probably this database have MyISAM tables
>>> If not ->  INSERTs should not appear in the backup file;
>>>
>>> So, other problem, do you know if you have a mixture of MyISAM and InnoDB
>>> tabels?
>>>
>>> if yes ->  Myisam TABLES will have data into MYI and MYD at this time;
>>> if not ->  You will be required to copy "cp" theInnoDB's tabelspace to
>>> recovery its data (all InnoDB table's data are inside tbs = ibdata)
>>>
>>> Best regards.
>>> --
>>> WB
>>>
>>> 2010/11/29 Magnus Ekhall<koma@stripped>
>>>
>>>> Hmm.
>>>>
>>>> The database directory I copied contains a lot of fmr, MYD and
>>>> MYI-files.
>>>>
>>>> I guess that makes it a MISAM database instead of innodb...
>>>>
>>>> Maybe that explains it?
>>>>
>>>> The dump I got contains data as well as table definitions for sure.
>>>>
>>>> Regards,
>>>> Magnus
>>>>
>>>> mån 2010-11-29 klockan 17:59 -0200 skrev Wagner Bianchi:
>>>>> Let me just understand what you did. Well, you just copied the
>> database
>>>>> directory containing the ".frm" files and didn', copied the iddata,
>> which
>>>> is
>>>>> the InnoDB tablespace. Am I right? If yes, you copy only tables with
>> no
>>>>> data. Is it what do you want?
>>>>>
>>>>> Best regards.
>>>>> --
>>>>> WB
>>>>>
>>>>> 2010/11/29 Magnus Ekhall<koma@stripped>
>>>>>
>>>>>> I did copy only the subdirectory of my
>>>>>> database: /var/lib/mysql/mydatabase
>>>>>>
>>>>>> ibdata1 was not included in the copy.
>>>>>>
>>>>>> I did not set innodb_file_per_table to anything, so I guess it has
>> a
>>>>>> default value.
>>>>>>
>>>>>> All this was on a fresh VirtualBox install of Ubuntu server 6.06.
>>>>>>
>>>>>> I could have sworn that the mysql version was 4.1 (that was what
>> the
>>>>>> crashed server ran, and what I aimed to install), but mysqld
>> --version
>>>>>> gives:
>>>>>>
>>>>>> Ver 5.0.22-Debian_0ubuntu6.06.15-log
>>>>>>
>>>>>> Just copying that subdirectory, setting the correct ownership of
>> the
>>>>>> files and then running
>>>>>>
>>>>>> mysqldump -p mydatabase>  dump
>>>>>>
>>>>>> gave me a nice dump which seems to contain what it should!
>>>>>>
>>>>>> That's about it really.
>>>>>>
>>>>>> Maybe copying the whole /var/lib/mysql directory would have worked
>>>>>> directly on a new, modern version of mysql?
>>>>>>
>>>>>> Regards,
>>>>>> Magnus
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> mån 2010-11-29 klockan 11:25 -0800 skrev Rick James:
>>>>>>> Please provide some details...
>>>>>>> Did you copy only the database subdirectory?  Or the entire tree
>> (as
>>>> I
>>>>>>> suggested)?
>>>>>>> Did your cp include ibdata1?
>>>>>>> What was the setting of innodb_file_per_table?
>>>>>>> Which 5.0.xx ?
>>>>>>>
>>>>>>> (I often get questions about backuping up InnoDB tables via cp; I
>>>> would
>>>>>>> like to know if you have found a different way that is
>> successful.)
>>>>>>> On 11/29/10 11:15 AM, Magnus Ekhall wrote:
>>>>>>>> I'm using InnoDB.
>>>>>>>>
>>>>>>>> Good news: I think I just managed to get a dump from the
>> database!
>>>>>>>> I installed an old version of Ubuntu in a VirtualBox and copied
>> the
>>>>>>>> database files to that box. mysqldump seemed to work just fine
>>>> then!
>>>>>>>> :)
>>>>>>>>
>>>>>>>> Thanks all for the many ideas and for all the help.
>>>>>>>>
>>>>>>>> Regards,
>>>>>>>> Magnus
>>>>>>>>
>>>>>>>> mån 2010-11-29 klockan 16:54 -0200 skrev Wagner Bianchi:
>>>>>>>>> As Rick said, having a cp of files, in the case that you are
>> using
>>>>>> InnoDB,
>>>>>>>>> it isn't enough. Other problem is regarding to the
>> compatibility
>>>> model
>>>>>> of
>>>>>>>>> InnoDB tablespace. The logical structure of this file have
>> been
>>>>>> changed
>>>>>>>>> since MySQL 5.0.
>>>>>>>>>
>>>>>>>>> So, are you using InnoDB or MyISAM in old database?
>>>>>>>>>
>>>>>>>>> Best regards.
>>>>>>>>> --
>>>>>>>>> WB
>>>>>>>>>
>>>>>>>>> 2010/11/29 Rick James<rjames@stripped>
>>>>>>>>>
>>>>>>>>>> Put all of
>>>>>>>>>>
>>>>>>>>>> /var/lib/mysql
>>>>>>>>>>
>>>>>>>>>> on a fresh machine; not just the database directory.  Then
>> backup
>>>> the
>>>>>>>>>> desired table(s).
>>>>>>>>>>
>>>>>>>>>> If you are using InnoDB , the directory you chose won't
>> suffice.
>>>>   If
>>>>>> it is
>>>>>>>>>> MyISAM, it should have worked.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> On 11/28/10 12:17 PM, Magnus Ekhall wrote:
>>>>>>>>>>
>>>>>>>>>>> Hello,
>>>>>>>>>>>
>>>>>>>>>>> I am trying to do a rather odd replication of a mysql
>> database
>>>> and
>>>>>> need
>>>>>>>>>>> some hints.
>>>>>>>>>>>
>>>>>>>>>>> An old server of mine died horribly during a recent power
>>>> outage.
>>>>>>>>>>> It had a mysql 4.1 server. The hardware has just given up,
>> but I
>>>>>> have
>>>>>>>>>>> managed to get all files from the harddrive.
>>>>>>>>>>>
>>>>>>>>>>> Since the server won't boot I cannot run mysqldump.
>>>>>>>>>>>
>>>>>>>>>>> I then tried just moving the database files
>>>>>>>>>>> from /var/lib/mysql/thedatabas onto a fresh install of mysql
>> (in
>>>>>> Ubuntu
>>>>>>>>>>> 10.10, so version 5.x), but that did not work.
>>>>>>>>>>>
>>>>>>>>>>> Can anyone suggest a way to get a dump of my database
>> somehow?
>>>>>>>>>>> I'm going to set up a new server running a recent version of
>>>> mysql,
>>>>>> so
>>>>>>>>>>> the equivalent of mysqldump is ideal.
>>>>>>>>>>>
>>>>>>>>>>> Any ideas at all?
>>>>>>>>>>>
>>>>>>>>>>> Regards,
>>>>>>>>>>> Magnus
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>> --
>>>>>>>>>> Rick James - MySQL Geek
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> --
>>>>>>>>>> MySQL Replication Mailing List
>>>>>>>>>> For list archives: http://lists.mysql.com/replication
>>>>>>>>>> To unsubscribe:
>>>>>>>>>>
>>>> http://lists.mysql.com/replication?unsub=wagnerbianchijr@stripped
>>>>>>>>>>
>>>>>>> --
>>>>>>> Rick James - MySQL Geek
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> MySQL Replication Mailing List
>>>>>> For list archives: http://lists.mysql.com/replication
>>>>>> To unsubscribe:
>>>>>> http://lists.mysql.com/replication?unsub=wagnerbianchijr@stripped
>>>>>>
>>>>>>
>>>>
>>>>
>>
>>

-- 
Rick James - MySQL Geek