14.1 MySQL

To get the source distribution of MySQL, download the latest tarball from http://www.mysql.com/downloads/. At the time of this writing, the latest production release was the 4.0. x series; we downloaded mysql-4.0.16.tar.gz .

14.1.1 Compiling MySQL

To compile MySQL from source:

1. Extract the tarball:

    $  cd ~/src  $  tar xvfz ~/Desktop/mysql-4.0.16.tar.gz  

2. Change to the top-level directory that tar created and run the configure script. We suggest specifying a prefix of /usr/local/mysql so it stays out the way of any other binaries you have in /usr/local .

    $  cd mysql-4.0.16  $  ./configure --prefix=/usr/local/mysql  

3. Next, type make to compile MySQL. Go get a few cups of coffee (compiling could take 30 minutes or more).

14.1.2 Installing MySQL

If the compilation succeeded, you're ready to install MySQL. If not, you should first search the MySQL mailing list archives (http://lists.mysql.com) to see if anyone has reported the same problem you experienced , and whether a fix is available ( otherwise , you should submit a bug report). If you're having a lot of trouble here, you may want to install one of the binary packages. If everything went OK, you can now install MySQL:

1. Run make install as root:

    $  sudo make install  

2. Install the default databases:

    $  sudo ./scripts/mysql_install_db  

3. Set permissions on the MySQL directories:

    $  sudo chown -R root  /usr/local/mysql  $  sudo chown -R mysql /usr/local/mysql/var  $  sudo chgrp -R mysql /usr/local/mysql  

4. Install a configuration file ( my-small.cnf , my-medium.cnf , my-large.cnf , or my-huge.cnf ):

    $  sudo cp support-files/my-medium.cnf /etc/my.cnf  

5. Now you're ready to install a startup script for MySQL. See Section 2.2.2 in Chapter 2 for a sample MySQL startup script. (For now, leave out the --password = password from the startup script. You can add it back in, with the appropriate password, after you set the MySQL root password.) After you've created the startup script, start MySQL:

    $  sudo SystemStarter start MySQL  

14.1.3 Configuring MySQL

Next, you need to configure MySQL. At a minimum, set the root user's password and create a user and a working database for that user . Before using MySQL, add the following line to your .bash_profile and start a new Terminal window to pick up the settings:

 export PATH=$PATH:/usr/local/mysql/bin 

To set the root password and create a new user:

1. Use mysqladmin to set a password for the root user (qualified as root@localhost and just plain old root ). When you enter the second line, there will be a root password in place, so you need to use - p , and you'll be prompted for the password you created on the first line:

    $  mysqladmin -u root password '   password   '  $  mysqladmin -u root -p -h localhost password '   password   '  Enter password:  ********  

2. Create a database for your user (you'll be prompted for the mysql root user's password):

    $  mysqladmin  -u root -p create   dbname    Enter password:  ********  

3. Log into the mysql shell as root, and grant full control over that database to your user, qualified as user@localhost and just the username alone (the -> prompt indicates that you pressed return without completing the command, and the mysql shell is waiting for more input):

    $  mysql -u root -p  Enter password:  ********  Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 12 to server version: 4.0.16-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>  GRANT ALL PRIVILEGES ON   dbname   .* TO   username   @localhost  ->  IDENTIFIED BY '   password   ';  Query OK, 0 rows affected (0.08 sec) mysql>  GRANT ALL PRIVILEGES ON   dbname   .* TO   username  ->  IDENTIFIED BY '   password   ';  Query OK, 0 rows affected (0.00 sec) mysql>  quit  Bye 

14.1.4 Playing with MySQL

You should be able to log in to MySQL as the user defined in the previous section, and do whatever you want within your database:

 $  mysql -u   username   -p   dbname  Enter password:  ********  Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 16 to server version: 4.0.16-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>  CREATE TABLE foo (bar CHAR(10));  Query OK, 0 rows affected (0.06 sec) mysql>  INSERT INTO foo VALUES('Hello');  Query OK, 1 row affected (0.00 sec) mysql>  INSERT INTO foo VALUES('World');  Query OK, 1 row affected (0.01 sec) mysql>  SELECT * FROM foo;  +-------+  bar    +-------+  Hello   World  +-------+ 2 rows in set (0.00 sec) mysql>  quit  Bye