Installing MySQL on a Mac OSX The Mac installer sets the ownership of all the MySQL files (at /usr/local/mysql) to _mysql and sqlgroup. You can see these from the Mac OSX by doing a "Get Info" command on the installed folder after it has been installed. You can also see the file ownerships from the Terminal by doing a "ls -l" command on /usr/local/mysql. These users, _mysql and sqlgroup, are part of every Mac OSX system; they don't need to be created.

/** /** /** /** /**

Obtain a MySQL distribution file from http://www.mysql.com/downloads/ Here's the latest 64 bit Intel version (as of 2/4/11) mysql-5.5.8-osx10.6-x86_64.dmg (MySql account is [email protected], taiwoumeac) You have a choice of Mac operating systems, and 32/64 bit CPU. All newer macs are 64. Use the mac .dmg version instead of .tar as it performs some extra setup steps for you.

/** Below are the instructions that come with the .dmg version ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 2.4.10 Installing MySQL on Mac OS X -----------------------------------

You can install MySQL on Mac OS X 10.3.x (`Panther') or newer using a Mac OS X binary package in PKG format instead of the binary tarball distribution. Please note that older versions of Mac OS X (for example, 10.1.x or 10.2.x) are *not* supported by this package. The package is located inside a disk image (`.dmg') file that you first need to mount by double-clicking its icon in the Finder. It should then mount the image and display its contents. To obtain MySQL, see *Note getting-mysql::. *Note*: Before proceeding with the installation, be sure to shut down all running MySQL server instances by either using the MySQL Manager Application (on Mac OS X Server) or via `mysqladmin shutdown' on the command line. To actually install the MySQL PKG file, double-click on the package icon. This launches the Mac OS X Package Installer, which guides you through the installation of MySQL. Due to a bug in the Mac OS X package installer, you may see this error message in the destination disk selection dialog: You cannot install this software on this disk. (null) If this error occurs, simply click the `Go Back' to the previous screen. Then click `Continue' destination disk selection again, and you should destination disk correctly. We have reported this investigating this problem.

button once to return to advance to the be able to choose the bug to Apple and it is

The Mac OS X PKG of MySQL installs itself into `/usr/local/mysql-VERSION' and also installs a symbolic link, `/usr/local/mysql', that points to the new location. If a directory named `/usr/local/mysql' exists, it is renamed to `/usr/local/mysql.bak' first. Additionally, the installer creates the grant tables in the `mysql' database by executing `mysql_install_db'. The installation layout is similar to that of a `tar' file binary distribution; all MySQL binaries are located in the directory `/usr/local/mysql/bin'. The MySQL socket file is created as `/tmp/mysql.sock' by default. See *Note installation-layouts::. MySQL installation requires a Mac OS X user account named `mysql'. A user account with this name should exist by default on Mac OS X 10.2 and up. If you are running Mac OS X Server, a version of MySQL should already be installed. The following table shows the versions of MySQL that ship with Mac OS X Server versions. *Mac OS X Server *MySQL Version* Version* 10.2-10.2.2 3.23.51 10.2.3-10.2.6 3.23.53 10.3 4.0.14 10.3.2 4.0.16 10.4.0 4.1.10a This manual section covers the installation of the official MySQL Mac OS X PKG only. Make sure to read Apple's help information about

installing MySQL: Run the `Help View' application, select `Mac OS X Server' help, do a search for `MySQL,' and read the item entitled `Installing MySQL.' For preinstalled versions of MySQL on Mac OS X Server, note especially that you should start `mysqld' with `safe_mysqld' instead of `mysqld_safe' if MySQL is older than version 4.0. If you previously used Marc Liyanage's MySQL packages for Mac OS X from `http://www.entropy.ch', you can simply follow the update instructions for packages using the binary installation layout as given on his pages. If you are upgrading from Marc's 3.23.x versions or from the Mac OS X Server version of MySQL to the official MySQL PKG, you also need to convert the existing MySQL privilege tables to the current format, because some new security privileges have been added. See *Note mysql-upgrade::.

If you want MySQL to start automatically during system startup, you also need to install the MySQL Startup Item. It is part of the Mac OS X installation disk images as a separate installation package. Simply double-click the `MySQLStartupItem.pkg' icon and follow the instructions to install it. The Startup Item need be installed only once. There is no need to install it each time you upgrade the MySQL package later. The Startup Item for MySQL is installed into `/Library/StartupItems/MySQLCOM'. (Before MySQL 4.1.2, the location was `/Library/StartupItems/MySQL', but that collided with the MySQL Startup Item installed by Mac OS X Server.) Startup Item installation adds a variable `MYSQLCOM=-YES-' to the system configuration file `/etc/hostconfig'. If you want to disable the automatic startup of MySQL, simply change this variable to `MYSQLCOM=-NO-'. On Mac OS X Server, the default MySQL installation bundled with the operating system uses the variable `MYSQL' in the `/etc/hostconfig' file. The Sun Microsystems, Inc. "Startup Item" installer disables this variable by setting it to `MYSQL=-NO-'. This avoids boot time conflicts with the `MYSQLCOM' variable used by the Sun Microsystems, Inc. "Startup Item". However, it does not shut down a running MySQL server. You should do that yourself.

After the installation, you can start up MySQL by running the following commands in a terminal window. You must have administrator privileges to perform this task. If you have installed the Startup Item, use this command: shell> sudo /Library/StartupItems/MySQLCOM/MySQLCOM start (ENTER YOUR PASSWORD, IF NECESSARY) (PRESS CONTROL-D OR ENTER "EXIT" TO EXIT THE SHELL) If you don't use the Startup Item, enter the following command sequence: shell> cd /usr/local/mysql shell> sudo ./bin/mysqld_safe (ENTER YOUR PASSWORD, IF NECESSARY) (PRESS CONTROL-Z) shell> bg (PRESS CONTROL-D OR ENTER "EXIT" TO EXIT THE SHELL) You should be able to connect to the MySQL server, for example, by running `/usr/local/mysql/bin/mysql'. *Note*: The accounts that are listed in the MySQL grant tables initially have no passwords. After starting the server, you should set up passwords for them using the instructions in *Note post-installation::. You might want to add aliases to your shell's resource file to make it easier to access commonly used programs such as `mysql' and `mysqladmin' from the command line. The syntax for `bash' is: alias mysql=/usr/local/mysql/bin/mysql alias mysqladmin=/usr/local/mysql/bin/mysqladmin For `tcsh', use: alias mysql /usr/local/mysql/bin/mysql alias mysqladmin /usr/local/mysql/bin/mysqladmin Even better, add `/usr/local/mysql/bin' to your `PATH' environment variable. You can do this by modifying the appropriate startup file for your shell. For more information, see *Note invoking-programs::. If you are upgrading an existing installation, note that installing a

new MySQL PKG does not remove the directory of an older installation. Unfortunately, the Mac OS X Installer does not yet offer the functionality required to properly upgrade previously installed packages. To use your existing databases with the new installation, you'll need to copy the contents of the old data directory to the new data directory. Make sure that neither the old server nor the new one is running when you do this. After you have copied over the MySQL database files from the previous installation and have successfully started the new server, you should consider removing the old installation files to save disk space. Additionally, you should also remove older versions of the Package Receipt directories located in `/Library/Receipts/mysql-VERSION.pkg'.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ /** Much of the following is now done automatically by the Mac mysql install package: /** /** /** /** /** /** /**

Instead of using the Terminal, you can manually move it to the proper place. open a window for /usr/local by entering it from the Finder Menu's /Go/GoToFolder dialog box. (If not there, create a folder "local" on the desktop and move it into /usr/) Manually move the distribution folder into /usr/local, you'll be asked to Authenticate with an account password. Keep the long folder name "mysql-5.1.48-osx10.6-x86_64" but created a symbolic link to it with the simpler mysql

ip-163-191:~ johntalbert$ ln -s /usr/local/mysql-5.5.8-osx10.5-x86 ip-163-191:~ johntalbert$ cd /usr/local/mysql/ ip-163-191:mysql johntalbert$ ls -al total 296 drwxr-xr-x@ drwxr-xr-x -rw-r--r--@ -rw-r--r--@ -rw-r--r--@ drwxr-xr-x@ drwxr-xr-x@ drwxr-xr-x@ drwxr-xr-x@ drwxr-xr-x@ drwxr-xr-x@ drwxr-xr-x@ drwxr-xr-x@ drwxr-xr-x@ drwxr-xr-x@ drwxr-xr-x@

16 4 1 1 1 44 4 4 47 12 4 19 3 32 28 16

johntalbert johntalbert johntalbert johntalbert johntalbert johntalbert johntalbert johntalbert johntalbert johntalbert johntalbert johntalbert johntalbert johntalbert johntalbert johntalbert

staff staff staff staff staff staff staff staff staff staff staff staff staff staff staff staff

544 136 17987 12388 113534 1496 136 136 1598 408 136 646 102 1088 952 544

Feb Feb Dec Dec Dec Feb Feb Feb Feb Feb Feb Feb Feb Feb Feb Feb

1 2 3 3 3 1 1 1 1 1 1 1 1 1 1 1

11:32 11:13 11:58 11:58 11:58 11:32 11:32 11:32 11:32 11:32 11:32 11:32 11:32 11:32 11:32 11:32

mysql

. .. COPYING INSTALL-BINARY README bin data docs include lib man mysql-test scripts share sql-bench support-files

The mysql_install_db program sets up the initial MySQL grant tables containing the privileges that determine how users are permitted to connect to the server. It seems that the installation program did run this script and set up a mysql database and grant tables under the user "mysql", a user that the Mac OSX operating system automatically includes. It may also set up a test database. All this is found at /usr/local/mysql/data (mysql is the alias you created above). These folders are owned by the user "mysql". This can be changed later to the user created above. The command that set all this up is as follows (note that the default user is specified as mysql). Don't run this, it has already been done by the installer. shell> cd /usr/local/mysql shell> sudo scripts/mysql_install_db --user=mysql

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ STARTUP PROBLEMS FOR MySQL: The Mac .dmg package for MySQL (mysql-5.5.8-osx10.6-x86_64.dmg) has installs for two startup items, a Preference Pane and a Startup script: /Library/Preference Panes/MySQL.prefPane /Library/StartupItems/MySQLCOM The startup script must be given the right privileges.

Do this from the TERMINAL:

sudo chown -R root:wheel /Library/StartupItems/MySQLCOM And the Preference Pane must be told where mysql lives: Open /usr/local/mysql/support-files/mysql.server in BBedit (show invisible files), unlock the file in BBedit, and perform and save the following edits: basedir=/usr/local/mysql datadir=/usr/local/mysql/data

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Verify the Server Use mysqladmin to verify that the server is running. If you are denied access use root (sudo at the start and a -p at the end). The following commands provide simple tests to check whether the server is up and responding to connections: shell> bin/mysqladmin version shell> bin/mysqladmin variables

To see what else you can do with mysqladmin, invoke it with the --help option. Verify that you can shut down the server: shell> bin/mysqladmin -u root shutdown

Verify that you can start the server again.Do this by using mysqld_safe or by invoking mysqld directly. For example: shell> bin/mysqld_safe --user=mysql &

Run some simple tests to verify that you can retrieve information from the server. shell> bin/mysqlshow shell> bin/mysqlshow mysql shell> bin/mysql -e "SELECT Host,Db,User FROM db" mysql

MySQL Monitor After you have installed MySQL, set it up to run automatically on startup, and configured it, you should be able to connect to it. The first time you start up mySQL Monitor it is as root. Here you will change some of the security settings and from then on start up mySQL as another user. You can do this by typing the following: shell> shell>

cd /usr/local/mysql bin/mysql -u root ip-171-121:mysql timara$ bin/mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1335 Server version: 5.5.8 MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

Now, you should assign passwords to the accounts created by mysql_install_db, restrict access to test databases, and deal with anonymous user accounts.

Securing your Database As you probably noticed when you logged in just now, you had to supply a username—root—but not a password. The initial installation of MySQL has no root password set. It's really important to set this password for obvious security reasons. In the MySQL monitor, type the following: set password for root@localhost=password('your password'); Obviously, you should replace your password with whatever you would like the root password to be. Log out (\q) and then log back in. This time, you need to log in like this: bin/mysql -u root -p MySQL then prompts you for your password. The -u means username, and the -p means log in with password.

To display which accounts exist in the mysql.user table and check whether their passwords are empty, use the following statement: mysql> SELECT User, Host, Password FROM mysql.user; +------+-----------------------------+-------------------------------------------+ | User | Host | Password | +------+-----------------------------+-------------------------------------------+ | root | localhost | *F94C4CF8E1C7C55A3F385F8B671040B473D0CE74 | | root | ip-171-121.main.oberlin.edu | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | ip-171-121.main.oberlin.edu | | +------+-----------------------------+-------------------------------------------+ 6 rows in set (0.00 sec)

On Unix, each root account permits connections from the local host. Connections can be made by specifying the host name localhost, the IP address 127.0.0.1, the IPv6 address ::1, or the actual host name or IP address. Some accounts are for anonymous users. These have an empty user name. The anonymous accounts have no password, so anyone can use them to connect to the MySQL server. On Unix, each anonymous account permits connections from the local host. Connections can be made by specifying a host name of localhost for one of the accounts, or the actual host name or IP address for the other. This output indicates that there are several root and anonymous-user accounts, one of which shows the ecryted password you set above. The output might differ on your system, but the presence of accounts with empty passwords means that your MySQL installation is unprotected until you do something about it: You should assign a password to each MySQL root account. If you want to prevent clients from connecting as anonymous users without a password, you should either assign a password to each anonymous account or else remove the accounts. mysql> mysql> mysql> mysql>

SET PASSWORD FOR 'root'@'ip-171-121.main.oberlin.edu' = PASSWORD('newpwd'); SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpwd'); SET PASSWORD FOR 'root'@'::1' = PASSWORD('newpwd'); FLUSH PRIVILEGES;

Another way of doing this: mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd') WHERE User = 'root'; mysql> FLUSH PRIVILEGES;

Or, using mysqladmin instead of the MySQL Monitor; however, the mysqladmin method of setting the root account passwords does not work for the 'root'@'127.0.0.1' or 'root'@'::1' account. Use the SET PASSWORD method shown earlier. shell> mysqladmin -u root password "newpwd" shell> mysqladmin -u root -h host_name password "newpwd"

To delete the anonymous account use: mysql> DROP USER ''@'localhost'; mysql> DROP USER ''@'host_name'; mysql> FLUSH PRIVILEGES;

By default, the mysql.db table contains rows that permit access by any user to the test database and other databases with names that start with test_. (These rows have an empty User column value, which for access- checking purposes matches any user name.) This means that such databases can be used even by accounts that otherwise possess no privileges. If you want to remove any-user access to test databases, do so as follows: mysql> DELETE FROM mysql.db WHERE Db LIKE 'test%'; mysql> FLUSH PRIVILEGES;

With the preceding change, only users who have global database privileges or privileges granted explicitly for the test database can use it. However, if you do not want the database to exist at all, drop it: mysql> DROP DATABASE test;

Now to recheck your users and databases: mysql> SELECT User, Host, Password FROM mysql.user; +------+-----------------------------+-------------------------------------------+ | User | Host | Password | +------+-----------------------------+-------------------------------------------+ | root | localhost | *F94C4CF8E1C7C55A3F385F8B671040B473D0CE74 | | root | ip-171-121.main.oberlin.edu | *F94C4CF8E1C7C55A3F385F8B671040B473D0CE74 | | root | 127.0.0.1 | *F94C4CF8E1C7C55A3F385F8B671040B473D0CE74 | | root | ::1 | *F94C4CF8E1C7C55A3F385F8B671040B473D0CE74 | +------+-----------------------------+-------------------------------------------+ 4 rows in set (0.00 sec)

Creating an Account for Basic Use It is usually a good idea to use some account other than root for basic day-to-day work for security reasons. Create an account for day-to-day use by typing the following: grant create, create temporary tables, delete, execute, index, insert, lock tables, select, show databases, update on *.* to username identified by 'password'; Obviously, you should substitute your own username and password into this command. You should now log out and log back in with the username and password you set. You will need to use a root account for some of the things we will do in the later chapters of this book, but this account we have just created has fewer privileges than root, so it is safer for day-to-day use. mysql> SELECT User, Host, Password FROM mysql.user; +-------+-----------------------------+-------------------------------------------+ | User | Host | Password | +-------+-----------------------------+-------------------------------------------+ | root | localhost | *F94C4CF8E1C7C55A3F385F8B671040B473D0CE74 | | root | ip-171-121.main.oberlin.edu | *F94C4CF8E1C7C55A3F385F8B671040B473D0CE74 | | root | 127.0.0.1 | *F94C4CF8E1C7C55A3F385F8B671040B473D0CE74 |

| root | ::1 | *F94C4CF8E1C7C55A3F385F8B671040B473D0CE74 | | sqljt | % | *770D13F96377F261D6E0E7BB4FCA16673B20C12F | +-------+-----------------------------+-------------------------------------------+ 5 rows in set (0.00 sec) mysql> quit Bye

Logging into MySQL Monitor:

bin/mysql -u username -p

ip-171-121:mysql timara$ bin/mysql -u sqljt -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6323 Server version: 5.5.8 MySQL Community Server (GPL) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.06 sec)

Permissions The Mac installer sets the ownership of all the MySQL files (at /usr/local/mysql) to _mysql and sqlgroup. You can see these from the Mac OSX by doing a "Get Info" command on the installed folder. You can also see the file ownerships from the Terminal by doing a "ls -l" command on /usr/local/mysql. These users, _mysql and sqlgroup, are part of every Mac OSX system; they don't need to be created. The MySQL server ownership system is separate from this Mac OSX security system. Internally, the MySQL Server stores user privilege information in the grant tables of the mysql database (that is, in the database named mysql at /usr/local/ mysql/data/mysql). The MySQL server reads the contents of these tables into memory when it starts and bases access-control decisions on the inmemory copies of the grant tables. The MySQL root user, created by the installer, has access to the mysql database. The root user is the 'all-powerful' administrator of the server with full privileges. This root is different from the MAC OSX root user you invoke from the Terminal App. The MySQL root (administrator) is authorized to create other users, each with specific privileges, database, host, and password. The syntax for mysql monitor command goes like this: GRANT privileges ON database.* TO 'username'@'hostname' IDENTIFIED BY 'password';

Changing permissions and ownership can be done from the Mac using the "Get Info" on any file or directory. Or it can be done from the terminal as shown below (use _mysql and sqlgroup instead of sqljt shown). ip-163-191:mysql johntalbert$ sudo chown -R sqljt . Password: ip-163-191:mysql johntalbert$ sudo chgrp -R sqlgroup . ip-163-191:mysql johntalbert$ ls -l total 296 -rw-r--r--@ -rw-r--r--@ -rw-r--r--@ drwxr-xr-x@ drwxr-xr-x@ drwxr-xr-x@ drwxr-xr-x@ drwxr-xr-x@ drwxr-xr-x@ drwxr-xr-x@ drwxr-xr-x@ drwxr-xr-x@ drwxr-xr-x@ drwxr-xr-x@

1 1 1 44 4 4 47 12 4 19 3 32 28 16

sqljt sqljt sqljt sqljt sqljt sqljt sqljt sqljt sqljt sqljt sqljt sqljt sqljt sqljt

sqlgroup sqlgroup sqlgroup sqlgroup sqlgroup sqlgroup sqlgroup sqlgroup sqlgroup sqlgroup sqlgroup sqlgroup sqlgroup sqlgroup

17987 12388 113534 1496 136 136 1598 408 136 646 102 1088 952 544

Dec Dec Dec Feb Feb Feb Feb Feb Feb Feb Feb Feb Feb Feb

3 3 3 1 1 1 1 1 1 1 1 1 1 1

11:58 11:58 11:58 11:32 11:32 11:32 11:32 11:32 11:32 11:32 11:32 11:32 11:32 11:32

COPYING INSTALL-BINARY README bin data docs include lib man mysql-test scripts share sql-bench support-files

If the installation did not run the script mysql_install_db you can do it now. This will create the data directory (/usr/local/mysql/data)and initialize its grant tables. If the installation did already run this, running it again will change the owner to whatever you want, "sqljt" shown below. Note all the helpful suggestions given after it runs, which we will follow. [ip-175-215:~/mysql] Admin% sudo scripts/mysql_install_db --user=sqljt Installing MySQL system tables... OK Filling help tables... OK

To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands:

./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h ip-171-121.main.oberlin.edu password 'new-password' Alternatively you can run: ./bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd ./mysql-test ; perl mysql-test-run.pl Please report any problems with the ./bin/mysqlbug script!

Manual Server Startup /** Make sure you are in mysql with 'cd /usr/local/mysql'. below. /** Use a Control-Z in the Terminal to get out of the job.

Start up mysqld as user root with the command

ip-175-215:mysql Admin$ sudo bin/mysqld_safe --user=root -p [2] 339 ip-175-215:mysql Admin$ 100714 15:45:25 mysqld_safe Logging to '/usr/local/mysql-5.1.48-osx10.6-x86_64/data/ ip-175-215.main.oberlin.edu.err'. 100714 15:45:25 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql-5.1.48-osx10.6-x86_64/ data /** mysql is now running.

You can see it from the Mac Utility ActivityMonitor's list of processes.

/** Starting up the MySQL Monitor. Will only accept commands as root at the start. /** After root is given a password below, /** add -p to the command the next time you use it.

mysql> help For information about MySQL products and services, visit: http://www.mysql.com/ For developer information, including the MySQL Reference Manual, visit: http://dev.mysql.com/ To buy MySQL Enterprise support, training, or other products, visit: https://shop.mysql.com/LO List of all MySQL commands: Note that all text commands must be first on line and end with ';' ? (\?) Synonym for `help'. clear (\c) Clear the current input statement. connect (\r) Reconnect to the server. Optional arguments are db and host. delimiter (\d) Set statement delimiter. edit (\e) Edit command with $EDITOR. ego (\G) Send command to mysql server, display result vertically. exit (\q) Exit mysql. Same as quit. go (\g) Send command to mysql server. help (\h) Display this help. nopager (\n) Disable pager, print to stdout. notee (\t) Don't write into outfile. pager (\P) Set PAGER [to_pager]. Print the query results via PAGER. print (\p) Print current command. prompt (\R) Change your mysql prompt. quit (\q) Quit mysql. rehash (\#) Rebuild completion hash. source (\.) Execute an SQL script file. Takes a file name as an argument. status (\s) Get status information from the server. system (\!) Execute a system shell command. tee (\T) Set outfile [to_outfile]. Append everything into given outfile. use (\u) Use another database. Takes database name as argument. charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. warnings (\W) Show warnings after every statement. nowarning (\w) Don't show warnings after every statement. For server side help, type 'help contents' mysql> status; -------------bin/mysql Ver 14.14 Distrib 5.1.48, for apple-darwin10.3.0 (i386) using readline 5.1 Connection id: Current database: Current user:

8 root@localhost

SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.1.48 MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /tmp/mysql.sock Uptime: 15 min 30 sec Threads: 1 Questions: 4 0.4 --------------

Slow queries: 0

Opens: 15

Flush tables: 1

Open tables: 8

mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 2 rows in set (0.00 sec) /** Examples of creating databases and users from the mysql monitor. mysql> CREATE DATABASE temp; Query OK, 1 row affected (0.00 sec) mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, -> ALTER, INDEX ON temp.* TO 'temp_user'@'localhost' -> IDENTIFIED BY 'temppassword33'; Query OK, 0 rows affected (0.02 sec) mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON temp.* -> TO 'restricted_user'@'localhost' -> IDENTIFIED BY 'restricteduserpassword34'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> SHOW DATABASES -> ; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | temp | +--------------------+

Revised User Creation: mysql> DROP USER 'sqljt'@'localhost'; Query OK, 0 rows affected (0.03 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> CREATE DATABASE timomeka; mysql> GRANT ALL ON timomeka.* TO 'sqljt'@'localhost' IDENTIFIED BY 'MMono235p'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT User, Host, Password FROM mysql.user; +-------+-----------------------------+-------------------------------------------+ | User | Host | Password | +-------+-----------------------------+-------------------------------------------+ | root | localhost | *F94C4CF8E1C7C55A3F385F8B671040B473D0CE74 | | root | ip-171-121.main.oberlin.edu | *F94C4CF8E1C7C55A3F385F8B671040B473D0CE74 | | root | 127.0.0.1 | *F94C4CF8E1C7C55A3F385F8B671040B473D0CE74 | | root | ::1 | *F94C4CF8E1C7C55A3F385F8B671040B473D0CE74 | | sqljt | localhost | *770D13F96377F261D6E0E7BB4FCA16673B20C12F | +-------+-----------------------------+-------------------------------------------+ 5 rows in set (0.00 sec)

Queries per second avg:

Creating a Database

Checking for the InnoDB Engine mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)

Configuration Files ??? The use of the my.cnf configuration file seems to be optional. It is not implemented by the installer. You can find samples of this file in /usr/local/mysql/support-files. There are several of them: my-huge.cnf, myinnodb-heavy-4G.cnf, my-large.cnf, my-medium.cnf, my-small.cnf. These sample files are filled with notes (lines starting with the # comment symbol). Read them to find out how to use them. If they are to be used you would make a copy of one of the samples, edit it with your info, rename the file as my.cnf, move it to the correct (/etc ??) location, Restart MySQL.

OMEKA ImageMagick Omeka only needs ImageMagick installed which it uses to create thumbnails for photos. The MacPorts is not needed. In fact, when I installed ImageMagick using MacPorts, it put the ImageMagick distribution files into /opt/local/ which Omeka, for some reason, did not like. The files should be installed in /usr/local instead. Dowload ImageMagick from http://www.macupdate.com/app/mac/12447/imagemagick. Double click on the .tar file to start the decompressor. You will end up with the folder "ImageMagick-6.7.1". Use the Mac Finder Menu GO / GO TO FOLDER… to open up the directory /usr/local. Then drag and drop the ImageMagick distribution files into the proper folders. For example the ImageMagick executables found in the bin folder should be dragged into /usr/ local/bin; the etc folder doesn't exist at /usr/local, so drop the whole etc folder from IMageMagick into /usr/local, and so on... Omeka doesn't require any other packages.

Apache/PHP Apache and PHP are installed on every Mac Operating system, but PHP is not enabled. Apache is found in /private/etc/apache2/ PHP must be enabled by editing the httpd.conf file in /private/etc/apache2/. Use BBedit to unlock and edit the file: Find the following line in httpd.conf.

#LoadModule php5_module libexec/apache2/libphp5.so Uncomment the line by deleting the pound sign (#) at the beginning of the line. To let Apache use Omeka's .htaccess file, find and change the 'None' to 'All' for the 'AllowOverrride line that applies to .htaccess:

AllowOverride All This will now allow PHP to be loaded when Apache restarts. Restart Apache from the Terminal with the command $ sudo

apachectl restart

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Testing PHP from Localhost Enable Web sharing on your Mac by going into SystemPreferences/Sharing and turn on "Web Sharing" Your mac will now serve websites found in /Library/WebServer/Documents/. In any web-browser use either your mac's IP address or, if you are on your own machine, "localhost" (http://localhost/) to access these websites. A standard test for your Mac's PHP is to display its phpinfo() screen from the localhost. To do this build a simple text file containing the line Save the file as test.php and place it in /Library/WebServer/Documents/. On your mac's web browser open up the address http://localhost/test and you will see the PHP Info displayed ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PHP Fixes Create /etc/php.ini and make it writable, using Terminal. $ cd /etc $ sudo cp php.ini.default php.ini $ sudo chmod 666 php.ini

Use BBedit to edit php.ini: find ;date.timezone = Uncomment it by erasing the semicolon. Enter your timezone from choices here: http://php.net/manual/en/timezones.php

Fix mysql.sock location (confusing - do whatever works) When running MySQL and PHP on the same Mac OS X 10.4 server, you may find that PHP cannot connect to MySQL. When PHP is communicating with a MySQL server on the same host (using "localhost" as the web address), it uses a socket file to communicate instead of the usual TCP/IP, and looks for it at /tmp/mysql.sock. MySQL uses either /tmp/mysql.sock or /var/mysql/mysql.sock for the socket. To find out where MySQL puts its socket file, start up mysql and run ' mysql> status; ' If MySQL creates this socket file at /var/mysql/mysql.sock. It can't do this if the directory /var/mysql doesn't exist. To resolve this issue, you can either change the location where MySQL creates its socket file, or modify the location where PHP looks for the file. Please note that the first option is less secure than the second so we will use the second option - the socket file will live at /var/mysql.

VERSION 1 Socket Setting ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

--in php.ini check the following 3 occurrences and edit them to equal /var/mysql/mysql.sock, and not /tmp/mysql.sock pdo_mysql.default_socket=/var/mysql/mysql.sock mysql.default_socket = /var/mysql/mysql.sock mysqli.default_socket = /var/mysql/mysql.sock --If the directory /var/mysql doesn't exist, use the Terminal App to create it and set permissions: sudo mkdir /var/mysql sudo chmod 775 /var/mysql --If there are still any attempts to find the socket at /tmp/sysql.sock create a link at /tmp that redirects it to /var/mysql/mysql.sock sudo ln -s /var/mysql/mysql.sock /tmp/mysql.sock ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Version 2 Socket Setting ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ******* On a Mac OSX Lion installation, performing the above changes caused the localhost login to fail ******** (Warning: mysql_connect() [function.mysql-connect]: [2002] No such file or directory (trying to connect via unix:///var/mysql/ mysql.sock) in /Library/WebServer/Documents/ca/app/lib/core/Db/mysql.php on line 110) Doing the following mysql.sock setup fixed the problem: --in php.ini check the following 3 occurrences and edit them to equal /tmp/mysql.sock pdo_mysql.default_socket = /tmp/mysql.sock mysql.default_socket = /tmp/mysql.sock mysqli.default_socket = /tmp/mysql.sock ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ****In any case, check for socket setup in the mysql log file found at /usr/local/mysql/data/your_ip_address.err***** Activate PHP short tags (if you like) Allows you to use , instead of in your php code. change short_open_tag = Off, to = On Set Larger File Sizes post_max_size = 5000M upload_max_filesize = 5000M memory_limit = 256M file_uploads = On max_execution_time = 600 max_input_time = 60 memory_limit = 256M mysql.connect_timeout = 60 To Display Errors while debugging installation display_errors = On, otherwise = Off

Installing OMEKA Omeka

Create complex narratives and share rich collections, adhering to Dublin Core standards with Omeka on your server, designed for scholars, museums, libraries, archives, and enthusiasts. Learn More

Now that you've got all the requirements in place it's time to set up Omeka. You will need to perform the following steps: 1.

2. 3. 4. 5.

6.

Set up an empty MySQL database for your installation. Give the database a name and create a login for it with full access. Note the login information - you'll need it later. You can use the MySQL command line or web-based tools like phpMyAdmin to create the database and login. Copy the contents of the Omeka software distribution to the root of the web server instance in which your installation will run. Edit db.ini, changing the various directory paths and database login parameters to reflect your server setup. Make sure the permissions on server root directories are such that the web server can write to them. In the next step, the web-based installer will need the access to create directories for uploaded media, and to generate cached files. In a web browser navigate to the web-based installer. If the URL for your installation server is http://www.myCollectiveaccessSite.org then the URL to the installer is http://www.myCollectiveaccessSite.org/install. Enter your email address and select the installation profile (a profile is a set of pre-configured values for your system) that best fits your needs. Then click on the "begin" button. The installer will give you login information for your newly installed system when installation is complete. Be sure to note this information in a safe place!

Creating a DataBase in the MySQL Monitor Start up the MySQL Monitor as root. Only root, the administrator, is allowed to create a user with full access to a database. shell> cd /usr/local/mysql shell> bin/mysql -u root -p mysql> create database xxxxxxx; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | xxxxxx | +--------------------+ 5 rows in set (0.00 sec)

MySQL Permissions The Mac installer sets the ownership of all the MySQL files (at /usr/local/mysql) to _mysql and sqlgroup. You can see these from the Mac OSX by doing a "Get Info" command on the installed folder. You can also see the file ownerships from the Terminal by doing a "ls -l" command on /usr/local/mysql. These users, _mysql and sqlgroup, are part of every Mac OSX system; they don't need to be created. The MySQL server ownership system is separate from this Mac OSX security system. Internally, the MySQL Server stores user privilege information in the grant tables of the mysql database (that is, in the database named mysql at /usr/local/mysql/data/mysql). The MySQL server reads the contents of these tables into memory when it starts and bases access-control decisions on the in-memory copies of the grant tables. The MySQL root user, created by the installer, has access to the mysql database. The root user is the 'all-powerful' administrator of the server with full privileges. This root is different from the MAC OSX root user you invoke from the Terminal App. The MySQL root (administrator) is authorized to create other users, each with specific privileges, database, host, and

password. The syntax for mysql monitor command goes like this: GRANT privileges ON database.* TO 'username'@'hostname' IDENTIFIED BY 'password'; Below is the command used to create a User with full privileges for the database created above.

mysql> GRANT ALL ON xxxxxxx.* TO 'xxxxx'@'localhost' IDENTIFIED BY 'xxxxxxx'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT User, Host, Password FROM mysql.user; +-------+-----------------------------+-------------------------------------------+ | User | Host | Password | +-------+-----------------------------+-------------------------------------------+ | root | localhost | *F94C4CF8E1C7C55A3F385F8B671040B473D0CE74 | | root | ip-171-121.main.oberlin.edu | *F94C4CF8E1C7C55A3F385F8B671040B473D0CE74 | | root | 127.0.0.1 | *F94C4CF8E1C7C55A3F385F8B671040B473D0CE74 | | root | ::1 | *F94C4CF8E1C7C55A3F385F8B671040B473D0CE74 | | sqljt | localhost | *770D13F96377F261D6E0E7BB4FCA16673B20C12F | +-------+-----------------------------+-------------------------------------------+

Omeka Files Your mac has the ability to serve websites found in /Library/WebServer/Documents/. If you are on your own machine, you can gain access to this website by entering either your mac's IP address or "localhost" (http://localhost/) on any web browser. To gain the same access over the internet from any computer you must first enable Web sharing on your Mac by going into SystemPreferences/Sharing and turn on "Web Sharing". This is where the Omeka distributions files are to be placed. Download the latest Omeka build from the website (Download Omeka – Omeka) Uncompress the zip file by simply double clicking on it. This results in a folder (omeka 1.4). Change the name of the folder to something appropriate (omeka) keeping in mind that this name will be part of the web address. Move the entire folder to /Library/WebServer/Documents/. Be sure to move the entire folder and not just the contents. The folder has an invisible .htaccess file which will not transfer if just the contents are moved instead of the folder. The .htaccess file must live inside the omeka folder so that its instructions are applied only to Omeka sites and not to others. Open the Omeka file db.ini in a text editor like BBedit and fill in the MySQL info: ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; ; Database Configuration ; ; Omeka requires MySQL 5 to work properly. To configure ; your database, replace the X's with your specific settings. ; If you're unsure about your database information, ask your ; server administrator, or consult the codex ; http://omeka.org/codex/configuring_omeka_database ; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; [database] host = "localhost" username = "xxxxx" password = "xxxxxxxxx" dbname = "xxxxxxxx" prefix = "omeka_" charset = "utf8" ;port = ""

xxxx is whatever you set up in MySQL above.

Moving the Media Directory Use a symbolic link to move the Omeka archive folder, which will get very large when storing movie files, to another disk drive. I built a directory called OmekaArchive on the drive TIM_Media1 to act as the Omeka "archive" directory, moved all the contents of /Library/WebServer/Documents/omeka/archive/ into it, erased the empty archive folder, and created an alias there called "archive" and pointing to /TIM_Media1/OmekaArchive. (Only worked from Terminal commands). This can be done from the Terminal:

shell> shell> shell> shell>

mv /Library/WebServer/Documents/omeka/archive/ /Volumes/TIM_Media1/OmekaArchive/ sudo ln -s /Volumes/TIM_Media1/OmekaArchive/archive/ /Library/WebServer/Documents/omeka/archive cd /Volumes/TIM_Media1/ sudo chmod 777 OmekaArchive

Omeka File Permissions Make sure the permissions on the server root directories are such that the web server can write to them. Also, the web-based installer will need the access to create directories for uploaded media, and to generate cached files. (After copying, the Mac OSX permissions are timer(Me)-Read&Write, everyone and staff - Read Only.) From the Mac OSX do a "Get Info" on /Library/WebServer/Documents/omeka and change the permissions to Read&Write for all. and for the archive in /Library/WebServer/Documents/omeka/. Or, from the Terminal App: shell> shell> shell> shell>

cd /Library/WebServer/Documents chmod 777 ca cd /Library/WebServer/Documents/omeka chmod 777 archive

Finding mysql.sock Omeka expects the mysql socket to live at /var/mysql/mysql.sock but on the Mac it is at /tmp/mysql.sock (or possibly at /private/ tmp/mysql.sock). To fix this, create the directory /var/mysql and create a link: shell> sudo mkdir /var/mysql shell> sudo ln -s /tmp/mysql.sock

/var/mysql/mysql.sock

Finding ImageMagick Make sure the ImageMagick was installed (by MacPorts) into /opt/local/include/ImageMagick/. This is actually the folder for the into /usr/local/bin /usr/local/bin into header files. The actual executable is /opt/local/bin/convert. The initial install will need you to fill in the location /opt/local/bin

Initial Install Turn on Web Sharing in the Mac Preferences 'Sharing'. Open a web browser and navigate to the web-based installer, http:// localhost/omeka/. If all goes well you hold get the 'install' page. I filled in the following information: Superuser Account Username: Administrator Password: xxxxxxxx Password retyped Email: xxxxxx Site Setting Administrator Email: [email protected] Site Title: TIMARA Omeka database Site Description: Database for TIMARA Media Files Site Copyright Information: -Site Author Information: -Tag Delimiter: , Fullsize Image Size: 800 Thumbnail Size: 200 Square Thumbnail Size: 200 Items Per Page admin : 10 Item Per Page public: 10 Show Empty Elements: not checked Imagemagick Directory Path: /usr/local/bin At this point you may want to remove the Omeka 'install' folder to prevent anyone from resetting the install parameters from the address http://localhost/omeka/install/

To get the Admin pages use the address http://localhost/omeka/admin/. You will be asked for your superuser name and password that you entered on the install page. To get the public page use the address http://localhost/omeka/

Display Error Messages If Omeka doesn't seem to be behaving correctly, for whatever reason, there are steps you can take to get more descriptive error messages. These are turned off by default, but can be used during the production of an Omeka site for troubleshooting. You can change some configuration settings in Omeka to display error messages directly on the screen. 1. 2. 3. 4.

Open the .htaccess file in the root of your Omeka installation, and change the value of 'php_value display_errors' to 1. Open 'application/config/config.ini', and change the value of 'debug.exceptions' to true Return to the page in Omeka that is causing the error. Do your best to repeat whatever originally caused the error, then send us the error message that is displayed on your screen. Be sure to disable these settings again once the problem is resolved.