- Part 1 LAMP
- Part 2 Users
- Part 3 Upgrade PHP to 7.3
- Part 4 Apache Enable Mod rewrite
- Part 5 Composer
- Part 6 Virtual Hosts
- Part 7 Let's Encrypt
- Part 8 MySQL
- Part 9 Remote MySQL over SSH
- Part 10 Laravel
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