MySQL 5.6 Installation from Binaries

MySQL 5.6 version Installation from Binaries

Step 1:

Create MySQL User on the server first.

userdel mysql;

groupadd -g 9251 mysql;

useradd -m -d /home/mysql -c "DB Users" -s /bin/bash -u 8251  -g 9251 mysql;

echo 'mysql' | passwd --stdin mysql;

echo 'mysql ALL=(ALL) NOPASSWD : ALL' >/etc/sudoers;

cat /etc/sudoers | grep -i mysql;

id mysql

Step 2:

Install MySQL

Download binaries from MySQL site and ensure the following path and pre-requisites are met.

Following mount points to be created :-

/mysqldata                 --  500GB

/mysqlbackup            --  500GB

/mysqlbinlog             --  300G

/mysqltemp               --   100G

/OSwatcher              --   50G

/oracle                       --    50G

Need “mysql” & “oracle” users (OS) to be created. From “mysql”, “oracle” user & it should not expire. Crontab access should be enabled for mysql user. Need root access so that can install MTOP, INNOTOP, OSWATCHER & other utilities. Need Mailx & SMTP service should be enabled for all servers. We need to setup open files limit and open process limit with below format.

cat /etc/security/limits.conf

mysql           soft     nproc       65536

mysql           hard    nproc       65536

mysql           soft     nofile        65536

mysql           hard    nofile        65536 

Follow the recommendation for kernel tuning and hardware also like swapiness and battery-backed cached RAID etc

Step 2:

Switch to mysql user and perform following  steps to install MySQL from binaries.

cd /mysqldata

mkdir data config software mysqlbinary

cd /mysqlbinlog

mkdir slowlog binlog  #will prefer to create table to can manager slow log more effectively

cp /tmp/p24372314_56_Linux-x86-64.zip /mysqldata/software/

cd  /mysqldata/software

unzip p24372314_56_Linux-x86-64.zip

mv mysql-advanced-5.6.32-linux-glibc2.5-x86_64.tar.gz ../mysqlbinary/

cd /mysqldata/mysqlbinary/

tar -zxvf mysql-advanced-5.6.32-linux-glibc2.5-x86_64.tar.gz

mv mysql-advanced-5.6.32-linux-glibc2.5-x86_64 mysql

vi /mysqldata/config/my.cnf

cd /mysqldata/mysqlbinary/mysql

./scripts/mysql_install_db --defaults-file=/mysqldata/config/my.cnf

Start MySQL

 ./bin/mysqld_safe --defaults-file=/mysqldata/config/my.cnf  &

Create alias for mysql login and start/stop service to manage

============================================================================
Login , shutdown and Startup  commands
============================================================================

Login -   mysql -u root -p -S /mysqldata/data/mysqld.sock

Shutdown -  ./bin/mysqladmin -u root -p -S /mysqldata/data/mysqld.sock shutdown

==============

Conf file

==============

[client]
port            = 3306
socket          = /data/mysqldata/mysqld.sock

[mysqld_safe]
socket          = /data/mysqldata/mysqld.sock
nice            = 0

[mysqld]
server-id = 1
character-set-server=utf8
port            = 3306
pid-file        = /data/mysqldata/mysqld.pid
socket          = /data/mysqldata/mysqld.sock
datadir         = /data/mysqldata
log-error       = /data/mysqldata/myqlerr.log
basedir         = /data/mysqlbinary/mysql/
tmpdir          = /data/mysqltemp

max_allowed_packet = 100M

skip-name-resolve

# Max Connections
max_connections=100

# Per Thread Buffers
read_buffer_size=1M
join_buffer_size=1M
sort_buffer_size=1M
read_rnd_buffer_size = 1M

# MyISAM buffers
key_buffer_size=16M

# Temporary Table
tmp_table_size=16M
max_heap_table_size=16M

# File Related
core-file
local-infile=1
secure_file_priv =/tmp/
open-files-limit=60000

### Changes for Mysql 5.6 ###
innodb_checksum_algorithm=INNODB
table_open_cache=3000
innodb_open_files=5000
table_definition_cache=3000
thread_cache_size=32

###  Slow query Log ###
long_query_time=1
slow_query_log=1
slow_query_log_file=/data/mysqldata/slowquery.log

##InnoDB Settings
innodb_buffer_pool_size=4G
innodb_purge_threads=1
innodb_buffer_pool_instances=6
innodb_thread_concurrency=64
innodb_log_file_size=1024M
innodb_log_buffer_size=16M
max_binlog_size=1G

## Mysql suggested Parameter
innodb_stats_on_metadata=0
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=1
max_connect_errors=20000
wait_timeout=180
interactive_timeout=180
log-warnings=2
log-raw=OFF
skip-grant-tables=FALSE
symbolic-links=0
plugin-load-add=validate_password.so
sql_mode=STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION

##Replication related
log-bin=/data/mysqldata1/mysql-bin
slave-compressed-protocol=1
binlog_format=MIXED
expire_logs_days=1
master_info_repository=TABLE

##MBSS parameters##
log-warnings = 2
log-raw = OFF
secure_auth=ON
local-infile=1
sql_mode=STRICT_ALL_TABLES,NO_AUTO_CREATE_USER
skip-grant-tables = FALSE
master_info_repository=TABLE
plugin-load=validate_password.so
validate-password=FORCE_PLUS_PERMANENT
validate_password_length=14
validate_password_mixed_case_count=1
validate_password_number_count=1
validate_password_special_char_count=1
validate_password_policy=MEDIUM