I don't know how to spot a hacker. The available protection:
* Limit SUPER to root@localhost
* All applications are GRANT ... ON dbname.* ... -- so that if a hacker
does get in (or there is "sql injection", he cannot get to `mysql` db.
* read-write separation. Where practical, have two different logins --
one for read+write parts of your app, one for readonly. Again, the
attempt is to limit the damage.
* Use sufficient hardened passwords (obviously) for _all_ logins.
Query cache --
If there is 'continual' write activity, the QC is useless -- _Any_ write
to a table purges _all_ entries in the QC for that table. That purging
takes CPU effort, and blocks some operations.
Tables that never change, and have a lot of _identical_ queries, can
benefit greatly from the QC.
Slow queries --
Obfuscate the names (but not the datatypes); we can help without
compromising security.
There a zillion and one different cases. I could write a book, and it
would not be complete.
On 3/5/12 10:36 AM, Revathi Rangachari wrote:
> Hi Rick
>
> Thanks for the explanation.
>
> (1) wait_timeout is global..in my setup.
>
> (2)> Aborted_connects/Uptime -- when this is> 1, you might
>> have a hacker> trying to get in -
> ...........In my case this is 2. How will I find out a hacker is trying to get in? My
> log_warnings is set to 1.
>
> (3) Disabling query_cache_type - will it increase the CPU utilization?
>
> (4) For investigating slow queries, I will not be in a position to share all the
> information that you had asked for, due to security policies, however if you can provide
> some guideline as to how to proceed with the investigations using the commands given
> below, I will give it a try.
>
> Thanks
> Revathi R
>
>
> --- On Fri, 3/2/12, Rick James<rjames@stripped> wrote:
>
>> From: Rick James<rjames@stripped>
>> Subject: Re: query_cache_type and Aborted Clients
>> To: internals@stripped
>> Date: Friday, March 2, 2012, 2:53 AM
>> Which wait_timeout? There are
>> about 3 -- session vs global and
>> interactive versus batch.
>>
>> query_cache is (probably) totally independent of
>> connections.
>>
>> Aborted_clients is since startup, and will be "ever
>> increasing" --
>> useless without dividing by something:
>>
>> Aborted_clients/Uptime -- if> 1, then increase
>> wait_timeout
>> Aborted_clients/Connections -- if> 30, then increase
>> wait_timeout
>> Aborted_connects/Uptime -- when this is> 1, you might
>> have a hacker
>> trying to get in
>> Connections/Uptime -- if> 1, you might think about why
>> you need to
>> reconnect so often.
>>
>> If a client sits there too long (more than wait_timeout), he
>> will be
>> disconnected. 30 seconds is plenty for web-type
>> applications.
>>
>> For investigating slow queries, please provide
>> * SHOW CREATE TABLE
>> * SHOW TABLE STATUS
>> * EXPLAIN SELECT ...
>> * SHOW VARIABLES LIKE '%buffer%'; and how much RAM do you
>> have?
>>
>> On 3/1/12 10:16 AM, Revathi Rangachari wrote:
>>> Hi
>>>
>>> Can anyone please let me know:
>>> (1) if query_cache_type is disabled, will it result in
>> the number of 'Aborted Clients' getting increased.
>>> The possible explanation I have is that because the
>> slow queries are taking a long time to execute, and the fact
>> that the query_cache_type is also disabled, will it result
>> in the clients getting disconnected from the server due to
>> wait_timeout set both at the mysql level and application
>> level?
>>> In my mysql setup the wait_timeout is 30 secs and the
>> query_cache_type is disabled. I have an ever increasing
>> number of Aborted Clients
>>>
>>> +------------------+--------+
>>> | Variable_name | Value |
>>> +------------------+--------+
>>> | Aborted_clients | 661117 |
>>> | Aborted_connects | 156 |
>>> +------------------+--------+
>>>
>>> The wait_timeout at application level is 5 secs.
>>>
>>> Thanks in advance for any explanation on my query
>>> Revathi R
>>>
>>>
>>>
>>>
>> --
>> Rick James - MySQL Geek
>>
>>
>> --
>> MySQL Internals Mailing List
>> For list archives: http://lists.mysql.com/internals
>> To unsubscribe: http://lists.mysql.com/internals
>>
>>
--
Rick James - MySQL Geek
