Installing MySQL Community Edition on Amazon Linux 2023 with Custom Data Directory

 

Setting up MySQL on Amazon Linux 2023 with a dedicated storage configuration can significantly improve your database performance and management capabilities. In this comprehensive guide, we'll walk through the process of installing MySQL Community Edition and configuring it to use a separate mounted filesystem for data storage.

Why Use a Custom Data Directory?

Before diving into the installation, let's understand why you might want to configure MySQL with a custom data directory:

• Performance: Dedicated storage can provide better I/O performance
• Scalability: Easier to resize storage independently from the root filesystem
• Backup Management: Simplified backup processes by isolating database files
• Security: Better separation between system and application data

System Requirements

For this tutorial, we're working with the following system configuration:

• Operating System: Amazon Linux 2023.8.20250818.0 x86_64
• Kernel: 6.1
• Storage Configuration:
  • /dev/xvda1 (40GB) - Root filesystem
  • /dev/xvdb (100GB) - Mounted at /data

Installation Process

Step 1: Verify Filesystem Configuration

Start by confirming that your additional storage is properly mounted and accessible:

df -h

You should see output similar to:

Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1       40G  2.1G   38G   6% /
/dev/xvdb       100G   24K   95G   1% /data

Step 2: Add the MySQL Repository

Amazon Linux 2023 doesn't include MySQL in its default repositories, so we need to add the official MySQL repository:

sudo dnf install -y https://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm

Step 3: Import the MySQL GPG Key

To ensure package integrity and avoid signature verification warnings:

sudo rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

Step 4: Install MySQL Community Server

Now install the MySQL Community Server package:

sudo dnf install -y --nogpgcheck mysql-community-server

Note: The --nogpgcheck flag is used as a fallback if there are any GPG verification issues. In production environments, ensure proper GPG verification.

Step 5: Prepare the Custom Data Directory

Create the MySQL data directory on your dedicated filesystem and set appropriate permissions:

sudo mkdir -p /data/mysql
sudo chown -R mysql:mysql /data/mysql
sudo chmod 750 /data/mysql

These commands:

• Create the directory structure
• Set ownership to the mysql user and group
• Set secure permissions (readable/writable by owner, readable by group, no access for others)

Step 6: Configure MySQL

Back up the original configuration file and create a new configuration:

sudo cp /etc/my.cnf /etc/my.cnf.backup

Create the new configuration file:

sudo bash -c 'cat > /etc/my.cnf << EOF
[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
socket=/var/lib/mysql/mysql.sock
EOF'

This configuration:

• Points MySQL to use /data/mysql as the data directory
• Maintains socket file in the default location for compatibility
• Specifies log and PID file locations

Step 7: Initialize the MySQL Database

Initialize MySQL with the new data directory:

sudo mysqld --initialize-insecure --user=mysql --datadir=/data/mysql

If SELinux is enabled on your system, set the proper security context:

sudo restorecon -R /data/mysql

Security Note: The --initialize-insecure flag creates a MySQL installation without a root password. This is acceptable for initial setup but should be secured immediately after installation.

Step 8: Start and Enable MySQL Service

Start the MySQL service and configure it to start automatically on boot:

sudo systemctl start mysqld
sudo systemctl enable mysqld
sudo systemctl status mysqld

The status command should show that MySQL is active and running.

Step 9: Secure Your MySQL Installation

Run the MySQL security script to improve your installation's security:

sudo mysql_secure_installation

The script will guide you through:

• Setting a root password
• Removing anonymous users
• Configuring remote root access
• Removing the test database
• Reloading privilege tables

Follow the prompts and choose options appropriate for your security requirements.

Step 10: Verify Configuration

Confirm that MySQL is using your custom data directory:

sudo mysql -u root -p -e "SHOW VARIABLES LIKE 'datadir';"

Expected output:

+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| datadir       | /data/mysql/ |
+---------------+--------------+

Testing Your Installation

To ensure everything is working correctly, try connecting to MySQL and running a few basic commands:

mysql -u root -p

Once connected, you can run:

SHOW DATABASES;
SELECT VERSION();
SHOW VARIABLES LIKE 'datadir';

Best Practices and Next Steps

Now that MySQL is installed and configured, consider implementing these additional practices:

Security Enhancements

• Create dedicated database users instead of using root
• Implement proper firewall rules
• Configure SSL/TLS encryption
• Regular security updates

Performance Optimization

• Tune MySQL configuration based on your workload
• Monitor disk I/O and memory usage
• Consider implementing MySQL performance schema

Backup Strategy

• Set up automated backups of your /data/mysql directory
• Test backup restoration procedures
• Consider point-in-time recovery capabilities

Monitoring

• Implement monitoring for database performance
• Set up alerts for disk space usage
• Monitor connection counts and query performance

Troubleshooting Common Issues

Permission Errors

If you encounter permission-related errors:

sudo chown -R mysql:mysql /data/mysql
sudo chmod -R 750 /data/mysql

SELinux Issues

If SELinux is causing problems:

sudo setsebool -P mysql_connect_any 1
sudo restorecon -R /data/mysql

Service Start Failures

Check the MySQL error log:

sudo tail -f /var/log/mysqld.log

You've successfully installed MySQL Community Edition on Amazon Linux 2023 with a custom data directory configuration. This setup provides a solid foundation for database operations with improved performance, easier management, and better separation of concerns.

The dedicated storage configuration will serve you well as your database grows, making it easier to scale storage, perform backups, and maintain optimal performance. Remember to implement proper security measures and backup strategies for production environments.

With this configuration in place, you're ready to start building applications that leverage MySQL's robust database capabilities while maintaining the flexibility and performance benefits of dedicated storage.