Install MySQL Server 5.6 (CentOS/Redhat)

Introduction

In October 2013, the MySQL development team officially launched support for yum repositories. This means that you can now ensure that you have the latest and greatest version of MySQL installed directly from the source!.In this guide we will install MySQL 5.6 on a fresh installation of Centos 6.

Installing MySQL

The yum repository file needs to be downloaded from MySQL’s developer website. Once installed, a simple yum update will make sure you are running on the latest point release of MySQL 5.6, including security updates. Yum also ensures that any dependencies are also installed, which makes the installation process just a bit simpler.

Details of system where practical has been applied

Operating System: CentOS 6.To install the MySQL server 5.6,follow the given below steps.

Step 1: Login into the Server and download the yum repo rpm package.

(URL: http://dev.mysql.com/downloads/repo/)5
Arch: x86_64 and i386 (Both)

wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm

Step 2: Now install the downloaded rpm package.

rpm -ivh mysql-community-release-el6-5.noarch.rpm

 OR

Log into your server and then download this file. Below is an example URL– you may want to double-check there is not a later version of the repo available:

wget http://dev.mysql.com/get/mysql-community-release-el6-3.noarch.rpm/from/http://repo.mysql.com/


Install the repository from the local file:

sudo yum localinstall mysql-community-release-el6-*.noarch.rpm

Step 3: Install Packages  and remove the Pre-installed mariadb Libraries 

yum remove mariadb-libs

yum install perl-Data-Dumper.x86_64

yum install perl-Test-Simple.noarch

rpm -ivh jemalloc-3.6.0-8.el7.centos.src.rpm

rpm -ivh jemalloc-3.3.0-1.fc16.src.rpm

yum -y install screen.x86_64

yum install perl

Check the Installed Packages :

[root@server22 yum.repos.d]# yum repolist enabled | grep mysql
mysql-connectors-community/x86_64 MySQL Connectors Community                  30
mysql-tools-community/x86_64      MySQL Tools Community                       40
mysql56-community/x86_64          MySQL 5.6 Community Server                 304
mysql56-community-source          MySQL 5.6 Community Server - Source          0

Step 4: Now install the mysql server by using yum command.

Note: The yum command by-default also install the dependencies

sudo yum install mysql-community-server

Step 5: After installation start the mysql server

sudo service mysqld start

OR

sudo service mysqld start

Step 6: Configure MySQL to start automatically on reboot:

sudo chkconfig mysqld on
chkconfig --list mysqld

Step 6: setup Password for Mysql User :

  /usr/bin/mysqladmin -u root password 'new-password'
  /usr/bin/mysqladmin -u root -h perconaserver1 password 'new-password'

Default Password can be Read from :

# cat /var/log/mysqld.log | grep "temporary password"
2016-05-16T07:09:49.796912Z 1 [Note] A temporary password is generated for root@localhost: 8)13ftQG5OYl

Step 7: You can use the given below command, and follow the instruction :

 /usr/bin/mysql_secure_installation

Reset Root Password :

Reset Root Password 

service mysql stop

/usr/bin/mysqld_safe --skip-grant-tables &

mysql -uroot

use mysql ;

update user set password=PASSWORD("server") where User='root';

flush privileges;

Applying Finishing Touches The MySQL development team put in a lot of effort to make sure that MySQL is better tuned out of the box, and 5.6 requires very little configuration. Having said that, there will be a couple of things you will want to tweak, which can be added to /etc/my.cnf under the [mysqld] heading group: sudo vim /etc/my.cnf It is recommended to set innodb_buffer_pool_size to 50-80% of system memory. In the case of the server I selected, 50% of 4GB = 2GB. This allows MySQL to cache more data (the default is only 128M) and can improve performance significantly. MySQL defaults to having very small transaction logs, which is a feature used to provide crash recovery. In development small log files can be useful to save space, but in production you will want to increase these to allow more writes to queue up in the background. The setting is innodb_log_file_size and recommended values range between 128M and 4G. When using SSDs, sequential IO is no faster than random IO. This means we can tell MySQL it can disable one of the optimizations it does and save a little CPU. The setting is innodb_flush_neighbors=0. By default, MySQL is exceptionally cautious to not lose any data even if there is a power loss. This comes at a performance cost, and in many cloud environments users chose to allow a few seconds of data loss on power failure instead. The setting to change is innodb_flush_log_at_trx_commit=2. UTF-8 is a better default for storing international characters in MySQL. You can change to it by setting character-set-server=utf8mb4 and collation-server=utf8mb4_general_ci. Individual databases, tables and columns can still overwrite this if necessary. Many syadmins recommend setting the timezone of servers to GMT, which can be done with timezone=GMT. To ensure compatibility with older versions, MySQL defaults to allowing incorrect and out of range values. I recommend that you enable the new stricter SQL_MODE options as long as your application(s) do not rely on this legacy MySQL behaviour. *All together, these are the changes: innodb_buffer_pool_size=2G innodb_log_file_size=256M innodb_flush_neighbors=0 innodb_flush_log_at_trx_commit=2 # Default to UTF-8 for text columns character-set-server=utf8mb4 collation-server=utf8mb4_general_ci # Set the default timezone to GMT. # This is a common recommended practice, but can also remove this line # If your date/time values appear incorrect. timezone=GMT

Sample my.cnf File :

For 2 GB RAM

#***** MySQL Configuration File for MysqlServer-56-5.6.35 *****#
[mysqld]
#ssl
#ssl-ca=/path//etc/ca-cert.pem
#ssl-cert=/path//etc/server-cert.pem
#ssl-key=/path//etc/server-key.pem
port=3306
report-host=server22
report-port=3306
performance_schema=ON

