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.

After CREATE USER, you specify a username. This is immediately followed by an @ sign and then the hostname from which this user will connect. If you only plan to access this user locally from your Ubuntu server, you can specify localhost. Wrapping both the username and host in single quotes isn’t always necessary, but doing so can help to prevent errors.

There is a known issue with some versions of PHP that causes problems with caching_sha2_password. If you plan to use this database with a PHP application — phpMyAdmin, for example — you may want to create a user that will authenticate with the older, though still secure, mysql_native_password plugin instead

You have several options when it comes to choosing your user’s authentication plugin. The auth_socket plugin mentioned previously can be convenient, as it provides strong security without requiring valid users to enter a password to access the database. But it also prevents remote connections, which can complicate things when external programs need to interact with MySQL.

To illustrate, the following command grants a user global privileges to CREATE, ALTER, and DROP databases, tables, and users, as well as the power to INSERT, UPDATE, and DELETE data from any table on the server. It also grants the user the ability to query data with SELECT, create foreign keys with the REFERENCES keyword, and perform FLUSH operations with the RELOAD privilege. However, you should only grant users the permissions they need, so feel free to adjust your own user’s privileges as necessary.

Note that this statement also includes WITH GRANT OPTION. This will allow your MySQL user to grant any permissions that it has to other users on the system.

Many guides suggest running the FLUSH PRIVILEGES command immediately after a CREATE USER or GRANT statement in order to reload the grant tables to ensure that the new privileges are put into effect

However, according to the official MySQL documentation, when you modify the grant tables indirectly with an account management statement like GRANT, the database will reload the grant tables immediately into memory, meaning that the FLUSH PRIVILEGES command isn’t necessary in our case. On the other hand, running it won’t have any negative effect on the system. If you need to revoke a permission, the structure is almost identical to granting it

Note that when revoking permissions, the syntax requires that you use FROM, instead of TO which you used when granting the permissions. You can review a user’s current permissions by running the SHOW GRANTS command

Just as you can delete databases with DROP, you can use DROP to delete a user

After creating your MySQL user and granting them privileges, you can exit the MySQL client

The -p flag will cause the MySQL client to prompt you for your MySQL user’s password in order to authenticate.