MySQL Memo

Library

MySQL

MySQL is a SQL Database.
To connect to a MySQL Database you need:
- The server address
- A username
- A password
- A database

Admin the MySQL

To enter the admin console you have 2 options.

mysql

As root, use mysql:

mysql

As user:

mysql -u=my_username -p

-p means a password is required, you need to have a user account on the MySQL server to use it. To exit the shell, use:

exit;

MySQL Workbench

A very good tool you can download with a GUI to remote control any MySQL server.
Using your user account you can manage everything with few clicks.

Users

Create a user

When you create a user account, it doesn't have any right, you have to add them to use a database or anything.

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

% as domain allow a connexion from anywhere
localhost as domain restrict the usage to a local connexion only.

Grant a user

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

Grant Different User Permissions

List of common possible permissions that users can enjoy. - ALL PRIVILEGES - as we saw previously, this would allow a MySQL user full access to a designated database (or if no database is selected, global access across the system)
- CREATE - allows to create new tables or databases
- DROP - allows to them to delete tables or databases
- DELETE - allows to delete rows from tables
- INSERT - allows to insert rows into tables
- SELECT - allows to use the SELECT command to read through databases
- UPDATE - allow to update table rows
- GRANT OPTION - allows to grant or remove other users’ privileges

You can use this template to grant a permission to a user:

GRANT type_of_permission ON database_name.table_name TO 'username'@'localhost';
FLUSH PRIVILEGES;

Update a password

ALTER USER 'user-name'@'localhost' IDENTIFIED BY 'NEW_USER_PASSWORD';
FLUSH PRIVILEGES;

Delete a user

DROP USER 'user'@'localhost';

Databases

Add a database to a specific user

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
CREATE DATABASE my_database;
GRANT ALL PRIVILEGES ON my_database TO 'newuser'@'localhost';

Delete a database

DROP DATABASE db_name;

Alternative:

DROP SCHEMA db_name;

Both commands do the same.

By @cGIfl300 in
Tags : #MySQL, #memo,