MySQL Cookbook
Everything you need to know about MySQL for the database courses @ HSLU
Georg Lampart, Michael Kaufmann
Version: 2021-10-01 12:02:42
Page 1
Table of Contents
Table of Contents .................................................................................................................................... 2
1 MySQL installation for Windows ...................................................................................................... 3
1.1 Setup and download .............................................................................................................. 3
1.2 Prerequisites .......................................................................................................................... 3
1.3 Steps ...................................................................................................................................... 3
1.4 Configuration.......................................................................................................................... 4
1.5 Samples ................................................................................................................................. 7
1.6 First connection with MySQL Workbench .............................................................................. 8
2 MySQL Installation for Mac .............................................................................................................. 9
2.1 Install Mysql Server................................................................................................................ 9
2.2 Install MySQL Workbench ................................................................................................... 20
3 Loading and Retrieving Data.......................................................................................................... 29
3.1 Allow Importing Local Files on Mac ..................................................................................... 29
3.2 Test the LOAD command in MySQL Workbench ................................................................ 33
3.3 Importing fast using MySQL Shell util.importTable .............................................................. 36
3.4 Visualization of MySQL with Tableau .................................................................................. 36
4 Demo database installation ............................................................................................................ 38
4.1 Prerequisites ........................................................................................................................ 38
4.2 Connection to MySQL .......................................................................................................... 38
4.3 Create a database ............................................................................................................... 39
5 Runing scripts ................................................................................................................................ 39
5.1 Create your own scripts ....................................................................................................... 40
6 MySQL Manual .............................................................................................................................. 41
6.1 Installation on Mac: .............................................................................................................. 41
6.2 Installation on Windows: ...................................................................................................... 41
6.3 Creating a database connection: ......................................................................................... 41
6.4 Creating a database model: ................................................................................................. 41
6.5 Loading Data Into the Model: ............................................................................................... 41
6.6 DB queries in the SQL editor: .............................................................................................. 41
Page 2
1 MySQL installation for Windows
1.1 Setup and download
• mysql-installer-community-8.0.19.0.msi
• https://dev.mysql.com/downloads/windows/installer/8.0.html
1.2 Prerequisites
On Windows systems, if "Visual C++ 2019 Redistributable" is missing, download the "x64" setup and in-
stall it first:
1.3 Steps
• Choose Developer Edition or Custom
• Install
- MySQL Server
- MySQL Workbench
- MySQL Shell
- (optional) Connector (e.g. Python, .NET)
- MySQL Documentation
- MySQL Samples and Examples
Page 3
• Install by clicking "Execute"
1.4 Configuration
• Configure Server
- Standalone MySQL Server
• Type and Networking
- If you want to install it locally: Development Computer; Port 3306; disable "Open Win-
dows Firewall ports for network access"
- If you want to install it on a remote server, e.g. for Project team work: Server Computer;
Port 3306; enable "Open Windows Firewall ports for network access"
Page 4
• Authentication Method
- Use a strong password
• Accounts and Roles
- Provide a strong password for "root" (and save it to a secure location) (no 1 in the
screenshot below)
à Also add a dedicated user (HSLUUser in the screenshot) with strong password (no 2,
3 and 4)
Page 5
• Windows Service
- Configure as service (MySQL80) with startup and Standard system account
• Execute
Page 6
1.5 Samples
• Configure Samples
- Enter the root password you haven chosen before
- Click "Check" (no 5) to verify if you can connect to the server with the root password
- If successful, you see a (green) status above (no 6)
• Execute
Page 7
1.6 First connection with MySQL Workbench
See section 4.2 Connection to MySQL.
Page 8
2 MySQL Installation for Mac
Legend
Þ Literal command ( menu point to klick; or keyboard input )
Þ < description of user activity >
Þ ( comment )
2.1 Install Mysql Server
2.1.1 Download Installation Files
https://dev.mysql.com/downloads/mysql/
Þ Download
Page 9
Þ No thanks, just start my download
2.1.2 Run Installation Files
Þ mysql-8.0.21-macos10.15-x86_64.dmg
Page 10
Þ Open
Þ Open
Þ Continue
Page 11
Þ Continue
Þ Continue
Page 12
Þ Agree
Þ Install
Page 13
Þ Use Password
Þ Next
Page 14
Þ <choose your root password>
Þ Finish
Page 15
Þ Close
2.1.3 Initialize the Database
For some reason, login into workbench only works if you initialize the database after the installation pro-
cedure.
Page 16
Þ System Preferences
Þ MySQL
Þ Initialize Database
Page 17
Þ OK
Þ Start MySQL Server
Page 18
Page 19
2.2 Install MySQL Workbench
2.2.1 Download Installation Files
https://dev.mysql.com/downloads/workbench/
Þ Download
Page 20
Þ mysql-workbench-community-8.0.21-macos-x86_64.dmg
Page 21
2.2.2 Install the Application
Þ <drag and drop MySQLWorkbench to Applications>
2.2.3 Open the Application
Þ Applications
Page 22
Þ <right-click> MySQLWorkbench
Þ Open
Þ Open
Page 23
2.2.4 Establish Database Connection
Þ <right-click> Local Instance 3306
Þ Edit Connection
Page 24
Þ Store in Keychain
Þ < Enter password for root user as defined in database initialization >
Þ OK
Page 25
Þ Test Connection
Þ Ok
Þ Close
Page 26
Þ <right-click> Local Instance 3306
Þ Open Connection
Page 27
2.2.5 Run first command in MySQL Workbench
Þ Schemas
Þ SHOW VARIABLES LIKE "secure_file_priv";
Þ Execute
Þ ( Secure_file_priv not set: NULL means variable value is empty )
Page 28
3 Loading and Retrieving Data
3.1 Allow Importing Local Files on Mac
The following steps need to be done only on Mac computers to allow data import using the LOAD com-
mand. On Windows, this should work already.
3.1.1 Create configuration file my.cnf
<Command> + <Space>
Þ Terminal
Þ sudo nano /etc/my.cnf
Þ <enter root password, see https://support.apple.com/en-us/HT204012 >
Þ < If this file exists already from earlier installations, remove it first, sudo rm /etc/my.cnf >
Page 29
Þ < Enter the following text: >
[mysqld_safe]
[mysqld]
secure_file_priv="/usr/local/mysql/"
Þ <Control> O
Þ Enter
Þ <Control> X
Þ ( FYI: if you work with large datasets, here you can specify net_read_timeout=250000 to support
long queries )
3.1.2 Restart the Database Server
Þ System Preferences
Þ MySQL
Page 30
Þ Stop MySQL Server
Þ Configuration
Page 31
Þ <Tick checkbox> Configuration File
Þ /etc/my.cnf
Þ Apply
Þ < Enter admin password >
Þ Instances
Þ Start MySQL Server
Þ < Enter admin password >
Page 32
3.1.3 Check the secure_file_priv variable in MySQL Workbench
Þ show variables like "secure_file_priv"
Þ Execute
Þ ( Secure_file_priv is now set => you can LOAD files from this folder )
Þ
3.2 Test the LOAD command in MySQL Workbench
This chapter describes how to import a CSV file into MySQL. The Screen Shots are from a Mac System,
but the LOAD command works in the same way on Windows systems.
3.2.1 Create local file to import
Þ Mac + Windows: Create local file to import in the folder indicated in the “secure_file_priv” variable
Þ Mac: <Command> + <Space>
Þ Mac: Terminal
Page 33
Þ Mac:
sudo nano /usr/local/mysql/share/employee1.txt
Þ Mac + Windows: Enter the following Text into the file: (exactly two lines, tabulators as value sepa-
rators, no newline character at the end). In the Mac example, I use nano; on Windows you can
use e.g. Notepad.
Page 34
100 Thomas Sales 5000
200 Jason Technology 5500
Þ Mac: <Control> O
Þ Mac: Enter
Þ Mac: <Control> X
3.2.2 Test Importing local file to Mysql in the Workbench
Þ Mac + Windows: Enter the following Text into MySQL Workbench:
SHOW VARIABLES LIKE "secure_file_priv" ;
-- This will give you the folder from where you can upload CSV files to the DB server.
CREATE DATABASE IF NOT EXISTS mydb; -- create new database
USE mydb; -- set current database
CREATE TABLE IF NOT EXISTS employees ( -- create table
emp_no INT NOT NULL,
first_name VARCHAR(14) NOT NULL,
department VARCHAR(14) NOT NULL,
Salary INT NOT NULL,
PRIMARY KEY (emp_no)
Page 35
);
SET SQL_SAFE_UPDATES = 0; -- update records without primary key value
DELETE FROM employees; -- in case you want to repeat the script
LOAD DATA INFILE <secure_file_priv> INTO TABLE <table_name>
-- adapt the path according to the value of variable secure_file_priv. E.g.:
LOAD DATA INFILE '/usr/local/mysql-8.0.21-macos10.15-x86_64/share/employee1.txt'
INTO TABLE employees; -- with LOAD command, importing data is much faster than in the graphical UI
Þ Execute
Þ (successfully imported local infile)
3.3 Importing fast using MySQL Shell util.importTable
• MYSQL Shell can import using Multithreading, which is very efficient.
• Install the newest version of MySQL Shell
• On Windows, make sure to use forward slashes “/” for file paths.
• Open Terminal and enter he following commands: (Example)
• Terminal> mysqlsh
• mysqlsh> \connect --mc root@localhost:3306
• mysqlsh> \sql SET GLOBAL local_infile = true; #allow local files
• mysqlsh> \sql SET GLOBAL connect_timeout=50000; #longer runtime
• mysqlsh> util.importTable("E:/import/steam_groups.csv",
{schema:"db", table:"tbl", dialect:"csv", skipRows:1, showPro-
gress:true, fieldsOptionallyEnclosed:true, fieldsTerminatedBy:";",
linesTerminatedBy:"\n", fieldsEnclosedBy:'"', threads: 8})
3.4 Visualization of MySQL with Tableau
• Download Tableau Desktop for Students
• Install Tableau Desktop
• Open Tableau Desktop
• Connect Tableu to your MySQL server
Add table or view, e.g. view “v” defined on MySQL as follows:
create or replace view v as
select p.Name, 'students' as attr, count(s.StudId) as val
from professors p
Page 36
join lectures l on l.givenBy = p.PersId
join attend a on a.LectId = l.LectId
join students s on s.StudId = a.StudId
group by p.Name
union
select p.Name, 'ects', sum(ects)
from professors p
join lectures l on p.persid = l.givenby
group by p.Name;
select * from v;
• In a work sheet, configure the pivot table to visualize the data. E.g.
Page 37
4 Demo database installation
4.1 Prerequisites
• Make sure that MySQL Server is locally installed (see “installation” above)
• MySQL Workbench is installed
4.2 Connection to MySQL
Open MySQL Workbench:
• You have/use the root use (local instance MySQL80 in the screenshot)
• Or you can add (no 1) the user you created during installation (e.g. HSLUUser). Enter a name for
the connection (no 2), add the user name (no 3).
When testing the connection (no 4), enter the pass-
word (no 5) and save the password in the vault (no
6).
• Click on one of these users (and enter the password
if not stored in the vault).
Page 38
4.3 Create a database
• Click the fourth symbol (no 1 in screenshot),
to create a database (or schema, respec-
tively)
• In the dialog, enter the name “course_admin”
for the database. Click “Apply” two times.
• In case of an error message, you might not
have permissions to create a database. The
reason will be that you are connected to the
wrong server (to the remote server in one of
the previous tasks; make sure you have in-
stalled MySQL locally and that you are con-
nected to that local server)
You should now see the database «course_admin» in
register «SCHEMAS» (no 2).
5 Runing scripts
• Open scripts with the menu File->Open SQL Scripts… (no 3) and run the
scripts.
• ALTERNATIVE for very large script files: File->Run SQL Script
• At the beginning, there is the state-
ment "USE course_admin" (no 4).
It makes sure that you run the
script in the correct database.
• Use the icon «Blitz» (no 5) or
CTRL+SHIFT+ENTER (for Win-
dows systems) to run all or se-
lected lines.
Page 39
5.1 Create your own scripts
• If you are not connected to the database, see details in section
4.2 and use Connection "root" or your own created user.
• You can create your own script with shortcut Ctrl+T (or with menu File->New Query Tab)
• Now, you have got an empty window.
• On the left side, you should see the database "course_admin" (no 1) in register “SCHEMAS”. If
that is not the case, you have not created the demo database (see section Fehler! Verweis-
quelle konnte nicht gefunden werden.).
• Change the context (or connection) to the demo database.
Enter "USE course_admin;" (no 2), select the text and click
CTRL+SHIFT+ENTER (or click the “Blitz” in the toolbox;
above (no 2)).
• In the area "Output", you should see a green icon if the
statement was successful. In case of errors, you will see a
red “stop” symbol and an error message (e.g. if you have
written an incorrect SQL statement).
• Important: Add a semicolon after each SQL statement (see
examples in the screenshots) to delimit several statements.
• You can write more SQL statements.
• With CTRL+SHIFT+ENTER you can run all statements in
the script or those you have selected. I recommend you that you select the SQL statement you
want to run.
• Save your script with CTRL+S (or with menu File->Save Script). You can open your script with
CTRL+SHIFT+O (or with menu File->Open SQL Scripts…).
Page 40
6 MySQL Manual
Based on the online Manual at https://dev.mysql.com/doc/workbench/en/, the following tutorials are es-
sential for working with MySQL workbench:
6.1 Installation on Mac:
• https://dev.mysql.com/doc/workbench/en/wb-installing-mac.html
6.2 Installation on Windows:
• https://dev.mysql.com/doc/workbench/en/wb-installing-windows.html
6.3 Creating a database connection:
• https://dev.mysql.com/doc/workbench/en/wb-mysql-connections-new.html
6.4 Creating a database model:
• https://dev.mysql.com/doc/workbench/en/wb-getting-started-tutorial-creating-a-model.html
6.5 Loading Data Into the Model:
• https://dev.mysql.com/doc/refman/8.0/en/load-data.html
6.6 DB queries in the SQL editor:
• https://dev.mysql.com/doc/workbench/en/wb-sql-editor-query-panel.html
Page 41
