TweetFollow Us on Twitter

Your First MySQL Database

Volume Number: 20 (2004)
Issue Number: 1
Column Tag: Programming

Untangling the Web

by Kevin Hemenway

Your First MySQL Database

Creating, administering, and maintaining are pretty easy.

Last month, besides exploring what was different between the Jaguar and Panther web serving configurations, we installed the MySQL database program (specifically, the "value-added" installer from third-party Server Logistics), used the provided System Preference to initialize the database and set the MySQL root password, then confirmed it was running through one of three avenues (log files, the Terminal, or the Activity Monitor).

We've yet to create a MySQL database or insert any data. We betta' rECtififffYYyy!

Exploring The MySQL Helper Programs

Along with the MySQL daemon (where "daemon" is an application that runs all the time, resolutely waiting for something to do), a bunch of other useful helper programs were installed alongside your new database server. Take a look in your /Library/MySQL/bin directory (Figure 1) for the complete list. Some of these programs (like msql2_mysql, make_win_src_distribution, mysql_install, and mysql_install_db) will never be used in normal (or even abnormal) operation. Others (like mysqladmin, mysql_setpermission, mysqlshow and mysqldump) will become regular additions to your MySQL administrating repertoire.

The most negative aspect of these helper utilities is that there's no central place to find information on what they all do. Some, like mysql_config, have no explanations for their purpose (commonly accessible by passing -?, -h, or --help as command line flags), but their source code can be viewed with vi, less, or BBEdit to divine their intent. Others, like mysqlshow, have manual pages that can be accessed with a command like man -M /Library/MySQL/man/ mysqlshow (see "Homework Malignments" for a shortcut), and still others, like mysql_setpermission, are Perl scripts that need an additional module (DBI) not installed by default.


Figure 1: A number of additional MySQL utilities were installed.

Since the DBI Perl module plays an important part (both in the mysql_setpermission script and any future database interaction in Perl), we'll install that onto our machines before we go much further. Prior to starting, however, we'll need to fix a bug that wasn't corrected in time for Panther's public release. Open /System/Library/Perl/5.8.1/darwin-thread-multi-2level/Config.pm in an authenticating text editor (I prefer BBEdit, but you can use something like sudo vi $filename), and look for the following line:

ld='MACOSX_DEPLOYMENT_TARGET=10.3 cc'

Add the word env, creating the following correction:

ld='env MACOSX_DEPLOYMENT_TARGET=10.3 cc'

After saving the file, we've got one more annoyance to take care of. Remember how we set the MySQL root password so that our database would be reasonably secure? Welp, the Perl module we're about to install requires access to MySQL to run some tests, and there's no simple way to give it what it needs without temporarily blanking out that root password. Using the installed MySQL System Preference doesn't allow us to set a null value, so we'll need to run the following in the shell: /Library/MySQL/bin/mysqladmin -u root -p password "" (no spaces between those quotes). You'll be prompted for your current password... once entered, the root password will be set to nothing.

We're now ready to start the DBI installation process. To do so, we're going to use Perl's CPAN (the "Comprehensive Perl Archive Network") to automatically download the module, check and enable any prerequisites, ensure everything is working properly with a bevy of tests, and finally, install the modules for regular use.

Normally, to start CPAN, we'd simply enter sudo perl -MCPAN -eshell at the command line. However, the module we'll be installing needs to know a bit about our MySQL installation so, just this once, we're going to use the following commands instead. These will, for the duration of our current shell, add the MySQL bin directory to the lookup path (instructions on how to set these permanently are available in "Homework Malignments"):

# if you're using the tcsh shell:
setenv PATH ${PATH}:/Library/MySQL/bin
sudo perl -MCPAN -eshell

# if you're using the bash shell, use the following:
PATH=$PATH:/Library/MySQL/bin sudo perl -MCPAN -eshell

If this is your first time using CPAN, you may be asked oodles and oodles of configuration questions, including whether you're using proxies, what CPAN sites you want to download from, and so on. In most cases, you can just accept the defaults. Eventually, you'll end up at a command prompt that looks something like this:

shell -- CPAN exploration and modules installation (v1.76)
ReadLine support enabled

cpan>

Now, type install Bundle::DBD::mysql. This will install a bunch of modules related to the MySQL DBD ("database definition") of Perl's DBI ("database interface")--you'll see a dozen screens of information fly by before the process is finished. Depending on your CPAN configuration, you may be asked to follow some missing module prerequisite, which you should generally always agree to.

If the CPAN process complains about its inability to download modules from any of the mirror sites you chose during configuration, you probably have Panther's internal firewall enabled. Cancel the CPAN process, execute export FTP_PASSIVE=1 in the shell, and then start the CPAN process again. More information is available in "Homework Malignments".

