$₹€£: Install MySQL Server 5.6 on Windows

Introduction

MySQL is a popular open source database and it can easily be installed on WIndows 7. This guide will walk you through the process for downloading, installing and configuring MySQL Server 5.6.X to work on computer intended to be used as a development box. We will update this article regularly as the installation process for MySQL Server 5.6.X changes.

Step 1: Download MySQL Server

Head over to the MySQL downloads page. We’ll be downloading the MySQL Community Server Edition and there should be a Download button for the Windows (x86, 64-bit) installer towards the middle of the page. This single installer will allow you to install the 32bit or 64bit version of MySQL Server. Press the download button as shown in the graphic below:

How to Install MySQL 5.7 and Get Started with SQL Programming

I want you to install MySQL on your own machine, because I want you to learn how to install, customize and operate complex industrial software system. Installation could be the hardest part in this exercise.

3.1 Step 1: Download and Install MySQL

For Windows

Download MySQL ZIP ARCHIVE from http://dev.mysql.com/downloads/mysql/:
1. Choose "General Available (GA) Releases" tab.
2. In "Select Platform", choose "Microsoft Windows".
3. Under "Other Downloads", download "Windows (x86, 64-bit), ZIP ARCHIVE" or "Windows (x86, 32-bit), ZIP ARCHIVE". (You can check whether your Windows is 32-bit or 64-bit from "Control Panel" ⇒ System ⇒ System ⇒ System Type.)
4. There is NO need to "Sign-up" - Just click "No thanks, just start my downloads!".
Create a project directory, say "d:\myProject" or "c:\myProject".
UNZIP the downloaded file into your project directory. MySQL will be unzipped as "d:\myProject\mysql-5.7.{xx}-winx64".
For ease of use, we shall shorten and rename the directory to "d:\myProject\mysql". Take note and remember your MySQL installed directory!!!

(NEW since MySQL 5.7.7) Initialize the database: Start a CMD (as administrator) and issue these commands:

// Change directory to the MySQL installed directory
// Suppose that your MySQL installed directory is d:\myProject\mysql
d:
cd \myProject\mysql\bin
 
// Initialize the database. Create a root user without password. Show the message on console
mysqld --initialize --console
......
...... [Note] A temporary password is generated for root@localhost: xxxxxxxx

During the installation, a superuser called root is created with a temporary random password, as shown above. TAKE NOTE of the root's PASSWORD, COPY and save it somewhere, and Take a Picture!!!!

If you make a mistake somewhere, delete the entire MySQL directory, and repeat step 2 and 3.

3.2 Step 3: Start the Server

The MySQL is a client-server system. The database is run as a server application. Users access the database server via a client program, locally or remotely thru the net, as illustrated:

The server program is called "mysqld" (with a suffix 'd', which stands for daemon - a daemon is a non-interactive process running in the background).
The client program is called "mysql" (without the 'd').

The programs mysqld and mysql are kept in the "bin" sub-directory of the MySQL installed directory.

Startup Server

For Windows

To start the database server, launch a CMD shell:

-- Change the current directory to MySQL's "bin" directory
-- Assume that the MySQL installed directory is "d:\myProject\mysql"
d:                       -- Set the current drive
cd \myProject\mysql\bin  -- Change Directory to the MySQL's bin directory
 
-- Start the MySQL Database Server
mysqld --console
......
......
XXXXXX XX:XX:XX [Note] mysqld: ready for connections.
Version: '5.6.xx-community'  socket: ''  port: 3306  MySQL Community Server (GPL)

Notes: The --console option directs the server output messages to the console. Without this option, you will see a blank screen.

Shutdown Server

For Windows

The quickest way to shut down the database server is to press Ctrl-C to initiate a normal shutdown. DO NOT KILL the server via the window's CLOSE button.

Observe these messages from the MySQL server console:

XXXXXX XX:XX:XX [Note] mysqld: Normal shutdown
......
XXXXXX XX:XX:XX  InnoDB: Starting shutdown...
XXXXXX XX:XX:XX  InnoDB: Shutdown completed; log sequence number 0 44233
......
XXXXXX XX:XX:XX [Note] mysqld: Shutdown complete

For Mac OS X

The EASY WAY: Via the graphical control. Click Apple Icon ⇒ System Preferences ⇒ MySQL ⇒ Stop.

WARNING: You should properly shutdown the MySQL server. Otherwise, you might corrupt the database and might have problems restarting it. BUT, if you encounter problem shutting down the server normally, you may kill the "mysqld" process in Task Manager (for Windows); or Activity Monitor (for Mac OS X); or System Monitor (for Ubuntu).

