Connect to MySQL

MySQL is one of the three most popular enterprise-class DBMS packages.   This topic is the final step in a three topic sequence showing a complete installation and then utilization of MySQL from the very beginning.      Please see the Install MySQL topic for basic info on MySQL.

 

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

 

Create a Database in MySQL

 

Connect to MySQL

 

This is the final topic in the above sequence of steps.

 

So far, we have installed MySQL on our server machine.  We have created a database within MySQL called gisdb.  Now, we will use that database to show an example of copying and pasting data from Manifold into MySQL.

A Reminder: Install MySQL Client .dll

Every machine running Manifold which will be used to connect to MySQL must have the MySQL Client .dll installed.   If we want to connect to our MySQL server from a remote machine running Manifold, we must install the MySQL Client .dll on that remote machine.   That is easy to do, as was seen in the Create a Database in MySQL topic.

 

If we have teleported into this topic without reading the preceding topics in this case study, we should make sure to install client software as discussed above.

 

MySQL must be running on the server machine.   If we cannot drag and drop components into the MySQL database, check to make sure the MySQL DBMS is running.  Login to the server machine (perhaps using Remote Desktop), launch the Windows Control Panel - Administrative Tools - Services applet and see if the MySQL service (may be called MySQL80) is Running.   If not, right-click onto that service and choose Start to start it manually.

Connect to a Database within MySQL

We continue from the end of the Create a Database in MySQL topic.  We right-click into the Project pane to create a new data source.

 

 

Right-click into the Manifold part of the Project pane and choose Create - New Data Source.  

 

In the dropdown menu we then choose More... to launch the New Data Source dialog.

 

 

Enter a descriptive name, MySQL gisdb, and choose Database: mysql as the type.  Click the browse [...] button to launch the Database Login dialog.

 

 

Enter the IP address of the MySQL server machine, for example, 192.168.2.44.   If we are connecting from the same machine on which MySQL has been installed, we would enter localhost as the name of the server.

 

Choose Use login and password and then enter Fred as the login.  Enter the password we provided for the Fred login when adding that user in the Install MySQL topic.

 

Enter gisdb as the database.   

 

It is a good idea to check for errors by pressing the Test button.

 

 

If that does not report a connection, we should fix any mistakes.  When we see Connection established we press OK.

Security

The best option for secure database connections is to use integrated security, where the Windows login through which a connection is made is mapped into user accounts within the DBMS, to which all the power and sophistication of roles and privileges can apply.  When using integrated security, no passwords or other credentials appear within connection strings.  Most DBMS packages support the use of integrated security.  To keep the presentation as simple as possible, except for the SQL Server example, integrated security is not used in DBMS topics.

 

Other options leverage the extensive security facilities already built into Window and databases.   If we do not use integrated security, but instead create a data source that uses login and password credentials we should take advantage of Windows security to protect those credentials.   It should go without saying that if we have access to a database in a particular role, we must know the password for the database user login that we will use.   We therefore should not fear using that password in plain text in the connection string if we have protected our credentials using the usual Windows security features.

 

For example, if we do not want other users on our computer to open the project and to use our access privileges to reach into the database, then we should Windows security features to ensure other users on the machine do not have permission to use the .map file.   If they have permissions to launch the .map file, it does not matter whether the password is stored in the connection string as plain text or in encrypted form: they still get the same level of access to the database when they connect through the data source using our credentials.  

 

Another way to enforce security is to save the connection to the database in a Windows DSN file as a user DSN.  User DSNs are specific to a particular user.   We then create a data source in Manifold by using File: dsn as the type in the New Data Source dialog, so that no connection strings are stored in the .map file.  Using a DSN has the advantage that we need only specify desired access to that one file to protect credentials, instead of to every .map project file we create.

 

 

Back in the Database Login dialog, press OK.

 

 

Back in the New Data Source dialog we review the connection string just built for obvious errors, find none, and press Create Data Source.

 

 

A new data source called MySQL gisdb appears in our Project pane.  That data source is a connection to the database called gisdb within our MySQL DBMS.    

 

Note from the illustration above we have two data sources simultaneously active into the same remote MySQL installation.  One of them is a data source connected to the master database.  The other is a data source connected to the gisdb database.   If we like, we can create additional databases in the MySQL installation and create additional data sources connected to those additional databases.   We can save those data source as Favorite Data Sources so they are available to us with one-click convenience.   If we save a project with multiple data sources in it, when we next open that project those data sources will still be there, ready for use.

 

For now, since we will work only with the MySQL gisdb data source, we will click on the MySQL data source to highlight it and we press the Delete button to delete it.   This simply removes the MySQL data source from our project and does not delete anything in the MySQL server.

 

 

We can expand the data source and then expand the System Data folder to see the virtual tables added by Manifold as well as the many system tables used by MySQL to operate a database.  For example, the information-schema.COLLATIONS table lists collations available in the database.  We can double-click that table to open it.   The table uses gray background color because it is a report of collations the database provides and is read-only.

 

 

