mysql setup

grant.sql

GRANT ALL PRIVILEGES ON *.* TO 'mysqluser'@'%' IDENTIFIED BY 'password';

FLUSH PRIVILEGES;

mysqld.cnf

[mysqld_safe]
log_error=/var/log/mysql/mysql-error.log
nice    = 0

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log_error      = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
general_log             = 1
general_log_file        = /var/log/mysql/mysql.log

slow_query_log         = 1
slow_query_log_file     = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes   = 1
log_slow_admin_statements       = 1

# Engine
default-storage-engine  = InnoDB

# Bin logs
binlog-format           = ROW
log-bin                 = /var/lib/mysql/mysql-bin
log-slave-updates       = 1
expire-logs-days        = 90
sync-binlog             = 1
max_binlog_size         = 100M

server-id = 1 # randomize it incase of multiple servers

# InnoDB
innodb-buffer-pool-size         = 2048M
innodb_buffer_pool_instances    = 8
innodb_additional_mem_pool_size = 20M
innodb_log_buffer_size          = 8M
innodb-log-files-in-group       = 2
innodb-log-file-size            = 256M
innodb-file-per-table           = 1
innodb-flush-log-at-trx-commit  = 1
innodb-flush-method             = O_DIRECT

# With virtual synchrony redundancy, make write queries faster
innodb_doublewrite              = 1

# This is a recommended tuning variable for performance
innodb_locks_unsafe_for_binlog  = 1

# Safety
max-connect-errors      = 1000000
max_allowed_packet      = 64M
skip-name-resolve
sysdate-is-now          = 1
innodb                  = FORCE
innodb-strict-mode      = 1

wait_timeout            = 60
interactive_timeout     = 60

# Buffers
sort_buffer_size        = 4M
read_buffer_size        = 2M
join_buffer_size        = 8M
read_rnd_buffer_size    = 16M

thread_concurrency      = 16 # Max CPU * 2

# CACHES AND LIMITS #
tmp-table-size          = 128M
max-heap-table-size     = 128M
query-cache-type        = 0
query-cache-size        = 0
query_cache_limit       = 1M
max-connections         = 5000
thread-cache-size       = 50
thread_stack            = 192K
open-files-limit        = 65535
table-definition-cache  = 1024
table-open-cache        = 2048

setup

When the installation is complete, we need to run some additional commands to get our MySQL environment set up securely.

First, we need to tell MySQL to create its database directory structure where it will store its information. You can do this by typing:

> sudo mysql_install_db

Afterwards, we want to run a simple security script that will remove some dangerous defaults and lock down access to our database system a little bit. Start the interactive script by running:

> sudo mysql_secure_installation

You will be asked to enter the password you set for the MySQL root account. Next, it will ask you if you want to change that password. If you are happy with your current password, type "n" for "no" at the prompt.

For the rest of the questions, you should simply hit the "ENTER" key through each prompt to accept the default values. This will remove some sample users and databases, disable remote root logins, and load these new rules so that MySQL immediately respects the changes we have made.

At this point, your database system is now set up and we can move on.

------------ Ubuntu config ----------------
conf: /etc/mysql/my.cnf
init: /etc/init/mysql.conf