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

BusyContacts 1.6.4 - Fast, efficient con...
BusyContacts is a contact manager for OS X that makes creating, finding, and managing contacts faster and more efficient. It brings to contact management the same power, flexibility, and sharing... Read more
Steam 4.0 - Multiplayer and communicatio...
Steam is a digital distribution, digital rights management, multiplayer and communications platform developed by Valve Corporation. It is used to distribute a large number of games and related media... Read more
OmniGraffle Pro 7.19.3 - 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 7.19.3 - Create diagrams, fl...
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
Hopper Disassembler 5.3.3- - Binary disa...
Hopper Disassembler is a binary disassembler, decompiler, and debugger for 32- and 64-bit executables. It will let you disassemble any binary you want, and provide you all the information about its... Read more
calibre 5.35.0 - Complete e-book library...
Calibre is a complete e-book library manager. Organize your collection, convert your books to multiple formats, and sync with all of your devices. Let Calibre be your multi-tasking digital librarian... Read more
Sound Studio 4.10.0 - Robust audio recor...
Sound Studio lets you easily record and professionally edit audio on your Mac. Easily rip vinyls and digitize cassette tapes, or record lectures and voice memos. Prepare for live shows with live... Read more
Sparkle Pro 4.0 - Visual website creator...
Sparkle Pro will change your mind if you thought building websites wasn't for you. Sparkle is the intuitive site builder that lets you create sites for your online portfolio, team or band pages, or... Read more
Dropbox 140.4.1951 - Cloud backup and sy...
Dropbox for Mac is a file hosting service that provides cloud storage, file synchronization, personal cloud, and client software. It is a modern workspace that allows you to get to all of your files... Read more
FotoMagico 6.0.5 - Powerful slideshow cr...
FotoMagico lets you create professional slideshows from your photos and music with just a few, simple mouse clicks. It sports a very clean and intuitive yet powerful user interface. High image... Read more

Latest Forum Discussions

See All

Best iPhone Game Updates: ‘Garena Free F...
Hello everyone, and welcome to the week! It’s time once again for our look back at the noteworthy updates of the last seven days. I got busted last week for not including the obligatory free-to-play matching puzzle game update of the week, and my... | Read more »
‘Horizon Chase’ China Spirit DLC Release...
Following the release of the excellent reveal of the Horizon Chase Senna Forever expansion, the game will be getting a new DLC on mobile platforms today. Today, the Horizon Chase China Spirit DLC pack will release on iOS and Android bringing in 9... | Read more »
‘PUZZLED’ from SNK and Hamster Is Out No...
Following ZED BLADE ACA NeoGeo earlier this month, SNK has brought over another game in the ACA NeoGeo series to both iOS and Android in the form of PUZZLED. SNK and Hamster originally brought the series to mobile with Samurai Shodown IV, Alpha... | Read more »
A House Full of Covid – The TouchArcade...
It’s been a rough week as both of our young children tested positive for Covid, and since recording this early on Friday my wife has tested positive now too. Thankfully the kids seemed to recover fairly quickly and are mostly back to normal, and I... | Read more »
TouchArcade Game of the Week: ‘Krispee S...
Krispee Street is a new hidden object game from Frosty Pop that is based on their popular and almost painfully sweet webcomic Krispee. This is one of the latest titles to be added to the Netflix Games catalog, which means you’ll need to log into... | Read more »
SwitchArcade Round-Up: ‘Escape Lala’, ‘B...
Hello gentle readers, and welcome to the SwitchArcade Round-Up for January 21st, 2022. In today’s article, we’ve got a lot of new releases. A lot. There were eight on the schedule when I went to bed last night. There were twenty-four when I woke up... | Read more »
Beta Testers Needed for Huge Version 2.0...
Ya’ll remember Dungeon Raid, right? The phenomenal matching RPG hybrid that launched on mobile more than a decade ago, but was more or less abandoned by its developer only to die a slow death on the App Store before the 32-bit Appocalypse finally... | Read more »
‘Ark Legends’ Gives Players a Chance to...
It’s Airpods and Amazon gift cards galore as Melting Games opens pre-registration for Ark Legends. The upcoming mobile RPG is giving away tons of in-game goodies such as gold, energy, iron core, hero summon chest and rare iron core to players who... | Read more »
‘Nickelodeon Extreme Tennis’ Out Now on...
Nickelodeon Extreme Tennis () from Old Skull Games and Nickelodeon is this week’s new Apple Arcade release. Nickelodeon Extreme Tennis features characters from old and new Nickelodeon shows including SpongeBob, TMNT, and many more. The tennis game... | Read more »
SwitchArcade Round-Up: ‘RPGolf Legends’,...
Hello gentle readers, and welcome to the SwitchArcade Round-Up for January 20th, 2022. In today’s article, we’ve got a massive amount of new releases to check out. We’ve got summaries of all of them, from heaven to hell. We also have the lists of... | Read more »

