Database Server Installation¶

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.

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. Copy my-huge.cnf to my.cnf
 
 > 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
      > 

7. Create the Leginon database, call it leginondb¶

       >mysqladmin create leginondb

8. Create the Project database, call it projectdb¶

       >mysqladmin create projectdb

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

11. Change Root password¶

      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;

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

12. Check MySQL variables¶

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

13. Make sure MySQL is running¶

      prompt:~> mysqlshow
      +--------------+
      | Databases    |
      +--------------+
      | mysql        |
      | leginondb    |
      | projectdb    |
      +--------------+

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

Configure phpMyAdmin¶

Edit the phpMyAdmin config file:

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

and change the following lines:

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

Edit the phpMyAdmin apache config file:

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

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

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

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