MySQL EE Installation

MySQL Enterprise Edition Installation

Databases

MySQL

MySQL Lifecycle

MySQL Install

As an alternative to the method documented below, it is possible to install MySQL Enterprise Edition from YUM or APT packages (either downloaded locally, to your own package server, or from an online repository).

Installing packages using YUM or APT packages has some disadvantages...

The install will go to the /usr/lib64/mysql directory.

The example below installs to a version specific location under /mysql. This means we can have multiple versions installed and use a symbolic link with a default name to point to the active installation. E.g. /mysql/current

The default databases will be created under /var/lib/mysql.

The example below installs all databases under /mysql/data (configured as a separate mount point). This keeps capacity management for MySQL separate from OS level capacity management.

In Note 1369139.1 Oracle make this statement...

"Due to the easier maintenance when using the RPM installations, Oracle recommends using these if possible, although considerations such as installing into a non-default location will tend to favor using the tar-ball."
Opinion: In an Enterprise environment where separate teams are responsible for OS and database and where an Enterprise Management solution is in place to raise automated alerts on conditions such as filesystems filling, I believe the tar-ball installation helps maintain clear lines of responsibility (e.g. if /mysql filesystem usage breaches a threshold then DBA team will be engaged, if /opt usage breaches a threshold then UNIX team will be engaged. Also, UNIX patching can be done on a separate schedule to MySQL patching, thus helping support teams more easily identify root cause of issues associated with the patches and allowing MySQL patches to be delayed whilst they are tested on other servers without impacting critical OS level patching). However, a good compromise is to create separate mount points for the subdirectories of /var/lib/mysql

The steps shown are based on a tarball installation for Oracle Enterprise Linux 7

Prerequistes

yum

Ensure there are no MariaDB or MySQL CE libraries already installed (some of these have shipped by default on some distributions)....

sudo su - root

yum list installed | grep -i maria

yum list installed | grep -i mysql

To remove, if necessary, use...

yum remove mariadb-libs.x86_64

yum remove mysql-community-*

yum remove mysql-release-el7.x86_64

yum clean all

IMPORTANT NOTE: Be careful if you have installed an application that will use MySQL before installing MySQL (e.g. Zabbix) as the commands above are likely to remove some components that the application is dependent on.

MySQL has a dependency on the libaio library...

rpm -q --last libaio

yum list installed | grep libaio

To install, if necessary, use...

yum install libaio

MySQL has a dependency on libtinfo.so.5...

(This tends not to be installed by default on OEL8)

ls /lib64/libtinfo.so.5

To install, if necessary, use...

yum install ncurses-compat-libs

Name Resolution

All MySQL Nodes in the cluster should be reachable via DNS or /etc/hosts

ping node

Firewall

Oracle Enterprise Linux ships with firewalld enabled

Linux Firewall

Network/firewall configuration should allow access to the MySQL server via port 22 and 3306 (assuming use of standard ports)

firewall-cmd --zone=public --list-ports

To configure firewalld appropriately...

firewall-cmd --zone=public --add-port=22/tcp

firewall-cmd --permanent --zone=public --add-port=22/tcp

firewall-cmd --zone=public --add-port=3306/tcp

firewall-cmd --zone=public --permanent --add-port=3306/tcp

firewall-cmd --zone=public --add-port=33060/tcp --permanent

firewall-cmd --zone=public --add-port=33061/tcp --permanent

firewall-cmd --reload

Filesystem Mount Points

UNIX Storage Management

/mysql - ~5GB

/mysql/data - (dependent on amount of data to be stored in database)

/mysql/log - 10GB minimum for test, 50GB minimum for live (dependent on amount of DML)

/mysql/backup - Approx 50% of database size per backup, assuming compression used

Check with...lsblkdf -h /mysql*tree -f /mysqlls -l mysql

Users & Groups

Linux Users & Groups

groupadd -g 27 -o -r mysql

useradd -M -N -g mysql -o -r -d /mysql/data -s /bin/false -c "MySQL Server" -u 27 mysql

