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:
Create a Database in 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.
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 operation, unzipping the 64-bit package into the
extras folder in the Manifold
installation folder.
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.
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.
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.
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.
Continue this case study on installing MySQL with the Connect to MySQL topic.
Jump to the beginning of the case study: Install MySQL
File - Create - New Data Source
Big List of Formats and Data Sources
Example: Switching between Manifold and Native Query Engines