Introduction

Data is stored in various formats including text, images, and video, which computers interpret as binary or hexadecimal machine language. Depending on the importance and complexity of the information, different management strategies are required. Databases are best suited for storing critical data with complex relationships.

Database Management Systems (DBMS) are generally categorized into:

• RDBMS: Oracle, MySQL, PostgreSQL, MSSQL
• NoSQL: MongoDB, Redis, Elasticsearch
• NewSQL: TiDB, Spanner, OceanBase, PolarDB

For enterprise environments using MySQL GA (General Availability) releases, stable versions include:

• 5.6: 5.6.34, 5.6.36, 5.6.38, 5.6.40
• 5.7: 5.7.18, 5.7.20, 5.7.24, 5.7.26
• 8.0: 8.0.14, 8.0.15, 8.0.16

Binary Installation

1. Preparation and User Setup

Download the generic Linux binaries and extract them to a standard directory. It is crucial to remove existing MariaDB libraries to avoid conflicts.

mkdir -p /opt/packages
cd /opt/packages
tar -xzvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.26-linux-glibc2.12-x86_64 /usr/local/mysql

# Remove conflicting OS databases
rpm -qa | grep mariadb
yum remove mariadb-libs -y

# Create service user
useradd -r -s /sbin/nologin mysql

2. Environment Variables

Add the MySQL binary path to the system PATH variable.

echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile
source /etc/profile
mysql -V

3. Data Directory Configuration

Prepare a dedicated disk for data storage. Format the new volume and mount it.

# Format and mount
mkfs.xfs /dev/sdb
mkdir -p /data/mysql
blkid /dev/sdb
# Add UUID entry to /etc/fstab
# UUID=... /data xfs defaults 0 0
mount -a

chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /data

4. Database Initialization

Initialize the data directory. Using --initialize-insecure is recommended for lab environments to avoid dealing with temporary passwords and complexity policies immediately. Note that libaio is required.

yum install -y libaio-devel numactl

mkdir -p /data/mysql/data
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data

5. Configuration File

Create a standard my.cnf configuration file.

cat > /etc/my.cnf <<'EOF'
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=1
port=3306
log_error=/data/mysql/data/err.log

[client]
socket=/tmp/mysql.sock
EOF

Service Management

Systemd Configuration

Create a systemd service unit file for modern process management.

cat > /etc/systemd/system/mysqld.service <<'EOF'
[Unit]
Description=MySQL Community Server
After=network.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF

systemctl daemon-reload
systemctl start mysqld

Troubleshooting Startup Failures

If the service fails to start (e.g., "PID file not found"), check the error log located in the data directory (e.g., /data/mysql/data/hostname.err). Common causes include:

• Incorrect paths in /etc/my.cnf.
• Socket file location conflicts.
• Incorrect ownership of the data directory.
• Invalid configuration parameters.

Password Management

Set Root Password

For a fresh install initialized with --initialize-insecure, set the root password.

mysqladmin -uroot password 'NewPassword123!'

Resetting a Forgotten Root Password

If the root password is lost, restart the database in maintenance mode by skipping grant tables and networking.

systemctl stop mysqld

# Start in safe mode
mysqld_safe --skip-grant-tables --skip-networking &

# Connect and reset
mysql -uroot

# Within the MySQL prompt
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword123!';

# Restart normally
systemctl restart mysqld