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.26, 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.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
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.
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:
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.
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.
Hope you got an idea how to install MySQL zip version in WIndows system.