Database Server Installation Shared » History » Revision 34
Revision 33 (Neil Voss, 05/12/2010 09:24 AM) → Revision 34/82 (Neil Voss, 05/12/2010 11:54 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.
*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 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 mysql 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.
<pre>
mysql> CREATE USER usr_object@'localhost' IDENTIFIED BY 'PASSWORD';
mysql> GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON leginondb.* TO usr_object@'localhost';
mysql> 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.
<pre>
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';
</pre>
Similarly, you can assign a domain
<pre>
mysql> CREATE USER usr_object@'%' IDENTIFIED BY 'PASSWORD';
mysql> GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON leginondb.* to usr_object@<host.mydomain.edu>;
mysql> GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON projectdb.* to usr_object@<host.mydomain.edu>;
</pre>
Next, give create and access privileges for the processing databases which begin with "ap".
<pre>
# if your web host is local
mysql> 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
mysql> GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON `ap%`.* to usr_object@<host.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>
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:
<pre>
>mysql -u root -p mysql
</pre>
h3. 12. Check MySQL variables
<pre>
>mysql -u usr_object -p 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. Make sure MySQL is running
<pre>
> mysqlshow
+--------------+
| Databases |
+--------------+
| mysql |
| leginondb |
| projectdb |
+--------------+
</pre>
h3. Or check with the following php script (if already installed)
<pre>
<?
mysql_connect('HOST.INSTITUTE.EDU', 'usr_object', 'PASSWORD','leginondb');
echo mysql_stat();
?>
</pre>
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 see any error messages, the above part may be configured correctly.
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>
!phpMyAdmin.png!
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.
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.