Managing MYSQL users

These are a few commands I use in mysql to manage users and grants. I do this infrequently so I put them here to save having to google them when I need them.

Grant a user 'dev' all privileges on a database called "test";

mysql> GRANT ALL PRIVILEGES ON `test`.* TO 'dev'@'localhost' IDENTIFIED BY 'devtest';
Query OK, 0 rows affected (0.02 sec)

See full syntax for GRANT command

To see what privileges a user has been granted;

mysql> SHOW GRANTS FOR 'dev'@'localhost';
+-----------------------------------------------------------------------------------------+
| Grants for dev@localhost                                                                |
+-----------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dev'@'localhost' IDENTIFIED BY PASSWORD '*D98YCCE724CCT7BFA48E1' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'dev'@'localhost'                                   |
+-----------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Sometimes I need to list all the users that have had permissions granted to them;

mysql> SELECT CONCAT('SHOW GRANTS FOR \'', user,'\'@\'', host, '\';') AS mygrants FROM mysql.user ORDER BY mygrants;


+-------------------------------------------------+
| mygrants                                        |
+-------------------------------------------------+
| SHOW GRANTS FOR ''@'localhost';                 |
| SHOW GRANTS FOR 'debian-sys-maint'@'localhost'; |
| SHOW GRANTS FOR 'dev'@'192.168.4.2';            |
| SHOW GRANTS FOR 'dev'@'localhost';              |
| SHOW GRANTS FOR 'root'@'127.0.0.1';             |
| SHOW GRANTS FOR 'root'@'::1';                   |
| SHOW GRANTS FOR 'root'@'localhost';             |
+-------------------------------------------------+

From that table you can copy-paste the relevant line to see the grants for a particular user.


Revoke a grant

mysql> REVOKE ALL PRIVILEGES ON `test`.* FROM 'dev'@'localhost';
Query OK, 0 rows affected (0.02 sec)

After revoking a users privileges, you will notice that the user still shows up with USAGE rights. To make a user go away completely you need to "drop" them;

mysql> drop user 'dev'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysqldump from remote host

First you need to set the appropriate privileges on the target SQL server.

In this instance I want to use mysqldump to backup a database using a nightly script.

For this example assume the following;

mysql server host:   myserver.example.org
target database:     mydb
Backup host:         backup.example.org
Backup username:     backup

On the mysql server log in as root with;

mysql -u root -p

Enter this on the mysql console;

GRANT SELECT,LOCK TABLES ON mydb.* TO backup@backup.example.org
flush privileges;

On the backup server run this command;

mysqldump --host=myserver -u backup mydb > test.sql

Assuming that works, you can put the final command into your backup shell script or simply place it in your crontab.

Access denied for user 'debian-sys-maint'@'localhost'

Got this from the ever helpful ubuntuforums

Find your debian-sys-maint password in /etc/mysql/debian.cnf

Then use;

GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'password-from-above' WITH GRANT OPTION;