This guide walks through the process of installing MySQL 8.0, configuring the database server, setting up initial security measures, and performing basic operations like creating and deleting databases and tables. This comprehensive setup is aimed at configuring a MySQL database server for development or production environments.
Install MySQL Server
dnf -y install mysql-server
Configure Character Set
/etc/my.cnf.d/charset.cnf to set the default character set to utf8mb4.[mysqld]
character-set-server = utf8mb4
[client]
default-character-set = utf8mb4
Enable and Start MySQL Service
systemctl enable --now mysqld
If Firewalld is active and remote access to MySQL Server is needed, allow the MySQL service through the firewall.
firewall-cmd --add-service=mysql --permanent
firewall-cmd --reload
Run the mysql_secure_installation script to secure your MySQL server, including setting up root passwords and removing insecure default settings.
mysql_secure_installation
Follow the on-screen prompts to configure your security options, such as setting the root password and removing anonymous users and test databases.
Connect to MySQL
mysql -u root -p
Show User List
SELECT user, host FROM mysql.user;
Show Database List
SHOW DATABASES;
Create a Test Database
CREATE DATABASE test_database;
Create a Test Table
CREATE TABLE test_database.test_table (id INT, name VARCHAR(50), address VARCHAR(50), PRIMARY KEY (id));
Insert Data into Test Table
INSERT INTO test_database.test_table(id, name, address) VALUES("001", "CentOS", "Hiroshima");
Select Data from Test Table
SELECT * FROM test_database.test_table;
Delete Test Database
DROP DATABASE test_database;
To completely reset MySQL data and reinitialize the database, follow these steps:
Stop MySQL Service
systemctl stop mysqld
Remove MySQL Data Directory
rm -rf /var/lib/mysql/*
Initialize MySQL
mysqld --initialize --user=mysql
Start MySQL Service
systemctl start mysqld
Retrieve Temporary Root Password
grep 'temporary password' /var/log/mysqld.log
Follow the mysql_secure_installation process again to secure your new MySQL installation.