Objectives:
In this laboratory you will:
INFO20003 Week 1 Lab: MySQL software setup
• Access MySQL Workbench
Copyright By PowCoder代写 加微信 powcoder
• Access a MySQL Server
• Disable Safe Updates in Workbench
• Change your INFO20003 Labs server password
• Familarise yourself with communication tools we’ll use in this subject
The below flowchart shows the overall process for the lab, refer back to this if you get lost at some point.
InstallMyUniApps Software
I’ll stream a virtual desktop environment using myUniApps
How do you want to access Workbench?
I’ll use an on-campus c om pu te r
I want to install Workbench on my device
Part 1: Access MySQL
Follow instructions for BYOD install of Workbe nch
Disable ‘Safe Updates’ in Workbe nch
Part 2: Access MySQL
Follow instructions for BYOD install of MySQL Server
Do you want to install a local MySQL server?
Follow Instructions to access INFO20003 MySQL Server
Change your
Password on INFO20003 server
No thanks, the provided server is OK
Figure 1: Lab1 Flowchart
INFO20003 L-Wk1 1
Learn about the
communication platforms we’ll use in INFO20003
Introduction: Overview of MySQL, MySQL Server & MySQL Workbench
MySQL Server is a database management system (DBMS). It is a multithreaded (many things can go on at once) multi-user (many users can connect to the server at once) relational database that is largely compliant with the SQL ISO standards. This is the database that will store your tables and data and on which you will use to learn SQL – Structured Query Language.
MySQL Workbench is a Graphical User Interface (GUI) tool that can be used to connect to and work with a MySQL server. It provides an integrated development environment for database design, database administration, SQL code development and basic database migration tools. In this subject we will be using most of these features of MySQL Workbench – at first the database design tool for modelling and then the tool for SQL code.
Part 1: Gaining access to MySQL Workbench
Options for Accessing Workbench You can access workbench in one of three ways:
a) Install MySQL Workbench on your personal device (recommended) [Further installation instructions are below]
b) Use MyUniApps to access a virtual machine (which has MySQL Workbench installed). [https://students.unimelb.edu.au/campus-
life/campuses-and-facilities/elearning-and-it/myuniapps ] c) Use the University’s computers in the building
INFO20003 L-Wk1 2
Option (a): BYOD Install of Workbench
If you’re using Option (b) or (c), skip this section
Workbench is available on Windows, macOS and Linux systems. If you’re interested in installing MySQL Workbench, it is available from the MySQL downloads pages:
http://dev.mysql.com/downloads/workbench/
When downloading, select “No thanks, just start my download” to avoid making an Oracle account. Troubleshooting:
• If installation fails for any reason or the installed product will not open, please download version 8.0.15 of Workbench from here: https://downloads.mysql.com/archives/workbench/
• If you using MacOS and you get an error similar to ‘cannot verify malware free’ when trying to open the application, open finder and navigate to the application in your apps folder > right-click > open (should give you an additional option to ‘open anyway’).
Turning Off Safe Updates
MySQL Workbench tries to prevent you from making mistakes by running in “safe update” mode. However, safe update mode is very strict, and it isn’t necessary in this subject where we are not dealing with real-world, valuable data.
To disable safe updates, toggle the option in Edit > Preferences > SQL Editor (for macOS, MySQL Workbench menu > Preferences > SQL Editor):
INFO20003 L-Wk1 3
Figure 2: Safe Updates is turned off
You may have to scroll down the preferences window to see the “Other” section to disable safe updates.
It is important that you exit and reload MySQL Workbench, and reconnect to any server that you were connected to (See Part2 of the lab), so that the preference changes take effect. This is true for all preference changes.
INFO20003 L-Wk1 4
Part 2: Gain Access to a MySQL server
By now, you have access to a working install of MySQL Workbench (on your own pc or a university computer). ‘MySQL Workbench’ is not a database itself, but rather a frontend interface for accessing a ‘MySQL server’ (the database!).
You have several options for accessing/setting up a MySQL server for use in this subject:
a) Use the MySQL server that the University provides for students (Easiest setup)
b) Install MySQL server on your pc and run it locally (Works offline + allows you full control over database)
We recommend setting up option (a) first as a fallback and to change your password, and then try option (b) if that interests you!
Here’s a brief comparison of the two options:
USE UNIVERSITY SETUP
Extra software required? #”None needed [aside from VPN]
Multiple schemas + full control? %$No (only one schema + minimal control)
INSTALL SERVER ON YOUR DEVICE
%$Need to install Server + run it on startup #”Yes
Ease of install/setup/troubleshooting?
“#Easier $%Potentially more difficult
Vpn + network required?
$%Requires VPN + network connectivity “#Once set up, works offline
INFO20003 L-Wk1
Server Option (a): The INFO20003 Labs Server
VPN Install
The server provided for INFO20003 is only available for use within the university network, or via the University’s VPN (Virtual Private Network).
To gain access to the University’s VPN you need to follow the instructions at https://studentit.unimelb.edu.au/wireless-vpn/vpn and follow the instructions for your Operating System. You MUST use the Cisco VPN, Forticlient VPN will not work.
Ensure the VPN is running & connected before continuing.
Connecting to the INFO20003 lab server
Now that the VPN is installed + connected, we will walk through how to add the INFO20003 lab server & change your password. TASK 1: Create a connection to the INFO20003 server in Workbench
1. Open MySQL Workbench and click the ‘+’ symbol next to ‘MySQL Connections’ as shown below:
Figure 3: MySQL Workbench welcome screen indicating ‘+’ button for new connection
INFO20003 L-Wk1 6
2. A window appears to setup your new connection. Fill in your connection details as follows:
Connection Name: You can specify any connection name you like, but you must specify a connection name. (e.g. “INFO20003 lab server”).
Hostname: info20003db.eng.unimelb.edu.au
Port: 3306
Username: Use your University of Melbourne username (the username you use to log into the LMS). If your username has capital letters, please use lowercase instead.
For example, ‘ ’ has an LMS username of ‘jsmith’, so her database username will similarly be:
Password: The default password is in the format of: username_YYYY
where YYYY is the current year. In the year 2021 Joanne’s password would be:
jsmith_2021
You will need to change your password (see task 2).
Default Schema: The Default Schema is the same as your username (important)
Figure 4: MySQL Workbench’s Setup New Connection dialog
INFO20003 L-Wk1 7
3. Click “Test Connection”. If the connection is correct you will see the following dialog box:
Figure 5: A successful connection
If you received this message, proceed to Task 2. Else, try the troubleshooting methods below. Note that the Mac version of Workbench only has one generic error message, so try all troubleshooting methods.
An error similar to:
“Cannot Connect to Database Server”
• Ensure you are connected to the Cisco VPN (see few pages up) or are on campus
• Ensure that you typed in the hostname + port correctly in the connection details
An error similar to:
“Access denied for user (using password: yes)”
• Please try using the previous year in your password (eg username_2020 if the year is 2021, this is likely needed if you enrolled early for the subject)
• Ensure you are using your LMS username (not just first initial + lastname)
• Ensure that your username is all in lower case, and include any numbers from your
LMS username
• Ensure you are using the password with correctly substituted year in place of ‘yyyy’
INFO20003 L-Wk1 8
• If you enrolled within the last 48 hours your account may not yet be created, please wait 48 hours for account creation. If after this time you have tried all above troubleshooting steps, email the head tutor to check on your account status.
INFO20003 L-Wk1 9
TASK 2: Test your saved connection to the MySQL Server.
1. Click on the connection that you created previously (e.g. “INFO20003 lab server” in image below)
Figure 6:MySQL Connections showing the new MySQL database connection
INFO20003 L-Wk1 10
You should get a window as shown below. Switch to the Schemas tab in the left-hand panel. You will only see one schema. Its name will be your username, and it should be shown in bold. If the name is not shown in bold automatically (without you clicking on it), you didn’t enter the “Default Schema” correctly when setting up the connection in task 1 at step 2. Go back and enter your username in the default schema section of the connection setup.
Figure 7: The MySQL Workbench query window with the schemas tab open and bold username.
INFO20003 L-Wk1 11
TASK 3: Change your password
First log into the server and make sure you can see the query window as shown in task 2. Decide on a new password (let’s say we’ve chosen your-new-super-secure-password-123 as our password), and then change to this new password by entering the following command into the query window (including the apostrophes):
SET PASSWORD = ‘your-new-super-secure-password-123’; Note that your password is case sensitive.
Now press Ctrl+Enter (⌘+Return on Mac), or the leftmost ‘lightning bolt’ button on the toolbar, to run the query.
Figure 8: The lightning button executes all statements in the query window
After running this command, you will receive confirmation in the ‘Output’ window at the bottom of Workbench that the password change was executed.
To test if your password was successfully changed, close the connection by closing workbench all together, and then try to reconnect to the DB server. Instead of connecting, Workbench should request a password. Use your new password (whatever you typed in the quotes, e.g. your-new-super-secure-password-123 if you ran the command exactly as given above) and click ‘save in vault’ so you don’t have to re-enter it!
WARNING: Do not forget your new password. MySQL is a REAL DBMS, and does not have a ‘password reset’ functionality that you can access remotely. If you do forget, you will have to request a password change from the head tutor, which can take up to 48 hours (or do a BYOD install of MySQL Server on your own computer, see ‘Server option b’ below)
INFO20003 L-Wk1 12
INFO20003 L-Wk1 13
Server Option (b): BYOD MySQL Server Install
This section is optional if you have completed option (a), in which case you can Skip to Part 3
In addition to MySQL Workbench, you may choose to additionally install MySQL Server on your own computer. Note that we do not provide technical support / troubleshooting for this product, eg resetting passwords etc.
Installing MySQL Server allows you to carry around your data with you on your own device, meaning you do not need to connect to our info20003db server through the University VPN to do your work in this subject. Installing your own server also allows you to have full control over the server, including creating multiple database schemas and changing user permissions – these actions are not required for this subject, but you may find them useful at some point during semester.
MySQL Server is supported on Linux, macOS and Windows operating systems.
MacOS and Linux installation:
Server can be downloaded from this link: https://dev.mysql.com/downloads/mysql/
Be sure to download MySQL Community Server version 8.0. When downloading, it is advised to select “No thanks, just start my download” rather than signing up for an Oracle account.
Important note for macOS users: Be sure to carefully note down the password that is shown to you during the installation process. If you forget the password, please read the appendix for help resetting it.
To start your MySQL server: on macOS you will need to open ‘System Preferences’, select the MySQL Server icon, and start the MySQL Server. On macOS you can select to start the database each time you start your machine.
Windows installation:
Server installer can be downloaded from this link (you can download either the ‘web’ or ‘full’ installer):
https://dev.mysql.com/downloads/windows/installer/
INFO20003 L-Wk1 14
Launch the downloaded file and follow it through to “add” a new product. When you reach the “Select Products and Features” screen, choose the X64 version of the server:
INFO20003 L-Wk1 15
Continue stepping through the wizard, accepting the default options until you get to the “Accounts and Roles” page.
Your username for this server will be root. You need to select a password for this root account. You don’t need to create any other user accounts.
Keep clicking through the wizard, accepting the defaults.
When you install the MySQL Server install on Windows it will create a MySQL Server service. This means your database server daemon (the process that allows the database server to start) will run each time you start your Windows machine.
INFO20003 L-Wk1 16
Connecting to your database from Workbench
When the install process is complete, try connecting to the server using Workbench in a similar manner to how you connected to the info20003 database in option (a) previously. This time, use the following parameters:
• hostname 127.0.0.1
• port 3306
• username root
• password is the password that you entered into the installation wizard
• default schema blank
Creating a new schema
You will need to create a schema to work with. To do so, right click in the schemas window and click ‘Create schema…’ , then follow the wizard. You will need to either click this schema to select it (the name turns bold) each time when connecting to the server, or you can change the connection settings to set ‘default schema’ to the name of this new schema
INFO20003 L-Wk1 17
Part 3: Communication tools we’ll use this semester
Once you’ve finished Parts 1 & 2, take some time to read about and set up the communication tools that we’ve provided for students this semester.
TASK 1: Go to canvas and read the “communication tools for students” page. The page is located at:
Canvas > Modules > “Welcome” > “Communication Tools for Students” TASK 2: Try using the tools! Consult your tutor if you get stuck or have other questions.
End of Lab
INFO20003 L-Wk1 18
Appendix A: macOS Password Reset for MySQL Server If you need to reset your MySQL Server root password on macOS, follow these steps:
1. In System Preferences, stop the MySQL server service. 2. Open a Terminal window.
3. In the Terminal window execute the following commands:
$ cd /usr/local/mysql/bin
$ sudo ./mysqld_safe –skip-grant-tables
4. You will then be prompted to enter a password. This is the password for the administrator account on the Macintosh – NOT the MySQL account generated as part of the install.
5. Open a second (new) Terminal window and execute the following commands:
$ cd /usr/local/mysql/bin
$ ./mysql -u root
mysql> FLUSH PRIVILEGES;
mysql> ALTER USER IDENTIFIED BY ‘newpassw0rdh3r3’; — your new password in quotes mysql> EXIT
6. Start the MySQL server service in System Preferences.
INFO20003 L-Wk1 19
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com