MySQL Configuration

I’ve been using MySQL 8 on Linux for a couple years but the
Linux repository version didn’t install the new MySQL Shell. So,
I discovered the new MySQL Shell when I installed MySQL 8 (8.0.21)
on Windows to teach my student how to use it to learn SQL commands.
I encourage you to read the full
MySQL Shell document
.

The following is a tutorial to provision a student user and
studentdb database in MySQL. It uses the MySQL Shell (mysqlsh) and
stages for uploads of comma-separated values files.

After installing MySQL on the Windows 10 OS, open the Window OS
Command Line Interface (CLI) shell by entering the following in the
search field at the bottom left:

cmd

It launches a CLI interface to the Windows OS. The cmd (command)
utility opens the CLI in the following directory (where you
substitute your user’s name for the username placeholder
below):

C:\Users\username

At the command prompt, you would enter mysql to launch the old
MySQL CLI. Instead of that, you enter mysqlsh to launch the new
MySQL CLI as the root user. The command to launch the MySQL Shell
as the root user is:

mysqlsh -uroot -p

It should return the following and prompt you for a single
character entry to save the password for the Operating System user.
As a rule, in a development instance that’s a good idea and
practice.

MySQL Shell 8.0.21

Copyright (c) 2016, 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 '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost'
Please provide the password for 'root@localhost:33060': ********
Save password for 'root@localhost:33060'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 9 (X protocol)
Server version: 8.0.21 MySQL Community Server - GPL
No default schema selected; type \use  to set one.
 MySQL  localhost:33060+ ssl  JS >

The prompt will not accept SQL commands because it always
initializes in the JavaScript (JS) context. The MySQL Shell
supports three interactive interfaces: JavaScript, Python, and
SQL.

You can verify the integrity of the shell from the JavaScript
(JS) context as the root user with the following command:

 MySQL  localhost:33060+ ssl  JS > shell.status()

As the root user, it should return something like this:

MySQL Shell version 8.0.21

Connection Id:                9
Default schema:
Current schema:
Current user:                 root@localhost
SSL:                          Cipher in use: TLS_AES_256_GCM_SHA384 TLSv1.3
Using delimiter:              ;
Server version:               8.0.21 MySQL Community Server - GPL
Protocol version:             X protocol
Client library:               8.0.21
Connection:                   localhost via TCP/IP
TCP port:                     33060
Server characterset:          utf8mb4
Schema characterset:          utf8mb4
Client characterset:          utf8mb4
Conn. characterset:           utf8mb4
Result characterset:          utf8mb4
Compression:                  Enabled (DEFLATE_STREAM)
Uptime:                       20 hours 4 min 19.0000 sec

You can switch to the SQL context as the root or any other user
with this command. The switch only changes your form of interaction
with the server and you remain connected as the root user:

 MySQL  localhost:33060+ ssl  JS > \sql

You can verify that your session is still owned by the root user
with the following SELECT statement:

 MySQL  localhost:33060+ ssl  SQL > SELECT user();

It returns:

+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.0005 sec)

The next step shows you how to setup a sample studentdb
database. Some syntax has changed from prior MySQL releases. Here
are the three steps:

  1. Create the studentdb database with the following command as the
    MySQL root user:
     MySQL  localhost:33060+ ssl  SQL > CREATE DATABASE studentdb;
    
  2. Grant the root user the privilege to grant to others, which
    root does not have by default. You use the following syntax as the
    MySQL root user:
     MySQL  localhost:33060+ ssl  SQL > GRANT ALL ON *.* TO 'root'@'localhost';
    
  3. Create the user with a clear English password and grant the
    user student full privileges on the studentdb database:
     MySQL  localhost:33060+ ssl  SQL > CREATE USER 'student'@'localhost' IDENTIFIED WITH mysql_native_password BY 'student';
     MySQL  localhost:33060+ ssl  SQL > GRANT ALL ON studentdb.* TO 'student'@'localhost';
    
  4. Our sample database uses large file uploads with MySQL’s LOAD
    command, which means you need to grant one additional global
    privilege:
     MySQL  localhost:33060+ ssl  SQL > GRANT FILE ON *.* TO 'student'@'localhost';
    

The MySQL FILE privilege is a global privilege to read and write
files on the local server. MySQL 8 installation on Windows 10 sets
the following directory as the target for uploading files in the
my.ini file:

