MySQL mysqlrpladmin utility commands

mysqlrpladmin utility commands in MySQL

mysqlrpladmin is administration utility for MySQL replication

MySQL utility mysqlrpladmin used to perform administrative actions on a database replication topology consisting of a master and its slaves. And used to recover from planned maintenance of the master or unplanned shutdown of master.

mysqlrpladmin --help
mysqlrpladmin --version

Usage: mysqlrpladmin --slaves=root@localhost:3306 command

mysqlrpladmin --master=dbhost1 --discover-slaves-login=dba health;
mysqlrpladmin --master=dbhost2 --discover-slaves-login=root:password health;
mysqlrpladmin --master=dbhost1 --slaves=slave1,dbhost2,myhost1 elect
mysqlrpladmin --master=dbhost1 --discover-slaves-login=root elect
mysqlrpladmin --master=mysqldb1 --slaves=slave1,mysqldb2 stop
mysqlrpladmin --master=dbhost2 --slaves=myhost1,dbhost1 reset

mysqlrpladmin --slaves=slave1,dbhost2,myhost1 --candidates=slave1,dbhost2,myhost1 failover
mysqlrpladmin --force --slaves=slave1,dbhost2,myhost1 --candidates=slave1,dbhost2,myhost1 failover
mysqlrpladmin --master=slave1 --demote-master --new-master=dbhost1 --discover-slaves-login=dba --rpl-user=dbhost1 --verbose --force switchover 
mysqlrpladmin --master=dbhost2 --demote-master --new-master=dbhost1 --discover-slaves-login=dba --rpl-user=dbhost1 --exec-after=/root/cron_scripts/post_failover.sh --verbose switchover

mysqlrpladmin --master=dbhost1 --discover-slaves-login=dba health; mysqlrpladmin --master=dbhost2 --discover-slaves-login=dba health;
mysqlrpladmin --master=root@localhost:13002 --new-master=root@localhost:13001 --discover-slaves-login=root --demote-master --rpl-user=repl:repl --quiet switchover
mysqlrpladmin --master=myhost1 --slaves=myhost2,myhost3 --new-master=myhost2 --demote-master --log=rpl_switchover.log switchover
mysqlrpladmin --master=myhost2 --slaves=myhost1,myhost3 --new-master=myhost3 --exec-before=./make-master-readonly-slave-my2.sh --exec-after=./make-slave-readwrite-master-my3.sh --log=rpl2.log switchover

---

mysql rpl admin options:
  --version              show program's version number and exit
  --help                display this help message and exit
  --license              display program's license and exit

  --candidates=CANDIDATES   connection information for candidate slave servers for failover in the form:
user[:password]@host[:port][:socket] or login-path[:port][:socket] or config- path[[group]] Valid only with failover command. List multiple slaves in comma-separated list.
  --discover-slaves-login=DISCOVER   at startup, query master for all registered slaves and use the user name and password specified to connect. Supply the user and password in the form user[:password] or login-path.
For example, --discover-slaves-login=joe:secret will use 'joe' as the user and 'secret' as the password for each discovered slave.

  --exec-after=EXEC_AFTER name of script to execute after failover or switchover
  --exec-before=EXEC_BEFORE name of script to execute before failover or switchover
  --log=LOG_FILE        specify a log file to use for logging messages
  --log-age=LOG_AGE      specify maximum age of log entries in days. Entries older than this will be purged on startup. Default = 7 days.
  --master=MASTER        connection information for master server in the form: user[:password]@host[:port][:socket] or login-path[:port][:socket] or config- path[[group]].
  --max-position=MAX_POSITION used to detect slave delay. The maximum difference between the master's log position and the slave's reported read position of the master. A value greater than this means the slave is too far behind the master. Default is 0.

  --ping=PING            Number of ping attempts for detecting downed server.
  --seconds-behind=MAX_DELAY used to detect slave delay. The maximum number of seconds behind the master permitted before slave is considered behind the master. Default is 0.
  --slaves=SLAVES        connection information for slave servers in the form: user[:password]@host[:port][:socket] or login-path[:port][:socket] or config- path[[group]]. List multiple slaves in comma- separated list.
  --timeout=TIMEOUT     maximum timeout in seconds to wait for each replication command to complete. For example, timeout for slave waiting to catch up to master. Default = 300.

  --script-threshold=SCRIPT_THRESHOLD Value for external scripts to trigger aborting the operation if result is greater than or equal to the threshold. Default = None (no threshold checking).
  --new-master=NEW_MASTER connection information for the slave to be used to replace the master for switchover, in the form:  user[:password]@host[:port][:socket] or login-path[:port][:socket] or config- path[[group]]. Valid only with switchover command.
  --force              ignore prerequisite check results or some inconsistencies found (e.g. errant transactions on slaves) and execute action
  -f FORMAT, --format=FORMAT display the output in either grid (default), tab, csv, or vertical format

  --demote-master      make master a slave after switchover.
  --no-health            turn off health report after switchover or failover.
  -v, --verbose          control how much information is displayed. e.g., -v = verbose, -vv = more verbose, -vvv = debug
  -q, --quiet            turn off all messages for quiet execution.
  --rpl-user=RPL_USER  the user and password for the replication user requirement, in the form: user[:password] or login-path. E.g. rpl:passwd

  --ssl-ca=SSL_CA        path to a file that contains a list of trusted SSL  CAs.
  --ssl-cert=SSL_CERT   name of the SSL certificate file to use for establishing a secure connection.
  --ssl-key=SSL_KEY     name of the SSL key file to use for establishing a secure connection.
  --ssl=SSL              specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. By default 0 (SSL not required).

