MySQL and Mac OS X

Ubuntu 18.04 Update

With MySQL now also running on my Ubuntu Linux server, I had to reconfigure the server to allow access to the database from any machine on my network.

$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

Change the following line:
bind-address = 127.0.0.1
to:
bind-address = 0.0.0.0

Restart the server and all is good.
$ sudo systemctl restart mysql.service

Leopard Update

With the release of Mac OS X 10.5 "Leopard", Apple has made a few more changes. Whereas prior versions came with Apache 1.3, PHP 4, and MySQL 4, Leopard comes (sort of) pre-configured with Apache 2.2 and PHP 5. The MySQL part is still a little tricky.

Here are some notes regarding MySQL under 10.5:

1. There is still the discrepancy between the old and new locations for mysql.sock
2. NetInfo is no longer available for setting up users & groups, however...
3. Leopard comes configured with a _mysql user and _mysql group
4. mysqld is deprecated. Instead we're supposed to use launchd
5. CocoaMySQL development has been abandoned. It's replacment is Sequel Pro

Mac OS X provides a utility for interacting with launchd. Running it with no arguments brings up an interactive command-line interface. However, to use it to launch mysqld, proceed with the following steps:

1. create a plist file called com.mysql.mysqld.plist containing the following
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
<key>Disabled</key>
<false/>
<key>GroupName</key>
<string>_mysql</string>
<key>KeepAlive</key>
<true/>
<key>Label</key>
<string>com.mysql.mysqld</string>
<key>Program</key>
<string>/usr/local/mysql/bin/mysqld</string>
<key>ProgramArguments</key>
<array>
<string>--user=_mysql</string>
</array>
<key>RunAtLoad</key>
<true/>
<key>Umask</key>
<integer>7</integer>
<key>UserName</key>
<string>_mysql</string>
<key>WorkingDirectory</key>
<string>/usr/local/mysql</string>
</dict>
</plist>

Mac OS X (both the client & server versions) comes with MySQL pre-installed. I discovered this while I was searching for a way to index and play my digital music. Enter NetJuke, an open-source digital music management system based on MySQL which uses a PHP/Web browser front end. So I started fumbling around.

Configuring the MySQL Server

First launch the MySQL server daemon from Terminal:
$ sudo mysqld_safe --user=mysql &

Next add a bit of security to the installation:
$ mysqladmin -u root password foobar

At this point you may receive the following error:

error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists!

If so, follow the instructions below under "MySQL 5 Socket". Otherwise, you are ready to roll your own database.

To connect to a MySQL server other than the localhost requires additional steps as detailed below. First, launch mysql from the machine running the MySQL server (Your user and password may vary):
Poseidon$ mysql -u root -p

Next, enter the following (all one line):

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.0.%'
IDENTIFIED BY "password" WITH GRANT OPTION

You will need to substitute the appropriate IP address and password. The "%" is a wildcard which I used to allow connection from any host on my LAN.

This worked like a charm for me, and now I am able to use the CocoaMySQL application to manage all my databases from any machine on my LAN.

Another (simpler) option for setting the root password is to use the mysqladmin utility:

$ mysqladmin -u root password NEWPASSWORD

To shutdown the MySQL server:
# mysqladmin -u root -p shutdown

- will prompt for password and shut down MySQL server.

Using the MySQL Client

I'm compiling this document as I learn MySQL, so if it's slightly non-linear, muddled, or incomplete, well, that's par for the course.

