• Skip to main content
  • Skip to primary sidebar

Victor Font Consulting Group, LLC

The DEX Intranet Specialists

  • 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

Recover MySQL Tables after Upgrade/OSX

August 4, 2014 By Victor M. Font Jr.

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

Filed Under: Code Snippet, How To, MySQL, OSX, Tutorial Tagged With: Code Snippet, How To, MySQL, OSX, Tutorial

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.

Primary Sidebar

Shopping Cart

Books

  • Ultimate Guide to the SDLC front cover The Ultimate Guide to the SDLC
    Rated 5.00 out of 5
    $74.95
  • Winning With WordPress Basics 2nd Edition Winning With WordPress Basics 2nd Edition $19.95

Recent Articles

  • Protected: WordPress Database Modernization Blueprint
  • Social Media Management
  • Site Growth Automation
  • GDPR Compliance Monitoring
  • Digital Strategy Accelerator

Top 10 Article Categories

Best Practice Code Snippet Computers and Internet Genesis How To Leadership Programming Servant Leadership Tutorial WordPress