Importing a database as a data file directory

[Tim Johnson]

I have a dual-boot OS X/Ubuntu 12.04 arrangement on a mac mini. The
ubuntu system has failed and I am unable to boot it.

I have one database on the ubuntu partition that was not backed up.

I am able to mount the ubuntu partion with fuse-ext2 from Mac OS X,
thus I can read and copy the mysql data files at /var/lib/mysql on the
ubuntu partition.

I presume that I should be able to retrieve the database by just
copying it to /opt/local/var/db/mysql5 - the location of the mysql
datafiles on the mac partition - and setting ownership and
permissions.

So, this is a "Help me before I hurt myself" sort of question: Are
there any caveats and gotchas to consider?

thanks

[Jan Steinman]

Post by Tim Johnson
So, this is a "Help me before I hurt myself" sort of question: Are
there any caveats and gotchas to consider?

Do you know if the database was shut down properly? Or did Ubunto crash and die and your partition become unbootable while the database was in active use?

Either way, you need to make sure MySQL is shut down when you move the files, and then repair them after starting.

I've had good experiences moving MyISAM files that way, but bad experience moving INNODB files. I suspect the latter are more aggressively cached.

:::: Mass media must constantly manipulate and deceive us in order to sell products... The most fundamental deception perpetrated on the public is that consumption of material goods is the source of human happiness. A secondary deception is hiding the fact that such consumption leads to major collateral damage -- the possible end of human life on the planet. -- Pat Murphy
:::: Jan Steinman, EcoReality Co-op ::::

[Tim Johnson]

Post by Jan Steinman

Post by Tim Johnson
So, this is a "Help me before I hurt myself" sort of question: Are
there any caveats and gotchas to consider?

Do you know if the database was shut down properly? Or did Ubunto
crash and die and your partition become unbootable while the
database was in active use?

The database had been shut down, no symptom occurred when the OS
was booted, I just couldn't reboot (for starters)

Post by Jan Steinman
Either way, you need to make sure MySQL is shut down when you move the files, and then repair them after starting.

Good tip.

Post by Jan Steinman
I've had good experiences moving MyISAM files that way, but bad
experience moving INNODB files. I suspect the latter are more
aggressively cached.

They are MyISAM ...
Thank you

[Reindl Harald]

Post by Jan Steinman
I've had good experiences moving MyISAM files that way, but bad experience
moving INNODB files. I suspect the latter are more aggressively cached

simply no, no and no again

independent of "innodb_file_per_table = 1" there is *always* a global
table-space (ibdata1) and you just can't move around innodb databases
on file-system level - there is not but and if and it has nothing to
do with caching

if caching would matter in that context it just would not be part of
the game in case off a not running service

http://www.xaprb.com/blog/2012/09/07/measuring-free-space-in-innodbs-global-tablespace/

[Reindl Harald]

Post by Tim Johnson
I have a dual-boot OS X/Ubuntu 12.04 arrangement on a mac mini. The
ubuntu system has failed and I am unable to boot it.
I have one database on the ubuntu partition that was not backed up.
I am able to mount the ubuntu partion with fuse-ext2 from Mac OS X,
thus I can read and copy the mysql data files at /var/lib/mysql on the
ubuntu partition.
I presume that I should be able to retrieve the database by just
copying it to /opt/local/var/db/mysql5 - the location of the mysql
datafiles on the mac partition - and setting ownership and
permissions.
So, this is a "Help me before I hurt myself" sort of question: Are
there any caveats and gotchas to consider?

in case of MyISAM a no-brainer

* stop the db server
* copy the folder there
* set permissions
* start the server
* run "mysql_upgrade --force -u root -p"

well, in case of replication you might want to rebuild the slave(s) from
scratch but that was it - doing this regulary to rsync whole databases
from stopped replication slaves as well as the document root on testing
machines

[Tim Johnson]

Post by Reindl Harald

Post by Tim Johnson
I have a dual-boot OS X/Ubuntu 12.04 arrangement on a mac mini. The
ubuntu system has failed and I am unable to boot it.
I have one database on the ubuntu partition that was not backed up.
I am able to mount the ubuntu partion with fuse-ext2 from Mac OS X,
thus I can read and copy the mysql data files at /var/lib/mysql on the
ubuntu partition.
I presume that I should be able to retrieve the database by just
copying it to /opt/local/var/db/mysql5 - the location of the mysql
datafiles on the mac partition - and setting ownership and
permissions.
So, this is a "Help me before I hurt myself" sort of question: Are
there any caveats and gotchas to consider?

in case of MyISAM a no-brainer

Yup. MyISAM ...

Post by Reindl Harald
* stop the db server
* copy the folder there
* set permissions
* start the server
* run "mysql_upgrade --force -u root -p"

Great! thanks for the detail

Post by Reindl Harald
well, in case of replication you might want to rebuild the slave(s) from
scratch but that was it - doing this regulary to rsync whole databases
from stopped replication slaves as well as the document root on testing
machines

Got it.

Thank you