Appion

h1. Database Server Installation

h2. Install MySQL 

The following is for the computer that hosts the databases. This involves installing MySQL server and creation/configuration of the leginondb and projectdb databases.

*Note:* You may already have MySQL Server and Client installed. Check by typing mysql at the command line. 

If you see a MySQL prompt (mysql>), skip to step 2

h3. Install MySQL-Server and MySQL-Client

To install Mysql on Linux you have two options (the first option is better):

# Use your package installer (yum, zypper, YaST, apt-get). For example:

<pre>sudo yum install mysql mysql-server</pre>

# Download the latest MySQL-server package for Linux from http://www.mysql.com

h3. Example MySQL configuration files are usually located in /usr/share/mysql.

<pre>

ls /usr/share/mysql/my*

    /usr/share/mysql/my-huge.cnf

    /usr/share/mysql/my-innodb-heavy-4G.cnf

    /usr/share/mysql/my-large.cnf

    /usr/share/mysql/my-medium.cnf

    /usr/share/mysql/my-small.cnf

</pre>

If that does not work try the locate function

<pre>

locate my | egrep "\.cnf$"

    /etc/my.cnf

    /usr/share/mysql/my-huge.cnf

    /usr/share/mysql/my-innodb-heavy-4G.cnf

    /usr/share/mysql/my-large.cnf

    /usr/share/mysql/my-medium.cnf

    /usr/share/mysql/my-small.cnf

</pre>

h3. Configure my.cnf in /etc using my-huge.cnf as the template

# Copy my-huge.cnf to my.cnf

<pre>sudo cp -v /usr/share/mysql/my-huge.cnf /etc/my.cnf</pre>

# Edit /etc/my.cnf to add or change query cache variables like these (be sure to place them under the @[mysqld]@ section):

<pre>

query_cache_type= 1

query_cache_size = 100M

query_cache_limit= 100M

</pre>

# Search for the text default-storage-engine in /etc/my.cnf.  If it exists and is set to other than MyISAM, you should change it to:

<pre>default-storage-engine=MyISAM</pre>

h3. Start the MySQL Server

For CentOS/Fedora/RHEL system use the service command:

<pre>

sudo /sbin/service mysqld start

</pre>

For other Unix systems:

<pre>

sudo /etc/init.d/mysqld start

</pre>

or on some installations,

<pre>

sudo /etc/init.d/mysql start

</pre>

For future reference: start | stop | restart MySQL Server with similar commands:

<pre>

sudo /etc/init.d/mysqld start

sudo /etc/init.d/mysqld stop

sudo /etc/init.d/mysqld restart

sudo /sbin/service mysqld start

sudo /sbin/service mysqld stop

sudo /sbin/service mysqld restart

</pre>

If you want to start MySQL automatically at boot

<pre>

sudo /sbin/chkconfig mysql on

</pre>

or for SuSe:

<pre>

sudo /sbin/chkconfig mysql on

</pre>

h3. 6. For future reference, the database location will be:

<pre>

ls /var/lib/mysql

    ibdata1  ib_logfile0  ib_logfile1  mysql  mysql.sock  test

</pre>

h3. 7. Create the Leginon database, call it leginondb

<pre>

sudo mysqladmin create leginondb

</pre>

h3. 8. Create the Project database, call it projectdb

<pre>

sudo mysqladmin create projectdb

</pre>

h3. 9. Connect to mysql db

If starting from scratch the mysql root user will have no password. This is assumed to be the case and we will set it later.

<pre>

mysql -u root mysql

</pre>

<pre>

mysql> select user, password, host from user;

      +------+----------+-----------+

      | user | password | host      |

      +------+----------+-----------+

      | root |          | localhost |

      | root |          | host1     |

      |      |          | host1     |

      |      |          | localhost |

      +------+----------+-----------+

      4 rows in set (0.00 sec)

</pre>

h3. 10. Create user

Create and grant privileges to a user called usr_object for the databases on both the localhost and other hosts involved. For example, use wild card '%' for all hosts. You can set specific (@ALTER, CREATE, DROP, DELETE, INSERT, RENAME, SELECT, UPDATE@) privileges or @ALL@ privileges to the user. See MySQL Reference Manual for details.

<pre>

mysql> CREATE USER usr_object@'localhost' IDENTIFIED BY 'PASSWORD';

mysql> GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON leginondb.* TO usr_object@'localhost';

mysql> GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON projectdb.* TO usr_object@'localhost';

</pre>

less secure version (no password and all privileges), we recommend not allowing the DROP and DELETE privileges.

