How to Set Up an Email Server with Postfix, Dovecot and Roundcube on Ubuntu 18.04

Postfix is a Mail Transfer Agent(Agent). It is a powerful open-source application that is capable of receiving and sending emails.

Dovecot is a free open-source POP3 and IMAP server that delivers and retrieves emails to local mailboxes on the Linux system.

Roundcube is a web-based email client that works pretty well with Postfix and Dovecot.

While utilizing the POP and IMAP protocol on Dovecot, Roundcube can read emails stored by Dovecot on virtual mailboxes.

At the same time, Roundcube can submit emails to Postfix using the SMTP protocol.

So by harnessing the power of three open source applications (Postfix, Dovecot, and Roundcube), you can create a fully functional send/receive email server.

This is a comprehensive guide for setting up an email server with Postfix, Dovecot, and Roundcube on Ubuntu 18.04 server.

Note: For a better experience with Postfix, Sign up with Digital Ocean and get up to $100 free trial credit. We recommend Digital Ocean because they allow outbound and inbound traffic to port 25.

Prerequisites

To follow along with this guide, you will require the following:

  1. A new VPS(Virtual Private Server) account. Sign up with Digital Ocean and enjoy up to $100 worth of free trial credit.
  2. A domain name(e.g. example.com)
  3. A VPS instance running Ubuntu 18.04 as the operating system
  4. A non-root user that can perform sudo tasks

Step 1: Configuring DNS Server

Your email server must have a fully qualified domain name.

To set up this, you must point the A record of your domain to the public IP address associated with your VPS instance.

You must also set MX records on your domain name DNS records editor. This is done from the control panel of your VPS provider.

Then, you need to change the hostname of your server.

To edit the server hostname, open the /etc/hostname file and change the name to something appropriate e.g., mail

$ sudo nano /etc/hostname

Save the file by pressing CTRL+X, Y and, Enter

The next step is editing the hosts’ file /etc/hosts. Open the file using nano text editor.

$ sudo nano /etc/hosts

Make sure you have the below two entries at the top of the file. Replace example.com with your domain name.

127.0.0.1    localhost
127.0.1.1    mail.example.com mail

Reboot the system

$ sudo reboot

Step 2: Installing Apache Web Server

With the server name and DNS records configured, we will go ahead and install Apache web server.

Apache is primarily needed by Roundcube email client for it to run from a browser like Google Chrome.

Install Apache.

$ sudo apt-get update
$ sudo apt-get install apache2

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

Also, make sure the Mod_Rewrite module is enabled. This will be required for Roundcube to work:

$ sudo a2enmod rewrite

Restart Apache.

$ sudo systemctl restart apache2

Step 3: Installing MySQL Database Server

Roundcube requires MySQL for it to work, so we are going to install the database server.

$ sudo apt-get install mysql-server

Press Y and Enter to confirm the installation.

Next, run the command below to secure MySQL database server.

$ sudo mysql_secure_installation

Step 3: Installing Let’s Encrypt Certificate

In order for the mail server to use SSL, we need to install an SSL certificate.

To do this, run the command below and remember to replace example.com with the exact domain name that you intend to use with your mail server.

$ sudo add-apt-repository ppa:certbot/certbot
$ sudo apt-get update
$ sudo apt-get install python-certbot-apache
$ sudo certbot --apache -d example.com -d www.example.com

Step 3: Installing PHP Scripting Language

Roundcube is written in PHP. As such, we need to install PHP together with all associated modules required by Roundcube.

$ sudo apt-get install php libapache2-mod-php php-mysql

Again, press Y when prompted to confirm the installation and hit Enter to continue.

Step 4: Installing Postfix MTA

Next, we are going to install Postfix Mail Transfer Agent (MTA). To do this, run the command below:

$ sudo apt-get install postfix

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

On the next screen, hit TAB then Enter to continue.

You will be prompted to select the mail server configuration type. Choose Internet Site and press TAB and Enter to continue.

On the next screen, you should enter the name of your domain without the ‘www’ part.

So, if your domain name is www.example.com, enter example.com on the system mail name field and hit Tab and Enter to continue.

After a few seconds, Postfix will be successfully installed on your Ubuntu 18.04 server

Step 5: Configuring Postfix

Postfix MTA is a very powerful and secure out-of-the-box. However, it requires a few configurations for it to work with Ubuntu 18.04 and Dovecot.

The main Postfix configuration file is located at /etc/postfix/main.cf.

We will back up this file before editing it by running the command below.

$ sudo mv /etc/postfix/main.cf /etc/postfix/main.cf.bk

This ensures that we can go back to the default settings in case we mess up with the Postfix configuration.

Next, we can create a new Postfix configuration file using nano text editor:

$ sudo nano /etc/postfix/main.cf

Paste the below information on the file and remember to replace example.com with your domain name.

# GENERAL SETTINGS

smtpd_banner = $myhostname ESMTP $mail_name
biff = no
append_dot_mydomain = no
readme_directory = no

# SMTP SETTINGS 

smtp_use_tls=yes
smtp_tls_security_level = may
smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache

# SMTPD SETTINGS 

smtpd_use_tls=yes
smtpd_tls_security_level = may
smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
smtpd_tls_cert_file=/etc/letsencrypt/live/example.com/fullchain.pem
smtpd_tls_key_file=/etc/letsencrypt/live/example.com/privkey.pem
smtpd_relay_restrictions = permit_mynetworks, permit_sasl_authenticated,  reject_unauth_destination

# SASL SETTINGS

smtpd_sasl_auth_enable = yes
smtpd_sasl_type = dovecot
smtpd_sasl_path = private/auth

# VIRTUAL MAIL BOX AND LMTP SETTINGS

virtual_transport = lmtp:unix:private/dovecot-lmtp
virtual_mailbox_domains = /etc/postfix/virtual_mailbox_domains



# OTHER SETTINGS

myhostname = mail.example.com
myorigin = /etc/mailname
mydestination =  localhost.$mydomain, localhost
relayhost = 
mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128
mailbox_size_limit = 0
recipient_delimiter = +
inet_interfaces = all
inet_protocols = all
alias_maps = hash:/etc/aliases
alias_database = hash:/etc/aliases

Once you add the settings above, save and close the file.

Step 6: Creating Virtual Mail Box Domains

Our Postfix configuration file that we created above instructed the mail server to look for virtual mailbox domains from the /etc/postfix/virtual_mailbox_domains file.

We need to add the domains that we intend to use with the mail server on this file. You can add as many domains as you want.

To keep things simple, we are adding the example.com domain.

Open the file:

$ sudo nano /etc/postfix/virtual_mailbox_domains

Then, add the entry below. Please note, the entry MUST be in two parts as shown below.

example.com #domain

Save and close the file when done.

Since Postfix is not configured to read plain text files, we will convert the file to a format that it can understand using the command below:

$ sudo postmap /etc/postfix/virtual_mailbox_domains

Remember, you must run that command each time you edit the /etc/postfix/virtual_mailbox_domains file.

The next step is setting up the Postfix’s master configuration file /etc/postfix/master.cf.

Open the file using nano text editor.

$ sudo nano /etc/postfix/master.cf

Look for the line below.

#submission inet n       -       y       -       -       smtpd

Then, remove the leading # symbol to uncomment it:

submission inet n       -       y       -       -       smtpd

When done, save the file and close it.

Step 7: Installing Dovecot on Ubuntu 18.04

Next, we will install Dovecot and all associated packages necessary for running IMAP, POP and LMTP protocol.

$ sudo apt-get install dovecot-core dovecot-imapd dovecot-pop3d dovecot-lmtpd

Step 8: Configuring Dovecot mail_location

In order for Dovecot to communicate with Postfix and our virtual mailbox domains, we need to make a few changes to its configuration files.

We will start off by editing the /etc/dovecot/conf.d/10-mail.conf file

$ sudo nano /etc/dovecot/conf.d/10-mail.conf

Locate the mail_location parameter :

mail_location = mbox:~/mail:INBOX=/var/mail/%u

Then, change its value as shown below :

mail_location = maildir:/var/mail/vhosts/%d/%n

Save and close the file

In a nutshell, we have instructed Dovecot to look for mails on the /var/mail/vhosts directory.

So for Dovecot to work, we need to create a sub-directory for each domain we intend to use with our email server.

Step 9: Creating Dovecot vhosts Directory

Let’s create the vhosts directory first:

$ sudo mkdir /var/mail/vhosts

Then we can go ahead and create the sub-directory for our domain name.

$ sudo mkdir /var/mail/vhosts/example.com

If you have multiple domains, repeat the command above while replacing the last part of the directory name(example.com) with the name of each domain.

Step 10: Creating Dovecot vmail User and Group

The next step is creating a vmail user and a group. We will also assign the user access to the vhosts directories that we created above.

First, let’s create the group:

$ sudo groupadd -g 5000 vmail

Next, we can create a vmail user and the same to the group that we have created above:

