Database Server Installation » History » Revision 8
Revision 7 (Amber Herold, 03/15/2010 04:29 PM) → Revision 8/32 (Amber Herold, 03/15/2010 04:33 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>