DEV Community

cGIfl300
cGIfl300

Posted on

Hyper QuickStart to MySQL

Be straight, just do it.

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
Enter fullscreen mode Exit fullscreen mode

As user:

mysql -u=my_username -p
Enter fullscreen mode Exit fullscreen mode

-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;
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

% 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;
Enter fullscreen mode Exit fullscreen mode

Grant Different User Permissions

Common possible permissions 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;
Enter fullscreen mode Exit fullscreen mode

Update a password

ALTER USER 'user-name'@'localhost' IDENTIFIED BY 'NEW_USER_PASSWORD';
FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

Delete a user

DROP USER 'user'@'localhost';
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

Delete a database

DROP DATABASE db_name;
Enter fullscreen mode Exit fullscreen mode

Alternative:

DROP SCHEMA db_name;
Enter fullscreen mode Exit fullscreen mode

Both commands do the same.

You don't like dev.io? No problem, bookmark this

Discussion (0)