Mermaids have the same probability of fixing your permission problems, but people continue believing in the FLUSH PRIVILEGES myth.I see suggesting the usage of FLUSH PRIVILEGES every time someone writes a tutorial or a solution to a problem regarding creating a new account or providing different privileges. For example, the top post on /r/mysql as of the writing of these lines, “MySQL:The user specified as a definer does not exist (error 1449)-Solutions” has multiple guilty cases of this (Update: the user has corrected those lines after I posted this article).

It is not my intention to bash that post, but I have seen …

Installed on a clean CentOS 6.4 AWS instance.

sudo su -
cd /tmp
wget http://cdn.mysql.com/Downloads/MySQL-5.6/MySQL-5.6.13-1.el6.x86_64.rpm-bundle.tar
tar xvf MySQL-5.6.13-1.el6.x86_64.rpm-bundle.tar
yum install -y libaio perl
rpm -i MySQL*.rpm

The following output is the sign that security is being considered with new MySQL versions. Woot!

A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.

You must change that password on your first connect,
no other statement but 'SET PASSWORD' will be accepted.
See the manual for the semantics of the 'password expired' flag.

Also, the account for the anonymous user has been removed.

In addition, you can run:

  /usr/bin/mysql_secure_installation

which will also give you the option of removing the test database.
This is strongly recommended for production servers.

See the manual for more instructions.

Please report any problems with the …

I've previously written about AppArmor and MySQL, and how to change MySQL's default file locations on systems with AppArmor enabled. Ubuntu and SUSE ship with AppArmor enabled, but some other distributions such as Oracle Linux don't, along with related distrubutions such as Red Hat, CentOS and Fedora. Rather, these other distributions use another mandatory access control system called SELinux. 

Here's some technical detail that might come in handy later.

SELinux uses concepts such as types and domains. Types belong to resources such as files and ports; these are the "objects" in SELinux. Domains contain the "subjects" (processes) and object types that are associated with each other in some way, for example because they are all related to …

The Issue
I haven't blogged in a while an I have a long TODO list of things to publish: The repository for the SNMP Agent, video and slides of my OSCON talk and a quick overview of MHA master-master support. In the meantime, here's a little fact that I didn't know from MySQL CREATE VIEW documentation:

Although it is possible to create a view with a nonexistent DEFINER account, an error occurs when the view is referenced if the SQL SECURITY value is DEFINER but the definer account does not exist.How can this be possible?
The ProblemFor a number of reasons we don't have the same user accounts on the master than we have on the slaves (ie: developers shouldn't be querying the master). Our configuration files include the following line:

replicate-ignore-table=mysql.user

So if …

I really like ZRM for MySQL Community Edition for backups. I find
it very simple to install and configure and it gets the job done. It's default settings suits most installations. The challenges arise once you need to adapt it to more complex organizations. Please note that all the issues described on this article refer to Linux installations.
What If DBAs Have No sudo?This was the 1st challenge that I ever faced. ZRM requires full root access at least during installation and in some cases, it requires some sudo permissions to be able to manage its files as well. Some organizations don't grant these permissions to the DBAs complicating the installation. When I looked into the scripts, I didn't see any reason why they wouldn't run in the user space, other than a few hard coded paths. I posted the question in the Zmanda forums and I got an answer with some …

Sometimes, you want to backup individual databases in MySQL to move to a different server. This part is easy using mysqldump:

shell> mysqldump -u root -p --databases db1 db2 ... > backup.sql

The problem is, what happens when you want to backup the permissions associated with these databases? Well, here are a few queries to help you out.

-- Grab the users with global permissions, 
-- with permissions to the databases you want, 
-- and tables/stored procedures in it.
mysql> SELECT u.* INTO OUTFILE '/tmp/user.txt'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
        LINES TERMINATED BY '\n'
        FROM
                mysql.user u
        WHERE
                u.Select_priv='Y'
        UNION
        SELECT u.*
        FROM
                mysql.user u,
                mysql.db d
        WHERE
                d.Db IN('db1', 'db2', ...) AND
                d.User = u.user
        UNION
        SELECT u.* …

I had an issue come up recently that involved some confusion over permissions for the same user, connecting through different interfaces. For example, say you have a server with the public IP address of 192.168.0.1. You could connect to it from the local machine using the following commands:

shell> mysql -h localhost           # Connects through the socket file
shell> mysql -h 127.0.0.1           # Connects through the loopback interface
shell> mysql -h 192.168.0.1         # Connects through the network interface

They all connect to the local server, but they can all have different permissions. Here are a couple of rules to make your life easier:

• Don’t use @127.0.0.1, unless you absolutely can’t use the socket file for some reason. Connecting through @localhost is usually faster than the loopback device, and it’s easier to type.
• Only connect through the network interface if you’re planning on …

Scenario: Someone wants to know which of the over 50 MySQL users have certain privileges.

There are many ways to solve this problem. Some of these scenarios are tedious and repetitious, others take no time at all.

The issue, of course, lies in what the “certain” privileges are. If it is “who has the SUPER privilege?” then a simple

SELECT user,host FROM mysql.user WHERE Super_priv='Y';

is sufficient. If it is “who has write access to the foo database”, you might write:

SELECT user,host FROM db WHERE Db='foo' AND Select_priv='Y';

but that only shows who explicitly has read permissions on that database; it does not include those who have global read permissions. The full query would be:
(more…)