Wilson Mar bio photo

Wilson Mar

Hello. Hire me!

Email me Calendar Skype call 310 320-7878

LinkedIn Twitter Gitter Google+ Youtube

Github Stackoverflow Pinterest

It worked for your dad. Embrace it.


Overview

This tutorial covers the basics for getting started with MySQL on Mac OSX, PROTIPs and NOTEs are provided as instructions are presented in a hands-on way.

Content here was developed based on advice from several websites.

From MySQL to MariaDB to Aurora

MySQL was first proposed by Edgar Cord in 1970. Its “open-source” free usage made it a natural choice for other open-source frameworks such as WordPress and thus its rapid growth.

NOTE: The “My” in MySQL is named after co-founder Michael’s daughter, My, a name from a 1952 children’s book from Finland, where her family originates.

After Oracle acquired Sun (MySQL’s owner), the freedom of open-source was realized when Maria-DB was forked to create a drop-in replacement for MySQL by MySQL co-founder Michael “Monty” Widenius, who left Oracle to start a new company (Monty Program).

Many developers have migrated to MariaDB. (MariaDB 5.1.53 is based on MySQL 5.1.53). If you don’t need to first uninstall MySQL, click here for instructions on installing MariaDB, below.

On August 2016, MariaDB’s MaxScale database proxy software was put under the Business Software License, which means it’s really not fully open source.

MySQL Still Alive

MySQL is thriving.

Uber in 2016 switched from Postgres to MySQL under Schemaless. More.

  • https://www.youtube.com/watch?v=bNeZYVIfskc
  • https://www.youtube.com/watch?v=Dg76cNaeB4s

Uber’s decision is based on similar success by Pinterest and Friendfeed, who said:

“MySQL works. It doesn’t corrupt data. Replication works. We understand its limitations already. We like MySQL for storage, just not RDBMS usage patterns.

Ironically, MySQL is a good choice for fast-growth companies because of its simplicity.