Available Commands:

•   elect        - perform best slave election and report best slave
•   failover    - conduct failover from master to best slave
•   gtid        - show status of global transaction id variables  also displays uuids for all servers
•   health      - display the replication health
•   reset        - stop and reset all slaves
•   start        - start all slaves
•   stop        - stop all slaves
•   switchover  - perform slave promotion

  Note:
   elect, gtid and health require --master and either --slaves or --discover-slaves-login;
   failover requires --slaves;
   switchover requires --master, --new-master and either --slaves or --discover-slaves-login;
   start, stop and reset require --slaves (and --master is optional)

Related MySQL Articles: mysqlbinlog utility    MySQL mysqlreplicate cheatsheet

MySQL mysqldbimport commands

mysqldbimport utility examples in MySQL

mysqldbimport     import metadata and data from files     
Usage: mysqldbimport --server=user:pass@host:port:socket db1.csv db2.sql db3.grid

mysqldbimport utility, in MySQL, imports metadata (object definitions) and/or data, from one or more files, for one or more databases with or without GITDs enabled.

mysqldbimport --version
mysqldbimport --help

mysqldbimport --server=root@127.0.0.1:13000 --import=both --bulk-insert world_export.sql
mysqldbimport --server=root@127.0.0.1:13000 --import=both --bulk-insert employees_export.sql employees_copy_export.sql --multiprocess=2
mysqldbimport --server=dba --import=both --bulk-insert world_export.sql --verbose --max-bulk-insert=50000

mysqldbimport --import=definitions --server=root@localhost --format=csv data.csv
mysqldbimport data.sql --import=both --bulk-insert --format=sql --server=root@localhost
mysqldbimport exp_data.sql --import=data --bulk-insert --format=sql --server=dba_login -vvv --skip-rpl --skip-blobs
mysqldbimport exp_data.sql --import=both --drop-first --bulk-insert --format=sql --server=dba_login --dryrun

mysqldbimport Options:
  --version             show program's version number and exit
  --help                 display a help message and exit
  --license             display program's license and exit
  --server=SERVER       connection information for the server in the form: user[:password]@host[:port][:socket] or login-path[:port][:socket] or config- path[[group]].

  --ssl-ca=SSL_CA        path to a file that contains a list of trusted SSL  CAs.
  --ssl-cert=SSL_CERT  name of the SSL certificate file to use for establishing a secure connection.
  --ssl-key=SSL_KEY     name of the SSL key file to use for establishing a secure connection.
  --ssl=SSL             specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. By default 0 (SSL not required).

  --character-set=CHARSET   sets the client character set. The default is retrieved from the server variable 'character_set_client'.
  -f FORMAT, --format=FORMAT   the input file format in either sql (default), grid, tab, csv, raw_csv or vertical format
  -i IMPORT_TYPE, --import=IMPORT_TYPE   control the import of either 'data' = only the table data for the tables in the database list, 'definitions' = import only the definitions for the objects in the database list, or 'both' = import the metadata followed by the data (default: import definitions)
  -d, --drop-first       drop database before importing.
  -b, --bulk-insert     use bulk insert statements for data (default:False)
  -h, --no-headers     files do not contain column headers (only applies to formats: tab, csv).
  --dryrun              import the files and generate the statements but do not execute them - useful for testing file validity
  --table=TABLE     destination table in the form: db.table.

  --skip-blobs         do not import blob data.
  --skip-rpl             do not execute replication commands.
  --skip-gtid           do not execute the GTID_PURGED statements.
  --skip=SKIP_OBJECTS   specify objects to skip in the operation in the form of a comma-separated list (no spaces). Valid values = tables, views, triggers, procedures, functions, events, grants, data, create_db
  -v, --verbose        control how much information is displayed. e.g., -v = verbose, -vv = more verbose, -vvv = debug
  -q, --quiet            turn off all messages for quiet execution.

  --new-storage-engine=NEW_ENGINE   change all tables to use this storage engine if storage engine exists on the destination.
  --default-storage-engine=DEF_ENGINE   change all tables to use this storage engine if the original storage engine does not exist on the destination.
  --multiprocess=MULTIPROCESS   use multiprocessing, number of processes to use for concurrent execution. Special values: 0 (number of processes equal to the CPUs detected) and 1 (default - no concurrency).
  --autocommit          use autocommit, by default autocommit is off and transactions are only committed once at the end of each imported file.
  --max-bulk-insert=MAX_BULK_INSERT  maximum bulk insert size, by default 30000.