chown -R mysql:mysql /mysql

chmod 750 /mysql/data

Check with...cat /etc/group | grep 27cat /etc/passwd | grep mysqlls -ld /mysql(750 equates to drwxr-xr-x)

Download

edelivery.oracle.com

support.oracle.com

Download Enterprise (or Standard) Edition TAR file from Oracle Software Delivery Cloud or My Oracle Support (Patches & Updates section).

V1006124-01.zip - MySQL Commercial Server 8.0.23 TAR for Oracle Linux / RHEL 7 x86(64bit), 1.1 GBV1008555-01.zip - MySQL Commercial Server 8.0.24 TAR for Oracle Linux / RHEL 7 x86(64bit), 812.7MBV1009256-01.zip - MySQL Commercial Server 8.0.25 TAR for Oracle Linux / RHEL 7 x86(64bit), 813.1MBV1009259-01.zip - MySQL Commercial Server 8.0.25 TAR for Generic Linux x86 (64bit), 934.3 MBV1010681-01.zip - MySQL Commercial Server 8.0.26 TAR for Generic Linix x86 (64bit), 955.4 MBV1017958-01.zip - MySQL Commercial Server 8.0.27 TAR for Oracle Linux / RHEL 7 x86(64bit), 878.9MBV1017960-01.zip - MySQL Commercial Server 8.0.27 TAR for Generic Linux x86 (64bit), 1.2GB

8.0.27MYZIP=V1017960-01.zipMYTAR=mysql-commercial-8.0.27-linux-glibc2.12-x86_64.tar.gz8.0.28MYZIP=V1019324-01.zipMYTAR=mysql-commercial-8.0.28-linux-glibc2.12-x86_64.tar.xz

Unpack

cd /mysql

tar xvf /mysql/${MYTAR}

ln -s $(echo ${MYTAR} | awk -F".tar" '{ print $1 }') current

export PATH=/mysql/current/bin:$PATH

Add this to the .bash_profile for the root user

Secure Location for INFILE/OUTFILE files

cd /mysql

mkdir mysql-files

chown mysql:mysql mysql-files

chmod 750 mysql-files

SELinux

Check current status using one/both of these commands...

sestatus

getenforce

If your server has SELinux enabled, you have two choices. Disable SELinux (not recommended, but might be right for a test system), or configure SELinux to allow our configuration...

Configure SELinux

semanage fcontext -a -t mysqld_db_t "/mysql(/.*)?"

restorecon -Rv /mysql

If you intend to change the port from 3306 you will also need to do something like this...

semanage port -a -t mysqld_port_t -p tcp 3307

Disable SELinux

Assuming SELinux is currently set to 'enforcing'...

setenforce 0

getenforce

sed -i 's/SELINUX=enforcing/SELINUX=permissive/' /etc/selinux/config

Initial Configuration

my.cnf

cd /mysql

touch my.cnf

chown root:root my.cnf

chmod 644 my.cnf

ln -s /mysql/my.cnf /etc/my.cnf

Add the following lines to /etc/my.cnf...

[mysqld]

# GENERAL #

# ------------------------------ #

basedir = /mysql/current

socket = /mysql/mysql.sock

pid-file = /mysql/mysql.pid

port = 3306

user = mysql

# Data Storage #

# ------------------------------ #

datadir = /mysql/data

default-storage-engine = InnoDB

# Secure INFILE/OUTFILE Location #

# ------------------------------ #

local_infile = OFF

secure_file_priv = /mysql/mysql-files

# BINARY LOGGING #

# ------------------------------ #

log-bin = /mysql/log/binlog

binlog_expire_logs_seconds = 2592000

sync-binlog = 1

binlog-format = ROW

binlog_rows_query_log_events = ON

# LOGGING #

# ------------------------------ #

log-error = /mysql/data/mysql-error.log

log-queries-not-using-indexes = 1

slow-query-log = 1

slow-query-log-file = /mysql/data/mysql-slow.log

