MySQL to MariaDB (and Aurora)

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.

Relational databases

MySQL and MariaDB are “Relational databases” to store information for applications.

Within each database, data is organized in a set of tables, each organizing data for a particular class of data.
Each table organizes data in “rows” representing a record.
Each row contains fields.
Each field has a type of information, such as text, a number, or boolean (True/False).
The record is the specific dataset; the values of the records make up its contents
Several tables can be joined together to obtain information out of the database.

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 still being used.

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

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

docker-compose -f src/main/docker/mysql.yml up -d
To stop it and remove the container, run:

docker-compose -f src/main/docker/mysql.yml down
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
Then run:

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

Install MySQL

Install MySQL on RedHat

yum install –y mariadb mariadb-server
systemctl start mariadb; systemctl enable mariadb
ss –tua grep mysql to verify it is listening
run mysql_secure_installation and make sure the password is set to password
type mysql -u root –p to log in
type show databases; to verify the availability of the administrative databases
type exit; to quit

Install MySQL on MacOS via Homebrew

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

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.

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

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

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

Verify install:

which mysql.server

The expected response:
```
/usr/local/bin/mysql
```
BLAH: But one cannot cd into that folder.

Configure
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.
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.
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
Craft this command, substituted with your own password:

UPDATE mysql.user SET Password=PASSWORD(‘Pa$$w0rd’) WHERE User=’root’; FLUSH PRIVILEGES;
Quit out of the MySQL session:

\q
Quit safe mode:

mysqladmin shutdown

Start daemon process
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

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

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

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

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:

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'

To quit out of a MySQL session:

\q

Stop server and process
PROTIP: Stop the server before killing its process:

mysql.server stop
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

Remove MySQL:

brew remove mysql

Response:

Uninstalling /usr/local/Cellar/mysql/5.7.13... (13,344 files, 445.0M)

Recover disk space from uninstalled items:

brew cleanup --force

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

Take a full backup before doing this.
If you have MySQL installed, uninstall MySQL.
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.

Remove dead symlinks:

brew prune

The response, for example:

Pruned 0 symbolic links and 17 directories from /usr/local

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:

Verify the location of mysql because mariadb is a flavor of it:

command -v mysql

The response:
```
/usr/local/bin/mysql
```
Invoke interactively from the command line:

echo $TMPDIR

Sample response:
```
/var/folders/j_/gh27gpxj1t58hyryfg9drvdc0001gn/T/
```
Now clear it:

unset TMPDIR

Do another echo to get a blank response.
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

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.

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

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.
Download SequelPro SQL client from

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

QUESTION: Where is this installed?
Pinch with three fingers for the Mac’s Launch window.
Use SequelPro’s keyboard shortcuts.

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

http://dev.mysql.com/downloads/workbench/ MySQL Workbench install on mac
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.

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

To exit, type in quit and press Enter:

quit

Then bring up mysql again.

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:

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)

mycli GUI

mycli.net is an open-source Python program with a GUI for selecting SQL queries:

Manage Users

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

use mysql; select * from user;
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
```
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>.

Create:

create database sampledb;

The response:
```
Query OK, 1 row affected (0.00 sec)
```
Switch

use sampledb;

Command Files

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

Copy a file SQL code from GitHub.

TBD

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

Save the SQL script to a folder such as ~/sql.
Invoke a SQL script using the Linux source command:

mysql < ~/sql/sqlfile1
Invoke a SQL script using the Linux source command:

source file_name . file_name

About the sample database creation
Since the script creates the database, the repeatable script needs to begin by deleting it:
```
DROP DATABASE IF EXISTS sampledb;
```
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.

Monitoring

https://github.com/osterman/mysql_manager
https://github.com/osterman/mysql_health from Erik Osterman (@eosterman)

He uses @Helm, @codefresh, Kubernetes to, on every git commit, auto spin up and test containers of a 12-factor microservices app : https://t.co/xK8RAIuVzO

https://github.com/osterman/kubernetes-on-aws Deploying Kubernetes on AWS with CloudFormation and CoreOS

https://github.com/osterman/geoip-api RESTful GeoIP API backed by MaxMind Lite

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

Wilson Mar