How to Install MySQL 8.0 Community Edition on Ubuntu 18.04

As of February 2019, MySQL 8.0 was the newest version of MySQL. The new and exciting version has great improvements that you probably don’t want to miss.

Being an open source database, MySQL 8.0 has been re-engineered to offer most Windows SQL  functions as well as extended JSON functions. The InnoDB engine has been improved to offer a crash-safe environment and the performance schema has been enhanced.

Another great feature that ships with MySQL 8.0 is role management for organizing users better.

You can take advantage of all these great MySQL 8.0 features by installing it on your Virtual Private Server(VPS).

This guide takes you through the steps of setting up MySQL 8.0 community edition on your Ubuntu 18.04 server.

Prerequisites

Before you begin, make sure you have the following:

  1. A VPS account. Signup with Digital Ocean and get free $100 credit to test this article and other cloud services.
  2. A non-root user that can perform sudo tasks on your Ubuntu 18.04 server.

Step 1: Downloading MySQL 8.0 Repository

The default MySQL edition available on the Ubuntu software repository is not the newest version. So we are going to manually download the repository using wget command.

First,  cd to the tmp directory:

$ cd /tmp

Then, add the repository.

$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.12-1_all.deb

Next, we are going to use the dpkg command to install the repository on Ubuntu 18.04 server:

$ sudo dpkg -i mysql-apt-config_0.8.12-1_all.deb

A dialog box will appear, choose OK and press Enter to continue.

Step 2: Installing MySQL 8.0 On Ubuntu 18.04 VPS

Next, update the package information index:

$ sudo apt-get update

To install MySQL, run the command below:

$ sudo apt-get install  mysql-server

Press Y and hit Enter when prompted to confirm the installation.

Towards the end of the installation, you will be prompted to enter the root user of your MySQL server; don’t confuse this with the root account of your VPS server.

 

Enter a strong password and hit Enter to continue. You will be prompted to repeat the password.

Select OK on the next dialog box and hit Enter

Then, select the right authentication plugin

The installation should be successfully installed.  You can confirm the MySQL version on your Ubuntu 18.04 server by running the command below:

$ mysql --version

Output:

mysql  Ver 8.0.15 for Linux on x86_64 (MySQL Community Server - GPL)

Step 3: Loggin to the MySQL 8.0 Server

To log in to the MySQL 8.0 server, run the command below:

$mysql -uroot -p

Enter the root password of the MySQL server and hit Enter to continue.

You will get a prompt similar to the one shown below:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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>

You can then start issuing commands on the MySQL command line interface e.g., show databases;

mysql> show databases;

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec

Conclusion

In this article, we have taken you through the steps of configuring MySQL 8.0 on your Ubuntu 18.04 VPS.

Sign up with Digital Ocean VPS today and get free $100 to deploy MySQL 8.0  on your next project to take advantage of improved features that you can’t find on other versions of MySQL.

How to Setup Fault-Tolerant Database with MySQL Group Replication on Ubuntu 18.04

MySQL group replication is a plugin that provides the functionality of creating a shared-nothing fault-tolerant database architecture. The add-on utilizes a group of servers that interact with each other to agree on the state of a database at any given time and any changes that may occur.

The plugin is useful when creating mission critical applications that require  highly available databases. It simply creates an additional layer of security by coordinating redundant servers in a simple way.

Data is replicated across multiple servers and if a member leaves a group, an auto-detect mechanisms notifies the other members about the change.  If the remaining servers can agree on a quorum, database operations resume as expected.

In this guide, we will walk you through the steps of setting up MySQL Group Replication plugin on Ubuntu 18.04 server.

Prerequisite

Step 1: Installing MySQL Community Edition

The default MySQL package available on the Ubuntu software repository does not support MySQL Group Replication plugin. So we will manually pull deb packages from the official MySQL download page and install the Community Edition.

We will repeat this procedure in all the 3 servers since each server will house its own copy of the replicated MySQL database:

SSH to server1 and navigate to the tmp directory using Linux cd command:

$ cd /tmp

Then, use wget command to download a tar archive with all the deb package files that we are going to install:

$ wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-server_5.7.23-1ubuntu18.04_amd64.deb-bundle.tar

Once the file is downloaded, make an installation directory:

$ mkdir installation

Unzip the archive file to the installation directory that we created:

$ tar -xvf mysql-server_5.7.23-1ubuntu18.04_amd64.deb-bundle.tar -C installation/

All the necessary deb files should now be placed under the installation directory. However, before we install them, we need to install libaio1 and libmecab2 dependencies for MySQL to work.

So,first update the package information index, then install the dependencies using the commands below:

$ sudo apt-get update
$ sudo apt-get install libaio1
$ sudo apt-get install libmecab2

Next, cd  to the installation directory:

$ cd installation

Then, install MySQL community server using the dpkg package manager by running the below commands one by one:

$ sudo dpkg -i mysql-common_5.7.23-1ubuntu18.04_amd64.deb
$ sudo dpkg -i mysql-community-client_5.7.23-1ubuntu18.04_amd64.deb
$ sudo dpkg -i mysql-client_5.7.23-1ubuntu18.04_amd64.deb
$ sudo dpkg -i mysql-community-server_5.7.23-1ubuntu18.04_amd64.deb

Enter a secure root password for your MySQL server when prompted.Repeat this procedure in all the 3 servers participating in the group replication.

Step 2: Generating a Universally Unique Identifier (UUID) for the Group

Next, we will generate Universally Unique Identifier (UUID) for our group. To do this, login to MySQL on server1 using the command below:

$ sudo mysql -uroot -p

Enter the root password of MySQL server that your created above  when prompted and press Enter to continue.

Then, run the below SQL command to generate the UUID:

SELECT UUID();
+————————————–+
| UUID()                               |
+————————————–+
| 99bfc355-cd41-11e8-ba46-5600018c8695 |
+————————————–+

Copy the value generated above, we will need it when making changes to the MySQL server configuration file.

Step 3: Configuring MySQL Group Replication Settings on server1

Next, we are going to configure server1 to support group replication by editing the MySQL configuration file using nano editor:

$ sudo nano /etc/mysql/my.cnf

Then, paste the below content at  the end of the file:

[mysqld]

bind-address= 0.0.0.0
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

The above configurations enable remote access to the MySQL server and assigns 1 as the unique server identifier. They also instruct MySQL  to enable global transaction identifiers and disable checksum for the binary log file.

Next, paste the settings below:

log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE

These configurations enable binary logging in a row-based format and instruct MySQL to store replication information in system tables instead of files to speed up replication.

Finally, paste the below group replication information and remember to replace the group name with the UUID that we generated earlier.

The address 192.0.2.1 should match the private IP address of your virtual machine instances.

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name=”99bfc355-cd41-11e8-ba46-5600018c8695″
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= “192.0.2.1:33061”
loose-group_replication_group_seeds= “192.0.2.1:33061, 192.0.2.2:33061, 192.0.2.3:33061”
loose-group_replication_bootstrap_group=offreport_host=192.0.2.1

The loose-group_replication_local_address should match the private IP address of  server1.

Also, make sure to include all the 3 private IP addresses for the servers participating in group replication as the value of the loose-group_replication_group_seeds.  

For the sake of simplicity, we have used the IP addresses 192.0.2.1, 192.0.2.2 and 192.0.2.3 for server1, server2 and server3 respectively.

Remember, the recommended port for MySQL Group Replication is 33061.

The loose- prefix  prevents MySQL server to encounter problems if the server starts before the plugin is installed.

The transaction_write_set_extraction configures the server to gather the write instructions set and encode it using the XXHASH64 algorithm

The group_replication_group_name sets the name of the group that is being created.

When set to off, the group_replication_start_on_boot directive disables the plugin from loading when the server starts.

The value specified in the group_replication_local_address tells the plugin to use the IP address and port combination to communicate to the other members of the group.

The  group_replication_group_seeds parameter is used to set the hosts and ports that will be used by new members joining the group. Although we have included all the 3 server addresses here, it is not mandatory to do this and you can choose only a few members as the value for the seeds depending on your group size.

We have set the loose-group_replication_bootstrap_group to off to avoid creating a new group with the same name every time our server reboots.

Once you have made the above changes, save the file by pressing CTRL+X, Y and Enter. Then run the command below to restart MySQL service on server1:

$ sudo service mysql restart

Step 4: Setting up User Credentials for the Group Replication Recovery Channel

To achieve distributed recovery, MySQL Group Replication uses the asynchronous replication protocol. This technology synchronizes new members before adding them to the group using group_replication_recovery channel .

Therefore a replication user must be created on each member in the group to aid in transferring transactions. To achieve this, log in to server1 using the command below:

$ sudo mysql -uroot -p

Enter the root password when prompted and hit Enter. Then disable binary logging to avoid logging the changes on the binary file:

mysql> SET SQL_LOG_BIN=0;

Then, run the commands below one by one to create the replication user and assign the correct privileges. Remember to replace PASSWORD with a strong value for security purposes.

mysql> CREATE USER ‘rep_user’@‘%’ IDENTIFIED BY ‘PASSWORD’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘rep_user’@‘%’;
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;

With the user configured, we can use the CHANGE MASTER TO query to tell MySQL  to assign the user to the group_replication_recovery channel:

mysql> CHANGE MASTER TO MASTER_USER=‘rep_user’, MASTER_PASSWORD=‘PASSWORD’      FOR CHANNEL ‘group_replication_recovery’;

Step 5: Installing the Group Replication Plugin and Bootstrapping the Group

We can now go ahead and install the group replication plugin on server1 using the command below:

mysql> INSTALL PLUGIN group_replication SONAME ‘group_replication.so’;

Everything is now set and we can now bootstrap the group with the commands below:

mysql>SET GLOBAL group_replication_bootstrap_group=ON;
mysql>START GROUP_REPLICATION;

To avoid bootstrapping multiple groups with the same name when the server restarts, we will set the GLOBAL group_replication_bootstrap_group back to off :

mysql>SET GLOBAL group_replication_bootstrap_group=OFF;

Once the group is started, we can check its status:

mysql> SELECT MEMBER_ID,MEMBER_HOST,MEMBER_STATE FROM performance_schema.replication_group_members;

Output:

+————————————–+————-+————–+
| MEMBER_ID                            | MEMBER_HOST | MEMBER_STATE |
+————————————–+————-+————–+
| 18cf9650-cde5-11e8-9e26-560001b743c7 | 192.0.2.1   | ONLINE |
+————————————–+————-+————–+

The output above shows that server1 is online and indeed a member of our group.

MySQL Group Replication works with Innodb tables. So , we are going to create a test database to see if the database server is working as expected.

mysql> create database test_replication;

Then, we can switch to the database:

mysql> use test_replication;

Next, we need to create a test_table:

mysql> create table test_table (student_id INT PRIMARY KEY,
student_name VARCHAR(30) NOT NULL) Engine = InnoDB;

We can confirm the presence of the table by running the command below:

mysql> show tables;

Output:

+—————————-+
| Tables_in_test_replication |
+—————————-+
| test_table                 |
+—————————-+

Step 6: Configuring server2

Once server1 is up and our group is running, we can now go ahead and configure server2 and join it to the group.

SSH to server2 and make sure you have installed MySQL Community Edition server as outlined in step 1. Then, edit the MySQL configuration file using a text editor:

$ sudo nano /etc/mysql/my.cnf

Paste the below settings at the end of the file and remember to replace 192.0.2.2 with the correct private addresses of your VPS :

[mysqld]

bind-address= 0.0.0.0
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name=”99bfc355-cd41-11e8-ba46-5600018c8695″
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= “192.0.2.2:33061”
loose-group_replication_group_seeds= “192.0.2.1:33061, 192.0.2.2:33061, 192.0.2.3:33061”
loose-group_replication_bootstrap_group=off

report_host=192.0.2.2

Press CTRL+X, Y and Enter to save the file.

Restart MySQL server for the changes to take effect using the command below:

$ sudo service mysql restart

Login to MySQL on server2:

$ sudo mysql-uroot -p

Enter your database password when prompted and hit Enter.

Next, create the replication user for the group replication recovery channel on server2 by running the commands below.

mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER ‘rep_user’@‘%’ IDENTIFIED BY ‘PASSWORD’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘rep_user’@‘%’;
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER=‘rep_user’, MASTER_PASSWORD=‘PASSWORD’      FOR CHANNEL ‘group_replication_recovery’;