If any critical errors occur, the install process will stop... in our case, we should have received a few during the testing phase of DBD::mysql (Figure 2). We could go nuts about actually hunting down and fixing these errors that caused the integrity checks to fail (thus canceling the installation), but honestly, you can "cheat" and force things forward anyways. Some will cluck at me for saying so, but run the following CPAN command to force the installation: force install Bundle::DBD::mysql. For more information about the failing tests, reference http://www.mail-archive.com/macosx@perl.org/msg05834.html.


Figure 2: Failures in the DBD::mysql module can be ignored.

Once the forced installation has finished, you'll be returned to the standard cpan> prompt. Type exit to finish the process, and then be sure to reset the MySQL root password back to what it was before. You can do this either from the command line (using /Library/MySQL/bin/mysqladmin -u root password "password") or by using the MySQL System Preference (Figure 3).


Figure 3: Using the System Preference to set the MySQL root password.

Creating Our First Database

The larger purpose of getting the Perl DBI and DBD modules installed was to use one of the MySQL helper utilities to create a new database, a new MySQL user, and set the proper permissions for access thereafter. There are many different ways and avenues this can be done; I happen to think that mysql_setpermission, a utility that requires DBI, is one of the easiest and friendlier paths to speed down.

We can start the mysql_setpermission script one of two different ways. Without any additional command line flags, it will attempt to connect to the MySQL database as the current user: morbus, in my case. Since that user has yet to be created within MySQL (remember, MySQL users and permissions have nothing to do with Linux users and permissions), I'll receive a disheartening error about lack of access:

~ > /Library/MySQL/bin/mysql_setpermission
Password for user to connect to MySQL: 
Can't make a connection to the mysql server.
 The error: Access denied for user: 'morbus@localhost'  
 (Using password: YES) at /Library/MySQL/bin/mysql_
 setpermission line 70, <STDIN> line 1.

We can solve this one of two ways: temporarily becoming the root user with sudo /Library/MySQL/bin/mysql_setpermission, or by passing the MySQL username as a command line flag with /Library/MySQL/bin/mysql_setpermission --user root. Regardless of the method, we'll reach a menu with numerous possibilities (Figure 4).


Figure 4: The starting menu of mysql_setpermission.

Since we've yet to create a database or MySQL user, we'll want to choose the second option, which allows us to do both. You'll be asked the name of the database to create, the username and password for the new MySQL user (passwords are heartily recommended if you plan on allowing the new user to modify data), and the hosts this user can access the database from. In our example (Figure 5), the new mactech database can be accessed by davemarksman from any host (represented by the % character). The host restriction determines whether other programs on other servers can connect to the mactech database. When a normal web visitor accesses any of our connecting code, it's considered localhost access (the visitor uses our Apache web server, which runs our PHP code, which connects to our MySQL installation.)


Figure 5: Adding a new MySQL database and user.

