Ease MySQL server administration with mysql-tools
mysql-tools is a script written with the intent to ease and automate the daily administration tasks of a MySQL server. This howto describe howto to install and use it.
This howto is tested on :
- Debian 6.0 Squeeze
- Debian 7.0 Wheezy
- Ubuntu 10.04 Lucid Lynx
- Ubuntu 10.10 Natty Narwal
Prerequisites
This howto recommends:
- a MySQL server, as described by 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/local/bin/mysql-tools" command chmod +x "/usr/local/bin/mysql-tools"
Note : This tool is improved on a regular basis.
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 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 "DATABASE_NAME" database with the "database_name" user :
# mysql-tools --create="DATABASE_NAME"
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="DATABASE_NAME"
Create a user without access rights (for monitoring use (Nagios)) :
# mysql-tools --adduser="user_name"
Delete a user :
# mysql-tools --deluser="user_name"
Drop all tables and views of the "DATABASE_NAME" database :
# mysql-tools --empty="DATABASE_NAME"
Drop a database and its dedicated user :
# mysql-tools --drop="DATABASE_NAME"
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).