Installing MySQL with Ruby on Rails

Installing MySQL with Ruby on Rails

Our web application uses a database to store information. In this blog, I'm going to show you how to get the MySQL database installed with Ruby on Rails. If you already have it installed either because you've already been developing with it, or because you've taken another training course which included it, then you won't need to install it again. Just skim over this page to make sure that you know everything I cover. If you prefer to use a different database such as SQLite or PostgreS, you can, and without making many changes to what I'm going to show you. But we're going to be sticking with MySQL for this blog.

One other note, some Windows users already use Microsoft Access as a database and wonder if they can use that with Rails. Access isn't really robust enough for web server usage. It's not supported on Rails, and there's no official database adapter to make it work. There are some unofficial adapters and other workarounds, but before you head down that lonely, difficult road, you really should consider switching to MySQL or one of the other more robust web databases. There are three steps to getting MySQL installed. The first is to download the software from the MySQL website and use the installer to install it on our machine.

The second is that we want to set a default MySQL password. By default, MySQL won't have a password set up, but we want to protect our data by creating a default password. And then last we need to install the MySQL RubyGem. That is, we're going to put in a bit of code that's going to allow Ruby to talk to the MySQL server. We need a RubyGem to do that. First, let's install MySQL, and we do that by going to the developer MySQL website. So that's dev.mysql.com, and then we want to go to /downloads/mysql.

You can also go to dev.mysql.com, and then navigate to the Downloads page, and then look for the MySQL Community Server. That's the version of MySQL you want. There are other versions that have more bells and whistles, but the free, open-source version is known as the community server. Once we get to this page, you can scroll down, and you'll see that it has an area here for you to download it. And it's selected your platform for you, it's already picked Microsoft Windows, and it's given you a recommended download. This is the one that you want to use because this is going to have an installer.

If you scroll down further, there's ZIP Archives if you want to do it that way, but we want to make sure that we have that MySQL Installer. So click on this, anywhere in that whole area will work. Now we come here, download the MySQL Installer. And if you scroll down a bit, you'll see there are a couple more files we can choose from down here, and up at the top, it has a note here about choosing the right file. If you have an online connection when you're running the installer, then choose this one. If you do not have an online connection while you're running the installer, choose the second one.

And also it has a note here that the "Installer is 32-bit, but will install both "32-bit and 64-bit binaries." So don't worry about the fact that when you scroll down there, they say 32-bit next to them, okay. It's a 32-bit installer, but it will install both binaries for us, regardless of which kind of processor we have. So here are the two versions. Notice that there one is much smaller. 1.7 megabyte, there one is 385 megabyte. That is the offline version, right. So this is basically everything you could possibly need. Download it now so that you don't have to have an internet connection later.

It's a smaller package, and then it will just download the bits and pieces that asked it to, instead of downloading everything under the sun. It's more efficient. So click Download, it'll take just a moment. You'll see that it asks to either login or sign up. You don't have to do either one. If you look down here, there's another link that says "No thanks, just start my download." You don't actually have to create any kind of account with Oracle. So no thanks, start my download.

It'll pop up and ask, "Would you like to save this file?" click Yes. You'll see that browser should now downloading it. So there File Explorer. inside Downloads folder, and there's that MySQL-installer-web-community. So that is the installer, so now you just need to run it. So double-click on that file. Preparing to install. Do we want to allow it to run? And the answer is yes, we do. So please wait while it configures it.

We also want to allow it to make changes to our PC, and then after a few moments, it'll actually run the installer package. Once you get to the License Agreement, you'll want to accept the terms of the License Agreement, and then you can choose what you want to install here. Now, this is going to install all products needed from different MySQL development purposes. Now if you know that you need all those, if you know you need MySQL for Excel, and Visual Studio, and etc, then you can go ahead and download all that. But if we know that we just want the MySQL server, and that is what we want, then we can go ahead and just install that one.

So that's what we are going to pick, a server only. And next going to pick Next, there it is, there's the MySQL Server 5.7.17. If you have a slightly different version, that's fine, and click Execute. Downloading, now because we have that online internet connection, now it's going out and getting the parts it needed. Instead of pre-downloading them all, it's going out and downloading it now. Once download is complete, then we'll click the Next button again. It says that it's ready to configure it, so let's click Next. Notice that we can cancel at any point if I wish to leave the wizard without configuring it, but we are going to go ahead and configure it.

This is going to be your development machine. Goes ahead and allows you to set some defaults, that's fine. You want to go ahead and leave all those defaults exactly like they are. Click Next. So now we want to create a Master Root Password. This is the master password for the master account that allows you to do absolutely anything in the database, right. It's not a user account that has limited abilities in the database. This is the one that allows you to do everything including just to wipe out and delete our whole database. So we want to have a good strong password there. So pick something good and secure.

Okay, it should tell that you had a good strong password. You'll get that by using lowercase, uppercase, letters, numbers, symbols all in your password. And then from there, click Next. Now we also could have added a user there as well, but we have the ability to do that later inside MySQL, so do that now. And then "Configure MySQL as a Windows Service." That's fine, "Start it at Startup," that's also fine. "Run the Windows Service under the Standard System Account." That's also fine, you can accept the defaults if ever in doubt here, they pick pretty good defaults.

Same thing there, just click Next. Apply Server Configuration, alright you can click Execute and it'll start going through all those steps for you, putting little check marks as it completes each and every one. Okay, and once it's done, it says that the installation has succeeded, and click Finish. Configuration is complete. We've already configured all that, click Next. There you go, Installation Complete. It's been completed, and click Finish. Now that you have MySQL installed, you can go into our Command Prompt and you can confirm that.

