MySQL is one of the most popular DataBase Management Softwares (DBMS), it is used for storing, manipulating, and managing data in a database using SQL queries. MySQL is typically used for web application development so it has a large community of developers who can help and answer questions. The main advantages of MySQL are that it is easy to use, inexpensive, reliable ( since 1995) and has a high performance and speed data processing that help enterprise users build scalable database applications. This tutorial, is a step by step guide on how to download, install and setup MySQL community server on your Windows system for local development purposes.
- How to install MySQL Workbench on mac OS.
- Why is Connection Pooling better than Single Connection?.
- How to create MySQL database using node.js.
- How to store Session in MySQL Database using express-mysql-session.
Now, scroll down then click on MySQL Community (GPL) Downloads:
One more time, select and click on Mysql Community Server
After that, the following page will appear, now you can select your operating system, Microsoft Windows. In the Recommended Download click on Go to Download Page>.
This page will propose two download options: The first one is the MySQL installer web community “.msi” file which will first install the downloader file then will download the MySQL installation files. The second option is the direct MySQL installer community and that what we need. So let s download the second one by clicking Download.
To skip the login and sign up step click on No thanks, just start my download And that will start your download .
When the download is done, double click the installer to lunch the installation process. Then allow MySQL to make changes on your computer and accept the license terms then click Next.
The next step will ask you to choose a setup type , select Custom because this option gives you the possibility to select and install only the products needed and should be installed in the next step. In this tutorial we will select MySQL Server and MySQL Workbench.
To select MySQL sever version, click on MySQL Server then select the latest version of MySQL. in this example the version is MySQL Server 8.0.22 – x64 . Note that the installer will give you automatically the option to install.
Make sure to click the green arrow Botton to confirm the selection.
Then select Applications and do the same for MySQL Workbench.
For the check requirements step, It Depends on your needs, if you need to install one of those requirements, check the required software and click execute, accept the terms then click Next .
Next, click Execute to start MySQL Server and MySQL workbench installation, It may take some time.
When the installation is done, it is time to start MySQL Server configuration: for the configuration type select Development Computer and keep all default values. You will see a yellow mark if the port is already used so you can change it and click Next.
For the authentication check the recommended one and use a strong password for your MySQL then click Next.
The default user is root so we need to set a password for the root account. Enter you password for this account and make sure to save it, because you will need it later. You can also add a new use in this step by clicking Add User if you want. when finish click Next.
Keep All the default values for the Windows Service configuration and click Next.
Click Finish to apply the configuration and complete the installation.
Once the MySQL installation is completed, open start menu , select MySQL in all programs and click on MySQL Command Line Client. Enter your MySQL password that you created earlier.
How to add MySQL to the path in Windows
In order to interact with MySQL Server and manipulate your databases from your Command Line, you need fist to add MySQL to your path. For that you need to have MySQL installed and your Command Line setup (you can see here How to setup your Command Line on Windows). Then follow this steps:
- The first thing to do is to find the path to mysql.exe in your computer. It is usually located in C:\Program Files\MySQL\MySQL Server 8.0\bin. Notice that the name of MySQL Server 8.0 directory can change depend on your MySQL Server version.
- Next , Right click on My Computer and click on Properties.
- Then click Advanced system settings and select Advanced.
- Go to Environment Variables, then System variables .
- In system variables select the path variable and click on Edit.
- keep the previous variable value and add to it the path to MySQL ( C:\Program Files\MySQL\MySQL Server 8.0\bin ), Make sure to separate them with ‘;’ .
- After that, click OK to validate the changes that you just made.
- finally, restart your Command Line application.
MySQL Commands Line Windows
After Adding MySQL to your path, you can verify the status of your MySQL Server in you Command Lineby typing:
To start your mysql server run the command :
To stop the server use this command:
In order to access the root of your database and MySQL Server type the command:
mysql -u root -p
Enter your device password then your MySQL password that we created earlier. A “mysql>” console will appear in your Terminal, that means that you can now interact directly with your database.
To get a list of all MySQL commands use the command help. To use this commands make sure to start line with the command_name and finish it with ‘;’.
The first thing we need to do is to create a new user rather than the root user. For that we type the command :
mysql> CREATE USER ‘username’@‘localhost’ IDENTIFIED BY ‘userpassword’;
To see all the users we just use the command:
mysql> SELECT user FROM mysql.user;
To give the new user ‘username’ the privileges of an admin to have the full access when interacting with MySQL database:
- First, type this:
mysql> GRANT ALL PRIVILEGES ON * . * TO ‘username’@‘localhost’;
- Then you need to flush all the privileges :
mysql> FLUSH PRIVILEGES;
- To check the privileges use:
mysql> SHOW GRANTS FOR ‘username’@‘localhost’;
- The command to delete a user is:
mysql> DROP USER 'username'@'localhost';
- Create database
mysql> CREATE DATABASE databasename;
- Show all the databases:
mysql> SHOW DATABASES;
- In order to create a table, we need first to select a database ‘databaseName’ then create the new table ‘Newtable’ :
mysql> USE databaseName;
mysql> CREATE TABLE Newtable ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, position varchar(255) NOT NULL, wage INT NOT NULL, 'password` varchar(255) NOT NULL, 'PRIMARY KEY (id), is_current_employee INT DEFAULT 1);
- To show all the tables in our database :
mysql> SHOW TABLES;
- If you want to delete a table or database use the command DROP with the name of table or database that you want to delete:
mysql> DROP DATABASE databaseName;
mysql> DROP TABLE tableName;
- To quit mysql use the command quit or exit.