MySQL on FlashArray Implementation and Best Practices

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

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. 

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

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

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

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 

Linux

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