How to Install MySQL Server on CentOS 8 (Database Setup Tutorial)
How to Install MySQL Server on CentOS 8. MySQL is a free, open source and one of the leading RDBMS that helps to store, manage and modify the data. It is suitable for applications, websites and online transactions. It is developed by the Swedish company MySQL AB in 1995. It is written in C and C++, and compatible with Windows, Linux, and Mac. Currently, MySQL is owned and managed by Oracle. MySQL uses a Structured Query Language (SQL) to authenticate and perform queries. MySQL server is used by many big organizations including WeChat, Facebook, GitHub, NASA, YouTube, etc.
MySQL Server Features
- Free and Open Source.
- Easy to learn and troubleshoot.
- Suitable for Web, Cloud, and Big Data platforms.
- It has built in advanced data security features and supports multiple storage engines.
- Compatible with many operating systems ( Linux, Windows, FreeBSD).
- GUI Support with user interface “MySQL Workbench“.
- Memory Efficient– MySQL has low memory leakage.
- Supports Master Slave replication and Multi version concurrency control.
Next, we will explain how to install MySQL Server on CentOS 8.
Install MySQL Server on CentOS 8
How to Install MySQL Server on CentOS 8. By default, the MySQL server version 8 package is included in the CentOS 8 default repository. You can install it using the following command:
dnf install mysql-server -y
Once the MySQL server has been installed, you will need to start the MySQL service and enable it to start at system reboot:
systemctl start mysqld
systemctl enable mysqld
You can check the status of the MySQL server using the following command:
systemctl status mysqld
Sample output:
● mysqld.service - MySQL 8.0 database server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
Active: active (running) since Wed 2021-10-27 04:07:11 EDT; 1s ago
Process: 3420 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS)
Process: 3296 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS)
Process: 3272 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)
Main PID: 3376 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 12524)
Memory: 462.8M
CGroup: /system.slice/mysqld.service
└─3376 /usr/libexec/mysqld --basedir=/usr
Oct 27 04:07:04 centos8 systemd[1]: Starting MySQL 8.0 database server...
Oct 27 04:07:05 centos8 mysql-prepare-db-dir[3296]: Initializing MySQL database
Oct 27 04:07:11 centos8 systemd[1]: Started MySQL 8.0 database server.
To verify the MySQL server version, run the following command:
mysql -V
Sample output:
mysql Ver 8.0.26 for Linux on x86_64 (Source distribution)
To check the detailed information of the MySQL server package, run the following command:
dnf info mysql-server
Sample output:
Installed Packages
Name : mysql-server
Version : 8.0.26
Release : 1.module_el8.4.0+915+de215114
Architecture : x86_64
Size : 121 M
Source : mysql-8.0.26-1.module_el8.4.0+915+de215114.src.rpm
Repository : @System
From repo : AppStream
Summary : The MySQL server and related files
URL : http://www.mysql.com
License : GPLv2 with exceptions and LGPLv2 and BSD
Description : MySQL is a multi-user, multi-threaded SQL database server. MySQL is a
: client/server implementation consisting of a server daemon (mysqld)
: and many different client programs and libraries. This package contains
: the MySQL server and some accompanying files and directories.
To check the MySQL server log for any error, run the following command:
journalctl -u mysqld.service -xe
tail -f /var/log/mysql/mysqld.log
Secure MySQL Installation
By default, the MySQL server is not secured. So it is recommended to secure MySQL and set a root password. You can do it by running the following script:
mysql_secure_installation
You will be asked to set a MySQL root password as shown below:
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No:
Please set the password for root here.
New password:
Re-enter new password:
Set your MySQL root password and press Enter. You will be asked to remove the anonymous users:
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Press Y and hit Enter. You will be asked to disallow root login remotely:
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
Press Y and hit Enter. You will be asked to remove the test database:
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
Press Y and hit Enter. You will be asked to reload the privileges tables:
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
Press Y and hit Enter to complete the script.
Next, log in to MySQL using a root user:
mysql -u root -p
Once you are log in, you will get the following output:
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Now, check the MySQL connection status using the following command:
mysql> STATUS;
Sample output:
--------------
mysql Ver 8.0.26 for Linux on x86_64 (Source distribution)
Connection id: 10
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.26 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Binary data as: Hexadecimal
Uptime: 2 min 7 sec
Threads: 2 Questions: 14 Slow queries: 0 Opens: 133 Flush tables: 3 Open tables: 49 Queries per second avg: 0.110
--------------
To exit from the MySQL shell, run the following command:
mysql> EXIT;
You can also check the MySQL version using the following command:
mysqladmin -u root -p version
You will get the MySQL version information in the following output:
Enter password:
mysqladmin Ver 8.0.26 for Linux on x86_64 (Source distribution)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version 8.0.26
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 3 min 29 sec
Threads: 2 Questions: 18 Slow queries: 0 Opens: 150 Flush tables: 3 Open tables: 66 Queries per second avg: 0.086
Create a Database and User in MySQL
Next section of how to Install MySQL Server on CentOS 8 we aim to show you how to create a database and user in MySQL. First, log in to the MySQL interface using the following command:
mysql -u root -p
Once you are log in, create a database named wpdb using the following command:
mysql> CREATE DATABASE wpdb;
To list all MySQL databases, run the following command:
mysql> SHOW DATABASES;
Sample output:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| wpdb |
+--------------------+
Next, create a user named wpuser and set a password using the following command:
mysql> CREATE USER 'wpuser'@'localhost' IDENTIFIED BY 'secure_password';
To grant specific privileges on wpdb database, run:
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON wpdb.* TO 'wpuser'@'localhost';
To grant all privileges on wpdb database, run:
mysql> GRANT ALL PRIVILEGES ON wpdb.* TO 'wpuser'@'localhost';
To list all MySQL users, run:
mysql> SELECT user,host FROM mysql.user;
Sample output:
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| wpuser | localhost |
+------------------+-----------+
To display all granted privileges, run:
mysql> SHOW GRANTS for wpuser@localhost;
Sample output:
+----------------------------------------------------------+
| Grants for wpuser@localhost |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `wpuser`@`localhost` |
| GRANT ALL PRIVILEGES ON `wpdb`.* TO `wpuser`@`localhost` |
+----------------------------------------------------------+
Create a Table in MySQL
To create a table in MySQL, you will need to switch to the database first.
To switch the database to wpdb, run:
mysql> USE wpdb;
Next, create a new table named employee, run:
mysql> CREATE table employee
(
Employee_id int auto_increment primary key,
Employee_first_name varchar(500) NOT null,
Employee_last_name varchar(500) NOT null,
Employee_emailID varchar(500),
Employee_department_ID int default 9,
Employee_Joining_date date
);
Next, insert some data in to employee table using the following command:
mysql> INSERT INTO employee (employee_first_name, employee_last_name, employee_emailid, employee_department_id) values ('Hitesh','Jethva','hitjethva@gmail.com','01');
mysql> INSERT INTO employee (employee_first_name, employee_last_name, employee_emailid, employee_department_id) values ('Jay','Jethva', 'jayjethva@gmail.com', '01');
mysql> INSERT INTO employee (employee_first_name, employee_last_name, employee_emailid, employee_department_id) values ('Vyom','Jethva','vyomjethva@gmail.com','01');
To display all data from the table, run:
mysql> SELECT * FROM employee;
You will get the following output:
+-------------+---------------------+--------------------+----------------------+------------------------+-----------------------+
| Employee_id | Employee_first_name | Employee_last_name | Employee_emailID | Employee_department_ID | Employee_Joining_date |
+-------------+---------------------+--------------------+----------------------+------------------------+-----------------------+
| 1 | Hitesh | Jethva | hitjethva@gmail.com | 1 | NULL |
| 2 | Jay | Jethva | jayjethva@gmail.com | 1 | NULL |
| 3 | Vyom | Jethva | vyomjethva@gmail.com | 1 | NULL |
+-------------+---------------------+--------------------+----------------------+------------------------+-----------------------+
Remove a Table, Database and User in MySQL
To delete a table, run the following command:
mysql> DROP TABLE employee;
To delete a database, run the following command:
mysql> DROP DATABASE wpdb;
To delete a user, run the following command:
mysql> DROP USER wpuser@localhost;
How to Install MySQL Server on CentOS 8 Conclusion
In the above guide, you learned how to install MySQL server on CentOS 8. You have learned what MySQL is with its features, how to create a user, database and table in MySQL. I hope you have now enough knowledge to interact with the MySQL database.
