Environment Consistency – MySQL (Windows)

We need to make our life easier by creating consistency for every machine. What this means is every machine you try to use, whether it be yours or your colleagues, the environment will be the same when you sit down to help them with something. This is very important if you’re a Development Manager and you’re trying to help one of your staff.

This is one of several posts in regards to this subject.

I really don’t use MySQL too much.  At work, I have Oracle and Sybase.  But I think I’m going to use it in the example code that I post later.  Because we’ll be using Hibernate, for the most part, it really doesn’t matter.  Hibernate hides the nuances of each type of database from us (for the most part).  Anyway, the real reason has to do with the fact we’ll be installing PHP (which we’ve already done for the Apple Macintosh in an earlier post and we’ll be doing for Windows in a later post).  We’ll also need it for WordPress which we’ll also be addressing in a later post.

Here we’re going to install MySQL for Windows.  We’ve already installed it for the Apple Macintosh in an earlier post.

First off, we need the binary.  We’ll go to the MySQL website to download the 5.1 MySQL version file called “mysql-essential-5.1.41-win32.msi”.  After we’ve gotten the file downloaded double click it and we’ll see this window.

Note: You may have downloaded the 64 bit version of MySQL which is completely fine.

Open File - Security Warning MySQL AB

Click “Run” and you’ll see this window:

MySQL 5.1.41 Installer Welcome Screen

Click “Next >” and you’ll see this window:

MySQL Server 5.1 - Setup Wizard

Click on the “Custom” radio button and click “Next >”.  You will see this window:

MySQL 5.1.41 Installer Welcome Screen (Custom Setup)

There’s no reason not to leave the defaults so click “Next >” and you’ll see this window:

MySQL 5.1.41 Installer Welcome Screen (Custom Setup)

Click “Install” and you’ll see a window similar to the following:

MySQL 5.1.41 Installer (Installing)

Once the software has completed installing you’ll see the following window:

MySQL Enterprise Ad

Click “Next >” here and you’ll see this window:

MySQL Enterprise Monitor Ad

Click “Next >” again and now you’ll see this window:

MySQL Setup Wizard Finished

I deselect the “Register the MySQL Server now” but you can leave it checked.  It’s up to you.  Now click “Finish” and you’ll see this window which is the configuration wizard:

MySQL Server Configuration Instance

Click “Next >” and you’ll see this window:

MySQL Instance Configuration Wizard (Type)

Leave “Detailed Configuration” checked and click “Next >” and you’ll see this window:

MySQL Instance Configuration Wizard (Detailed Configuration)

Leave “Developer Machine” checked and click “Next >” and you’ll see this window:

MySQL Instance Configuration Wizard (Multifunction Database)

Leave “Multifunctional Database” checked and click “Next >” and you’ll see this window:

MySQL Instance Configuration Wizard (InnoDB Database)

I’m leaving the defaults selected on my machine but you may want to put the database on a different disk if you have one.  Click “Next >” and you’ll see this window:

MySQL Instance Configuration Wizard (Connections)

Leave the default of “Decision Support (DSS)/OLAP” selected as we won’t be making a lot of concurrent connections to our machine while we’re developing software.  Click “Next >” and you’ll see this window:

MySQL Instance Configuration Wizard (Network)

You’re probably behind a firewall on a corporate LAN so you should be OK to leave these defaults (the port specifically).  Click “Next >” and you’ll see this window:

MySQL Server Instance Configuration (Character Set)

We may do some multi-lingual development so let’s change this to “Best Support for Multilingualism” and the window will look like this:

MySQL Server Instance Configuration (Character Set)

Click “Next >” and you’ll see a window like this:

MySQL Server Instance Configuration (Windows Service)

We want to keep “Install As Windows Service” checked so that MySQL will start up for us automatically.  And we’re also going to check “Include Bin Directory in Windows PATH”.  The window should look like this:

MySQL Server Instance Configuration (Windows Service)

Click “Next >” and you should see this window:

MySQL Server Instance Configuration (Accounts)

Enter a “New root password:” and “Confirm:”.  We then click “Next >” and will see this window:

MySQL Server Instance Configuration (Ready to Execute)

Click “Execute” and you’ll see a window similar to this:

MySQL Server Instance Configuration (Executing)

When it’s finished, you should see this window:

MySQL Server Instance Configuration (Finished Executing)

Click “Finish”.  Let’s reboot and the system should come up with MySQL started and everything in place.

When that’s done, let’s start up a command window by clicking on “Start” and then “Run” and typing “Cmd”.  You should see a window like this:

Windows Run Window

Click “OK” and you should see a window like this:

Windows CMD Window

Type in the following

mysql -u root -p

and you should see a window like this:

mysql -u root -p

Enter the password that you chose for your root password and you should see a screen like this:

Logged into MySQL

And you’re done!  You’ve now got MySQL installed.

MAMP

We have now created a MAMP (Macintosh, Apache, MySQL, PHP) environment.  This posting is just to group those particular posts together:

Environment Consistency – PHP (Apple Macintosh)

Environment Consistency – MySQL (Apple Macintosh)

