TweetFollow Us on Twitter

MySQL: PHP's Perfect Partner

Volume Number: 21 (2005)
Issue Number: 3
Column Tag: Programming

Getting Started

by Dave Mark

MySQL: PHP's Perfect Partner

A few month's back, I wrote about PHP. We walked through the installation process, then went through the basics. PHP is a wonderful tool, all on its lonesome. But boy does it shine when you add a database backend to the mix. There are several database systems that work well with PHP. If you're relatively new to this business, or if your database requirements are not particularly sophisticated, MySQL is the perfect choice.

I figure, if you're reading this article, you are probably pretty new to the database universe and, therefore, MySQL is a good choice for you. But just in case you've wandered into the room with some existing expertise, but are new to open source solutions like MySQL, here's a great URL that will tell you what MySQL does differently than ANSI Standard Query Language:

http://dev.mysql.com/doc/mysql/en/Differences_from_ANSI.html

MySQL is rock-solid, and incredibly fast. There are more than five million active MySQL installations in the world and MySQL has been downloaded more than ten million times. Most are of the LAMP variety (Linux/Apache/MySQL/PHP/Perl). Lots of Mac and Windows setups as well. There are a number of benchmarks that show MySQL as the fastest such systems available, faster than the most expensive commercial DBMS's, faster even than PostgreSQL.

MySQL uses the GNU General Public License (GPL). Want to run it on your personal computer? No charge! Compare that to the $1,000 per seat licenses of some commercial DBMS apps, or the $50,000+ cost for some commercial server packages. And chances are good that if your ISP offers PHP, they'll offer MySQL as well. MySQL is not hard to find.

In this month's column, we're going to install MySQL, then make sure it's set up and ready to use. If you don't already have a reasonably recent version of PHP installed on your computer, now would be an excellent time to do so.

Installing MySQL

With each new release, the folks at MySQL AB (the corporate entity that owns the rights to MySQL) have made it easier and easier to install MySQL. In the early days of Mac OS X, installation was a bear. You had to locate the source code, build and debug, searching the net to find info on the many compile switches, till you finally made your way through a successful build. You then needed to deal with ownership issues to make sure someone couldn't creep over the net and illicitly access your tables. Working with early MySQL releases required a real pioneering spirit. Nowadays, installation is fairly straight-forward, almost trivial.

Early versions of Mac OS X required that you go through the process of creating a new user named mysql. That user was given ownership of the installed files that were not owned by Root. Most folks added the mysql account using the Accounts pane in System Preferences. That worked fine, though it created some files and directories that would only be used by a human user and added that user to the set of users presented at login.

Nowadays (since the release of Mac OS X 10.2), Apple takes care of this bit of business for you. Though they don't do the installation of MySQL, recent versions of Mac OS X create the mysql user as part of the System install. To see this for yourself, go into your Applications directory, Utilities subdirectory, and launch NetInfo Manager (in the Finder, note that shift-command-U is a shortcut to the Utilities directory).

When NetInfo Manager's main window appears, use its browser to locate the users directory. You should see a user named mysql in the second column (see Figure 1). Notice the values for home and shell. Since we won't be logging in as mysql, there's no reason for a shell and home directory.


Figure 1. NetInfo Manager, showing the mysql user.

Downloading MySQL

Though the net is full of tons of excellent MySQL resources, by far the most important is found at MySQL's official home at http://www.mysql.com. Most of the stuff we'll be interested in lies behind the Developer Zone tab at http://dev.mysql.com. To start your download decision-making process, navigate to the main download page:

http://dev.mysql.com/downloads/

Figure 2 shows the links of interest when I navigated there. Notice the Mirrors link. When you click to this page, the site will use your IP address to build a list of mirror sites it thinks are geographically close to you. You'll definitely want to check this page out if you run into problems downloading from the main site.


Figure 2. The important links on the downloads page.

Next on the list is the latest GA (Generally Available) release, followed by the previous GA release. After that is the latest preview release (essentially a beta). I would definitely stick with the most recent GA release. Click on that link.