3.3 Step 4: Start a Client

Recall that the MySQL is a client-server system. Once the server is started, one or more clients can be connected to the database server. A client could be run on the same machine (local client); or from another machine over the net (remote client).

To login to the MySQL server, you need to provide a username and password. During the installation, MySQL creates a superuser called "root" with a temporary random password. I hope that you have taken note of this password!!

The MySQL installation provides a command-line client program called "mysql". (Recall that the server program is called "mysqld" with a suffix 'd'; the client program does not have the suffix 'd').

Let's start a command-line client with the superuser "root".

First, make sure that the server is running. See previous step to re-start the server if it has been shutdown.

For Windows

Start another NEW CMD shell to run the client:

-- Change the current directory to <MYSQL_HOME>\bin.
-- Assume that the MySQL is installed in "d:\myProject\mysql".
d:                       -- Change the current drive
cd \myProject\mysql\bin  -- Change Directory to YOUR MySQL's "bin" directory
   
-- Start a client as superuser "root"
mysql -u root -p
Enter password:
   // Enter the root's password set during installation.
   // NOTHING will be shown for maximum security.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.39-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
-- Client started. The prompt changes to "mysql>".
-- You can now issue SQL commands.

For Mac OS X

Open a NEW "Terminal" and issue these commands to start a MySQL client with superuser root:

-- Change the current directory to <MYSQL_HOME>\bin.
cd /usr/local/mysql/bin
 
-- Start a client with superuser "root"
./mysql -u root -p
Enter password:     
   // Enter the root's password set during installation.
   // NOTHING will be shown for maximum security. 
Welcome to the MySQL monitor.  Commands end with ; or \g.
......
mysql>
-- Client started. The prompt changes to "mysql>".
-- You can now issue SQL commands.

(Skip Unless...) Read "Common Problems in Starting the MySQL Client".

3.4 Step 5: Change the Password for the Superuser "root"

As mentioned earlier, the MySQL installation creates a superuser called "root" with a temporary random password. "root" is a privileged user that can do anything, including deleting all the databases. You are required to change the root's password after logging in.

Change the Password for "root"

Let's continue with our client session started earlier.

-- Change password for 'root'@'localhost'. Replace xxxx with your chosen password
-- (For my students: use xxxx as the password. Otherwise,
--   you will ask me what is your password next week.)
-- Take note that strings are to be enclosed by a pair of single-quotes.
mysql> alter user 'root'@'localhost' identified by 'xxxx';
Query OK, 0 rows affected (0.00 sec)
 
-- logout and terminate the client program
mysql> quit
Bye

Re-Start a Client as "root" with the New Password

We have just changed the password for root and exited the client. Start a client and login as root again. Enter the password when prompted.

For Windows

-- Change the current working directory to <MYSQL_HOME>\bin
mysql -u root -p
Enter password:    // Type your password and enter. NOTHING will be shown for security.
Welcome to the MySQL monitor.
......  
mysql>
-- client started, ready to issue SQL command

For Mac OS X

-- Change the current working directory to /usr/local/mysql/bin
cd /usr/local/mysql/bin
./mysql -u root -p
Enter password:    // Type your password and enter. NOTHING will be shown for security.
Welcome to the MySQL monitor.
......
mysql>
-- client started, ready to issue SQL command

3.5 Step 6: Create a New User

The superuser "root" is privileged, which is meant for database administration and is not meant for operational. We shall create a new user - let's call it "myuser" - with a lesser privilege. To create a new user, start a client with superuser "root":

-- Start a client, IF IT IS NOT STARTED
mysql -u root -p     // Windows
./mysql -u root -p   // Mac OS X
   
-- Create a new user called "myuser", which can login from localhost, with password "xxxx"
mysql> create user 'myuser'@'localhost' identified by 'xxxx';
Query OK (0.01 sec)
   
-- Grant permission to myuser
mysql> grant all on *.* to 'myuser'@'localhost';
Query OK (0.01 sec)
   
mysql> quit

Explanation

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'xxxx'
We use the command "create user" to create a new user called 'myuser'@'localhost', who can login to the server locally from the same machine (but not remotely from another machine), with password "xxxx".
GRANT ALL ON *.* TO 'myuser'@'localhost'
The newly created user has NO privilege to perform any database operation including select. We use the "grant" command to grant "all" the privileges (including select, insert, delete, and so on) to this new user on ALL the databases and ALL the tables ("on *.*"). This new user, in practice, has the same privilege as root, except that it cannot issue grant command. For production, you should grant only the necessary privileges on selected databases and selected tables, e.g., "grant select, insert, update on studentdb.*" - it can issue select, insert and update (but no delete, create/drop table) on ALL the tables of the database studentdb only.

