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:
- Ready to upgrade to VPS? Read everything you need to know about the Best VPS hosting providers available today
- Want info about best web hosting? Clicking this link can be of great help.