Next, we open another Manifold session (not illustrated) in which we have stored some drawings of Mexico using different projections.  We copy and paste those into our working session, as seen above.  We have pasted two drawings, each with its table.  One drawing shows Mexico using Latitude / Longitude projection, and the other shows Mexico using Pseudo-Mercator projection.   

 

In the illustration above, we have opened the Latitude / Longitude drawing, named MexicoLL.

 

 

To copy that drawing and its table into our MySQL database, we ctrl-click on MexicoLL and MexicoLL Table, to highlight them...

 

 

...and then we drag and drop the highlighted items into the MySQL gisdb portion of the project.

 

 

The new components appear in the MySQL database, with their names automatically converted into MySQL names and some new, virtual components, such as drawings, added.   In the illustration above, we have closed the MexicoLL drawing previously opened.

 

To provide a consistent, orderly, user interface, Manifold will automatically generate virtual components, such as folders, some Manifold system tables, and some automatically-generated drawings, that appear to be in the database but which are generated on the fly from metadata and are not a new, real table or other new item added to the database.   See the Real and Virtual Components topic for discussion and examples.

 

 

We double-click the gisdb.MexicoLL drawing within the MySQL database to open it.   It opens, still retaining the Style with which it was formatted within Manifold.  Copying drawings and their tables into MySQL saves the style those drawings use as well.   

 

 

 

 

We can switch to the Component tab of the Info pane to get information on the drawing we have opened.  Manifold has automatically specified the coordinate system within MySQL as required by MySQL.

 

 

We can copy another drawing into our MySQL database by ctrl-clicking on MexicoPM and MexicoPM Table to highlight them, and then we drag and drop the highlighted items into the MySQL gisdb portion of the project.    In the illustration above, we have closed the gisdb.MexicoLL drawing previously opened.

 

 

We double-click on the gisdb.MexicoPM drawing within the MySQL gisdb data source to open that drawing.    It opens, also still retaining the Style with which it was formatted within Manifold.

 

 

 

 

The Component tab of the Info pane shows information on the gisdb.MexicoPM drawing.   Manifold has automatically specified the coordinate system within MySQL as required by MySQL.

 

If we compare the above illustration to the analogous illustrations for the Info pane of the dbo.MexicoPM in the Connect to SQL Server topic, and the public.MexicoPM drawing in the Connect to PostgreSQL topic, we see a difference between how MySQL and SQL Server name indexes and how PostgreSQL names indexes.   SQL Server and My SQL require that an index name be unique within a table, but they both allow the same index name to be used in different tables.  If we have an index called Geom_x in one table we are free to use that same name for an index, Geom_x in different tables.  That is the same as in Manifold, so there is no need to rename an index when copying multiple tables to MySQL or to SQL Server that use the same name for an index.  Our drawing as seen above therefore uses the same Geom_x name for the spatial index that most Manifold drawings use by default.

 

PostgreSQL is different in that PostgreSQL requires index names to be unique throughout an entire database.  If one table in the database uses Geom_x as an index name, then no other table in the database can use Geom_x as a name for an index.   When Manifold copies multiple tables to PostgreSQL, if an index name is already used by one of those tables, Manifold will, on the fly, synthesize a new index name that does not conflict, automatically adjusting references to that index name in other components.

Use Components in the Database

We can work with components, like drawings and tables, saved into the database as if they were local components stored within our project.   So far, we have just opened and viewed the gisdb.MexicoLL drawing and the gisdb.MexicoPM drawing, but we can do more than viewing.  We can work with them as if they were resident within the project.

 

 

For example, we can Alt-click the province of Durango in the gisdb.MexicoPM drawing and immediately the attributes will appear in the Info pane.  We can click any segment or handle in that province to enable it for editing with the Coordinates tab, just as if it were a local drawing.

 

 

Or we can click on the Coordinates tab to show the coordinates, and thus enable the object for editing.

 

 

If we Ctrl-click the province of Chihuahua, it will immediately be selected and appear in red selection color, just like it would in a local drawing.   In the illustration above, we have switched back to the Project pane.

 

 

Open the drawing's table in the database, gisdb.MexicoPM Table, and we see that the corresponding record for Chihuahua is also selected and shown in red selection color, just as it would be with a local drawing's table.

Next Steps

We have finished the last topic in the case study on installing MySQL.

 

See the preceding topic: Create a Database in MySQL

 

Jump to the beginning of the case study: Install MySQL

 

See Also

Schema

 

Project Pane

 

Info Pane: Component

 

Layers Pane

 

Info Pane

 

File - Create - New Data Source

 

Command Window

 

Real and Virtual Components

 

DBMS Data Sources - Notes

 

Install MySQL

 

Create a Database in MySQL

 

Install SQL Server

 

Install PostgreSQL

 

Install Oracle

 

Big List of Formats and Data Sources

 

Example: Switching between Manifold and Native Query Engines