Related MySQL Articles: mysqld_multi utility examples  mysqlserverinfo commands list

mysqldbexport utility in MySQL

MySQL mysqldbexport commands cheatsheet

mysqldbexport     export metadata and data from databases   
Usage: mysqldbexport --server=user:pass@host:port:socket db1, db2, db3

MySQL mysqldbexport utility exports metadata (object definitions) and/or data from one or more databases with/without GITDs enabled.

mysqldbexport --help
mysqldbexport --version

mysqldbexport --server=root@server1:3310 --format=csv db1 --export=data
mysqldbexport --server=root:pass@localhost --skip=GRANTS --export=DEFINITIONS util_test
mysqldbexport --server=root:pass@localhost --export=DATA --bulk-insert util_test
mysqldbexport --server=host22 --export=DATA --bulk-insert util_test --locking=lock-all

mysqldbexport --server=root@localhost:3311 util_test --export=both --rpl-user=rpl:rpl --rpl=master -v
mysqldbexport --server=root@localhost:3310 util_test --export=both --rpl-user=rpl:rpl --rpl=slave --verbose
mysqldbexport --server=root@127.0.0.1:13000 --export=both employees
mysqldbexport --server=source_host --export=both employees  employees_export.sql

mysqldbexport --server=root@127.0.0.1:13000 --export=both employees --output-file=employees_export.sql --multiprocess=2
mysqldbexport --server=username:password@localhost --export=DATA --bulk-insert testSchema --output-file=D:/tmp/dump.sql

$ mysqldbexport --server=root:password@127.0.0.1 --export=data --locking=snapshot --character-set=utf8 --file-per-table --verbose --output-file=/tmp/myexport.log --exclude="sandbox.get_tmp" sandbox
$ mysqldbexport --server=root:password@127.0.0.1 --export=data --locking=snapshot --character-set=utf8 --file-per-table --verbose --output-file=/tmp/myexport.log --exclude=".*_(tmp|box).*" sandbox
mysqldbexport --exclude="batch%" --server=myhost:3306 --export=data --file-per-table --format=csv --no-headers --locking=snapshot mydb 
mysqldbexport --skip=GRANTS --server=src_host --export=DEFINITIONS util_test

mysql db export Options:
  --version             show program's version number and exit
  --help                 display a help message and exit
  --license             display program's license and exit

  --server=SERVER       connection information for the server in the form: user[:password]@host[:port][:socket] or login-path[:port][:socket].
  --character-set=CHARSET   sets the client character set. The default is retrieved from the server variable 'character_set_client'.
  -f FORMAT, --format=FORMAT   display the output in either sql (default), grid, tab, csv, or vertical format
  -d DISPLAY, --display=DISPLAY   control the number of columns shown: 'brief' = minimal columns for object creation (default), 'full' = all columns, 'names' = only object names (not valid for --format=sql)

  -e EXPORT, --export=EXPORT   control the export of either 'data' = only the table data for the tables in the database list, 'definitions' = export only the definitions for the objects in the database list, or 'both' = export the metadata followed by the data (default: export definitions)
  -b, --bulk-insert      use bulk insert statements for data (default:False)
  -h, --no-headers     do not show column headers (only applies to formats:  tab, csv).
  --skip-blobs           do not export blob data.
  --file-per-table       write table data to separate files. Valid only for --export=data or --export=both.

  -x EXCLUDE, --exclude=EXCLUDE   exclude one or more objects from the operation using either a specific name (e.g. db1.t1), a LIKE pattern (e.g. db1.t% or db%.%) or a REGEXP search pattern. To use a REGEXP search pattern for all exclusions, you must also specify the --regexp option. Repeat the --exclude option for multiple exclusions.
  -a, --all             include all databases
  --skip=SKIP_OBJECTS   specify objects to skip in the operation in the form of a comma-separated list (no spaces). Valid values = tables, views, triggers, procedures, functions, events, grants, data, create_db

  -v, --verbose         control how much information is displayed. e.g., -v =  verbose, -vv = more verbose, -vvv = debug
  -q, --quiet             turn off all messages for quiet execution.
  -G, --basic-regexp, --regexp  use 'REGEXP' operator to match pattern. Default is to use 'LIKE'.
  --locking=LOCKING     choose the lock type for the operation: no-locks = do not use any table locks, lock-all = use table locks but no transaction and no consistent read, snapshot (default): consistent read using a single transaction.
  --rpl-user=RPL_USER   the user and password for the replication user requirement, in the form: user[:password] or login-path. E.g. rpl:passwd
  --rpl=RPL_MODE, --replication=RPL_MODE   include replication information. Choices: 'master' = include the CHANGE MASTER command using the source server as the master, 'slave' = include the CHANGE MASTER command for the source server's master (only works if the source server is a slave), and 'both' = include 'master' and 'slave' options where applicable.
  --rpl-file=RPL_FILE, --replication-file=RPL_FILE   path and file name to place the replication information generated. Valid on if the --rpl option is specified.

  --skip-gtid               skip creation of GTID_PURGED statements.
  --comment-rpl         place the replication statements in comment statements. Valid only with --rpl option.
  --skip-fkey-checks    skip creation of foreign key disable/enable statements.
  --multiprocess=MULTIPROCESS   use multiprocessing, number of processes to use for concurrent execution. Special values: 0 (number of processes equal to the CPUs detected) and 1 (default - no concurrency).
  --output-file=OUTPUT_FILE   path and file name to store the generated output, by default the standard output (no file).

