Loading...

Setup Digital Ocean - Part 6 MySQL

David Carr

2 min read - 7th Aug, 2019

Table of Contents


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

0 comments
Add a comment