Appion

h1. Database Server Installation

{{toc}}

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 steps 1 and 2.

h3. 1. Install MySQL-Server

* Use your package installer (yum, zypper, YaST) if available.

            OR

* Download the latest MySQL-server RPM for Linux from www.mysql.com

* Install the MySQL-server rpm:

    rpm -Uvh MySQL-server-5.0.xx-y.i386.rpm

(substitute correct version numbers)

h3. 2. Install MySQL-Client

* Use your package installer (yum, zypper, YaST) if available.

            OR

* Download the latest MySQL-client RPM for Linux from www.mysql.com

* Install the MySQL-client rpm:

    rpm -Uvh MySQL-client-5.0.xx-y.i386.rpm

(substitute correct version numbers)

h3. 3. MySQL configuration file is usually located in /usr/share/mysql. There are several examples there:

  > 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

  > 

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

         1. > cp /usr/share/mysql/my-huge.cnf /etc/my.cnf

         2. Edit /etc/my.cnf to add or change query cache variables like these:

            query_cache_type= 1

            query_cache_size = 100M

            query_cache_limit= 100M

h3. 5. start MySQL Server

       >/etc/init.d/mysqld start

on some installation,

       >/etc/init.d/mysql start

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

       >/etc/init.d/mysqld start

       >/etc/init.d/mysqld stop

       >/etc/init.d/mysqld restart

If you want to start MySQL automatically at boot on SuSE

       SuSE >chkconfig mysql on

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

      > cd /var/lib/mysql

      Directory: /var/lib/mysql

      > ls

      yourdbserver.pid

      ib_logfile0

      mysql

      mysql.sock

      test

      > 

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

       >mysqladmin create leginondb

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

       >mysqladmin create projectdb

h3. 9. Connect to mysql db

<pre>

      >mysql mysql

      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 may also set specific privilege to the user. See MySQL Reference Manual for details

      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';

Similarly,

      mysql> create user usr_object@'%';

      mysql> grant all privileges on leginondb.* to usr_object@'%';

      mysql> grant all privileges on projectdb.* to usr_object@'%';

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

      // if your web host is local

      mysql> grant all privileges on `ap%`.* to usr_object@localhost; 

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

      mysql> grant all privileges on `ap%`.* to usr_object@`%`;       

h3. 11. Change Root password

<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:

      >mysql -u root -p mysql

h3. 12. Check MySQL variables

<pre>

      >mysql -u usr_object 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. 13. Make sure MySQL is running

<pre>

      prompt:~> mysqlshow

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

      | Databases    |

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

      | mysql        |

      | leginondb    |

      | projectdb    |

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

</pre>

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

      <?

      mysql_connect('your_host.your_institute.edu', 'usr_object', '','leginondb');

      echo mysql_stat();

      ?> 

Output

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

h2. Configure phpMyAdmin 

Edit the phpMyAdmin config file:

<pre>

$ sudo vi /etc/phpMyAdmin/config.inc.php

</pre> 

and change the following lines:

<pre>

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

</pre>

Edit the phpMyAdmin apache config file:

<pre>

$ sudo vi /etc/httpd/conf.d/phpMyAdmin.conf

</pre>

and change the following lines:

*Note:* If you want to access phpMyAdmin from another computer, you can add it to its web access configuration file found as /etc/httpd/conf.d/phpMyAdmin.conf in a typical installation

<pre>

<Directory /usr/share/phpMyAdmin/>

   order deny,allow

   deny from all

   allow from 127.0.0.1

   allow from YOUR_IP_ADDRESS

</Directory>

</pre>

To test the PHPMyAdmin configuration, point your browser to http://YOUR_IP_ADDRESS/phpmyadmin. 

______