Installing MySQL 8 Zip Archive in Windows

Introduction

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

The advantage of installing zip archive version of MySQL server is that later if you find this server is not required for your application then it would be easier to remove the zip version of MySQL installation any time. You do not need to check in the registry for any uninstalled or unremoved files, in the system, that might exist if you install exe or msi version of MySQL server.

Prerequisites

MySQL 8.0.17 – 8.0.26, 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.xx, where xx is the any version from 17 to 26.
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-8.0.26/"
# set datadir to the location of your data directory
datadir="C:/my_data/data/"

Create a data directory as I mentioned in the above my.ini file.

Note that images below may be with the older version of MySQL server but the installation has been tested with the versions which are mentioned in Prerequisites section above.

Initializing MySQL Server

I am done with installation of MySQL server in the above section.

Now I 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.

Navigate to the root directory of MySQL server using command window. Then go to bin directory.
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.

Connecting to MySQL Server

Connect to MySQL server using command line tool as follows:

Open command window.
Navigate to the root directory of MySQL. Then to the bin directory.
Execute command mysql.exe -u root.

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

You can also use any MySQL server client (SQLyog, HeidiSQL, etc.) to connect the server.

Setting Password

You can also set password for your root user. In the above using MySQL client I have logged in without using password but it is not a good idea to keep it open for everyone without using password.

In the MySQL client execute the following command to set the password:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'your password';

In the above command you should replace the string your password by actual password you want to use for user root.

Execute the following command to reflect your changes permanently:

FLUSH PRIVILEGES;

Now exit your MySQL client using exit; command and restart your MySQL server and MySQL client.

To access again your MySQL database using MySQL client execute the following command:

mysql.exe -u root -p

The above command will prompt for your root user’s password and you can type and you will see the same window in MySQL client as you had seen while using without option -p in the command.

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 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;

Note that the declaration of size in int data type and autofilled have been deprecated in MySQL version 8. If you use these then MySQL server will show you warnings.

Hope you got an idea how to install MySQL zip version in WIndows system.

Roy Tutorials