Create a MySQL database using command line (CLI)

On your local OSX environment using Terminal or iTerm you can create a MySQL database, database user, and password, as well as, assign all privileges to the user for the database.

Knowing your credentials, before beginning you will need to know the following:

user: {user}
password: {password}
database: {database}

For my local environment, I'll be using these credentials

Walkthrough the steps to build your database:

The initial step is to create the user.  If you want to view the current list of users, log in MySQL

lando mysql

Your response screen post logging in will look similar to

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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>

Users

To view the list of current users, execute the command

SELECT user FROM mysql.user;

The list of MySQL users for me was

+---------------+
| user          |
+---------------+
| laravel       |
| root          |
| mysql.session |
| mysql.sys     |
+---------------+
4 rows in set (0.01 sec)

As root already exists as a user, there is no need to create the user.  But how do create a user if they don't exist?

If your user already exists, in future you can log into MySQL as the user as that user.  For example to log in a root, I would use:

lando mysql -u root

How to show MySQL more user information

The following query provides a table with the User, Host, and authentication_string columns:

SELECT User, Host, authentication_string FROM mysql.user;

How to show the current user

Use the user() or current_user() function to get the details of the current MySQL user:

SELECT user();
# or 
SELECT current_user();

1. Create a user in MySQL

Create a new database user:

GRANT ALL PRIVILEGES ON *.* TO '{user}'@'localhost' IDENTIFIED BY '{password}';

And replacing the relevant values

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'root';

2. Log out of MySQL

Log out of MySQL by typing:

\q
# or you can use
exit;

3. Log in as your new user

Log in as the new database user you have just created:

mysql -u {user} -p
# For me that would be 
mysql -u root -p

Databases

To see a list of databases on your system, enter the command

show databases;

And you will see a response similar to 

+--------------------+
| Database           |
+--------------------+
| information_schema |
| laravel            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

If your database isn't in the list, time to create it

​
4. Create your database

CREATE DATABASE {database};
# As per my example, in my situation that would be
CREATE DATABASE safs;

​The response for a successfully generated database will be

Query OK, 1 row affected (0.02 sec)