3.6 Step 7: Create a new Database, a new Table in the Database, Insert Records, Query and Update

A MySQL server contains many databases. A database contains many tables. A table contains rows (records) and columns (fields).

Let's create a database called "studentdb", and a table called "class101" in the database. The table shall have three columns: id (of the type INT - integer), name (of the type VARCHAR(50) - variable-length string of up to 50 characters), gpa (of the type FLOAT - floating-point number).

CAUTION: Programmers don't use blank and special characters in names (database names, table names, column names). It is either not supported, or will pose you many more challenges.

TIPS: Before we proceed, here are some tips on using the client:

You need to terminate your command with a semicolon (;), which sends the command to the server for processing. E.g.,
```
mysql> select * from class101 ;
-- Send the command to the server for processing
```

You can use \c to cancel (abort) the current command. E.g.,

mysql> select * from class101 \c
-- abort (cancel) the command

A command can span several lines. The prompt for subsequent lines changes to ->. You need to terminate the command with a semicolon (;). E.g.,
```
mysql> select * 
    -> from class101
    -> 
    -> ;
-- A command can span several lines, ended with a semicolon.
```

If you open a single quote, without closing it, the prompt changes to '>. For example,

mysql> select 'xxx
    '> '\c        // close the single-quote and abort

You can use up/down arrow keys to retrieve the previous/next commands (from the history commands).
(For Windows) You should enable copy/paste functions of CMD shell. To enable copy/paste, click the CMD's icon ⇒ Properties ⇒ Options ⇒ Edit Options ⇒ Check "QuickEdit Mode". You can then select the desired texts and use a "right-click" to copy the selected text; another "right-click" to paste.

Let's start a client with our newly-created user "myuser".

-- Start a client, IF IT IS NOT STARTED
-- cd to the MySQL's bin directory
mysql -u myuser -p     // Windows
./mysql -u myuser -p   // Mac OS X
   
-- Create a new database called "studentdb"
mysql> create database if not exists studentdb;
Query OK, 1 row affected (0.08 sec)
   
-- list all the databases in this server
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| ......             |
| studentdb          |
| ......             |
+--------------------+
x rows in set (0.07 sec)
   
-- Use "studentdb" database as the default database
-- You can refer to tables in the default database by the tablename alone, instead of databasename.tablename
mysql> use studentdb;
Database changed
   
-- Remove the table "class101" in the default database if it exists
mysql> drop table if exists class101;
Query OK, 0 rows affected (0.15 sec)
   
-- Create a new table called "class101" in the default database 
--  with 3 columns of the specified types
mysql> create table class101 (id int, name varchar(50), gpa float);
Query OK, 0 rows affected (0.15 sec)
   
-- List all the tables in the default database "studentdb"
mysql> show tables;
+---------------------+
| Tables_in_studentdb |
+---------------------+
| class101            |
+---------------------+
1 row in set (0.00 sec)
   
-- Describe the "class101" table (list its columns' definition)
mysql> describe class101;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| gpa   | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)
   
-- Insert a row into "class101" table.
-- Strings are to be single-quoted. No quotes for INT and FLOAT.
mysql> insert into class101 values (11, 'Tan Ah Teck', 4.8);
Query OK, 1 row affected (0.03 sec)
   
-- Insert another row
mysql> insert into class101 values (22, 'Mohamed Ali', 4.9);
Query OK, 1 row affected (0.03 sec)
   
-- Select all columns (*) from table "class101"
mysql> select * from class101;
+----+-------------+------+
| id | name        | gpa  |
+----+-------------+------+
| 11 | Tan Ah Teck |  4.8 |
| 22 | Mohamed Ali |  4.9 |
+----+-------------+------+
2 rows in set (0.00 sec)
  
-- Select columns from table "class101" with criteria
mysql> select name, gpa from class101 where gpa > 4.85;
+-------------+------+
| name        | gpa  |
+-------------+------+
| Mohamed Ali |  4.9 |
+-------------+------+
1 rows in set (0.00 sec)
  
-- Update selected records
mysql> update class101 set gpa = 4.4 where name = 'Tan Ah Teck';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0   
 
