Introduction

We will see here how to install MySQL zip archive in Windows operating system. We will download MySQL community version for showing this example. We will also see how to connect to MySQL server using default user root.

Prerequisites

MySQL 8.0.17, Windows 10 64 bit

Installing MySQL

Go through the following steps in order to install MySQL server.

  • Download MySQL community version from the page. Make sure to download the one that is under Other Downloads: Windows(x86, 64) Zip Archive.
  • Extract the zip into physical drive location. Let’s say under drive C. So the MySQL root directory is C:\mysql-8.0.17.
  • Create my.ini file under MySQL root directory.
  • Put below content into my.ini file.
[mysqld]
# set basedir to your installation path
basedir="C:/mysql-8.0.17/"
# set datadir to the location of your data directory
datadir="C:/my_data/data/"
  • Create a data directory as we mentioned in the above my.ini file.

Initializing MySQL Server

We are done with installation of MySQL server in the above section.

Now we will initialize the MySQL server. Initialization can be done in two ways: secure and insecure ways.

Go through the following steps to initialize MySQL server for the first time.

  1. Navigate to the root directory of MySQL server using command window. Then go to bin directory.
  2. Execute command mysqld.exe --initialize-insecure

Once initialization successfully done, you will see few directories and files created under the data directory.

Starting MySQL Server

  • Go to the root directory of MySQL server using command window tool. Then to the bin directory.
  • Execute command mysqld.exe.
install mysql zip archive in windows

Connecting to MySQL Server

Connect to MySQL server using command line tool as follows:

  1. Open command window.
  2. Navigate to the root directory of MySQL. Then to the bin directory.
  3. Execute command mysql.exe -u root.
install mysql zip archive in windows

Now you can execute MySQL query. You can switch to database. You can create new database. You can create table under database.

Common Operations

By default test database exists in the MySQL server.

Creating Database

To create database in MySQL server execute the command create database <database name> in the command tool.

Switching to Database

To switch to another database execute command use <database name> in the command line tool.

Showing Warnings

To see warnings if there is any, execute command show warnings in the command line tool.

Dropping Table

To drop a table from database use command drop <table name>.

Truncating Table

To truncate table execute command truncate table <table name> in the command line tool.

Creating Table

To create a table use below example, adjust according to your requirement:

With Collate at Column

CREATE TABLE `employee` (
  `emp_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `emp_first_name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `emp_last_name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `emp_mgr_id` int(11) DEFAULT NULL,
  `emp_designation` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Without Collate at Column

CREATE TABLE `user` (
	`user_id` int unsigned NOT NULL AUTO_INCREMENT,
	`login_username` varchar(100) NOT NULL,
	`login_password` varchar(255) NOT NULL,
	`last_login` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`user_id`),
	UNIQUE KEY `login_email_UNIQUE` (`login_username`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

Thanks for reading.