[cobalt-users] MySQL Server startup

[Oliver Schlag <chairman@xxxxxxxx>]

Hy together,

i've had some problems with MySQL on my RaQ and some other people have reported 
me similar problems with too many open connections and users seeing other users 
databases (but could not access). For this problem i've sat down and searched 
for a way to solve the Problem. My main Problem was that some users created bad 
PHP/Perl scripts, which does not close the mysql connection correct and the 
mysql thread runs and runs and consumes memory and cpu time. So i've searched a 
bit and found a little solution, which i want to share with you.

Please not that the following is an 'as is' solution. It works on my system, it 
may work on your's too but it's possible that it will not work. So first create 
a backup copy of all files you modify and take in mind that the cobalt warranty 
may void if you change any system files via root access.

To make theese changes connect via SSH (if you not have installed it use telnet 
and install SSH for security reasons first) to your RaQ and su to root.

1. Switch into /etc/rc.d/init.d
2. cp /etc/rc.d/init.d/mysql /etc/rc.d/init.d/mysql.backup
3. Open the file mysql in your favourite editor (mine is vi)
4. Search the line 
$bindir/safe_mysqld --user=$mysql_daemon_user --pid-file=$pid_file --
datadir=$datadir &

5. Change this line to 
$bindir/safe_mysqld --user=$mysql_daemon_user --pid-file=$pid_file --
datadir=$datadir --set-variable max_connections=200 --set-variable 
interactive_timeout=120 --set-variable wait_timeout=120 --safe-show-database &

6. Save the file and quit the editor

7. Make /etc/rc.d/init.d/mysql stop and then /etc/rc.d/init.d/mysql start

8. Check that the new settings apply run mysqld --help and look what are the 
values of the changed variables.

So what does it make ?

--set-variable max_connections=200 : Sets the maximum connections up to 200. 
Everybody has to take how many are good for his system. This depends on the 
memory and the global usage of the system. Perhaps a lower value is good on 
some systems. (On my systems its still 100 and after changing the timeouts i've 
never had any more problems with wild growing mysql processes)

--set-variable interactive_timeout=120 : The number of seconds the server waits 
for activity on an interactive connection before closing it.

--set-variable wait_timeout=120 : The number of seconds the server waits for 
activity on a connection before closing it

--safe-show-database : Don't show databases for which the user doesn't have any 
privileges.

Hope that helps some people. If there are any Problems then please report them. 
Perhaps you have to tune the settings to fit the needs of your users, but on 
most systems this should run. There are more tips for tuning mysql but i've not 
tested them so i will not post them.

If something went wrong just copy the mysql.backup back to mysql and all should 
go like before.

Have a nice day
Oliver Schlag

------------------------------------------------------------------------
Oliver Schlag
Hauptstr. 43                                          Tel : 06081-963009
61267 Neu Anspach                                     Fax : 06081-43953
------------------------------------------------------------------------

P.S. I've written this out of my mind only with the redhat rpm here, so please 
look for errors.