How to Install MySQL Server on RedHat/CentOS 8 and Create Database, MySQL User, and Table

MySQL is one of the most popular open-source relational database management systems used for storing and managing data. If you're running RedHat or CentOS 8 and need to set up MySQL for your application or project, this guide will walk you through the installation process, creating a database, a MySQL user, and a table.

Step 1: Update the System

Before you start installing MySQL, it's a good practice to ensure your system is up to date. Open a terminal and run the following commands:

sudo dnf update

This will update the package list and upgrade existing packages on your CentOS 8 system.

Step 2: Install MySQL Server

CentOS 8's default package manager is `dnf`. You can easily install MySQL Server using the following command:

sudo dnf install mysql-server

This command will download and install MySQL Server and its dependencies. Once the installation is complete, start the MySQL service and enable it to start on boot:

sudo systemctl start mysqld

sudo systemctl enable mysqld

Step 3: Secure MySQL Installation

For security purposes, MySQL comes with a script to help you secure your installation. Run the following command and follow the prompts:

sudo mysql_secure_installation

This script will allow you to set a root password, remove anonymous users, disallow root login remotely, remove the test database, and reload the privilege tables.

Step 4: Log in to MySQL

Now that you have MySQL installed and secured, you can log in as the root user:

sudo mysql -u root -p

You'll be prompted to enter the root password you set during the secure installation process.

Step 5: Create a Database

Let's create a new database. Replace `<database_name>` with your desired database name:

CREATE DATABASE <database_name>;

For example, to create a database named "mydb," use this command:

CREATE DATABASE mydb;

SHOW DATABASES;

Step 6: Create a MySQL User

After creating the database, you can create a MySQL user and grant privileges to it. Replace `<username>` and `<password>` with your preferred username and password:

CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';

For example, to create a user named "myuser" with the password "mypassword," use this command:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

Now, grant the user privileges on the database you created:

GRANT ALL PRIVILEGES ON <database_name>.* TO '<username>'@'localhost';

Using the example user and database from above, grant privileges as follows:

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';

Don't forget to flush the privileges to apply the changes:

FLUSH PRIVILEGES;

To change mysql user password 

Use mysql;

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

Step 7: Create a Table

With your database and user in place, you can now create tables and start storing data. Let's create a simple table for demonstration. Replace `<table_name>` with your desired table name and define the table structure:

CREATE TABLE <table_name> (

   employee_id INT AUTO_INCREMENT PRIMARY KEY,
   first_name VARCHAR(255),
   last_name VARCHAR(50) NOT NULL,
   email VARCHAR(100) UNIQUE
   hire_date DATE,
   salary DECIMAL(10, 2)
);

For example, to create a table named "employees" with columns for employee_id, first_name, last_name, email, hire_date and salary:

CREATE TABLE employees (

    employee_id INT AUTO_INCREMENT PRIMARY KEY,

    first_name VARCHAR(50) NOT NULL,

    last_name VARCHAR(50) NOT NULL,

    email VARCHAR(100) UNIQUE,

    hire_date DATE,

    salary DECIMAL(10, 2)

);

To view all available tables in database 

SHOW TABLES;

To see table's field formats.

DESCRIBE table_name;

To view contant of a tables in database 

SELECT * FROM table_name;

8. Delete a Database:

To delete a database, you can use the MySQL command line:

DROP DATABASE your_database_name;

 9. Delete a Table:

To delete a table, you can use the MySQL command line. Make sure you're in the correct database:

USE your_database_name;

Then, drop the table:

DROP TABLE your_table_name;

10. Change MySQL User Password:

To change the password for a MySQL user, use the following command:

sql

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

Replace 'username' with the actual username and 'new_password' with the new password.

11. Backup and Restore:

• To create a MySQL backup (dump):
  
  mysqldump -u <username> -p <database_name> > backup.sql
  
  
• To restore a backup:
  
  mysql -u <username> -p <database_name> < backup.sql

12. Optimize and Repair Tables:

• To optimize all tables in a database:
  
  
• OPTIMIZE TABLE <table_name>;
  
  
• To repair all tables in a database:
  
  REPAIR TABLE <table_name>;

TIP : If you want to give graphical access of mysql server to the users you can install PhpMyAdmin or Adminer also MySQL Workbench client  for Developer  

Download Adminer

Now, you have a MySQL server up and running on your CentOS 8 system, a database created, a MySQL user set up, and a table ready to store your data.

Remember to replace placeholders with your actual database, user, password, and table names as needed. MySQL is a versatile and powerful database system, and you can continue to build and expand your database as your application or project grows.