MySQL on FlashArray Implementation and Best Practices
MySQL is an open source relational database management system (RDBMS) that can be acquired directly from Mysql.com, Oracle 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 layoutAll 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.
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 locationThe data files and database contents can be moved from the default location without data loss. This requires downtime.
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 movementMicrosoft 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 databaseMicrosoft 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 permissionsMicrosoft 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 locationMicrosoft 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 issuesGet 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 issuesEdit 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 MySQLMicrosoft 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 directoryIt 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.
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. 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 fileWindows 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 filesThis 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 issuesGet 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 issuesEdit 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 MySQLMicrosoft 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 filesWhen 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 movementEach 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>). 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 databaseMicrosoft 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 volumeMicrosoft 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 directoryMicrosoft 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. 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 issuesGet 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 issuesEdit 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 MySQLMicrosoft 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 volumeIn 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 |
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. |