Uber uses MySQL to build a key-value store (hash map) which saves any JSON data without strict schema validation, in a schemaless fashion (hence the name #schemaless). It’s much like Google Big Table.

It has append-only sharded MySQL with buffered writes to support failing MySQL masters and a publish-subscribe feature for data change notification triggers. Lastly, Schemaless supports global indexes over the data.

The key difference is that the SQL schema is not used to designate domain information such as “inventory”.

Cloud instances

There are several offerings that provide not only the database, but also automation such as monitoring, scaling to provide capacity, backup/roll-back functionality, auto-failover in several zones, auto-update of core software, etc.

AWS Aurora

https://aws.amazon.com/rds/aurora replaces MySQL in the AWS cloud, enabling scaling to 64 TB and replicates across 3 zones.

Aurora is managed by the AWS RDS (Relational Data Service) (along with Postgres and DynamoDB).

PROTIP: Many choose MySQL instead of DynamoDB for equivalent off-line capability since DynamoDB only works within the Amazon cloud.

It deals with:

  • instances
  • Security Groups
  • Parameter Groups
  • Snapshots

This means some limitations:

  • Logs larger than 2% of storage allocated will get rotated every 24 hours.

Google Cloud SQL

On August, 2016 Google announced Cloud SQL announced, a hosted version of MySQL 5.7 for the Google Cloud Platform.

MySQL via Homebrew on Mac OSX

PROTP: Use Homebrew instead of downloading from http://dev.mysql.com/downloads, which gets you the latest version instead of one vetted.

  1. Prepare the Homebrew environment (before any Homebrew install):

    brew update

    This can take several minutes.

    Resolve any issues reported.

    brew doctor
    brew upgrade

    This can cause other packages to be downloaded, which can take several minutes.

  2. Run the latest version of the Homebrew installer:

    brew install mysql

    The response:

    ==> Downloading https://homebrew.bintray.com/bottles/mysql-5.7.13.el_capitan.bot
    ######################################################################## 100.0%
    ==> Pouring mysql-5.7.13.el_capitan.bottle.tar.gz
    ==> /usr/local/Cellar/mysql/5.7.13/bin/mysqld --initialize-insecure --user=mac -
    ==> Caveats
    We've installed your MySQL database without a root password. To secure it run:
     mysql_secure_installation
     
    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
    ==> Summary
    🍺  /usr/local/Cellar/mysql/5.7.13: 13,344 files, 445.0M   
    

    Notice the installer is specific to the version of Mac OSX (such as “El Capitan”).

    Alternately, to install a specific version, such as 5.6:

    brew install mysql56

  3. Expose folders and dependencies:

    NOTE: mysql is the command line tool.
    mysqld is the server.

    brew info mysql

    The sample response:

    mysql: stable 5.7.13 (bottled)
    Open source relational database management system
    https://dev.mysql.com/doc/refman/5.7/en/
    Conflicts with: mariadb, mariadb-connector-c, mysql-cluster, mysql-connector-c, percona-server
    /usr/local/Cellar/mysql/5.7.13 (13,344 files, 445.0M) *
      Poured from bottle on 2016-07-01 at 20:05:45
    From: https://github.com/Homebrew/homebrew-core/blob/master/Formula/mysql.rb
    ==> Dependencies
    Build: cmake ✘
    Required: openssl ✔
    ==> Options
    --with-archive-storage-engine
      Compile with the ARCHIVE storage engine enabled
    --with-blackhole-storage-engine
      Compile with the BLACKHOLE storage engine enabled
    --with-debug
      Build with debug support
    --with-embedded
      Build the embedded server
    --with-local-infile
      Build with local infile loading support
    --with-test
      Build with unit tests
    ==> Caveats
    We've installed your MySQL database without a root password. To secure it run:
     mysql_secure_installation
     
    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
      

    https://dev.mysql.com/doc/refman/5.6/en/osx-installation.html

  4. Verify:

    which mysql

    The response:

    /usr/local/bin/mysql
    

    Configure

  5. Open in Terminal a shell window.

  6. Set permissions for root access:

    sudo chown -R mysql /usr/local/var/mysql/

  7. Joe Fallon, in his blog, proposed additional configurations.

    Invoke daemon process

  8. Invoke mysql daemon from the command line:

    mysql.server start

    Response:

    Starting MySQL
    . SUCCESS! 
    

  9. List the daemons started by the command above:

    ps

    A sample response:

      PID TTY           TIME CMD
    21069 ttys002    0:00.02 /bin/sh /usr/local/Cellar/mysql/5.7.13/bin/mysqld_safe
    21161 ttys002    0:00.33 /usr/local/Cellar/mysql/5.7.13/bin/mysqld --basedir=/u
    

    If you want to use the database, proceed to Work with SQL below.

    Stop server and process

  10. PROTIP: Stop the server before killing its process:

    mysql.server stop

  11. To kill the processes:

    pkill mysql

    Alternately, specify by process ID (which is differs over time):

    kill -9 21069
    kill -9 21161

    Uninstall MySQL from Homebrew

  12. Remove MySQL:

    brew remove mysql
    brew cleanup --force

    Response:

    Uninstalling /usr/local/Cellar/mysql/5.7.13... (13,344 files, 445.0M)
    
  13. Remove services MacOS invokes when a user logs in:

    sudo ls ~/Library/LaunchAgents

    If file “homebrew.mxcl.mysql.plist” exists, remove it:

    sudo rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

    NOTE: In previous versions, the file was “com.mysql.mysqld.plist”.

  14. Remove lefover database files:

    ls /usr/local/var/mysql

    If files exist there, remove them all:

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

    Skipping the above step means you will not be able to log in as root initially to set the password.

MariaDB via Homebrew

MariaDB ships with additional storage engines installed (no compilation as with MySQL): Aria, XtraDB (an enhanced and extended version of the InnoDB storage engine), PBXT, FederatedX (a drop-in replacement for Federated), OQGraph, and SphinxSE in addition to standard MyISAM, blackhole, CSV, Memory, etx.

show storage engines;

http://kb.askmonty.org/v/mariadb is the MariaDB Knowledgebase.

  1. Take a full backup before doing this.
  2. Uninstall MySQL if you have it installed.
  3. Install MariaDB:

    brew install mariadb

    If MySQL is already installed, the response is:

    Error: Cannot install mariadb because conflicting formulae are installed.
    mysql: because mariadb, mysql, and percona install the same binaries.
    

    If you get the above, uninstall mysql, kill the processes running, and try again.

    The response:

    ==> Downloading https://homebrew.bintray.com/bottles/mariadb-10.1.14.el_capitan.
    ######################################################################## 100.0%
    ==> Pouring mariadb-10.1.14.el_capitan.bottle.tar.gz
    ==> Caveats
    A "/etc/my.cnf" from another install may interfere with a Homebrew-built
    server starting up correctly.
     
    To connect:
     mysql -uroot
     
    To have launchd start mariadb now and restart at login:
      brew services start mariadb
    Or, if you don't want/need a background service you can just run:
      mysql.server start
    ==> Summary
    🍺  /usr/local/Cellar/mariadb/10.1.14: 573 files, 131.1M
    
  4. Verify

    which mysql

    The response:

    /usr/local/bin/mysql
    
  5. Invoke interactively from the command line:

    echo $TMPDIR

    Sample response:

    /var/folders/j_/gh27gpxj1t58hyryfg9drvdc0000gn/T/
    

    Now clear it:

    unset TMPDIR

    Do another echo to get a blank response.

  6. Invoke interactively from the command line:

    
    mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql 
    

    NOTE: The whoami returns the output from running the whoami command.

    In previous examples, “–tmpdir=/tmp” is not recognized.

    The response:

    2016-07-01 20:40:43 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
    2016-07-01 20:40:43 [NOTE]    Creating data directory /usr/local/var/mysql
    2016-07-01 20:40:43 [NOTE]    Generating random password to /Users/mac/.mysql_secret...done.
    2016-07-01 20:40:43 [NOTE]    Executing /usr/local/opt/mysql/bin/mysqld --bootstrap --datadir=/usr/local/var/mysql --lc-messages-dir=/usr/local/opt/mysql/share/mysql --lc-messages=en_US --basedir=/usr/local/opt/mysql
    2016-07-01 20:40:44 [NOTE]    Creating system tables...done.
    2016-07-01 20:40:44 [NOTE]    Filling system tables with data...done.
    2016-07-01 20:40:45 [NOTE]    Filling help table with data...done.
    2016-07-01 20:40:45 [NOTE]    Creating default user root@localhost
    2016-07-01 20:40:45 [NOTE]    Creating default proxy root@localhost
    2016-07-01 20:40:45 [NOTE]    Creating sys schema
    2016-07-01 20:40:45 [NOTE]    done.
    2016-07-01 20:40:47 [WARNING] The bootstrap log isn't empty:
    2016-07-01 20:40:47 [WARNING] 2016-07-02T02:40:43.275138Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead
    

Work with SQL

  1. Define a root password to secure your installation using the provided script:

    mysql_secure_installation

    This invokes the script in a folder such as:
    /usr/local/Cellar/mysql/5.5.10/bin/
    (depending on the version installed).

    The response:

    Securing the MySQL server deployment.
     
    Connecting to MySQL using a blank password.
    Segmentation fault: 11
    

    CAUTION: This is where I’m stuck now.

Interactive SQL Client

NOTE: mysqladmin is the default client tool for performing administrative tasks.

PROTIP: Use Sequel Pro’s Export and Import features (use a MySQL dump) to move databases.

NOTE: There is no brew install sequelpro.

  1. Download SequelPro SQL client from

    http://www.sequelpro.com

  2. In the Downloads folder click the file downloaded (sequel-pro-1.1.2.dmg as of June 2016).
  3. In the window that pops up, click “Sequel Pro”.
  4. Click “Open” in the “downloaded from the internet” dialog.
  5. Click X to dismiss the “Sequel Pro” window.
  6. Press command+tab to select Sequel Pro’s “pancake” icon.

    QUESTION: Where is this installed?

  7. Pinch with three fingers for the Mac’s Launch window.

  8. Use SequelPro’s keyboard shortcuts.

    Sockets

  9. Since OS X expects the MySQL socket mysql.sock to be in folder /var/mysql, create it and add a symbolic link to where the socket actually lives:

    sudo mkdir /var/mysql
    sudo chmod 755 /var/mysql
    sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock

    Alternately, installation of MySQL 5 by MacPorts places the Socket File in folder:

    /opt/local/var/run/mysql5/mysqld.sock
    

    and data files in

    /opt/local/var/db/mysql5/
    

    http://www.sequelpro.com/docs/Where_are_MySQLs_Files

  10. Create a new connection via the Socket option without changing any settings.

    Both MySQL and MariaDB use main configuration file my.cnf.

Interactive mode

http://dev.mysql.com/downloads/workbench/ MySQL Workbench install on mac

The command-line client is mysql.

  1. Start the mysqld server so anyone can log in with full permissions:

    mysqld_safe –skip-grant-tables

    Open another Terminal shell window on any folder.

  2. Log in without a password:

    mysql -u root

    The response:

    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.7.13 Homebrew
     
    Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
     
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
     
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    

    See docs on Connecting to and Disconnecting from the Server

  3. To exit, type in quit and press Enter:

    quit

    Then bring up mysql again.

  4. To set automatic:

#start launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

#stop launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

List databases

There are two ways to get a list of databases:

  1. From the command line:

    mysqlshow

    From within MySQL:

    show databases;

    The expected response on a newly created instance:

    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    +--------------------+
    3 rows in set (0.00 sec)
    

MySQLAdmin

mysqladmin is a command-line interface for administrators to perform server administration tasks.

  1. On a Terminal command line:

    mysqladmin

    The response is long, starting with…

    mysqladmin  Ver 8.42 Distrib 5.7.13, for osx10.11 on x86_64
    Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
     
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
     
    Administration program for the mysqld daemon.
    Usage: mysqladmin [OPTIONS] command command....
    

Manage Users

  1. List users in the mysql system database by specifying a single line:

    use mysql; select * from user;

  2. List users by entering SQL commands in several lines:

    SELECT * FROM information_schema.TABLES

    The response is an arrow:

    ->
    

    To exit, press control+C then Enter.

    Enter more lines:

    WHERE TABLE_NAME LIKE ‘%user%’

    To have mysql process the statement lines, type a semicolon and press Enter:

    ;

    The response should be a row containing:

    mysql   user    BASE TABLE  MyISAM
    
  3. Reset all the root passwords (the password being “password”):

    UPDATE mysql.user SET Password=PASSWORD(‘NewPassword’) WHERE User=’root’;
    FLUSH PRIVILEGES;

    If you kill the running copy of mysqld_safe and start mysql again without the skip-grant-tables option, you should be able to log in with mysql -u root -p and the new password you just set.

Create a database

These commands are entered mysql>.

  1. Create:

    create database sampledb;

    The response:

    Query OK, 1 row affected (0.00 sec)
    
  2. Switch

    use sampledb;

Command Files

PROTIP: Interact with the databae via batch-submitted files for repeatability.

  1. Copy a file SQL code from GitHub.

    TBD

PROTIP: Interact with the databae via batch-submitted files for repeatability.

  1. Save the SQL script to a folder such as ~/sql.

  2. Invoke a SQL script using the Linux source command:

    mysql < ~/sql/sqlfile1

  3. Invoke a SQL script using the Linux source command:

    source file_name
    . file_name

    About the sample database creation

  4. Since the script creates the database, the repeatable script needs to begin by deleting it:

    DROP DATABASE IF EXISTS sampledb;
    
  5. PROTIP: If a SQL script needs to create several databases, group the various actions for a particular database together rather than doing each step for each database.

    DROP DATABASE IF EXISTS sampledb;
    create database sampledb;
    use sampledb;
    

    To create a database based on this discussion:

CREATE SCHEMA 'inmail';
CREATE TABLE  'inmail','NEWMAIL' (
'first_name' VARCHAR(48) NULL,
'family_name' VARCHAR(96) NULL,
'subscribe' VARCHAR(1) NULL,
'emailaddr' VARCHAR(128) NULL,
'password' VARCHAR(48) NULL,
'emailsubject' VARCHAR(120) NULL,
'loc' VARCHAR(48) NULL,
'rating' INT NOT NULL,
'pubthis' VARCHAR(1) NULL,
'comments' VARCHAR(4046) NULL,
'refererurl' VARCHAR(255) NULL,
'user_agent' VARCHAR(45) NULL,
'remote_addr' VARCHAR(48) NULL,
'local_addr' VARCHAR(48) NULL,
'city_addr' VARCHAR(96) NULL,
'street_addr' VARCHAR(96) NULL,
'phone_country' MEDIUMINT UNSIGNED NOT NULL,
'phone_number’ MEDIUMINT UNSIGNED NOT NULL,
'postal' MEDIUMINT UNSIGNED NOT NULL,
'longitude' NUMERIC 9,6 NULL,
'latitude' NUMERIC 8,6 NULL,
'visitor_id' INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
'savedatetime' TIMESTAMP,
);
// comment not a BLOB to reduce overhead
// phone number is 10 digits in the US.
// postal code 11
// TIMESTAMP is 

InnoDB Engine

InnoDB is newer, faster, and scales better than the MyISAM database engine.

Allocate a maximum amount of RAM to your MySQL instance with the innodb_buffer_pool_size parameter and give at least 15Mb to the query_cache_size parameter.

Read this article about InnoDB Performance Optimization Basics.

Learn More

Pinal Dave (pronounced “da way”, @pinaldave from India) has been producing video courses for years on SQLAuthority.com before doing the 29 Apr 2013 MySQL Fundamentals video course on Pluralsight (2 hour 37 minutes)