Related MySQL Articles: mysqlimport commands  mysqldbimport utility examples

MySQL utility - mysql_secure_installation commands

mysql_secure_installation utility in MySQL

mysql_secure_installation - Improve MySQL/MariaDB Installation Security

mysql_secure_installation

#/usr/bin/mysql_secure_installation

mysql_secure_installation --host=::1 --port=3309


Related MySQL Articles: 

mysqlgrants usage examples

MySQL utility mysqlgrants usage

mysqlgrants - display grants per object
Usage: mysqlgrants --server=user:pass@host:port  [db_name[.obj_name]]

mysqlgrants utility allows DBAs to see which users have what level of access for each MySQL database objects like databases, tables, functions, and procedures. 

mysqlgrants --help
mysqlgrants --version

mysqlgrants --server=user:pass@localhost:3311 --show=user_grants testdb test.t3 test.t2 test.t1 test.p1 test.f1
mysqlgrants --server=user:pass@localhost:3312 --show=raw devdb test.t2 test.t1 test.p1 test.f1
mysqlgrants --server=mysql_admin --show=raw --privileges=ALL staging test.t3 test.t2 test.p1 test.f1

mysqlgrants --server=user:pass@localhost:3313 --show=users --privileges=SELECT,INSERT,EXECUTE prod test.t3 test.t1 test.p1 test.f1
mysqlgrants --server=master db1.* --inherit-level=object --show raw
mysqlgrants --server=local_dba db1.* --inherit-level=database --show-raw

mysqlgrants Options:
  --version             show program's version number and exit
  --help                 display a help message and exit
  --license             display program's license and exit
  --server=SERVER       connection information for the server in the form:
                        user[:password]@host[:port][:socket] or login-path[:port][:socket] or config-path[[group]].

  --ssl-ca=SSL_CA       path to a file that contains a list of trusted SSL  CAs.
  --ssl-cert=SSL_CERT   name of the SSL certificate file to use for  establishing a secure connection.
  --ssl-key=SSL_KEY     name of the SSL key file to use for establishing a  secure connection.
  --ssl=SSL             specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. By default 0 (SSL not required).

  -v, --verbose         control how much information is displayed. e.g., -v =  verbose, -vv = more verbose, -vvv = debug
  --show=SHOW_MODE      controls the content of the report. If the value USERS is specified, the report shows only the list of users  with any kind of grant over the object. If USER_GRANTS  is specified the reports shows each user along with her list of privileges for each object. Finally,  specifying RAW the utility returns each user along with the list of SQL grant statements that have influence over the specific object. Default is USER_GRANTS.
  --privileges=PRIVILEGES  minimum set of privileges that a user must have for any given object. Unless a user has all the privileges listed for a specific object, she will not appear in the list of users with privileges for that specific object. To list multiple privileges, use a comma-separated list.

  --inherit-level=INHERIT_LEVEL specify the level of inheritance that should be taken  into account. If OBJECT is specified, global level and database level grants are not inherited by objects. If  DATABASE level is specified global level grants are  not inherited by databases and objects inside those  databases. Finally, if GLOBAL level is specified, normal inheritance rules are applied, global grants apply to both databases and objects and database level grants apply to the objects.

Introduction
------------
The mysqlgrants utility is designed to display the users who have access to a list of objects and/or databases. It can also display the privileges grouped by user and the raw GRANT statements.

Furthermore, if the user specifies a list of privileges, the utility shall display those users who have all of the privileges listed (they are AND conditions).

In order to use the utility, you need to specify at least one object to check.
To specify several objects at once, you should list each object as a separate argument for the utility, using full qualified names as shown by the following examples:

  # Get the list of users with their respective privileges for the 'db1' database and 'db1'.'table1' table.

  $ mysqlgrants --server=root:pass@host1:3306 --show=user_grants db1 db1.table1

  # Get the list of users with both SELECT and UPDATE privileges on the 'db1' database and 'db1'.'table1' table.

  $ mysqlgrants --server=root:pass@host1:3306 --show=users --privileges=SELECT,UPDATE db1 db1.table1

  # Get the list of users that have at least the TRIGGER and DROP privileges
  # for database 'db1' and 'db1'.'table1' table and show the list of SQL GRANT statements that grant them those privileges.

  $ mysqlgrants --server=root:pass@host1:3306 --show=raw --privileges=TRIGGER,DROP db1 db1.table1

  # Get the list of users with specific privileges at the object level, for all the objects of the database 'db1'.

  $ mysqlgrants --server=root:pass@host1:3306 --inherit-level=object db1.*

