Tutorial

How To Install MySQL on Ubuntu 18.04

Updated on March 18, 2022

Manager, Developer Education

English
How To Install MySQL on Ubuntu 18.04
Not using Ubuntu 18.04?Choose a different version or distribution.
Ubuntu 18.04

A previous version of this tutorial was written by Hazel Virdó

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.

The short version of the installation is simple: update your package index, install the mysql-server package, and then run the included security script.

  1. sudo apt update
  2. sudo apt install mysql-server
  3. sudo mysql_secure_installation

This tutorial will explain how to install MySQL version 5.7 on an Ubuntu 18.04 server. However, if you’re looking to update an existing MySQL installation to version 5.7, you can read this MySQL 5.7 update guide instead.

Prerequisites

To follow this tutorial, you will need:

Step 1 — Installing MySQL

On Ubuntu 18.04, only the latest version of MySQL is included in the APT package repository by default. At the time of writing, that’s MySQL 5.7

To install it, update the package index on your server with apt:

  1. sudo apt update

Then install the default package:

  1. sudo apt install mysql-server

Ensure that the server is running using the systemctl start command:

  1. sudo systemctl start mysql.service

These commands will install and start MySQL, but will not prompt you to set a password or make any other configuration changes. Because this leaves your installation of MySQL insecure, we will address this next.

Step 2 — Configuring MySQL

For fresh installations, you’ll want to run the included security script. This changes some of the less secure default options for things like remote root logins and sample users. On older versions of MySQL, you needed to initialize the data directory manually as well, but this is done automatically now.

Run the security script:

  1. sudo mysql_secure_installation

This will take you through a series of prompts where you can make some changes to your MySQL installation’s security options. The first prompt will ask whether you’d like to set up the Validate Password Plugin, which can be used to test the strength of your MySQL password. Regardless of your choice, the next prompt will be to set a password for the MySQL root user. Enter and then confirm a secure password of your choice.

From there, you can press Y and then ENTER to accept the defaults for all the subsequent questions. This will remove some anonymous users and the test database, disable remote root logins, and load these new rules so that MySQL immediately respects the changes you have made.

To initialize the MySQL data directory, you would use mysql_install_db for versions before 5.7.6, and mysqld --initialize for 5.7.6 and later. However, if you installed MySQL from the Debian distribution, as described in Step 1, the data directory was initialized automatically; you don’t have to do anything. If you try running the command anyway, you’ll see the following error:

Output
mysqld: Can't create directory '/var/lib/mysql/' (Errcode: 17 - File exists)
. . .
2018-04-23T13:48:00.572066Z 0 [ERROR] Aborting

Note that even though you’ve set a password for the root MySQL user, this user is not configured to authenticate with a password when connecting to the MySQL shell. If you’d like, you can adjust this setting by following Step 3.

Step 3 — (Optional) Adjusting User Authentication and Privileges

In Ubuntu systems running MySQL 5.7 (and later versions), the root MySQL user is set to authenticate using the auth_socket plugin by default rather than with a password. This allows for some greater security and usability in many cases, but it can also complicate things when you need to allow an external program (e.g., phpMyAdmin) to access the user.

In order to use a password to connect to MySQL as root, you will need to switch its authentication method from auth_socket to mysql_native_password. To do this, open up the MySQL prompt from your terminal:

  1. sudo mysql

Next, check which authentication method each of your MySQL user accounts use with the following command:

  1. SELECT user,authentication_string,plugin,host FROM mysql.user;
Output
+------------------+-------------------------------------------+-----------------------+-----------+ | user | authentication_string | plugin | host | +------------------+-------------------------------------------+-----------------------+-----------+ | root | | auth_socket | localhost | | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost | | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost | | debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost | +------------------+-------------------------------------------+-----------------------+-----------+ 4 rows in set (0.00 sec)

