Home / Programming / MySQL edit
Try Documentalist, my app that offers fast, offline access to 190+ programmer API docs.

connecting to the database with mysql executable
/usr/bin/mysql -u root@localhost --password=<pwd> <dbname>
  • -p : will ask for password
  • --password : provide password
  • --port=3307 : connect to non-standard port (3306 is default)
Can connect to remote servers, but the server might be configured to not allow remote connections (per user).
using mysqlsh [OPTIONS] [URI] -f <path> [script args...]
  • on mac use brew install Caskroom/cask/mysql-shell to install
  • --sql : run in sql mode (JavaScript is default)
  • -f <path> : process a file
  • --port=<port> : connect to port
  • --dbuser=<user> : user
mysql cmd-line ui
  • show databases;
  • use <db>;
  • show tables;
  • describe <table>;
default config for mysql:5.6 docker image
/var/lib/mysql : data directory
/etc/mysql/my.cnf : configuration file
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
skip-host-cache
skip-name-resolve
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
explicit_defaults_for_timestamp

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address	= 127.0.0.1

#log-error	= /var/log/mysql/error.log

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
notes
brew install homebrew/versions/mysql55 : so that it matches version on gce
brew link --force homebrew/versions/mysql55

import (
_ "github.com/go-sql-driver/mysql"
)

Types:
int, integer - 4 bytes
bigint - 8 bytes
float - 4 bytes
double - 8 bytes

date - 3 bytes
time - 3 bytes
datetime - 8 bytes
timestamp - 4 bytes
varchar(n) - L + 1 bytes if n <= 255, L + 2 bytes i n > 255, max is 65535 
text
varbinary(n)
blob

Notes:
varchar is stored inline, size limited to max row size (65k)
text is stored on the size, not max row limit, slower than varchar

CREATE [UNIQUE] INDEX idx_name ON table(column,...) using [HASH | BTREE]

Homebrew:
To connect:
    mysql -uroot

To have launchd start mysql at login:
    ln -sfv /usr/local/opt/mysql/*.plist ~/Library/LaunchAgents
Then to load mysql now:
    launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
Or, if you don't want/need launchctl, you can just run:
    mysql.server start
==> /usr/local/

http://blog.ionelmc.ro/2014/12/28/terrible-choices-mysql/


SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
local:
STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
on gce is empty

Version:
local: 5.6.22
gce: 5.5.38-log

https://github.com/yahoo/mysql_perf_analyzer
http://githubengineering.com/using-mysql-performance-schema-for-workload-analysis/
http://stackoverflow.com/questions/3499372/get-list-of-mysql-databases-and-server-version
https://github.com/pubnative/mysqldriver-go

Feedback about page:

Feedback:
Optional: your email if you want me to get back to you:

Need fast, offline access to 190+ programmer API docs? Try my app Documentalist for Windows