Environment Consistency – PHP/MySQL Configuration (Apple Macintosh)

Environment consistency – Verify PHP to MySQL Connectivity (Apple Macintosh)

Environment Consistency – Verify PHP to MySQL Connectivity (Apple Macintosh)

We need to make our life easier by creating consistency for every machine. What this means is every machine you try to use, whether it be yours or your colleagues, the environment will be the same when you sit down to help them with something. This is very important if you’re a Development Manager and you’re trying to help one of your staff.

This is one of several posts in regards to this subject.

This post will show how to do a verification that PHP is talking to our MySQL database for the Apple Macintosh.  We’ll go back and show how to do it for Windows in a later post.

So, to do a test, to make sure we’re talking to the database, we start up NetBeans and click on “File”, “New Project” which will give us this dialog box:

New Project PHP Application

Now click on “PHP”, “PHP Application” and click “Next” which will give us a dialog box that looks like this:

New PHP Project

We’re entering “PhpDbTest” for the “Project Name:” and need to make sure that our “Sources Folder:” is “PhpDbTest” as well.  Click “Next >” and you should see this dialog box:

New PHP Project (Project URL)

As noted when we set up PHP, I run my applications out of my “/Users/thcampbell/Sites” directory so I click the “Copy files from Sources Folder to another location” and “Browse…” to that directory so that it shows up in the “Copy to Folder:” area of the dialog box.  I also change the “Project URL:” so that it reads “http://localhost/~thcampbell/PhpDbTest”. 

You’ll need to substitute your username where I’m using “thcampbell”

After making these changes the dialog box should look like this:

Project URL Modification

Click “Finish”

Now NetBeans is going to start up and automatically opens an “index.php” file for us and we’re going to add the following (highlighted) between “<?php” and “?>”

PHP Database Connectivity Code

What’s going on here is that we are setting up a couple of variables for connecting to our database:

$dbhost = ‘localhost’;
$dbuser = ‘root’;
$dbpass = ‘xxxxxx’;

You would, of course, want to change the ‘xxxxxx’ to your database password from when we hooked up MySQL for our Apple machine.

The following line will do the actual connection to our database and if there is an error it will quit and throw an error message:

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die (‘Error connecting to mysql’);

The following line does a select query of “Host” and “User” from the “mysql.user” table in our database:

$result = mysql_query(“SELECT Host,User from mysql.user”);

The “mysql.user” table is always there and holds the information of users that have access to our database as we saw when we hooked up our database in an earlier post.

These lines will display the data to our browser when we run the program:

while($row = mysql_fetch_array($result)) {
echo $row['Host'] . ” ” . $row['User'];
echo “<br />”;
}

and finally, let’s be a good boy or girl and close our database connection:

mysql_close($conn);

So, let’s run it from NetBeans by right clicking on our Project Name “PhpDbTest” and clicking on “Run”.  The default browser should start up and you should see a screen similar to the following:

PHP/MySQL Successful Test

If you see this screen then you’re done!  You connected to the database without any problem.

However, maybe you see this screen:

PHP/MySQL Unsuccessful Test

It’s probably your username or password for your database that is incorrect.  It is also possible you don’t have your PHP talking to MySQL properly but that’s fairly unlikely if you did the steps in this post.

If you can’t figure it out or something else went wrong post something in the comments section and maybe I or someone else can help.

I used information from the following sites for this posting

http://www.w3schools.com/PHP/php_mysql_select.asp

http://www.php-mysql-tutorial.com/wikis/mysql-tutorials/connect-to-mysql-database.aspx

Environment Consistency – PHP/MySQL Configuration (Apple Macintosh)

We need to make our life easier by creating consistency for every machine. What this means is every machine you try to use, whether it be yours or your colleagues, the environment will be the same when you sit down to help them with something. This is very important if you’re a Development Manager and you’re trying to help one of your staff.

This is one of several posts in regards to this subject.

We’ve installed PHP and MySQL but we haven’t “connected” the two of them.  We’re going to do that today and it’s actually pretty easy to do.  All we have to do is tell PHP where the MySQL socket file is located.  The PHP we installed, thinks it is going to be at “/var/mysql/mysql.sock”.  However, on our Apple Macintosh it isn’t located there.  It’s located at “/tmp/mysql.sock”.  All we have to do is start up a “Terminal” session and edit our “php.ini” file using “sudo” like so:

sudo vi /etc/php.ini

and we then find the line that starts “mysql.default_socket” like so (it’s highlighted):

mysql.default_socket (before)

and change it from “/var/mysql/mysql.sock” to “/tmp/mysql.sock” like so (again highlighted):

mysql.default_socket (after)

now we have another line that starts “pdo_mysql.default_socket” that we also need to change.  We’re changing it in the same way.  From “/var/mysql/mysql.sock” to “/tmp/mysql.sock”.  Here are two screen shots of the before and after:

pdo_mysql.default_socket (before)

pdo_mysql.default_socket (after)

save it and you’re done!

Reboot to make sure everything gets re-initialized.  This may be unnecessary but what’s it hurt?

Next week, I’ll show you how to verify that you’re talking to the database.