In this example, you can see that the root user does in fact authenticate using the auth_socket plugin. To configure the root account to authenticate with a password, run the following ALTER USER command. Be sure to change password to a strong password of your choosing, and note that this command will change the root password you set in Step 2:

  1. ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Then, run FLUSH PRIVILEGES which tells the server to reload the grant tables and put your new changes into effect:

  1. FLUSH PRIVILEGES;

Check the authentication methods employed by each of your users again to confirm that root no longer authenticates using the auth_socket plugin:

  1. SELECT user,authentication_string,plugin,host FROM mysql.user;
Output
+------------------+-------------------------------------------+-----------------------+-----------+ | user | authentication_string | plugin | host | +------------------+-------------------------------------------+-----------------------+-----------+ | root | *3636DACC8616D997782ADD0839F92C1571D6D78F | mysql_native_password | localhost | | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost | | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost | | debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost | +------------------+-------------------------------------------+-----------------------+-----------+ 4 rows in set (0.00 sec)

You can see in this example output that the root MySQL user now authenticates using a password. Once you confirm this on your own server, you can exit the MySQL shell:

  1. exit

Alternatively, some may find that it better suits their workflow to connect to MySQL with a dedicated user. To create such a user, open up the MySQL shell once again:

  1. sudo mysql

Note: If you have password authentication enabled for root, as described in the preceding paragraphs, you will need to use a different command to access the MySQL shell. The following will run your MySQL client with regular user privileges, and you will only gain administrator privileges within the database by authenticating:

  1. mysql -u root -p

From there, create a new user and give it a strong password:

  1. CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';

Then, grant your new user the appropriate privileges. For example, you could grant the user privileges to all tables within the database, as well as the power to add, change, and remove user privileges, with this command:

  1. GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

Note that, at this point, you do not need to run the FLUSH PRIVILEGES command again. This command is only needed when you modify the grant tables using statements like INSERT, UPDATE, or DELETE. Because you created a new user, instead of modifying an existing one, FLUSH PRIVILEGES is unnecessary here.

Following this, exit the MySQL shell:

  1. exit

Finally, let’s test the MySQL installation.

Step 4 — Testing MySQL

Regardless of how you installed it, MySQL should have started running automatically. To test this, check its status.

  1. 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 2018-04-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.

  1. sudo mysqladmin -p -u root version

You should see output similar to this:

Output
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:			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.

Conclusion

You now have a basic MySQL setup installed on your server. Here are a few examples of next steps you can take:

Want to launch a high-availability MySQL cluster in a few clicks? DigitalOcean offers worry-free MySQL managed database hosting. We’ll handle maintenance and updates and even help you migrate your database from external servers, cloud providers, or self-hosted solutions. Leave the complexity to us, so you can focus on building a great application.

Learn more here

About the authors
Default avatar

Manager, Developer Education

Technical Writer @ DigitalOcean

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
10 Comments
Leave a comment...

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

I followed the instructions on Step 3 and just ended up locking myself of mysql. Something there is not right or should be made more clear as to what is going on.

