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.

Timeline

MySQL was first proposed by Edgar Cord in 1970 with co-founder Michael “Monty” Widenius.

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

MySQL’s “open-source” free usage helped it gain rapid growth as a natural choice for other open-source frameworks such as WordPress.

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).

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

However, 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 Kicking

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 Docker

  1. To start a mysql database in a docker container, run:

    docker-compose -f src/main/docker/mysql.yml up -d

  2. To stop it and remove the container, run:

    docker-compose -f src/main/docker/mysql.yml down

  3. To dockerize your application and all the services that it depends on, build a docker image of your app by running:

    ./mvnw package -Pprod docker:build

  4. Then run:

    docker-compose -f src/main/docker/app.yml up -d

MySQL via Homebrew on Mac OSX

Install MySQL

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. Download and install the most recent version of the Homebrew installer:

    brew install mysql

    The response on July 4, 2017:

    ==> Downloading https://homebrew.bintray.com/bottles/mysql-5.7.18_1.sierra.bottl
    Already downloaded: /Users/mac/Library/Caches/Homebrew/mysql-5.7.18_1.sierra.bottle.tar.gz
    ==> Pouring mysql-5.7.18_1.sierra.bottle.tar.gz
    ==> Using the sandbox
    ==> Caveats
    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
    ==> Summary
    🍺  /usr/local/Cellar/mysql/5.7.18_1: 321 files, 232.9MB
    

    PROTIP: Notice the installer is specific to the version of Mac OSX (such as “Sierra” in this case).

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

    brew install mysql56

  3. After some time, to upgrade MySQL:

    brew upgrade mysql

    A sample response:

    ==> Upgrading 1 outdated package, with result:
    mysql 5.7.18_1
    ==> Upgrading mysql 
    ==> Downloading https://homebrew.bintray.com/bottles/mysql-5.7.18_1.sierra.bottle.tar.gz
    ######################################################################## 100.0%brew info mysql
    ==> Pouring mysql-5.7.18_1.sierra.bottle.tar.gz
    ==> Using the sandbox
    

    ==> Caveats
    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
    ==> Summary
    🍺  /usr/local/Cellar/mysql/5.7.18_1: 321 files, 232.9MB
    
  4. Expose folders and dependencies:

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

    brew info mysql

    The sample response:

    mysql: stable 5.7.18 (bottled)
    Open source relational database management system
    https://dev.mysql.com/doc/refman/5.7/en/
    Conflicts with:
      mariadb (because mysql, mariadb, and percona install the same binaries.)
      mariadb-connector-c (because both install plugins)
      mysql-cluster (because mysql, mariadb, and percona install the same binaries.)
      mysql-connector-c (because both install MySQL client libraries)
      percona-server (because mysql, mariadb, and percona install the same binaries.)
    /usr/local/Cellar/mysql/5.7.17 (321 files, 234.4MB)
      Poured from bottle on 2017-03-08 at 15:46:47
    From: https://github.com/Homebrew/homebrew-core/blob/master/Formula/mysql.rb
    ==> Dependencies
    Build: cmake ✘
    Required: openssl ✔
    ==> Requirements
    Required: macOS >= 10.7 ✔
    ==> 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
      

    The same Caveats are shown.

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

  5. Verify install:

    which mysql.server

    The expected response:

    /usr/local/bin/mysql
    

    BLAH: But one cannot cd into that folder.

    Configure

  6. Set OS permissions for root execution access the directory of executables:

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

    Supply the password when prompted.

    No response is expected.

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

    Start to Change Password

    PROTIP: Your first interaction with MySQL after install should be to change the password.

  8. Start in Safe Mode

    sudo mysqld_safe --skip-grant-tables --skip-syslog --skip-networking

    Per https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

  9. Craft this command, substituted with your own password:

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

  10. Quit out of the MySQL session:

    \q

  11. Quit safe mode:

    mysqladmin shutdown

    Start daemon process

  12. Invoke mysql foreground daemon from the command line:

    sudo mysql.server start

    The response after supplying the password reflects the log file specific to your machine:

    Starting MySQL
    .Logging to '/usr/local/var/mysql/macs-MacBook-Pro-4.local.err'.
    . SUCCESS! 
    

    List processes

  13. List brew services:

    brew services list

    Name         Status  User Plist
    mongodb      started mac  /Users/mac/Library/LaunchAgents/homebrew.mxcl.mongodb.plist
    mysql        started mac  /Users/mac/Library/LaunchAgents/homebrew.mxcl.mysql.plist
    

  14. List the daemons (processes) started by the command above, piped to filter the output:

    ps -ax | grep mysql

    A sample response (in addition to grep itself):

      PID TTY           TIME CMD
    21069 ttys002    0:00.02 /bin/sh /usr/local/Cellar/mysql/5.7.18_1/bin/mysqld_safe
          --datadir=/usr/local/var/mysql 
          --pid-file=/usr/local/var/mysql/macs-MacBook-Pro-4.local.pid
    21161 ttys002    0:00.33 /usr/local/Cellar/mysql/5.7.18_1/bin/mysqld 
          -basedir=/usr/local/Cellar/mysql/5.7.18_1 
          --datadir=/usr/local/var/mysql 
          --plugin-dir=/usr/local/Cellar/mysql/5.7.18_1/lib/plugin 
          --user=mysql 
          --log-error=/usr/local/var/mysql/macs-MacBook-Pro-4.local.err 
          --pid-file=/usr/local/var/mysql/macs-MacBook-Pro-4.local.pid
    

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

    Invoke MySQL

  15. If MySQL was started in the foreground, start another terminal window.

    One cannot just invoke mysql without specifying credentials, or this appears:

    ERROR 1045 (28000): Access denied for user 'mac'@'localhost' (using password: NO)
    
  16. The command in the Caveats message states this, which logs in without a password:

    mysql -uroot

    Alternately, to specify a password:

    mysql -u root -p

    You need to Start the MySQL service if you see this:

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

    Success means the prompt should now change to:

    mysql >
    

    That’s after reading this:

    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 3
    Server version: 5.7.19 Homebrew
     
    Copyright (c) 2000, 2017, 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.
    

    If instead you get this error message:

    my_print_defaults: Can't read dir of '/usr/local/etc/my.cnf.d' (Errcode: 2 - No such file or directory)
    my_print_defaults: [ERROR] Fatal error in defaults handling. Program aborted!
      

    Fix the damage done by brew prune:

    mkdir /usr/local/etc/my.cnf.d

    No response is expected. Try the command again.

    Slash commands:

  17. For help hints:

    \h

    Results in this:

    For information about MySQL products and services, visit:
    http://www.mysql.com/
    For developer information, including the MySQL Reference Manual, visit:
    http://dev.mysql.com/
    To buy MySQL Enterprise support, training, or other products, visit:
    https://shop.mysql.com/
     
    List of all MySQL commands:
    Note that all text commands must be first on line and end with ';'
    ?         (\?) Synonym for `help'.
    clear     (\c) Clear the current input statement.
    connect   (\r) Reconnect to the server. Optional arguments are db and host.
    delimiter (\d) Set statement delimiter.
    edit      (\e) Edit command with $EDITOR.
    ego       (\G) Send command to mysql server, display result vertically.
    exit      (\q) Exit mysql. Same as quit.
    go        (\g) Send command to mysql server.
    help      (\h) Display this help.
    nopager   (\n) Disable pager, print to stdout.
    notee     (\t) Don't write into outfile.
    pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
    print     (\p) Print current command.
    prompt    (\R) Change your mysql prompt.
    quit      (\q) Quit mysql.
    rehash    (\#) Rebuild completion hash.
    source    (\.) Execute an SQL script file. Takes a file name as an argument.
    status    (\s) Get status information from the server.
    system    (\!) Execute a system shell command.
    tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
    use       (\u) Use another database. Takes database name as argument.
    charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
    warnings  (\W) Show warnings after every statement.
    nowarning (\w) Don't show warnings after every statement.
    resetconnection(\x) Clean session context.
     
    For server side help, type 'help contents'
    
  18. To quit out of a MySQL session:

    \q

    Stop server and process

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

    mysql.server stop

  20. To kill the processes:

    sudo pkill mysql

    No response is given unless you didn’t use sudo:

    pkill: signalling pid 24162: Operation not permitted
    

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

    kill -9 21069
    kill -9 21161

Uninstall MySQL from Homebrew

  1. Remove MySQL:

    brew remove mysql

    Response:

    Uninstalling /usr/local/Cellar/mysql/5.7.13... (13,344 files, 445.0M)
    
  2. Recover disk space from uninstalled items:

    brew cleanup --force

    Response:

    ==> This operation has freed approximately 318.2MB of disk space.
    
  3. 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”.

  4. 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 (with no additional 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, etc.

show storage engines;
   

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

  1. Take a full backup before doing this.
  2. If you have MySQL installed, uninstall MySQL.
  3. If you have MariaDB installed:

    brew uninstall mariadb --force -s

    -s clears the brew cache.

    --force is needed in the command to avoid this:

    Error: No such keg: /usr/local/Cellar/mariadb
    

    No response is returned if it worked.

  4. Remove dead symlinks:

    brew prune

    The response, for example:

    Pruned 0 symbolic links and 17 directories from /usr/local
    
  5. 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
    

    Before you boldly start:

  6. Verify

    which mysql

    The response:

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

    echo $TMPDIR

    Sample response:

    /var/folders/j_/gh27gpxj1t58hyryfg9drvdc0001gn/T/
    
  8. Now clear it:

    unset TMPDIR

    Do another echo to get a blank response.

  9. Invoke interactively from the command line (all in one line):

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

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

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

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 Clients

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.18, for osx10.12 on x86_64
    Copyright (c) 2000, 2017, 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.
    

    The response listing command usage options:

    Usage: mysqladmin [OPTIONS] command command....
      --bind-address=name IP address to bind to.
      -c, --count=#       Number of iterations to make. This works with -i
                       (--sleep) only.
      -#, --debug[=#]     This is a non-debug version. Catch this and exit.
      --debug-check       This is a non-debug version. Catch this and exit.
      --debug-info        This is a non-debug version. Catch this and exit.
      -f, --force         Don't ask for confirmation on drop database; with
                       multiple commands, continue even if an error occurs.
      -C, --compress      Use compression in server/client protocol.
      --character-sets-dir=name 
                       Directory for character set files.
      --default-character-set=name 
                       Set the default character set.
      -?, --help          Display this help and exit.
      -h, --host=name     Connect to host.
      -b, --no-beep       Turn off beep on error.
      -p, --password[=name] 
                       Password to use when connecting to server. If password is
                       not given it's asked from the tty.
      -P, --port=#        Port number to use for connection or 0 for default to, in
                       order of preference, my.cnf, $MYSQL_TCP_PORT,
                       /etc/services, built-in default (3306).
      --protocol=name     The protocol to use for connection (tcp, socket, pipe,
                       memory).
      -r, --relative      Show difference between current and previous values when
                       used with -i. Currently only works with extended-status.
      --secure-auth       Refuse client connecting to server if it uses old
                       (pre-4.1.1) protocol. Deprecated. Always TRUE
      -s, --silent        Silently exit if one can't connect to server.
      -S, --socket=name   The socket file to use for connection.
      -i, --sleep=#       Execute commands repeatedly with a sleep between.
      --ssl-mode=name     SSL connection mode.
      --ssl               Deprecated. Use --ssl-mode instead.
                       (Defaults to on; use --skip-ssl to disable.)
      --ssl-verify-server-cert 
                       Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.
      --ssl-ca=name       CA file in PEM format.
      --ssl-capath=name   CA directory.
      --ssl-cert=name     X509 cert in PEM format.
      --ssl-cipher=name   SSL cipher to use.
      --ssl-key=name      X509 key in PEM format.
      --ssl-crl=name      Certificate revocation list.
      --ssl-crlpath=name  Certificate revocation list path.
      --tls-version=name  TLS version to use, permitted values are: TLSv1, TLSv1.1,
                       TLSv1.2
      -u, --user=name     User for login if not current user.
      -v, --verbose       Write more information.
      -V, --version       Output version information and exit.
      -E, --vertical      Print output vertically. Is similar to --relative, but
                       prints output vertically.
      -w, --wait[=#]      Wait and retry if connection is down.
      --connect-timeout=# 
      --shutdown-timeout=# 
      --plugin-dir=name   Directory for client-side plugins.
      --default-auth=name Default authentication client-side plugin to use.
      --enable-cleartext-plugin 
                       Enable/disable the clear text authentication plugin.
      --show-warnings     Show warnings after execution
     
    Variables (--variable-name=value)
    and boolean options {FALSE|TRUE}  Value (after reading options)
    --------------------------------- ----------------------------------------
    bind-address                      (No default value)
    count                             0
    force                             FALSE
    compress                          FALSE
    character-sets-dir                (No default value)
    default-character-set             auto
    host                              (No default value)
    no-beep                           FALSE
    port                              0
    relative                          FALSE
    secure-auth                       TRUE
    socket                            (No default value)
    sleep                             0
    ssl                               TRUE
    ssl-verify-server-cert            FALSE
    ssl-ca                            (No default value)
    ssl-capath                        (No default value)
    ssl-cert                          (No default value)
    ssl-cipher                        (No default value)
    ssl-key                           (No default value)
    ssl-crl                           (No default value)
    ssl-crlpath                       (No default value)
    tls-version                       (No default value)
    user                              (No default value)
    verbose                           FALSE
    vertical                          FALSE
    connect-timeout                   43200
    shutdown-timeout                  3600
    plugin-dir                        (No default value)
    default-auth                      (No default value)
    enable-cleartext-plugin           FALSE
    show-warnings                     FALSE
     
    Default options are read from the following files in the given order:
    /etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf 
    The following groups are read: mysqladmin client
    The following options may be given as the first argument:
    --print-defaults        Print the program argument list and exit.
    --no-defaults           Don't read default options from any option file,
                         except for login file.
    --defaults-file=#       Only read default options from the given file #.
    --defaults-extra-file=# Read this file after the global files are read.
    --defaults-group-suffix=#
                         Also read groups with concat(group, suffix)
    --login-path=#          Read this path from the login file.
     
    Where command is a one or more of: (Commands may be shortened)
      create databasename Create a new database
      debug     Instruct server to write debug information to log
      drop databasename Delete a database and all its tables
      extended-status       Gives an extended status message from the server
      flush-hosts           Flush all cached hosts
      flush-logs            Flush all logs
      flush-status    Clear status variables
      flush-tables          Flush all tables
      flush-threads         Flush the thread cache
      flush-privileges      Reload grant tables (same as reload)
      kill id,id,...  Kill mysql threads
      password [new-password] Change old password to new-password in current format
      ping      Check if mysqld is alive
      processlist   Show list of active threads in server
      reload    Reload grant tables
      refresh   Flush all tables and close and open logfiles
      shutdown    Take server down
      status    Gives a short status message from the server
      start-slave   Start slave
      stop-slave    Stop slave
      variables             Prints variables available
      version   Get version info from server
    
  2. Use mysqladmin to change password (replacing [newpassword] with one you create)

    mysqladmin -u root password [newpassword]
    

    PROTIP: Write the password in a secure document and paste it in the command to be sure you have it correct in the future.

    SequelPro

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

    NOTE: There is no brew install sequelpro.

  3. Download SequelPro SQL client from

    http://www.sequelpro.com

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

    QUESTION: Where is this installed?

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

  10. Use SequelPro’s keyboard shortcuts.

    Sockets

  11. 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:

    cd \
    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

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

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

Interactive mode

VIDEO The command-line client is mysql.

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

  2. 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.

  3. 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

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

    quit

    Then bring up mysql again.

  5. To start automatically upon server start:

    #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)
    

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.

Replication

If the MySQL instance is running within AWS, Replicate it external to Amazon RDS based on GTIDs (Global Transaction Identifiers). This involves setting the db on and off as read-only.

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)

Tim Molter’s
blog has a Q&A about installation going back to 2009.

https://stackoverflow.com/questions/15016376/cant-connect-to-local-mysql-server-through-socket-homebrew contains a deprecated suggestion in:

mysql_install_db –basedir=”$(brew –prefix mysql)”