Currently seeking new career opportunities in web development, particularly with Laravel, Hire Me

Setup Linode - Part 8 MySQL

David Carr

Development VPS Tutorials Linode

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

Laravel Modules Your Logo Your Logo Your Logo

Become a sponsor

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

Sponsor

My Latest Book

Modular Laravel Book - Laravel: The Modular way

Learn how to build modular applications with Laravel Find out more

Subscribe to my newsletter

Subscribe and get my books and product announcements.

Learn Laravel with Laracasts

Faster Laravel Hosting

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