How to Create User and Database MySQL via Command Line (CLI)

How to Create User and Database MySQL via Command Line (CLI)

The problem of how to create MySQL users and databases through the command line is usually felt by hosting users who are using VPS for the first time, especially small VPS RAM without panels and without phpmyadmin. In this article we will discuss several ways to create a MySQL Database and MySQL user via the command line.

How to install Easyengine and wordpress on VPS
How To Install Letsencrypt auto renew on Vestacp Web Panel
How to fix Error 413 (Request Entity Too Large) in NGINX

The problem of how to create MySQL users and databases through the command line is usually felt by hosting users who are using VPS for the first time, especially small VPS RAM without panels and without phpmyadmin. In this article we will discuss several ways to create a MySQL Database and MySQL user via the command line.

How to Create User and Database MySQL via Command Line:

There are 2 ways to create a Table Database and User in MySQL via the command line

1. Create user and Database directly using root access

The first way is to create a database and mySQL user through root at once. This method is effective if you only make 1 database with 1 user, but it will be very ineffective if the database is made a lot.

The first step is to enter mysql as root:

mysql -u root

then, Create the database:

CREATE DATABASE mydbname;

After that, give privileges to the new user:

GRANT ALL PRIVILEGES ON mydbname.* TO 'myuser01'@'localhost' IDENTIFIED BY 'yourpasswordhere';

With these two steps, you have successfully created a database and user and connected it …
The complete code is as follows:

root@bestariwebhost:~# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 95765
Server version: 5.7.26-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE mydbname;
Query OK, 1 row affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON mydbname.* TO 'myuser01'@'localhost' IDENTIFIED BY 'passwordanda';
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> \q;
Bye

2. The database is created through each user

The second way is to make the user root, then create a database in each user.

First step: Login to mysql through the root user

mysql -u root

Second step: Create a new user as follow:

CREATE USER 'mydbuser01'@'localhost' IDENTIFIED BY 'passwdDB';

after that exit the root user with the following command:

mysql> \q

The next step is to log in with the user you just added:

mysql -u mydbuser01 -p

Enter the database password according to what you have created. After that, create a database according to your needs:

CREATE DATABASE mydbname01;
CREATE DATABASE mydbname02;

Incoming search terms:

  • how to create user in mysql database

COMMENTS

WORDPRESS: 0
DISQUS: 0