DEV Community

cGIfl300
cGIfl300

Posted on

1

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

Do your career a favor. Join DEV. (The website you're on right now)

It takes one minute and is worth it for your career.

Get started

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

πŸ‘‹ Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay