Appion

h1. Database Server Installation

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.

h3. 1 Install MySQL-Server and MySQL-Client

*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>), you may skip this step.

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

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

   <pre>sudo yum install mysql mysql-server</pre>

   For Suse

   <pre>yast2 -i mysql mysql-client</pre>

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

h3. 2 Locate Example MySQL configuration files 

They are usually located in /usr/share/mysql.

<pre>

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

</pre>

If that does not work try the locate function

<pre>

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

</pre>

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

# Copy my-huge.cnf to my.cnf

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

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

<pre>

query_cache_type = 1

query_cache_size = 100M

query_cache_limit= 100M

</pre>

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

<pre>default-storage-engine=MyISAM</pre>

h3. 4 Start the MySQL Server

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

<pre>

sudo /sbin/service mysqld start

</pre>

For other Unix systems:

<pre>

sudo /etc/init.d/mysqld start

</pre>

or on some installations (Suse),

<pre>

sudo /etc/init.d/mysql start

</pre>

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

For Centos, Fedora

<pre>

sudo /etc/init.d/mysqld start

sudo /etc/init.d/mysqld stop

sudo /etc/init.d/mysqld restart

</pre>

or

<pre>

sudo /sbin/service mysqld start

sudo /sbin/service mysqld stop

sudo /sbin/service mysqld restart

</pre>

or for Suse

<pre>

sudo /etc/init.d/mysql start

sudo /etc/init.d/mysql stop

sudo /etc/init.d/mysql restart

</pre>

h3. 5 Configure MySQL to start automatically at boot

<pre>

sudo /sbin/chkconfig mysqld on

</pre>

or for SuSe:

<pre>

sudo /sbin/chkconfig --add mysql

</pre>

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

<pre>

ls /var/lib/mysql

    ibdata1  ib_logfile0  ib_logfile1  mysql  mysql.sock  test

</pre>

h3. 7 Create the Leginon database, call it leginondb

<pre>

sudo mysqladmin create leginondb

</pre>

h3. 8 Create the Project database, call it projectdb

<pre>

sudo mysqladmin create projectdb

</pre>

h3. 9 Connect to mysql db

If starting from scratch, the mysql root user will have no password. This is assumed to be the case and we will set it later.

<pre>

mysql -u root mysql

</pre>

You should see a mysql prompt: mysql>

You can view the current mysql users with the following command.

<pre>

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 can set specific (@ALTER, CREATE, DROP, DELETE, INSERT, RENAME, SELECT, UPDATE@) privileges or @ALL@ privileges to the user. See MySQL Reference Manual for details. The following examples demonstrate some of the options available.

At the mysql prompt execute the following commands:

<pre>

CREATE USER usr_object@'localhost' IDENTIFIED BY 'YOUR PASSWORD';

GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON leginondb.* TO usr_object@'localhost';

GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON projectdb.* TO usr_object@'localhost';

</pre>

After the webserver and myamiweb is installed, you should change privileges of these three tables:

<pre>

GRANT DELETE ON leginondb.ViewerImageStatus TO usr_object@'localhost';

GRANT DELETE ON leginondb.ImageStatusData TO usr_object@'localhost';

GRANT DELETE ON projectdb.processingdb TO usr_object@'localhost';

</pre>

If you use Leginon to do robotic grid screening, you will also need to do the following to grant delete privileges to the these tables at the mysql prompt:

<pre>

GRANT DELETE ON projectdb.gridboxes TO usr_object@'localhost';

GRANT DELETE ON projectdb.grids TO usr_object@'localhost';

GRANT DELETE ON projectdb.gridlocations TO usr_object@'localhost';

</pre>

You may choose to use the following less secure version (no password and all privileges) of the commands above, however, we recommend not allowing the DROP privilege on all tables nor DELETE privilege on most tables.

At the mysql prompt execute the following commands:

<pre>

CREATE USER usr_object@'localhost';

GRANT ALL PRIVILEGES ON leginondb.* TO usr_object@'localhost';

GRANT ALL PRIVILEGES ON projectdb.* TO usr_object@'localhost';

</pre>

You may also choose to assign a domain to your commands and use a wildcard to allow access from all computers in the domain. 

<pre>

CREATE USER usr_object@'%.mydomain.edu' IDENTIFIED BY 'YOUR PASSWORD';

GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON leginondb.* to usr_object@'%.mydomain.edu';

GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON projectdb.* to usr_object@'%.mydomain.edu';

GRANT DELETE ON leginondb.ViewerImageStatus TO usr_object@'%.mydomain.edu';

GRANT DELETE ON leginondb.ImageStatusData TO usr_object@'%.mydomain.edu';

GRANT DELETE ON projectdb.processingdb TO usr_object@'%.mydomain.edu';

</pre>

Again, if you use Leginon to do robotic grid screening, you may want to grant delete privileges to the these tables at the mysql prompt:

<pre>

GRANT DELETE ON projectdb.gridboxes TO usr_object@'%.mydomain.edu';

GRANT DELETE ON projectdb.grids TO usr_object@'%.mydomain.edu';

GRANT DELETE ON projectdb.gridlocations TO usr_object@'%.mydomain.edu';

</pre>

h3. 11 Give create and access privileges for the processing databases which begin with "ap".

<pre>

# if your web host is local

GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON `ap%`.* to usr_object@localhost; 

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

GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON `ap%`.* to usr_object@'%.mydomain.edu';       

</pre>

h3. 12 Change Root password

To set the root password use the command: 

<pre>

sudo mysqladmin -u root password NEWPASSWORD

</pre>

Or you can do it from within mysql

<pre>

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

# run the flush privileges command to avoid problems

flush privileges;

^D or exit;

</pre>

From now on, you will need to specify the password to connect to the database as root user like this:

<pre>

mysql -u root -p mysql

</pre>

h3. 13 Check MySQL variables

<pre>

# at the command prompt, log into the leginon database

mysql -u usr_object -p leginondb

# At the mysql prompt show variables that begin with 'query'.

# Check that the changes you made to my.cfg are in place.

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)

exit;

</pre>

 If you do not see your changes, try restarting mysql. 

 On centOS: 

<pre>

sudo /etc/init.d/mysqld restart

</pre>

h3. 14 Make sure MySQL is running

<pre>

mysqlshow -u root

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

      | Databases    |

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

      | mysql        |

      | leginondb    |

      | projectdb    |

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

</pre>

h3. Run the following command from the command line:

<pre>

php -r "mysql_connect('localhost', 'usr_object', 'PASSWORD', 'leginondb'); echo mysql_stat();"; echo ""

</pre>

Expected output:

<pre>

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

</pre>

If there are any error messages, mysql may be configured incorrectly.

*Note:* If you do not have php and php-mysql packages installed you need to install them to run the above command. The yum installation is:

<pre>

sudo yum -y install php php-mysql

</pre>