Helpful Hints
-------------
  - To use the --show=users option you need to specify at least one privilege using the --privilege option.
  - You can list the users that have specific privileges using the option --privileges. The user must have all privileges listed in order to be included in the result.
  - If you specify some privileges on the --privileges option that are not valid for all the specified objects,  any that do not apply are not included in the list. For example, the SELECT privilege will be ignored for stored routines and the EXECUTE privilege will be ignored for  tables but both will be taken into account for databases.
  - The --inherit-level option can be used for filtering out global users, and also users with the same database level privileges at the object level.

Related MySQL Articles: 

mysqlslavetrx MySQL utility

MySQL utility mysqlslavetrx cheatsheet

mysqlslavetrx - skip transactions on slaves

Usage: mysqlslavetrx --gtid-set=gtid_set --slaves=user:pass@host:port

mysqlslavetrx utility used to skip multiple transactions on slaves in a single step for GTID enabled MySQL servers, by injecting empty transactions on all specified slaves for each GTID in the specified GTID set.

mysqlslavetrx --help
mysqlslavetrx --version

mysqlslavetrx --gtid-set=2e84a6ae-b8dc-11e6-884d-005056a12ebe:7-9 --slaves=user:pass@localhost:3311,user:pass@localhost:3312
mysqlslavetrx --gtid-set=0b6e2f7a-2888-11e4-907e-005056855cdb:6-12 --slaves=user:pass@localhost:3311,user:pass@localhost:3312 --dryrun
mysqlslavetrx --gtid-set=1e8f69aa-48e7-11e8-b8e8-005056a13b37:6-12 --slaves=user:pass@localhost:3311,user:pass@localhost:3312 --verbose
mysqlslavetrx --gtid-set=2e84a6ae-b8dc-11e6-884d-005056a12ebe:5-11 --slaves=slave-admin -vv

mysqlslavetrx Options:
  --version            show program's version number and exit
  --help                display a help message and exit
  --license            display program's license and exit
  --ssl-ca=SSL_CA      path to a file that contains a list of trusted SSL CAs.
  --ssl-cert=SSL_CERT  name of the SSL certificate file to use for establishing a secure connection.
  --ssl-key=SSL_KEY    name of the SSL key file to use for establishing a  secure connection.
  --ssl=SSL            specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. By default 0 (SSL not  required).
  --gtid-set=GTID_SET  set of Global Transaction Identifiers (GTID) to skip.
  --slaves=SLAVES      connection information for slave servers in the form:
                       user[:password]@host[:port][:socket] or login-path[:port][:socket] or config-path[[group]]. List multiple slaves in comma-separated list.
  --dryrun             determine the transactions (GTID) to be skipped for each slave but without effectively skipping them (injecting empty transactions) - useful to test the  transactions that would be skipped.
  -v, --verbose        control how much information is displayed. e.g., -v = verbose, -vv = more verbose, -vvv = debug

Introduction
------------
The mysqlslavetrx utility is designed to skip multiple transactions on slaves in a quick and easy way. More specifically, it injects empty transactions on the slaves for each GTID that will be skipped.

The utility requires GTIDs to be enabled on all slaves. It does not require replication to be stopped. However, in some situation it is recommended.
For example, in order to skip a transaction from the master  on a slave, that slave should be stopped otherwise the target transaction might still be replicated (and not skipped).

Note: Only transactions (GTIDs) that were not committed can be skipped, since two transactions cannot be applied with the same GTID. GTIDs already in the GTID_EXECUTED set of a slave will be ignored.

The utility requires the specification of the GTID set to skip and the list of target slaves as shown in the following example.

  # Skip the specified GTID set (three transaction: 10, 11, 12) on two slaves.
  $ mysqlslavetrx --gtid-set=ee2655ae-2e88-11e4-b7a3-606720440b68:10-12  --slaves=rpl:pass@host2:3306,rpl:pass@host3:3306

Helpful Hints
-------------
  - Use the --dryrun option to execute the utility in dry run mode and confirm which transactions would be skipped with the provided input values without  effectively skipping them.

WARNING: Skipping transactions is a useful technique to recover from erroneous situations with replication. However, it must be applied with extreme caution and with full knowledge of its consequences as it might lead to data
inconsistencies between the replication servers. For example, if a transaction that insert some data 'row1' in table 't1' fails on one slave and that transaction is skipped to solve the issue, then that data will be missing from the slave (and no longer replicated). As a consequence the data for table 't1' will be inconsistent with the one on the master and the other slaves because 'row1' will be missing.

Related MySQL Articles: 

mysqlprocgrep utility commands in MySQL

MySQL utility mysqlprocgrep cheatsheet

mysqlprocgrep     search or grep for process information/list            
mysqlprocgrep --version
mysqlprocgrep --help
Usage: mysqlprocgrep --server=user:pass@host:port:socket [options]

mysqlprocgrep utility scans the process lists for servers specified using instances of the --server option and selects those that match the conditions specified.

mysqlprocgrep --server=root:secret@localhost
mysqlprocgrep --server=root@localhost --match-user=procgrep --age=1m --kill-query
mysqlprocgrep --server=dba --match-command=sleep --age=1h --kill
mysqlprocgrep --kill-connection --sql-body --match-user=data --match-state=sleep

mysqlprocgrep --server=root@localhost --match-user=satya --kill-connection --format=CSV
mysqlprocgrep --server=root@localhost --match-command=sleep --age=1h --kill-connection --verbose
mysqlprocgrep --server=mysql_dba --match-db=sakila --kill-connection --format=TAB -v

mysqlprocgrep Options:
  --version                 show program's version number and exit
  --help                     display a help message and exit
  --license                 display program's license and exit
  --server=SERVER    connection information for the server in the form: user[:password]@host[:port][:socket] or login-path[:port][:socket] or config- path[[group]].

  --ssl-ca=SSL_CA        path to a file that contains a list of trusted SSL CAs.
  --ssl-cert=SSL_CERT  name of the SSL certificate file to use for   establishing a secure connection.
  --ssl-key=SSL_KEY    name of the SSL key file to use for establishing a  secure connection.
  --ssl=SSL                  specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. By default 0 (SSL not required).

  --character-set=CHARSET    sets the client character set. The default is retrieved from the server variable 'character_set_client'.
  -G, --basic-regexp, --regexp    use 'REGEXP' operator to match pattern. Default is to use 'LIKE'.
  -Q, --print-sql, --sql     print the statement instead of sending it to the server. If a kill option is submitted, a procedure will be generated containing the code for executing the kill.
  --sql-body                   only print the body of the procedure.

  --kill-connection     kill all matching connections.
  --kill-query            kill query for all matching processes.
  --print                   print all matching processes.
  -f FORMAT, --format=FORMAT   display the output in either grid (default), tab, csv, or vertical format
  -v, --verbose         control how much information is displayed. e.g., -v =  verbose, -vv = more verbose, -vvv = debug

  --match-id=PATTERN      match the 'ID' column of the PROCESSLIST table.
  --match-user=PATTERN  match the 'USER' column of the PROCESSLIST table.
  --match-host=PATTERN  match the 'HOST' column of the PROCESSLIST table.
  --match-db=PATTERN     match the 'DB' column of the PROCESSLIST table.
  --match-command=PATTERN  match the 'COMMAND' column of the PROCESSLIST table.
  --match-info=PATTERN     match the 'INFO' column of the PROCESSLIST table.
  --match-state=PATTERN   match the 'STATE' column of the PROCESSLIST table.
  --age=AGE                      show only processes that have been in the current  state more than a given time.


Related MySQL Articles: mysqlrplms utility in MySQL

mysqlauditgrep utiltity in MySQL

MySQL utility - mysqlauditgrep commands

mysqlauditgrep    audit log search utility      
  
mysqlauditgrep utility allows MySQL users/DBAs to search current or archived audit logs, and display data from the audit log file according to the search criterion.

Usage: mysqlauditgrep [options] AUDIT_LOG_FILE
mysqlauditgrep --help
mysqlauditgrep --version

mysqlauditgrep --file-stats --format=CSV /mysql_audit/data/audit.log
mysqlauditgrep --users=tester1,tester2 /mysql_audit_grep/audit.log
mysqlauditgrep --start-date=2019-05-27T16:45:00 --end-date=2019-05-28 /mysql_audit/data/audit.log

mysqlauditgrep --pattern="% = ___"; /mysql_audit_grep/data/audit.log
mysqlauditgrep --query-type=show,SET /mysql_audit/data/audit.log
mysqlauditgrep --pattern=".* = ..." --regexp /mysql_audit/data/audit.log
mysqlauditgrep --file-stats --format=RAW /mysql_audit/data/audit.log
mysqlauditgrep --event-type="Ping,Connect" /mysql_audit/audit.log

mysqlauditgrep --status=1100-1199,1046 /mysql_audit_grep/data/audit.log
mysqlauditgrep --users=root --start-date=0 --end-date=2019-04-10 --event-type=Query --query-type=SET --status=0 --pattern="%audit_log%" /mysql_audit_grep/data/audit.log

