Installing MySQL on Ubuntu 18.04

Installing MySQL Server on Ubuntu 18.04

Last updated on | 13 replies

In this guide we will install and configure MySQL on Ubuntu Server 18.04 (Bionic Beaver). MySQL is the world’s most popular open source database, enabling the cost-effective delivery of reliable, high-performance and scalable Web-based database applications.

Prerequisites

You should use a non-root user account as explained in the Ubuntu 18.04 Initial Server Setup.

1. Install MySQL

Let’s begin by updating the package lists and installing MySQL on Ubuntu 18.04. Below we have two commands separated by &&. The first command will update the package lists to ensure you get the latest version and dependencies for MySQL. The second command will then download and install MySQL.

sudo apt update && sudo apt install mysql-server

Press y and ENTER when prompted to install the MySQL package.

Once the package installer has finished, we can check to see if the MySQL service is running.

sudo service mysql status

If running, you will see a green Active status like below.

● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2018-04-02 02:40:59 CEST; 2min 47s ago
Main PID: 18476 (mysqld)
Tasks: 27 (limit: 4915)
CGroup: /system.slice/mysql.service
└─18476 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

Apr 02 02:40:59 ubuntu1804 systemd[1]: Starting MySQL Community Server...
Apr 02 02:40:59 ubuntu1804 systemd[1]: Started MySQL Community Server.

You may need to press q to exit the service status.

2. Configure Security

You should now run mysql_secure_installation to configure security for your MySQL server.

sudo mysql_secure_installation

If you created a root password in Step 1, you may be prompted to enter it here. Otherwise you will be asked to create one. (Generate a password)

You will be asked if you want to set up the Validate Password Plugin. It’s not really necessary unless you want to enforce strict password policies for some reason.

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

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:

Press ENTER here if you don’t want to set up the validate password plugin.

Please set the password for root here.

New password:

Re-enter new password:

If you didn’t create a root password in Step 1, you must now create one here.

Generate a strong password and enter it. Note that when you enter passwords in Linux, nothing will show as you are typing (no stars or dots).

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) :

Press y and ENTER to remove anonymous users.

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) :

Press y and ENTER to disallow root login remotely. This will prevent bots and hackers from trying to guess the root password.

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) :

Press y and ENTER to remove the test database.

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) :

Press y and ENTER to reload the privilege tables.

All done!

As a test, you can log into the MySQL server and run the version command.

sudo mysqladmin -p -u root version

Enter the MySQL root password you created earlier and you should see the following:

mysqladmin Ver 8.42 Distrib 5.7.21, for Linux on x86_64
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.

Server version 5.7.21-1ubuntu1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 2 hours 34 min 19 sec

Threads: 1 Questions: 15 Slow queries: 0 Opens: 113 Flush tables: 1 Open tables: 106 Queries per second avg: 0.001

You have now successfully installed and configured MySQL for Ubuntu 18.04!

What Next?

Now that your MySQL server is up and running, you might want to install phpMyAdmin, which allows you to easily manage your MySQL users and databases through a browser interface. Before installing phpMyAdmin, you must have Apache/Nginx and PHP installed first. Please refer to the following guides:

Apache

Nginx

Let me know if this helped. Follow me on Twitter, Facebook and YouTube, or 🍊 buy me a smoothie.

13 replies

Leave a reply

Your email address will not be published. Required fields are marked *

  1. Not sure if this is the right place to ask this, but how do I allow multiple sites to access the MySQL database? I have 3 sites hosted on my server, but can’t get any of them to create a new database from a install script (php). I have Nginx+MySQL+PHP+PHPmyAdmin all set up, but am at a loss as how to get each one to use the MySQL.

    I’ve tried Googling it, but all I ever get is how to install each one. Nothing about allowing multiple access.

    Reply

    1. If MySQL is on the same server as your sites, then it is just localhost. There is no special setup.

      Are you able to connect to port 3306?

      https://devanswe.rs/cant-connect-mysql-server-remotely/

      If you can, I would create a test database and user in phpMyAdmin, then do a test connection in PHP to see if you can connect.

      function db_connect($host,$user,$pass,$db) {

   $mysqli = new mysqli($host, $user, $pass, $db);

   $mysqli->set_charset("utf8");

   if($mysqli->connect_error) 
     die('Connect Error (' . mysqli_connect_errno() . ') '. mysqli_connect_error());

   return $mysqli;
}

$mysqli = db_connect('localhost','username','password','database');

      Replace username, password and database with your own details.

      https://devanswe.rs/database-connection-php-mysqli-example/

      Reply

      1. Thanks for getting back. I created the test DB, and created a php file with the test and ran it. Got a blank page, so I’m assuming it was successful.

        I feel like an idiot right now…cause the reason the install script wasn’t working (wait for it………) was because I forgot to create the database in PHPmyAdmin first. DOH! LMAO

        Thanks for your help. 🙂

        Reply

  2. It didn’t work for me, after the sudo mysql_secure_installation command, and supplying a root password, the computer responded … Failed! Error: Table ‘mysql.role_edges’ doesn’t exist. What went wrong?

    Reply

      1. Thanks for your reaction, after running the command you proposed, the computer responds:
        Checking if update is needed.
        Checking server version.
        Running queries to upgrade MySQL server.
        mysql_upgrade: [ERROR] 3009: Column count of mysql.user is wrong. Expected 49, found 45. Created with MySQL 80011, now running 80012. Please use mysql_upgrade to fix this error.

        Reply

        1. It sounds like the db might be corrupted.

          Try running

          sudo mysql_upgrade --force root -p

          If that fails, I would recommend removing MySQL completely and starting from scratch. These commands will wipe any databases you have so run at your own risk.

          sudo service mysql stop
sudo killall -9 mysql
sudo apt-get remove --purge mysql-server mysql-client mysql-common
sudo apt-get autoremove
sudo apt-get autoclean
sudo deluser -f mysql
sudo rm -rf /var/lib/mysql
sudo apt-get purge mysql-server-core-5.7
sudo apt-get purge mysql-client-core-5.7
sudo rm -rf /var/log/mysql
sudo rm -rf /etc/mysql

          Then follow this guide again to install MySQL.

          Reply

          1. Yesss, now it is fixed thank you!
            btw: the command was sudo mysql_upgrade –force root -p (so without minus sign before the word root)