How To Use the MySQL One-Click Install Image for Ubuntu 16.04
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 DayThank 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://126.96.36.199/phpmyadmin Username: admin Passowrd: 73b7c69f2f08bae8998e1f84bfc4a171334b5b4cc679bf4a The details of your PHP installation can be seen at http://188.8.131.52/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
Before you run MySQL's security script, you'll need the MySQL root password. Print the contents of
- 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.
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.
OutputPackage: mysql-server Status: install ok installed Priority: optional Section: database Installed-Size: 180 Maintainer: Ubuntu Developers <firstname.lastname@example.org> 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:
Outputmysqladmin 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.
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.