For me, the most recent GA release was MySQL 4.1. Clicking on that link brought me to the MySQL 4.1 Downloads page. Take a minute to read the text at the top of the page:

The MySQL database server is available under the MySQL AB "dual licensing" model. Under this model, users may choose to use MySQL products under the free software/open source GNU General Public License (commonly known as the "GPL") or under a commercial license.

Click on the GNU General Public License link, then on the commercial license link. In effect, you can use the MySQL server at no cost to you, if your app is 100% GPL. This is the beauty of Open Source and the GPL model. If you are not familiar with GPL or are new to Open Source, it is well worth your time to read through the MySQL license pages. I think they are very well written and very understandable. Worth taking the time to do this.

If you are just in learning mode, the GPL license is fine. The commercial license is for people who do not want to release their source code or who find the GPL licensing rules too restrictive. The cool thing is, even the commercial license is relatively inexpensive when compared to other commercial products.

Scroll down the MySQL 4.1 Downloads page until you come to an area labeled Mac OS X downloads. As you can see in Figure 3, this part of the page is divided into 4 different sets. Two are installer-based, two are tar-ball based. There's one of each type for Jaguar and one for Panther. We're going for the Panther version with the installer package.


Figure 3. The Mac OS X download options.

Now that we've decided that, we need to decide between standard, max, or debug versions of the server software. You'd use the debug version if you were trying to debug the MySQL source itself, or if you were trying to track down a particularly knotty problem in your code and needed to see the MySQL symbols. Note that, as with any software package, there's a significant performance hit associated with the debug version.

The max version includes a number of esoteric features that you most likely won't need (the NDB storage engine, Berkeley DB storage engine, UDFs, BIG_TABLE support, etc.) The features in max tend to be beta in nature and will migrate to the standard release as they stabilize.

Bottom line, standard is the one you want. So, if you have Panther installed, you'd go to the Mac OS X downloads section, then click on the 4th overall Pick a mirror link. It'll be the first link in the subsection labeled Installer package (Mac OS X v10.3).

Once the mirror page appears, you might want to use the login link to create a new MySQL account and login. With a login, you'll be able to post questions to the forum, subscribe to the MySQL newsletter, etc. Worth it.

Once you're logged in, click on the closest mirror, then go get a nice piece of halvah. Tap, tap, tap. Done yet? Ah, there you go. If you downloaded the installer package, you'll get a .dmg file which should automatically mount as a disk image. Open the image. You'll see two packages and a readme file. The first package is the MySQL server package. The second installer, called MySQLStartupItem.pkg, installs a Startup Item which will automatically start up the MySQL server at boot time. If you are going to spend any amount of time with MySQL, you'll want this Startup Item installed.

Start with the main package. Then install the Startup Item. If you run into any problems, dig into the readme file. There's a lot of helpful info in there.

Starting the Server for the First Time

Your next step is to start the MySQL server, so we can start to play! The simplest way to do this is to just restart your computer and let the Startup Item do its thing. But it's worth seeing how this is done by hand, just to get a sense of how this works.

Fire up Terminal, then type this command:

man -ps

When you hit return at the end of the command, one page worth of the manual page for the ps command will display in the Terminal window, and a colon (:) prompt will appear at the bottom of the screen. You are looking for the list of options to the ps command, specifically descriptions of the "-a" and "-x" options. To move down a page, hit the space bar. To quit, either hit enough spaces to scroll to the end or type the letter q.

The listing for "-a" says, "Display information about other users' processes as well as your own." The listing for "-x" says, "Display information about processes without controlling terminals." Let's combine these two, like so:

ps -ax

You'll see a long scrolling list of processes. At this point, none of them should have the word mysql in them. Unless you have a really wide monitor, the commands will likely get clipped, making them hard to read. Try this command instead:

ps -ax > textfile

This does the same thing, but redirects the process listing into a text file named textfile. Unless you've specifically changed directories since you started up Terminal, the file should be in your home directory. Go into the Finder, look in your home folder, and drag textfile onto TextEdit. That's better!

Now let's start the server. At the command prompt, type:

