MySQL 5.7 Install | Configure MySQL | Configure MySQL Replication | Configure systemd for single instance

Install MySQL 5.7 Community Edition on Linux:

#yum install mysql80-community-release-el7-1.noarch.rpm
#yum install mysql-community-server
#yum install perl-DBD-MySQL-4.023-6.el7.x86_64.rpm
#yum install percona-release-0.1-4.noarch.rpm

Increase no. of open files:

Edit file /etc/security/limits.conf and includes as follows, which will increase no of open files for mysql user to 65535 from 1024 which is default.

excute ulimit -a after sudo to mysql, if you are logged in exit and login again then and then only you will be able to see it.

mysql            soft    nofile          65535
mysql            hard    nofile          65535

Ref.:

https://dev.mysql.com/doc/refman/8.0/en/linux-installation-yum-repo.html
https://jinyuwang.weebly.com/for-mysql/how-to-enable-binary-logging-for-mysql

RPM Packages for MySQL Community Edition:

Package Name                                                                      Summary
mysql-community-server                    Database server and related tools
mysql-community-client                     MySQL client applications and tools
mysql-community-common                Common files for server and client libraries
mysql-community-server-minimal      Minimal installation of the database server and related
tools
mysql-community-devel         Development header files and libraries for MySQL database client applications           
mysql-community-libs                        Shared libraries for MySQL database client applications
mysql-community-libs-compat           Shared compatibility libraries for previous MySQL installations
mysql-community-embedded             MySQL embedded library
mysql-community-embedded-devel   Development header files and libraries for MySQL as an embeddable library
mysql-community-test                        Test suite for the MySQL server

Install MySQL 5.7.22 Community edition:

01. Install mysql80-community-release-el7-1.noarch.rpm on server either using yum or Ansible

It has repository for MySQL 5.5,5.6,5.7 and MySQL 8. To disable it by setting enable=0

            It will create mysql repository at /etc/yum.repos.d

            mysql-community.repo  mysql-community-source.repo

02. Enable by setting 1 as follows for MySQL 5.7 Community edition

            # Enable to use MySQL 5.7

            [mysql57-community]
            name=MySQL 5.7 Community Server
            baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
            enabled=1
            gpgcheck=1
            gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

03. Disable by setting 0 for MySQL 8.0 Community

            [mysql80-community]
            name=MySQL 8.0 Community Server
            baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch/
            enabled=0
            gpgcheck=1
            gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

04. Check enabled repo list   

[root@host_name yum.repos.d]# yum repolist enabled | grep mysql

mysql-connectors-community/x86_64 MySQL Connectors Community        51
mysql-tools-community/x86_64      MySQL Tools Community                       63
mysql57-community/x86_64          MySQL 5.7 Community Server                267

[root@host_name yum.repos.d]# yum install mysql-community-server

Check MySQL installation:

 [root@host_name yum.repos.d]# yum list all | grep mysql80

mysql80-community-release.noarch            el7-1                          @/mysql80-community-release-el7-1.noarch

[root@host_name yum.repos.d]# yum list all | grep mysql57

