In this article, We are going to perform How to install MySQL 8 on CentOS 8 or any other cloud platform like Amazon EC2, Azure VM, Google Compute Cloud, etc., with preinstalled CentOS 8.
Table of Contents
Introduction
MySQL is open-source relational database management server and mostly used in web-base technology and is owned by Oracle.
Prerequisites
- CentOS 8 with Minimal Installation
- SSH access with sudo privileges
- Firewall Port: 3306
New Features in MySQL 8.0
- Transnational Data Dictionary
- Atomic Data Definition Statement Support
- Enhanced Security and account management.
- Persistent runtime configuration
- Table encryption management
- MySQL’s JSON functionality Enhancements
- Support different types of indexes and Query Optimization
- Supports windows functions
- Handling client connections on multiple network interfaces
If you want to know detailed features of MySQL 8.0 , go through this link.
Step 1: Download and Install MySQL 8 RPM Repository
Switch to Root user
$ sudo su
Below are commands to add/download MySQL RPM repository using command line,
# wget https://dev.mysql.com/get/mysql80-community-release-el8-1.noarch.rpm
Then, below command is to install above downloaded rpm repository,
# rpm -ivh mysql80-community-release-el8-1.noarch.rpm
Check if MySQL 8 repository added and Enables in CentOS 8
# yumrepolist all | grep mysql | grep enabled
Output:
Last metadata expiration check: 0:18:06 ago on Sun 05 Apr 2020 06:01:32 AM EDT. mysql-connectors-community MySQL Connectors Community enabled: 42 mysql-tools-community MySQL Tools Community enabled: 19 mysql80-community MySQL 8.0 Community Server enabled: 31
Step 2: Install MySQL 8 on CentOS 8
Disable AppStream temporarily and Install MySQL 8 Community Server
# yum --disablerepo=AppStream install -y mysql-community-server
Sample output:
MySQL 8.0 Community Server 27 MB/s | 27 kB 00:00 Importing GPG key 0x5072E1F5: Userid : "MySQL Release Engineering <[email protected]>" Fingerprint: A4A9 4068 76FC BD3C 4567 70C8 8C71 8D3B 5072 E1F5 From : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql Key imported successfully Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : mysql-community-common-8.0.19-1.el8.x86_64 1/4 Installing : mysql-community-libs-8.0.19-1.el8.x86_64 2/4 Running scriptlet: mysql-community-libs-8.0.19-1.el8.x86_64 2/4 Installing : mysql-community-client-8.0.19-1.el8.x86_64 3/4 Running scriptlet: mysql-community-server-8.0.19-1.el8.x86_64 4/4 Installing : mysql-community-server-8.0.19-1.el8.x86_64 4/4 Running scriptlet: mysql-community-server-8.0.19-1.el8.x86_64 4/4 Verifying : mysql-community-client-8.0.19-1.el8.x86_64 1/4 Verifying : mysql-community-common-8.0.19-1.el8.x86_64 2/4 Verifying : mysql-community-libs-8.0.19-1.el8.x86_64 3/4 Verifying : mysql-community-server-8.0.19-1.el8.x86_64 4/4 Installed: mysql-community-server-8.0.19-1.el8.x86_64 mysql-community-client-8.0.19-1.el8.x86_64 mysql-community-common-8.0.19-1.el8.x86_64 mysql-community-libs-8.0.19-1.el8.x86_64 Complete!
Start the MySQL Service
# systemctl start mysqld
Check status MySQL Service
# systemctl status mysqld
Output:
mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2020-04-05 06:14:09 EDT; 3s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 18779 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 18881 (mysqld)
Status: "Server is operational"
Tasks: 39 (limit: 26213)
Memory: 578.6M
CGroup: /system.slice/mysqld.service
└─18881 /usr/sbin/mysqld
Enable the MySQL service at system startup
# systemctl enable mysqld
Step 3: Secure MySQL Installation
After installing MySQL 8 root password can be found in /var/log/mysqld.log directory, copy it and enter while securing MySQL
# cat /var/log/mysqld.log | grep -i 'temporary password'
Output:
2020-04-05T10:13:29.408103Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: dgZWT4Nut>2A
MySQL Server comes with a script mysql_secure_installation this can do multiple security related operations,
# mysql_secure_installation
Next, you have to enter yes or No to below security features,
- Change the password for root ? ((Press Y|Y for Yes, any other key for No)
- Remove anonymous users? (Press Y|Y for Yes, any other key for No)
- Disallow root login remotely? (Press Y|Y for Yes, any other key for No)
- Remove test database and access to it? (Press Y|Y for Yes, any other key for No)
- Reload privilege tables now? (Press Y|Y for Yes, any other key for No)
Securing the MySQL server deployment. Enter password for user root: Enter the Temporary Password The existing password for the user account root has expired. Please set a new password. New password: Enter Root Password Re-enter new password: Re-Enter Root Password The 'validate_password' component is installed on the server. The subsequent steps will run with the existing configuration of the component. Using existing password for root. Estimated strength of the password: 100 Change the password for root ? ((Press y|Y for Yes, any other key for No) : no We have already set root password ... skipping. 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 - To remove Anonymous user 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 - Disallow Root Login Remotely Success. 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 - To Remove Test Database - 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 - To make above changes , Reload it. Success. All done!
Step 4: Login to MySQL Database
Use below command to login MySQL database
# mysql -u root -p
here , -u flags specifies User Name and -p flags specifies Password.
After entering password , We can see below welcome message,
Enter password: Enter the root user password here
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.19 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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>
Successfully We have covered install MySQL 8 on centos 8.
Step 5: Create MySQL Remote User
First, Login to MySQL Server with root user using command line,
Login to MySQL using username root and password
# mysql -u root -p
Below is command is to create user , here i am creating user “fosstechnix“.
mysql> CREATE USER 'fosstechnix'@'%' IDENTIFIED BY 'Fosstechnix@123';
Next, assign the privileges to database with below command , here i am assigning all databases privileges to user fosstechnix,
If you want to assign privileges to specific database replace ” .” with database name.
mysql> GRANT ALL PRIVILEGES ON * . * TO 'fosstechnix'@'%';
OR
If you want to allow “fosstechnix” user to give privileges to other user.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'fosstechnix'@'%' WITH GRANT OPTION;
To take effect reload the privileges using below command,
mysql> FLUSH PRIVILEGES;
To Exit from mysql> prompt, Type exit.
mysql> exit
Step 6: Enable MySQL Remote Access
By default, In MySQL database server remote access is disabled for security reason.
To enable remote connections of MySQL Server, we have to change bind-address in MySQL configuration file.
Open the /etc/my.cnf file
# vim /etc/my.cnf
Below the [mysqld] section find the Line,
[mysqld] bind-address = 127.0.0.1
And replace it to
bind-address = 0.0.0.0
Restart the MySQL Server to take effect.
# systemctl restart mysqld
To Enable MySQL 8 on System startup.
$ systemctl enable mysqld
Step 7: Start,Stop,Restart and Status of MySQL Service
To start MySQL Service
# systemctl start mysqld
To stop MySQL Service
# systemctl stop mysqld
To restart of MySQL Service
# systemctl restart mysqld
To check status of MySQL Service
# systemctl status mysqld
Conclusion
In this article, We have covered How to install MySQL 8 on CentOS 8, mysql secure installation, creating user in MySQL 8, Enable remote access in MySQL and checked MySQL Service to start,stop,enable and restart.
Related Articles:
How to Install MySQL 5.7 on Ubuntu 16.04
How to Install MySQL 8.0 on Ubuntu 18.04/16.04
How to Install MariaDB on Ubuntu 18.04/16.04 LTS
Reference
