Skip to content
/ MySQL Public

Simple MySQL utilities, mostly for monitoring Aurora servers.

License

Notifications You must be signed in to change notification settings

Tinram/MySQL

Repository files navigation

MySQL

Programs

Scripts


db_copy.sh

Purpose

  • Copy a MySQL database between servers via mysqlpump (or mysqldump) and the PC, attempting to preserve character set and collations of the source database.
  • Avoids the human time / hassle of manual mysqlpump export file and reload, plus character set amnesia for migration.
  • Created to circumvent the flaky operation of mysql-utilities' mysqldbcopy (1.6.1).

Set-up

    chmod 744 db_copy.sh

Usage

    ./db_copy.sh

– prompts for credentials of the MySQL servers and source database.


mysql_vars.sh

Purpose

  • Display important MySQL server variables and counters.
  • Uses Bash for server portability.
  • Output can be easily be diff'd to compare servers.

Set-up

    chmod 744 mysql_vars.sh

Usage

    ./mysql_vars.sh

– prompts for host, username, and password (not echoed) of the MySQL server.

Usage on Windows

On Windows, via a PuTTY connection, the script output may exceed PuTTY's scrollback limit. I chose an interactive connection input for my usage on Linux-to-Linux machines (avoiding switches). Nevertheless, this means that stdout redirection is blocked.

Short of rewriting the command-line parsing, a workaround is:

    ./mysql_vars.sh | tee myvars.txt

mysql_varmon.sh

Purpose

Continously monitor MySQL server variables.

Uses Bash for server portability and updating ease (but possesses a flickery terminal refresh).
This directly led to mysqlmon.

Set-up

    chmod 744 mysql_varmon.sh

Usage

    ./mysql_varmon.sh

– prompts for host, username, and password (not echoed) of the MySQL server.

Ctrl + C to exit.


mysql_grants.sh

Purpose

List GRANTs.

Similar to pt-show-grants, with cleaner output.
Connect as root user.

Set-up

    chmod 744 mysql_grants.sh

Usage

    ./mysql_grants.sh

schema_cleaner.php

Purpose

Python and PHP scripts to minimise and clarify database schema files exported by mysqldump (-d / --no-data) for text editor viewing i.e. by removing comments and directives. (As a result, the output file will not import into MySQL.)

Usage

    python3 schema_tools/schema_cleaner.py -f <filename.sql>
    php schema_tools/schema_cleaner.php <filename.sql>

schema_summary.php

Purpose

PHP CLI script to extract a brief summary of table names and foreign keys from large unwieldy schema files.

The keyword option enables searching for keywords in table names and table contents.

Usage

    php schema_tools/schema_summary.php <filename> [keyword]

Example for querying a large schema file for user references in table names and columns:

    php schema_tools/schema_summary.php bigschema.sql user | less

schema_splitter.php

Purpose

PHP CLI script to split a large MySQL schema file by table definitions into separate .sql files.

Usage

    php schema_tools/schema_splitter.php <filename>

example:

    php schema_tools/schema_splitter.php ../schema_example/dbfilltest.sql

sys_perf_search.py

Purpose

PHP CLI script to split a large MySQL schema file by table definitions into separate .sql files.

Search Performance Schema, Sys schema, or Information Schema for keywords in table names and table fields.

Usage

Edit MySQL instance connection credentials and search keyword in the script's CONFIGURATION section.

    python3 sys_perf_search.py

table_bench.php

Purpose

  • Time row inserts into a MySQL table through configuration toggles controlling transactions, prepared statements etc.
  • Provides a timing harness for altering mysqld variables.

Timings are often highly variable, and so require much averaging. Nevertheless, large time differences are noticeable for prepared statements and my.cnf variables adjusted for INSERTs.

Usage

  1. cd table_bench

  2. Set the database connection parameters in setup.php

  3. php setup.php

  4. Set the parameters in table_bench/config.php

  5. php table_bench.php

The above PHP files can also be executed through a web server.


table_sizer.php

Purpose

PHP CLI script to query MySQL table size properties, and if timestamps are available, provide an estimation of data insertion rates.

Usage

First, add the database connection and table parameters in the configuration section at the top of the script.

    php table_sizer.php

or if made executable:

    ./table_sizer.php

License

Scripts and executables released under the GPL v.3.

About

Simple MySQL utilities, mostly for monitoring Aurora servers.

Topics

Resources

License

Stars

Watchers

Forks

Packages

No packages published