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 to step 2

Install MySQL-Server and MySQL-Client¶

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

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

   sudo yum install mysql mysql-server

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

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

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

If that does not work try the locate function

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

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

1. Copy my-huge.cnf to my.cnf
   

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

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

   query_cache_type= 1
   query_cache_size = 100M
   query_cache_limit= 100M
   

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

   default-storage-engine=MyISAM

Start the MySQL Server¶

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

sudo /sbin/service mysqld start

For other Unix systems:

sudo /etc/init.d/mysqld start

or on some installations,

sudo /etc/init.d/mysql start

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

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

If you want to start MySQL automatically at boot

sudo /sbin/chkconfig mysql on

or for SuSe:

sudo /sbin/chkconfig mysql on

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

ls /var/lib/mysql
    ibdata1  ib_logfile0  ib_logfile1  mysql  mysql.sock  test

7. Create the Leginon database, call it leginondb¶

sudo mysqladmin create leginondb

8. Create the Project database, call it projectdb¶

sudo mysqladmin create projectdb

9. Connect to mysql db¶

If starting from scratch the mysql root user will have no password. To set the root password use the command: sudo mysqladmin -u root password NEWPASSWORD

mysql -u root -p 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 can set specific (create, select, update, alter, drop, show, describe, rename) privileges or ALL privileges to the user. See MySQL Reference Manual for details.

mysql> CREATE USER usr_object@'localhost' IDENTIFIED BY 'PASSWORD';
mysql> GRANT ALTER, CREATE, DESCRIBE, SELECT, SHOW, UPDATE ON leginondb.* TO usr_object@'localhost';
mysql> GRANT ALTER, CREATE, DESCRIBE, SELECT, SHOW, UPDATE ON projectdb.* TO usr_object@'localhost';

less secure version (no password and all privileges)

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, you can assign a domain

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

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

# if your web host is local
mysql> GRANT ALTER, CREATE, DESCRIBE, SELECT, SHOW, UPDATE ON `ap%`.* to usr_object@localhost; 
# for all other hosts if you are accessing the databases from another computer
mysql> GRANT ALTER, CREATE, DESCRIBE, SELECT, SHOW, UPDATE ON `ap%`.* to usr_object@<host.mydomain.edu>;       

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 $EDITOR /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.