Price Scanner via MacPrices.net

Verizon’s 2022 iPad promo: $100-$310 off any...
Verizon has cellular-capable iPads on sale for $100-$310 off MSRP when purchased with an Unlimited service plan. Sale price is applied to your account monthly over a 24 or 30 month period, depending... Read more
Sunday Sale: Apple AirPods are on sale for up...
Amazon has Apple AirPods on sale for $10-$100 off MSRP today, depending on the model. All are in stock today with free delivery: – AirPods Max headphones (Blue): $449 $100 off MSRP – AirPods Max... Read more
These Apple resellers are offering 13″ M1 Mac...
Apple resellers are offering discounts on 13″ MacBook Pros with M1 Apple Silicon processors ranging up to $150 off MSRP. Here’s where to get one today: (1): Apple’s 13″ MacBook Pros with M1 Apple... Read more
Amazon lowers prices on select 13″ M1 MacBook...
Amazon has select Apple 13″ M1 MacBook Airs on sale for $150 off MSRP this weekend, starting at only $849. Their prices are the lowest available for new MacBook Airs today. Stock may come and go, so... Read more
Apple has 13″ M1 MacBook Airs back in stock s...
Apple has restocked a full line of 13″ M1 MacBook Airs, Certified Refurbished, starting at only $849 and up to $190 off original MSRP. These are the cheapest M1-powered MacBooks for sale today at... Read more
In stock and on sale! 16″ 10-Core M1 Pro MacB...
Amazon has new 16″ 10-Core/512GB M1 Pro MacBook Pros in stock today and on sale for $50 off MSRP including free shipping. Their prices are the lowest available for new M1 Pro 16″ MacBook Pro from any... Read more
Deal Alert!: 14″ M1 Pro with 10-Core CPU in s...
Amazon has the new 14″ M1 Pro MacBook Pro with a 10-Core CPU and 16-Core GPU in stock today and on sale for $2299.99 including free shipping. Their price is $200 off Apple’s standard MSRP, and it’s... Read more
Apple has 24-inch M1 iMacs (8-Core CPU/8-Core...
Apple has restocked a wide array of 24-inch M1 iMacs with 8-Core CPUs and 8-Core GPUs in their Certified Refurbished store. Models are available starting at only $1269 and range up to $260 off... Read more
Select 24″ M1 iMacs are on sale for $100 off...
Sales of Apple’s new 24″ M1 iMacs have been rare since its introduction, perhaps due to global supply issues. However, B&H is offering a $100 discount on select 24″ iMacs, and they’re in stock... Read more
M1 Mac minis are back in stock today at Apple...
Apple has M1-powered Mac minis available in their Certified Refurbished section starting at only $589 and up to $140 off MSRP. Each mini comes with Apple’s one-year warranty, and shipping is free: –... Read more

Jobs Board

Registered Nurse (RN) Employee Health PSJH -...
…is calling for a Registered Nurse (RN) Employee Health PSJH to our location in Apple Valley, CA.** We are seeking a Registered Nurse (RN) Employee Health PSJH to be Read more
Systems Administrator - Pearson (United State...
…and troubleshoot Windows operating systems (workstation and server), laptop computers, Apple iPads, Chromebooks and printers** + **Administer and troubleshoot all Read more
IT Assistant Level 1- IT Desktop Support Anal...
…providing tier-1 or better IT help desk support in a large Windows and Apple environment * Experience using IT Service Desk Management Software * Knowledge of IT Read more
Human Resources Business Partner PSJH - Provi...
…**is calling a** **Human Resources Business Partner, PSJH** **to our location in Apple Valley, CA.** **Applicants that meet qualifications will receive a text with Read more
Manager Community Health Investment Programs...
…is calling a Manager Community Health Investment Programs PSJH to our location in Apple Valley, CA.** **Qualified candidates will be invited to do a self-paced video Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.