Testing Apache - Perl - MySQL

Apache - Perl - MySQL
The Big Picture

Installing and Setting Up MySQL

Installing MySQL Perl DBI:DBD Modules

Testing Apache - Perl - MySQL

1. Open the "Sharing" System Preferences panel (under Internet & Networking).

2. Make sure Personal Web Sharing is turned ON. This turns on the Apache web server. Close the Sharing panel.

3. Open a Terminal Window. Enter each line as follows (followed by return on your keyboard): (or just cut from edit box and paste into your Terminal Window)

cd /Library/WebServer/CGI-Executables

ed

a

#!/usr/bin/perl

print ("Content-Type: text/html\n\n");

print "Hello World...Perl is working through Apache!";

exit 0;

.

w test1.pl

q

chmod uga+x test1.pl


4. Now open this page through your browser running on the same machine:

http://localhost/cgi-bin/test1.pl


You should get the "Hello World" message displayed in your browser.

You can also test this from another machine on your LAN by going to the local IP address of this Mac OS X machine, example:

http://192.168.0.20/cgi-bin/test1.pl


The key thing to note is that Perl scripts are placed in the "/Library/WebServer/CGI-Executables" directory with Mac OS X. From a browser, however ,they appear to run from the "cgi-bin" directory.

5. Now we create a MySQL database to which we can connect through Perl. For this example, we will create the database named "johndoe". Use whatever name you deem appropriate. You will have to enter your root mysql password after entering any mysqladmin command using the "-p" option. (you will be prompted).

mysqladmin -u root -p create johndoe


Give "johndoe" access privileges to this database. You will enter the mysql monitor, which will prompt you with "mysql>". Make sure you terminate the GRANT command with the semicolon (;). You should also substitute "some_password" with a password of your choosing for this user access.

mysql -u root -p

[enter your root password]

GRANT ALL PRIVILEGES ON *.* TO johndoe@localhost IDENTIFIED BY 'some_password' WITH GRANT OPTION;

\q


The key item to note is that we are creating a MySQL database table named "johndoe" which corresponds to the user "johndoe". You do not have to stick with this convention, although we have found it convenient. (that is, your users and database names can be different).

6. Now we need to fully test the connection between Perl and the MySQL database you just created. There are many ways this could be done. One way is to simply create a test connection script, similar as follows:

ed

a

#!/usr/bin/perl

print ("Content-Type: text/html\n\n");

$DB_Host = "localhost";

$DB_Name = "johndoe";

$DB_User = "johndoe";

$DB_Password = "your_password";

print "Trying to connect to MySQL DB<br>";

if(!($dbh = Mysql->Connect($DB_Host,$DB_Name,$DB_User,$DB_Password)))

{

print "Could Not Connect to Database! Reason: ".($Mysql::db_errstr);

exit;

}

print "Successful MySQL DB Connection";

exit 0;

.

w test2.pl

q

chmod uga+x test2.pl


Then run the script from a browser by calling:

http://localhost/cgi-bin/test2.pl


You should see the "Successful..." message.

7. You can also download, install, and configure our MySQL Monitor package. This will not only let you test the connection to your MySQL server through Perl, but it will also give you a great way to administer database tables directly through your browser. To do so:

cd /Library/WebServer/Documents

ftp quicomm2.com [login anonymous, password: your email address]

cd ftp

get mysql_monitor200.tar
[you should see the file downloaded]
control-D [terminates ftp session]

tar -xvf mysql_monitor200.tar

mv mysql_command200.pl ../CGI-Executables/

cd ../CGI-Executables

chmod uga+x mysql_command200.pl

ed mysql_command200.pl

1,$ s/\/local//

1,$ s/MasterHTMLDirectory = ""/MasterHTMLDirectory = "\/Library\/WebServer\/Documents"/

1,$ s/yourpath/Library\/WebServer/

1,$ s/DB_Host = ""/DB_Host = "localhost"/

1,$ s/DB_Name = ""/DB_Name = "johndoe"/ [or whatever DB name you created]

1,$ s/DB_User = ""/DB_User = "johndoe"/ [or whatever DB user you added]

1,$ s/DB_Password = ""/DB_Password = "some_password"/ [the password for the user named above]

1,$ s/Script_Password = ""/Script_Password = "some_script_password"/ [enter a password which is needed to run the script]

w

q

cd ..

mkdir mysql

cd mysql

mkdir dump

mkdir load

mkdir import

mkdir export

chmod uga+rw dump

chmod uga+rw load

chmod uga+rw import

chmod uga+rw export

q


Now you should be able to call the mysql_command200.pl script from your browser:

http://localhost/cgi-bin/mysql_command200.pl


You should see the MySQL Monitor page in your browser. The fact that you can see the page tells us that Perl can communicate with MySQL. Now try to create a new database table by entering the following into the Command section at the bottom of the page:

CREATE TABLE my_test (timeNow double(12,0), name char(32));


Enter your script password and click "Run Command". The page should refresh, and you should now see "my_test" listed under Existing Tables. You can Show Structure, Dump Table, etc. Note that you only have to enter the script password the first time you run the script. For added security, you can also define specific IP addresses from which the script will run. Please see our
MySQL Monitor pages for additional features and options.

Congratulations! MySQL and Perl are now all setup for use under your Mac OS X system.