Friday, 2 February 2018

How to Install Latest MySQL 5.7.21 on RHEL/CentOS 7

MySQL is an open source free relational database management system (RDBMS) released under GNU (General Public License). 

This article will walk through you the process of installing and updating latest MySQL 5.7


Yum Installation 

Step 1 : Adding the MySQL Yum Repository


NoteThese instructions only works on fresh installation of MySQL on the server, if there is already a MySQL installed using a third-party-distributed RPM package, please upgrade or replace the installed MySQL package using the MySQL Yum Repository”.

Download and add the following MySQL Yum repository to your respective Linux distribution system’s repository list to install the latest version of MySQL.

$ wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

After downloading the package for your Linux platform, now install the downloaded package with the following command.

$ yum localinstall mysql57-community-release-el7-11.noarch.rpm

You can verify that the MySQL Yum repository has been added successfully by using following command.

$ yum repolist enabled | grep "mysql.*-community.*"

Step 2: Installing Latest MySQL Version

$ yum install mysql-community-server

Note : Installing MySQL Release Series

To install specific version from specific sub-repository, you can use --enable or --disable options

$ yum-config-manager --disable mysql57-community
$ yum-config-manager --enable mysql56-community

Step 3 :  Starting the MySQL Server

After installation start the MySQL server with following command 

$ service mysqld start

Verify the status :

$ service mysqld status

Redirecting to /bin/systemctl status  mysqld.service
mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled)
Active: active (running) since Thu 2015-10-29 05:15:19 EDT; 4min 5s ago
Process: 5314 ExecStart=/usr/sbin/mysqld --daemonize $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 5298 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 5317 (mysqld)
CGroup: /system.slice/mysqld.service
└─5317 /usr/sbin/mysqld --daemonize
Oct 29 05:15:19 localhost.localdomain systemd[1]: Started MySQL Server.

Step 4 : Securing the MySQL Installation


At the initial start-up of the server, the following happens, when MySQL data directory is empty:
  • The server is initialized.
  • An SSL certificate and key files are generated in the data directory.
  • Thevalidate_password plugin is installed and enabled.
  • A superuser account’root’@’localhost’ is created. A password for the superuser is set and stored in the error log file.
  • Look for root password in error log file.
The command mysql_secure_installation allows you to secure your MySQL installation by performing important settings like setting the root password, removing anonymous users, removing root login etc.

Get the root user password :


$ grep 'temporary password' /var/log/mysqld.log

Execute the secure mysql installation tool

$ mysql_secure_installation

Step 5 : Connecting to MySQL Server

$ mysql -u root -p

Step 6 : Change the root@'localhost' password

MySQL 5.7.6 and later:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

MySQL 5.7.5 and earlier:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

 Step 7 : MySQL configuration file: /etc/my.cnf

By, default my.cnf will get created by MySQL rpm installation with default configuration in it. We need to add few more configuration variables in order to make MySQL DB server ready for production use. Following are the standard configuration settings for a production database. These variable values may vary as per the application scope and data workload.


[mysqld]
 
user = mysql
port = 3306
server_id=152
skip-name-resolve
default_storage_engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid_file = /var/run/mysql/mysqld.pid

datadir =/var/lib/mysql/data
 

innodb_file_per_table=1
innodb_buffer_pool_size = 4000M (60-70 % of RAM memory)
innodb_data_file_path= ibdata1:1G:autoextend
innodb-log-files-in-group=3
innodb_flush_method = O_DIRECT
 

log_error = /var/log/mysql/mysqld.log
master_info_repository = TABLE
relay_log_info_repository = TABLE
log-bin=mysql-bin152
relay-log=relay-bin152
relay_log_recovery=on
log-slave-updates= 1
expire_logs_days = 7
gtid-mode=on
enforce-gtid-consistency=1
binlog_format=row
 
[mysql]
socket = /var/lib/mysql/mysql.sock
 
[client]
socket = /var/lib/mysql/mysql.sock
port = 3306

Restart the mysql service once the changes are being made.

MySQL Variables
Usermysql service user
Server-idValue : 1 default
Any number in DB group
PortValue: 3306 default
Skip-name-resolveDo not resolve host names when checking client connections. Use only IP addresses.
bind_addressMySQL bind_address for network interfaces.
IPv4 : 0.0.0.0
IPv4 and IPv6 : *
SocketUnix socket file for listening local connections
Pid-fileThe path name of the process ID file.
default_storage_engineDefault storage engine for MySQL
Value: Innodb
DatadirMain directory where database,system tablespace and log files will get store.
innodb_file_per_tableSeperate tablespace for each table.Good for performance and reclaiming free space.
Value : on
Innodb_buffer_pool_sizeValue should be 60-70 percent of RAM memory of server
innodb_log_file_sizeRedo and undo logs ,useful for innodb recovery.
Value should be greater if you are using BLOB datatype in your database.
Value: innodb_log_file_size=150M
innodb_log_files_in_groupNumber for innodb_log_file
Value : 3
innodb_data_file_path=System tablespace configuration
Value:ibdata1:1G;ibdata2:1G:autoextend (vary)
innodb_flush_methodMethod used to flush data to the InnoDB data files and log files.
value : O_DIRECT
innodb_tmpdirtmp directory for ONLINE ALTER operations.
log_errormysql server log
log-binBinary log file name
Value : mysql-bin152
binlog_formatbinary log formate for data
Value:row
master_info_repositorycrash-safe replication settings, storing log info in table instead of file.
relay_log_info_repositoryValue:  TABLE
relay-logrelay log name
relay-log=relay-bin152
relay_log_recoveryrelay_log_recovery= on
log-slave-updateslog-slave-updates=1
expire_logs_daysAuto delete binary logs after mentioned days
expire_logs_days= 60
gtid-modeEnable GTID for transactions
Value : on
enforce-gtid-consistencyValue : on

RPM Installation 

The following list shows the available RPM packages. 

  • MySQL-server-VERSION.glibc23.i386.rpm

The MySQL server. You need this unless you only want to connect to a MySQL server running on another machine.

  • MySQL-client-VERSION.glibc23.i386.rpm

The standard MySQL client programs. You probably always want to install this package.

  • MySQL-devel-VERSION.glibc23.i386.rpm

The libraries and include files needed to compile other MySQL clients, such as the Perl MySQL module. 
Install this RPM if you intend to compile C API applications.

  • MySQL-shared-VERSION.glibc23.i386.rpm

The shared libraries (libmysqlclient.so*) that certain languages and applications need to dynamically load and use MySQL. 
It contains single-threaded and thread-safe libraries. 

  • MySQL-shared-compat-VERSION.glibc23.i386.rpm

The shared libraries for older releases. It contains single-threaded and thread-safe libraries. 
Install this package if you have applications installed that are dynamically linked against older versions of MySQL but you want to upgrade to the current version without breaking the library dependencies.

  • MySQL-embedded-VERSION.glibc23.i386.rpm

The embedded MySQL server library.

  • MySQL-test-VERSION.glibc23.i386.rpm


The MySQL test suite.

Note : Before Installation remove MariaDB library package and postfix package.

$ yum remove postfix mariadb-libs



$  yum swap mariadb-libs mysql-community-libs

Step 1 : Download the rpm bundles 

Download the rpm Bundles from https://dev.mysql.com/downloads/mysql/

$ wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.21-1.el7.x86_64.rpm-bundle.tar

After installation extract the rpm bundle

$ tar -xvf mysql-5.7.21-1.el7.x86_64.rpm-bundle.tar



Install perl 

$ yum -y install perl

Step 2 : Install the MySQL 5.7 rpm's now 

$ rpm -ivh mysql-community-common-5.7.21-1.el7.x86_64.rpm
$ rpm -ivh mysql-community-libs-5.7.21-1.el7.x86_64.rpm
$ rpm -ivh mysql-community-libs-compat-5.7.21-1.el7.x86_64.rpm
$ rpm -ivh mysql-community-embedded-5.7.21-1.el7.x86_64.rpm
$ rpm -ivh mysql-community-embedded-compat-5.7.21-1.el7.x86_64.rpm
$ rpm -ivh mysql-community-client-5.7.21-1.el7.x86_64.rpm
$ rpm -ivh mysql-community-server-5.7.21-1.el7.x86_64.rpm

Note : Make sure percona and Maria-DB libaries are completely removed

Get the root user password :


$ grep 'temporary password' /var/log/mysqld.log

Execute the secure mysql installation tool


$ mysql_secure_installation

Step 3 :  Starting the MySQL Server

After installation start the MySQL server with following command 


$ service mysqld start

Enable the mysql services 

$ service mysqld enable


TAR Installation