mysql-community-client.x86_64               5.7.22-1.el7                   @mysql57-community
mysql-community-common.x86_64               5.7.22-1.el7                   @mysql57-community
mysql-community-libs.x86_64                 5.7.22-1.el7                   @mysql57-community
mysql-community-libs-compat.x86_64          5.7.22-1.el7                   @mysql57-community
mysql-community-server.x86_64               5.7.22-1.el7                   @mysql57-community
mysql-community-client.i686                 5.7.22-1.el7                   mysql57-community
mysql-community-common.i686                 5.7.22-1.el7                   mysql57-community
mysql-community-devel.i686                  5.7.22-1.el7                   mysql57-community
mysql-community-devel.x86_64                5.7.22-1.el7                   mysql57-community
mysql-community-embedded.i686               5.7.22-1.el7                   mysql57-community
mysql-community-embedded.x86_64             5.7.22-1.el7                   mysql57-community
mysql-community-embedded-compat.i686        5.7.22-1.el7                   mysql57-community
mysql-community-embedded-compat.x86_64      5.7.22-1.el7                   mysql57-community
mysql-community-embedded-devel.i686         5.7.22-1.el7                   mysql57-community
mysql-community-embedded-devel.x86_64       5.7.22-1.el7                   mysql57-community
mysql-community-libs.i686                   5.7.22-1.el7                   mysql57-community
mysql-community-libs-compat.i686            5.7.22-1.el7                   mysql57-community
mysql-community-release.noarch              el7-7                          mysql57-community
mysql-community-test.x86_64                 5.7.22-1.el7                   mysql57-community
mysql-ref-manual-5.5-en-html-chapter.noarch 1-20170320                     mysql57-community
mysql-ref-manual-5.5-en-pdf.noarch          1-20170320                     mysql57-community
mysql-ref-manual-5.7-en-html-chapter.noarch 1-20180304                     mysql57-community
mysql-ref-manual-5.7-en-pdf.noarch          1-20180304                     mysql57-community
mysql57-community-release.noarch            el7-10                         mysql57-community

Create file /etc/instance_name.cnf:

[mysqld]
socket                                = /mysql/instance_name/data/instance_name.sock
port                                    = 3777
pid-file                               = /mysql/instance_name/data/instance_name.pid
basedir                              = /mysql/instance_name/data
datadir                               = /mysql/instance_name/data
user                                   =  mysql
tmpdir                                = /mysql/instance_name/temp
slow_query_log                 = on
slow_query_log_file          = /mysql/instance_name/logs/host_name-slow.log
log-error                            = /mysql/instance_name/logs/instance_name.err

Initialize data directories:

mysqld  --defaults-file=/etc/instance_name.cnf  --initialize-insecure &

Start mysqld:

Make sure to become user mysql

mysqld --defaults-file=/etc/instance_name.cnf &

Connect:

mysql -uroot -p<code> -S/mysql/instance_name/data/instance_name.sock

Stop mysqld:

mysqladmin -uroot -S/mysql/instance_name/data/instance_name.sock shutdown

mysql> select @@pid_file;
+-------------------------------+
| @@pid_file                    |
+-------------------------------+
| /mysql/instance_name/data/instance_name.pid |
+-------------------------------+

Configure systemd for single instance:

Edit file /usr/lib/systemd/system/mysqld.service

[root@host_name system]# cat mysqld.service

# Copyright (c) 2015, 2016, Oracle and/or its affiliates. All rights reserved.

#

# This program is free software; you can redistribute it and/or modify

# it under the terms of the GNU General Public License as published by

# the Free Software Foundation; version 2 of the License.

#

# This program is distributed in the hope that it will be useful,

# but WITHOUT ANY WARRANTY; without even the implied warranty of

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

# GNU General Public License for more details.

#

# You should have received a copy of the GNU General Public License

# along with this program; if not, write to the Free Software

# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#

# systemd service file for MySQL forking server

#

[Unit]

Description=MySQL Server

Documentation=man:mysqld(8)

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

After=network.target

After=syslog.target

[Install]

WantedBy=multi-user.target

[Service]

User=mysql

Group=mysql

Type=forking

#PIDFile=/var/run/mysqld/mysqld.pid

#Customize by Shrenik

PIDFile=/mysql/instance_name/data/instance_name.pid

# Disable service start and stop timeout logic of systemd for mysqld service.

TimeoutSec=0

# Execute pre and post scripts as root

PermissionsStartOnly=true

# Needed to create system tables

ExecStartPre=/usr/bin/mysqld_pre_systemd

# Start main service

#ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS

# Customization start

#PIDFile=/mysql/instance_name/data/instance_name.pid

# Start main service

