Database Server Installation » History » Revision 3
Revision 2 (Amber Herold, 03/15/2010 04:11 PM) → Revision 3/32 (Amber Herold, 03/15/2010 04:19 PM)
h1. Database Server-side Installation Setup MySQL database 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 Configure MySQL The following is for Edit the computer that hosts the databases. This involves installing MySQL server and creation/configuration of the leginondb and projectdb databases. configuration file: Table�4.3.�Leginon Database Supporting Packages <pre> Name: Download site: $ sudo nano /etc/my.cnf MySQL-Server 5.0 </pre> Add or higher http://www.mysql.com edit the following lines: <pre> MySQL-Client 5.0 or higher http://www.mysql.com query_cache_type = 1 MySQL 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) 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) 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 > 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 5. start </pre> Restart MySQL Server >/etc/init.d/mysqld start on some installation, >/etc/init.d/mysql start For future reference: start | stop | <pre> $ sudo /sbin/service mysqld 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 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 > 7. Create leginon database, here we call dbemdata >mysqladmin create dbemdata 8. Create project database, here we call projectdata (optional) >mysqladmin create projectdata 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) 10. 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 usr_object@'%'; mysql> grant all privileges on dbemdata.* to usr_object@'%'; mysql> grant all privileges on projectdata.* to usr_object@'%'; 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; 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; 13. Make sure MySQL is running prompt:~> mysqlshow +--------------+ | Databases | +--------------+ | mysql | | dbemdata | | projectdata | +--------------+ 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 </pre> 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>