NAME
App::MysqlUtils - CLI utilities related to MySQL
VERSION
This document describes version 0.022 of App::MysqlUtils (from Perl
distribution App-MysqlUtils), released on 2022-10-19.
SYNOPSIS
This distribution includes the following CLI utilities:
* mysql-copy-rows-adjust-pk
* mysql-drop-all-tables
* mysql-drop-dbs
* mysql-drop-tables
* mysql-fill-csv-columns-from-query
* mysql-find-identical-rows
* mysql-query
* mysql-run-pl-files
* mysql-run-sql-files
* mysql-sql-dump-extract-tables
FUNCTIONS
mysql_copy_rows_adjust_pk
Usage:
mysql_copy_rows_adjust_pk(%args) -> [$status_code, $reason, $payload, \%result_meta]
Copy rows from one table to another, adjust PK column if necessary.
This utility can be used when you have rows in one table that you want
to insert to another table, but the PK might clash. When that happens,
the value of the other columns are inspected. When all the values of the
other columns match, the row is assumed to be a duplicate and skipped.
If some values of the other column differ, then the row is assumed to be
different and a new value of the PK column is chosen (there are several
choices on how to select the new PK).
An example:
% mysql-copy-rows-adjust-pk db1 --from t1 --to t2 --pk-column id --adjust "add 1000"
Suppose these are the rows in table "t1":
id date description user
-- ---- ----------- ----
1 2018-12-03 12:01:01 Created user u1 admin1
2 2018-12-03 12:44:33 Removed user u1 admin1
And here are the rows in table "t2":
id date description user
-- ---- ----------- ----
1 2018-12-03 12:01:01 Created user u1 admin1
2 2018-12-03 13:00:45 Rebooted machine1 admin1
3 2018-12-03 13:05:00 Created user u2 admin2
You can see that row id=1 in both tables are identical. This will be
skipped. On the other hand, row id=2 in "t1" is different with row id=2
in "t2". This row will be adjusted: "id" will be changed to 2+1000=1002.
So the final rows in table "t2" will be (sorted by date):
id date description user
-- ---- ----------- ----
1 2018-12-03 12:01:01 Created user u1 admin1
1002 2018-12-03 12:44:33 Removed user u1 admin1
2 2018-12-03 13:00:45 Rebooted machine1 admin1
3 2018-12-03 13:05:00 Created user u2 admin2
So basically this utility is similar to MySQL's INSERT ... ON DUPLICATE
KEY UPDATE, but will avoid inserting identical rows.
If the adjusted PK column clashes with another row in the target table,
the row is skipped.
This function is not exported.
This function supports dry-run operation.
Arguments ('*' denotes required arguments):
* adjust* => *str*
How to adjust the value of the PK column.
Currently the choices are:
* "add N" add N to the original value.
* "subtract N" subtract N from the original value.
* database* => *str*
(No description)
* from* => *str*
Name of source table.
* host => *str* (default: "localhost")
(No description)
* password => *str*
Will try to get default from "~/.my.cnf".
* pk_column* => *str*
Name of PK column.
* port => *int* (default: 3306)
(No description)
* to* => *str*
Name of target table.
* username => *str*
Will try to get default from "~/.my.cnf".
Special arguments:
* -dry_run => *bool*
Pass -dry_run=>1 to enable simulation mode.
Returns an enveloped result (an array).
First element ($status_code) is an integer containing HTTP-like status
code (200 means OK, 4xx caller error, 5xx function error). Second
element ($reason) is a string containing error message, or something
like "OK" if status is 200. Third element ($payload) is the actual
result, but usually not present when enveloped result is an error
response ($status_code is not 2xx). Fourth element (%result_meta) is
called result metadata and is optional, a hash that contains extra
information, much like how HTTP response headers provide additional
metadata.
Return value: (any)
mysql_drop_all_tables
Usage:
mysql_drop_all_tables(%args) -> [$status_code, $reason, $payload, \%result_meta]
Drop all tables in a MySQL database.
For safety, the default is dry-run mode. To actually drop the tables,
you must supply "--no-dry-run" or DRY_RUN=0.
This function is not exported.
This function supports dry-run operation.
Arguments ('*' denotes required arguments):
* database* => *str*
(No description)
* host => *str* (default: "localhost")
(No description)
* password => *str*
Will try to get default from "~/.my.cnf".
* port => *int* (default: 3306)
(No description)
* username => *str*
Will try to get default from "~/.my.cnf".
Special arguments:
* -dry_run => *bool*
Pass -dry_run=>1 to enable simulation mode.
Returns an enveloped result (an array).
First element ($status_code) is an integer containing HTTP-like status
code (200 means OK, 4xx caller error, 5xx function error). Second
element ($reason) is a string containing error message, or something
like "OK" if status is 200. Third element ($payload) is the actual
result, but usually not present when enveloped result is an error
response ($status_code is not 2xx). Fourth element (%result_meta) is
called result metadata and is optional, a hash that contains extra
information, much like how HTTP response headers provide additional
metadata.
Return value: (any)
mysql_drop_dbs
Usage:
mysql_drop_dbs(%args) -> [$status_code, $reason, $payload, \%result_meta]
Drop MySQL databases.
For safety, the default is dry-run mode. To actually drop the databases,
you must supply "--no-dry-run" or DRY_RUN=0.
Examples:
# Drop dbs D1, D2, D3 (dry-run mode)
% mysql-drop-dbs D1 D2 D3
# Drop all dbs with names matching /^testdb/ (dry-run mode)
% mysql-drop-dbs --db-pattern ^testdb
# Actually drop all dbs with names matching /^testdb/, don't delete more than 5 dbs
% mysql-drop-dbs --db-pattern ^testdb --limit 5 --no-dry-run
This function is not exported.
This function supports dry-run operation.
Arguments ('*' denotes required arguments):
* db_pattern => *re*
(No description)
* dbs => *array[str]*
(No description)
* host => *str* (default: "localhost")
(No description)
* limit => *posint*
Don't delete more than this number of databases.
* password => *str*
Will try to get default from "~/.my.cnf".
* port => *int* (default: 3306)
(No description)
* username => *str*
Will try to get default from "~/.my.cnf".
Special arguments:
* -dry_run => *bool*
Pass -dry_run=>1 to enable simulation mode.
Returns an enveloped result (an array).
First element ($status_code) is an integer containing HTTP-like status
code (200 means OK, 4xx caller error, 5xx function error). Second
element ($reason) is a string containing error message, or something
like "OK" if status is 200. Third element ($payload) is the actual
result, but usually not present when enveloped result is an error
response ($status_code is not 2xx). Fourth element (%result_meta) is
called result metadata and is optional, a hash that contains extra
information, much like how HTTP response headers provide additional
metadata.
Return value: (any)
mysql_drop_tables
Usage:
mysql_drop_tables(%args) -> [$status_code, $reason, $payload, \%result_meta]
Drop tables in a MySQL database.
For safety, the default is dry-run mode. To actually drop the tables,
you must supply "--no-dry-run" or DRY_RUN=0.
Examples:
# Drop table T1, T2, T3 (dry-run mode)
% mysql-drop-tables DB T1 T2 T3
# Drop all tables with names matching /foo/ (dry-run mode)
% mysql-drop-tables DB --table-pattern foo
# Actually drop all tables with names matching /foo/, don't delete more than 5 tables
% mysql-drop-tables DB --table-pattern foo --limit 5 --no-dry-run
This function is not exported.
This function supports dry-run operation.
Arguments ('*' denotes required arguments):
* database* => *str*
(No description)
* host => *str* (default: "localhost")
(No description)
* limit => *posint*
Don't delete more than this number of tables.
* password => *str*
Will try to get default from "~/.my.cnf".
* port => *int* (default: 3306)
(No description)
* table_pattern => *re*
(No description)
* tables => *array[str]*
(No description)
* username => *str*
Will try to get default from "~/.my.cnf".
Special arguments:
* -dry_run => *bool*
Pass -dry_run=>1 to enable simulation mode.
Returns an enveloped result (an array).
First element ($status_code) is an integer containing HTTP-like status
code (200 means OK, 4xx caller error, 5xx function error). Second
element ($reason) is a string containing error message, or something
like "OK" if status is 200. Third element ($payload) is the actual
result, but usually not present when enveloped result is an error
response ($status_code is not 2xx). Fourth element (%result_meta) is
called result metadata and is optional, a hash that contains extra
information, much like how HTTP response headers provide additional
metadata.
Return value: (any)
mysql_fill_csv_columns_from_query
Usage:
mysql_fill_csv_columns_from_query(%args) -> [$status_code, $reason, $payload, \%result_meta]
Fill CSV columns with data from a query.
This utility is handy if you have a partially filled table (in CSV
format, which you can convert from spreadsheet or Google Sheet or
whatever), where you have some unique key already specified in the table
(e.g. customer_id) and you want to fill up other columns (e.g.
customer_name, customer_email, last_order_date) from a query:
% mysql-fill-csv-columns-from-query DBNAME TABLE.csv 'SELECT c.NAME customer_name, c.email customer_email, (SELECT date FROM tblorders WHERE client_id=$customer_id ORDER BY date DESC LIMIT 1) last_order_time FROM tblclients WHERE id=$customer_id'
The $NAME in the query will be replaced by actual CSV column value.
SELECT fields must correspond to the CSV column names. For each row, a
new query will be executed and the first result row is used.
This function is not exported.
This function supports dry-run operation.
Arguments ('*' denotes required arguments):
* count => *bool*
Instead of returning the CSV rows, just return the count of rows
that get filled.
* database* => *str*
(No description)
* escape_char => *str*
Specify character to escape value in field in input CSV, will be
passed to Text::CSV_XS.
Defaults to "\\" (backslash). Overrides "--tsv" option.
* filename* => *filename*
Input CSV file.
* header => *bool* (default: 1)
Whether input CSV has a header row.
By default ("--header"), the first row of the CSV will be assumed to
contain field names (and the second row contains the first data
row). When you declare that CSV does not have header row
("--no-header"), the first row of the CSV is assumed to contain the
first data row. Fields will be named "field1", "field2", and so on.
* host => *str* (default: "localhost")
(No description)
* password => *str*
Will try to get default from "~/.my.cnf".
* port => *int* (default: 3306)
(No description)
* query* => *str*
(No description)
* quote_char => *str*
Specify field quote character in input CSV, will be passed to
Text::CSV_XS.
Defaults to """ (double quote). Overrides "--tsv" option.
* sep_char => *str*
Specify field separator character in input CSV, will be passed to
Text::CSV_XS.
Defaults to "," (comma). Overrides "--tsv" option.
* tsv => *bool*
Inform that input file is in TSV (tab-separated) format instead of
CSV.
Overriden by "--sep-char", "--quote-char", "--escape-char" options.
If one of those options is specified, then "--tsv" will be ignored.
* username => *str*
Will try to get default from "~/.my.cnf".
Special arguments:
* -dry_run => *bool*
Pass -dry_run=>1 to enable simulation mode.
Returns an enveloped result (an array).
First element ($status_code) is an integer containing HTTP-like status
code (200 means OK, 4xx caller error, 5xx function error). Second
element ($reason) is a string containing error message, or something
like "OK" if status is 200. Third element ($payload) is the actual
result, but usually not present when enveloped result is an error
response ($status_code is not 2xx). Fourth element (%result_meta) is
called result metadata and is optional, a hash that contains extra
information, much like how HTTP response headers provide additional
metadata.
Return value: (any)
mysql_find_identical_rows
Usage:
mysql_find_identical_rows(%args) -> [$status_code, $reason, $payload, \%result_meta]
List rows on one table that are identical on another table.
This function is not exported.
Arguments ('*' denotes required arguments):
* database* => *str*
(No description)
* exclude_columns => *array[str]*
What column(s) to exclude from comparison.
* host => *str* (default: "localhost")
(No description)
* password => *str*
Will try to get default from "~/.my.cnf".
* port => *int* (default: 3306)
(No description)
* return_column* => *str*
What column to return.
* t1* => *str*
Name of the first table.
* t2* => *str*
Name of the second table.
* username => *str*
Will try to get default from "~/.my.cnf".
Returns an enveloped result (an array).
First element ($status_code) is an integer containing HTTP-like status
code (200 means OK, 4xx caller error, 5xx function error). Second
element ($reason) is a string containing error message, or something
like "OK" if status is 200. Third element ($payload) is the actual
result, but usually not present when enveloped result is an error
response ($status_code is not 2xx). Fourth element (%result_meta) is
called result metadata and is optional, a hash that contains extra
information, much like how HTTP response headers provide additional
metadata.
Return value: (any)
mysql_query
Usage:
mysql_query(%args) -> [$status_code, $reason, $payload, \%result_meta]
Run query and return table result.
This is like just regular querying, but the result will be returned as
table data (formattable using different backends). Or, you can output as
JSON.
Examples:
# by default, show as pretty text table, like in interactive mysql client
% mysql-query DBNAME "SELECT * FROM t1"
# show as JSON (array of hashes)
% mysql-query DBNAME "QUERY..." --json ;# or, --format json
# show as CSV
% mysql-query DBNAME "QUERY..." --format csv
# show as CSV table using Text::Table::CSV
% FORMAT_PRETTY_TABLE_BACKEND=Text::Table::Org mysql-query DBNAME "QUERY..."
This function is not exported.
Arguments ('*' denotes required arguments):
* add_row_numbers => *bool*
Add first field containing number from 1, 2, ...
* database* => *str*
(No description)
* host => *str* (default: "localhost")
(No description)
* password => *str*
Will try to get default from "~/.my.cnf".
* port => *int* (default: 3306)
(No description)
* query* => *str*
(No description)
* username => *str*
Will try to get default from "~/.my.cnf".
Returns an enveloped result (an array).
First element ($status_code) is an integer containing HTTP-like status
code (200 means OK, 4xx caller error, 5xx function error). Second
element ($reason) is a string containing error message, or something
like "OK" if status is 200. Third element ($payload) is the actual
result, but usually not present when enveloped result is an error
response ($status_code is not 2xx). Fourth element (%result_meta) is
called result metadata and is optional, a hash that contains extra
information, much like how HTTP response headers provide additional
metadata.
Return value: (any)
mysql_run_pl_files
Usage:
mysql_run_pl_files(%args) -> [$status_code, $reason, $payload, \%result_meta]
Run each .pl file, feed the output to `mysql` command and write result
to .txt file.
The ".pl" file is supposed to produce a SQL statement. For simpler
cases, use mysql-run-sql-files.
This function is not exported.
Arguments ('*' denotes required arguments):
* database* => *str*
(No description)
* directory => *dirname* (default: ".")
Specify directory for the resulting *.txt files.
* mkdir => *true* (default: 1)
Create output directory if not exists.
* overwrite_when => *str* (default: "none")
Specify when to overwrite existing .txt file.
"none" means to never overwrite existing .txt file. "older"
overwrites existing .txt file if it's older than the corresponding
.sql file. "always" means to always overwrite existing .txt file.
* pl_files* => *array[filename]*
(No description)
Returns an enveloped result (an array).
First element ($status_code) is an integer containing HTTP-like status
code (200 means OK, 4xx caller error, 5xx function error). Second
element ($reason) is a string containing error message, or something
like "OK" if status is 200. Third element ($payload) is the actual
result, but usually not present when enveloped result is an error
response ($status_code is not 2xx). Fourth element (%result_meta) is
called result metadata and is optional, a hash that contains extra
information, much like how HTTP response headers provide additional
metadata.
Return value: (any)
mysql_run_sql_files
Usage:
mysql_run_sql_files(%args) -> [$status_code, $reason, $payload, \%result_meta]
Feed each .sql file to `mysql` command and write result to .txt file.
This function is not exported.
Arguments ('*' denotes required arguments):
* database* => *str*
(No description)
* directory => *dirname* (default: ".")
Specify directory for the resulting *.txt files.
* mkdir => *true* (default: 1)
Create output directory if not exists.
* overwrite_when => *str* (default: "none")
Specify when to overwrite existing .txt file.
"none" means to never overwrite existing .txt file. "older"
overwrites existing .txt file if it's older than the corresponding
.sql file. "always" means to always overwrite existing .txt file.
* sql_files* => *array[filename]*
(No description)
Returns an enveloped result (an array).
First element ($status_code) is an integer containing HTTP-like status
code (200 means OK, 4xx caller error, 5xx function error). Second
element ($reason) is a string containing error message, or something
like "OK" if status is 200. Third element ($payload) is the actual
result, but usually not present when enveloped result is an error
response ($status_code is not 2xx). Fourth element (%result_meta) is
called result metadata and is optional, a hash that contains extra
information, much like how HTTP response headers provide additional
metadata.
Return value: (any)
mysql_sql_dump_extract_tables
Usage:
mysql_sql_dump_extract_tables(%args) -> [$status_code, $reason, $payload, \%result_meta]
Parse SQL dump and spit out tables to separate files.
This function is not exported.
Arguments ('*' denotes required arguments):
* dir => *dirname*
Directory to put the SQL files into.
* exclude_table_patterns => *array[re]*
(No description)
* exclude_tables => *array[str]*
(No description)
* include_table_patterns => *array[re]*
(No description)
* include_tables => *array[str]*
(No description)
* overwrite => *bool*
(No description)
* stop_after_table => *str*
(No description)
* stop_after_table_pattern => *re*
(No description)
Returns an enveloped result (an array).
First element ($status_code) is an integer containing HTTP-like status
code (200 means OK, 4xx caller error, 5xx function error). Second
element ($reason) is a string containing error message, or something
like "OK" if status is 200. Third element ($payload) is the actual
result, but usually not present when enveloped result is an error
response ($status_code is not 2xx). Fourth element (%result_meta) is
called result metadata and is optional, a hash that contains extra
information, much like how HTTP response headers provide additional
metadata.
Return value: (any)
HOMEPAGE
Please visit the project's homepage at
<https://metacpan.org/release/App-MysqlUtils>.
SOURCE
Source repository is at
<https://github.com/perlancar/perl-App-MysqlUtils>.
SEE ALSO
AUTHOR
perlancar <perlancar@cpan.org>
CONTRIBUTING
To contribute, you can send patches by email/via RT, or send pull
requests on GitHub.
Most of the time, you don't need to build the distribution yourself. You
can simply modify the code, then test via:
% prove -l
If you want to build the distribution (e.g. to try to install it locally
on your system), you can install Dist::Zilla,
Dist::Zilla::PluginBundle::Author::PERLANCAR,
Pod::Weaver::PluginBundle::Author::PERLANCAR, and sometimes one or two
other Dist::Zilla- and/or Pod::Weaver plugins. Any additional steps
required beyond that are considered a bug and can be reported to me.
COPYRIGHT AND LICENSE
This software is copyright (c) 2022, 2020, 2019, 2018, 2017, 2016 by
perlancar <perlancar@cpan.org>.
This is free software; you can redistribute it and/or modify it under
the same terms as the Perl 5 programming language system itself.
BUGS
Please report any bugs or feature requests on the bugtracker website
<https://rt.cpan.org/Public/Dist/Display.html?Name=App-MysqlUtils>
When submitting a bug or request, please include a test-file or a patch
to an existing test-file that illustrates the bug or desired feature.