Part2: Installing and configuring mySQL

This is the second part of the article series detailing how to set up and configure a complete WordPress Development Environment on a MacBook Pro.

The MySQL Database Server

This is a must for a development environment on your Mac, so go ahead register and download the free MySQL Community Server.

There is a huge list of packages available for different platforms, make sure you select the right right one (Intel based machines are the x86 installs). Because I am running Leopard on this MacBook Pro, I selected mysql-5.0.67-osx10.5-x86.dmg

Although you do not have to fill in all the details on the downloads page, you should consider this as a small token of appreciation of the efforts by the team behind this fantastic database system.

Installation

  1. Double click the MySQL package (rather than the startup packge), and install away. My installation completed without issues.
  2. Double click on the mySQL.PrefPane and select “install” when prompted. This is the preference tool letting you start and stop the MySQL server.

And that is it…

Verification

Check the installation from the terminal:

>tbrams$ /usr/local/mysql/bin/mysql
>Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 8
>Server version: 5.0.67 MySQL Community Server (GPL)
>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>mysql> show databases;
>+--------------------+
>| Database           |
>+--------------------+
>| information_schema | 
>| test               | 
>+--------------------+<
>2 rows in set (0.00 sec)
>mysql> 

So we are in business. Remember to set up the mysql binary directory in your path, so you do not have to remember and retype the entire path every time.

Go to you login directory and create/modify your .profile to include this, for example like:

>echo 'export PATH=$PATH:/usr/local/mysql/bin' >> ~/.profile

Now, if you want to test this, use the source command to process it, like source .profile and you should be able to start the MySQL client by typing myslq on the command line.

Setting up the databases

wsip-70-165-185-216:~ tbrams$ mysqladmin -uroot create brams
wsip-70-165-185-216:~ tbrams$ mysqladmin -uroot create sonnal
wsip-70-165-185-216:~ tbrams$ mysqladmin -uroot create ltk
wsip-70-165-185-216:~ tbrams$ 
wsip-70-165-185-216:~ tbrams$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.0.67 MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| brams              | 
| ltk                | 
| mysql              | 
| sonnal             | 
| test               | 
+--------------------+
6 rows in set (0.00 sec)
mysql>

Granting access is necessary because these tables are made as root. Here is an example, where I am creating a user called tbrams with the password b117 in the brams database:

mysql> grant all on brams.* to tbrams@localhost identified by "b117"

After this MySQL has to be started with a username and password. So from now on, write something like $ mysql brams -u tbrams -p to get started. It will look like something along these lines:

$ mysql brams -u tbrams -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.0.67 MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.<
mysql> CREATE TABLE employee_data
 -> ( -> emp_id int unsigned not null auto_increment primary key,  -> f_name varchar(20),  -> l_name varchar(20),  -> title varchar(30),  -> age int,  -> yos int, -> salary int,  -> perks int,  -> email varchar(60)  -> );
Query OK, 0 rows affected (0.00 sec)
mysql> show tables
 -> ;
+-----------------+
| Tables_in_brams |
+-----------------+<
| employee_data   | 
+-----------------+
1 row in set (0.00 sec)

Check the new table by using the describe command

mysql> DESCRIBE employee_data;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| emp_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| f_name | varchar(20)      | YES  |     | NULL    |                | 
| l_name | varchar(20)      | YES  |     | NULL    |                | 
| title  | varchar(30)      | YES  |     | NULL    |                | 
| age    | int(11)          | YES  |     | NULL    |                | 
| yos    | int(11)          | YES  |     | NULL    |                | 
| salary | int(11)          | YES  |     | NULL    |                | 
| perks  | int(11)          | YES  |     | NULL    |                | 
| email  | varchar(60)      | YES  |     | NULL    |                | <
+--------+------------------+------+-----+---------+----------------+<
9 rows in set (0.01 sec)
mysql> 

Fill something into this table by creating a file with this content in the sites/brams directory

INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("John", "Hagan", "Senior Programmer", 32, 4, 120000, 25000, "john_hagan@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Ganesh", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g_pillai@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Anamika", "Pandit", "Web Designer", 27, 3, 90000, 15000, "ana@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 26, 2, 85000, 15000, "mary@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Fred", "Kruger", "Programmer", 31, 3, 75000, 15000, "fk@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("John", "MacFarland", "Programmer", 34, 4, 80000, 16000, "john@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Edward", "Sakamuro", "Programmer", 25, 2, 75000, 14000, "eddie@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "alok@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Hassan", "Rajabi", "Multimedia Programmer", 33, 3, 90000, 15000, "hasan@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 43, 2, 85000, 12000, "ps@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Arthur", "Hoopla", "Multimedia Programmer", 32, 1, 75000, 15000, "arthur@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 2, 110000, 20000, "kim@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 35, 2, 100000, 13000, "roger@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 34, 1, 90000, 12000, "danny@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 2, 120000, 28000, "mike@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Monica", "Sehgal", "Marketing Executive", 30, 3, 90000, 25000, "monica@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Hal", "Simlai", "Marketing Executive", 27, 2, 70000, 18000, "hal@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Joseph", "Irvine", "Marketing Executive", 27, 2, 72000, 18000, "joseph@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Shahida", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 36, 4, 120000, 25000, "peter@bignet.com");

Then run the script in MySQL by

mysql brams < employee.dat -u tbrams -p

Check the table - afterwards there should be 26 rows in there:

mysql> select count(*) from employee_data;
+----------+
| count(*) |
+----------+
|       26 |
+----------+
1 row in set (0.00 sec)

mysql>

Checking access rights

Just in case you run into an error message like Cannot establish a connection to the database later, one of many reasons may be that you forgot the step above. Here is how this would look in MySQL:

mysql> show grants for 'tbrams'@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for tbrams@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tbrams'@'localhost' IDENTIFIED BY PASSWORD '*B323D6D364162817C52B4F990D3695BDA32C1758' |
| GRANT ALL PRIVILEGES ON `brams`.* TO 'tbrams'@'localhost'                                                     |
| GRANT ALL PRIVILEGES ON `sonnal`.* TO 'tbrams'@'localhost'                                                    |
+---------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

In this example, I apparently forgot to grant myself access right to the LTK database. Here is how to remidy that:

mysql> grant all on ltk.* to tbrams@localhost identified by "b117";
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'tbrams'@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for tbrams@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tbrams'@'localhost' IDENTIFIED BY PASSWORD '*B323D6D364162817C52B4F990D3695BDA32C1758' |
| GRANT ALL PRIVILEGES ON `brams`.* TO 'tbrams'@'localhost'                                                     |
| GRANT ALL PRIVILEGES ON `sonnal`.* TO 'tbrams'@'localhost'                                                    |
| GRANT ALL PRIVILEGES ON `ltk`.* TO 'tbrams'@'localhost'                                                       |
+---------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql>

Getting PHP into the euquation

With enough test data in the database, we can compose a small php script pulling first and last names from the table and preseting as simple text strings in the browser:

<?php
$con = mysql_connect("localhost","tbrams","b117");
if (!$con) {
   die('Could not connect: ' . mysql_error());
}
mysql_select_db("brams", $con);
$result = mysql_query("SELECT * FROM employee_data");
while($row = mysql_fetch_array($result)) {
   echo $row['f_name'] . " " . $row['l_name'];
   echo "<br />";
}
mysql_close($con);
?>

Fixing the PHP/MySQL issue

Unfortunately this does not run out of the box. There is a configuration problem build into the combined installation of MySQL and PHP/Apache2 on the Leopard system results in a socket error that we need to fix:

Warning:  mysql_connect() [function.mysql-connect]: Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2) in /Users/tbrams/Sites/brams/index.php on line 2
Could not connect: Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2)

This problem has to do with the location of the mysql.sock file. Previously, before Leopard, the default location for this file was in the /tmp</samp> directory. That location has now moved to the /var/mysql</samp> directory. PHP will look for it there.

Unfortunately, by default MySQL will still place it in the old location. So, we have a problem.

We can fix this by creating a my.cnfconfiguration file in the /etc</span> directory.

Unless you already have a MySQL config file in /etc/my.cnf, then you need to copy a template out of the support files from MySQL. I copied the small memory configuration template to /etc/my.cnf and launched my favorite editor.

sudo cp /usr/local/mysql-5.0.67-osx10.5-x86/support-files/my-small.cnf /etc/my.cnf
cd /etc
mate my.cnf (or any other editor you like)

You will need to do this:

  1. Shutdown MySQL (mysqladmin -uroot shutdown)
  2. Use the editor to change all occurrences of /tmp/mysql.sock to /var/mysql/mysql.sock in the /etc/my.conf file
  3. Create /var/mysql/ and set full access to this
    sudo mkdir /var/mysql
    sudo chmod 777 /var/mysql
    
  4. Start MySQL back up again
    cd /usr/local/mysql/
    sudo ./bin/safe_mysqld &
    

Everything up and running

Now try the http://brams/ again in the browser and you should see the list with employee names in the browser.

What is next

Read about how to install and configure WordPress in the next part of this article series.

References

Posted in Apple, Technotes | Leave a comment