# INNODB #

# ------------------------------ #

innodb-log-files-in-group = 2

innodb-log-file-size = 256M

innodb-file-per-table = 1

innodb-buffer-pool-size = 2560M

innodb-io-capacity = 2000

innodb-io-capacity-max = 4000

[client]

socket = /mysql/mysql.sock

MySQL Configuration

NOTES:

This my.cnf is suitable for MySQL 8.0 (other versions may differ)
binlog_expire_logs_seconds = 2592000 seconds = 30 days (this is the default)

MySQL Slow Query Log

It is recommended to set appropriate values for:

MySQL Redo

innodb_log_file_size

A value of 256M may be a suitable starting point before using the process on the linked page to calculate a suitable value yielding switches approximately every hour. The default is 48M, but the Oracle recommended default is 64M. The initial setting isn't as important as ensuring you revisit this as soon as you are able to measure the throughput of the database.

MySQL Memory

innodb_buffer_pool_size

For a system with 8G of physical RAM, a value of 6144M (6G) might be appropriate - but it is recommended to follow the link above to better understand your choice. Always revisit your setting as soon as you are able to measure the server memory usage under a production workload.

Initialize Data Directory

cd /mysql/current

bin/mysqld --defaults-file=/mysql/my.cnf --initialize

Configure systemd

This allows us to use systemd to manage the MySQL instance state.

UNIX Services

touch /etc/systemd/system/mysqld.service

chmod 644 /etc/systemd/system/mysqld.service

Add the following lines to /usr/lib/systemd/system/mysqld.service...

[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

# Have mysqld write its state to the systemd notify socket

Type=notify

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

TimeoutSec=0

# Start main service

ExecStart=/mysql/current/bin/mysqld --defaults-file=/mysql/my.cnf $MYSQLD_OPTS

# Use this to switch malloc implementation

EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit

LimitNOFILE = 10000

Restart=on-failure

RestartPreventExitStatus=1

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

Environment=MYSQLD_PARENT_PID=1

PrivateTmp=false

Enable the service...

systemctl enable mysqld.service

Test manually starting MySQL...

systemctl start mysqld

systemctl status mysqld

Reboot your server and check MySQL automatically starts...

reboot

systemctl status mysqld

Check the logfile...

journalctl -u mysqld

Reset Password

Find out the initial password...

grep 'temporary password' /mysql/data/mysql-error.log

Filename is based on value of log-error from /mysql/my.cnf

Use it to login...

mysql -u root -p

Enter the password when prompted

Change the password (be sure to store it somewhere securely)...

ALTER USER 'root'@'localhost' IDENTIFIED BY RANDOM PASSWORD;

passwordsgenerator.net

Password Manager Pro

KeePass

Initial Tests

mysqlshow -u root -p

Enter the password when prompted. You should see four default databases.

mysqladmin -u root -p version

Enter the password when prompted.

mysql -u root -p

show databases;

Configure logrotate

Paste following into /etc/logrotate.d/mysql...

logrotate

/mysql/data/*log {

# create 600 mysql mysql

notifempty

weekly

rotate 5

missingok

compress

postrotate

# just if mysqld is really running

if test -x /mysql/current/bin/mysqladmin && \

env HOME=/root/ /mysql/current/bin/mysqladmin ping &>/dev/null

then

env HOME=/root/ /mysql/current/bin/mysqladmin flush-logs

endscript

}

For all files ending with log in /mysql/data
(option) Create new files with 600 permissions
Don't rotate if log file is empty
Rotate weekly
Maximum of 5 versions of a file
If the logfile is missing, ignore and continue
Compress old versions with gzip
After Rotate is complete...
... if the mysqladmin file exists...
... and mysqladmin ping says db is up...
... flush-logs ...
End postrotate commands

NOTE: You need mysqladmin to be able to login locally without needing manual password entry in order for the ping and flush-logs commands to run correctly...

mysql_config_editor set --login-path=mysqladmin --host=localhost --user=root --password

MySQL Users