How to Open MySQL Server Table on QGIS

Hello everyone, welcome to another QGIS Tutorial. On this article, we are going to learn about MySQL and QGIS connection. I am going to show you how to open MySQL Table in QGIS. Actually, this is not a spatially enabled MySQL database. My client has many data in their MySQL server. Most data is point data such as drill hole collar and their proposed drill hole location, rock, and soil sample location. They don’t have any spatial data. In this case, can we open MySQL database directly in QGIS?

Basically, QGIS supports various database types. We can open PostgreSQL, SQL Server, SQLite and also MySQL Server. In this example below, I am going to open some tables stored in MySQL Server.

Steps to Open MySQL Table in QGIS

Step 1. Create a connection to MySQL Database

Open QGIS and then go to Layer | Add Layer | Add Vector Layer. It will bring up Data Source Manager | Vector window as follow.

Select Database and then select MySQL from the database type. Click the New button to create a new MySQL connection.

On the New connection window, we need to provide some information:

• Connection Name —- could be anything. Make it easier to remember for you
• Host —- This is the MySQL hostname or IP address
• Database —- The MySQL database we are going to connect to
• Username —- MySQL username
• Password —- MySQL password

**Please note that you will need to enable remote connection to your MySQL Server. By default, MySQL only accepts a local connection.

Click OK and then it will return to the previous window. Now, Click the Add button and it will display the tables in your MySQL Database. Click tables that you want to open in QGIS. You can select more than one table at a time by pressing CTRL while you select the tables.

In a few moments, your tables should be shown in QGIS

Conclusion

Basically, we can open MySQL table directly from QGIS. We cannot store the spatial data/information to MySQL database without spatial extension. But, configuring the MySQL spatial extension will not be discussed in this article.