sudo /Library/StartupItems/MySQLCOM/MySQLCOM start

The sudo command is asking Unix to do this command as super user, or root. You should be prompted for your root password. Once you successfully enter your password, you should see this message:

Starting MySQL database server

Cool. Now do your ps -ax again and you should see two process entries that resemble these two:

  388  ??  S      0:00.03 sh ./bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/
                                   local/mysql/data/Dave-Marks-Computer.local.pid
  408  ??  S      0:02.06 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/
                     my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql 
                     --pid-file=/usr/local/mysql/d

The first entry is the shell wrapper for the server daemon. Basically, this shell is acting purely as a wrapper and a safe way to communicate with the server.

The second entry is the server daemon itself. Though the daemon does all the work, you shouldn't have a need to communicate with it directly. Notice that the daemon is running with user=mysql, and not as root. This is the right way to do this. Running as root would create a dangerous security hole.

Want to shut down the MySQL server? Don't worry, it's perfectly fine to do this. Type this command:

sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop

Check your ps -ax again. The two processes should be gone. Go ahead and start the server again, so we can play a bit. When you restart your machine, the Startup Item issues the same command you're using to start the server:

sudo /Library/StartupItems/MySQLCOM/MySQLCOM start

Setting Up the Aliases

To help save some typing, let's set up a couple of aliases. If you are using bash shell, type these two commands:

alias mysql=/usr/local/mysql/bin/mysql
alias mysqladmin=/usr/local/mysql/bin/mysqladmin

If you are using almost any other shell, type these two commands:

alias mysql /usr/local/mysql/bin/mysql
alias mysqladmin /usr/local/mysql/bin/mysqladmin

If you are not sure which shell you are using, check the title of the Terminal window. It should say. Or just type one of the sets above. If you get an error, try the other set.

Once you've successfully executed one set or the other, add the two lines to your shell's startup file so these two aliases will be setup automatically each time you open a new Terminal window. For now, just type the commands and leave the Terminal window open so the aliases stick around.

Now, if you type mysql, you'll execute the command /usr/local/mysql/bin/mysql and when you type mysqladmin, you'll execute the command /usr/local/mysql/bin/mysqladmin. Aliases are very useful.

Setting Up the MySQL Accounts

Our last step before we actually start playing with MySQL itself is to secure the default MySQL accounts and set up a non-root account for our dabbling pleasure.

MySQL ships with two root accounts and two anonymous accounts that do not have passwords. Obviously, a dangerous situation, though one that makes perfect sense from the vendor's perspective.

There are a number of ways to do this. We'll use the mysql alias we just set up. In Terminal, type this command:

mysql -u root

This command starts up the mysql monitor using the root user. Note that this is not the same as your Unix root account. MySQL maintains its own list of users, as well as its own data security model that allows these users to own the MySQL data. Normally, when you start up the mysql client, you'd type a user name and a password. Since there is no root password yet, all we need do is specify the user name. mysql will reply as follows:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1 to server version: 4.1.8-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Notice that you are now running the MySQL monitor. The prompt at the bottom of the Terminal window is the standard MySQL prompt. You can exit the monitor by typing the command exit, followed by a return. Don't do this quite yet!

MySQL ships with a user table that holds all its account info. Let's ask the monitor to list the host and user columns in that table. At the mysql> prompt, type this command:

select host,user from mysql.user;

Notice the semicolon (;) at the end of the command. Very important!!! The semi tells the monitor that we've reached the end of the command. Here's the results on my computer:

+--------------------------- +------+
| host                       | user |
+--------------------------- +------+
| Dave-Marks-Computer.local  |      |
| Dave-Marks-Computer.local  | root |
| localhost                  |      |
| localhost                  | root |
+--------------------------- +------+

4 rows in set (0.64 sec)

mysql>

Notice that I've got 4 accounts. Two root accounts, two anonymous accounts. One of each type is for connecting from the local host. The other is for connecting from any other host.

Let's add some passwords to these accounts, keep the bad guys out! Still in the monitor, type this command, replacing xxxxx with the password you want for your local anonymous account, yyyyy with the host name from the host column above (the entry in that column that is not localhost) and zzzzz with the password you want for your second anonymous account:

SET PASSWORD FOR ''@'localhost' = PASSWORD('xxxxx');
SET PASSWORD FOR ''@'yyyyy' = PASSWORD('zzzzz');

This is the reply I got to each of these commands:

Query OK, 0 rows affected (0.00 sec)

If you'd like some evidence that you just changed the password, try this command:

select password,user from mysql.user;

Here's the result I got, after I added my passwords:

+ ----------------------------------------- +------+
|   password                                | user |
+ ----------------------------------------- +------+
|                                           | root |
|                                           | root |
|   *18796D3E621A0FB8F69503C1006CF26D337330 |      |
|   *18796D3E621A0FB8F69503C1006CF26D337330 |      |
+ ----------------------------------------- +------+
4 rows in set (0.00 sec)

Notice that the password column is stored in an encrypted form, as you might expect. Notice also that the root passwords have not been set yet. We'll do those next. Finally, note that I changed both my passwords to the same value. Good strategy? Perhaps not, but I wanted to show the consistency of the encryption. In real life, I delete the anonymous accounts completely, since I don't like them hanging around. Want to try this? Here's how you delete your anonymous accounts. Do not type these commands, unless you really don't want your anonymous accounts!!

DELETE FROM mysql.user WHERE User = '';
FLUSH PRIVILEGES;

The first command deletes the unnamed users from the user table. The second command is necessary since the table is only read when the server is first started. This prevents us having to restart the server.

Our last task is to set passwords for the root accounts. As you did before, substitute your new root password for xxxxx, your host name for yyyyy, and your second root password for zzzzz.

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('xxxxx');
SET PASSWORD FOR 'root'@'yyyyy' = PASSWORD('zzzzz');

Feel free to use this command again, to check your results:

select password,user from mysql.user;

Here's my results:

+ --------------------------------------- +------+
| password                                | user |
+ --------------------------------------- +------+
| *18796D3E621A0FB8F69503C1006CF26D337330 | root |
| *18796D3E621A0FB8F69503C1006CF26D337330 | root |
+ --------------------------------------- +------+
2 rows in set (0.00 sec)

Notice that my anonymous accounts are gone. If you somehow forget your root password after you do this, here's a link to a page that tells you how to reset the password:

http://dev.mysql.com/doc/mysql/en/Resetting_permissions.html

Till Next Month...

We did a lot this month. But the real fun comes in my next MySQL column when we really get into this stuff. We'll create tables, add data and, eventually, use PHP to pull that data out of the database and display it in a web page. Cool!

Not sure if we'll do all this next month, but I'll try. In the meantime, be sure to check out the new books at http://spiderworks.com. Rumor has it that there's a series of Tiger books in the works. Automator, Dashboard, and Spotlight. Excellent! See you next month...


Dave Mark is a long-time Mac developer and author and has written a number of books on Macintosh development, including Learn C on the Macintosh, Learn C++ on the Macintosh, and The Macintosh Programming Primer series. Dave's been busy lately cooking up his next concoction. Want a peek? http://www.spiderworks.com.

 

Community Search:
MacTech Search:

Software Updates via MacUpdate

