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?

Dave DeLong
  • 323
  • 2
  • 10
  • 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 '11 at 17:15

2 Answers2

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!
Dave DeLong
  • 323
  • 2
  • 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

RolandoMySQLDBA
  • 16,544
  • 3
  • 48
  • 84
  • 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 '11 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. – RolandoMySQLDBA Dec 24 '11 at 20:41
  • You can completely cleanup InnoDB infrastructure after all is said and done : See my post in StackOverflow : http://stackoverflow.com/a/4056261/491757 – RolandoMySQLDBA Dec 24 '11 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 '11 at 20:47