For those of you who are used to using a GUI (like phpmyadmin) when creating a database and its user, you will feel confused when dealing with the commandline. You will ask how to create a user and database with the command line.
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;
