If you often write applications that connect to database servers, it helps if you know how to install a MySQL database on your Windows machine for testing purposes.
Connecting to a database with most applications usually just involves a specific connection string, so testing your application with one database server and then switching it later to a “production” server is very simple.
In this article, we’re going to walk you through the steps of setting up your own local MySQL database on any Windows PC. Later in the article you’ll also learn how to set up that test database for remote connections, so you don’t have to connect to it from the same computer that you’re doing your programming on.
MySQL Database Prerequisites
Deploying a MySQL database in a production environment is a science in itself. Database administrators usually look at number of users, how frequently calls will be made to the database, an estimation of how much data will be loaded into the database over time, and things like that.
Since most of those factors are different for every implementation, system requirements are different each time. Enterprise installations usually require 4 CPU cores, 8GB of RAM, and a RAID setup for fast read/writes to and from the database. However, in the case of a local install, you don’t really have to worry about all of that.
A test MySQL database can be installed on almost any flavor of Linux, Windows, or Mac. Review the list of supported platforms if you’re at all concerned. Download the Windows MySQL installer, which includes everything you need.
Installing Your Test MySQL Database on Windows
A local installation of a test MySQL database like this is very fast and easy. Just accept the License Agreement, then choose the Developer Default setup type, and click Next.
You may see a few items in the “Check Requirements” box of products that can’t be installed because you don’t have existing software on your system.
So long as the MySQL server and the Workbench software are not on this list, you’re fine. You can click Next, and then Execute.
MySQL Server Setup
The installation wizard comes with a setup wizard for the SQL Server. Choosing the right settings for your test server setup is important. For this type of server, make sure you select Standalone MySQL Server / Classic MySQL Replication.
Next, for Server Configuration Type, select Development Machine. Leave the default settings (TCP/IP port 3306) for Connectivity. Our guide to networking terms may come in handy here if you need a refresher.
On the next screen, set your root password, and then click Add User to add a new user to this database. We’re going to call this user “Remote_User”, give the user DB Admin rights, and set the password.
On the next step, leave Configure MySQL Server as a Windows Service and Start the MySQL Server at System Startup both enabled. Run the service under the Standard System Account. Click Next through the document store step, and then Execute to finish this stage of the setup. If there are any other configuration options that come up for other products, you can just bypass those without any changes for now by clicking Next or Finish.
Setting Up Your Test Database
Once you’re finished, the wizard should automatically launch the MySQL Workbench application. This is where you’ll create your database and test table, and set up remote access. First, you need to create your test database by creating a new schema. In Workbench, you’ll find this in the lower left corner of the screen.
Right-click in the white space under “world” and choose Create Schema. Give it any name you like, and click Apply. Right click on this new schema and select Set as Default Schema. Next, create a table in this database called “Test_Table”. To do this, click on the SQL query icon in the menu and paste in the following SQL script.
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 script. This will create a test table with one value. You can confirm it was created by drilling down to the test table in the schema explorer, right-clicking on the table, and selecting Select Rows – Limit 1000.
You’ll see the table with your test data.
Set Up Remote Access to Your MySQL Database
The final step is to configure your new MySQL database to allow remote access, for a specific user and from a specific IP address. We’ve already created “Remote_User” for this purpose, and gave it a secure password.
To configure remote access, launch myslq.exe by opening a command prompt and navigating to C:\Program Files\MySQL\MySQL Server 5.X\bin and type:
mysql -u root -p
It will require that you type in the root password you’ve already set up. In this command window, type in:
GRANT ALL PRIVILEGES ON *.* TO 'Remote_User'@'' IDENTIFIED BY ''
You should see a “Query OK” message if the command worked successfully.
Finally, it’s time to test the remote connection. On any other computer on your network, install and lauch MySQL Workbench. Under the Database menu, select Connect to Database.
In this configuration window, you’ll want to choose TCP/IP as the connection method and type in the IP of the computer where you set up and configured your SQL database. You can leave the port at default 3306. Change the Username to “Remote_User” and type “test” for Default Schema.
When you click OK, if you’ve set up everything as described in the article above, you’ll see that MySQL Workbench successfully connects to the remote MySQL database, and can browse the schema or run queries against it.
Keep in mind that remotely connecting to a MySQL database on a local network (LAN) only requires the configuration above. If you have any issues with the LAN setup, don’t forget to troubleshoot your network connection between the two computers first.
However, if you’re hoping to access your MySQL database over the internet, you’ll have to add a port forwarding rule in your internet router so that requests over this port from the remote computer get routed properly to the MySQL server IP. Our guide to port forwarding may help here.
The value of having a test MySQL database locally or on the same LAN as your development PC is that it lets you develop your applications in a near real-world scenario. Running a database on the same exact machine where you’re developing your code will work, but it doesn’t let you test application issues that might result from network communications, security issues, and more.
Testing against a dedicated remote PC running your database will let you develop your queries and your database applications more strategically and accurately for the intended use.
Have you ever had to set up your own MySQL database on a network or any other remote node? What sorts of issues did you come across? Share your own experiences in the comments section below!