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.

 
AAPL
$567.77
Apple Inc.
+43.02
MSFT
$39.86
Microsoft Corpora
+0.17
GOOG
$525.16
Google Inc.
-1.78

MacTech Search:
Community Search:

Software Updates via MacUpdate

Ember 1.5.1 - Versatile digital scrapboo...
Ember (formerly LittleSnapper) is your digital scrapbook of things that inspire you: websites, photos, apps or other things. Just drag in images that you want to keep, organize them into relevant... Read more
Cyberduck 4.4.4 - FTP and SFTP browser....
Cyberduck is a robust FTP/FTP-TLS/SFTP browser for the Mac whose lack of visual clutter and cleverly intuitive features make it easy to use. Support for external editors and system technologies such... Read more
TechTool Pro 7.0.3 - Hard drive and syst...
TechTool Pro is now 7, and this is the most advanced version of the acclaimed Macintosh troubleshooting utility created in its 20-year history. Micromat has redeveloped TechTool Pro 7 to be fully 64... Read more
MacFamilyTree 7.1.6 - Create and explore...
MacFamilyTree gives genealogy a facelift: it's modern, interactive, incredibly fast, and easy to use. We're convinced that generations of chroniclers would have loved to trade in their genealogy... Read more
EtreCheck 1.9.9 - For troubleshooting yo...
EtreCheck is a simple little app to display the important details of your system configuration and allow you to copy that information to the Clipboard. It is meant to be used with Apple Support... Read more
TeamViewer 9.0.28116 - Establish remote...
TeamViewer gives you remote control of any computer or Mac over the Internet within seconds, or can be used for online meetings. Find out why more than 200 million users trust TeamViewer! Free for... Read more
DiskAid 6.6.3 - Use your iOS device as a...
DiskAid is the ultimate transfer tool for accessing the iPod, iPhone or iPad directly from the desktop. Access data such as: music, video, photos, contacts, notes, call history, text messages (SMS),... Read more
Viber 4.1.0 - Send messages and make cal...
Viber lets you send free messages and make free calls to other Viber users, on any device and network, in any country! Viber syncs your contacts, messages and call history with your mobile device,... Read more
Apple iOS 7.1.1 - The latest version of...
The latest version of iOS can be downloaded through iTunes. Apple iOS 7 brings an all-new design and all-new features. Simplicity Simplicity is often equated with minimalism. Yet true simplicity is... Read more
1Password 4.3 - Powerful password manage...
1Password is a password manager that uniquely brings you both security and convenience. It is the only program that provides anti-phishing protection and goes beyond password management by adding Web... Read more

Latest Forum Discussions

See All

