Optimal MySQL 8 Installation on Linux Mint and Ubuntu Environments

MySQL is one of the most widely used open source relational database management systems. Its proven performance, reliability and ease of use has led to widespread adoption across web and business applications. This comprehensive guide draws on my expertise as a full stack developer to walk you through optimized installation and configuration of MySQL 8.x on Linux Mint 20 and Ubuntu 20.04 LTS.

Understanding MySQL Popularity

MySQL‘s popularity is driven by several key factors:

• Speed and Scalability: Benchmark tests demonstrate MySQL‘s high performance for both read and write operations, along with ability to handle heavy workloads and datasets. Results from the industry standard SysBench tool indicate MySQL can outperform databases like PostgreSQL and Mongo in certain workloads:

Database	Read Speed	Write Speed
MySQL	95783 rows/sec	966 rows/sec
PostgreSQL	61211 rows/sec	521 rows/sec
Mongo	37949 rows/sec	1381 rows/sec

• Ease of Use: MySQL‘s syntax follows common SQL standards, making it intuitive for developers familiar with other relational databases. Advanced GUI tools like MySQL Workbench simplify database administration.

• Reliability: Architecture optimized for high availability and uptime with ACID compliance for data accuracy and transaction support.

• Flexibility: Works across platforms like Windows, Linux, macOS both on-premise and cloud. Variety of storage engines to match application requirements.

• Cost: Open source MySQL is free to install and use without licensing fees. Paid enterprise edition adds advanced features.

These characteristics explain why MySQL remains a trusted choice to power web apps (WordPress, Facebook), business systems and data pipelines.

Prerequisites

Before installing MySQL, its best to update your Linux Mint 20 or Ubuntu 20.04 to pull in latest security patches and kernel improvements:

sudo apt update
sudo apt upgrade -y

Reboot to apply updated kernel:

sudo reboot

Check you have >= 4GB of RAM available for best MySQL performance:

free -h

Increase swap space if RAM is under 4 GB:

sudo fallocate -l 4G /swapfile
sudo chmod 600 /swapfile
sudo mkswap /swapfile  
sudo swapon /swapfile
echo ‘/swapfile none swap sw 0 0‘ | sudo tee -a /etc/fstab

Now system should be ready for optimal MySQL functionality.

Install MySQL Server

Adding MySQL APT repository allows installing latest MySQL version instead of outdated Ubuntu/Mint packages:

sudo apt install lsb-release ca-certificates apt-transport-https software-properties-common -y
wget https://repo.mysql.com/RPM-GPG-KEY-mysql-2022 -O gpg.asc
sudo apt-key add gpg.asc
echo "deb [arch=amd64] https://repo.mysql.com/repo/mysql-apt-config $(lsb_release -sc) mysql-apt-config" | sudo tee /etc/apt/sources.list.d/mysql.list  
sudo apt update

Install MySQL community server:

sudo apt install mysql-server -y  

MySQL will automatically start upon installation. Check status with:

sudo systemctl status mysql

By default, MySQL runs under the mysql system user account instead of root to reduce vulnerabilities.

Improve Startup Performance

MySQL stores its data files and logs under /var/lib/mysql. For faster database restarts, these files should be located on a high performance disk rather than slow rotational HDD.

First, copy existing MySQL data to SSD drive mounted at /ssd:

sudo rsync -avP /var/lib/mysql /ssd  

Rename original folder and link the new location:

sudo mv /var/lib/mysql /var/lib/mysql.bak 
sudo ln -s /ssd/mysql /var/lib/mysql

Modify ownership permissions since data is now under /ssd:

sudo chown -R mysql:mysql /ssd/mysql

These enhancements will significantly boost MySQL restart times and overall system response.

Secure MySQL Installation

The mysql_secure_installation script locks down MySQL by changing unsafe defaults. Always run it as a first step:

sudo mysql_secure_installation  

When prompted, set a strong root password, remove anonymous user accounts, disable remote root access and reload privileges. This establishes baseline security.

Next, protect MySQL further by adding these configuration tweaks in /etc/mysql/my.cnf:

[mysqld]  
skip_networking = true  
bind_address = 127.0.0.1 

# Other security enhancements
sql-mode = STRICT_ALL_TABLES
ft_min_word_len=4

key_buffer_size = 16M
max_allowed_packet = 16M 
thread_cache_size = 128
sort_buffer_size = 4M
bulk_insert_buffer_size = 16M
tmp_table_size = 32M
max_heap_table_size = 32M  

Restart MySQL to apply changes:

sudo systemctl restart mysql

This disables external connections for improved security, sets conservative SQL modes to prevent errors, and fine tunes cache/buffer sizes for faster queries based on server resource limits.

Create Dedicated MySQL User

The default root MySQL account has unlimited privileges which poses security issues if compromised. Hence, its best practice is to create less privileged users tied to specific applications or databases.

Connect on the MySQL command line as root:

sudo mysql 

Create a new user (replace the username and password):

CREATE USER ‘dbuser‘@‘localhost‘ IDENTIFIED BY ‘new_password_here‘;

Grant the user necessary privileges. This allows dbuser full access to the appdb database:

GRANT ALL PRIVILEGES ON appdb.* TO ‘dbuser‘@‘localhost‘;

Check the privileges were configured correctly:

SHOW GRANTS FOR ‘dbuser‘@‘localhost‘; 

This displays granted privileges for the user.

Finally reload privileges to refresh permissions and exit MySQL shell:

FLUSH PRIVILEGES;
exit

Now dbuser can be used in applications instead of high-privilege root account. Repeat the above process to create separate database users for each app.

Backup and Recovery Methods

Backups are vital to guard against data loss from hardware failures, accidents or attacks. MySQL includes several backup strategies:

Cold Backup: Shut down MySQL gracefully and copy datadir (/var/lib/mysql) contents to remote storage. Fast but gives inconsistent data if writes occur during transfer.

Hot Online Backup: Use mysqldump to export database files to SQL while MySQL is running:

mysqldump -uroot -p --all-databases > fulldatabase.sql

Can slow performance. Options like --single-transaction give better consistency.

Warm Online Backup: Tools like mysqlbackup perform backups by briefly putting tables in read-only mode during transfers for consistency:

mysqlbackup -uroot -p --backup-dir=/backups/latest backup 

Minimizes downtime and impact on performance.

Incremental: Back up changes since last full backup using mysqlbinlog:

mysqlbinlog mysql-bin.000005 > changes-since-last-fullbackup.sql 

Quickly copies only latest updates instead of entire dataset.

For recovery, the backed up files, logs or dumps are imported back to MySQL or restored on replacement servers. Testing restoration process regularly allows verifying recovery SLAs.

Infrastructure Recommendations

For reliable production use, MySQL should run on resilient infrastructure:

• Storage: Use RAID-based network attached block storage or SAN instead of local server disks for enhanced redundancy and speeds.

• Memory: Increase available RAM to accommodate buffered caches and indexes for faster queries.

• High Availability: Replication, clustering and failover across multiple servers prevents downtime if one system fails.

• Load Balancing: Share queries systematically across replicated database servers to distribute workload.

• Vertical Scaling: Beef up single server hardware (CPU, RAM, IOPS) for supporting growth before sharding data horizontally.

Adjust infrastructure tiers based on historical and projected traffic patterns. Monitor growth with tools like Grafana and scale up components proactively.

Conclusion

Installing MySQL on Linux Mint or Ubuntu grants access to one of the most battle-tested databases for serving web and enterprise applications at scale. Proper configuration optimizing for performance, high availability and security is key to smooth operations.

Follow backup best practices and test restores periodically on separate environments. Consider commercial MySQL offerings like Oracle MySQL HeatWave or AWS RDS to automate administration tasks as application use expands globally. Reach out in comments for any other tips for your MySQL deployment journey!