mysqlauditgrep Options:
  --version             show program's version number and exit
  --help                 display this help message and exit
  --license             display program's license and exit
  -f FORMAT, --format=FORMAT   display the output in either GRID (default), TAB, CSV, VERTICAL and RAW format
  -u USERS, --users=USERS    find log entries by user name. Accepts a comma- separated list of user names, for example: joe,sally,nick
  --file-stats          display the audit log statistics.

  --start-date=START_DATE   retrieve log entries starting from the specified date/time. If not specified or the value is 0, all entries from the start of the log are displayed. Accepted formats: yyyy-mm-ddThh:mm:ss or yyyy-mm-dd.
  --end-date=END_DATE   retrieve log entries until the specified date/time. If not specified or the value is 0, all entries to the end of the log are displayed. Accepted formats: yyyy- mm-ddThh:mm:ss or yyyy-mm-dd.
  -e PATTERN, --pattern=PATTERN     search pattern to retrieve matching entries.
  --query-type=QUERY_TYPE    search for all SQL statements/commands from the given list of commands. Accepts a comma-separated list of commands. Supported values: CREATE, ALTER, DROP, TRUNCATE, RENAME, GRANT, REVOKE, SELECT, INSERT, UPDATE, DELETE, COMMIT, SHOW, SET, CALL, PREPARE, EXECUTE, DEALLOCATE

  --event-type=EVENT_TYPE    search for all recorded event types from the given list of supported log events. Accepts a comma- separated list of event types. Supported values: Audit, Binlog Dump, Change user, Close stmt, Connect Out, Connect, Create DB, Daemon, Debug, Delayed insert, Drop DB, Execute, Fetch, Field List, Init DB, Kill, Long Data, NoAudit, Ping, Prepare, Processlist, Query, Quit, Refresh, Register Slave, Reset stmt, Set option, Shutdown, Sleep, Statistics, Table Dump, Time

  --status=STATUS       search for all entries with the specified status values. Accepts a comma-separated list of non-negative integers (corresponding to MySQL error codes) or intervals marked with a dash. For example: 1051,1068-1075,1109,1146.
  -G, --basic-regexp, --regexp  use 'REGEXP' operator to match pattern. Default is to use 'LIKE'.
  -v, --verbose         control how much information is displayed. e.g., -v =  verbose, -vv = more verbose, -vvv = debug


Related MySQL Articles: MySQL utility - mysqlauditadmin  mysqldiskusage utility

MySQL user clone mysqluserclone utility

MySQL mysqluserclone utility cheatsheet

mysqluserclone    clone a MySQL user account to one or more new users 

MySQL mysqluserclone utility uses an existing MySQL user account on one server, and clones it to create one or more new user accounts, on the same server or a different server.

Usage: mysqluserclone --source=user:pass@host:port:socket --destination=user:pass@host:port:socket joe@localhost sam:secret1@localhost

mysqluserclone --version
mysqluserclone --help

mysqluserclone --source=root:secret@localhost --list
mysqluserclone --source=root@localhost --list --format=CSV -vvv
mysqluserclone --source=root@localhost --dump --format=tab -vvv

mysqluserclone --source=root@localhost --destination=root@localhost joe@localhost sam:secret1@localhost sally:secret2@localhost
mysqluserclone --source=root@localhost --destination=root@localhost source_user@localhost target_user:secret1@192.168.1.1 --verbose

mysqluserclone Options:
  --version             show program's version number and exit
  --help                 display a help message and exit
  --license             display program's license and exit

  --ssl-ca=SSL_CA       path to a file that contains a list of trusted SSL     CAs.
  --ssl-cert=SSL_CERT   name of the SSL certificate file to use for  establishing a secure connection.
  --ssl-key=SSL_KEY     name of the SSL key file to use for establishing a   secure connection.
  --ssl=SSL             specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. By default 0 (SSL not required).

  --source=SOURCE       connection information for source server in the form: user[:password]@host[:port][:socket] or login-path[:port][:socket] or config- path[[group]].
  --destination=DESTINATION connection information for destination server in the form: user[:password]@host[:port][:socket] or login-path[:port][:socket] or config- path[[group]].
  -d, --dump            dump GRANT statements for user - does not require a  destination
  --force               drop the new user if it exists

  --include-global-privileges  include privileges that match base_user@% as well as   base_user@host
  -l, --list            list all users on the source - does not require a destination
  -f FORMAT, --format=FORMAT display the list of users in either grid (default), tab, csv, or vertical format - valid only for --list option
  -v, --verbose         control how much information is displayed. e.g., -v = verbose, -vv = more verbose, -vvv = debug
  -q, --quiet           turn off all messages for quiet execution.
  

Related MySQL Articles: mysqlshow utility commands  mysqlfailover commands in MySQL

MySQL mysql_install_db utility examples

mysql_install_db utility commands in MySQL

mysql_install_db -- MySQL Database Deployment Utility.

mysql_install_db --help
Usage: mysql_install_db [OPTIONS]
mysql_install_db --version

mysql_install_db --user=mysql
mysql_install_db --datadir=path/to/datadir

mysql_install_db --user=mysql --datadir=/clients/example/mysql/data/
mysql_install_db --user=mysql --datadir=/clients/example/mysql/data/ --verbose

mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
$sudo ./mysql_install_db --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql/ --user=mysql