My ultimate goal is to learn how to build web-sites with dynamic content so they can be managed by non-technical end-users via a web/PHP interface. My reference (which is, unfortunately, built using the ASP.NET framework) is the well-designed [Quester Gallery|http://www.questergallery.com] site. This works well for me since many of the sites I build are for artists. The goal is for all content to be managed by a PHP interface with categories, media types, artist names, images of the items, and so on stored in MySQL tables.

So here are some MySQL tips commands as I learn about them. Also peppered through here will be usage tips for PHPMyAdmin, the PHP-based administration front-end for MySQL, and PHPFriendly, which I'm not exactly sure I need or how it fits in to the overall system.

General Notes in no logical order

If you followed the steps from the beginning, the MySQL server should already be running on your machine. To launch MySQL client and connect to the database "example":

$ mysql -u -p example
Password:

To connect to a MySQL server other than on the localhost:
$ mysql -u -p -h

To change the mysql command prompt:
mysql> prompt (\u@\h) [\d]>\_
PROMPT set to '(\u@\h) [\d]>\_'
(user@host) [database]>

To change it back to the default:
(user@host) [database]> prompt
Returning to default PROMPT of mysql>
mysql>

To connect to an existing database:
mysql> use example;

Once connected to a database:
mysql> show tables;

Display columns of a specific table:
mysql> desc users;

Create a database:
mysql> create database example;

Show all databases:
mysql> show databases;

Create a new table:
mysql> CREATE TABLE tardis ( id int(11) AUTO_INCREMENT, \
type varchar(32) NOT NULL, content text NOT NULL, PRIMARY KEY \
(`id`) );

The back slash is the line continuation character. You can type the entire command on a single line without the backslashes.

Add a column to a table:
mysql> ALTER TABLE table_name ADD COLUMN column_name column_definition;

Add a column of type enum:
mysql> ALTER TABLE table_name ADD COLUMN column_name enum('Y', 'N') DEFAULT 'Y' NOT NULL;

Add a "row" to a table:
mysql> INSERT INTO 'table' (column) VALUES ('value');

Update a "row" in a table:
mysql> UPDATE table_name SET column1='value', column2='value' WHERE id = '10';

Delete a "row" in a table:
mysql> mysql> DELETE FROM table_name WHERE column1='value';

Note that the following two are Terminal/Shell commands.

Export the contents of a database:
$ mysqldump -u username -ppassword database_name > FILE.sql

Import the contents of a previously exported database:
$ mysql -u username -ppassword database_name < FILE.sql

You can also include the -h option on either of the above

Rename a table:
mysql> ALTER TABLE old_table_name RENAME TO new_table_name;

Rename a column in a table:
mysql> ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name column_definition;

for example:
mysql> ALTER TABLE users CHANGE COLUMN id_foo id_bar int(11);

Delete one or more columns from in a table (both the data and structure):
mysql> ALTER TABLE table_name DROP COLUMN column_a, DROP COLUMN column_b;

Empty a table of all data while leaving the structure intact:
mysql> DELETE FROM table_name;

Export data to a tab-delimited text file:
mysql> SELECT * INTO OUTFILE "test.txt" FIELDS ENCLOSED BY '"' TERMINATED BY '\t' LINES TERMINATED BY '\
n' FROM tbl_name;

Note that this will create a file named "test.txt" in the MySQL _server_ directory. Under Mac OSX, this may be something like /usr/local/mysql/bin/data/tests

MySQL 5 Socket

Mac OS X 10.3 (Panther) came with MySQL version 4.0.18, while Mac OS X 10.4 (Tiger) shipped with MySQL version 5. Unfortunately, when I upgraded to Tiger, I discovered that netjuke doesn't support MySQL 5, so I had to downgrade to version 4. This wasn't a big deal (plus I've forgotten the details), but there were other issues as well.

For some reason, Apple decided to change the socket that the mysql admin utility uses to connect to the server. Under Panther, it was
/tmp/mysql.sock

Under Tiger, it became
/var/mysql/mysql.sock

I discovered this while attempting to use the excellent GUI MySQL admin utility by the name of CocoaMySQL http://cocoamysql.sourceforge.net.

The proper way to fix this is probably to run the mysql_config utility. Prior to figuring that out, however, I came up with the following solution which also works, but is a bit of a kludge.

On the machine which is running the MySQL server, launch Terminal and create the directory (if it doesn't already exist):
Poseidon$ sudo mkdir /var/mysql

Next, create a symbolic link pointing to the original socket:
Poseidon$ sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock

This worked like a charm for me. After creating the symbolic link, I was able to run the mysql utility.

PHPMyAdmin

This tutorial assumes you are already running Apache and have mod_php enabled.

PHPMyAdmin is a PHP front-end for MySQL database administration. Begin by downloading the installer from the PHPMyAdmin Web Site.

Unzip, untar, etc, and copy it to an appropriate folder on your web server. On Mac OS X, that would be something like /Library/WebServer/Documents/phpmyadmin.

From here on, all configuration is done within your web browser. Point it to the appropriate directory on your web server. In my case:

http://poseidon/phpmyadmin/scripts/setup.php

Click on "Add Server". On the next page, Fill in the MySQL user name in the field labeled "User for config auth" and password in the "Password for config auth".

To take advantage of PHPMyAdmin advanced features, you will setup a database and add the appropriate information to the PHPMyAdmin user, password, and database fields.

Unless you know what you're doing, leave the other fields black to accept the default settings. Click Add, then save the server.

A configuration file called "config.inc.php" will be created in the "config" subdirectory of the phpmyadmin directory. Move it to the phpmyadmin main directory and you area ready to go.