User Comments

The client group of the config file does not seem to be read at all unless MYSQL_READ_DEFAULT_GROUP is used.

If you wish that the client group is read, but don't want to specify a default group, include the following:

mysql_options(mysql, MYSQL_READ_DEFAULT_GROUP, "client");

Be aware that connection wait_timeout may drop temporary tables generating a replication error at the slave ("table not found").
To prevent temporary table drops on long processes try to increase timeout for the client.

This is a script I made to dump GRANTs of MySQL
excluding root@localhost and Debian's debian-sys-maint@localhost users.

It also feature the ability to Dump hidden GRANTs
which are in mysql.db and not declared in mysql.user
for example USAGE grant to test@10.20.30.40 in mysql.user
but SELECT grant to test@% in mysql.db (without test@% in mysql.user)
such kind of manual rights SHOW GRANTS cannot handle
even in MySQL version 5.1.

### ------8<----CUT-HERE-----8<--------###
#!/bin/bash
# Copyright (c) 2011 Guy Baconniere

USAGE="$0 <MYSQLIP> [ <USERNAME> ] [ <PASSWORD> ]"

MASTERIP=${1:?$USAGE}
USERNAME=${2}
PASSWORD=${3}

if [ -z "$USERNAME" ]; then
AUTHENTIFICATION="--host=$MASTERIP"
else
AUTHENTIFICATION="--user=$USERNAME --password=$PASSWORD --host=$MASTERIP"
fi

# Dump all
( \
cat <<EOT | \
mysql \
$AUTHENTIFICATION \
--batch \
--silent \
--quick | \
mysql \
$AUTHENTIFICATION \
--batch \
--silent \
--quick | \
awk '/^GRANT/ { printf("%s;\n",$0); } END { printf("FLUSH PRIVILEGES;\n"); }'

SELECT
DISTINCT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') AS query
FROM mysql.user
WHERE
NOT (user IN ('root','debian-sys-maint') AND host IN ('localhost'));

EOT
for table in db tables_priv columns_priv; do \
mysqldump \
$AUTHENTIFICATION \
--single-transaction \
--skip-extended-insert \
--skip-comments \
--complete-insert \
--compatible=ansi \
--quick \
--where="CONCAT(user,'@',host) NOT IN (SELECT CONCAT(user,'@',host) FROM mysql.user)" \
mysql \
db; \
done | \
awk '/^INSERT/ { gsub("INSERT INTO \"","REPLACE INTO \"mysql.",$0); print $0; } END { printf("FLUSH PRIVILEGES;\n"); }' \
)
### ------8<----CUT-HERE-----8<--------###

Add your own comment.