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:

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


0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.