If you regularly write applications that connect to a database server, knowing how to install the MySQL database system on your computer will be very helpful in case you need to test it.
The steps to install MySQL, the basic settings for you to use MySQL on Windows as well as access to the remote database will be detailed in this article. Invite you to follow!
Conditions when deploying MySQL
Before deploying a MySQL database in a work environment, administrators must estimate the number of users, the frequency of queries to the database, and estimate how much data is loaded. database over time, and a lot of other things like that.
Because these factors are different for each deployment, depending on the system, the system requirements when installing MySQL will also vary. MySQL database installations for businesses often require 4-core CPU, 8GB of RAM, RAID setup to read and write on the database faster. However, in the case of a local install, you don’t really have to worry about that.
A trial MySQL can be installed on most Linux, Windows, and Mac distributions.
Download the MySQL Installer installation
Test MySQL installed on Windows
Installing the experimental MySQL on the computer is quite quick and easy, you only need to accept the agreement of use by default, choose the type of setting is Developer Default and press next.
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 on this list, everything is fine, you can click Next and Execute.
Set up MySQL
MySQL installation instructions are available for SQL Server. Choosing the right settings for a test server setup is very important. For this type of server, make sure you choose Standalone MySQL Server / Classic MySQL Replication.
Next, for Server Config Type, choose Development Machine. Keep default settings (TCP / IP port 3306) for Connectivity.
On the next screen, you set the admin password and select Add User to add new users to this database. Suppose you add a user Remote_User, giving this user administrative rights to the database and setting a password.
In the next step, hold Configure MySQL Server as a Windows Service and Start the MySQL Server at System Startup is turned on. Run the service with Standard System Account. Press next Skip to the document archive step and click Execute to finish the setup process. If there are any configuration options for other products that you don’t need to change, you can skip them by clicking next or finish.
Set up a 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 table, 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.
Right-click on a space under “world” and select Create Schema > name 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 the SQL query icon on the menu and pasting the following:
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 the lightning icon to execute the code above. To check if a table has been created, look for the table name in SCHEMAS Tables, right-click on the table name and select Select Rows – Limit 1000.
You will see the Test data table.
Set up remote access to the 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 the mysql.exe file by opening cmd and navigating to: C: Program Files MySQL MySQL Server 5.X bin and enter:
mysql -u root -p
It will ask for the admin password you have set above, in this command prompt, enter:
GRANT ALL PRIVILEGES ON *.* TO 'Remote_User'@'' IDENTIFIED BY ''
If everything is correct, you will see the Query OK message.
Finally, check the remote connection. On any computer on the network, with MySQL Workbench installed, open it up, under the menu Database choose Connect to Database.
In this configuration window, select TCP / IP for Connection Method, enter the IP of the computer containing the installed and configured SQL database, to Port The default is 3306, change Username Fort 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 that the MySQL Workbench successfully connects to the remote MySQL database and can browse through the schemas or run queries on it.
A few other notes
Remember, connecting remotely to a MySQL database on a LAN only requires the above settings. If you have any problems with your 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 so that requests from the remote server through this port to the correct IP of MySQL Server. Please refer to how to install Port Forwarding on Router if you don’t know it yet.
The value of having a test database on a PC or a PC on a LAN, allows you to develop applications in scenarios that are more similar to the real world. Operating the database on the exact same machine will tell you if the code has been written correctly, but it does not tell you about other application testing situations related to network connectivity or security, etc. Checking the database on a remote computer will help you create queries and database applications in a more strategic and accurate way.
Hope the article is useful to you.
- Install SQL server 2016 from the Installation Wizard (Setup)
- Instructions for installing MS SQL Server
- How to install Python on Windows, macOS, Linux
- Instructions for installing Windows Server 2012 in detail step by step