Database Server Installation » History » Revision 22
Revision 21 (Amber Herold, 04/06/2010 01:38 PM) → Revision 22/32 (Amber Herold, 04/06/2010 06:02 PM)
h1. Database Server-side Installation
{{toc}}
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.
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 the Leginon database, call it leginondb
>mysqladmin create leginondb
h3. 8. Create the Project database, call it projectdb
>mysqladmin create projectdb
h3. 9. Connect to mysql db
<pre>
>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)
</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 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@`%`;
h3. 11. Change Root password
<pre>
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;
</pre>
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
h3. 12. Check MySQL variables
<pre>
>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;
</pre>
h3. 13. Make sure MySQL is running
<pre>
prompt:~> mysqlshow
+--------------+
| Databases |
+--------------+
| mysql |
| leginondb |
| projectdb |
+--------------+
</pre>
h3. 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
h2. Configure phpMyAdmin
Edit the phpMyAdmin config file:
<pre>
$ sudo vi /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 vi /etc/httpd/conf.d/phpMyAdmin.conf
</pre>
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
<pre>
<Directory /usr/share/phpMyAdmin/>
order deny,allow
deny from all
allow from 127.0.0.1
allow from YOUR_IP_ADDRESS
</Directory>
</pre>
To test the PHPMyAdmin configuration, point your browser to http://YOUR_IP_ADDRESS/phpmyadmin.