Database Server Installation » History » Revision 9
Revision 8 (Amber Herold, 03/15/2010 04:33 PM) → Revision 9/32 (Amber Herold, 03/15/2010 04:34 PM)
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....
Start Here: http://emg.nysbc.org/documentation/leginon/bk02ch04s07.php
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
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.
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>