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
- Double click the MySQL package (rather than the startup packge), and install away. My installation completed without issues.
- 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:
- Shutdown MySQL (mysqladmin -uroot shutdown)
- Use the editor to change all occurrences of
/etc/my.conffile - Create
/var/mysql/and set full access to thissudo mkdir /var/mysql sudo chmod 777 /var/mysql
- 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
- Creating databases, tables, granting rights etc: http://www.webdevelopersnotes.com/tutorials/sql/mysql_lesson_mysql_tables.php3
- Fixing the socket problem: http://remysharp.com/2007/10/27/lamp-in-leopard-osx-105-php5-and-apache-22/