Ease MySQL / MariaDB server administration with mysql-tools

mysql-tools is a shell script automating database and user management from command-line for MySQL and MariaDB. This post describe how to install it and give some usage examples.

This howto is tested on:

• Debian 10.0 Buster

Requirements

This howto recommends:

• biapy-updater, for automatic software updates.
• a MySQL or MariaDB server, as described by Install MariaDB (MySQL) on Debian.

Installation

Detect if sudo is available (“command” is used if not):

cmdProxy='command'
command type -f 'sudo' &>'/dev/null' && cmdProxy='sudo'

Environment setup

Install the required software:

${cmdProxy} apt install 'mysql-client' 'apg'

Application’s installation

Install the tool:

${cmdProxy} wget 'https://raw.githubusercontent.com/biapy/howto.biapy.com/master/mysql/mysql-tools' \
    --quiet --no-check-certificate --output-document='/usr/local/bin/mysql-tools'

Declare the downloaded file as executable:

${cmdProxy} chmod +x '/usr/local/bin/mysql-tools'

Automatic updates

Configure biapy-updater automatic updates to check for new versions of the software:

${cmdProxy} tee -a '/etc/biapy-updater.conf' <<< 'mysql-tools'

Introducing 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 with dots replaced by dashes 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 with dots replaced by dashes 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.

Usage

Create the “DATABASE_NAME” database with the “database_name” user :

command mysql-tools --create="DATABASE_NAME"

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

command mysql-tools --host="host1" --host="host2" \
    --create="DATABASE_NAME"

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

command mysql-tools --adduser="user_name"

Delete a user :

command mysql-tools --deluser="user_name"

Drop all tables and views of the “DATABASE_NAME” database :

command mysql-tools --empty="DATABASE_NAME"

Drop a database and its dedicated user :

command mysql-tools --drop="DATABASE_NAME"

Get help on advanced usage of the tool :

command mysql-tools --help

Thanks

• Thanks to everybody that posted in Drop all tables in a MySQL database (en).