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
user | root |
password | root |
database | safs |
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)