Project

General

Profile

Database Server Installation » History » Version 13

Amber Herold, 03/16/2010 01:50 PM

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