Linode | VPS | Tutorials | Development

David Carr

Setup Linode - Part 8 MySQL


 

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

Domains are often purchased from multiple providers, keeping track of where a domain is and its DNS settings can be tricky. Domain Mapper solves this by listing all your domains in one place. View your DNS settings and receive reminders to renew your domains. Try it today.

Support my work by donating with PayPal.

Subscribe to my newsletter

Subscribe and get my books and product announcements.

© 2009 - 2021 DC Blog. All code MIT license. All rights reserved.