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
User mysql service user
Server-id Value : 1 default
Any number in DB group
Port Value: 3306 default
Skip-name-resolve Do not resolve host names when checking client connections. Use only IP addresses.
bind_address MySQL bind_address for network interfaces.
IPv4 : 0.0.0.0
IPv4 and IPv6 : *
Socket Unix socket file for listening local connections
Pid-file The path name of the process ID file.
default_storage_engine Default storage engine for MySQL
Value: Innodb
Datadir Main directory where database,system tablespace and log files will get store.
innodb_file_per_table Seperate tablespace for each table.Good for performance and reclaiming free space.
Value : on
Innodb_buffer_pool_size Value should be 60-70 percent of RAM memory of server
innodb_log_file_size Redo 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_group Number for innodb_log_file
Value : 3
innodb_data_file_path= System tablespace configuration
Value:ibdata1:1G;ibdata2:1G:autoextend (vary)
innodb_flush_method Method used to flush data to the InnoDB data files and log files.
value : O_DIRECT
innodb_tmpdir tmp directory for ONLINE ALTER operations.
log_error mysql server log
log-bin Binary log file name
Value : mysql-bin152
binlog_format binary log formate for data
Value:row
master_info_repository crash-safe replication settings, storing log info in table instead of file.
relay_log_info_repository Value:  TABLE
relay-log relay log name
relay-log=relay-bin152
relay_log_recovery relay_log_recovery= on
log-slave-updates log-slave-updates=1
expire_logs_days Auto delete binary logs after mentioned days
expire_logs_days= 60
gtid-mode Enable GTID for transactions
Value : on
enforce-gtid-consistency Value : 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

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: