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:

[top-companies category="vps"

Was this article helpful?