Logged in as a user use sudo to access elevated privileges or login as root.
sudo mysql -u root
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;
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';
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
Subscribe to my newsletter for the latest updates on my books and digital products.
Find posts, tutorials, and resources quickly.
Subscribe to my newsletter for the latest updates on my books and digital products.
