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:

Share on        

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