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.