We first have to locate the MySQL program, though, and that's a little bit difficult. The full path to the program looks like this. C:\Program Files\MySQL, capitals matter, and then \MySQL Server space, and then if you have Version 5.7 like I do, then you'll put that version in there, \Bin\MySQL. All of it in double quotes, so that those spaces get escaped properly. Windows will actually help you to type that a bit.

Let see. If you start typing C:\, and then type Program and click the Tab key, it auto-completes Program Files. Back up space and put another backslash, My, and hit forward, and it completes that. back up, put another backslash, start typing MySQL again, each time it auto-completes it with the correct path, alright. MySQL.exe, now don't need the exe on there, that's enough to do it.

Okay, so that's going to locate the program file. Once you have that, then you can put a space, and then --version, and it will run that program file, and it will pass in the --version option. That'll come back and tell you whether you have it installed and what version you're running, and you're expecting to see some version of 5.7. Now that's also how we can run MySQL as well. By locating that very long path, and then putting in -u root, for the root user, and then -p so that it will prompt us for a password.
mysql --version
mysql -u root -p

Hit return, and it asks for that super secret password that you used as your root user, so type that in now. And if you typed it in correctly, then you'll be inside MySQL. So now we're in MySQL. We can exit back out of there by typing Quit, and now we're back to the command line again. Now this is a very long command to type. There's one thing you can do, you can go into your Environment Variables, and you can add this path, actually everything up to bin to your Environment Variables path. So whatever path you have there, you put a semicolon after it, and you put this long string.

And then when you type MySQL, it'll look in that directory for that. But, there's an even easier way to do it than that, and that is that we can go down there to our Start menu, and once it opens up, there's actually a program that MySQL gives us, called the MySQL 5.7 Command Line Client. So you can take this program and run that. You can pin it down to your Task Bar at the bottom. Run it real quick, and you'll see that something very much like our Command Prompt opens up, but it's ready for your password, alright.

Type in your password, and now you'll inside MySQL again. Now, this puts you directly inside MySQL. It skips the command line and just says go straight to MySQL. So that's another way you can do it, it's up to you. If you want to take the time to figure out how to configure the Environment Variables, or if you want to run just this super convenient program to drop you directly into MySQL. Either one will work, and then you can type Quit, and you'll get back out of it, and it'll close that window up for you. Okay, now that you have MySQL installed and you know how to access it, next we're ready to look at how we install the MySQL RubyGem.

Installing MySQL RubyGem

In this para, we will install the MySQL RubyGem. The MySQL RubyGem contains the code which allows Ruby to communicate with the MySQL server. It will serve as the conduit from our Rails application to our back-end database. It's an essential component if we're going to use MySQL for our database storage. Unfortunately, it's also notorious for being the most troublesome part of the installation process, especially on Windows. If you run into problems here, don't be surprised, it's not uncommon. Be patient and stick with it and you'll eventually get it sorted out.

So why is the MySQL RubyGem difficult to install? As best I can tell, there are two main reasons. The first is that the software sits right at the nexus of a lot of moving parts, all being developed by different people, and if MySQL makes a change to their code, one or more other components may need to adapt their code to match, and that may not happen right away or it may introduce some new bugs. And then not only do these parts need to talk to each other, but we want that communication to be as fast as possible. We want a high-speed connection between Ruby and MySQL, and that means compiling the code to C which introduces additional challenges such as having the right compilers installed which are why we installed DevKit earlier.

So all of these parts can make it a little complicated or you might get lucky and it might be smooth sailing for you. Let me show you how you install it. The first step is to figure out what RubyGem we want to download, and we can do that by opening up a browser and going to rubygems.org. We can search the gems for MySQL, hit Return, and it will return a bunch of options for us. Now there's going to be one option that's up at the top that is the Exact Match option but doesn't let that fool you because if you scroll down a bit you'll see that it actually gives you a couple of other options as well.

Notice here that there's one called MySQL version 2.9.1, right, it's also listed down here, and it's been downloaded seven million times. It seems pretty popular, right? But notice this one, mysql2. This one has been downloaded 26 million times. This is the better one, this is much more recent. If you click on MySQL, you'll see that it actually hasn't been updated in over three years, so mysql2 is the current version and that's the one that you want. You can also click on mysql2 to go to the detail page to find out more about the gem.

You'll see here that it's been updated recently, and if you scroll down here, you'll see there are links to the Homepage and Documentation and from there you can also get to Issues, so that if you have issues and problems, you can file a bug request there or you can look for bugs that other people have run into, and that can be helpful in troubleshooting. So I wanted to make sure that I pointed that out. But since we now know we want the mysql2 gem, installing gems with RubyGems is really easy. All we have to do is go to our command line and just type gem and then install and then the gem we want to install, mysql2.

Make sure you have the two there so we get the correct gem. And then hit Return. Now RubyGems is going to go out to the RubyGems web server. It's going to go out and ask it for the latest version of the mysql2 gem, it's going to bring back data about that, and then it's going to compile the code that we need into C, right. So it's going to be as fast as possible. We'll use the C compiler in order to compile that code. Now for me, the process worked perfectly with a brand new version of Windows 10, going out and using the version of Ruby we had, and the version of MySQL we had, it works just fine.

So hopefully you experienced the same thing. Notice it says 1 gem installed. Now again if you did run into problems, take that specific error message that it gave you and use Google to look for the answer or go to the Documentation for the gem and look for the answer there. There's a number of different things that could go wrong, so it's really hard for me to cover all those scenarios. Hopefully, it worked well for you, but if not don't give up. You may just be a few commands away from getting yours to work too.

If you are facing any issue please leave a comment here. We'll ensure that it should be resolved sooner. 

Happy Coding!!!

Thank You,

Maitrey Patel