• Skip to main content

Victor Font Consulting Group, LLC

The DEX Intranet Specialists

Call Us:

+1 919-604-5828

  • Home
  • Care Plans
    • Care Articles
    • Optional Subscriptions
  • Consultations
  • Products
    • Code Snippets
    • Public GitHub Repositories
    • Gist Snippets
    • Pastebin Snippets (Free)
    • Free Plugins
  • FAQs
  • Support
    • Graphic Design
  • Contact
    • Speakers
    • Portfolio
  • Resources
    • Free WordPress Video Training
    • Tutorials
    • Articles
    • Cybersecurity
    • EU Referral Network
You are here: Home / Code Snippet / Recover MySQL Tables after Upgrade/OSX

Recover MySQL Tables after Upgrade/OSX

By Victor M. Font Jr.
August 4, 20148 Comments

powered-by-mysqlMySQL✝ is the database that powers WordPress enabled websites. Other applications which use MySQL databases include: TYPO3, MODx, Joomla, phpBB, MyBB, Drupal and many others. It's a central component of the broadly used LAMP open source web application software stack. LAMP is an acronym for “Linux, Apache, MySQL, Perl/PHP/Python." As such, it is either the 2nd or 3rd most popular database system in the world, depending on who's counting that is.

Application developers frequently setup a LAMP stack on their laptops or home computers so they can work when they are at their creative best, which means anytime of the day or night when inspiration strikes them. If you're anything like me or some of my developer friends, you probably like to keep your software updated with the latest versions or keep multiple versions of software running so you can support specific client installations.

My LAMP stack, which I use for WordPress and PHP development projects, is setup on my MacBook Pro with retina display. OSX comes with a version of the Apache web server preinstalled, but to complete the LAMP stack you have to install MySQL and PHP yourself. There are plenty of instructions on the web for setting up a development environment on OSX. A lot of these instructions reference Homebrew or some other package installer, some even walk you through compiling binaries for yourself. I don't like things to get that complicated. My personal preference is to install the pre-compiled PHP binaries available from http://php-osx.liip.ch/ and the MySQL Community Edition DMG archive.

The Mystery

When I setup MySQL for the first time on this MacBook, the current release was 5.6.16. Since then, there have been four minor point releases. The most current version as of this article is 5.6.20. You would think that upgrading MySQL would be a straightforward process. It should be as simple as downloading the latest stable DMG archive and running it. But things aren't always that simple, are they? If you do this, you will find that when you start the new version your working data tables have mysteriously disappeared!

How It Happens

When you run the MySQL DMG archive, the package installs the software in ~/usr/local/ in a directory named after the version release. For example, version 5.6.16 is installed in ~/usr/local/mysql-5.6.16-osx10.7-x86_64; likewise, version 5.6.20 is in ~/usr/local/mysql-5.6.20-osx10.8-x86_64.

The installation scripts create a complete file structure for the new version in its new directory. This includes the directory where MySQL stores its databases and tables, which is appropriately named “data." The install process does not migrate the old data to the new directory structure. You have to do this yourself. Fortunately, while the install package deletes the older version's binaries, it leaves the old data directory and my.cnf file intact. My.cnf is the MySQL configuration file that contains any environment customizations you may have made.

The Fix

To migrate the old database to the new version, first make sure MySQL is not running:

1. Launch a terminal window and enter
sudo /usr/local/mysql/support-files/mysql.server stop
2. Run the DMG Archive to install the new version of MySQL
 
3. Change the name of the new data directory to dataold
sudo mv /usr/local/mysql-5.6.20-osx10.8-x86_64/data /usr/local/mysql-5.6.20-osx10.8-x86_64/dataold
4. Copy the old databases to the new location
sudo cp -rf /usr/local/mysql-5.6.16-osx10.7-x86_64/data /usr/local/mysql-5.6.20-osx10.8-x86_64/
5. Set the correct ownership of the copied directory to _mysql
sudo chown -R _mysql:_mysql /usr/local/mysql-5.6.20-osx10.8-x86_64/data
6. Restart MySQL
sudo /usr/local/mysql/support-files/mysql.server start

These steps are all that I needed to upgrade from 5.6.16 to 5.6.20. Sometimes you may run into a specific situation where you have to reset the root password, run mysql_upgrade or repair the tables. Also, if you have customizations in the old my.cnf file, you'll need to copy those over into the new my.cnf file as well.

An Alternative

Of course there's another way to migrate your data. You could just as easily dump your data from the old MySQL before you upgrade. Then after the upgrade is installed, import the data into the new installation. I never remember to do that though. So I inevitably have to go through these steps.

✝The official way to pronounce “MySQL" is “My ESS QUE ELL" (not “my sequel")

  • 2shares
  • Facebook0
  • Twitter0
  • Pinterest0
  • LinkedIn2
  • Print
  • SMS0

About Victor M. Font Jr.

Victor M. Font Jr. is an award winning author, entrepreneur, and Senior IT Executive. A Founding Board Member of the North Carolina Executive Roundtable, he has served on the Board of Advisors, of the North Carolina Technology Association, the International Institute of Business Analysis, Association of Information Technology Professionals, Toastmasters International, and the North Carolina Commission for Mental Health, Developmental Disabilities, and Substance Abuse Services. He is author of several books including The Ultimate Guide to the SDLC and Winning With WordPress Basics, and Cybersecurity.

Reader Interactions

VictorFont.com runs on the Genesis Framework

Genesis FrameworkThe Genesis Framework empowers you to quickly and easily build incredible websites with WordPress. Genesis provides the secure and search-engine-optimized foundation that takes WordPress to places you never thought it could go.

Check out the incredible features and the selection of designs. It's that simple—start using Genesis now!

Click here to download The Genesis Guide for Absolute Beginners (PDF - 1.4 MB)

Leave a Reply Cancel reply

Your email address and website will not be published. Required fields are marked *
Posting a comment means that you agree with and accept our Comment & Product Review Policy

Comments

  1. Harsha

    November 4, 2016 at 8:43 am

    Thanks a ton mate, You saved my life

    Reply
  2. Fernando Alves Machado

    May 22, 2016 at 5:53 am

    Thanks. Very helpfull

    Reply
  3. Martin

    February 21, 2016 at 2:50 pm

    Another one who wants to say: Thank You!
    First time upgrading a MySQL server on Mac OSX and I instantly freaked out ;-) after not seeing my databases. Your fix is easy and worked like a charm. Nice post.

    Reply
  4. Jdeme

    June 28, 2015 at 5:06 pm

    Thanks for posting this. I had slightly different versions but it worked just fine.

    Reply
    • Victor M. Font Jr.

      June 28, 2015 at 6:33 pm

      You’re welcome!

      Reply
  5. Michael

    March 4, 2015 at 3:10 pm

    Thank you so much. That worked perfect for me!

    Reply
  6. Nate Baldwin

    November 15, 2014 at 3:58 pm

    Thanks for the post – just what I needed today after upgrading MySQL on the in-house server.

    Reply
    • Victor M. Font Jr.

      November 15, 2014 at 4:08 pm

      You’re welcome Nate.

      Reply

Call: +1 919-604-5828

Send us an E-mail

Accessibility Statement | Affiliate Marketing Disclosure | Capability Statement

Cookie Policy | Comment & Product Review Policy | Privacy Policy | Site Map | Terms & Conditions

Copyright © 2003–2023 Victor M. Font Jr.

Return to top of page