#!/bin/bash
# Huang Jinqiang <hzkeung@vip.qq.com>
# notice: server-id use mysql port and ip last bit
 
port=3306
ip_last=$(/usr/sbin/ip addr show|awk -F'[./]' '/inet / && !/127.0.0.1/{print $4}')
 
yum -y update
yum -y install gcc gcc-c++ openssl openssl-devel zlib zlib-devel libaio wget lsof vim-enhanced sysstat ntpdate
sed -i '/SELINUX/s/enforcing/disabled/' /etc/selinux/config
setenforce 0
 
ntpdate 0.pool.ntp.org
timedatectl set-timezone "Asia/Shanghai"
 
groupadd -g 27 mysql
useradd -g 27 -u 27 -M -d /usr/local/mysql -s /sbin/nologin mysql
 
mkdir /opt/mysql /data/mysql/mysql${port}/{data,logs,tmp} -p
 
cd /opt
#wget -c http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.14-linux-glibc2.5-x86_64.tar.gz
tar xf mysql-5.7.14-linux-glibc2.5-x86_64.tar.gz -C /opt/mysql
ln -s /opt/mysql/mysql-5.7.14-linux-glibc2.5-x86_64 /usr/local/mysql
chown mysql.mysql /opt/mysql /usr/local/mysql /data/mysql -R
echo 'PATH=$PATH:/usr/local/mysql/bin' > /etc/profile.d/mysql.sh
echo "alias mysql='mysql -A -U'" >> /etc/profile.d/mysql.sh
source /etc/profile
 
cat > /etc/my.cnf << EOF
#my.cnf
[client]
port = ${port}
socket = /tmp/mysql.sock
 
[mysql]
prompt="\u@\h [\d]>" 
 
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/mysql${port}/data
port = ${port}
socket = /tmp/mysql.sock
event_scheduler = 0
explicit-defaults-for-timestamp=on
tmpdir = /data/mysql/mysql${port}/tmp

#timeout
interactive_timeout = 300
wait_timeout = 300
 
#character set
character-set-server = utf8
 
open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000

#logs
log-output=file
slow_query_log = 1
slow_query_log_file = slow.log
log-error = error.log
log_error_verbosity=3
pid-file = mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1
 
#binlog
binlog_format = row
server-id = ${port}${ip_last}
log-bin = /data/mysql/mysql${port}/logs/mysql-bin
binlog_cache_size = 4M
max_binlog_size = 256M
max_binlog_cache_size = 1M
sync_binlog = 0
expire_logs_days = 10
#procedure 
log_bin_trust_function_creators=1
 
#
gtid-mode=on
binlog_gtid_simple_recovery = 1
enforce_gtid_consistency = 1
log_slave_updates
 
#relay log
skip_slave_start = 1
max_relay_log_size = 128M
relay_log_purge = 1
relay_log_recovery = 1
relay-log=relay-bin
relay-log-index=relay-bin.index
#slave-skip-errors=1032,1053,1062
#skip-grant-tables
 
#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 128K
join_buffer_size = 128K
thread_cache_size = 200
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M
 
#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
 
#innodb
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:100M:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M
innodb_log_file_size = 100M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT
EOF
 
cd /usr/local/mysql/
./bin/mysqld --initialize
cp support-files/mysql.server /etc/init.d/mysqld
/etc/init.d/mysqld start