Project

General

Profile

Database Server Installation » History » Version 27

Amber Herold, 04/16/2010 09:55 AM

1 23 Amber Herold
h1. Database Server Installation
2 1 Amber Herold
3 27 Amber Herold
[[Database Server Installation Shared]]
4 22 Amber Herold
5 3 Amber Herold
h2. Install MySQL 
6 1 Amber Herold
7 3 Amber Herold
The following is for the computer that hosts the databases. This involves installing MySQL server and creation/configuration of the leginondb and projectdb databases.
8 1 Amber Herold
9 11 Amber Herold
Note: You may already have MySQL Server and Client installed. Check by typing mysql at the command line. 
10 4 Amber Herold
If you see a MySQL prompt (mysql>), skip steps 1 and 2.
11
 
12 8 Amber Herold
h3. 1. Install MySQL-Server
13 1 Amber Herold
14 7 Amber Herold
* Use your package installer (yum, zypper, YaST) if available.
15 3 Amber Herold
            OR
16 7 Amber Herold
* Download the latest MySQL-server RPM for Linux from www.mysql.com
17
* Install the MySQL-server rpm:
18 1 Amber Herold
19 7 Amber Herold
    rpm -Uvh MySQL-server-5.0.xx-y.i386.rpm
20 1 Amber Herold
21 7 Amber Herold
(substitute correct version numbers)
22 3 Amber Herold
23 8 Amber Herold
h3. 2. Install MySQL-Client
24 3 Amber Herold
25 7 Amber Herold
* Use your package installer (yum, zypper, YaST) if available.
26 3 Amber Herold
            OR
27 7 Amber Herold
* Download the latest MySQL-client RPM for Linux from www.mysql.com
28 18 Amber Herold
* Install the MySQL-client rpm:
29 3 Amber Herold
30 7 Amber Herold
    rpm -Uvh MySQL-client-5.0.xx-y.i386.rpm
31 3 Amber Herold
32 7 Amber Herold
(substitute correct version numbers)
33 3 Amber Herold
34 8 Amber Herold
h3. 3. MySQL configuration file is usually located in /usr/share/mysql. There are several examples there:
35 3 Amber Herold
36 7 Amber Herold
  > ls /usr/share/mysql/my*
37
    /usr/share/mysql/my-huge.cnf
38
    /usr/share/mysql/my-innodb-heavy-4G.cnf
39
    /usr/share/mysql/my-large.cnf
40
    /usr/share/mysql/my-medium.cnf
41
    /usr/share/mysql/my-small.cnf
42
  > 
43 3 Amber Herold
44 8 Amber Herold
h3. 4. Configure my.cnf in /etc using my-huge.cnf as the template
45 3 Amber Herold
46
         1. > cp /usr/share/mysql/my-huge.cnf /etc/my.cnf
47
48
         2. Edit /etc/my.cnf to add or change query cache variables like these:
49
            query_cache_type= 1
50
            query_cache_size = 100M
51
            query_cache_limit= 100M
52
53 8 Amber Herold
h3. 5. start MySQL Server
54 3 Amber Herold
55
       >/etc/init.d/mysqld start
56
57 7 Amber Herold
on some installation,
58 3 Amber Herold
59 1 Amber Herold
       >/etc/init.d/mysql start
60 3 Amber Herold
61 7 Amber Herold
For future reference: start | stop | restart MySQL Server with similar commands:
62 1 Amber Herold
63 3 Amber Herold
       >/etc/init.d/mysqld start
64
       >/etc/init.d/mysqld stop
65
       >/etc/init.d/mysqld restart
66
67 7 Amber Herold
If you want to start MySQL automatically at boot on SuSE
68 3 Amber Herold
69
       SuSE >chkconfig mysql on
70
71 8 Amber Herold
h3. 6. For future reference, the database location will be:
72 3 Amber Herold
73
      > cd /var/lib/mysql
74
      Directory: /var/lib/mysql
75
      > ls
76 1 Amber Herold
      yourdbserver.pid
77
      ib_logfile0
78
      mysql
79 3 Amber Herold
      mysql.sock
80 1 Amber Herold
      test
81 3 Amber Herold
      > 
82
83 11 Amber Herold
h3. 7. Create the Leginon database, call it leginondb
84 8 Amber Herold
85 11 Amber Herold
       >mysqladmin create leginondb
86 3 Amber Herold
87 11 Amber Herold
h3. 8. Create the Project database, call it projectdb
88 1 Amber Herold
89 11 Amber Herold
       >mysqladmin create projectdb
90 3 Amber Herold
91 8 Amber Herold
h3. 9. Connect to mysql db
92 3 Amber Herold
93 12 Amber Herold
<pre>
94 3 Amber Herold
      >mysql mysql
95
96 1 Amber Herold
      mysql> select user, password, host from user;
97 3 Amber Herold
      +------+----------+-----------+
98
      | user | password | host      |
99 1 Amber Herold
      +------+----------+-----------+
100
      | root |          | localhost |
101
      | root |          | host1     |
102
      |      |          | host1     |
103
      |      |          | localhost |
104 3 Amber Herold
      +------+----------+-----------+
105
      4 rows in set (0.00 sec)
106 12 Amber Herold
</pre>
107 1 Amber Herold
108 3 Amber Herold
h3. 10. Create user
109 1 Amber Herold
110 3 Amber Herold
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
111 8 Amber Herold
112
      mysql> create user usr_object@'localhost';
