Comprehensive Guide to Installing, Configuring, and Managing MySQL on CentOS Linux

1. Installation Preparation

1.1 Prepare Linux System and Tools

Install and start two CentOS 7 virtual machines; note MAC address, hostname, IP address, and UUID.

Master VM cloning operations.

Install remote access tools such as Xshell and Xftp.

Be aware of MySQL usage differences between CentOS 6 and CentOS 7.

# 1. Firewall:
#   6 is iptables, 7 is firewalld
# 2. Service start command:
#   6 uses service, 7 uses systemctl

1.2 Check if MySQL Is Already Installed

If installed via RPM, check the RPM package list:

rpm -qa | grep -i mysql # -i ignores case

Check MySQL service status:

systemctl status mysqld.service

If old mysql‑libs packages exist, they will be listed.

1.3 Uninstall MySQL

1.3.1 Stop mysqld Service

systemctl stop mysqld # .service suffix optional

1.3.2 List Installed Packages

rpm -qa | grep -i mysql
yum list installed | grep mysql

1.3.3 Remove Packages

yum remove mysql-xxx mysql-xxx mysql-xxx mysqk-xxxx

Repeat rpm -qa | grep -i mysql until no MySQL packages remain.

1.3.4 Delete MySQL Files

find / -name mysql
rm -rf xxx

1.3.5 Delete my.cnf Configuration File

rm -rf /etc/my.cnf # similar to my.ini on Windows

2. MySQL Linux Installation

2.1 MySQL Four Main Versions

(Image showing MySQL version options)

2.2 Download Specific MySQL Version

2.2.1 Download URL

MySQL Community Edition download link.

2.2.2 Choose Appropriate Version

The course uses version 8.0.25 for installation. Choose the RPM Bundle for Red Hat Enterprise Linux; it contains all components.

2.2.3 Extract Installation Files from the Bundle

(Image showing extracted files)

2.3 Check MySQL Dependencies on CentOS 7

2.3.1 Ensure /tmp Has Sufficient Permissions

chmod -R 777 /tmp # grant permissions recursively

2.3.2 Verify Required Packages

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

If missing, install them from the CentOS installation media.

2.4 MySQL Installation Process on CentOS 7

2.4.1 Upload Extracted Files to /opt

Use Xftp or another file transfer tool.

2.4.2 Install Packages 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

If errors occur, remove conflicting mariadb‑libs:

yum remove mariadb-libs # then retry the installation

2.4.3 Verify MySQL Version

mysql --version
mysqld --version

2.4.4 Initialize the Service

mysqld --initialize --user=mysql

This creates a temporary root password marked as expired; retrieve it from /var/log/mysqld.log .

2.4.5 Start MySQL and Check Status

# Start
systemctl start mysqld.service
# Stop
systemctl stop mysqld.service
# Restart
systemctl restart mysqld.service
# Status
systemctl status mysqld.service

Use ps -ef | grep -i mysql to view the process.

2.4.6 Enable/Disable Auto‑Start

systemctl list-unit-files | grep mysqld.service # shows if enabled
systemctl enable mysqld.service   # enable auto‑start
systemctl disable mysqld.service  # disable auto‑start

3. MySQL Login

3.1 First Login

mysql -uroot -p # enter the temporary password

3.2 Change Root Password

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

MySQL 5.7+ enforces stronger password policies; use a complex password to succeed.

3.3 Configure Remote Access

3.3.1 Problem Description

SQLyog or Navicat may report error 1130 because remote connections are disabled.

3.3.2 Ensure Network Connectivity

ping

3.3.3 Open Port 3306 (or disable firewall – not recommended)

# CentOS 6
service iptables stop
# CentOS 7
systemctl stop firewalld.service
systemctl start firewalld.service
systemctl status firewalld.service
firewall-cmd --list-all
firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload

3.3.4 Modify the User Table

use mysql;
SELECT Host, User FROM user;

Change Host from localhost to % to allow any IP:

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

3.3.5 Test Remote Connection

For MySQL 5.7 the connection works directly. For MySQL 8 you may need to change the authentication plugin:

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'abc123';

4. MySQL 8 Password Strength Evaluation

4.1 Password Differences Across Versions

MySQL 5.7 accepts simple passwords; MySQL 8 rejects them with error 1819 unless the password policy is lowered.

4.2 Security Policy Before MySQL 8

Enable validate_password plugin via my.cnf or runtime command:

# In my.cnf
[mysqld]
plugin-load-add=validate_password.so
validate_password=FORCE_PLUS_PERMANENT

Or install at runtime:

INSTALL PLUGIN validate_password SONAME 'validate_password.so';

4.3 MySQL 8 Security Policy

MySQL 8.0 automatically installs the validate_password component. Show its variables:

SHOW VARIABLES LIKE 'validate_password%';

Adjust policy level:

SET GLOBAL validate_password_policy=LOW;   # 0
SET GLOBAL validate_password_policy=MEDIUM;# 1
SET GLOBAL validate_password_policy=STRONG; # 2

4.4 Uninstall Plugin/Component (Optional)

UNINSTALL PLUGIN validate_password;
UNINSTALL COMPONENT 'file://component_validate_password';

5. Character Set Operations

5.1 Change MySQL 5.7 Character Set

MySQL 5.7 defaults to latin1 ; newer versions default to utf8mb4 . To change the server default, edit /etc/my.cnf :

character_set_server=utf8

Restart MySQL:

systemctl restart mysqld

5.1.2 Modify Existing Databases and Tables

# Change database character set
ALTER DATABASE db_name CHARACTER SET 'utf8';
# Change table character set
ALTER TABLE table_name CONVERT TO CHARACTER SET 'utf8';

5.2 Character Set Levels

Four levels: server, database, table, column. Each can be set independently using character_set_* variables or CREATE/ALTER statements.

5.2.1 Server Level

[server]
character_set_server=gbk
collation_server=gbk_chinese_ci

5.2.2 Database Level

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

5.2.3 Table Level

CREATE TABLE tbl_name (col VARCHAR(20)) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE tbl_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

5.2.4 Column Level

CREATE TABLE tbl(col VARCHAR(20) CHARACTER SET gbk);
ALTER TABLE tbl MODIFY col VARCHAR(20) CHARACTER SET utf8;

5.2.5 Summary

If a lower level does not specify a character set, it inherits from the next higher level.

5.3 utf8 vs utf8mb4

utf8mb3 uses up to 3 bytes per character; utf8mb4 supports the full 4‑byte UTF‑8 range.

5.4 Character Set Flow During a Query

System variables character_set_client , character_set_connection , and character_set_results control how the server decodes incoming statements and encodes outgoing results. Example shows converting a Chinese character from client UTF‑8 to server GBK and back.

6. SQL Case Sensitivity

6.1 Windows vs Linux

Keywords and functions are case‑insensitive, but database, table, and column names are case‑sensitive on Linux (default lower_case_table_names=0 ) and case‑insensitive on Windows ( lower_case_table_names=1 ).

6.2 Setting Case Sensitivity on Linux

Add lower_case_table_names=1 to my.cnf under [mysqld] and restart MySQL. Existing objects must be renamed to lower case before the change.

6.3 SQL Writing Recommendations

Write keywords and functions in uppercase.

Use lowercase for database, table, and column names.

Terminate statements with a semicolon.

Adopt a consistent naming convention to avoid cross‑platform issues.

7. Reasonable sql_mode Settings

7.1 Loose vs Strict Mode

Loose mode truncates over‑length values silently; strict mode (default in MySQL 5.7+) raises errors for such violations, encouraging proper validation at the application level.

7.2 Viewing and Setting sql_mode

SELECT @session.sql_mode;
SELECT @global.sql_mode;
SHOW VARIABLES LIKE 'sql_mode';

Temporary change:

SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE,...';
SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE,...';

Permanent change: add to /etc/my.cnf under [mysqld] :

[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Restart MySQL for the permanent setting to take effect.