Database Server Installation Shared » History » Revision 38
Revision 37 (Amber Herold, 05/14/2010 03:44 PM) → Revision 38/82 (Amber Herold, 05/17/2010 10:41 AM)
h1. Database Server Installation 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. h3. Install MySQL-Server and MySQL-Client *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>), you may skip this step. to step 2 h3. Install MySQL-Server and MySQL-Client To install Mysql on Linux you have two options (the first option is better): # Use your package installer (yum, zypper, YaST, apt-get). For example: <pre>sudo yum install mysql mysql-server</pre> # Download the latest MySQL-server package for Linux from http://www.mysql.com h3. Example MySQL configuration files are usually located in /usr/share/mysql. <pre> 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 </pre> If that does not work try the locate function <pre> locate my | egrep "\.cnf$" /etc/my.cnf /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 </pre> h3. Configure my.cnf in /etc using my-huge.cnf as the template # Copy my-huge.cnf to my.cnf <pre>sudo cp -v /usr/share/mysql/my-huge.cnf /etc/my.cnf</pre> # Edit /etc/my.cnf to add or change query cache variables like these (be sure to place them under the @[mysqld]@ section): <pre> query_cache_type= 1 query_cache_size = 100M query_cache_limit= 100M </pre> # Search for the text default-storage-engine in /etc/my.cnf. If it exists and is set to other than MyISAM, you should change it to: <pre>default-storage-engine=MyISAM</pre> h3. Start the MySQL Server For CentOS/Fedora/RHEL system use the service command: <pre> sudo /sbin/service mysqld start </pre> For other Unix systems: <pre> sudo /etc/init.d/mysqld start </pre> or on some installations, <pre> sudo /etc/init.d/mysql start </pre> For future reference: start | stop | restart MySQL Server with similar commands: <pre> sudo /etc/init.d/mysqld start sudo /etc/init.d/mysqld stop sudo /etc/init.d/mysqld restart sudo /sbin/service mysqld start sudo /sbin/service mysqld stop sudo /sbin/service mysqld restart </pre> If you want to start MySQL automatically at boot <pre> sudo /sbin/chkconfig mysqld on </pre> or for SuSe: <pre> sudo /sbin/chkconfig mysql on </pre> h3. 6. For future reference, the database location will be: <pre> ls /var/lib/mysql ibdata1 ib_logfile0 ib_logfile1 mysql mysql.sock test </pre> h3. 7. Create the Leginon database, call it leginondb <pre> sudo mysqladmin create leginondb </pre> h3. 8. Create the Project database, call it projectdb <pre> sudo mysqladmin create projectdb </pre> h3. 9. Connect to mysql db If starting from scratch the mysql root user will have no password. This is assumed to be the case and we will set it later. <pre> mysql -u root mysql </pre> <pre> 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 can set specific (@ALTER, CREATE, DROP, DELETE, INSERT, RENAME, SELECT, UPDATE@) privileges or @ALL@ privileges to the user. See MySQL Reference Manual for details. At the mysql prompt execute the following commands: <pre> CREATE USER usr_object@'localhost' IDENTIFIED BY 'YOUR PASSWORD'; GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON leginondb.* TO usr_object@'localhost'; GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON projectdb.* TO usr_object@'localhost'; </pre> less secure version (no password and all privileges), we recommend not allowing the DROP and DELETE privileges. At the mysql prompt execute the following commands: <pre> CREATE USER usr_object@'localhost'; GRANT ALL PRIVILEGES ON leginondb.* TO usr_object@'localhost'; GRANT ALL PRIVILEGES ON projectdb.* TO usr_object@'localhost'; </pre> Similarly, you can assign a domain <pre> CREATE USER usr_object@'%.mydomain.edu' IDENTIFIED BY 'YOUR PASSWORD'; GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON leginondb.* to usr_object@'%.mydomain.edu'; GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON projectdb.* to usr_object@'%.mydomain.edu'; </pre> Next, give create and access privileges for the processing databases which begin with "ap". <pre> # if your web host is local GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON `ap%`.* to usr_object@localhost; # for all other hosts if you are accessing the databases from another computer GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON `ap%`.* to usr_object@'%.mydomain.edu'; </pre> h3. 11. Change Root password To set the root password use the command: <pre> sudo mysqladmin -u root password NEWPASSWORD </pre> Or you can do it from within mysql <pre> 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 flush privileges; ^D or exit; </pre> From now on, you will need to specify the password to connect to the database as root user like this: <pre> mysql -u root -p mysql </pre> h3. 12. Check MySQL variables <pre> mysql -u usr_object -p leginondb 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) exit; </pre> h3. Make sure MySQL is running <pre> mysqlshow +--------------+ | Databases | +--------------+ | mysql | | leginondb | | projectdb | +--------------+ </pre> h3. Run the following command from the command line: <pre> php -r "mysql_connect('localhost', 'usr_object', 'PASSWORD', 'leginondb'); echo mysql_stat();"; echo "" </pre> Expected output: <pre> Uptime: 1452562 Threads: 1 Questions: 618 Slow queries: 0 Opens: 117 Flush tables: 1 Open tables: 106 Queries per second avg: 0.000 </pre> If there are any error messages, mysql may be configured incorrectly. h2. Configure phpMyAdmin Edit the phpMyAdmin config file @/etc/phpMyAdmin/config.inc.php@ and change the following lines: <pre> $cfg['Servers'][$i]['AllowRoot'] = FALSE; </pre> Edit the phpMyAdmin apache config file @/etc/httpd/conf.d/phpMyAdmin.conf@ 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> *Note:* If you want to access phpMyAdmin from another computer, you can also add it to this config file with an @allow from@ tag Next restart the web server to take on the new setting <pre> sudo /sbin/service httpd restart </pre> To test the phpMyAdmin configuration, point your browser to http://YOUR_IP_ADDRESS/phpMyAdmin or http://localhost/phpMyAdmin and login with the usr_object user. !phpMyAdmin.png! A common problem is that the firewall may be blocking access to the web server and mysql server. On CentOS/Fedora you can configure this with the system config: <pre> system-config-securitylevel </pre> Firewall configuration is specific to different Unix distributions, so consult a guide on how to do this on non-RedHat machines.