mysql> select * from class101;
+----+-------------+------+
| id | name        | gpa  |
+----+-------------+------+
| 11 | Tan Ah Teck |  4.4 |
| 22 | Mohamed Ali |  4.9 |
+----+-------------+------+
2 rows in set (0.00 sec)
 
-- delete selected records
mysql> delete from class101 where id = 22;
Query OK, 1 row affected (0.03 sec)
   
mysql> select * from class101;
+----+-------------+------+
| id | name        | gpa  |
+----+-------------+------+
| 11 | Tan Ah Teck |  4.4 |
+----+-------------+------+
1 rows in set (0.00 sec)
 
-- You can store SQL commands in a FILE (called SQL script) and run the script.
-- Use a programming text editor to CREATE a NEW FILE called "mycommands.sql" 
--   containing the following three SQL statements.
-- (For Windows) Save the file under "d:\myProject".
-- (For Mac OS X) Save the file under "Documents".
insert into class101 values (33, 'Kumar', 4.8);
insert into class101 values (44, 'Kevin', 4.6);
Select * from class101;

-- Once you have created the file, you can use the following "source" command 
--   to run the SQL script.
-- You need to provide the full path to the script.
-- (For Windows) The filename is d:\myProject\mycommands.sql.
-- (For Mac OS X) The filename is ~/Documents/mycommands.sql
mysql> source d:\myProject\mycommands.sql   // For Windows
mysql> source ~/Documents/mycommands.sql    // For Mac OS X
Query OK, 1 row affected (0.00 sec)   -- INSERT command output
Query OK, 1 row affected (0.00 sec)   -- INSERT command output
+------+-------------+------+         -- SELECT command output
| id   | name        | gpa  |
+------+-------------+------+
|   11 | Tan Ah Teck |  4.4 |
|   33 | Kumar       |  4.8 |
|   44 | Kevin       |  4.6 |
+------+-------------+------+
3 rows in set (0.00 sec)

Exercises:

Select records with names starting with letter 'K'. (Hints: name like 'K%')
Select records with names NOT starting with letter 'K'. (Hints: name NOT like ...)
Select records with gpa between 4.35 and 4.65. (Hints: test-1 AND test-2)
Select records with names having a letter 'e'. (Hints: name like '%e%')
Select records with names having a letter 'e' or 'a'. (Hints: test-1 OR test-2)
Select records with names NOT having a letter 'e' or 'a'. (Hints: NOT (test-1 OR test-2))
Select records with names having a letter 'e' and gpa ≥ 4.5.

(Skip Unless... ) Read "Common Problems in Using the mysql Client".

3.7 More Exercises

Show all the databases.
Create a new database called "ABCTrading".
Set the "ABCTrading" database as the default database.
Show all the tables in the default database.

Create a new table called "products" with the columns and type indicated below.

+-------+----------+-------------+----------+---------+
| id    | category | name        | quantity | price   |
| (INT) | CHAR(3)  | VARCHAR(20) | (INT)    | (FLOAT) |
+-------+----------+-------------+----------+---------+
| 1001  | PEN      | Pen Red     |     5000 |  1.23   |
| 1002  | PEN      | Pen Blue    |     8000 |  1.25   |
| 1003  | PEN      | Pen Black   |     2000 |  1.25   |
| 1004  | PCL      | Pencil 2B   |    10000 |  0.49   |
| 1005  | PCL      | Pencil 2H   |     9000 |  0.48   |
+-------+----------+-------------+----------+---------+

Show the table description.
Insert the above records and list all the records.
List records with name containing "Pencil".
List records with price ≥ 1.0.
Increase the price of all items by 10%, and list all the records.
Remove "Pen Red" from the table, and list all the records.

4. Many-to-many Relationship

In a bookstore, a book is written by one or more authors; an author may write zero or more books. This is known as a many-to-many relationship. It is IMPOSSIBLE to capture many-to-many relationship in a SINGLE table with a fixed number of columns, without duplicating any piece of information! For example, if you organize the data in the table below, you will not know how many author columns to be used; and you need to repeat all the data for repeating authors.

The many-to-many relationship between books and authors can be modeled with 3 tables, as shown below. A books table contains data about books (such as title and price); an authors table contains data about the authors (such as name and email). A table called books_authors joins the books and authors tables and captures the many-to-many relationship between books and authors.

Exercises

Create a database called "mybookstore".
Use "mybookstore" as the default database.
Create 3 tables "books", "authors", and "books_authors" in the database "mybookstore", with column names and types as shown.
Insert the respective records into the tables. List the contents of each of the tables.

Try this query and explain the output:

SELECT books.title, books.price, authors.name
   FROM books, books_authors, authors
   WHERE books.isbn = books_authors.isbn
      AND authors.authorID = books_authors.authorID
      AND authors.name = 'Tan Ah Teck';

List all the books (title, price, qty) by "Tan Ah Teck" with price less than 20.
List all the authors (name and email) for the book title "Java for Dummies".
List all the books (title, price, qty) and all the authors (name and email) for books with title beginning with "Java" (Hints: title LIKE 'Java%').

5. Backup and Restore Databases

5.1 Backup via "mysqldump" Utility Program

You can use the "mysqldump" utility program to back up the entire server (all databases), selected databases, or selected tables of a database. The "mysqldump" program generates a SQL script that can later be executed to re-create the databases, tables and their rows.

For example, the following command backups the entire "studentdb" database to a SQL script called "backup_studentdb.sql".

For Windows

-- Start a NEW "cmd"
d:
cd \myProject\mysql\bin
mysqldump -u myuser -p --databases studentdb > "d:\myProject\backup_studentdb.sql"

For Mac OS X

-- Start a NEW "terminal"
cd /usr/local/mysql/bin
./mysqldump -u myuser -p --databases studentdb > ~/Documents/backup_studentdb.sql
     // ~ denotes the home directory of the current login user

Study the output file, which contains CREATE DATABASE, CREATE TABLE and INSERT statements to re-create the database and tables dumped.

5.2 Restore via "source" command in a mysql client

You can restore from the backup by running the "source" command in a MySQL client. For example, to restore the studentdb backup earlier:

For Windows

-- Start a MySQL client
d:
cd \myproject\mysql\bin
mysql -u myuser -p
-- Run the backup script to recreate the database
mysql> drop database if exists studentdb;
mysql> source d:\myProject\backup_studentdb.sql

For Mac OS X

-- Start a MySQL client
cd /usr/local/mysql/bin
./mysql -u myuser -p
-- Run the backup script to recreate the database
mysql> drop database if exists studentdb;
mysql> source ~/Documents/backup_studentdb.sql

6. Summary of Frequently-Used Commands

(For Windows) Starting MySQL Server and Client

-- Start the Server
cd path-to-mysql-bin
mysqld --console
 
-- Shutdown the Server
Ctrl-c
 
-- Start a Client
cd path-to-mysql-bin
mysql -u username -p

(For Mac OS X) Starting MySQL Server and Client

-- Start/shutdown the Server: 
-- Use Graphical Control
 
-- Start a Client
cd /usr/local/mysql/bin
./mysql -u username -p

Frequently-used MySQL Commands

MySQL commands are NOT case sensitive.

-- General
;           -- Sends command to server for processing (or \g)
\c          -- Cancels (aborts) the current command
 
-- Database-level
DROP DATABASE databaseName;                 -- Deletes the database
DROP DATABASE IF EXISTS databaseName;       -- Deletes only if it exists
CREATE DATABASE databaseName;               -- Creates a new database
CREATE DATABASE IF NOT EXISTS databaseName; -- Creates only if it does not exists
SHOW DATABASES;                             -- Shows all databases in this server
   
-- Set default database.
-- Otherwise you need to use the fully-qualified name, in the form 
--   of "databaseName.tableName", to refer to a table.
USE databaseName
   
-- Table-level
DROP TABLE tableName;
DROP TABLE IF EXISTS tableName;
CREATE TABLE tableName (column1Definition, column2Definition, ...);
CREATE TABLE IF NOT EXISTS tableName (column1Definition, column2Definition, ...);
SHOW TABLES;              -- Shows all the tables in the default database
DESCRIBE tableName;       -- Describes the columns for the table
DESC tableName;           -- Same as above
   
-- Record-level (CURD - create, update, read, delete)
INSERT INTO tableName VALUES (column1Value, column2Value,...);
INSERT INTO tableName (column1Name, ..., columnNName) 
   VALUES (column1Value, ..., columnNValue);
DELETE FROM tableName WHERE criteria;
UPDATE tableName SET columnName = expression WHERE criteria;
SELECT column1Name, column2Name, ... FROM tableName 
   WHERE criteria
   ORDER BY columnAName ASC|DESC, columnBName ASC|DESC, ...;
  
-- Running a script of MySQL statements
SOURCE full-Path-Filename

=======================================================================

https://www.ntu.edu.sg/home/ehchua/programming/sql/MySQL_HowTo.html

========================================================================

$₹€£

Wednesday, 22 June 2016