Skip to main content
Pure Technical Services

MySQL on FlashArray Implementation and Best Practices

Currently viewing public documentation. Please login to access the full scope of documentation.

MySQL is an open source relational database management system (RDBMS) that can be acquired directly from Mysql.comOracle Software Delivery Cloud, or from the MySQL github repository

MySQL offers a pluggable storage engine architecture that enables different storage engines to be loaded and unloaded from a running MySQL server. Each storage engine can suit the needs of a different use case. Documentation on the supported storage engines can be found in the MySQL Reference Manual: Alternative Storage Engines

Due to its open source nature, there are a number of MySQL forks and deployment types. Some deployment types and revisions are:

  • MySQL Community Edition - Freely downloadable and available under the GPL license. Includes multiple storage engines and is supported by a community of open source developers. 
  • MySQL Enterprise Edition - A commercial version of MySQL which Includes a comprehensive set of advanced features and management tools. 
  • MySQL Classic Edition -  A commercial version of MySQL with standard database capabilities. Includes the InnoDB and MyISAM storage engines. 
  • MySQL Cluster - A commercial version of MySQL as a distributed database providing scalability and high availability. Uses the NDB storage engine. 
  • MySQL InnoDB Cluster - A high availability solution for MySQL where data is replicated between MySQL server instances. 

Hardware Environment Requirements 

Block storage on FlashArray can be accessed using a number of protocols. These protocols and their requirements are listed as follows :

Protocol  Requirements
Fibre Channel Protocol (FCP) for SCSI A fiber channel fabric with an established zone between the initiator (MySQL deployment system) and host (FlashArray).
Internet Protocol for SCSI (iSCSI) A high speed (Min 10Gb/s speed capability) TCP/IP network with the initiator (MySQL deployment system) and target(FlashArray) having either routable or direct line-of-sight connectivity between assigned addresses. 
NVM Express over RoCEv2 Fabric (NVMe-oF/RoCEv2)

The transport for RoCEv2 uses theIPv4 user datagram protocol (UDP) which requires a lossless network. The converged Ethernet network switches are required to support industry standard congestion control mechanisms. 

The guides on how to configure the switches and topologies can be found at these locations :

The MySQL deployment system will require dedicated Host Bus Adapters (HBA) which support RoCEv2. 

More information on supported hardware and configurations for NVMe-oF/RoCEv2 can be found in the NVMe-oF Support Matrix.

NVM Express over Fibre Channel Fabric (FC-NVMe)

NVMe/FC uses standard Fibre Channel mechanisms for the transport of NVMe commands. 

More information on supported hardware and configurations for NVMe/FC can be found in the NVMe-oF Support Matrix.

NVM Express over Transmission Control Protocol (NVMe/TCP)

NVMe/TCP use standard TCP mechanisms for the transport of NVMe commands. 

More information on supported hardware and configurations for NVMe/TCP can be found in the NVMe-oF Support Matrix.

Recommended Configuration for MySQL on FlashArray 

Operating System Requirements 

MySQL can be deployed on a number of operating platforms. The current supported platforms can be found in the MySQL documentation

The relevant operating system and recommendations can be found below:

Operating System Recommendations 
Red Hat Enterprise Linux/ CentOS / Oracle Linux/Ubuntu/SUSE/Debian

The recommended settings for Linux operating systems can be found in the Linux best practices for FlashArray.

In addition to the existing best practices ensure that the number of requests per volume is set to a higher value. Increasing this value allows individual volumes to service more storage requests at any single time. 

Add the following to the udev rules file for FlashArray to increase the maximum number of requests for a single volume: 

Set DM devices number of requests to 1024 or higher. This uses 1MB of memory per request
ACTION=="add|change", KERNEL=="dm-[0-9]*", SUBSYSTEM=="block", ENV{DM_NAME}=="3624a937*", ATTR{queue/nr_requests}="1024" 
Microsoft Windows Server  The best practices for Microsoft Windows can be set using the Test-WindowsBestPractices Cmdlet

 

File System Configuration

The recommended file systems for MySQL with FlashArray are NTFS (Microsoft Windows) , XFS (Linux) and EXT4 (Linux).  

Microsoft Windows

If setting the InnoDB page size to 32K or 64K (default is 16K) , NTFS (for the innodb_data_home_dir) should be formatted with an allocation unit size which matches the page size. 