ExecStart=/usr/sbin/mysqld --defaults-file=/etc/instance_name.cnf --daemonize --pid-file=/mysql/instance_name/data/instance_name.pid $MYSQLD_OPTS

# Set enviroment variable MYSQLD_PARENT_PID. This is required for restart.

Environment=MYSQLD_PARENT_PID=1

#Customization end Shrenik

# Use this to switch malloc implementation

EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit

LimitNOFILE = 5000

Restart=on-failure

RestartPreventExitStatus=1

PrivateTmp=false

Reload systemd:

[root@host_name system]# systemctl daemon-reload

Start system:

[root@host_name system]# systemctl start mysqld

[root@host_name system]# pwd
/usr/lib/systemd/system

Note:

Configuration error can be noticed when there is failed message during startup.

[root@host_name ~]# systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

Troubleshoot systemd:

Check /var/log/messges file to troubleshoot systemd startup issues.

[root@host_name ~]# systemctl start mysqld

When there is no message after start command means there is no error and you can connect to MySQL instance.

Alter root user:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY ‘<code>';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '<code>' WITH GRANT OPTION;

mysql> flush privileges;

Check you can connect or not:

$mysqladmin -uroot –p<code> -S/mysql/instance_name/data/instance_name.sock status

Enable bin log:

Add following parameter in cnf file and bounce mysqld

log_bin = /mysql/instance_name/data/binlogs/instance_name-bin
log_bin_index = /mysql/instance_name/data/binlogs/instance_name-bin.index
server_id = 3350
expire_logs_days = 7

mysql> show binary logs;

mysql> select variable_value as "BINARY LOGGING STATUS (log_bin) ::" from information_schema.global_variables where variable_name='log_bin';

Connect using mysql:

mysql -uroot –p<code> -S/mysql/instance_name/data/instance_name.sock
mysql -ubkupusr –p<code> -S/mysql/instance_name/data/instance_name.sock

Create application accounts:

CREATE USER 'user_name'@'%' IDENTIFIED BY '<code>';

mysql> GRANT ALL PRIVILEGES ON *.* TO '<user_name>'@'%' IDENTIFIED BY '<code>';

mysql> FLUSH PRIVILEGES;

Configure Master Slave replication using GTID:

Create User - repl on Master:

mysql> CREATE USER 'repl'@'%.<domain_name>.com' IDENTIFIED BY '<code>';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.<domain_name>.com';
mysql> FLUSH PRIVILEGES;

Add following parameter in .cnf file of Master and Slave:

########               Master-Slave config                ########

gtid-mode=on
enforce-gtid-consistency=true
relay-log-info-repository=TABLE
master-info-repository=TABLE
expire_logs_days = 7
relay_log_info_repository=TABLE
relay-log=/mysql/instance_name/data/binlogs/instance_name-relay
relay-log-index=/mysql/instance_name/data/binlogs/instance_name-relay.index

Bounce mysqld:

$sudo systemctl restart mysqld

Execute following on slave:

CHANGE MASTER TO
MASTER_HOST = 'host_name.<domain>.com',
MASTER_PORT = 3777,
MASTER_USER = 'repl',
MASTER_PASSWORD = '<code>',
MASTER_AUTO_POSITION = 1;

mysql> start slave;

On Master:

SHOW PROCESSLIST \G;
SHOW SLAVE HOSTS;

CREATE USER 'bkupusr'@'localhost' IDENTIFIED BY ‘<code>';

GRANT CREATE,INSERT,UPDATE,SELECT,DROP,RELOAD,ALTER,SUPER,PROCESS,LOCK TABLES,REFERENCES,CREATE ROUTINE,TRIGGER ON *.* TO 'bkupusr'@'localhost';

GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'bkupusr'@'localhost';

GRANT CREATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO 'bkupusr'@'localhost';

GRANT REPLICATION CLIENT ON *.* TO 'bkupusr'@'localhost';