Setup Digital Ocean - Part 6 MySQL

• Part 1 LAMP
• Part 2 Upgrade PHP to 7.3
• Part 3 Apache Enable Mod Rewrite
• Part 4 Virtual Hosts
• Part 5 Lets Encrypt
• Part 6 MySQL
• Part 7 Remote MySQL
• Part 8 Composer
• Part 9 Laravel

Logged in as a user use sudo to access elevated privileges or login as root.

sudo mysql -u root

Databases

Create a new database

CREATE DATABASE database_name;

Show all databases

SHOW DATABASES;

Delete database

DROP DATABASE database_name;

switch to a specific database

use Demo;

Users

Create MySQL User

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'user_password';

change newuser with the desired user name.
change user_password with the desired password
set localhost to allow only connections on localhost directly ie only on the host machine.
Replace localhost with an IP address for a specific connection

To create a user that can connect from any host, use the '%' wildcard as a host part:

CREATE USER 'newuser'@'%' IDENTIFIED BY 'user_password';

Privileges

There are multiple types of privileges that can be granted to a user account. You can find a full list of privileges supported by MySQL here.
The most commonly used privileges are:

ALL PRIVILEGES – grants all privileges to a user account.
CREATE – user account is allowed to create databases and tables.
DROP - user account is allowed to drop databases and tables.
DELETE - user account is allowed to delete rows from a specific table.
INSERT - user account is allowed to insert rows into a specific table.
SELECT – user account is allowed to read a database.
UPDATE - user account is allowed to update table rows.

Grant all privileges to a user account over a specific database:

GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';

Display MySQL User Account Privileges
To find the privilege(s) granted to a specific MySQL user account use the `SHOW GRANTS` statement:

SHOW GRANTS FOR 'database_user'@'localhost';

Revoke Privileges from a MySQL User Account
The syntax to revoke one or more privileges from a user account is almost identical as when granting privileges.

For example to revoke all privileges from a user account over a specific database, use the following command:

REVOKE ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';

Remove an Existing MySQL User Account

To delete a MySQL user account use the DROP USER statement:

DROP USER 'user'@'localhost';

The command above will remove the user account and its privileges.

Source https://linuxize.com/post/how-to-create-mysql-user-accounts-and-grant-privileges/#disqus\_thread