Ease MySQL server administration with mysql-tools

This howto is tested on :

• Debian 6.0 Squeeze
• Ubuntu 10.04 Lucid Lynx
• Ubuntu 10.10 Natty Narwal

Prerequisites

This howto recommend the installation of a MySQL server as described in Install and setup MySQL on Debian.

Installation

Install the MySQL client software and the password generation tool apg :

command apt-get install mysql-client apg

Install the mysql-tools script:

command wget "https://raw.github.com/biapy/howto.biapy.com/master/mysql/mysql-tools" \
    --quiet --no-check-certificate --output-document="/usr/bin/mysql-tools"
command chmod +x "/usr/bin/mysql-tools"

Note : This tool is improved on a regular basis.

Presentation of mysql-tools

Naming convention

Here is a database naming convention proposal:

The database name "ttrss_rss.domain.com" is decomposed in:

• ttrss_ : hosted application code (here, Tiny Tiny RSS). Allow to group databases by application in PHPMyAdmin.
• rss.domain.com : Domain name where is hosted the application making use of the database.

The MySQL user for the database "ttrss_rss.domain.com" is "t_rss.domain.com" where:

• t_ : First letter of the application code (here t_ is the first letter of ttrss_).
• rss.domain.com : Domain name where is hosted the application making use of the database.

The user name is cut after the 17th char if the domain name is too long.

mysql-tools usage

Create the "MY_BASE" database with the "my_base" user :

# mysql-tools --create="MY_BASE"

Create the "MY_BASE" with access allowed from host1 and host2 on the network (in addition to localhost) :

# mysql-tools --host="host1" --host="host2" --create="MY_BASE"

Create a user without access rights (for monitoring use (Nagios)) :

# mysql-tools --adduser="my_user"

Delete a user :

# mysql-tools --deluser="my_user"

Drop all tables of the "MY_BASE" database :

# mysql-tools --empty="MY_BASE"

Drop a database and its dedicated user :

# mysql-tools --drop="MY_BASE"

Get help on advanced usage of the tool :

# mysql-tools --help

Thanks

• Thanks to GitHub for hosting the MySQL administration script provided by this howto.
• Thanks to everybody that posted in Drop all tables in a MySQL database (en).