In the last post, we talked about the basics of SQL, it’s definition, advantages and disadvantages. In this tutorial, we will learn about MySQL that is one of the various SQL databases like PostgreSQL, SQLite, etc. The most basic step of learning something new is the installation. We will take a brief look at how to install MySQL on Windows.
Installing MySQL on Windows
The installation of MySQL on Windows can be a difficult task considering the number of steps required to complete it. I recommend you to follow the steps of the installation closely so you don’t face any problem. I’ll be using Windows 10 (64-bit). Following are the steps to install MySQL on your Windows system.
How to Install MySql Server?
The complete installation of MySQL Server passes through various stages, below are the steps we need to perform to install the MySQL Server successfully:
- How to Download MySQL Installer?
- How to Install MySQL Server?
- How to Set up Product Configuration for MySQL Server?
- Installing Samples and Examples
How to Download MySQL Installer?
1. Go to www.mysql.com.
2. Go to the Downloads tab present just beside the MySQL logo.
3. Once you are on the download page, you need to proceed to Community, which can be found on the bar below the MySQL logo.
4. Once you’ve got to the Community page, proceed to the MySQL Community Server.
Note: We are using the community version because, unlike the enterprise version, it is unpaid. The community version does lack a few of the features like Enterprise plug-in (Thread Pool, PAM, Audit, etc.) and also does not provide certification and indemnification support. You can choose between the two according to your choice after considering these differences.
5. Scroll down and you’ll find the list of previous versions of MySQL server compatible with Windows, but we will go with the latest. Instead of downloading everything bit by bit and then installing, I recommend you to use the MySQL Installer instead. This makes the installation lot easier and installs all the required packages. In case you don’t use it, you’ll have to install other things like the Debug Binaries and Test Suite, separately.
Once the OS is selected, click on Go to Download Page.
Note: You can notice that the same installer can be used for both the version, 32 bit and 64 bit. In the screenshot below you can also see the various operating systems available for installation of MySQL.
6. Once you click the MySQL Installer download, you’ll be directed to a new page, scroll down the page and you’ll find two different links to download MySQL Installer.
Note: There are two files available, the first one is mysql-installer-web-community, which is 15.8 Mb, the second one is mysql-installer-community, which is 230.0 Mb.
- Web: The web community file is the preferred way to download as it will give you the liberty to choose which product you want to install and then download them. Once you’ve selected the product you want to install, it will download the package corresponding to the package and then install it, thus saving storage and time.
- Full: Bundles all of the MySQL products for Windows (including the MySQL server), i.e., if there are 10 packages are available, the full version will first download all the packages and then ask you which one you want to install.
If you’re using the web version, you need the MySQL Shell and Command Line Client to run the queries apart from the Sever and connectors. For the purpose of the tutorial, I’m using the full version.
7. On the download page, you don’t need to sign up or log in, you may download it directly by scrolling down the page and selecting No thanks, just start my download.
8. Once downloaded, double-click the Installer to start the installation process.
How to Install MySQL Server?
1. Once you run the MySQL installer, it starts with the License Agreement page. You just need to check the checkbox with along I accept the license terms and click next.
2. In the next step, you need to choose the setup type, I’m using Developers Default in this tutorial because it installs products like MySQL Server, MySQL Shell, etc. which actually compliment the application development with MySQL.
3. Once you have decided the setup type, you’ll get to the next step which will show the additional requirements, like I need to install Python 2.7.0. These additional softwares are used to perform a variety of works later on, like Python 2.7.0 works as a connector and is further supports the new X DevAPI for development with MySQL Server 8.0. It may also include various other software and packages like Microsoft Visual C++ Redistributable, etc.
4. After you have installed the additional requirements, you’ll be directed to the Installation page where all the MySQL products are to be installed. It also looks for the latest versions available, so you may need to download the fresher versions, you just need to press the execute button and it will start to download all the packages that need to be installed.
As you press the execute button, the installer will download and install all the products.
5. Once the installation is complete, you’ll be able to click the next button.
6. After the installation, you’ll land on the product configuration window. This is the process of actually customizing the products according to your system to achieve maximum results.
How to Set up Product Configuration for MySQL Server?
1. The second part of the installation starts with Group Replication. There are two installation options available the Standalone MySQL Server / Classic MySQL Replication (default) and the Sandbox InnoDB Cluster Test Setup (for testing only). While the first one actually configures multiple SQL servers manually, the second one can only be used to create and configure sandbox InnoDB cluster instances for testing purpose. So, as we are using a single system to run and learn about the MySQL, we will use the Standalone MySQL Server / Classic MySQL Replication (default) as for now we will work on a single server for development using MySQL.
2. The Type and Networking show three different options: Development Computer, Server Computer, and Dedicated Computer. These three configurations are based upon the amount of system resources (memory) that will be assigned to your MySQL server instance. You can think of the three configurations as:
- Development: This configuration is suitable for a personal computer. Such a system will also run other applications like Google Chrome, Music Player, etc which a personal computer uses. thus the memory available has to be shared with other applications so this configuration uses the least amount of memory.
- Server: In Server Computer medium amount of memory is used. There will be some other application running on the system like a web server.
- Dedicated: When the machine is only dedicated to run the MySQL server, it is termed as a dedicated computer. This configuration uses the majority of the available memory and no major application, like a web server, is running on the computer.
For learning Basic SQL, I suggest using Development Computer configuration.
Apart from the Config Type, you may ask why am I using TCP/IP connectivity? Well, there is a pretty simple answer to that, the TCP/IP connectivity allows servers to be separated with a minimum headache in a long run. Shared memory may not run into firewall issues but in a longer term, separating severs in Shared Memory is simply a big task at hand. Named Pipe is not secure as anyone can access them using \ServerName\pipe\PipeName. So, TCP/IP stands out to be the best option.
3. Once you’re on the Authentication Method window, you can proceed using the default installation and selecting the Config type as Development Computer over the Legacy Authentication Method. On the Authentication Method screen, select the Recommended option if not selected, i.e., Use Strong Password Encryption for Authentication.
Using the Legacy Authentication Method puts the organization over risk as it only requires a username and password without additional features like multifactor authentication, anomaly detection or enterprise federation through single sign-on (SSO). With the modern strong Password Encryption, it is way secure to log in now.
4. In the next step, you’ll be asked to select a password for your root account, once you do that you can press the Next button.
5. After selecting a password for your root account, you can continue till Apply Configuration using the default installation options. On the Apply Configuration, you’ll need to add apply all the changes using the Execute button.
6. Once the execution is complete, you can move on using the Finish button.
Note: Here you can see that during the installation that it started the server, thus you don’t need to start the server every time, it will do that by default on itself.
Installing Samples and Examples
Here the installer actually installs few examples and samples which you can later use for your ease and also checks the connection to the sever. The examples can also be seen once you have started using the MySQL Command Line Client. You will find them later in the next tutorial.
1. Now you’ll be on the Server Connection screen, you’ll need to type in your password. The password should be the same as the one you selected on the Account and Roles screen. After you enter the password, click on the Check button.
2. Once the authentication is complete, you’ll get a new message as All Connections succeeded and then you can click on Next button.
3. Now you can continue through the Apply Configuration and then you can just finish the installation yourself with the final finish looking like this
How to Start/Run MySQL Server
So following these steps you can install MySQL on your Windows system. You can look at the various installed products like here are the two which open just after the installation:
While the MySQL shell is a command line interface, the MySQL Workbench is actually a graphical tool for working MySQL servers and databases. The Workbench mainly covers five functionalities which are:
- SQL Development
- Data Modeling (Design)
- Server Administration
- Data Migration
- MySQL Enterprise Support
We won’t go into the details of all these functionalities and we will be using the MySQL Shell or Command Line Client as they are query based.
MySQL Command Line Client
Once complete, open Start menu and search for MySQL Command Line Client. Run the command line and then you need to enter your password. After this, you can run the MySQL commands. It is a MySQL command line prompt where you can execute the MySQL queries directly.
We will discuss the usage and difference between MySQL Shell and Command Line Client in the next tutorial where we learn to create databases.
So now you have installed MySQL on your system. With this done, we can now proceed to run commands and learn to create databases.