Appion

h1. Database Server-side Installation

Refer to [[Start_with_existing_ANY_Linux_flavor_OS#2-Setup-MySQL-databases|Setup MySQL databases]]

Note: the above link includes unnessary steps....

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.

Table 4.3. Leginon Database Supporting Packages

Name:	Download site:

MySQL-Server 5.0 or higher	http://www.mysql.com

MySQL-Client 5.0 or higher	http://www.mysql.com

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 leginon database, here we call dbemdata

       >mysqladmin create dbemdata

h3. 8. Create project database, here we call projectdata (optional)

       >mysqladmin create projectdata

h3. 9. Connect to mysql db

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

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 dbemdata.* to usr_object@'localhost';

      mysql> grant all privileges on projectdata.* to usr_object@'localhost';

Similarly,

      mysql> create user usr_object@'%';

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

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

h3. 11. Change Root passworld

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

      Query OK, 2 rows affected (0.01 sec)

      Rows matched: 2  Changed: 2  Warnings: 0

      mysql>^D or exit;

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

***If your password change does not take effect in your next database connection, you will need to flush the mysql pivileges cache as mysql root and connecting with you old password (or no password in the above case):

      >mysql -u root mysql

      mysql> flush privileges;

      mysql>^D or exit;

h3. 12. Check MySQL variables

      >mysql -u usr_object dbemdata

      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;

h3. 13. Make sure MySQL is running

      prompt:~> mysqlshow

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

      | Databases    |

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

      | mysql        |

      | dbemdata     |

      | projectdata  |

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

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

      <?

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

      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 nano /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 nano /etc/httpd/conf.d/phpMyAdmin.conf

</pre>

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>