Instructions for installing MySQL on Windows and remote access

If you regularly write applications that connect to the database server, it is helpful to know how to install the MySQL database on your computer in case you need to test.

Steps to install MySQL, the basic settings for you to use MySQL on Windows as well as access to remote databases will be presented in detail in this article by TipsMake.com. Please follow us!

Conditions when deploying MySQL

Before deploying the MySQL database in the working environment, administrators must estimate the number of users, the frequency of queries to the database, estimate how much data is loaded. database over time, and many other types of problems like that.

Because these factors are different for each deployment, depending on the system, the system requirements when installing MySQL will be different. Enterprise MySQL database installations often require 4-core CPUs, 8GB of RAM, and RAID settings to read and write on the database faster. However, in the case of local installation, you don't really have to worry about those things.

A test MySQL can be installed on most Linux, Windows, and Mac distributions.

Download the MySQL Installer version

Install MySQL test on Windows

The installation of MySQL testing on computers is quite fast and easy, you just need to accept the user agreement by default, select the type of setting as Developer Default and click Next .

Instructions for installing MySQL on Windows and remote access Picture 1

There are several items in Check Requirements that cannot be installed because you do not have the corresponding software on your computer. As long as MySQL Server and Workbench are not in this list, everything is fine, you can click Next and Execute.

Instructions for installing MySQL on Windows and remote access Picture 2

Setting up MySQL

MySQL installation instructions are available for SQL Server. It is important to choose the correct settings for the test server settings. For this type of server, make sure you choose Standalone MySQL Server / Classic MySQL Replication .

Instructions for installing MySQL on Windows and remote access Picture 3

Next, for Server Config Type , select Development Machine . Keep the default settings ( TCP / IP port 3306 ) for Connectivity.

Instructions for installing MySQL on Windows and remote access Picture 4

On the next screen, you set up the admin password and select Add User to add a new user to this database. Suppose you add the Remote_User user, grant this user administrative rights to the database and set a password.

Instructions for installing MySQL on Windows and remote access Picture 5

In the next step, keep Configure MySQL Server as a Windows Service and Start the MySQL Server at System Startup is turned on. Run the service using Standard System Account . Click Next to go through the document storage step and click Execute to finish the setup process. If there are any configuration options for other products that you do not need to change, you can ignore them by clicking Next or Finish .

Set up test database

When the installation process finishes, the program will automatically open the MySQL Workbench application. This is where you will create the database and test tables, set up remote access. First, you need to create a test database by creating a new schema. In Workbench, you will find this option in the lower left corner.

Instructions for installing MySQL on Windows and remote access Picture 6

Right-click on the white space under "world" and select Create Schema > name the schema> Apply . Right-click on this new schema and select Set as Default Schema . Next, create a Test_Table table in the database, by clicking on the SQL query icon on the menu and pasting it into the following paragraph:

 CREATE TABLE Test_Table ( id smallint unsigned not null auto_increment , name varchar ( 25 ) not null , constraint my_example primary key ( id ) ) ; INSERT INTO Test_Table ( id , name ) VALUES ( null , 'Test data' ) ; 

Click on the lightning icon to execute the above code. To check if the table has been created, find the table name in the Tables section of SCHEMAS, right-click on the table name and select Select Rows - Limit 1000.

Instructions for installing MySQL on Windows and remote access Picture 7

You will see the Test data table.

Instructions for installing MySQL on Windows and remote access Picture 8

Set up remote access to MySQL database

The final step to setting up MySQL is to allow remote access, for certain users and from certain IP addresses. In the previous section, we created Remote_User with a secure password for this purpose.

To configure remote access, launch mysql.exe file by opening cmd and navigate to: C: Program FilesMySQLMySQL Server 5.Xbin and enter:

 mysql -u root -p 

It will require you to enter the admin password you have set up above, in this command window, you will continue to enter:

 GRANT ALL PRIVILEGES ON * . * TO 'Remote_User' @' ' IDENTIFIED BY ' ' 

If everything is correct, you will see a Query OK message.

Finally, check the remote connection. On any computer on the network, with MySQL Workbench installed, open it, under the Database menu select Connect to Database.

Instructions for installing MySQL on Windows and remote access Picture 9

In this configuration window, select TCP / IP for Connection Method , enter the IP of the computer that has the SQL database installed and configured, leave the default Port of 3306 , change the Username to Remote_User and enter "test" for Default Schema .

When you click OK, if you set up everything as described in the article, you will see MySQL Workbench successfully connected to the remote MySQL database and can browse through the schema or run the query on it.

A few other notes

Remember, remote connection to the MySQL database on the LAN only requires the configuration set above. If you have any problems with LAN setup, check the network connection between the two computers first.

If you want to access the MySQL database via the Internet, you will have to add Port Forwarding on the router to request from the remote server through this port to the correct IP of the MySQL Server. Please refer to how to install Port Forwarding on the Router if you do not know.

The value of having a database test on a personal computer or a computer on a LAN allows you to develop applications in a scenario that is much closer to the real world. Operating the database on the same machine will correctly tell you the code has been written correctly, but it does not indicate other application testing situations related to network connection or security, . Check Searching the database on a remote computer will help you create queries and apply the database in a more accurate and strategic way.

Hope the article is useful to you.

See more:

1. Install SQL server 2016 from the Installation Wizard (Setup)
2. Instructions for installing MS SQL Server
3. How to install Python on Windows, macOS, Linux
4. Instructions for installing Windows Server 2012 step by step