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
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.
Jan PalménFebruary 19, 2020
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?
adminFebruary 22, 2020
I am sorry, I don’t use Mac OS X so, unfortunately I cannot provide you a guide.
lslJuly 9, 2020
I’m intrested in configuring the MySQL spatial extension, can you provide me a guide?
ianAugust 6, 2020
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…
adminAugust 6, 2020
Personally I havent tried in qgis 3.14 yet.
Will give it a try.
Janne PalménJanuary 22, 2021
There is no MySQL option in neither 3.12, 3,14 nor 3.16. 🙁