Last Updated on January 14th, 2021 at 11:39 am
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.
MySQL 8.0.17/8.0.17, Windows 10 64 bit
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 or C:\mysql-8.0.22.
- 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/" or basedir="C:/mysql-8.0.22/" # 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.
- Navigate to the root directory of MySQL server using command window. Then go to bin directory.
- Execute command
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
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.
By default test database exists in the MySQL server.
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.
To see warnings if there is any, execute command
show warnings in the command line tool.
To drop a table from database use command
drop <table name>.
To truncate table execute command truncate
table <table name> in the command line tool.
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.