mysql install db options:
  -?, --help          Display this help and exit.
  --admin-auth-plugin=name Plugin to use for the default admin account.
  --admin-host=name    Hostname part of the default admin account.
  --admin-require-ssl Require SSL/TLS for the default admin account.
  --admin-user=name    Username part of the default admin account.

  --basedir=name      The path to the MySQL installation directory.
  --builddir=name      For use with --srcdir and out-of-source builds. Set this to the location of the directory where the built files reside.
  --datadir=name      The path to the MySQL data directory.

  -f, --extra-sql-file=name Optional SQL file to execute during bootstrap.
  --insecure          Disables random passwords for the default admin account.
  --lc-messages=name  Specifies the language to use.
  -l, --lc-messages-dir=name    Specifies the path to the language files.
  --login-file=name   Use the MySQL password store at the specified location to set the default password. 
This option takes precedence over admin-user, admin-host options. Use the login-path option to change the default credential category (default  is 'client').
  --login-path=name   Set the credential category to use with the MySQL password store when setting default credentials. 
This option takes precedence over admin-user, admin-host options.

  --mysqld-file=name  Qualified path to the mysqld binary.
  --random-password-file=name  Specifies the qualified path to the .mysql_secret  temporary password file.
  --skip-sys-schema   Skip installation of the sys schema.
  --srcdir=name       For internal use. This option specifies the directory  under which mysql_install_db looks for support files such as the error message file and the file for populating the  help tables.

  -u, --user=name     The effective user id used when executing the bootstrap sequence.
  -v, --verbose        Be more verbose when running program.
  -V, --version        Print program version and exit.

The following options may be given as the first argument for mysql_install_db:
--print-defaults        Print the program argument list and exit.
--no-defaults            Don't read default options from any option file,  except for login file.
--defaults-file=#        Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.

mysql_install_db variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
admin-auth-plugin                 mysql_native_password
admin-host                        localhost
admin-require-ssl                 FALSE
admin-user                        root
basedir                           (No default value)
builddir                          (No default value)
datadir                           (No default value)
extra-sql-file                    (No default value)
insecure                          FALSE
lc-messages                       en_US
lc-messages-dir                   (No default value)
login-file                        (No default value)
login-path                        client
mysqld-file                       (No default value)
random-password-file              (No default value)
skip-sys-schema                   FALSE
srcdir                            (No default value)
user                              (No default value)
verbose                           FALSE
version                           FALSE

Related MySQL Articles: mysqldbcopy commands cheatsheet MySQL mysqldbexport cheatsheet

MySQL mysqlrplcheck utility commands

MySQL mysqlrplcheck replication check utility

mysqlrplcheck    MySQL utility to check MySQL replication between master slave


Usage: mysqlrplcheck --master=root@localhost:3306 --slave=root@localhost:3310

mysqlrplcheck is to check prerequisites for replication between a MySQL master and a slave, to ensure a healthy replication setup.

mysqlrplcheck --help
mysqlrplcheck --version

mysqlrplcheck --master=dbhost1 --slave=dbhost2

mysqlrplcheck --master=dbhost1 --slave=dbhost2 --verbose

mysqlrplcheck --master=master_host --slave=slave_host --vv --width=120

[root@localhost]# mysqlrplcheck --master=mysql1 --slave=mysql2

mysqlrplcheck --master=root@proddb --slave=root@prodslave

mysqlrplcheck --master=root:secret@localhost:3306 --slave=root:secret@localhost:3310 --show-slave-status

mysqlrplcheck --master=root:secret@localhost:3306 --slave=root:secret@localhost:3310 --show-slave-status --verbose --width=200

mysql rpl check command options:
  --version              show program's version number and exit
  --help                display a help message and exit
  --license              display program's license and exit

  --master=MASTER       connection information for master server in the form:
                        user[:password]@host[:port][:socket] or login-path[:port][:socket] or config-path[[group]].
  --slave=SLAVE          connection information for slave server in the form:
                        user[:password]@host[:port][:socket] or login-path[:port][:socket] or config-path[[group]].
  --master-info-file=MASTER_INFO the name of the master information file on the slave. Default = 'master.info' read from the data directory. Note: this option requires that the utility run on the slave with appropriate file read access to the data directory.
  -s, --show-slave-status show slave status
  --width=WIDTH          display width
  --suppress            suppress warning messages

  --ssl-ca=SSL_CA        path to a file that contains a list of trusted SSL CAs.
  --ssl-cert=SSL_CERT  name of the SSL certificate file to use for establishing a secure connection.
  --ssl-key=SSL_KEY    name of the SSL key file to use for establishing a secure connection.
  --ssl=SSL              specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. By default 0 (SSL not required).
  -v, --verbose          control how much information is displayed. e.g., -v =  verbose, -vv = more verbose, -vvv = debug
  -q, --quiet            turn off all messages for quiet execution.

Related MySQL Articles:  MySQL mysqlreplicate cheatsheet    mysqlrpladmin utility commands