# Secure File Priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"

You can find the setting in the C:\ProgramData\MySQL\MySQL
Server 8.0\my.ini file. You can find this value without referencing
the my.ini file by querying the data:

show variables like 'secure_file_priv';

A new installation should return:

+------------------+------------------------------------------------+
| Variable_name    | Value                                          |
+------------------+------------------------------------------------+
| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------+------------------------------------------------+
1 row in set (0.2253 sec)

You can test the ability to use the LOAD command with the
following avenger.csv test file. Copy it into the
C:\ProgramData\MySQL\MySQL Server 8.0\Uploads directory and make
sure the directory permissions are set to read-only for Everyone
(check the Microsoft OS documentation if these concepts are new to
you).

1,'Anthony','Stark','Iron Man'
2,'Thor','Odinson','God of Thunder'
3,'Steven','Rogers','Captain America'
4,'Bruce','Banner','Hulk'
5,'Clinton','Barton','Hawkeye'
6,'Natasha','Romanoff','Black Widow'

Open another cmd (command) CLI and put the following code into a
file that you save as avenger.sql in the C:\Users\username
directory.

-- Conditionally drop objects.
SELECT 'AVENGER' AS "Drop Table";
DROP TABLE IF EXISTS avenger;

-- Create an avenger table.
CREATE TABLE avenger
( avenger_id      INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, first_name      VARCHAR(20)
, last_name       VARCHAR(20)
, character_name  VARCHAR(20));

-- Load the data from a file, don't forget the \n after the \r on Windows or it won't work.
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/avenger.csv'
-- LOAD DATA INFILE 'avenger.csv'
INTO TABLE avenger
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '/'
LINES TERMINATED BY '\r\n';
 
-- Select the uploaded records.
SELECT * FROM avenger;

From the cmd (command) shell, launch the MySQL Shell as the
student user with the following syntax:

mysqlsh -ustudent -p -Dstudentdb

At the MySQL Shell (mysqlsh) prompt switch from the default
JavaScript (JS) context to SQL context and run the following
query:

 MySQL  localhost:33060+ ssl  studentdb  SQL > \sql
 MySQL  localhost:33060+ ssl  studentdb  SQL > SELECT user(), database();

If you did everything above correctly, it should return:

+-------------------+------------+
| user()            | database() |
+-------------------+------------+
| student@localhost | studentdb  |
+-------------------+------------+
1 row in set (0.0003 sec)

Again, assuming you did everything above correctly, you should
be able to run your avenger.sql script file from the MySQL Shell
(mysqlsh) prompt, like:

 MySQL  localhost:33060+ ssl  studentdb  SQL > source avenger.sql

It should return output like the following:

Query OK, 0 rows affected (0.0003 sec)
+------------+
| Drop Table |
+------------+
| AVENGER    |
+------------+
1 row in set (0.0003 sec)
Query OK, 0 rows affected (0.0595 sec)
Query OK, 0 rows affected (0.0002 sec)
Query OK, 0 rows affected (0.1293 sec)
Query OK, 0 rows affected (0.0002 sec)
Query OK, 6 rows affected (0.0046 sec)

Records: 6  Deleted: 0  Skipped: 0  Warnings: 0
Query OK, 0 rows affected (0.0004 sec)
+------------+------------+------------+-------------------+
| avenger_id | first_name | last_name  | character_name    |
+------------+------------+------------+-------------------+
|          1 | 'Anthony'  | 'Stark'    | 'Iron Man'        |
|          2 | 'Thor'     | 'Odinson'  | 'God of Thunder'  |
|          3 | 'Steven'   | 'Rogers'   | 'Captain America' |
|          4 | 'Bruce'    | 'Banner'   | 'Hulk'            |
|          5 | 'Clinton'  | 'Barton'   | 'Hawkeye'         |
|          6 | 'Natasha'  | 'Romanoff' | 'Black Widow'     |
+------------+------------+------------+-------------------+
6 rows in set (0.0005 sec)

You now have a student user and studentdb database like my
students. I hope it was fun to build.

You can find the my.ini file in the C:\ProgramData\MySQL\MySQL
Server 8.0 directory of a standard Windows 10 file system. Its a
good idea not to change anything unless you know what you’re
doing, and remember you need to restart the Microsoft MySQL80
Service for any change to be made effective in your database
operations.