$ sudo useradd -r -g vmail -u 5000 vmail -d /var/mail/vhosts -c "virtual mail user"

Then, we need to assign the ownership of the directories to the vmail user.

$ sudo chown -R vmail:vmail /var/mail/vhosts/

Step 11: Enabling Dovecot for Secure POP3 and IMAP Services

Next we are going to edit the /etc/dovecot/conf.d/10-master.conf file and enable IMAPs and POP3 secure services.

First, open the file

$ sudo nano /etc/dovecot/conf.d/10-master.conf

Then, find the entries below.

inet_listener imaps {
    #port = 993
    #ssl = yes
  }

Change them to:

inet_listener imaps {

    port = 993
    ssl = yes
  }

On the same file, locate the below content.

inet_listener pop3s {

    #port = 995
    #ssl = yes

  }

Change the above to:

inet_listener pop3s {

    port = 995
    ssl = yes

  }

Step 12: Setting up Dovecot LMTP service

On the same file, we need to enable the LMTP service.

Find the entries below:

service lmtp {

unix_listener lmtp {

#mode = 0666

}

And change them to:

service lmtp {

unix_listener /var/spool/postfix/private/dovecot-lmtp {

mode = 0600
user = postfix
group = postfix

 }

Don’t close the file yet.

Step 13: Configuring Dovecot Authentication Socket

We need to configure the authentication socket. So, locate:

service  auth {

...

  # Postfix smtp-auth

  #unix_listener /var/spool/postfix/private/auth {

  #  mode = 0666

  #}



}

And change the above entries to:

service auth {

...

#Postfix smtp-auth

unix_listener /var/spool/postfix/private/auth {

mode = 0666

user=postfix

group=postfix

}

...

Save and close the file when done.

Step 14: Setting Up Dovecot Authentication Process

Next, we will set up Dovecot authentication process by editing the /etc/dovecot/conf.d/10-auth.conf file:

$ sudo nano /etc/dovecot/conf.d/10-auth.conf

Find the entry below.

# disable_plaintext_auth = yes

Then, uncomment it by removing the leading pound symbol

disable_plaintext_auth = yes

Also, we have to change the authentication mechanism from plain to plain login.

On the same file, find the below entry.

auth_mechanisms = plain

And change it to:

auth_mechanisms = plain login

We can now set up the users and passwords configurations.

We will use a password file since it is a flexible method for configuring new users.

First,  disable the default Dovecot behavior for authenticating users using system information.

Locate the line:

!include auth-system.conf.ext

And comment it by adding a pound symbol(#) at the beginning :

#!include auth-system.conf.ext

Then, enable password file configuration. Locate the entry below.

#!include auth-passwdfile.conf.ext

Then, change it to:

!include auth-passwdfile.conf.ext

Save and close the file

Next we will edit the /etc/dovecot/conf.d/auth-passwdfile.conf.ext file.

$ sudo nano /etc/dovecot/conf.d/auth-passwdfile.conf.ext

Make sure the file looks as shown below:

passdb {

  driver = passwd-file

  args = scheme=PLAIN username_format=%u /etc/dovecot/dovecot-users

}



userdb {

driver = static

args = uid=vmail gid=vmail home=/var/mail/vhosts/%d/%n



 # Default fields that can be overridden by passwd-file

 #default_fields = quota_rule=*:storage=1G

 # Override fields from passwd-file

 #override_fields = home=/home/virtual/%u

}

Save and close the file.

Step 15: Creating Dovecot Password File

The next step is creating a password file for each user that we intend to assign an email account.

$ sudo nano /etc/dovecot/dovecot-users

Users must be added using the format user@domainname.com  followed by the password.

Example:

admin@example.com:{plain}Mis25sOpdsio

You can add as many users as you want and finally save and close the file.

In order for Dovecot to work with SSL for security reasons, we will make a few changes to the /etc/dovecot/conf.d/10-ssl.conf file

$ sudo nano /etc/dovecot/conf.d/10-ssl.conf

Change the ssl parameter value from no to required.

Locate:

ssl = no

And change it to:

ssl = required

Step 16: Configuring Dovecot to Use Let’s Encrypt Certificate

The next step is to point the Let’s encrypt certificate files that were generated earlier to Dovecot.

Find the below two lines on the /etc/dovecot/conf.d/10-ssl.conf file:

#ssl_cert = </etc/dovecot/dovecot.pem

#ssl_key = </etc/dovecot/private/dovecot.pem

Then change their values to:

ssl_cert = </etc/letsencrypt/live/example.com/fullchain.pem

ssl_key = </etc/letsencrypt/live/example.com/privkey.pem

Save and close the file and restart Apache, Postfix and Dovecot for the changes to be effected:

$ sudo service apache2 restart 
$ sudo service postfix restart 
$ sudo service dovecot restart

Step 17: Installing Roundcube

The next step is installing Roundcube email client on Ubuntu 18.04 server.

Since the Roundcube package is available on the Ubuntu software repository, we are going to run the command below to install it:

$ sudo apt-get install roundcube

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

Next, add Roundcube path to the default SSL configuration file:

$ sudo nano /etc/apache2/sites-enabled/000-default-le-ssl.conf

Add Alias /mail /usr/share/roundcube below ServerAlias www.example.com

Alias /mail /usr/share/roundcube

Save and close the file. Then, restart Apache for the changes to take effect

$ sudo service apache2 restart

Step 18: Testing the Configuration

To test the configuration, visit www.example.com/mail on your server and replace example.com with your domain name.

You should see a page similar to the one shown below.

Enter the username and password that you created on the Dovecot password file to login. On the server field, enter ‘localhost’.

If you have reached this step, congratulations! From this point forward, you can start sending and receiving emails right from the Roudcube dashboard as shown below.

To check Postfix error log file, run the command below.

$ sudo tail -f /var/log/syslog | grep postfix

You can also telnet Gmail servers to make sure outbound traffic from port 25 is allowed from your VPS provider.

$ telnet alt4.gmail-smtp-in.l.google.com 25

In case the port is blocked, contact your VPS provider and ask them to enable it.

They will be more than happy to assist you provided you won’t send SPAM emails from the server.

Conclusion

In this guide, we have shown you how to configure an email server with Postfix, Dovecot, and Roundcube on Ubuntu 18.04 VPS.

We hope you enjoyed the guide. To test out configuring email with Postfix, Dovecot, and Roundcube, Sign up with Digital Ocean today and enjoy up to $100 worth of free cloud credit!

How to Set up Multiple Websites on Ubuntu 18.04 VPS with Apache

Apache is one of the best open-source web servers that run millions of sites on the web. It is very fast, secure, flexible, and takes minutes to set up on a Linux server e.g., Ubuntu 18.04.

If you have purchased a VPS plan, there are high chances that you want to run multiple websites on your server. This will reduce additional costs which you would have incurred to deploy additional VPS machines.

Running multiple websites is possible on a single Ubuntu 18.04 server because Apache comes with a feature known as Virtual hosts that allows you to configure unlimited websites on a single server.

In this guide, we will take you through the steps of configuring multiple websites on your Ubuntu 18.04 with Apache.

Prerequisites

To follow along with the guide, you will require the following:

  1. A VPS account. Sign up with Digital Ocean today and get up to $100 worth of free credit to test this article and other cloud products.
  2. A non-root user account that can perform sudo tasks on your VPS server.

For better clarification, we will set up two domains: example.com and example.net on the server.

Step 1: Installing Apache Web Server

The first step is installing the web server. We will use Ubuntu apt manager to install Apache.

First,  update the package information index:

$ sudo apt-get update

Then, install Apache

$ sudo apt-get install apache2

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

Once installed, you can enter the IP address associated with your VPS machine on a web browser like Google Chrome. If the Apache installation was completed successfully, you should see a page similar to the one shown below:

Step 2: Creating the First Virtual Host on Ubuntu 18.04 Server

Apache creates a virtual host when installed for the first time. The configuration file can be located at /etc/apache2/sites-available/000-default.conf. To avoid any conflicts, we need to  disable the virtual host by running the command below.

$ sudo a2dissite 000-default.conf

Next, create a virtual host configuration for the example.com domain.

$ sudo mkdir -p /var/www/example.com/public_html

Next,  we will change the ownership of the directory that we have created above.

$ sudo chown -R $USER:$USER /var/www/example.com/public_html

Then,  issue the right file permissions to the directory.

$ sudo chmod -R 755 /var/www/example.com/public_html

Also, to ensure that newly created files and directories inherit the permissions we  created above, we are going to run the command below:

$ sudo find /var/www/example.com/public_html -type d -exec chmod g+s {} \;

Once we have the directory for the example.com website in place, we can create a new virtual host configuration file and reference to that directory:

$ sudo nano /etc/apache2/sites-available/example.com.conf

Paste the information below:

<VirtualHost *:80>

    ServerAdmin admin@example.com
    ServerName example.com
    ServerAlias www.example.com
    DocumentRoot /var/www/example.com/public_html
    ErrorLog ${APACHE_LOG_DIR}/error.log
    CustomLog ${APACHE_LOG_DIR}/access.log combined

<Directory /var/www/example.com/public_html>

Options -Indexes +FollowSymLinks -MultiViews
AllowOverride All          
Require all granted

</Directory>

</VirtualHost>

Next, enable the example.com virtual host by running the command below.

$ sudo a2ensite example.com.conf

We can create a sample home page for the example.com website. So, create the file using nano text editor:

$ sudo nano /var/www/example.com/public_html/index.html

Paste the information below in the file.

<html>
  <head>
    <title>Site 1</title>
  </head>
  <body>
    <h1>This is a sample page for example.com website</h1>
  </body>
</html>

Step 3: Creating the Second Virtual Host on Ubuntu 18.04 Server

Just like we have done for the example.com website, we need to create a directory for the example.net virtual host on Apache.

$ sudo mkdir -p /var/www/example.net/public_html

Then we need to change the file ownership and associate them to the currently logged in user.

$ sudo chown -R $USER:$USER /var/www/example.net/public_html

Then, run the command below to issue the right permissions.

$ sudo chmod -R 755 /var/www/example.net/public_html

Make sure that files created under the directory inherit permissions from the parent directory.

$ sudo find /var/www/example.net/public_html -type d -exec chmod g+s {} \;

Create a virtual host file for the example.net website.

$ sudo nano /etc/apache2/sites-available/example.net.conf

Then, paste the information below.

<VirtualHost *:80>
    ServerAdmin admin@example.net
    ServerName example.net
    ServerAlias www.example.net
    DocumentRoot /var/www/example.net/public_html
    ErrorLog ${APACHE_LOG_DIR}/error.log
    CustomLog ${APACHE_LOG_DIR}/access.log combined

<Directory /var/www/example.net/public_html>

Options -Indexes +FollowSymLinks -MultiViews
AllowOverride All           
Require all granted

</Directory>

</VirtualHost>

Close and save the file by pressing CTRL+X, Y and hit Enter.

Next, enable the example.net virtual host by running the command below:

$ sudo a2ensite example.net.conf

Next, create a sample home page for the example.net website.

$ sudo nano /var/www/example.net/public_html/index.html

Enter the information below;

<html>

  <head>

    <title>Site 2</title>

  </head>

  <body>

    <h1>This is a sample page for example.net website</h1>

  </body>

</html>

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

Step 3: Restart Apache and Test the Configuration

In order for Apache to load the settings for the newly created virtual hosts, it must be restarted.

$ sudo systemctl restart apache2

Then, on your local computer, add the example.com and example.net domain names on the hosts file and point them to the public IP address of your VPS machine and save the file.

If you are running Windows, edit the file c:\Windows\System32\Drivers\etc\hosts and save it. Remember to replace 192.88.99.0 with the IP address associated with your VPS machine:

# Copyright (c) 1993-2009 Microsoft Corp.

...

# localhost name resolution is handled within DNS itself.

#             127.0.0.1       localhost

#             ::1             localhost

192.88.99.1 example.net

192.88.99.1 example.com

..

Then, save the file and visit the example.com on your browser. You should see a page similar to the one below:

Next, visit example.net on your browser to test the 2nd virtual host. Your browser should display the page shown below.

Conclusion

That’s all when it comes to hosting multiple sites on your Ubuntu 16.04 server with Apache. A virtual host is a great feature that you can leverage to reduce cost of ownership when you wish to run multiple sites on a single VPS machine.

Remember, you can configure as many websites as you want provided your VPS plan can handle the disk space, memory, and bandwidth.

The virtual hosts configuration should work pretty well on any Digital Ocean server.  Sign up with Digitial Ocean today and get $100 worth of free trial credit.

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.

How to Setup WordPress Website with Apache, MySQL and PHP on Ubuntu 18.04 VPS

Since May 2003, WordPress has remained the most popular, stable, secure and easy to use Content Management System (CMS) for setting up websites.

The free Open-source software is fully built-in and comes with thousands of beautiful themes and plugins for extended functionalities.

WordPress WYSIWYG interface is browser-based and does not require complicated FTP or HTML coding software applications to launch a website.

Due to its clean and simple code, the CMS is loved by all major search engines because it is easier to index.

WordPress is based on PHP and MySQL and runs on most popular web servers including Apache and Nginx.

On a shared hosting, WordPress can be installed by automatic script installers such as Softaculous.

However, the installation method on a VPS hosting  takes a new approach and can be done with just a few and simple shell commands.

This is a step-by-step guide on setting up a WordPress website with  Apache, MySQL and PHP on Ubuntu 18.04 VPS.

Prerequisites

  • A VPS account running Ubuntu 18.04 Operating System. Sign up with Digital Ocean and get free trial credit worth $100 to configure this article and test other cloud products.
  • A domain name (e.g. example.com). You can buy a domain name from a registrar of your choice(e.g. Namecheap).
  • A non-root user that can perform sudo tasks for your Ubuntu 18.04 Server.

Step 1: Point your Domain Name DNS records to your VPS

The first step is to point the DNS records of your domain name to your VPS . When visitors enter your domain name on a browser, they should be routed to the IP address associated with your VPS account.

You should ask your VPS provider about the correct DNS records to use if you are in doubt.

For instance, if you are running a VPS from Vultr, the DNS records look like these:

  • ns1.vultr.com
  • ns2.vultr.com

Step 2: Connecting to Your VPS Server

SSH to your Ubuntu 18.04 server using the public IP address (e.g. 198.18.0.22) associated with your VPS instance.  You can do this via the Command Line Interface (CLI) that ships with Linux or Mac. If you are running Windows on your local computer, consider downloading PuTTY SSH client.

Step 3: Installing Apache Web Server

The first software we are going to install is Apache. It’s an Open source web server application that runs the world’s busiest sites. Apache can handle large traffic and has lots of built-in security features and expandable modules.

To install Apache, first update the package information list on your Ubuntu server using the command below:

$ sudo apt-get update

Then, pull Apache from Ubuntu’s software repository using the command below:

$ sudo apt-get install apache2 php libapache2-mod-php

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

Step 4: Initializing WordPress Website Directory Structure

Apache has a special feature called Virtual Hosts. This advanced feature allows you to host unlimited websites on a single Linux machine.

Before we create the Virtual Host configuration file, we must initialize the directory for holding our website files.

We have to create the directory under the /var/www/ folder using the command below:

$ sudo mkdir -p /var/www/example.com/public_html

Step 5: Creating a Virtual Host for the WordPress Website

When you install Apache, a default Virtual host (000-default.conf) is created under the /etc/apache2/sites-available directory. To keep things simple, we will create our own virtual host to run our WordPress site.

Please note, virtual hosts configuration files must end with a ‘.conf’ extension. So let’s create the configuration file for our domain name www.example.com

$ sudo nano /etc/apache2/sites-available/example.com.conf

Then, copy and paste the below content on that file:

<VirtualHost *:80>

    ServerAdmin admin@example.com

    ServerName example.com

    ServerAlias www.example.com

    DocumentRoot /var/www/example.com/public_html

    ErrorLog ${APACHE_LOG_DIR}/error.log

    CustomLog ${APACHE_LOG_DIR}/access.log combined

  <Directory /var/www/example.com/public_html>

     Options -Indexes +FollowSymLinks -MultiViews

     AllowOverride All

     Require all granted

  </Directory>

</VirtualHost>

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

We need to disable the default virtual host and enable the one we have just created, run the commands below:

$ sudo a2dissite 000-default.conf
$ sudo a2ensite example.com.conf

Restart Apache web server

$ sudo systemctl restart apache2

Step 6: Installing MySQL Database Server

WordPress relies on MySQL to store data. Apart from photos, plugins, and themes, post and web pages are stored in the MySQL relational database.

Like WordPress and Apache, MySQL is free and Open-source. It is also available on the Ubuntu’s software repository and we can install it using the apt command:

$ sudo apt-get install mysql-server

Press Y and hit Enter when prompted to confirm the installation

Step 7: Securing MySQL Server

MySQL installation is not secure by default. It comes with some test databases and other settings that we must disable. To do this, run the command below:

$ sudo mysql_secure_installation

You will get a prompt to answer multiple questions. We have prepared the correct responses, just follow the settings below:

Setup 'validate password' plugin? [Y/N] Y
Password Validation Policy Level: 2
Root Password: PASSWORD
Re-enter password: REPEAT PASSWORD
Continue with the password provided? Y
Remove anonymous users? [Y/N] Y
Disallow root login remotely? [Y/N] Y
Remove test database and access to it? [Y/N] Y
Reload privilege tables now? [Y/N] Y

If all goes well, you will get a success message.

Step 8: Creating a WordPress Database and User

Next, we are going to create a database and username for our WordPress software. First, log in to the MySQL command line interface using the command below:

$ sudo mysql -u root -p

Enter the root password of your MySQL server when prompted and hit Enter.

Then, on the command prompt that appears, enter the below SQL command to create a database:

mysql> Create database wordpress DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

To create a database user, run the command below:

mysql>Create user  'wp_user'@'localhost' IDENTIFIED BY 'PASSWORD';

Then, assign all privileges to the ‘wordpress’ database to the user we have created above using the command below:

mysql> Grant all privileges on wordpress.* to  'wp_user'@'localhost';

Flush privileges for the changes to take effect:

mysql> Flush privileges;

Exit from the MySQL command line interface:

mysql> Exit;

Step 9: Installing PHP on Ubuntu 18.04 Server

WordPress is written in PHP language and we must install the software on our Ubuntu 18.04 server in order for the application to function.

We are also going to install all the required PHP modules that WordPress require.

We can install all the software and modules in one line using the command below:

$ sudo apt-get install php php-cli php-common php-mbstring php-gd php-intl php-xml php-mysql php-zip php-curl php-xmlrpc

Restart Apache web server for the changes to take effect:

$ sudo systemctl restart apache2

Step 10: Downloading and Installing WordPress on Ubuntu 18.04 VPS

We now have a web server, a database for our WordPress software and PHP scripting language. We can now go ahead and download WordPress.

First, cd to the ‘tmp’ directory:

$ cd /tmp

Then, grab the latest WordPress version using wget command:

$ wget -c http://wordpress.org/latest.tar.gz

To unzip the WordPress installation files to the root directory of the virtual host that we created earlier, run the commands below:

$ sudo tar -xzvf latest.tar.gz
$ sudo cp -r wordpress/.  /var/www/example.com/public_html/

Apache runs under the www-data user, we need to grant the web server full permissions to control our website’s file. Run the command below:

$ sudo chown -R www-data:www-data /var/www/example.com/public_html

Then, we can set the appropriate permissions to ensure the outside world has read and execute permissions only:

$ sudo chmod -R 755 /var/www/example.com/public_html

To make sure that newly created files and folders inherit the correct permissions, we can run the command below:

$ sudo find /var/www/example.com/public_html -type d -exec chmod g+s {} \;

Step 11: Finalizing WordPress Installation on Ubuntu 18.04 Server

We now have WordPress installation files on the root of our website. However, we need to configure the database settings on the WordPress configuration files.

So type the command below to copy wp-config.php file from the default wp-config-sample.php file:

$ sudo cp /var/www/example.com/public_html/wp-config-sample.php /var/www/example.com/public_html/wp-config.php

Then, open the new configuration file that we have copied using a nano editor:

$ sudo nano /var/www/example.com/public_html/wp-config.php

Look for the values:

define('DB_NAME', 'database_name_here');
/** MySQL database username */
define('DB_USER', 'username_here');
/** MySQL database password */
define('DB_PASSWORD', 'password_here');

And change them to:

define('DB_NAME', 'wordpress');
/** MySQL database username */
define('DB_USER', 'wp_user');
/** MySQL database password */
define('DB_PASSWORD', 'PASSWORD');

Remember to replace PASSWORD with the correct value.

Before you close the file add the line below at the top:

<?php

And the line below at the bottom

?>

If you don’t add the two lines, PHP will not be able to parse the configuration file.

On a browser, enter your domain to finalize WordPress installation. If you followed all the steps, you should see the below web page:

Configuring WordPress with Apache, MySQL and PHP on Ubuntu 18.04 VPS

Congratulations! Your WordPress site is now installed. Just follow the wizard to complete the final settings.

Finally, you can create new posts, add a new web page or probably change the WordPress theme to match the look and feel of your company brand.

Conclusion

We have shown you how to install WordPress with Apache, MySQL, and PHP on Ubuntu 18.04 server.

Remember, you can run as many WordPress sites as required on your VPS plan by leveraging the power of Apache Virtual Host feature.

Sign up with Digital Ocean today and enjoy up to $100 worth of free trial credit to test this article and other cloud products.

How to Install and Secure MySQL on Ubuntu 16.04 VPS Server

MySQL is one of the most popular database systems that utilize Structured Query Language (SQL). The relational database is commonly used alongside Apache/LightSpeed/Ngnix web servers and PHP/Perl/Python scripting languages.

Due to its stability, MySQL is used by many developers in production. Popular uses include WordPress, Joomla and Drupal content management systems. MySql is also used in major websites including Facebook, Twitter, YouTube and Google.

The database management system is very secure, scalable and performs well especially in an environment that demands the use of transactions. MySql’s complete workflow and reduced total cost of ownership gives developers the flexibility of using it.

MySQL works pretty well in an Ubuntu 16.04 Virtual Private Server and here is how to install it.

Prerequisites

  • An Ubuntu 16.04 VPS server from Digital OceanVultr or Linode.
  • A command line tool like putty.
  • A non-root username with sudo rights.

Step 1: Downloading MySQL from the Ubuntu Package Repository

MySQL is maintained in the Ubuntu central repository and we can pull it using the apt-get command. But before we do this, we need to make sure that our Ubuntu 16.04 server is up to date.

Run the command below to update your Ubuntu server

sudo apt-get update

Then install MySQL server by running this command.

sudo apt-get install mysql-server

Press Y then Enter when you are promoted to install the package.

Step 2: Setting the MySQL Server root Password

MySQL server has a default user called root and should not be confused with the root of your Linux system. Once you complete the MySQL installation, you will be requested to supply a password for the root user.

Kindly use a strong password for security purposes. A mix of upper and lower case characters combined with numbers and special characters form a hard-to-crack password. Press Enter after entering the password and confirm the same on the next screen

Step 3: Securing the MySQL Server

The default MySql installation is not secure because it contains anonymous users and a test database. So we need to harden the installation to avoid somebody from login on the system with the default settings.

Luckily, we can run a command that will make things easier for us. Just type the following on your command line tool:

sudo mysql_secure_installation

The command will run and you will be prompted to enter the MySQL root password that you created in Step 2 above.

Password Validation Plug-in

MySql secure installation will prompt you to setup a Validate Password Plug-in which is used for testing MySQL passwords to improve security.

The command will read as follows

Would you like to setup VALIDATE PASSWORD plugin?

Press Y and Enter to enable the Plug-in

The Validation Plug-in has three levels of password validation policy

LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

You will get a prompt to choose your desired policy which reads as follows

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:

Just enter 2 and press Enter. This will make sure that passwords created for any MySQL database user will contain a mix of numeric, mixed case and special characters.

Changing Password for the root User

Next, you will get a prompt to change the password for the MySQL root user.

The prompt will read as follows:

Change the password for root ? ((Press y|Y for Yes, any other key for No) :

Since we chose a strong password while installing MySQL in the above steps, we are not going to change the password. So just press N and Enter to proceed.

Removing Anonymous Users

Leaving any anonymous users on your MySQL database is very dangerous. You will get a prompt to remove the user.

The prompt will read as the below text:

Remove anonymous users? (Press y|Y for Yes, any other key for No) :

Press Y and then Enter to remove the user

Disallowing Remote root Login

MySQL remote root login leaves an open door for hackers who can recursively connect from any IP address to your MySQL server and probably brute-force your password.

To disable this, you will get a prompt which reads:

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y

Press Y and then Enter to disable root login remotely

Deleting the MySQL default test Database

Fresh MySQL installation comes with a test database named ‘test’ that can be used by anyone. This is intended for testing and should be removed in a production environment.

You will get a prompt to remove the database which reads as follows:

Remove test database and access to it? (Press y|Y for Yes, any other key for No) :

Press Y and then Enter to remove the database

Reloading MySQL Server Privileges

The changes we have made so far have not yet been committed to the system and MySQL privileges need to be reloaded.

The prompt for refreshing the settings will read:

Reload privilege tables now? (Press y|Y for Yes, any other key for No) :

Press Y and then Enter for the changes to be effected. Finally, you will see a success message.

Success.

All done!

Step 4: Disabling Strict SQL Mode in MySQL 5.7

The default MySQL strict mode can bring problems when you try to insert data in a table. If a field is null and there is no default value defined, the transaction will fail.

Let’s create a new MySQL configuration file and make the changes using a nano text editor.

sudo nano /etc/mysql/conf.d/disable_strict_mode.cnf

Once the text editor opens, enter the following text

[mysqld]
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Press CTRL + X then Y and Enter to save the changes

You need to restart the MySQL server for the changes to be effected by entering the command below on your terminal.

sudo service mysql restart

Step 5: Checking the Status of MySQL server

The MySQL server installation on our Ubuntu 16.04 VPS server is complete but we can test whether everything is working by running the below command.

sudo systemctl status mysql.service

If there were no hiccups with the MySQL installation, you should see the following status text:

Conclusion

That is all it takes to Install MySQL server on your Ubuntu VPS. From this point, you can create MySQL databases, tables and users using the command line. However, if you want a better tool to manage your MySQL database from a web browser, you should consider installing phpMyadmin. See my guide for installing phpMyadmin on Ubuntu server 16.04.