<pre>

mysql> CREATE USER usr_object@'localhost';

mysql> GRANT ALL PRIVILEGES ON leginondb.* TO usr_object@'localhost';

mysql> GRANT ALL PRIVILEGES ON projectdb.* TO usr_object@'localhost';

</pre>

Similarly, you can assign a domain

<pre>

mysql> CREATE USER usr_object@'%' IDENTIFIED BY 'PASSWORD';

mysql> GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON leginondb.* to usr_object@<host.mydomain.edu>;

mysql> GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON projectdb.* to usr_object@<host.mydomain.edu>;

</pre>

Next, give create and access privileges for the processing databases which begin with "ap".

<pre>

# if your web host is local

mysql> GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON `ap%`.* to usr_object@localhost; 

# for all other hosts if you are accessing the databases from another computer

mysql> GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON `ap%`.* to usr_object@<host.mydomain.edu>;       

</pre>

h3. 11. Change Root password

To set the root password use the command: 

<pre>

sudo mysqladmin -u root password NEWPASSWORD

</pre>

Or you can do it from within mysql

<pre>

mysql> update user set password=password('your_own_root_password') where user="root";

Query OK, 2 rows affected (0.01 sec)

Rows matched: 2  Changed: 2  Warnings: 0

mysql> flush privileges;

mysql>^D or exit;

</pre>

From now on, you will need to specify the password to connect to the database as root user like this:

<pre>

>mysql -u root -p mysql

</pre>

h3. 12. Check MySQL variables

<pre>

>mysql -u usr_object -p leginondb

mysql> SHOW VARIABLES LIKE 'query%';

      +------------------------------+-----------+

      | Variable_name                | Value     |

      +------------------------------+-----------+

      | ft_query_expansion_limit     | 20        |

      | have_query_cache             | YES       |

      | long_query_time              | 10        |

      | query_alloc_block_size       | 8192      |

      | query_cache_limit            | 104857600 | <<---This should correspond to your change

      | query_cache_min_res_unit     | 4096      |

      | query_cache_size             | 104857600 | <<---This should correspond to your change

      | query_cache_type             | ON        | <<---This should correspond to your change

      | query_cache_wlock_invalidate | OFF       |

      | query_prealloc_size          | 8192      |

      +------------------------------+-----------+

      10 rows in set (0.00 sec)

mysql> exit;

</pre>

h3. Make sure MySQL is running

<pre>

> mysqlshow

      +--------------+

      | Databases    |

      +--------------+

      | mysql        |

      | leginondb    |

      | projectdb    |

      +--------------+

</pre>

h3. Or check with the following php script (if already installed)

<pre>

<?

  mysql_connect('HOST.INSTITUTE.EDU', 'usr_object', 'PASSWORD','leginondb');

  echo mysql_stat();

?> 

</pre>

From the command line:

<pre>

php -r "mysql_connect('localhost', 'usr_object', 'PASSWORD', 'leginondb'); echo mysql_stat();"; echo ""

</pre>

Expected output:

<pre>

Uptime: 1452562 Threads: 1 Questions: 618 Slow queries: 0 Opens: 117 Flush tables: 1 Open tables: 106 Queries per second avg: 0.000

</pre>

If see any error messages, the above part may be configured correctly.

h2. Configure phpMyAdmin 

Edit the phpMyAdmin config file @/etc/phpMyAdmin/config.inc.php@ and change the following lines:

<pre>

$cfg['Servers'][$i]['AllowRoot']     = FALSE;

</pre>

Edit the phpMyAdmin apache config file @/etc/httpd/conf.d/phpMyAdmin.conf@ and change the following lines:

<pre>

<Directory /usr/share/phpMyAdmin/>

   order deny,allow

   deny from all

   allow from 127.0.0.1

   allow from YOUR_IP_ADDRESS

</Directory>

</pre>

*Note:* If you want to access phpMyAdmin from another computer, you can also add it to this config file with an @allow from@ tag

Next restart the web server to take on the new setting

<pre>

sudo /sbin/service httpd restart

</pre>

To test the phpMyAdmin configuration, point your browser to http://YOUR_IP_ADDRESS/phpMyAdmin or http://localhost/phpMyAdmin and login with the usr_object user.

A common problem is that the firewall may be blocking access to the web server and mysql server. On CentOS/Fedora you can configure this with the system config:

<pre>

system-config-securitylevel

</pre>

Firewall configuration is specific to different Unix distributions, so consult a guide on how to do this on non-RedHat machines.