By default, the user that is created will have very slim access to the specified database (they'll only be able to read data with SELECT, not modify or delete). Since our next article will talk about inserting, modifying, and deleting, we'll want to next choose option 5, which allows us to give davemarksman heightened privileges (Figure 6).


Figure 6: Choosing the database to give heightened user privileges to.

Once you've created your database and configured your user permissions, you can exit the program by choosing option 0, which will send you back to the shell prompt. You can confirm your database has been created by running /Library/MySQL/bin/mysqlshow -u root -p, which gives output something like:

~ > /Library/MySQL/bin/mysqlshow --user root -p
Enter password: 
+-----------+
| Databases |
+-----------+
| mactech   |
| mysql     |
| test      |
+-----------+

Homework Malignments

I shall no longer prophesize about what will be in the next article, as I seemingly always overshoot my estimates. Instead, students may contact the teacher at morbus@disobey.com.

When you're in the Terminal, there are two environment variables that help control how much typing you have to do: PATH and MANPATH. The first controls what directories will be looked into when you attempt to run a binary program without a full path (like vi compared to /usr/bin/vi), and the second determines what directories are looked into for manual pages. You can see their current configuration by typing printenv in your Terminal. To make working with the MySQL shell programs easier, you'll want to add /Library/MySQL/bin to your PATH, and /Library/MySQL/man to your MANPATH. In Panther, which uses the bash shell by default, add PATH="$PATH:/Library/MySQL/bin" to your (possibly non-existent) /Users/username/.bash_profile. Then, with an authenticating text editor, add OPTIONAL_MANPATH /Library/MySQL/man and OPTIONAL_MANPATH /man to the /etc/manpath.config file. With that finished, restart your Terminal, and you should be able to type man mysqlshow.

You can also add export FTP_PASSIVE=1 to the .bash_profile (see previous Malignment). This instructs CPAN (specifically, the Net::FTP Perl module) to use passive file transfer mode which, for some, is required when the Panther firewall is enabled. Thanks to the macosx@perl.org mailing list for that tidbit.


Kevin Hemenway, coauthor of Mac OS X Hacks and Spidering Hacks, is better known as Morbus Iff, the creator of disobey.com, which bills itself as "content for the discontented." Publisher and developer of more home cooking than you could ever imagine (like the popular open-sourced aggregator AmphetaDesk, the best-kept gaming secret Gamegrene.com, the ever ignorable Nonsense Network, etc.), he has started to self-teach indexing and cataloging skills for his . .. . . "media collection". Contact him at morbus@disobey.com.

 

Community Search:
MacTech Search:

Software Updates via MacUpdate

BetterTouchTool 1.84 - Customize Multi-T...
BetterTouchTool adds many new, fully customizable gestures to the Magic Mouse, Multi-Touch MacBook trackpad, and Magic Trackpad. These gestures are customizable: Magic Mouse: Pinch in / out (zoom... Read more
Dropbox 8.4.21 - Cloud backup and synchr...
Dropbox is an application that creates a special Finder folder that automatically syncs online and between your computers. It allows you to both backup files and keep them up-to-date between systems... Read more
OmniGraffle Pro 6.6.1 - Create diagrams,...
OmniGraffle Pro helps you draw beautiful diagrams, family trees, flow charts, org charts, layouts, and (mathematically speaking) any other directed or non-directed graphs. We've had people use... Read more
OmniGraffle 6.6.1 - Create diagrams, flo...
OmniGraffle helps you draw beautiful diagrams, family trees, flow charts, org charts, layouts, and (mathematically speaking) any other directed or non-directed graphs. We've had people use Graffle to... Read more
f.lux 37.7 - Adjusts the color of your d...
f.lux makes the color of your computer's display adapt to the time of day, warm at night and like sunlight during the day. Ever notice how people texting at night have that eerie blue glow? Or wake... Read more
BBEdit 11.6.1 - Powerful text and HTML e...
BBEdit is the leading professional HTML and text editor for the Mac. Specifically crafted in response to the needs of Web authors and software developers, this award-winning product provides a... Read more
ScreenFlow 6.1 - Create screen recording...
ScreenFlow is powerful, easy-to-use screencasting software for the Mac. With ScreenFlow you can record the contents of your entire monitor while also capturing your video camera, microphone and your... Read more
Microsoft Office 2016 15.25 - Popular pr...
Microsoft Office 2016 - Unmistakably Office, designed for Mac. The new versions of Word, Excel, PowerPoint, Outlook and OneNote provide the best of both worlds for Mac users - the familiar Office... Read more
FileZilla 3.21.0 - Fast and reliable FTP...
FileZilla (ported from Windows) is a fast and reliable FTP client and server with lots of useful features and an intuitive interface. Version 3.21.0: Fixed Vulnerabilities Fixed a string format... Read more
Fantastical 2.2.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

3 best fantasy football apps to get you...
Last season didn't go the way you wanted it to in fantasy football. You were super happy following your drafts or auctions, convinced you had outsmarted everyone. You were all set to hustle on the waiver wire, work out some sweet trades, and make... | Read more »
Pokemon GO update: Take me to your leade...
The Team Leaders in Pokemon GO have had it pretty easy up until now. They show up when players reach level 5, make their cases for joining their respective teams, and that's pretty much it. Light work, as Floyd Mayweather might say. [Read more] | Read more »
Ruismaker FM (Music)
Ruismaker FM 1.0 Device: iOS Universal Category: Music Price: $4.99, Version: 1.0 (iTunes) Description: Following up on the success of Ruismaker, here's her crazy twin-sister, designed for people who want to design their own... | Read more »
Space Marshals 2 (Games)
Space Marshals 2 1.0.15 Device: iOS iPhone Category: Games Price: $5.99, Version: 1.0.15 (iTunes) Description: The sci-fi wild west adventure in outer space continues with Space Marshals 2. This tactical top-down shooter puts you in... | Read more »
Dungeon Warfare (Games)
Dungeon Warfare 1.0 Device: iOS Universal Category: Games Price: $3.99, Version: 1.0 (iTunes) Description: Dungeon Warfare is a challenging tower defense game where you become a dungeon lord to defend your dungeon against greedy... | Read more »
Solitairica (Games)
Solitairica 1.0.7 Device: iOS Universal Category: Games Price: $3.99, Version: 1.0.7 (iTunes) Description: Solitairica takes RPG combat and challenging rogue-like progression to a fresh new place—the world of solitaire! | Read more »
Bowmasters tips, tricks and hints
At least for this writer, archery was one of the more pleasant surprises of the 2016 Rio Olympics. As opposed to target shooting with guns, which was dreadfully boring, watching people shoot arrows at targets was pretty darn cool. [Read more] | Read more »
Best apps for watching live TV
The Olympics have come and gone, leaving nearly everyone in a temporary state of "What the heck am I going to watch on TV right now?" Besides old reruns of Golden Girls, but that goes without saying. [Read more] | Read more »
What is Flip Diving, and why has it take...
Move over Pokemon GO. There's a new king in town, and it's "the world's #1 cliff diving game." [Read more] | Read more »
5 places where Pokemon GO is still numbe...
In the U.S., the bloom is off the Pokemon Go rose ever so slightly. It's still doing great, sitting atop the top grossing chart as it has for some time, but it's no longer among the top 10 free apps in downloads, possibly because darn near... | Read more »

Price Scanner via MacPrices.net

Apple refurbished 13-inch MacBook Airs availa...
Apple has Certified Refurbished 2016 and 2015 13″ MacBook Airs now available starting at $849. An Apple one-year warranty is included with each MacBook, and shipping is free: - 2016 13″ 1.6GHz/8GB/... Read more
Apple refurbished iPad mini 2s available for...
Apple is offering Certified Refurbished iPad mini 2s for up to $80 off the cost of new minis. An Apple one-year warranty is included with each model, and shipping is free: - 16GB iPad mini 2 WiFi: $... Read more
Save up to $600 with Apple refurbished Mac Pr...
Apple has Certified Refurbished Mac Pros available for up to $600 off the cost of new models. An Apple one-year warranty is included with each Mac Pro, and shipping is free. The following... Read more
Mac Pros on sale for $200 off MSRP
B&H Photo has Mac Pros on sale for $200 off MSRP. Shipping is free, and B&H charges sales tax in NY only: - 3.7GHz 4-core Mac Pro: $2799, $200 off MSRP - 3.5GHz 6-core Mac Pro: $3799, $200... Read more
Will We See A 10.5″ iPad Pro in 2017? – The ‘...
A MacRumors report, cites a research note from KGI Securities analyst Ming-Chi Kuo, saying a new size iPad model is in the works. According to the highly respected Cho, who has a strong track record... Read more
IOGEAR USB-C Docking Station Transforms Lapto...
IOGEAR has announced the launch of its innovative USB-C Docking Station with Power Delivery which turns USB-C enabled laptops into desktop workstations. The new IOGEAR USB-C Docking Station features... Read more
12-inch Retina MacBooks on sale for up to $10...
Amazon has 2016 12″ Apple Retina MacBooks on sale for $100 off MSRP. Shipping is free: - 12″ 1.1GHz Space Gray Retina MacBook: $1199 $100 off MSRP - 12″ 1.1GHz Silver Retina MacBook: $1224.99 $75 off... Read more
13-inch 2.5GHz MacBook Pro (Apple refurbished...
Apple has Certified Refurbished 13″ 2.5GHz MacBook Pros available for $829, or $270 off the cost of new models. Apple’s one-year warranty is standard, and shipping is free: - 13″ 2.5GHz MacBook Pros... Read more
21-inch iMacs on sale for up to $120 off MSRP
B&H Photo has 21″ iMacs on sale for up to $120 off MSRP including free shipping plus NY sales tax only: - 21″ 3.1GHz iMac 4K: $1379 $120 off MSRP - 21″ 2.8GHz iMac: $1199.99 $100 off MSRP - 21″ 1... Read more
Typinator 6.10 comes with 50 improvements – G...
Ergonis Software today announced release of Typinator 6.10, a new version of their text expander utility for macOS. Typinator 6.10 comes with 50 improvements, including new features, compatibility... Read more

Jobs Board

*Apple* Mobile Master - Best Buy (United Sta...
What does a Best Buy Apple Mobile Master do? At Best Buy, our mission is to leverage the unique talents and passions of our employees to inspire, delight, and enrich Read more
*Apple* Retail - Multiple Positions Akron, O...
Job Description: Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
Simply Mac *Apple* Specialist- Repair Techn...
…The Technician is a master at working with our customers to diagnose and repair Apple devices in a manner that exceeds the expectations set forth by Apple Read more
*Apple* Retail - Multiple Positions Germanto...
Job Description: Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
*Apple* Professional Learning Specialist - A...
# Apple Professional Learning Specialist Job Number: 51234379 Portland, Maine, Maine, United States Posted: Aug. 18, 2016 Weekly Hours: 40.00 **Job Summary** The Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.