Create a Database in MySQL

MySQL is one of the three most popular enterprise-class DBMS packages.   This topic is the second 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 second topic in the above sequence of steps.

 

In the prior topic, we have installed MySQL.  We will now connect to the MySQL installation from a remote machine.   We will use Manifold, and issue commands in Manifold that will create a database in our MySQL installation.

Install MySQL Client .dll

Every machine running Manifold which will be used to connect to MySQL must have the MySQL Client .dll installed.   The easiest way to do that at the present time is to visit the Product Downloads page on the Manifold website and, from the DLLs for Popular Open Source DBMS Packages section, to download the extension-dlls zip package for 64-bit and/or 32-bit operation, unzipping the appropriate package into the bin64 and/or bin folder of the Manifold installation folder.

 

It is possible that future Manifold builds may include native MySQL client software as a built-in part of Manifold, eliminating the need to manually install the MySQL client .dll.

Connect to MySQL

Launch Manifold.

 

 

Choose File - Create - New Data Source.  

 

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

 

 

Enter a descriptive name, MySQL, 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.

 

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 appears in our Project pane.  That data source is a connection to our MySQL Server installation.    We can now use that connection to issue commands within that data source, which will take effect with the full power and privileges of our Fred login.

Create a Database

We often use the word database casually, as a synonym for Database Management System (DBMS), or, in the case of file databases, in the sense of connecting to a particular database file, such as a particular GPKG database file or a particular Microsoft Access database .mdb file.  

 

In this topic we use the special MySQL meaning of database, to mean a collection of tables, queries and other infrastructure grouped together under a name within a particular MySQL DBMS installation.   When connecting to a MySQL installation, MySQL allows us to specify the particular database within that server to which we want to connect.   If we do not specify a particular database, we will simply connect to the overall master database by default.  

 

We could, in theory, connect to and use the default master database without ever creating specific databases.  But that would be disorderly, somewhat like keeping hundreds of files in a Windows system in the root of the C: drive without ever organizing those hundreds of files into sensible folders.   It is much more orderly and efficient to create one or more databases within which we group tables and other components.  For example, within our MySQL installation we might create a database called gisdb within which we will keep all of our GIS data.

 

 

In the Project pane we right-click on the MySQL data source and choose New Command Window.

 

 

That launches a Command Window that operates in the context of the MySQL data source, that is, within the MySQL installation.  Whatever SQL and other commands we enter into that command window will be executed within the MySQL installation using the native SQL of MySQL.

 

We enter the command

 

CREATE DATABASE gisdb;

 

And then we press the ! button to run the command.  

 

 

We have now created a new database, called gisdb, within the MySQL server.  In the next topic, we look at an example using the gisdb database we have created.

 

Notes

Check for existing databases - We can see what databases already exist in the server by running the SHOW DATABASES; command in the Command Window.

 

Deleting a database - If we make a typographical error when creating a database or if we simply want to delete a database we used for experimentation, we can open a command window on the MySQL data source and enter a command such as:

 

DROP DATABASE gisdb;

 

That will instantly delete the database and all it contains.  MySQL is designed for experts, so when issuing commands like that there are no confirmation dialogs: give a command to drop a database and MySQL immediately and irreversibly deletes that database.  Handle with care.

 

Next Steps

Continue this case study on installing MySQL with the Connect to MySQL topic.

 

Jump to the beginning of the case study: Install MySQL

 

See Also

Schema

 

DBMS Data Sources - Notes

 

Project Pane

 

File - Create - New Data Source

 

Command Window

 

Real and Virtual Components

 

Install MySQL

 

Connect to MySQL

 

Install SQL Server

 

Install PostgreSQL

 

Install Oracle

 

Big List of Formats and Data Sources

 

Example: Switching between Manifold and Native Query Engines