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
Note: These 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