Optimized MySQL configuration [socket & PID paths taken from stock Ubuntu Server setups] (updated July 2017)

my.cnf

# Optimized MySQL configuration by Fotis Evangelou - Updated July 2017
#
# The settings provided below are a starting point for a 4GB - 8GB RAM server with 4 CPU cores.
# If you have less or more resources available you should adjust accordingly to save CPU,
# RAM and disk I/O usage.
# The settings marked with a specific comment or the word "UPD" after the value
# should be adjusted for your system by using MySQL DB diagnostics tools like:
# http://mysqltuner.com/
# or
# https://launchpad.net/mysql-tuning-primer (supports MySQL up to v5.6)
#
#
# Note that if there is NO comment after a setting value, then 99,9% of the times you won't need to adjust it.
#
#
# THINGS TO DO AFTER YOU UPDATE MY.CNF - TROUBLESHOOTING
# If MySQL cannot start or restart (most probably), then perform the following actions.
# If any terminal commands are mentioned, make sure you execute them as "root" user:
#
# 1. If the server had the stock MySQL configuration and you addded or updated any
#    "innodb-log-*" settings (as suggested below), then execute these commands ONLY
#    the first time you apply this configuration:
#
#    $ rm -rvf /var/lib/mysql/ib_logfile*
#    $ service mysql restart
#
# 2. If the setting "bind-address" is not commented out, then make sure the file /etc/hosts is
#    properly configured. A good example of a "clean" /etc/hosts file is something like this:
#
#    127.0.0.1 localhost localhost.localdomain
#    ::1       localhost localhost.localdomain
#    1.2.3.4   hostname.domain.tld hostname
#
#    Finally restart MySQL:
#
#    $ service mysql restart
#

[client]
port                            = 3306
socket                          = /var/run/mysqld/mysqld.sock

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

[mysqld]
# Basic
basedir                         = /usr
bind-address                    = 127.0.0.1 # Comment out if you want remote servers to connect to this server's MySQL instance
datadir                         = /var/lib/mysql
lc-messages-dir                 = /usr/share/mysql
max-allowed-packet              = 128M
max-connect-errors              = 1000000
pid-file                        = /var/run/mysqld/mysqld.pid
port                            = 3306
skip-external-locking
skip-name-resolve
socket                          = /var/run/mysqld/mysqld.sock
tmpdir                          = /tmp
user                            = mysql

# InnoDB Settings
default-storage-engine          = InnoDB
innodb-buffer-pool-instances    = 2     # Use 1 instance per 1GB of InnoDB pool size
innodb-buffer-pool-size         = 2G    # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0
innodb-file-per-table           = 1
innodb-flush-log-at-trx-commit  = 0
innodb-flush-method             = O_DIRECT
#innodb-io-capacity             = 1000
#innodb-io-capacity_max         = 3000
innodb-log-buffer-size          = 16M
innodb-log-file-size            = 128M
#innodb-max-dirty-pages-pct     = 0
#innodb-thread-concurrency      = 4     # Optional: Set to the number of CPUs on your system (minus 1 or 2) to better contain CPU usage
                                        # E.g. if your system has 8 CPUs, try 6 or 7 and check the overall load from MySQL.

# MyISAM Query Cache Settings
query-cache-limit               = 4M    # UPD
query-cache-size                = 48M   # UPD
query-cache-type                = 1

key-buffer-size                 = 48M   # UPD

low-priority-updates            = 1
concurrent-insert               = 2

# Common
max-connections                 = 100   # UPD
back-log                        = 512

wait-timeout                    = 90
interactive-timeout             = 90

join-buffer-size                = 2M    # UPD
read-buffer-size                = 2M    # UPD
read-rnd-buffer-size            = 4M    # UPD
sort-buffer-size                = 4M    # UPD

thread-cache-size               = 100   # UPD (most of the times you probably won't need to change this)
thread-stack                    = 192K

max-heap-table-size             = 128M
tmp-table-size                  = 128M

table-definition-cache          = 8000  # UPD
table-open-cache                = 8000  # UPD
#table-open-cache-instances     = 16    # For MySQL 5.7+ only
open-files-limit                = 24000 # UPD

ft-min-word-len                 = 3     # Minimum length of words to be indexed for search results

expire-logs-days                = 7
#log-bin
log-error                       = /var/lib/mysql/mysql_error.log
log-queries-not-using-indexes   = 1
long-query-time                 = 8
max-binlog-size                 = 100M
#server-id                      = 1
slow-query-log                  = 1
slow-query-log-file             = /var/lib/mysql/mysql_slow.log
#sync-binlog                    = 0

[mysqldump]
quick
quote-names
max-allowed-packet              = 16M

[mysql]

[isamchk]
key-buffer-size                 = 16M