MySQL, owned
by Oracle ever since Oracle's acquisition of Sun Microsystems, is one
of the three most popular enterprise-class DBMS packages. MySQL
is a high quality, genuinely enterprise-class DBMS that is both well known
and well respected.
Oracle provides MySQL in various editions, including a free, open source edition called MySQL Community Edition, which we use as the MySQL installation in this documentation. MySQL Community Edition may be deployed in production and used at no charge, including commercial use if desired. The high quality of MySQL Community Edition, very extensive and sophisticated feature set, lack of significant limitations, and exceptionally easy installation process make MySQL a favorite for GIS use.
MySQL Community Edition is similar to Oracle's commercial MySQL Enterprise Edition, except that unlike MySQL Enterprise Edition it is not supported by Oracle, has fewer updates and less frequent bug fixes, lacks a few utilities provided by MySQL Enterprise Edition, and is free.
The main page for MySQL downloads, including the free MySQL Community Edition, at the time of this writing is at https://dev.mysql.com/downloads/mysql/
This topic is the first step in a three topic sequence showing a complete installation and then utilization of MySQL from the very beginning, using a simplified approach intended to facilitate experimentation by individuals. A full enterprise installation should be undertaken by experts, to insure all required security facilities are correctly configured. The examples these topics show, especially in the last topic in the sequence, will be useful for working with an existing MySQL installation our organization may have.
There is no requirement to install
MySQL or any other DBMS for using Manifold. Manifold includes as
built-in capabilities everything you need to do Manifold. In general,
storing GIS data within Manifold .map project
files will be faster, and will deliver higher performance with greater
ease of use and more capabilities than storing GIS data within MySQL or
some other DBMS.
So why install or use MySQL? Manifold users often use MySQL or any other DBMS for three reasons:
Access to data - Organizations often use enterprise-class DBMS packages to store data. Being able to reach into organizational data provides tremendous access to data that can be combined with geospatial data in Manifold or in other systems, or to simply get access to a seemingly infinite range of data of all kinds in tables. Topics showing how to connect to a given database teach us how to access that data so we can use it.
Capabilities not in Manifold - While Manifold generally provides far broader geospatial capabilities than any DBMS, big DBMS packages such as MySQL have very many capabilities evolved over many years, including capabilities not found in Manifold. By running server-side queries we can take advantage of those capabilities.
Alternative data storage - Whatever storage infrastructure has been invented, there is a database that uses it. Connecting to different DBMS packages allows Manifold users to store their data in whatever data technology they like, such as cloud storage, massively distributed storage, and memory resident storage. MySQL, for example, provides options for high reliability, clustered storage.
Interchange with other applications - There are many other applications which can interact with data stored in a MySQL database. Using MySQL as a common data store allows easy interchange of data between those applications.
Multiuser, simultaneous use of GIS data - Manifold .map files at the present time do not allow simultaneous access by multiple Manifold sessions. If a .map file is open in one Manifold session it cannot be opened in another. If we require simultaneous access to data by multiple Manifold sessions, such as might be run by different people on different machines, we can store the data in a DBMS, such as PostgreSQL, Oracle, MySQL, SQL Server, DB2, or other DBMS.
Disclaimer:
These topics were accurate when written, using the versions of installation
software indicated. Third party packages can and do change, so this
topic may be out-of-date. It is provided as an example of how such
installations can be approached.
Installing MySQL and readying it for use in typical installations involves three steps:
Install MySQL
This is the first topic in the above sequence of steps.
We are using a 64-bit Windows 10 system to which we have connected via Remote Desktop (RDP). This entire topic was conducted on that machine via RDP. We have downloaded the Oracle installation package for MySQL from https://dev.mysql.com/downloads/mysql/ - the file we have downloaded and have now launched is called mysql-installer-community-8.0.15.0.msi, approximately 332 MB in size. A web installer is also available, which downloads installation files as need be.
We double-click the mysql-installer-community-8.0.15.0.msi file to launch the installer.
Check the I accept box and press Next.
For this example we will choose a simplified, but fully functional, installation. Click Custom and then press Next.
Expand the MySQL Servers heading, click on MySQL Server 8.0.15 - X64 to highlight it, and then press the green right arrow button.
That adds MySQL Server 8.0.15 - X64 to the list of products/features to be installed. Next, we expand the Applications heading, click on the MySQL Workbench 8.0.15 - X64 line to highlight it, and then again we press the green right arrow button.
That adds MySQL Workbench 8.0.15 - X64 to the list of products/features to be installed. Strictly speaking, we do not need to add the MySQL Workbench as we will not use it in these topics, but having the Workbench around for use is not a bad idea in any MySQL installation so we add it just in case it might be useful in the future. Press Next.
The dialog lists the products to be installed. Press Execute.
If we were using a web installer, at this point it would download the two products and begin installing them. We are using a local installer so it immediately just installs them. When finished, the Status will show as Complete and green check marks will appear by each product. Press Next.
The dialog moves on to Product Configuration, with only MySQL Server requiring configuration. Press Next.
We leave the default choice of a standalone MySQL Server installation. Press Next.
In the Type and Networking step we choose Development Computer, the default choice. This uses minimal memory, which is what we want since for the sake of this topic we are installing MySQL on a machine that hosts many examples. If the machine were to be used as a host for a big MySQL installation that was intended to serve many client computers on which people were doing GIS, connected to MySQL, we would choose either Server Computer or Dedicated Computer.
We leave checked the default choice of opening a port in Windows Firewall, thanking MySQL for the courtesy of doing this for us. Press Next.
We change the authentication method
to Use Legacy Authentication.
Tech Tip: MySQL Versions from 8.0 onward provide stronger authentication, but to make that stronger authentication work all clients which connect to a MySQL Server installed with the Use Strong Password choice must use 8.0 level client .dll files. In real life, many packages, for example GDAL, will install MySQL client .dll files that are older than 8.0 and which will not work with MySQL Servers installed with the Use Strong Password choice. If those .dll files appear in the Windows PATH ahead of more recent, 8.0 MySQL client .dll files, we can encounter the chaotic situation where connection attempts from remote machines will fail, and will do so in a highly confusing way. In other cases, users might be working with older versions of MySQL client .dll files that they have downloaded in the past and have failed to update. Those will not work either.
The easiest way to avoid such chaos is to install MySQL with the Use Legacy Authentication option checked. In that case, both older and newer MySQL client .dll files will work. Press Next.
Enter a password for the root account. We use a password of 12345xy, which is an absurdly weak password. In real life, use a stronger password. We will also add a user account by pressing the Add User button.
We add a user name Fred, also
using a weak password of 12345xy.
In real life, we would use a stronger password and a different
password than root. We
have made Fred a DB
Admin, so the Fred account
can do superuser things like creating and dropping databases. We
leave the Host setting at
the default of <All Hosts (%)>
and Authentication of MySQL.
Press OK.
Tech Tip: One of the quirks of MySQL is that a user account is not just the user's login name, such as Fred, plus a password, such as 12345xy. In addition, the machine from which the user connects to MySQL, called the Host, is also part of the set of credentials that allows or does not allow a given user to connect to MySQL. For example, the default superuser login of root works only when connecting to MySQL from the same machine on which MySQL is installed. If root tries to connect to MySQL from a different machine, say, a machine with the system name of PASCAL, the login will be rejected with a message (reported in the Manifold Log Window) of:
*** (root)::[MySQL] Error 1045 (28000): Access denied for user 'root'@'PASCAL' (using password: YES)
As far as MySQL is concerned, root trying to connect from the machine PASCAL is not the same root as trying to connect from the localhost. Lucky for us, the default setting of Host when adding a new user, as seen in the illustration above, is to specify the parameter %, which means "accept this user login from any host." User Fred, therefore, will be able to connect to MySQL from any other remote machine.
After adding Fred as a user, we again click Add User to add user Lucy, using the same settings as Fred. After doing that we get the display above. Press Next.
In the Windows Service dialog we accept all defaults. Press Next.
We get one last chance to go back and change any settings. Press Execute.
After all configuration settings are applied, press Finish.
The result is to go back to the Product Configuration dialog, with a note that configuration is complete. Press Next.
We do not want to launch MySQL Workbench, so we uncheck that box. Press Finish.
We have accomplished the installation of MySQL, and we now have an operating MySQL server running on our machine.
Continue this case study on installing MySQL with the Create a Database in MySQL topic.
Limits on Simultaneous Users? - MySQL does not limit the number of concurrent connections, but the Windows system on which it is installed may limit the number of concurrent connections. For example, if we install MySQL on a Windows 10 machine we will be limited by Windows, and not by MySQL, to a maximum of 20 connections. If we install on a Windows Server machine we can have more connections.
File - Create - New Data Source
Big List of Formats and Data Sources
Example: Switching between Manifold and Native Query Engines