Introduction

MySQL is an open-source database management system, commonly installed as part of the popular LAMP (Linux, Apache, MySQL, PHP/Python/Perl) stack. It uses a relational database and SQL (Structured Query Language) to manage its data.

MySQL can be used to store, secure, and retrieve data for many kinds of applications, including data warehousing, e-commerce, and logging applications. It is most often associated with web-based applications and online publishing.

DigitalOcean's MySQL One-Click Application image quickly deploys a Ubuntu server with MySQL already installed, as well as phpMyAdmin to help manage your database server. This image will install the latest version of MySQL included in the APT package repository by default. If you need a specific version of MySQL, you can follow our How To install the latest version of MySQL guide.

Step 1 — Creating a MySQL Droplet

To create a MySQL Droplet, start on the Droplet creation page. In the Choose an image section, click the One-click apps tab and select the MySQL on 16.04 image.

Next, select a size for your Droplet, your desired region, and any additional settings (like private networking IPv6 support, or backups). Add any SSH keys and fill in a hostname for your Droplet. When you're ready, click Create Droplet to spin up the server.

After your Droplet has been created, let's connect to it as root via SSH to run MySQL's included security script.

Step 2 — Securing MySQL

Log in to your Droplet as root via SSH. If you haven't used SSH or PuTTY before, you can read this How To Connect To Your Droplet with SSH tutorial. Make sure to replace your_server_ip with your Droplet's IP address, which can be found in the Droplets tab.

ssh root@your_server_ip

If you are prompted for a password, enter the password that was emailed to you when the Droplet was created and follow the on-screen prompts to replace your temporary password. If you set up the Droplet with SSH keys, the keys will be used for authentication instead.

Once you log in, you'll see the Message of the Day. This contains the username and password for phpMyAdmin, the location of the MySQL root user password, and information on open application ports configured in the firewall.

Message of the Day
Thank you for using DigitalOcean's MySQL 1-Click.

For your convience, phpMyAdmin has been installed to help you manage your
Database server. You may connect to it via:
    URL:      http://159.203.124.192/phpmyadmin
    Username: admin
    Passowrd: 73b7c69f2f08bae8998e1f84bfc4a171334b5b4cc679bf4a

The details of your PHP installation can be seen at http://159.203.124.192/info.php

Passwords have been saved to /root/.digitalocean_password

You are encouraged to run mysql_secure_installation to ready your server for
production.

The UFW firewall is enabled. All ports execpt for SSH, HTTP/HTTPS and MySQL
are blocked by default.

-------------------------------------------------------------------------------
To delete this message of the day: rm -rf /etc/update-motd.d/99-one-click

The programs included with the Ubuntu system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Ubuntu comes with ABSOLUTELY NO WARRANTY, to the extent permitted by
applicable law.

If you need to refer back to this later, you can find the information in the file /etc/update-motd.d/99-one-click.

Before you run MySQL's security script, you'll need the MySQL root password. Print the contents of /root/.digitalocean_password.

cat /root/.digitalocean_password

You'll see three lines, like this:

root_mysql_pass="7fea9fc2a0173be16c1c5e23d0c81cc2edd134353fa9e967"
admin_mysql_pass="73b7c69f2f08bae8998e1f84bfc4a171334b5b4cc679bf4a"
app_mysql_pass="dff4eb0c526a0110ebc5b5ce631da53c5dba5999c6e374cc"

Copy the root MySQL password. Next, run the mysql_secure_installation script to prepare your server for production. This will guide you through some procedures that will remove some defaults that are dangerous to use in a production environment.

mysql_secure_installation

This will prompt you for the root password that was set during installation, which is saved in /root/.digitalocean_password/. You can press Y and then ENTER to accept the defaults for all subsequent questions. For a more detailed walkthrough of these options, you can see this step of the LAMP installation tutorial.

Now that MySQL is installed and secured, let's test that it works.

Step 3 — Testing MySQL

The first check you can do is to see which version of MySQL is installed.

dpkg -s mysql-server

You'll see output like this, which includes the version of the package and other information.

Output
Package: mysql-server
Status: install ok installed
Priority: optional
Section: database
Installed-Size: 180
Maintainer: Ubuntu Developers <ubuntu-devel-discuss@lists.ubuntu.com>
Architecture: all
Source: mysql-5.7
Version: 5.7.18-0ubuntu0.16.04.1
Depends: mysql-server-5.7
. . .

MySQL should have started running automatically, so check the status of the process, too.

systemctl status mysql.service

You'll see output similar to the following:

Output
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en
   Active: active (running) since Wed 2016-11-23 21:21:25 UTC; 30min ago
 Main PID: 3754 (mysqld)
    Tasks: 28
   Memory: 142.3M
      CPU: 1.994s
   CGroup: /system.slice/mysql.service
           └─3754 /usr/sbin/mysqld

If MySQL isn't running, you can start it with sudo systemctl start mysql.

For an additional check, you can try connecting to the database using the mysqladmin tool, which is a client that lets you run administrative commands. For example, this command says to connect to MySQL as root (-u root), prompt for a password (-p), and return the version.

mysqladmin -p -u root version

You'll see output similar to this:

Output
mysqladmin  Ver 8.42 Distrib 5.7.16, for Linux on x86_64
Copyright (c) 2000, 2016, 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.16-0ubuntu0.16.04.1
Protocol version    10
Connection      Localhost via UNIX socket
UNIX socket     /var/run/mysqld/mysqld.sock
Uptime:         30 min 54 sec

Threads: 1  Questions: 12  Slow queries: 0  Opens: 115  Flush tables: 1  Open tables: 34  Queries per second avg: 0.006

This means MySQL is up and running.

Next Steps

With your MySQL one-click image up and running, you can now start working on your project. Here are a few examples of next steps you can take:

• If you're new to MySQL, you can learn more about MySQL commands and then follow our How to Create a Database in MySQL guide to start managing your databases in a SQL environment.
• You can implement some additional security measures to protect your database.
• If you'd like a graphical interface for administering your MySQL server, phpMyAdmin is a popular web-based solution. It already pre-installed in our MySQL One-click image. You can refer to our tutorial How To Secure phpMyAdmin and get started.
• If you want to change where MySQL stores its data, like moving it to a different storage device you can, relocate the data directory.
• For more advanced setups, learn how to manage your MySQL infrastructure with SaltStack, a configuration management system.