This is the only work which has to be done under a privileged account (root):


shell> sudo yum install libaio
shell> sudo groupadd mysql
shell> sudo useradd -r -g mysql -s /bin/bash mysql
shell> sudo cp mysqld@.service /etc/systemd/system/

Installing the binaries :


shell> mkdir /home/mysql/product
shell> cd /home/mysql/product
shell> tar xf /download/mysql-<version>.tar.gz
shell> ln -s mysql-<version> mysql-5.7.x
shell> ln -s mysql-5.7.x mysql
shell> echo 'export PATH=$PATH:/home/mysql/product/mysql/bin' >> ~/.bashrc
shell> . ~/.bashrc

Creating, starting and stopping several mysql enterprise server instances


shell> export INSTANCE_NAME=MYSQL01   # and MYSQL02 and MYSQL03
shell> mkdir -p /mysql/${INSTANCE_NAME}/etc /mysql/${INSTANCE_NAME}/log /mysql/${INSTANCE_NAME}/data /mysql/${INSTANCE_NAME}/binlog
shell> cat /mysql/${INSTANCE_NAME}/etc/my.cnf
#
# /mysql/${INSTANCE_NAME}/etc/my.cnf
#
[mysqld]
datadir   = /mysql/${INSTANCE_NAME}/data
pid_file  = /var/run/mysqld/mysqld_${INSTANCE_NAME}.pid
log_error = /mysql/${INSTANCE_NAME}/log/error_${INSTANCE_NAME}.log
port      = 3306   # and 3307 and 3308
socket    = /var/run/mysqld/mysqld_${INSTANCE_NAME}.sock
_EOF
shell> cd /home/mysql/product/mysql
shell> bin/mysqld --defaults-file=/mysql/${INSTANCE_NAME}/etc/my.cnf --initialize --user=mysql --basedir=/home/mysql/product/mysql
shell> bin/mysqld --defaults-file=/mysql/${INSTANCE_NAME}/etc/my.cnf --daemonize >/dev/null 2>&1 &
shell> mysqladmin --user=root --socket=/var/run/mysqld/mysqld_${INSTANCE_NAME}.sock --password shutdown

The MySQL Database Instances should be started automatically at system reboot. For this we need a Systemd unit file:


#
# /etc/systemd/system/mysqld@.service
#

[Unit]

Description=Multi-Instance MySQL Enterprise Server
After=network.target syslog.target


[Install]

WantedBy=multi-user.target
 

[Service]

User=mysql
Group=mysql
Type=forking
PIDFile=/var/run/mysqld/mysqld_%i.pid
TimeoutStartSec=3
TimeoutStopSec=3
# true is needed for the ExecStartPre
PermissionsStartOnly=true
ExecStartPre=/bin/mkdir -p /var/run/mysqld
ExecStartPre=/bin/chown mysql: /var/run/mysqld
ExecStart=/home/mysql/product/mysql/bin/mysqld --defaults-file=/mysql/%i/etc/my.cnf --daemonize
LimitNOFILE=8192
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false


shell> cp mysqld@.service /etc/systemd/system/

Reload the daemon process 


shell> sudo systemctl daemon-reload
shell> sudo systemctl enable mysqld@MYSQL01   
shell> sudo systemctl start mysqld@MYSQL01
shell> sudo systemctl status 'mysqldTMYSQL*'
shell> sudo systemctl start mysqld@MYSQL01


MySQL DB Monitoring using Bash Script 

Create a user for monitoring purpose 


CREATE USER 'monitor'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT,PROCESS ON *.* TO 'monitor'@'localhost';

Sample script To check server status:


#!/bin/sh
 
EMAIL_IDS=alert@gmail.com
 
# Connection details
 
MYSQL_USER=monitor
 
MYSQL_PASS=password
 
MYSQL_HOST=localhost
 
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST}"
 
SERVER_HOST=$( hostname )
 
# MySQL status
 
mysqladmin ${MYSQL_CONN} ping 2>/dev/null 1>/dev/null
 
if [ $? -ne 0 ]; then
 
echo "MySQL Down" | mail -s " MySQL not running on $SERVER_HOST" "$EMAIL_IDS"
 
fi


3 comments:

  1. Great Blog Post! This is really Helpful. Thank you!

    ReplyDelete
  2. Good explanation,thanks for writing,it is useful for so many developers
    AWS Online Training

    ReplyDelete