Posts Tagged ‘ database ’

Getting Sequel Pro to read MySQL on MacOS

As a follow-up to the previous post (Installing MySQL on MacOS with Homebrew), to get Sequel Pro working you need to do the following:

Open my.cnf (usually at /usr/local/etc) and add the following line and save:

Sign into mysql with mysql -u root -p

Set the root user password with:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '[PASSWORD]';

where [PASSWORD] is a password of your choosing.

Delete my.cnf (MySQL will regenerate it):

sudo rm -rf /usr/local/etc/my.cnf

Restart mysql:

brew services restart mysql

Now Sequel Pro should be able to log into MySQL.

Sequel Pro and MySQL connection failed
Cannot find MySQL.sock

Installing MySQL on MacOS with Homebrew

I was trying to install MySQL on MacOS and I followed the instructions to install it via Homebrew:

$ brew install mysql

Pretty easy, but then as I worked along I screwed something up where I could not load mysql in the command line. I would get the following error:

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

So I do the logical step of uninstalling and reinstalling:

$ brew uninstall mysql
$ brew install mysql

I still get the same error. Two hours of browsing StackOverflow later, I figured it out. I failed to notice the following errors during reinstallation:

2019-02-21T01:02:48.455076Z 0 [System] [MY-013169] [Server] /usr/local/Cellar/mysql/8.0.15/bin/mysqld (mysqld 8.0.15) initializing of server in progress as process 18070
2019-02-21T01:02:48.457920Z 0 [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.
2019-02-21T01:02:48.457929Z 0 [ERROR] [MY-013236] [Server] Newly created data directory /usr/local/var/mysql/ is unusable. You can safely remove it.
2019-02-21T01:02:48.457994Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-02-21T01:02:48.458203Z 0 [System] [MY-010910] [Server] /usr/local/Cellar/mysql/8.0.15/bin/mysqld: Shutdown complete (mysqld 8.0.15) Homebrew.

This is the key line:
Newly created data directory /usr/local/var/mysql/ is unusable. You can safely remove it.

What do I do? Remove that directory!

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

Then run the post install script:

$ brew postinstall mysql

And voilà, mysql loads!

$ mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.15 Homebrew

Learn from my fail.

Cloning Mongo Database From Heroku to Local

These are the steps I compiled to clone a Mongo database from Heroku to local. It is provided as-is without warranty nor guarantee nor support.

  1. Open command prompt (Windows) or Terminal (Mac/Linux) and navigate to the project’s working folder.
  2. Get the config vars from Heroku with heroku config --app <appname>
    • Find the MONGOLAB_URI value. It will be in the form of: mongodb://<username>:<password>@<url>:<port>/<database>
  3. Run the following command to make a copy of the production database to your local working directory.
    • Use the values from the config file: mongodump -h <url>:<port> -d <database> -u <username> -p <password>
    • mongodump will create the following directory to store the data: dump/<database>/
  4. Make sure your local instance of mongod is running.
  5. Drop your existing local database with the following command.
    • mongo <dbname> --eval "db.dropDatabase()"
    • <dbname> is your local database name specified in your project’s config file under MONGOLAB_URI.
  6. Use mongorestore to put the prod data into your local mongodb.
    • mongorestore -d <dbname> dump/<database>/
  7. Ensure data was restored
    • Start mongo command line interface: mongo
    • Show databases and ensure <dbname> is in the list: > show databases
    • Switch to <dbname>: > use <dbname>
    • Ensure games and players tables exist: > show collections
    • If satisfied, then exit mongo command line: > exit
  8. Run the app and see the prod data (see “Running” section below)
  9. ???
  10. Profit!