Database Server-side Installation¶

Refer to Setup MySQL databases
Note: the above link includes unnessary steps....
Start Here: http://emg.nysbc.org/documentation/leginon/bk02ch04s07.php

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
MySQL

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.

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)

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)

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
  >

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

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

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

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

>mysqladmin create projectdata

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)

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

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;

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;

13. Make sure MySQL is running¶

prompt:~> mysqlshow
      ------------
| Databases    |
      ------------
| mysql        |
| dbemdata     |
| projectdata  |
      ------------

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

Configure phpMyAdmin¶

Edit the phpMyAdmin config file:

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

and change the following lines:

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

Edit the phpMyAdmin apache config file:

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

and change the following lines:

<Directory /usr/share/phpMyAdmin/>
   order deny,allow
   deny from all
   allow from 127.0.0.1
   allow from YOUR_IP_ADDRESS
</Directory>