Introduction
MySQL is one of the most utilized open source database platforms globally. It’s available both as a 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 will show how to install MySQL 8.0 Community version which is currently the latest version (and includes powerful features, yet very easy to set up and use). We will then illustrate how to retrieve the mysql root password, alter it and create a database in Ubuntu 18.04 Linux VPS.
Special Note: before installing MySQL on your server we recommend that you consider if you have the right hosting service. Hosting Services that specialize in MySQL database hosting (such as A2hosting) will be able to provide better support on MySQL related topics. Visit Hostadvice’s reviews for the best MySQL hosting services.
MySQL Installation
Download MySQL Apt Repository
$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb
Install the MySQL Apt Repository
$ sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb
MySQL installer appears as shown below and prompts you to choose the appropriate mysql repository’s version. Ensure the selected mysql version in “MySQL Server & Cluster” is “mysql-8.0”. Then press the Down key in your computers to select “OK” then press Enter key.
Then you’re returned to the shell prompt upon completion of mysql apt repo installation.
You can confirm that the repository for MySQL 8.0 has been installed by checking the file /etc/apt/sources.list.d/mysql.list
$ sudo vim /etc/apt/sources.list.d/mysql.list
The file should have content as shown below:
### THIS FILE IS AUTOMATICALLY CONFIGURED ### # You may comment out entries below, but any other modifications may be lost. # Use command 'dpkg-reconfigure mysql-apt-config' as root for modifications. deb http://repo.mysql.com/apt/ubuntu/ bionic mysql-apt-config deb http://repo.mysql.com/apt/ubuntu/ bionic mysql-8.0 deb http://repo.mysql.com/apt/ubuntu/ bionic mysql-tools #deb http://repo.mysql.com/apt/ubuntu/ bionic mysql-tools-preview deb-src http://repo.mysql.com/apt/ubuntu/ bionic mysql-8.0
Install MySQL 8.0 Server and Start MySQL Service
First of all update the repository by running command:
$ sudo apt-get update
Then install the mysql server
$ sudo apt-get install -y mysql-server
MySQL Installer will provide several prompts during the installation as follows:
Enter root password:
Repeat root password
Information on the new changes in MySQL 8.0 about the new improved SHA-256 password authentication method. Press Tab to highlight the “OK”, then Enter to continue.
Select the default authentication plugin. This is a new feature in MySQL 8.0 which recommends usage of improved SHA-256 password authentication method which is the highlighted “Use Strong Password Encryption (RECOMMENDED)”. Incase you were doing an upgrade from MySQL 5.7 to 8.0, choosing this method will prevent old clients from connecting to the server. If this is the case, you’ll have to choose the other option “Use Legacy Authentication Method (Retain MySQL 5.* Compatibility)”
Then Press Enter for the installation to continue.
At the end of installation start mysql server.
$ sudo systemctl start mysqld
Confirm the mysql version is 8.0
$ mysql -version mysql Ver 8.0.11 for Linux on x86_64 (MySQL Community Server - GPL)
Secure the MySQL Installation
$ sudo mysql_secure_installation Securing the MySQL server deployment. Enter password for user root: VALIDATE PASSWORD PLUGIN 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 plugin? Press y|Y for Yes, any other key for No: No Using existing password for root. Change the password for root ? ((Press y|Y for Yes, any other key for No) : No ... 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 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 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 - 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 Success. All done!
Create MySQL Database
Log into mysql server as root
$ mysql -u root -p
Enter the password for root in the prompt.
Create new database called ‘myfirstdb’
mysql> CREATE DATABASE myfirstdb;
List all databases in the mysql-server
mysql> SHOW DATABASES;
Sample Output
$ 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 MySQL Community Server - GPL 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> CREATE DATABASE myfirstdb; Query OK, 1 row affected (0.03 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | myfirstdb | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 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:
- Read more about the best VPS hosting providers you can find today
- Your query to the best web hosting can end by clicking on this link.