This comment has been deleted

    I went through the same issue. Followed instructions on Step 3 and ended up locking myself of mysql.

    But then I found what was wrong. The command changes the password to “password”. You need to type “password” as password, to be able to login as root.

    As we have set root’s password in the previous step (Step 2), when running “mysql_secure_installation”, we simply don’t see that we are UNDOING it at Step 3.

    Definitely, it should be made more clear as to what is going on, as suggested by brandon.

    Were you trying to do this on a remote machine? If yes, you would be locked out since it restricts remote users from using root login.

    I did the same and I had to uninstall and then reinstall mysql to recover.

    I confirm that instructions on Step 3 results in locking up ourselves out of mysql. Indeed, something there is not right.

    alexdo
    Site Moderator
    Site Moderator badge
    May 31, 2024

    It’s possible that if the root user was previously using auth_socket for authentication and you’ve switched it to mysql_native_password, it might cause authentication issues, especially if you don’t set a password during the process.

    Here are a few potential reasons why this could lead to being locked out of MySQL:

    If the root user had an empty password before, switching to mysql_native_password without setting a password could cause authentication failures.

    MySQL may have a password policy in place that requires a certain level of complexity or length for passwords. If the password you set doesn’t meet these requirements, it could lead to authentication failures.

    There could be configuration issues in MySQL that prevent authentication with passwords, such as misconfigured authentication plugins or incorrect settings in the my.cnf configuration file.

    Regards

    Hello. On the 3rd step when I try execute command:

    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
    

    I faced error:

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password' at line 1
    
    alexdo
    Site Moderator
    Site Moderator badge
    May 31, 2024

    It seems like you’re using MariaDB instead of MySQL. The ALTER USER syntax you’re trying to use is specific to MySQL 5.7 and later versions, and it may not be compatible with MariaDB.

    In MariaDB, you can set the password for the ‘root’ user using a different syntax. Here’s how you can do it:

    SET PASSWORD FOR 'root'@'localhost' = PASSWORD('your_password');
    

    Replace 'your_password' with the desired password for the ‘root’ user.

    Alternatively, you can try using the mysql_native_password plugin in MariaDB. Here’s how you can do it:

    UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE User = 'root'; FLUSH PRIVILEGES; SET PASSWORD FOR 'root'@'localhost' = PASSWORD('your_password');
    

    Again, replace 'your_password' with the desired password for the ‘root’ user.

    After setting the password, you should be able to log in using the specified password. If you encounter any issues or need further assistance, feel free to ask!

    Thanks for the great tutorial(s)!

    I have a question regarding mysql_secure_installation:

    I installed mysql-server 5.7 on Ubuntu 18.04 and ran the commands manually, as described here:

    UPDATE mysql.user SET authentication_string='secretpassword' WHERE user='root';
    DELETE FROM mysql.user WHERE user='';
    DELETE FROM mysql.user WHERE user='root' AND Host NOT IN ('localhost', 
    '127.0.0.1', '::1');
    DROP DATABASE IF EXISTS test;
    DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
    FLUSH PRIVILEGES;
    

    Apart from changing the root password, each of the commands showed no effect at all:

    Query OK, 0 rows affected (0.00 sec)
    

    I have two questions:

    1. Why would I want to use mysql_secure_installation if it does not have any effect?
    2. Why would I want to set a root password if I stick with the default auth_socket authentification?

    Not sure about your 2nd question, but the commands showing 0 rows affected only means that database rows/records weren’t affected. Since we are doing administrative type setup tasks, we are modifying those things, but not a specific database record. I think its just a default response in the mysql cli to report if/how many records were changed from the query.

    Thanks a lot for taking your time to clarify this. I still wonder whether mysql_secure_installation makes a difference or if it’s just redundant (in the context of mysql-server 5.7 on Ubuntu 18.04) .

    Sure thing. The 2-3 tutorials I’ve seen say to go ahead and do it. It just takes a few minutes. I don’t think its redundant because its basically setting things up with various choices you make rather than stuff that would be enabled as part of a distro’s included installations. But I’m not certain. In general, I try to go with more secure options like this since security is such an issue.

    I was unable to login to mysql after installation no matter what I did until I found this, but I lost the link to the original page, just copying from my notes. I’m running ubuntu server 18.04 on ESXi. I installed mysql using sudo apt-get install mysql-server. These steps make your password root. Just substitute your desired password for ‘root’ after the word BY at the end of the line that starts with ALTER USER.

    sudo nano /etc/mysql/my.cnf Add to bottom of file: [mysqld] skip-grant-tables

    save and exit

    sudo service mysql restart mysql -u root -p (press enter to leave blank password)

    mysql> flush privileges;

    ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘root’; exit sudo service mysql restart sudo nano /etc/mysql/my.cnf and change from skip-grant-tables to #skip-grant-tables save and exit sudo service mysql restart

    Now you will be able to login with the new password mysql -u root -p mysql>flush privileges;

    alexdo
    Site Moderator
    Site Moderator badge
    May 31, 2024

    Thanks for sharing this information.

    You can also always reach out in our community whenever you have questions or want to share details on SysAdmin, DigitalOcean and beyond.

    https://www.digitalocean.com/community/questions

    Regards

    Hi there, apache2 is being disabled after installation of MySQL on ubuntu 14.4 , any thing am doing wrong?

    alexdo
    Site Moderator
    Site Moderator badge
    May 31, 2024

    It’s unusual for installing MySQL to disable Apache2. However, it’s possible that there might be some configuration or dependency conflict causing this behavior.

    Regards

    Mark

    This was a great guide. Thank you.

    I have one question though and I’ve googled trying to find the answer but so far have found nothing.

    In Step 3 …

    Is there an alternative means of accomplishing the following 2 from a command line using say mysqladmin?

    ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;

    FLUSH PRIVILEGES;

    I have an application that requires the above as part of the applications installation but I am trying to just put the application’s entire install into a Bash script with “minimal” user interaction.

    Using your Step 3 works - but requires the manual entry/execution of:

    $ mysql msyql> ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’; mysql> FLUSH PRIVILEGES; mysql> quit

    I thought perhaps in a script I could just do:

    mysqladmin -u root password NEWPASSWORD

    then

    mysqladmin flush-privileges

    but the access to the application fails where it succeeds if I do it your way using the manual entry/execution of commands.

    My assumption is that

    mysqladmin -u root password NEWPASSWORD changes the password but does not change auth-socket to mysql_native-password like your method.

    So are you aware of who to make the equivalent of your method? can mysqladmin do that?

    thanks in advance for any info Brian

    Mark… never mind I figured it out. This works as a way to execute all of that in a single command line:

    mysql -uroot -e “ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;” -e “FLUSH PRIVILEGES;”

    where ‘password’ needs to be changed to the new password for root such as: ‘newpassword’

    Thanks again. brian

    alexdo
    Site Moderator
    Site Moderator badge
    May 31, 2024

    Hey, Mark

    I’m glad that you’ve sorted this out. The one-liner command should work fine as described.

    Regards

    On Ubuntu 18.10 on Digital Ocean this guide don’t work… I really don’t know why I . just change the droplet and everything works as expected.

    alexdo
    Site Moderator
    Site Moderator badge
    May 31, 2024

    Thanks for your feedback.

    There might have been changes to the article to address some error or typo. I believe the article should work just fine on Ubuntu 18.04.

    Regards

    This comment has been deleted

      Got it working without any problems, thank you. How would I go about setting up remote connections so I can connect from my mac using sequel pro?

      alexdo
      Site Moderator
      Site Moderator badge
      May 31, 2024

      You can check this article on how to allow remote access:

      https://www.digitalocean.com/community/tutorials/how-to-allow-remote-access-to-mysql

      Regards

      Try DigitalOcean for free

      Click below to sign up and get $200 of credit to try our products over 60 days!

      Sign up

      Join the Tech Talk
      Success! Thank you! Please check your email for further details.

      Please complete your information!

      Become a contributor for community

      Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

      DigitalOcean Documentation

      Full documentation for every DigitalOcean product.

      Resources for startups and SMBs

      The Wave has everything you need to know about building a business, from raising funding to marketing your product.

      Get our newsletter

      Stay up to date by signing up for DigitalOcean’s Infrastructure as a Newsletter.

      New accounts only. By submitting your email you agree to our Privacy Policy

      The developer cloud

      Scale up as you grow — whether you're running one virtual machine or ten thousand.

      Get started for free

      Sign up and get $200 in credit for your first 60 days with DigitalOcean.*

      *This promotional offer applies to new accounts only.