Complete MySQL Installation Guide: Binary vs Source, Security Hardening & High‑Concurrency Tuning

First, the author explains that MariaDB is a fork of MySQL created by its original founder after Oracle’s acquisition, and that the two are highly compatible at the protocol level, but the focus of the guide remains on the official MySQL distribution.

MySQL Features and Advantages

The guide lists MySQL’s open‑source nature, strong InnoDB performance, mature ecosystem, scalability (replication, read/write splitting, group replication), and cross‑platform support, noting that MySQL 8.0 adds window functions, CTEs, JSON enhancements, invisible indexes, and histograms.

Binary Installation (Recommended)

Step‑by‑step commands are provided:

cd /usr/local/src
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.40-linux-glibc2.17-x86_64.tar.xz

After creating a dedicated mysql user and group, the tarball is extracted, a symbolic link ln -s mysql-8.0.40-linux-glibc2.17-x86_64 mysql is created for easy upgrades, and a data directory /data/mysql is prepared with proper ownership.

A minimal /etc/my.cnf is written via a heredoc, specifying basedir, datadir, socket, port, and error‑log location.

Database initialization is performed with:

bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

The temporary root password is extracted from the error log using grep 'temporary password' /data/mysql/mysql.err. MySQL is then started either via a Systemd script ( service mysqld start) or with mysqld_safe, and the PATH is updated to include /usr/local/mysql/bin.

Source Compilation (Deep Customization)

For scenarios requiring plugins or CPU‑specific optimizations, the guide lists required build dependencies for CentOS/RHEL and Ubuntu/Debian, and notes that MySQL 8.0.35+ needs CMake 3.x.

The Boost‑included source tarball is downloaded and extracted, then configured with CMake:

cmake . \
  -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
  -DMYSQL_DATADIR=/data/mysql \
  -DSYSCONFDIR=/etc \
  -DWITH_BOOST=boost/boost_1_77_0/ \
  -DWITH_INNOBASE_STORAGE_ENGINE=1 \
  -DWITH_MYISAM_STORAGE_ENGINE=1 \
  -DWITH_PARTITION_STORAGE_ENGINE=1 \
  -DWITH_SSL=system \
  -DWITH_ZLIB=system \
  -DDEFAULT_CHARSET=utf8mb4 \
  -DDEFAULT_COLLATION=utf8mb4_general_ci \
  -DENABLED_LOCAL_INFILE=1 \
  -DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
  -DMYSQL_TCP_PORT=3306
make -j $(nproc)
make install

After compilation, the same initialization, startup, and environment‑variable steps from the binary path are reused.

Post‑Installation Security Hardening

The guide stresses changing the autogenerated root password and running mysql_secure_installation, which prompts for enabling validate_password, removing anonymous users, disabling remote root login, dropping the test database, and reloading privileges.

It then shows how to create a dedicated business user with limited host access:

CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE USER 'myapp'@'192.168.1.%' IDENTIFIED BY 'StrongP@ss123';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp'@'192.168.1.%';
FLUSH PRIVILEGES;

Limiting the host to an internal subnet adds an extra security layer.

High‑Concurrency Production Tuning

Key InnoDB settings are presented, e.g., innodb_buffer_pool_size = 12G for a 16 GB server (60‑80 % of RAM) and innodb_buffer_pool_instances = 8. Buffer sizes for sorting, reading, and joining are also tuned.

Connection limits are raised ( max_connections = 2000) with appropriate timeouts and a thread cache ( thread_cache_size = 64), while warning about memory consumption per connection.

I/O and log parameters are adjusted, such as innodb_log_file_size = 2G, innodb_flush_log_at_trx_commit = 1, and enabling the slow‑query log ( slow_query_log = 1, long_query_time = 2).

A complete my.cnf example consolidates all recommended settings for a 16 GB, 2000‑connection server, covering paths, character set, InnoDB core, connection limits, buffers, and logging.

Finally, the guide reminds to restart MySQL and verify the new variables, e.g., mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';".

Summary

The article starts with the MySQL‑MariaDB relationship, walks through both binary and source installations, details essential security initialization, and finishes with a production‑grade high‑concurrency configuration, providing ready‑to‑copy commands throughout.