TunnelBear 3.5.1 - Subscription-based pr...
TunnelBear is a subscription-based virtual private network (VPN) service and companion app, enabling you to browse the internet privately and securely. Features Browse privately - Secure your data... Read more
Typinator 7.4 - Speedy and reliable text...
Typinator turbo-charges your typing productivity. Type a little. Typinator does the rest. We've all faced projects that require repetitive typing tasks. With Typinator, you can store commonly used... Read more
Fantastical 2.4.5 - Create calendar even...
Fantastical 2 is the Mac calendar you'll actually enjoy using. Creating an event with Fantastical is quick, easy, and fun: Open Fantastical with a single click or keystroke Type in your event... Read more
Monosnap 3.4.9 - Versatile screenshot ut...
Monosnap lets you capture screenshots, share files, and record video and .gifs! Features Capture Capture full screen, just part of the screen, or a selected window Make your crop area pixel... Read more
Skim 1.4.32 - PDF reader and note-taker...
Skim is a PDF reader and note-taker for OS X. It is designed to help you read and annotate scientific papers in PDF, but is also great for viewing any PDF file. Skim includes many features and has a... Read more
ForkLift 3.1.1 - Powerful file manager:...
ForkLift is a powerful file manager and ferociously fast FTP client clothed in a clean and versatile UI that offers the combination of absolute simplicity and raw power expected from a well-executed... Read more
Direct Mail 5.2.1 - Create and send grea...
Direct Mail is an easy-to-use, fully-featured email marketing app purpose-built for macOS. Create, send, and track great looking email campaigns that get results. Start your newsletter by selecting... Read more
Direct Mail 5.2.1 - Create and send grea...
Direct Mail is an easy-to-use, fully-featured email marketing app purpose-built for macOS. Create, send, and track great looking email campaigns that get results. Start your newsletter by selecting... Read more
Skim 1.4.32 - PDF reader and note-taker...
Skim is a PDF reader and note-taker for OS X. It is designed to help you read and annotate scientific papers in PDF, but is also great for viewing any PDF file. Skim includes many features and has a... Read more
ForkLift 3.1.1 - Powerful file manager:...
ForkLift is a powerful file manager and ferociously fast FTP client clothed in a clean and versatile UI that offers the combination of absolute simplicity and raw power expected from a well-executed... Read more

Latest Forum Discussions

See All

What mobile gaming can learn from the Ni...
While Nintendo might not have had things all its own way since it began developing for mobile, one thing it has got right is the release of the Switch. After the disappointment of the WiiU, which I still can't really explain, the Switch felt a... | Read more »
Programmer of Sonic The Hedgehog launche...
Japanese programmer Yuji Naka is best known for leading the team that created the original Sonic The Hedgehog. He’s moved on from the speedy blue hero since then, launching his own company based in Tokyo – Prope Games. Legend of Coin is the... | Read more »
Why doesn't mobile gaming have its...
The Overwatch League is a pretty big deal. It's an attempt to really push eSports into the mainstream, by turning them into, well, regular sports. But slightly less sweaty. It's a lavish affair with teams from all around the world, and more... | Read more »
Give Webzen’s new billiard game PoolTime...
Best known for producing hugely popular MMO titles, South Korean publisher Webzen is now taking aim at a different genre altogether. PoolTime is a realistic eight ball pool simulator, allowing you to compete in real-time matches against players... | Read more »
Let Them Come Guide - How to survive aga...
Let Them Come is all about making it as far as possible against overwhelming odds. Check out some of these tips to help you last a little longer in your unwinnable fight: [Read more] | Read more »
All the best games on sale for iPhone an...
Happy last day of the week. I hope you've been having a good one. I have. I saw ten doggos today. So because I'm in a good mood, I thought I'd round up all of the best games that are currently on sale on the App Store. [Read more] | Read more »
The very best games that came out for iP...
We're getting to the end of the first real, full, proper week of 2018. And in that time we've seen some pretty awesome games landing on the App Store. Of course, we've seen some absolute duffers as well. The sort of games that you look at and... | Read more »
Rusty Lake Paradise (Games)
Rusty Lake Paradise 1.4 Device: iOS Universal Category: Games Price: $2.99, Version: 1.4 (iTunes) Description: Jakob, the oldest son of the Eilander family, is returning to Paradise island after his mother passed away. Since her... | Read more »
Antihero Guide - Sneaky tricks to get ah...
Games of Antihero start out small and streamlined, but they quickly turn into long strategic conquests as you fight for control of the Victorian-era streets. If you find yourself struggling in the skullduggery department, here are a few things you... | Read more »
Here's why Niantic pulling Pokemon...
If there's one thing that Pokemon GO did well, it was bringing people together. I still remember seeing groups of people around the marina near where I live in the weeks after the game came out, all of them trying to grab some water Pokemon. There... | Read more »

Price Scanner via MacPrices.net

