Database Server Installation » History » Revision 28
Revision 27 (Amber Herold, 04/16/2010 09:55 AM) → Revision 28/32 (Amber Herold, 04/16/2010 09:56 AM)
{{include(Database h1. Database Server Installation Shared)}} [[Database Server Installation Shared]] 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. ______