4

I have an external hard drive that has a non-bootable clone of an old machine (that I no longer have). One of the things that was present on that machine was a MySQL installation (v 5.5.19) with several databases that I'd like to save.

I poked around and found that there's a {root}/usr/local/mysql/data directory, and that contains subdirectories that appear to correspond to the databases I'd like to save.

I copied over one of these directories to my main machine (also running MySQL 5.5.19) and got the file permissions and ownership all set back up correctly (_mysql:wheel, 0660 on the .frm files, etc).

When I log in to PHPMyAdmin now, I can see the database listed on the left, with the correct number of tables in parentheses. However, when I attempt to connect to that database, it says that there are no tables in the database.

Obviously, I've missed something. What have I missed, and is there another way to do this that might be easier? Could I, perhaps, alter the myriad of .cnf files to point to the MySQL files on my external hard drive and then use mysqldump on those? Which files would I need to alter?

1
  • thanks for asking this question. i have the exact same problem and wanted to ask it myself. my hardware got killed and most binaries are compiled to old native architecture, so there is only a little chance that they will run on the new hardware.
    – Baarn
    Dec 24, 2011 at 17:15

2 Answers 2

5

I figured it out!

For posterity, here's what I did:

  1. I killed the MySQL server
  2. I copied /usr/local/mysql/support-files/my-small.cnf to /etc/my.cnf
  3. I opened my.cnf and added this line in the [mysqld] section:

    # point the datadir to the old stuff on the external hard drive
    datadir = /Volumes/Clone/usr/local/mysql/data
    
  4. I saved and closed my.conf
  5. I restarted the MySQL server. The MySQL pane in System Preferences had a warning about how "/Volumes/Clone/usr/local/mysql/data" didn't have the proper ownership, but I figured this was OK to ignore. It was also reassuring to see that MySQL was looking in the right place for data.
  6. I opened PHPMyAdmin and logged in.
  7. I exported the databases I cared about as .sql.gz files
  8. I re-killed the MySQL server and deleted the /etc/my.cnf file
  9. I re-started the MySQL server and logged in to PHPMyAdmin
  10. I imported my databases, and everything is looking great!
1
  • Feels great to solve it on your own, doesn't it. +1 for posting how you went about doing it !!! Dec 24, 2011 at 20:10
2

First thing you should do is this:

chown -R mysql:mysql /usr/local/mysql/data

You should find out how big the InnoDB files are

  • ibdata1
  • ib_logfile0
  • ib_logfile1

If ibdata1 is < 20M, and ib_logfile0 = 5M, then mysql was running without a my.cnf in place. You should be able to startup mysql. If they are different sizes, you need to create my.cnf with the following

[mysqld]
innodb_log_file_size=???? (file of ib_logfile0 in M)

I hope this is a good start

4
  • What is the reasoning behind the size of the ibdata1 and the contents of my.cnf? Doesn't the ibdata1 just grow with the data in the databases? And also, if you cleanly shutdown mysql, you should be able to delete (backup if you're not sure) the ib_logfile files, it'll recreate them just fine (which is also necessary if you change the innodb_log_file_size parameter).
    – arjarj
    Dec 24, 2011 at 20:39
  • It depends. If innodb_file_per_table is disabled, eveyything about InnoDB and its grandmother is in ibdata1. That's when it bloats really fast. If innodb_file_per_table is enabled, then table data and indexes resides in separate .ibd file and ibdata1 grows very slowly. Dec 24, 2011 at 20:41
  • You can completely cleanup InnoDB infrastructure after all is said and done : See my post in StackOverflow : stackoverflow.com/a/4056261/491757 Dec 24, 2011 at 20:43
  • Ah yeah, but innodb_file_per_table is not enabled by default, so I usually assume it's turned off, and see ibdata1 grow accordingly, much the horror of some administrators.
    – arjarj
    Dec 24, 2011 at 20:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .