DEV Community

Felice Forby
Felice Forby

Posted on

Using MySQL with Rails 6 and installing MySQL on Mac (macOS Mojave)

#rails #mysql

I recently had to switch databases from Sqlite to MySQL after already creating a Rails app. I also had to install MySQL on my Mac.

This article goes over how I did this and fixed a weird OS-related error I got when trying to install the mysql gem.

Install MySQL on your Mac (with homebrew)

If you use homebrew for package management, you can install MySQL by using the following command on the command line:

brew install mysql
Enter fullscreen mode Exit fullscreen mode

After it finishes installing, make sure you go over the instructions that homebrew gives you:

We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start
Enter fullscreen mode Exit fullscreen mode

You can set up a password with the mysql_secure_installation command.

You can start up MySQL right away with the brew services start mysql command.

Next, let's set up Rails to use MySQL!

Use MySQL with a brand new Rails app

If you haven't created your application yet, you can run the rails new command with a flag that configures your database to use MySQL instead of the default Sqlite:

rails new appname -d mysql
Enter fullscreen mode Exit fullscreen mode

That will set up the Gemfile to use the mysql2 gem and make a config/database.yml with some default MySQL configurations.

Switching databases in an existing Rails 6 application

Rails 6 adds the handy rails db:system:change command that will automatically create the code needed to switch databases, which involves editing the config/database.yml file and adding the mysql2 gem to the Gemfile.

Use the following command from within your Rails application directory (you can also change to other databases, too. See [here](https://gorails.com/episodes/rails-6-db-system-change-command]:

rails db:system:change --to=mysql
Enter fullscreen mode Exit fullscreen mode

It will ask you if you want to overwrite the config/database.yml file. Type y and press enter and you'll see that Rails has updated the following files:

force  config/database.yml
 gsub  Gemfile
 gsub  Gemfile
Enter fullscreen mode Exit fullscreen mode

This adds the mysql gem to the Gemfile. Before installing it, open up the Gemfile, look for the line that starts with gem mysql, and check which version of the gem it's using. It may be an older version, so you can check the Ruby Gems directory for the newest version and use that instead if you like.

Run bundle install to install the mysql2 gem. If everything goes well, all you need to do is create the database (skip down to the "Create the database" section below). I ran into an error, which I'll discuss below.

Manually configure Rails for MySQL

To manually set up MySQL, first, remove the gem sqlite3 line from the Gemfile and add gem mysql2 in its place.

Configure the config/database.yml like below. These are just basic configurations provided by Rails (some comments cut out to save space), but adjust as needed:

# config/database.yml

# MySQL. Versions 5.5.8 and up are supported.
#
# Install the MySQL driver
#   gem install mysql2
#
# Ensure the MySQL gem is defined in your Gemfile
#   gem 'mysql2'
#
# And be sure to use new-style password hashing:
#   https://dev.mysql.com/doc/refman/5.7/en/password-hashing.html
#
default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password:
  socket: /tmp/mysql.sock

development:
  <<: *default
  database: myapp_development

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: myapp_test

production:
  <<: *default
  database: myapp_production
  username: myapp
  password: <%= ENV['PACHISLO_DATABASE_PASSWORD'] %>
Enter fullscreen mode Exit fullscreen mode

Error when installing the mysql2 gem

When trying to install the mysql gem, I got a crazy long error like this:

ERROR:  Error installing mysql2:
    ERROR: Failed to build gem native extension.

    current directory: /Users/morinoko/.rbenv/versions/2.6.3/lib/ruby/gems/2.6.0/gems/mysql2-0.5.2/ext/mysql2
/Users/morinoko/.rbenv/versions/2.6.3/bin/ruby -I /Users/morinoko/.rbenv/versions/2.6.3/lib/ruby/2.6.0 -r ./siteconf20190926-66829-1k4yip5.rb extconf.rb
checking for rb_absint_size()... yes
checking for rb_absint_singlebit_p()... yes
checking for rb_wait_for_single_fd()... yes
-----
Using mysql_config at /usr/local/bin/mysql_config
-----
checking for mysql.h... yes
checking for errmsg.h... yes
checking for SSL_MODE_DISABLED in mysql.h... yes
checking for SSL_MODE_PREFERRED in mysql.h... yes
checking for SSL_MODE_REQUIRED in mysql.h... yes
checking for SSL_MODE_VERIFY_CA in mysql.h... yes
checking for SSL_MODE_VERIFY_IDENTITY in mysql.h... yes
checking for MYSQL.net.vio in mysql.h... yes
checking for MYSQL.net.pvio in mysql.h... no
checking for MYSQL_ENABLE_CLEARTEXT_PLUGIN in mysql.h... yes
checking for SERVER_QUERY_NO_GOOD_INDEX_USED in mysql.h... yes
checking for SERVER_QUERY_NO_INDEX_USED in mysql.h... yes
checking for SERVER_QUERY_WAS_SLOW in mysql.h... yes
checking for MYSQL_OPTION_MULTI_STATEMENTS_ON in mysql.h... yes
checking for MYSQL_OPTION_MULTI_STATEMENTS_OFF in mysql.h... yes
checking for my_bool in mysql.h... no
-----
Don't know how to set rpath on your system, if MySQL libraries are not in path mysql2 may not load
-----
-----
Setting libpath to /usr/local/Cellar/mysql/8.0.17_1/lib
-----
creating Makefile

current directory: /Users/morinoko/.rbenv/versions/2.6.3/lib/ruby/gems/2.6.0/gems/mysql2-0.5.2/ext/mysql2
make "DESTDIR=" clean

current directory: /Users/morinoko/.rbenv/versions/2.6.3/lib/ruby/gems/2.6.0/gems/mysql2-0.5.2/ext/mysql2
make "DESTDIR="
compiling client.c
compiling infile.c
compiling mysql2_ext.c
compiling result.c
compiling statement.c
linking shared-object mysql2/mysql2.bundle
ld: library not found for -lssl
clang: error: linker command failed with exit code 1 (use -v to see invocation)
make: *** [mysql2.bundle] Error 1

make failed, exit code 2

Gem files will remain installed in /Users/morinoko/.rbenv/versions/2.6.3/lib/ruby/gems/2.6.0/gems/mysql2-0.5.2 for inspection.
Results logged to /Users/morinoko/.rbenv/versions/2.6.3/lib/ruby/gems/2.6.0/extensions/x86_64-darwin-18/2.6.0-static/mysql2-0.5.2/gem_make.out
Enter fullscreen mode Exit fullscreen mode

After some googling, it seems like this error sometimes shows up if you don't have command-line tools installed (or it got removed when upgrading the mac OS).

Try to install them with:

xcode-select --install
Enter fullscreen mode Exit fullscreen mode

If it installs, that may have fixed your problem. Run bundle install again and it should work.

For me, however, I got a notice telling me that the command line tools were already installed:

xcode-select: error: command line tools are already installed, use "Software Update" to install updates
Enter fullscreen mode Exit fullscreen mode

To fix this, run the installer with

sudo open /Library/Developer/CommandLineTools/Packages/macOS_SDK_headers_for_macOS_10.14.pkg
Enter fullscreen mode Exit fullscreen mode

or

sudo installer -pkg /Library/Developer/CommandLineTools/Packages/macOS_SDK_headers_for_macOS_10.14.pkg -target /
Enter fullscreen mode Exit fullscreen mode

(I used the first command)

It turns out that this headers package was not included (missing?) in the newest version of Xcode, so the above command allows you to install it manually.

Now try running bundle install again and hopefully it will work (if not, you'll have to keep googling)!

Create the database

You will have to create the database from scratch and re-run any migrations if you had some already.

First, since Rails overwrote the config/database.yml file, make sure you open it up and make any necessary changes like editing the database names.

When everything is configured correctly, create the database:

# Create the database
rails db:create

#=> Created database 'mydatabase_development'
#=> Created database 'mydatabase_pachislo_test'

# Run migrations
rails db:migrate
Enter fullscreen mode Exit fullscreen mode

Now you should be able to boot up your application with rails server!

References

Top comments (5)

Collapse
 
luciagirasoles profile image
Mayra Navarro • • Edited

Thank you!
I had to run:
brew reinstall openssl && brew link openssl --force
bundle config --local build.mysql2 "--with-ldflags=-L/usr/local/opt/openssl/lib --with-cppflags=-I/usr/local/opt/openssl/include"

Just in case someone needs it in the future

Collapse
 
morinoko profile image
Felice Forby •

Thanks for your extra bit of info! <3

Collapse
 
bastianhilton profile image
Sebastian hilton •

thanks so much for the detail post on how to create a project with mysql, how to add to existing and adding so much details. Don't find to many articles like this from the tech community. Definetly adding to my faves.

Collapse
 
priteshjain profile image
Pritesh Jain •

Saved my day!! Specially the installer once.
thanks

Collapse
 
morinoko profile image
Felice Forby •

Yay! Glad it helped!

Read next

jetthoughts_61 profile image

Upgrading PostgreSQL on Heroku: A Step-by-Step Guide

JT Dev -

ftisiot profile image

How to CONCAT in MySQL

Francesco Tisiot -

d4be4st profile image

Publish/Subscribe with Sidekiq

štef -

joshdevhub profile image

Configuring IRB autocomplete

Josh Smith -