113 11 Amber Herold
      mysql> grant all privileges on leginondb.* to usr_object@'localhost';
114
      mysql> grant all privileges on projectdb.* to usr_object@'localhost';
115 3 Amber Herold
116 8 Amber Herold
Similarly,
117 3 Amber Herold
118 4 Amber Herold
      mysql> create user usr_object@'%';
119 11 Amber Herold
      mysql> grant all privileges on leginondb.* to usr_object@'%';
120
      mysql> grant all privileges on projectdb.* to usr_object@'%';
121 3 Amber Herold
122 19 Eric Hou
Next, give create and access privileges for the processing databases which begin with "ap".
123
124 20 Eric Hou
      // if your web host is local
125
      mysql> grant all privileges on `ap%`.* to usr_object@localhost; 
126
      // for all other hosts if you are accessing the databases from another computer
127
      mysql> grant all privileges on `ap%`.* to usr_object@`%`;       
128 19 Eric Hou
129
130 3 Amber Herold
h3. 11. Change Root password
131 1 Amber Herold
132 12 Amber Herold
<pre>
133
      mysql> update user set password=password('your_own_root_password') where user="root";
134 3 Amber Herold
      Query OK, 2 rows affected (0.01 sec)
135
      Rows matched: 2  Changed: 2  Warnings: 0
136 1 Amber Herold
137 12 Amber Herold
      mysql> flush privileges;
138 1 Amber Herold
      mysql>^D or exit;
139 12 Amber Herold
</pre>
140 3 Amber Herold
141
From now on, you will need to specify the password to connect to the database as root user like this:
142
143
      >mysql -u root -p mysql
144
145 1 Amber Herold
h3. 12. Check MySQL variables
146 8 Amber Herold
147 12 Amber Herold
<pre>
148 11 Amber Herold
      >mysql -u usr_object leginondb
149 3 Amber Herold
150
      mysql> SHOW VARIABLES LIKE 'query%';
151
      +------------------------------+-----------+
152
      | Variable_name                | Value     |
153
      +------------------------------+-----------+
154
      | ft_query_expansion_limit     | 20        |
155 1 Amber Herold
      | have_query_cache             | YES       |
156 3 Amber Herold
      | long_query_time              | 10        |
157
      | query_alloc_block_size       | 8192      |
158
      | query_cache_limit            | 104857600 | <<---This should correspond to your change
159
      | query_cache_min_res_unit     | 4096      |
160 1 Amber Herold
      | query_cache_size             | 104857600 | <<---This should correspond to your change
161 3 Amber Herold
      | query_cache_type             | ON        | <<---This should correspond to your change
162
      | query_cache_wlock_invalidate | OFF       |
163
      | query_prealloc_size          | 8192      |
164 1 Amber Herold
      +------------------------------+-----------+
165 3 Amber Herold
      10 rows in set (0.00 sec)
166
167 1 Amber Herold
      mysql> exit;
168 12 Amber Herold
</pre>
169 3 Amber Herold
170
h3. 13. Make sure MySQL is running
171
172 12 Amber Herold
<pre>
173 1 Amber Herold
      prompt:~> mysqlshow
174 3 Amber Herold
      +--------------+
175
      | Databases    |
176
      +--------------+
177
      | mysql        |
178 13 Amber Herold
      | leginondb    |
179
      | projectdb    |
180 3 Amber Herold
      +--------------+
181 12 Amber Herold
</pre>
182 3 Amber Herold
183 8 Amber Herold
h3. 14. Or check with the following php script (if already installed)
184 3 Amber Herold
185
      <?
186 11 Amber Herold
      mysql_connect('your_host.your_institute.edu', 'usr_object', '','leginondb');
187 3 Amber Herold
      echo mysql_stat();
188
      ?> 
189
190 8 Amber Herold
Output
191 3 Amber Herold
192
       Uptime: 1452562 Threads: 1 Questions: 618 Slow queries: 0 Opens: 117 Flush tables: 1 Open tables: 106 Queries per second avg: 0.000
193
194 1 Amber Herold
195
h2. Configure phpMyAdmin 
196
197
Edit the phpMyAdmin config file:
198
199
<pre>
200 14 Amber Herold
$ sudo vi /etc/phpMyAdmin/config.inc.php
201 1 Amber Herold
</pre> 
202
203
and change the following lines:
204
205
<pre>
206
$cfg['Servers'][$i]['AllowRoot']     = FALSE;
207
</pre>
208
209
Edit the phpMyAdmin apache config file:
210
211
<pre>
212 14 Amber Herold
$ sudo vi /etc/httpd/conf.d/phpMyAdmin.conf
213 1 Amber Herold
</pre>
214
215
and change the following lines:
216 17 Amber Herold
217 16 Amber Herold
*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
218 1 Amber Herold
219
<pre>
220
<Directory /usr/share/phpMyAdmin/>
221
   order deny,allow
222
   deny from all
223
   allow from 127.0.0.1
224
   allow from YOUR_IP_ADDRESS
225
</Directory>
226
</pre>
227 15 Amber Herold
228
To test the PHPMyAdmin configuration, point your browser to http://YOUR_IP_ADDRESS/phpmyadmin. 
229 24 Amber Herold
230
______