10″ iPad Pros on sale for $50-$75 off MSRP, n...
B&H Photo has 10″ and #Apple #iPad Pros on sale for up to $75 off MSRP. Shipping is free, and B&H charges sales tax in NY & NJ only. Note that some sale prices are restricted to certain... Read more
Apple refurbished Mac minis available startin...
Apple has restocked Certified Refurbished Mac minis starting at $419. Apple’s one-year warranty is included with each mini, and shipping is free: – 1.4GHz Mac mini: $419 $80 off MSRP – 2.6GHz Mac... Read more
Amazon offers Silver 13″ Apple MacBook Pros f...
Amazon has new Silver 2017 13″ #Apple #MacBook Pros on sale today for up to $150 off MSRP, each including free shipping: – 13″ 2.3GHz/128GB Silver MacBook Pro (MPXR2LL/A): $1199.99 $100 off MSRP – 13... Read more
Sale: 12″ 1.3GHz MacBooks on sale for $1499,...
B&H Photo has Space Gray and Rose Gold 12″ 1.3GHz #Apple MacBooks on sale for $100 off MSRP. Shipping is free, and B&H charges sales tax for NY & NJ residents only: – 12″ 1.3GHz Space... Read more
Apple offers Certified Refurbished 2017 iMacs...
Apple has a full line of Certified Refurbished iMacs available for up to $350 off original MSRP. Apple’s one-year warranty is standard, and shipping is free. The following models are available: – 27... Read more
13″ MacBook Airs on sale for $120-$100 off MS...
B&H Photo has 2017 13″ 128GB MacBook Airs on sale for $120 off MSRP. Shipping is free, and B&H charges sales tax for NY & NJ residents only: – 13″ 1.8GHz/128GB MacBook Air (MQD32LL/A): $... Read more
15″ Touch Bar MacBook Pros on sale for up to...
Adorama has Space Gray 15″ MacBook Pros on sale for $200 off MSRP. Shipping is free, and Adorama charges sales tax in NJ and NY only: – 15″ 2.8GHz MacBook Pro Space Gray (MPTR2LL/A): $2199, $200 off... Read more
21″ 3.4GHz 4K iMac on sale for $1399, $100 of...
Adorama has the 21″ 3.4GHz 4K #Apple #iMac on sale today for $1399. Their price is $100 off MSRP. Shipping is free, and Adorama charges sales tax in NJ and NY only: – 21″ 3.4GHz 4K iMac (MNE02LL/A... Read more
B&H offering 13″ Apple MacBook Pros for u...
B&H Photo has 13″ MacBook Pros on sale for up to $75-$120 off MSRP. Shipping is free, and B&H charges sales tax for NY & NJ residents only: – 13-inch 2.3GHz/128GB Space Gray MacBook Pro (... Read more
B&H continues to offer clearance 2016 15″...
B&H Photo has clearance 2016 15″ #MacBook Pros available for up to $800 off original MSRP. Shipping is free, and B&H charges NY & NJ sales tax only: – 15″ 2.7GHz Touch Bar MacBook Pro... Read more

Jobs Board

Commerce Engineer, *Apple* Media Products -...
# Commerce Engineer, Apple Media Products Job Number: 113161479 Santa Clara Valley, California, United States Posted: 01-Nov-2017 Weekly Hours: 40.00 **Job Summary** Read more
*Apple* Retail - Multiple Positions - Apple,...
Job Description:SalesSpecialist - Retail Customer Service and SalesTransform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
Site Reliability Engineer, *Apple* Pay - Ap...
# Site Reliability Engineer, Apple Pay Job Number: 113356036 Santa Clara Valley, California, United States Posted: 12-Jan-2018 Weekly Hours: 40.00 **Job Summary** Read more
UI Tools and Automation Engineer, *Apple* M...
# UI Tools and Automation Engineer, Apple Media Products Job Number: 86351939 Santa Clara Valley, California, United States Posted: 11-Jan-2018 Weekly Hours: 40.00 Read more
*Apple* Retail - Multiple Positions - Apple,...
Job Description: Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.