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

Note:

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

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.

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)

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

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

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

(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

(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

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

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

Note:

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.


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

Note:

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

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

(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

(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

(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

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

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

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).

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>).

Note:

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

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

(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>

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.

Note:

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

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

(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

(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

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

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

Linux


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