Page size Allocation Unit Size 
16k (default) 16K
32K 32K
64K 64K

Linux 

XFS 

When using the XFS file system for any MySQL database files the default options are typically all that is required. 

XFS file systems can be created on a FlashArray volume using the command:

mkfs.xfs /dev/mapper/<device>

The default mount options in /etc/fstab should be used with XFS in the majority of cases. Using noatime can provide additional performance in some scenarios. 

/dev/mapper/<device> /mountpoint xfs defaults 0 0

If using iSCSI connectivity to FlashArray ensure the _netdev,nofail options are present:

/dev/mapper/<device> /mountpoint xfs _netdev,nofail 0 0

For systems using the Linux Kernel 4.13 or later the "nobarrier" option is deprecated for XFS. To increase the possible performance of the volume add the following udev rule to the existing rules file:

FCP and iSCSI 

ACTION=="add|change", KERNEL=="sd*[!0-9]", SUBSYSTEM=="block", ENV{ID_VENDOR}=="PURE", ATTR{queue/write_cache}="write through"
ACTION=="add|change", KERNEL=="dm-[0-9]*", SUBSYSTEM=="block", ENV{DM_NAME}=="3624a937*", ATTR{queue/write_cache}="write through"

 NVMe-oF connectivity

ACTION=="add|change", KERNEL=="nvme*[!0-9]", SUBSYSTEM=="block", ATTR{queue/write_cache}="write through"
ACTION=="add|change", KERNEL=="dm-[0-9]*", SUBSYSTEM=="block", ENV{DM_NAME}=="eui.00723ec7b5b427*", ATTR{queue/write_cache}= "write through"

Once the rules have been added reload and apply them using the udevadm utility:

 udevadm control --reload-rules && udevadm trigger
EXT4 

The EXT4 file system should be created with the "-o discard" option to enable automating unmap. 

mkfs.ext4 -o discard /dev/mapper/<device>

With EXT4 the volume can be mounted using /etc/fstab with the default options but to increase the possible performance the "noatime" and "barrier=0" options should be used. 

/dev/mapper/<device> /mountpoint ext4 noatime,barrier=0 0 0

If using iSCSI connectivity to FlashArray ensure the _netdev,nofail options are present:

/dev/mapper/<device> /mountpoint ext4 _netdev,nofail,noatime,barrier=0 0 0

Adding an entry to /etc/fstab

Entries in /etc/fstab can be done using the device path or the UUID of the filesystem.

To get the UUID of a file system use the blkid command and match the device to the respective UUID.

