Prerequisites and Package Acquisition

Before beginning the installation, ensure you have downloaded the MySQL 8.0 RPM bundle for Rocky Linux 9 from the official MySQL website. The file will typically be named something like mysql-8.0.xx.rpm-bundle.tar. Transfer this archive to your target Rocky Linux 9 server.

Extracting and Installing the RPM Packages

Once the archive is on the server, extract its contents and install the necesary RPM packages in the correct sequence. The installation process relies on the DNF package manager.

# Extract the downloaded archive
tar -xvf mysql-8.0.xx.rpm-bundle.tar

# Install MySQL server and its dependencies
dnf -y localinstall mysql-community-common-8.0.xx-1.el9.x86_64.rpm
dnf -y localinstall mysql-community-libs-8.0.xx-1.el9.x86_64.rpm
dnf -y localinstall mysql-community-client-8.0.xx-1.el9.x86_64.rpm
dnf -y localinstall mysql-community-icu-data-files-8.0.xx-1.el9.x86_64.rpm
dnf -y localinstall mysql-community-server-8.0.xx-1.el9.x86_64.rpm

Configuring MySQL Server

After installation, it's crucial to configure the MySQL server settings. The primary configuration file is /etc/my.cnf. Below is a sample configuration that includes essential settings for performance and character encoding.

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set = utf8mb4

[mysql]
prompt="MySQL [\d]> "
no-auto-rehash

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
default_authentication_plugin = mysql_native_password

datadir = /var/lib/mysql
pid-file = /var/lib/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1

init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci

skip-name-resolve
back_log = 300

max_connections = 2000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 1024
max_allowed_packet = 256M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 128M

read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 256M

thread_cache_size = 64

log_bin = mysql-bin
binlog_format = row
binlog_expire_logs_seconds = 604800

log_error = /var/lib/mysql/mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log

performance_schema = 0
explicit_defaults_for_timestamp

lower_case_table_names = 1

skip-external-locking

default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 512M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G

interactive_timeout = 28800
wait_timeout = 28800

binlog_rows_query_log_events = 1
log_bin_trust_function_creators = 1
log_timestamps = SYSTEM

sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
innodb_strict_mode = 0

[mysqldump]
quick
max_allowed_packet = 256M

Disabling Initial Password Generation

To allow the server to start without generating a random root password, you need to modify the initialization script. This step ensures the server starts with a empty root password, which you can then set manually.

# Modify the systemd pre-initialization script to use --initialize-insecure
sed -i 's/--initialize /--initialize-insecure /' /usr/bin/mysqld_pre_systemd

Starting the MySQL Service

Finally, enable and start the MySQL service to complete the installation. The server will now be running and ready for initial configuration.

# Enable and start the MySQL service
systemctl enable --now mysqld