LESSON

Connecting to MySQL

Description

Learn how to connect Ignition to a MySQL database from the Config section in the Gateway.

Video recorded using: Ignition 8.0

Transcript

(open in window)

[00:00] In this lesson, we'll take a look at how to connect Ignition to a MySQL database. MySQL is one of the more popular database offerings, and one that Ignition can connect to without too much trouble. So to connect to MySQL, I'm in the config section of my gateway web page and from here in the sidebar, I'll go ahead and find databases and then go to connections. Now currently, I don't have any database connections at all. So to change that, I can click Create new Database Connection here. And once I click that, I'll be given a couple of different connection types to choose from based on the JDBC drivers I have installed on this system. In layman's terms, a JDBC driver is basically a bridge between Ignition's Java based platform and a database server. And the one that I'd like to use is MySQL because I'm going to be connecting to a MySQL database. However, I am going to run into a bit of trouble here when I click Next. I'm getting a banner up at the top here that says the MySQL driver is missing required files. Now you may or may not be seeing this error when you try this out on your own system. The reason I'm seeing this error here is because as of Ignition 8.0 we cannot ship Ignition with the MySQL JDBC driver pre-installed. However, if you're running Ignition seven nine or below, or you've upgraded to Ignition 8 rather than using a fresh Ignition eight install, you likely won't encounter this problem. Since we only have this limitation on fresh installs of Ignition 8.0 and above. Still I'll need to fix this error before moving on. We can see that the banner is directing me to a help page that includes information about installing the necessary files. So I'll go ahead and open that. And if I go to that browser tab, it's actually a user manual page on JDBC drivers and translators, which helps to explain why we're running into trouble here. Now ultimately, all that I have to do to fix the problem I'm encountering is install the necessary JDBC driver file on the gateway. And I can get that file by finding MySQL in this table here, and then clicking on this download link. This will take me to MySQL's official website and to the download page for our JDBC driver. So on here, I just need to select an operating system and here I select platform independent. And then I'll go ahead and download the second one here, which is the ZIP archive. And I'll say No thanks, just start my download. So that ZIP file I just downloaded contains the necessary files. And now I can go back to my gateway web page and fix my MySQL JDBC driver. To do that, I have to briefly leave this databases connections page and go to the databases drivers page right below it in the sidebar. And here we can see immediately that there's a problem with our MySQL JDBC driver. So to fix that, I'll click Edit. And in fact this driver interface is 99% set up already. The only thing that I need to address is this JAR files property here. This is going to expect a file from the ZIP folder I've just downloaded. So I'll say Choose File. And then I'll go into my downloads. I'll right click on that ZIP folder to extract. And then I'll pull over the dialog and then click Extract. Close out of that, go back into the folder. And there we go. Now I'm inside of that zipped folder. And in here, the only thing that I need is this MySQL connector Java.jar file. So once I found that, I'll say open, and then I'll scroll down and hit Save Changes. And there we go. Now we've repaired our MySQL JDBC driver. So once more, it's very possible you would not have had to go through these steps. But if you're installing Ignition eight for the first time, this is something you'll have to do when you first create a MySQL database connection. So now that we've repaired our JDBC driver, I'll go back into databases connections, and then click Create new Database Connection again, select my MySQL JDBC driver, and then click Next. And now we're ready to set up our database connection properties. So first, I'll give my database connection a name. So how about MySQL, I'm going to scroll down just a little bit and find the connector URL property here. This is a very important property for us because it dictates exactly how we're going to be connecting to a database server. And specifically, this Connect URL is a string that the JDBC driver is using to actually configure the database connection. So this connect URL is a JDBC driver specific string. And we have little helpers at the bottom here for what we need to pass in on that string. So in this case, the string calls for a host name or IP address, and then a port. And then finally a database name or a schema to connect to. Because I have MySQL running on the same box that Ignition is installed on, I can just leave my host name as localhost. But if Ignition is connecting to your database remotely, you'll probably need to specify a hostname or IP address. Next, we need to specify a username and password. So I'm going to connect to my database as the root user. And then I'll enter in my root user's password, and I'll enter it in twice. So next, if I scroll down just a little bit more, I have some extra connection properties. If we need to change some connection settings, this is the place to do it. I'm happy leaving everything at the default though. A couple other settings too, I have a Failover Datasource. This allows me to use some other database connection as a backup database connection in case this one fails. I'll leave that property alone as well. But if I scroll down to the very bottom here, there is a checkbox for Show Advanced properties. And if I scroll down again, here we have some more nitty gritty settings for how our database connection should work. So for example, I could mess with the connection pooling to allow more or fewer concurrent connections to our database, or I could change something like the validation query to dictate how Ignition should decide whether our database connection is valid. So we've explored a couple of the settings down here, but I don't need to change anything. So I'll just scroll down to the very bottom and say Create New Database Connection. Once I'm here, my database connection will briefly show as reconnecting and then say valid. And once it says valid, that's how I know that the connection has been made successfully. If for any reason the status said faulted instead of valid. We have a handy link here to the database connection status page, which can give you more info on what went wrong.

You are editing this transcript.

Make any corrections to improve this transcript. We'll review any changes before posting them.