[root@DB-01 ~]# blkid
/dev/sdb: PTUUID="91360acd-7332-47d9-9027-0300c7e3a081" PTTYPE="gpt"
/dev/sda: PTUUID="91360acd-7332-47d9-9027-0300c7e3a081" PTTYPE="gpt"
/dev/sdc: PTUUID="91360acd-7332-47d9-9027-0300c7e3a081" PTTYPE="gpt"
/dev/mapper/3624a93708488b6dac70f42a20001ec55: PTUUID="91360acd-7332-47d9-9027-0300c7e3a081" PTTYPE="gpt"
/dev/sdd: PTUUID="91360acd-7332-47d9-9027-0300c7e3a081" PTTYPE="gpt"
/dev/mapper/3624a93708488b6dac70f42a20001ec55p1: UUID="D759-B5A6" BLOCK_SIZE="512" TYPE="vfat" PARTLABEL="EFI System Partition" PARTUUID="8f317ff7-97f5-48ab-83d8-f54d647fb390"
/dev/mapper/3624a93708488b6dac70f42a20001ec55p2: UUID="97d13054-20c7-436e-b6ab-ef8b8f3ce46b" BLOCK_SIZE="512" TYPE="xfs" PARTUUID="2644a548-dde5-4ac8-8679-c100011a0d78"
/dev/mapper/3624a93708488b6dac70f42a20001ec55p3: UUID="GXujto-h3yl-V3hq-jFZs-3xd6-RhLB-0vFf8l" TYPE="LVM2_member" PARTUUID="380fbf39-eb6e-4bf0-ae25-f4abd6f02803"
/dev/mapper/rhel-root: UUID="5547b1a3-590a-4d54-9d0b-710714ca7e52" BLOCK_SIZE="512" TYPE="xfs"
/dev/mapper/rhel-swap: UUID="70470107-9368-4d32-82d8-7620f1cdc665" TYPE="swap"
/dev/mapper/rhel-home: UUID="0e08dae8-3d93-451c-a021-b3fe58f9b464" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme0n1: UUID="36202f29-95e8-49d2-a200-4057712b9236" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme0n2: UUID="489e7195-46b5-466a-8770-9aa7938c8afb" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme0n3: UUID="0f4da946-a86e-4fd3-bcd3-0d74d88ef83b" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme0n4: UUID="9a5e14cc-b226-4f7c-91a5-f8397a73b5bd" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme0n5: UUID="41e4a009-b98a-4d48-9d69-f45e717485e2" BLOCK_SIZE="512" TYPE="xfs"
/dev/mapper/eui.00668f1ab9b15f4b24a937c400011884: UUID="36202f29-95e8-49d2-a200-4057712b9236" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme1n1: UUID="36202f29-95e8-49d2-a200-4057712b9236" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme1n2: UUID="489e7195-46b5-466a-8770-9aa7938c8afb" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme1n3: UUID="0f4da946-a86e-4fd3-bcd3-0d74d88ef83b" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme1n4: UUID="9a5e14cc-b226-4f7c-91a5-f8397a73b5bd" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme1n5: UUID="41e4a009-b98a-4d48-9d69-f45e717485e2" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme2n1: UUID="36202f29-95e8-49d2-a200-4057712b9236" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme2n2: UUID="489e7195-46b5-466a-8770-9aa7938c8afb" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme2n3: UUID="0f4da946-a86e-4fd3-bcd3-0d74d88ef83b" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme2n4: UUID="9a5e14cc-b226-4f7c-91a5-f8397a73b5bd" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme2n5: UUID="41e4a009-b98a-4d48-9d69-f45e717485e2" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme3n1: UUID="36202f29-95e8-49d2-a200-4057712b9236" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme3n2: UUID="489e7195-46b5-466a-8770-9aa7938c8afb" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme3n3: UUID="0f4da946-a86e-4fd3-bcd3-0d74d88ef83b" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme3n4: UUID="9a5e14cc-b226-4f7c-91a5-f8397a73b5bd" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme3n5: UUID="41e4a009-b98a-4d48-9d69-f45e717485e2" BLOCK_SIZE="512" TYPE="xfs"
/dev/mapper/eui.00668f1ab9b15f4b24a937c400011890: UUID="489e7195-46b5-466a-8770-9aa7938c8afb" BLOCK_SIZE="512" TYPE="xfs"
/dev/mapper/eui.00668f1ab9b15f4b24a937c400011898: UUID="0f4da946-a86e-4fd3-bcd3-0d74d88ef83b" BLOCK_SIZE="512" TYPE="xfs"
/dev/mapper/eui.00668f1ab9b15f4b24a937c4000118a0: UUID="9a5e14cc-b226-4f7c-91a5-f8397a73b5bd" BLOCK_SIZE="512" TYPE="xfs"
/dev/mapper/eui.00668f1ab9b15f4b24a937c4000118a1: UUID="41e4a009-b98a-4d48-9d69-f45e717485e2" BLOCK_SIZE="512" TYPE="xfs"

 Then use the UUID in place of the device path (works for both ext4 and XFS):

UUID=489e7195-46b5-466a-8770-9aa7938c8afb /mountpoint ext4 _netdev,nofail,noatime,barrier=0 0 0

Volume and File System Architectural Layout 

There are a number of ways in which the file system for MySQL can be established. The primary recommendation is that all volumes for a MySQL instance, regardless of how many, should be exported from the same FlashArray. The following table highlights the different approaches to the file system layouts for InnoDB based MySQL installations. 

Default layout 

All of the database/schema files, data dictionary, undo files, bin logs, and transaction logs will be placed in the same directory at the default location for the operating system.

Microsoft Windows Linux 
MySQL_Default_Layout_Windows.jpg MySQL_Default_Layout_Linux.jpg

The recommendation for volumes from FlashArray is to mount a volume to the following locations with the correct permissions set before installing MySQL:

Microsoft Windows - C:\ProgramData\MySQL  (Permissions - NETWORK SERVICE - Full Control)

Linux - /var/lib/MySQL  (Permissions - user mysql , group mysql , drwxr-x--x)

No system variables have been set or changed with this layout. 

Migrating from default location to a different location 

The data files and database contents can be moved from the default location without data loss. This requires downtime. 

