Pre-installation Checks

Before installing MySQL on Linux systems, verify that you have two CentOS 7 virtual machines properly configured with unique MAC addresses, hostnames, IP addresses, and UUIDs. Ensure you have access tools like Xshell and Xftp. Note the differences between CentOS 6 and 7: CentOS 6 uses iptables firewall while CentOS 7 uses firewalld, and service management differs between the versions.

Checking Existing MySQL Installation

To check if MySQL is already installed using RPM packages:

rpm -qa | grep -i mysql

Check MySQL service status:

systemctl status mysqld.service

Uninstalling MySQL

To completely remove MySQL:

1. Stop MySQL service: systemctl stop mysqld.service
2. Check installed packages: rpm -qa | grep -i mysql or yum list installed | grep mysql
3. Remove packages: yum remove mysql-package-names
4. Delete residual files: find / -name mysql | xargs rm -rf
5. Remove configuration: rm -rf /etc/my.cnf

MySQL Installation on Linux

MySQL offers several editions including Community Server (free), Enterprise Edition (paid), Cluster, and Cluster CGE. For this guide, we'll use MySQL Community Server 8.0.25.

Downloading MySQL

Download the appropriate version from MySQL official website. For Linux systems, download the RPM Bundle package containing all necessary components.

Checking Dependencies

Ensure proper permissions for /tmp directory:

chmod -R 777 /tmp

Check required dependencies:

rpm -qa|grep libaio
rpm -qa|grep net-tools

Installation Process

Copy installation files to /opt and install in order:

rpm -ivh mysql-community-common-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.25-1.el7.x86_64.rpm

Verification and Initialization

Check MySQL version:

mysql --version

Initialize MySQL:

mysqld --initialize --user=mysql

View initial root password:

cat /var/log/mysqld.log

Service Management

Start MySQL service:

systemctl start mysqld.service

Check service status:

systemctl status mysqld.service

Enable auto-start:

systemctl enable mysqld.service

MySQL Login and Configuration

Login with initial password:

mysql -hlocalhost -P3306 -uroot -p

Change password after login:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_secure_password';

Remote Access Configuration

Configure firewall for MySQL port:

firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload

Enable remote root access:

USE mysql;
UPDATE user SET host = '%' WHERE user = 'root';
FLUSH PRIVILEGES;

Character Set Configuration

Check current character set settings:

SHOW VARIABLES LIKE 'character%';

Modify /etc/my.cnf to set default character set:

[mysqld]
character_set_server=utf8mb4

Restart MySQL after configuration changes:

systemctl restart mysqld

MySQL Directory Structure

MySQL data directory location:

SHOW VARIABLES LIKE 'datadir';

MySQL stores data in /var/lib/mysql/ by default. Table structures are stored in .frm files (MySQL 5.7) or incorporated into .ibd files (MySQL 8.0). InnoDB uses .ibd files for table data and indexes, while MyISAM uses .MYD for data and .MYI for indexes.

User and Privilege Management

Create new user:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Grant privileges:

GRANT privilege_type ON database.table TO 'username'@'host';

View user privileges:

SHOW GRANTS FOR 'username'@'host';

MySQL Architecture Overview

MySQL follows a layered architecture:

1. Connection Layer: Hand client connections and authentication
2. Service Layer: Includes SQL interface, parser, optimizer, and cache components
3. Storage Engine Layer: Plugin-based storage engines (InnoDB, MyISAM, etc.)
4. File System Layer: Physical data storage on disk

Storage Engines

MySQL supports multiple storage engines. Key engines include:

• InnoDB: Transaction-safe with foreign key support, row-level locking
• MyISAM: Non-transactional, table-level locking, full-text indexing
• Memory: Data stored in memory for fast access
• CSV: Stores data in comma-separated values format

View available engines:

SHOW ENGINES;

Set default storage engine:

SET DEFAULT_STORAGE_ENGINE=InnoDB;

Specify storage engine at table creation:

CREATE TABLE table_name (...) ENGINE = InnoDB;