How to reset MySQL with a fresh installation

MySql errors about socks and PID’s can be frustrating. Here’s a brute force way to fix them.

Instructions

Are you getting an error that looks like this?

$ mysql -v

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Or this?

$ mysql.server start

Starting MySQL

. ERROR! The server quit without updating PID file (/usr/local/var/mysql/Kaylas-MacBook-Pro.local.pid).

Or this?

Library not loaded: /usr/local/opt/openssl/lib/libssl.1.0.0.dylib (LoadError)
  Referenced from: /Users/brianbirdwell/dev/landcentral/lc-admin/vendor/ruby/2.1.0/extensions/x86_64-darwin-18/2.1.0-static/mysql2-0.3.20/mysql2/mysql2.bundle
  Reason: image not found - /Users/brianbirdwell/dev/landcentral/lc-admin/vendor/ruby/2.1.0/extensions/x86_64-darwin-18/2.1.0-static/mysql2-0.3.20/mysql2/mysql2.bundle

You probably need to dump your current versions of MySQL and start anew! Here are steps to fully uninstall MySQL and get it running again:

1. Uninstall MySQL from Homebrew

First, stop all versions of MySQL currently running on your machine:

$ brew services stop mysql@5.6

Then unlink MySQL:

$ brew services unlink mysql@5.6

If you receive an error, try this

brew unlink mysql@5.6

Finally, uninstall MySQL:

$ brew uninstall mysql @5.6

Repeat for mysql, mysql@5.7 and/or any other mysql versions on your machine.

2. Remove the configuration files related to MySQL from your machine

Change directories to your root:

$ cd ~/

Next, run a search for mysql in your usr directory:

$ find /usr -name mysql

Remove any directories that come up in that search (though if you have results from rbenv, bundler, or your dev directory, disregard them):

$rm -rf /usr/local/var/mysql

Finally, after you remove the directories, run another search just to make sure everything is gone:

$ find /usr -name mysql

3. Reinstall MySQL, re-create your databases, and re-populate them

You’re ready to install mysql again!

$ brew install mysql@5.6

$ brew link -—force mysql@5.6

$ brew services start mysql@5.6

$ brew services list

If brew services list shows your chosen version of mysql as started in green, you’re good to go!

To further verify, check your mysql version:

$ mysql -v

This should either return the version number or start up a mysql console for you!

NOTE: This process will delete all of the MySQL databases on your machine!

You’ll need to run rake db:create in your projects to set the databases up, and run rake db:seed or use a local snapshot to populate the database:

$ mysql -u root -h localhost <your_database_name> < ~/dev/databases/<database_snapshot_name>.sql

Potential Issues:

Library not loaded: /usr/local/opt/mysql/lib/libmysqlclient.20.dylib

You may see this error when attempting to access the database from the app or console. This likely occurs because the MySQL adapter gem is looking for a version of MySQL that was removed or is no longer being used. You can solve this one of two ways:

bundle install --force

This completely reinstalls the gems locally, which should update the MySQL connection.

Alternatively, you can find the actual directory where your libmysqlclient.dylib file exists and symlink (ln -s) the reference to the failing file to the true dylib file. Only use this if the bundle install --force is truly not an option, as this option is a much more brittle solution

IMPORTANT! Not all of our projects run on the same versions of MySQL. Sometimes we have to switch versions, and this can cause errors. If you know of a better process to handling this, please update this guide!