Microsoft Windows  Linux 
MySQL_ChangeDir_Layout_Windows.jpg MySQL_ChangeDir_Layout_Linux.jpg

The recommendation for a migration from the default location to a different one are to ensure the FlashArray volume has been formatted with a file system and mounted at the desired location. This example will assume that the new location is C:\MySQL for windows and /MySQL/data for Linux operating systems. 

1. Set the permissions prior to data movement 

Microsoft Windows - C:\MySQL  (Permissions - NETWORK SERVICE - Full Control). It is recommended NOT to place the database files directly on the root of the filesystem - for this example a new directory "data" will be created on the filesystem root and the permissions inherited from the parent. 

Linux - /MySQL/data  (Permissions - user mysql , group mysql , drwxr-x--x)

2. Stop the MySQL database

Microsoft Windows- In a PowerShell terminal type the following command (for those running MySQL 8 the service name is mysql80, for mysql 5.7 it is mysql57):

net stop mysql80

Linux - In a terminal type the following command:

sudo systemctl stop mysqld 
3. Copy the contents of the default directory to a new location , preserving existing permissions

Microsoft Windows - In a PowerShell terminal use the following command to copy from the default directory to the new one while preserving the permissions:

xcopy "C:\ProgramData\MySQL\MySQL Server 8.0\Data" C:\MySQL\data 
/O/X/E/H/K

Linux - In a command terminal use the following command to copy the contents of the default directory to the intended target destination:

sudo rsync -av /var/lib/mysql /MySQL
4. Modify the option file to point to the new database location 

Microsoft Windows - Modify the file "C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" and change the datadir entry to point towards the new location:

[mysqld]
datadir=C:\MySQL\data

Linux - Modify the  /etc/my.cnf configuration file and change the datadir entry to point towards the new location:

[mysqld]
datadir=/MySQL
5a. (SELinux enabled systems only)  - Change SELinux configuration to avoid service startup issues

Get the SElinux labeling for the default data directory with the following command:

ls -lh -Zd /var/lib/mysql
drwxr-xr-x. mysql mysql system_u:object_r:mysqld_db_t:s0 
/var/lib/mysql

Once you have the label change it with the following command:

chcon -R -u system_u -r object_r -t mysqld_db_t /MySQL
5b. (AppArmor enabled systems only) - Change AppArmor configuration to avoid service startup issues 

Edit the AppArmour configuration at the following location:

sudo vim /etc/apparmor.d/usr.sbin.mysqld

Change/copy the lines granting access to /var/lib/mysql to the new location 

reload AppArmor with  the following command:

service apparmor reload
6. Start MySQL

Microsoft Windows - Run the following command in a PowerShell terminal (for those running MySQL 8 the service name is mysql80, for mysql 5.7 it is mysql57):

net start MySQL80

Linux - Run the following command in a terminal:

systemctl start mysqld

  

Separate data, binary, and transaction log directory

It is possible to separate MySQL data directories based on what content should be located in each. This can be useful in some scenarios such as a replication use case recovering from binary logs on a remote system or applying quotas/quality of service restrictions on a per volume basis. 

Microsoft Windows Linux 
MySQL_Seperate_Layout_Windows.jpg MySQL_Seperate_Layout_Linux.jpg

For each of the segregated directories required a corresponding volume on FlashArray should be present and connected to the MySQL host. 

In this example the host is named DB-01 and 5 volumes have been created for the MySQL instance. MySQL should be stopped during this process

pureuser@flasharray> purevol list
Name                    Size   Source  Created                  Serial
DB-01-MySQL-Base        2T     -       2021-02-19 00:53:54 PST  668F1AB9B15F4BC400011884
DB-01-MySQL-BinLog      1248G  -       2021-02-22 07:35:40 PST  668F1AB9B15F4BC4000118A0
DB-01-MySQL-Log         1T     -       2021-02-19 00:54:45 PST  668F1AB9B15F4BC400011890
DB-01-MySQL-Undo        512G   -       2021-02-19 00:55:18 PST  668F1AB9B15F4BC400011898

Each volume has been connected to the initiator, formatted with the relevant file system, and mounted at the relevant directory: 

Microsoft Windows 

In each mount a folder is created "data" which the "NETWORK SERVICE" user is assigned full control over. This user can be changed but must be the same user responsible for starting th MySQLXX service. 

clipboard_ee5162a66fb4da314a3b6ff66e2df72be.png

PS C:\Users\DBTest> sl C:\MySQL\
PS C:\MySQL> dir


    Directory: C:\MySQL


Mode                LastWriteTime         Length Name
----                -------------         ------ ----
d----l        2/22/2021   7:47 AM                Base
d----l        2/22/2021   7:49 AM                BinLog
d----l        2/22/2021   7:49 AM                Log
d----l        2/22/2021   7:47 AM                Undo

Linux 

Each mounted volume needs to be owned by the mysql user and group. 

[root@DB-01 ~]# chown mysql:mysql /mysql/*
[root@DB-01 ~]# ll /mysql/
total 0
drwxr-xr-x 3 mysql mysql 18 Feb 19 01:30 base
drwxr-xr-x 2 mysql mysql  6 Feb 22 07:38 binlog
drwxr-xr-x 2 mysql mysql  6 Feb 22 07:34 log
drwxr-xr-x 2 mysql mysql  6 Feb 19 01:10 undo
[root@DB-01 ~]# df -h
Filesystem                                       Size  Used Avail Use% Mounted on
devtmpfs                                         252G     0  252G   0% /dev
tmpfs                                            252G     0  252G   0% /dev/shm
tmpfs                                            252G  131M  252G   1% /run
tmpfs                                            252G     0  252G   0% /sys/fs/cgroup
/dev/mapper/rhel-root                            410G  5.5G  405G   2% /
/dev/dm-5                                       1014M  256M  759M  26% /boot
/dev/mapper/rhel-home                             37G  293M   37G   1% /home
/dev/mapper/3624a93708488b6dac70f42a20001ec55p1  599M  6.9M  592M   2% /boot/efi
tmpfs                                             51G     0   51G   0% /run/user/0
/dev/mapper/3624a9370668f1ab9b15f4bc400011898    512G  3.6G  509G   1% /mysql/undo
/dev/mapper/3624a9370668f1ab9b15f4bc400011884    2.0T   15G  2.0T   1% /mysql/base
/dev/mapper/3624a9370668f1ab9b15f4bc400011890    1.0T  7.2G 1017G   1% /mysql/log
/dev/mapper/3624a9370668f1ab9b15f4bc4000118a0    1.3T  8.8G  1.3T   1% /mysql/binlog
1. Add the directory locations to the options file

Windows 

In windows the options file is located at: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini.

Place the following variables and entries into the file with any other required customizations: 

[mysqld]
# Data ,undo and log management
basedir = C:/MySQL/Base
datadir = C:/MySQL/Base/Data
innodb_data_home_dir = C:/MySQL/Base/Data
innodb_data_file_path  = ibdata1:12M:autoextend
innodb_log_group_home_dir = C:/MySQL/Log/Data
innodb_undo_directory = C:/MySQL/Undo/Data
log-bin = C:/MySQL/BinLog/Data

Linux 

In Linux the options file is located at: /etc/my.cnf.

Place the following variables and entries into the file with any other required customizations: 

[mysqld]
# Data ,undo and log management
basedir = /mysql/base
datadir = /mysql/base/data
innodb_data_home_dir = /mysql/base/data
innodb_data_file_path  = ibdata1:12M:autoextend
innodb_log_group_home_dir = /mysql/log
innodb_undo_directory = /mysql/undo
log-bin = /mysql/binlog/binlog
2. (Optional) Initialize the Data Directory and associated data files

This step is only optional if the data files have not been copied from the default location. See the section on Migrating from default location to a different location for how to copy the existing data files. 

Initializing the Data Directory creates a new MySQL instance with new data files and/or formats. This process can be used to change the default page size if it is required. A new defaults file at a different location or the standard defaults file can be used. Additional information on how to initialize the Data Directory can be found in the MySQL documentation

Windows

In a PowerShell terminal execute the following to initialize the Data Directory:

C:\Program Files\MySQL\MySQL Server 8.0\bin> .\mysqld.exe --defaults-file='C:\ProgramData\MySQL\MySQL Server 8.0\my.ini' --initialize 
--console

If the Data Directory initializes successfully the output will offer a temporary password:

2021-02-22T16:50:38.962139Z 0 [System] [MY-013169] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe 
(mysqld 8.0.22-commercial) initializing of server in progress as process 6608
2021-02-22T16:50:39.023330Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-02-22T16:50:44.079380Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-02-22T16:50:46.799503Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: GopNqqPte9=M 

Linux

In a terminal execute the following command as root to initialize the Data Directory: 

mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql

Once the Data Directory has initialized successfully the output will offer a temporary password:

2021-02-22T16:22:17.187727Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.23) initializing of server in progress as
process 612803
2021-02-22T16:22:17.187749Z 0 [ERROR] [MY-010338] [Server] Can't find error-message file '/mysql/base/share/mysql-8.0/errmsg.sys'. 
Check error-message file location and 'lc-messages-dir' configuration directive.
2021-02-22T16:22:17.195778Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-02-22T16:22:18.757767Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-02-22T16:22:19.553265Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 4pOt_Alia-eq
 3a. (SELinux enabled systems only)  - Change SELinux configuration to avoid service startup issues

Get the SElinux labeling for the default data directory with the following command:

ls -lh -Zd /var/lib/mysql
drwxr-xr-x. mysql mysql system_u:object_r:mysqld_db_t:s0 /var/lib/mysql

Once you have the label change it with the following command:

chcon -R -u system_u -r object_r -t mysqld_db_t /MySQL
3b. (AppArmor enabled systems only) - Change AppArmor configuration to avoid service startup issues 

Edit the AppArmour configuration at the following location:

sudo vim /etc/apparmor.d/usr.sbin.mysqld

Change/copy the lines granting access to /var/lib/mysql to the new location.

reload AppArmor with  the following command:

service apparmor reload
4. Start MySQL

Microsoft Windows - Run the following command in a PowerShell terminal (for those running MySQL 8 the service name is mysql80, for mysql 5.7 it is mysql57):

net start MySQL80

Linux - Run the following command in a terminal:

systemctl start mysqld
5. Harden the MySQL deployment  (Only required if initializing a new data directory)

In order to access and customize the MySQL deployment, the initial setup process needs to be performed. To do this the mysql_secure_installation utility will be used. 

Microsoft Windows - The mysql_secure_installation executable is typically located in the bin folder of the installation directory for MySQL - the default location is "C:\Program Files\MySQL\MySQL Server 8.0\bin". The temporary password retrieved at the end of step 2 needs to be used to as the root password. 

Linux - The msql_secure_installation is accessible from the command line by invoking "mysql_secure_installation". 

Securing the MySQL server deployment.

Enter password for user root: ************

The existing password for the user account root has expired. Please set a new password.

New password: *********

Re-enter new password: *********

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: n
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

At this point the MySQL deployment is ready to use for database operations. 

Separating database/schema files 

When a database or schema is created the corresponding data files are placed in a folder in the data directory. This layout is suitable for the majority of implementations but can be enhanced to allow for applying quality of service limits or per database capacity/quota management. 

This example is made up of four pre-existing databases/schemas each with the respective names warehouse01,warehouse02,warehouse03 and warehouse04. The files for each database/schema will be relocated to separate volumes and a symbolic link created to the data directory. 

Four  volumes have been created on FlashArray and connected to the MySQL host - each corresponding to a database/schema:

pureuser@flasharray> purevol list
Name                    Size   Source  Created                  Serial
DB-01-MySQL-Base        2T     -       2021-02-19 00:53:54 PST  668F1AB9B15F4BC400011884
DB-01-MySQL-BinLog      1248G  -       2021-02-22 07:35:40 PST  668F1AB9B15F4BC4000118A0
DB-01-MySQL-LCMessages  128G   -       2021-02-22 07:36:07 PST  668F1AB9B15F4BC4000118A1
DB-01-MySQL-Log         1T     -       2021-02-19 00:54:45 PST  668F1AB9B15F4BC400011890
DB-01-MySQL-Undo        512G   -       2021-02-19 00:55:18 PST  668F1AB9B15F4BC400011898
Warehouse01             768G   -       2021-02-23 06:20:17 PST  668F1AB9B15F4BC4000118A4
Warehouse02             768G   -       2021-02-23 06:20:17 PST  668F1AB9B15F4BC4000118A5
Warehouse03             768G   -       2021-02-23 06:20:17 PST  668F1AB9B15F4BC4000118A3
Warehouse04             768G   -       2021-02-23 06:20:17 PST  668F1AB9B15F4BC4000118A2
1. Set the permissions prior to data movement 

Each volume needs to be formatted and mounted into a directory. 

Microsoft Windows - The location of the mounted database volumes will be C:\MySQL\<DatabaseName>. A folder named "Data" is created in the root directory of each volume and the correct permissions assigned (NETWORK SERVICE with full control or the appropriate user which starts the mysql windows service).clipboard_eadc661c7a7ac43a3eebdedc55e66ff2c.png

Linux - The location of the mounted volumes will be /mysq/<DatabaseName>. The correct permissions need to be assigned to each directory (chown mysql:mysq <directory>).

MySQL naming for both databases are directories are case sensitive. Ensure that the directory names directly match the database names. 

[root@DB-01]# df -h
Filesystem                                      Size  Used Avail Use% Mounted on
devtmpfs                                        252G     0  252G   0% /dev
tmpfs                                           252G     0  252G   0% /dev/shm
tmpfs                                           252G   11M  252G   1% /run
tmpfs                                           252G     0  252G   0% /sys/fs/cgroup
/dev/mapper/rhel-root                           410G   38G  373G  10% /
/dev/mapper/rhel-home                            37G  293M   37G   1% /home
/dev/dm-5                                      1014M  256M  759M  26% /boot
/dev/mapper/3624a93708488b6dac70f42a20001edce1  599M  6.9M  592M   2% /boot/efi
tmpfs                                            51G     0   51G   0% /run/user/0
/dev/mapper/3624a9370668f1ab9b15f4bc400011886   2.0T   15G  2.0T   1% /mysql/base
/dev/mapper/3624a9370668f1ab9b15f4bc40001189e   512G  3.7G  509G   1% /mysql/undo
/dev/mapper/3624a9370668f1ab9b15f4bc400011894   1.0T   40G  985G   4% /mysql/log
/dev/mapper/3624a9370668f1ab9b15f4bc4000118a2   768G  5.4G  763G   1% /mysql/warehouse01
/dev/mapper/3624a9370668f1ab9b15f4bc4000118a3   768G  5.4G  763G   1% /mysql/warehouse02
/dev/mapper/3624a9370668f1ab9b15f4bc4000118a4   768G  5.4G  763G   1% /mysql/warehouse03
/dev/mapper/3624a9370668f1ab9b15f4bc4000118a5   768G  5.4G  763G   1% /mysql/warehouse04
2. Stop the MySQL database

Microsoft Windows- In a PowerShell terminal type the following command (for those running MySQL 8 the service name is mysql80 , for mysql 5.7 it is mysql57):

net stop mysql80

Linux - In a terminal type the following command:

sudo systemctl stop mysqld 
3. (Pre-existing database objects) Move the contents of the database directories to the new volume

Microsoft Windows - In a PowerShell terminal use the following command to copy from the default directory to the new one while preserving the permissions:

xcopy "C:\MySQL\Base\Data\<DatabaseName>" C:\MySQL\<DatabaseName>\Data\ 
/O/X/E/H/K

Microsoft Windows- In a command terminal use the following command to copy the contents of the default directory to a new one:

sudo rsync -av /mysql/base/data/<databasename> /mysql/<databasename>
 4. Create a symbolic link from the base data directory to the new volume directory

Microsoft Windows 

In Microsoft Windows the mklink utility in command prompt will be used to create the symbolic link for each database volume. The following syntax will be used "mklink Link Target":

C:\Users\DBTest>mklink C:\MySQL\Base\Data\<database> C:\MySQL\<database volume>\Data
symbolic link created for C:\MySQL\Base\Data\<database> <<===>> C:\MySQL\<database volume>\Data

Once the symbolic links have been created there will be .symbolic files in the data directory. 

The directory the symbolic link points to must have the correct permissions assigned. 

clipboard_ea9b72d63d8d98318abbd6b8c89e925e8.png

Linux  - creating symbolic links will follow the syntax "ln -s <new volume directory> <base data directory>.

ln -s /mysql/<database> /mysql/base/data/ 

Once the symbolic links have been created they will show as links to the database volumes when looking at the directory listing.  

[root@DB-06 base]# ll /mysql/base/data/
total 36916
-rw-r-----. 1 mysql mysql       56 Feb 23 06:10  auto.cnf
-rw-------. 1 mysql mysql     1676 Feb 23 06:10  ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Feb 23 06:10  ca.pem
-rw-r--r--. 1 mysql mysql     1112 Feb 23 06:10  client-cert.pem
-rw-------. 1 mysql mysql     1680 Feb 23 06:10  client-key.pem
-rw-r-----. 1 mysql mysql     4020 Feb 23 07:03  ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Feb 23 07:03  ibdata1
drwxr-x---. 2 mysql mysql        6 Feb 23 07:03 '#innodb_temp'
drwxr-x---. 2 mysql mysql      143 Feb 23 06:10  mysql
-rw-r-----. 1 mysql mysql 25165824 Feb 23 07:00  mysql.ibd
drwxr-x---. 2 mysql mysql     8192 Feb 23 06:10  performance_schema
-rw-------. 1 mysql mysql     1676 Feb 23 06:10  private_key.pem
-rw-r--r--. 1 mysql mysql      452 Feb 23 06:10  public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Feb 23 06:10  server-cert.pem
-rw-------. 1 mysql mysql     1680 Feb 23 06:10  server-key.pem
drwxr-x---. 2 mysql mysql       28 Feb 23 06:10  sys
lrwxrwxrwx. 1 root  root        18 Feb 23 07:03  warehouse01 -> /mysql/warehouse01
lrwxrwxrwx. 1 root  root        18 Feb 23 07:04  warehouse02 -> /mysql/warehouse02
lrwxrwxrwx. 1 root  root        18 Feb 23 07:04  warehouse03 -> /mysql/warehouse03
lrwxrwxrwx. 1 root  root        18 Feb 23 07:04  warehouse04 -> /mysql/warehouse04
5a. (SELinux enabled systems only)  - Change SELinux configuration to avoid service startup issues

Get the SElinux labeling for the default data directory with the following command:

ls -lh -Zd /var/lib/mysql
drwxr-xr-x. mysql mysql system_u:object_r:mysqld_db_t:s0
/var/lib/mysql

Once you have the label change it with the following command:

chcon -R -u system_u -r object_r -t mysqld_db_t /MySQL
5b. (AppArmor enabled systems only) - Change AppArmor configuration to avoid service startup issues 

Edit the AppArmour configuration at the following location:

sudo vim /etc/apparmor.d/usr.sbin.mysqld

Change/copy the lines granting access to /var/lib/mysql to the new location 

reload AppArmor with  the following command:

service apparmor reload
6. Start MySQL

Microsoft Windows - Run the following command in a PowerShell terminal (for those running MySQL 8 the service name is mysql80 , for mysql 5.7 it is mysql57):

net start MySQL80

Linux - Run the following command in a terminal:

systemctl start mysqld
7. Create a database object and check the database volume 

In any one of the MySQL tools create a table and then check the corresponding data volume for the files created:

use <DatabaseName>
create table testtable(int numberone);

The table will have a corresponding *.ibd file created in the database volume. 

Microsoft Windows 

clipboard_e5b7d9efb32bcc344050b7cdcaeb32306.png

Linux

[root@DB-01]# ll /mysql/warehouse01/
total 80
-rw-r-----. 1 mysql mysql 114688 Feb 23 07:53 testtable.ibd

Server System Variables 

Server System variables are global values which can effect the behavior of various components. Server System variables are different to the variables used for storage engine configuration as they either do not affect or apply across multiple storage engines (where applicable). 

The following Server System variables can impact storage configuration :

Parameter Name Description Default Value Recommended Value 
basedir The path to the MySQL installation base directory. 

Microsoft Windows - C:\ProgramData\MySQL\MySQL Server<version>\

Linux - /var/lib/

Only change this if using a separate layout for data, binary and transaction log directories.
datadir The path to the MySQL server data directory. 

Microsoft Windows - C:\ProgramData\MySQL\MySQL Server<version>\Data

Linux - /var/lib/mysql

Only change this if using a separate layout for data, binary and transaction log directories.
table_open_cache The number of open tables for all threads.   2000 If the value of opened tables is too large (see Opened_tables status variable) then increase this value. 
table_open_cache_instances The number of open tables cache instances.  16 To improve scalability by reducing contention among sessions the open tables cache can be partitioned into several smaller cache instances. Modify this value in conjunction with table_open_cache to tune the workload to achieve higher performance. 
open_files_limit The number of file descriptors available to MySQL from the operating system  5000

Depending on required number of open descriptors , for example a single MyIASM table could have 3 open descriptors. 

Will require system limits (ulimit -n) to be set for Linux Operating Systems. 

Ensure that innodb_open_files is not greater than this value.