Chris's Blog – October 2009 Archive (5)

I have complied a list of a good tips for DBAs to consider when implementing security policies in MySQL.



-- The List

1. Make sure that ALL root accounts (ALL PRIVILEGED accounts) have strong passwords and apply them using hashes not plain text. If you leave the password blank anyone can connect as root without a password and be granted all privileges.



2. For ALL other accounts use passwords and apply them using hashes not plain text.

A good… Continue

Consider excluding the following grants from users on any production MySQL server.



-- GRANT OPTION

"The GRANT OPTION privilege enables you to give to other users or remove from other users those privileges that you yourself possess." (http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_grant-option)



-- RELOAD

"The RELOAD privilege enables use of the… Continue

In the past I have needed to gain access to MySQL servers when the password had been strangely forgotten. Below there are thee different processes to gain access to MySQL if you just don’t have the right kind of access you need or want.



Option 1: --skip-grant-tables



This will allow you to login to the server and change what you need to in the mysql.user table. Of course you will need to restart the server again without --skip-grant-tables if you want the… Continue

Sometimes problems manifest inside of MySQL but the MySQL server is really not the problem. A good example is how MySQL uses reverse DNS lookups for authentication.



The Problem



You are alerted to a problem with a MySQL server either by Nagios or whatever… You log into the server and everything seems to be working correctly until you run “show full processlist”. The majority of your connections are in an “Unauthenticated” state and the rest are in some other state,… Continue

Over the past few years I have found that "pager" inside of MySQL is a really useful tool. I have come up with a few simple, but extremely effective, ways to use it.



-- Example 1

Lets say you have a MySQL server that is really busy with extremely long queries. You run a “show full processlist” and everything going on streams before your eyes, new lines and all. A more readable way to see what is going on is…



Mysql> pager less –S



By running the… Continue