#------------------------------------------------------------------------------
# innodb_buffer_pool_size = n
#
# The size in bytes of the memory buffer InnoDB uses to cache data and indexes
# of its tables. The larger you set this value, the less disk I/O is needed to
# access data in tables. On a dedicated database server, you may set this to up
# to 70% of the machine physical memory size. However, do not set it too large
# because competition for physical memory might cause paging in the OS.
# Set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
#innodb_buffer_pool_size = 128M

#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
log_bin=/opt/mysql/3306/bin-log/binary-log
log-bin-index=/opt/mysql/3306/bin-log/binary-log.index
server-id = 0555
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#------------------------------------------------------------------------------
# datadir = $dirpath
#
# The MySQL data directory (Where DB files are stored).
datadir=/var/lib/mysql/data 
#------------------------------------------------------------------------------
# socket
#
# name of the socket file that is used for local client connections. The
# default is /tmp/mysql.sock.
socket=/var/lib/mysql/mysql.sock
# Log files
#------------------------------------------------------------------------------
#general_log=[0|1]
#
# Whether logging of all statements to the general query log is enabled.
general_log=1

#------------------------------------------------------------------------------
# general_log_file=filename
#
# General query log to capture all statements
general_log_file=/var/log/general.log
#------------------------------------------------------------------------------
# log_slave_updates = [1|0] (Required for Backup/Repeaters/Failover Servers)
#
# Whether updates received by a slave server from a master server should be
# logged to the slave's own binary log.
log-slave-updates = 1

#------------------------------------------------------------------------------
# slow-query-log=[0|1]
#
# Specify the initial slow query log state
# 1 = on
# 2 = off
slow-query-log=1

#------------------------------------------------------------------------------
# slow_query_log_file=$filename (Slow Query Log)
#
# slow query log file. "Slow" is determined by the value of
# the long_query_time variable.
slow_query_log_file= /var/log/slow.log

#------------------------------------------------------------------------------
# long_query_time = n
#
# If a query takes longer than this many seconds, the server increments the
# Slow_queries status variable. If you are using the --log-slow-queries option,
# the query is logged to the slow query log file. This value is measured in 
# real time, not CPU time, so a query that is under the threshold on a lightly
# loaded system might be above the threshold on a heavily loaded one. 
long_query_time = 0

#------------------------------------------------------------------------------
# log_queries_not_using_indexes = [1|0]
#
# Whether queries that do not use indexes are logged to the slow query log.
log_queries_not_using_indexes = 0

#------------------------------------------------------------------------------
# read-only = [0|1]
#
# When this option is set on, the server allows no updates except from users
# that have the SUPER privilege or (on a slave server) from updates performed
# by slave threads. On a slave server, this can be useful to ensure that the
# slave accepts updates only from its master server and not from clients.
read-only = 0


####################################
# Binary log configuration         #
####################################
#------------------------------------------------------------------------------
# log_bin=$filename (Binary Log)
#
# Whether the binary log is enabled.
log_bin=/var/lib/mysql/binary-log/binary-log
log-bin-index=/var/lib/mysql/binary-log/binary-log.index

#------------------------------------------------------------------------------
# binlog_format=[STATEMENT|ROW|MIXED]
#
# This variable sets the binary logging format
binlog_format=STATEMENT

#------------------------------------------------------------------------------
# max_binlog_size = n 
#
# If a write to the binary log causes the current log file size to exceed the
# value of this variable, the server rotates the binary logs (closes the 
# current file and opens the next one). You cannot set this variable to more 
# than 1GB or to less than 4096 bytes. The default value is 1GB.


#------------------------------------------------------------------------------
# relay-log = $file
#
# The basename for the relay log. The default basename is host_name-relay-bin.
# The server creates relay log files in sequence by adding a numeric suffix to
# the basename. 
relay-log = /var/lib/mysql/relay/relay-bin

#------------------------------------------------------------------------------
# relay-log-index = $file
#
# The name to use for the relay log index file. The default name is
# host_name-relay-bin.index in the data directory, where host_name is the name
# of the slave server.
relay-log-index = /var/lib/mysql/relay/relay-bin.index

#------------------------------------------------------------------------------
# relay-log-info-file = $file
#
# The name to use for the file in which the slave records information about
# the relay logs. The default name is relay-log.info in the data directory.
relay-log-info-file = /var/lib/mysql/relay/relay-log.info
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
# replicate-ignore-db=db_name
# 
# Tells the slave to not replicate any statement where the default database 
# (that is, the one selected by USE) is db_name. 
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
binlog-ignore-db=performance_schema

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#------------------------------------------------------------------------------
expire_logs_days = 4
#
# The number of days for automatic binary log removal. The default is 0, which
# means 'no automatic removal.' 
# Note: removals happen at startup and at binary log rotation.

#crash Safe Relay log Config Options   #

relay_log_info_repository = TABLE

relay_log_recovery = ON
sync_master_info = 1
master_info_repository = TABLE
# max_connections = n 
#
# The number of simultaneous client connections allowed. Default is 100. Note: 
# Increasing this increases the number of file descriptors that mysqld requires
max_connections = 2400

[mysqld_safe]
#------------------------------------------------------------------------------
# log_error=$filename (Errorlog)
#
# The location of the error log.
log-error=/var/log/mysqld.log
MySQL1-file=/var/run/mysqld/mysqld.pid

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Powered by WordPress.com.

Up ↑

%d bloggers like this: