How to Install MYSQL 8.0 and Create a Database on a CentOS 7 Linux VPS or Dedicated Server

How to Install MYSQL 8.0 and Create a Database on a CentOS 7 Linux VPS or Dedicated Server

Introduction

MySQL is one of the most widely used open-source database platform globally. It’s available both as community version (free) and enterprise version (paid) which has more features for enterprise environment.

MySQL is a relational database system with Structure Query Language (SQL) commands [SELECT, CREATE TABLE, UPDATE, DELETE, INSERT, DROP TABLE, e.t.c] for managing relational databases. In this tutorial, we show how to install MySQL 8.0 Community version which is currently the latest version with very powerful features, yet very easy to set up and use. Then we illustrate how to retrieve the MySQL root password, alter it and create a database in CentOS 7 Linux VPS.

MySQL Installation

Remove/Uninstall the MariaDB package if it’s installed in CentOS 7

$ sudo yum remove mariadb mariadb-server

Delete/Remove the config files

$ sudo rm -rf /etc/my.cnf /etc/my.cnf.d

Add the MySQL 8.0 Repository

Run the commands to download repository for MySQL 8.0 Installation.

$ cd /usr/local/src
$ sudo  wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
$ sudo rpm -ivh mysql80-community-release-el7-1.noarch.rpm

Install the MySQL 8.0 Server and Start MySQL Service

$ sudo yum install mysql-server
$ sudo systemctl start mysqld

Get the default password for root (MySQL database user)

The temporary root password is usually generated during mysql-server installation and saved in MySQL error log file.

Check the main MySQL config file for the location of MySQL log file.

$ sudo vim /etc/my.cnf

Sample output of my.cnf config file

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Note the line: log-error=/var/log/mysqld.log

The location of the log file is /var/log/mysqld.log

Run the command below to retrieve the password:

$ more /var/log/mysqld.log | grep temporary

Sample Output

[linuxuser@centos7-hostadvice4 ~]$ more /var/log/mysqld.log | grep temporary
2018-06-11T14:14:13.216928Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: u)shs5&cOn5m

Create MySQL Database

Log into mysql server as root

$ mysql -u root -p

Enter the temporary password for root in the prompt.

Then change the root password

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPasswword2018&*';

Create new database called ‘myfirstdb’

mysql> CREATE DATABASE myfirstdb;

List all databases in the mysql-server

mysql> SHOW DATABASES;

Sample Output

[linuxuser@centos7-hostadvice4 ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.11
 
Copyright (c) 2000, 2018, 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> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPasswword2018&*';
Query OK, 0 rows affected (0.07 sec)
 
mysql> CREATE DATABASE myfirstdb;
Query OK, 1 row affected (0.06 sec)
 
mysql> SHOW DATABASES;
+--------------------+
| Database       |
+--------------------+
| information_schema |
| myfirstdb      |
| mysql          |
| performance_schema |
| sys            |
+--------------------+
5 rows in set (0.14 sec)
 
mysql> quit;
Bye

Conclusion

We have now gotten you up and running in the most critical part (i.e. installation of the latest version of MySQL Server and creating a database). The next step is to learn how to run more MySQL commands in order to manipulate the database to your liking. These include creating new mysql users, granting users privileges, creating tables in databases, inserting and updating records in tables, making queries to obtain records from the tables and many other functionalities. You should also consider learning how to backup and restore databases from the command line.

 

Check out these top 3 VPS services:

Kamatera
$4.00 /mo
Starting price
Visit Kamatera
Rating based on expert review
  • User Friendly
    3.5
  • Support
    3.0
  • Features
    3.9
  • Reliability
    4.0
  • Pricing
    4.3
Hostinger
$2.99 /mo
Starting price
Visit Hostinger
Rating based on expert review
  • User Friendly
    4.7
  • Support
    4.7
  • Features
    4.8
  • Reliability
    4.8
  • Pricing
    4.7
Liquid Web
$5.00 /mo
Starting price
Visit Liquid Web
Rating based on expert review
  • User Friendly
    3.8
  • Support
    4.8
  • Features
    4.5
  • Reliability
    4.6
  • Pricing
    3.8

How to Run ProxySQL Against MySQL for Monitoring on CentOS 7

ProxySQL serves to monitor and filter; as a firewall all incoming requests from
3 min read
Kennedy Mbuvi
Kennedy Mbuvi
Author

How to Create New MySQL Users Accounts and Manage MySQL Privileges

This tutorial will help you create new MySQL (database) user accounts and grant
4 min read
David Malcom
David Malcom
Author

How to Tune and Optimize Performance of MySQL 8.0 on a CentOS 7 Server

Sometimes, MySQL face performance limitations if not properly tuned and optimize
8 min read
Eliran Ouzan
Eliran Ouzan
Web Designer & Hosting Expert

How to Install phpMyAdmin on a CentOS 7 VPS or Dedicated Server

Learn how to setup phpMyAdmin on your CentOs 7 VPS server and manage your MySQL/
2 min read
Max Ostryzhko
Max Ostryzhko
Senior Web Developer, HostAdvice CTO
HostAdvice.com provides professional web hosting reviews fully independent of any other entity. Our reviews are unbiased, honest, and apply the same evaluation standards to all those reviewed. While monetary compensation is received from a few of the companies listed on this site, compensation of services and products have no influence on the direction or conclusions of our reviews. Nor does the compensation influence our rankings for certain host companies. This compensation covers account purchasing costs, testing costs and royalties paid to reviewers.
Click to go to the top of the page
Go To Top