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.
This works excellent i Linux but in the MacOs version of QGIS 3.4 (and above), there is no MySQL datatype option. Can you describe how to connect to MySQL in MacOs 10.15?
Hi Jan,
I am sorry, I don’t use Mac OS X so, unfortunately I cannot provide you a guide.
I’m intrested in configuring the MySQL spatial extension, can you provide me a guide?
doesn’t work for me in QGIS 3.14 windows 10…. BUT Mysql works 100% fine in MANIFOLD server and client network workplace… sharing data, editing etc is jut a breeze… But now I struggled using QGIS mysql connection…
Hi,
Personally I havent tried in qgis 3.14 yet.
Will give it a try.
There is no MySQL option in neither 3.12, 3,14 nor 3.16. 🙁
I have tested in 3.30 and 3.32 and in both cases once I have set up and successfully tested the connection I click ‘add’ and no popup appears to list available tables.
There is an Options section that appears on this page now that wasn’t in previous versions. It looks like it is designed to allow you to filter back the listed tables, but I have tried a ‘*’ filter and it doesn’t seem to help.
I have been able to add tables using previous versions of QGIS (I think I had this working in 3.14). I am considering downgrading. Tables that I added in previous versions and saved in .qgs file are still opening as they should. The problem is related to adding new layers.