Setup Digital Ocean - Part 6 MySQL

David Carr

Digital Ocean VPS Tutorials

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

Fathom Analytics $10 discount on your first invoice using this link

David Carr - Laravel Developer

Hi, I’m David Carr

A Senior Developer at Vivedia
I love to use the TALL stack (Tailwind CSS, Alpine.js, Laravel, and Laravel Livewire)

I enjoy writing tutorials and working on Open Source packages.

I also write books. I'm writing a new book Laravel Testing Cookbook, This book focuses on testing coving both PestPHP and PHPUnit.

Sponsor me on GitHub

Subscribe to my newsletter

Subscribe and get my books and product announcements.

Laravel Testing Cookbook

Help support the blog so that I can continue creating new content!

Fathom Analytics $10 discount on your first invoice using this link

Subscribe to my newsletter

Subscribe and get my books and product announcements.

© 2006 - 2023 DC Blog. All code MIT license. All rights reserved.