MySQL manager

Commandline client for MySQL database and user administration (in PHP)

This tool allows for managing users, databases and access rights on a MySQL database server from the command line. You won’t need graphical user interfaces like MySQL’s Administrator or phpMyAdmin for this simple task. This is especially useful for automated routines like setting up applications or as backend script for a web UI to allow users to manage their own databases. This script, along with a small security wrapper, is used in the clients’ configuration interface at dotforward.de webhosting. Here are some examples of usage:

• Add a new database “db1”:
  mysqlmgr add database db1
• Add a new user “alice”, interactively ask for the password and grant reading access to the created database:
  mysqlmgr -a add user alice
  mysqlmgr allow user alice all hosts database db1 read
• Add a new user “bob” with the password as argument:
  mysqlmgr add user bob password s3cr3t
• Allow “bob” from all hosts writing/defining access to the created database:
  mysqlmgr allow user bob all hosts database db1 read write define
• List all users:
  mysqlmgr list users
• Show all access rights for user “bob”:
  mysqlmgr show user bob
• Rename user “bob” to “chris”:
  mysqlmgr rename user bob to chris
• Disallow “chris” to add new data to his database:
  mysqlmgr deny user chris all hosts database db1 insert update
• Show MySQL version of the server db7.company.com:
  mysqlmgr -h db7.company.com version

You can add or edit user accounts for separate originating hostnames. If none is specified, the host “%” is used, which means “connections from all hosts”. When editing a user account, you must use the same host specification as when you added it. (Think of the username and hostname as a pair that describes the user account.) To modify all user accounts with that name (i.e. for connections from all possible hosts), you can use the argument “all hosts” which effectively applies the operation on all present user accounts. If you don’t want to care about different hostnames, you can always omit this specification.

These management commands are sent to the MySQL server through a superuser connection. This is usually the username “root”. To not always enter root’s MySQL password and specifying the server’s hostname (and port) if non-local, you can edit these default values at the top of the programme file. The default connection parameters (except the password) are displayed in the help message.

Download

Version 20080719 (19. Juli 2008)

mysqlmgr.zip (PHP-Programm im ZIP-Archiv, 6 kB)

Requirements

This application requires a PHP command line interpreter (CLI) of version 5.0 or newer, with MySQL support, in the path /usr/bin/php. With small modifications in the first line (#!) this script should be able to run on any platform supported by PHP, i.e. also Windows.

Known issues, open tasks

• Revoke all access rights on a deleted database (and table)
• Support table-level access rights
• Test and if necessary replace commandline arguments parser code (better version included in the archive)

Changes

Version 20080719 (19. Juli 2008)

• Print newline after asking for a password invisibly

Version 20080405 (5. April 2008)

• Make typing of the password invisible like it is in ‘su’ – Only supported on Linux! Other OS use the old method.

Version 20060503 (3. Mai 2006)

• Initial release

Licence, terms of use

This programme is “freeware”. This means that it is free of charge for private and commercial use. Further distribution is prohibited without my permission. There is no warranty, not even or merchantability for fitness for a particular purpose. I am not liable for any damage caused through appropriate or inappropriate use.