Pen & Ink Review
Pen & Ink Review By Jennifer Allen on April 24th, 2014 Our Rating: :: CONVENIENT ARTISTRYiPad Only App - Designed for the iPad Pen & Ink is an ideal way to sketch down some visual ideas or simply spark your imagination.   | Read more »
Sonic & All-Stars Racing Transformed...
Sonic & All-Stars Racing Transformed Now Free, to Add New Iconic Characters and Elements Posted by Tre Lawrence on April 24th, 2014 [ | Read more »
Soccer Rally 2 Review
Soccer Rally 2 Review By Carter Dotson on April 24th, 2014 Our Rating: :: GOALKEEPINGUniversal App - Designed for iPhone and iPad Soccer Rally 2 is the most serious vehicular soccer game ever created.   | Read more »
Galaxy Conquerors Review
Galaxy Conquerors Review By Jennifer Allen on April 24th, 2014 Our Rating: :: RETRO SHOOTINGUniversal App - Designed for iPhone and iPad Old school shooting is fun but inaccurate in Galaxy Conquerors.   | Read more »
Yomi Review
Yomi Review By Rob Thomas on April 24th, 2014 Our Rating: :: C-C-C-COMBO BREAKERiPad Only App - Designed for the iPad Round One – Fight! No quarters required for this iOS adaptation of a tabletop adaptation of the arcade fighting... | Read more »
Injustice: Gods Among Us Updated with Ne...
Injustice: Gods Among Us Updated with New Characters, Leaderboards, Gear, and Online Multiplayer Posted by Rob Rich on April 24th, 2014 [ | Read more »
Spin It Review
Spin It Review By Jordan Minor on April 24th, 2014 Our Rating: :: SPIN ME RIGHT ROUNDUniversal App - Designed for iPhone and iPad Spin It has a fine puzzle game model, but its execution lacks energy.   | Read more »
Productivity App NoteSuite is Having its...
Productivity App NoteSuite is Having its Biggest Sale Ever, Just for One Week Posted by Rob Rich on April 24th, 2014 [ permalink ] | Read more »
Wayward Souls Review
Wayward Souls Review By Carter Dotson on April 24th, 2014 Our Rating: :: CARRY ON, WAYWARD SONUniversal App - Designed for iPhone and iPad Wayward Souls is a roguelike-inspired action-RPG that sets a high bar for other games to... | Read more »
The Sandbox Gets Update, Receives New Ca...
The Sandbox Gets Update, Receives New Campaign and New Elements Posted by Tre Lawrence on April 24th, 2014 [ permalink ] Universal App - Designed for iPhone and iPad | Read more »

Price Scanner via MacPrices.net

Strong iPhone Sales Drive Apple Record March...
Apple on Wednesday announced financial results for its fiscal 2014 second quarter ended March 29, 2014. The Company posted quarterly revenue of $45.6 billion and quarterly net profit of $10.2 billion... Read more
Award-Winning NoteSuite Productivity App is $...
Minneapolis based Theory.io has announced an 80-Percent Markdown NoteSuite for iPad. NoteSuite helps users stay organized by capturing their notes, to-dos and documents in one organized place.... Read more
16GB 1st generation iPad mini available for $...
Radio Shack has a select number of refurbished 1st generation 16GB WiFi iPad minis available for $199.99 on their online store. Choose free shipping or free ship-to-store. We expect these to sell out... Read more
13-inch 2.5GHz MacBook Pro on sale for $100 o...
B&H Photo has the 13″ 2.5GHz MacBook Pro on sale for $1099 including free shipping plus NY sales tax only. Their price is $100 off MSRP. Read more
iPad Sales “Lull” A Reality Correction Of Unm...
I have lots of time for Jean-Louis Gassée, the former Apple Computer executive (1981 to 1990) who succeeded Steve Jobs as head of Macintosh development when the latter was dismissed in 1985. Mr.... Read more
Apple Makes OS X Betas Available To All – Wit...
Apple’s OS X Beta Seed Program, which lets you install the latest pre-release builds, try it out, and submit your feedback, is now open to anyone who wants to sign on rather than to developers and... Read more
Apple Releases iOS 7.1.1 Update
The latest iOS 7.1.1 update contains improvements, bug fixes and security updates, including: • Further improvements to Touch ID fingerprint recognition • Fixes a bug that could impact keyboard... Read more
Logitech Announces Thinner, Lighter, More Fle...
Logitech has announced an update to its Ultrathin for iPad Air, iPad mini and iPad mini with Retina display, improving the flexibility and design of its award-winning predecessor with an even thinner... Read more
Logitech Introduces Hinge, Big Bang and Turna...
Logitech has announced expansion of its tablet product line with three new cases – the Logitech Hinge, the Logitech Big Bang and the Logitech Turnaround – each for the iPad Air, iPad mini and iPad... Read more
WaterField’s Rough Rider Leather Messenger Ba...
WaterField Designs have announced the new 15-inch size of their popular Rough Rider leather messenger bag, a vintage-looking bag that combines Old West charm and ruggedness with distinctly modern... Read more

Jobs Board

*Apple* Solutions Consultant (ASC) - Apple (...
**Job Summary** The ASC is an Apple employee who serves as an Apple brand ambassador and influencer in a Reseller's store. The ASC's role is to grow Apple Read more
*Apple* Solutions Consultant (ASC) - Apple (...
**Job Summary** The ASC is an Apple employee who serves as an Apple brand ambassador and influencer in a Reseller's store. The ASC's role is to grow Apple Read more
Position Opening at *Apple* - Apple (United...
…customers purchase our products, you're the one who helps them get more out of their new Apple technology. Your day in the Apple Store is filled with a range of Read more
*Apple* Solutions Consultant (ASC) - Apple (...
**Job Summary** The ASC is an Apple employee who serves as an Apple brand ambassador and influencer in a Reseller's store. The ASC's role is to grow Apple Read more
*Apple* Inc. Research Data Specialist - Appl...
…of Worldwide Market Research & Intelligence. The team is responsible for conducting Apple branded consumer market research. It is also responsible for analyzing data Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.