Q&A | How to import layers into MySQL database and open with QGIS

Today I want to answer a question: how to import a shape file into MySQL, and open it with QGIS and display it in the map window.

01 Tool software

QGIS: 3.16.6

MySQL: 8.0.25.0

GDAL: 3.3.0

02 Prepare MySQL

MySQL installation package download address: https://dev.mysql.com/get/Downloads/MySQLInstaller/mysql-installer-community-8.0.25.0.msi

Choose to install all and follow the prompts to complete.

Find MySQL Workbench 8.0 from the start menu and open:

Create a user for importing data, such as "newuser", and set the parameters as follows:

Authentication Type: standard;
Limit to Hosts Matching:%;
Set and confirm the password;

Click [Apply] to create a user.

Create a new database for storing data. Click the

button on the toolbar, set the name to: "qgistest", click the [Apply] button to create the database.

03 Prepare GDAL

GDAL 3.3.0 download address: https://download.gisinternals.com/sdk/downloads/release-1928-x64-gdal-3-3-0-mapserver-7-6-3.zip.

Unzip the downloaded zip archive, enter the "...\ release-1928-x64-gdal-3-3-0-mapserver-7-6-3\" directory, double-click "SDKShell.bat" to run the GDAL command line, At the prompt, enter the following command to check the GDAL version to see if it is running normally:

ogr2ogr –version

If the GDAL version is output normally and no error is reported, the GDAL installation is complete.

04 Import shape data to MySQL

Store the shape file that needs to be imported into MySQL in a path without Chinese characters and spaces, such as the path used in this article: "D:\data\world.shp", run the following command in the GDAL command line:

ogr2ogr MySQL:qgistest,host=localhost,user=newuser,password=123456 D:\data\world.shp -nln world -update -overwrite

The description of each part of the command line is as follows:

ogr2ogr: GDAL data conversion command;

MySQL: qgistest: connect to the qgistest database under MySQL;

Host: database host, if MySQL is installed on this machine, just use localhost, if on the network, you need to enter the IP address of the host;

User: the user name to log in to the database;

Password: login database password;

D:\data\world.shp: The absolute path of the Shape file to be imported, modify it according to your own file path, without spaces or Chinese characters;

-nln world: The name of the new layer is world, and the name of the layer can be drawn up according to your needs;

-update: open the data source in update mode;

-overwrite: Delete the existing layer with the same name and recreate it.

If the error "ERROR 1: MySQL connect failed for: test,host=localhost,user=newuser,password=XXXXXXXXXXXXX denied for user'newuser'@'%' to database'test'" appears when running the above command, as shown below :

Open MySQL Workbench 8.0->Users and Privileges, select "newuser", switch to "Scheme privileges" on the right, click the "Add Entry" button, add Scheme permissions, and then click the "Select ALL" button below, click "Apply" to The user grants all permissions:

05 QGIS open data

Open QGIS, click the

Data Source Manager button in the toolbar, switch to [Vector] -> [Database] -> [Connect] -> [New], set the connection parameters as follows:

Type: MySQL;
Name: any character;
Host: localhost;
Database: qgistest;
Authentication -> Basic -> Username/Password: newuser/123456, and check the [Storage] checkbox;

The details are as follows:

Click [OK] and click the [Add] button below to close the data source manager and return to the main QGIS window. At this point, the "qgistest world" layer has appeared in the layer panel, but the map window still does not display graphics. Right-click the "qgistest world" layer -> [Properties] -> [Source] -> [Query Builder], and enter the following expression in the query construction dialog box:

select * from world;

After clicking the [OK] button twice, you should be able to see the world administrative division map in the map window.

06 written at the end

Due to various restrictions, the QGIS team's current development of the MySQL database is almost at a standstill. Using the method in this article, the data can be imported and displayed, but the layer cannot be edited and modified directly. Therefore, if you need to use QGIS to edit data, it is recommended to use other databases for storage.


Copyright Notice

This article welcomes reprinting, please indicate the source when reprinting.