Introduction
MySQL is an open-source relational database management system. It is commonly deployed as part of the LAMP stack (which stands for Linux, Apache, MySQL, and PHP) and, as of this writing, is the most popular open-source database in the world.
This guide outlines how to create a new MySQL user and grant them the permissions needed to perform a variety of actions.
Prerequisites
In order to follow along with this guide, you’ll need access to a MySQL database. This guide assumes that this database is installed on a virtual private server running Ubuntu 20.04, though the principles it outlines should be applicable regardless of how you access your database.
If you don’t have access to a MySQL database and would like to set one up yourself, you can follow one of our guides on How To Install MySQL. Again, regardless of your server’s underlying operating system, the methods for creating a new MySQL user and granting them permissions will generally be the same.
You could alternatively spin up a MySQL database managed by a cloud provider. For details on how to spin up a DigitalOcean Managed Database, see our product documentation.
Please note that any portions of example commands that you need to change or customize will be highlighted like this throughout this guide.
Creating a New User
Upon installation, MySQL creates a root user account which you can use to manage your database. This user has full privileges over the MySQL server, meaning it has complete control over every database, table, user, and so on. Because of this, it’s best to avoid using this account outside of administrative functions. This step outlines how to use the root MySQL user to create a new user account and grant it 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 plugin requires that the name of the operating system user that invokes the MySQL client matches the name of the MySQL user specified in the command. This means that you need to precede the mysql command with sudo to invoke it with the privileges of the root Ubuntu user in order to gain access to the root MySQL user: