MySQL Server and MySQL WorkBench installation

Use- MySQL server setup

Solution :

Downlaod the commmunity server and workbench from below URL:

1)      Install MySQL Community Server - zip file

2)      Install MySQL workbench. - You can install the workbench using a zip file or an msi installer (recommended)

1. Installation of MySQL community Server

1. Extarct the Zip file
2. Navigate to the Bin folder of MySQL and Type cmd in explorer
3. key in c:\\bin\mysqld --initialize --console and it will generate the temporary password and make a note of it.
4. Start the Server : c:\\bin\mysqld  --console  and make a note of port number i.e 3306 (deafult port)
5.  Install workBench and Open the database connection to connect the server , key in some name for connection, port number , password to connect.
6. Once it's connect to server, ask for password change.
7. That's it!.

Reference :

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 network, as illustrated:

1. 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).
2. 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.

Start a "Client"

For Windows

Start Another NEW CMD shell to run the client (You need to keep the CMD that run the server):

-- Change the current directory to MySQL's binary directory.
-- Assume that the MySQL is installed in "c:\myWebProject\mysql".
c:
cd \myWebProject\mysql\bin
   
-- Start a client as superuser "root" (-u), and prompt for password (-p)
mysql -u root -p
Enter password:   // Enter the root's password set during installation.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 8.0.xx
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 such as SELECT, INSERT and DELETE.

Startup Server

For Windows

To start the database server, launch a new CMD shell:

-- Change the current directory to MySQL's binary directory
-- Assume that the MySQL installed directory is "c:\myWebProject\mysql"
c:
cd \myWebProject\mysql\bin
 
-- Start the MySQL Database Server
mysqld --console
......
......
XXXXXX XX:XX:XX [Note] mysqld: ready for connections.
Version: '8.0.xx'  socket: ''  port: 3306  MySQL Community Server (GPL)

Note: The --console option directs the 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
(You may need to press ENTER to get the command prompt?!)

Changing 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 macOS, there is no need to change the password, but there is no harm trying it out)
-- (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 in MySQL.
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 directory to MySQL's binary directory
c:
cd \myWebProject\mysql\bin
-- Start a MySQL client
mysql -u root -p
Enter password:   // Enter the NEW password
Welcome to the MySQL monitor.
......  
mysql>
-- client started, ready to issue SQL command

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

Reference URL:

http://www3.ntu.edu.sg/home/ehchua/programming/sql/mysql_howto.html