Then, install the MySQL group replication plugin on server2 using the commands below:

mysql>INSTALL PLUGIN group_replication SONAME ‘group_replication.so’;

Then, start the plugin to join server2 to the group:

mysql> START GROUP_REPLICATION;

You  can confirm the status of the group by running the command below on server2:

mysql> SELECT MEMBER_ID,MEMBER_HOST,MEMBER_STATE FROM performance_schema.replication_group_members;

Output:

+————————————–+————-+————–+
| MEMBER_ID                            | MEMBER_HOST | MEMBER_STATE |
+————————————–+————-+————–+
| 18cf9650-cde5-11e8-9e26-560001b743c7 | 192.0.2.1   | ONLINE |
| 210cc012-cdf4-11e8-8be1-560001b74419 | 192.0.2.2   | ONLINE |
+————————————–+————-+————–+

As you can see from the output above, we now have two members in the group. To confirm if server2 was able to pick up data from server1, we can run the command below and see whether the sample database that we created above was synchronized succesfully:

mysql> show databases;

Output:

+——————–+
| Database           |
+——————–+

| test_replication   |
+——————–+

Step 7: Configuring server3

Just like we have done on server2, we can configure server3 and join it to the group. Before doing this, make sure you have installed MySQL community on server3 as discussed in step 1.

Then, edit the configuration file of server3 using nano text editor:

$ sudo nano /etc/mysql/my.cnf

Paste the below configuration information at the end of the file. Remember to replace 192.0.2.3 with the correct private address assigned to your server3 virtual machine instance.

[mysqld]

bind-address= 0.0.0.0
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name=”99bfc355-cd41-11e8-ba46-5600018c8695″
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= “192.0.2.3:33061”
loose-group_replication_group_seeds= “192.0.2.1:33061, 192.0.2.2:33061, 192.0.2.3:33061”
loose-group_replication_bootstrap_group=off

report_host=192.0.2.3

Press CTRL+X, Y and hit Enter to save the file.Then, restart MySQL server for the changes to take effect:

$ sudo service mysql restart

Next, log in to MySQL on server3:

$ sudo mysql -uroot -p

Enter your MySQL password when prompted and hit Enter. We need to create a user for the group replication channel on server3 just like we did on server1 and server2:

mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER ‘rep_user’@‘%’ IDENTIFIED BY ‘PASSWORD’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘rep_user’@‘%’;
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER=‘rep_user’, MASTER_PASSWORD=‘PASSWORD’      FOR CHANNEL ‘group_replication_recovery’;

Next, run the command below to install the Group Replication plugin:

mysql>INSTALL PLUGIN group_replication SONAME ‘group_replication.so’;

Then start the Group Replication plugin to join server3 the group:

mysql> START GROUP_REPLICATION;

We can then confirm the status of our group using the commands below:

mysql> SELECT MEMBER_ID,MEMBER_HOST,MEMBER_STATE FROM performance_schema.replication_group_members;

Output:

+————————————–+————-+————–+
| MEMBER_ID                            | MEMBER_HOST | MEMBER_STATE |
+————————————–+————-+————–+
| 1634bce6-cdfb-11e8-8f8d-560001b74425 | 192.0.2.1   | ONLINE |
| 18cf9650-cde5-11e8-9e26-560001b743c7 | 192.0.2.2   | ONLINE |
| 210cc012-cdf4-11e8-8be1-560001b74419 | 192.0.2.3   | ONLINE |
+————————————–+————-+————–+

As you can see above, our group has 3 members like we expected and our Group Replication setup is working like expected.

We can confirm if server3 was able to synchronize data from the group by running the command below:

mysql> show databases;

Output:

+——————–+
| Database           |
+——————–+

| test_replication   |
+——————–+

The database that we created is already synchronized and our Group Replication is working as expected.

Conclusion

In this guide, we have taken you through the steps of setting up and configuring MySQL group replication on Ubuntu 18.04 server.

If you have followed along, you are able to create a fault-tolerant database cluster using a shared-nothing architecture that ensures high availability for your database server.

New to VPS hosting, sign up with Digital Ocean and get $100 worth of free trial credit.