Develop a single host multiple client MYSQL database

Please forgive me if the title is not correct. I am still trying to understand how this works.

I have installed MySQL workbench and the MySQL server on my laptop. I have successfully created a database that store driver details, as per the picture.

I can access this info only one my laptop. However, I would also like to access this info from other PC’s. I was thinking of having the main database in a secured room and then connecting about 3 additional PCs to the main database.

I have also written a simple c# script on Visual Studio that runs on my main laptop where the database is stored. This script just allows a user to enter his username and password and then displays the contains of the database (the table in the database) on a datagrid view. I have included the SQL injection code to prevent unwanted characters. This application also allows the user to insert, update, and delete info from the database provided the user that logs in has these privileges granted.

Now I would also like to run this application on the other 3 PC’s, but obviously I have a problem. This problem is these PC’s cannot access the main database. The main database user has privileges that allow him to alter the database but the other uses that are located in the access points to do not have this privilege. All they can do it enter their username and password and see the data from the database been displayed on the datagrid view.

So my question is how do I grant access to these other three PC’s to access the main database? MySQL Workbench and MySQL server is so far only installed on my laptop and not on the other 3 PC’s.

Database users

Error message

Answer

The entire point of database technology is to allow multiple clients to access the same server and share the same data. (Actually there’s another point: the ability to handle vast amounts of data. But that’s not your problem right now.)

Here’s what you need to do.

1. Get MySQL server software set up on a server in your server room. Find out the hostname of that server.
2. Log in to that server using MySQL Workbench. It asks you for the hostname.
3. Use Workbench to migrate your data from your localhost MySQL server to the one in the server room. Workbench has decent features to help you do that.
4. Create a mysql account on the shared server that just has access to the database you just created. Keep in mind that MySQL users look like this: 'david'@'localhost' or ‘david'@'*' or 'mickey'@'*.animation.disney.com'. That is, they specify both the username and the machine the user runs on.
5. Change your C# connection string to mention the shared server’s hostname and the account you just created.

myConnectionString = "server=shared.example.com;uid=root;pwd=12345;database=test";

Somehow tell the other users of your app to use this connection string.

There’s another, simpler but less robust, way to do all this. Make sure your laptop has its own hostname. I dunno, david.example.com maybe? Ask your local LAN or VPN administrator. Or just use your laptop’s IP address in place of a hostname. Then put that hostname into your connection string. Then other users of your application can hit the MySQL server on your laptop. But, if you switch off your laptop and take it home they’ll lose access.