Last edited 3 weeks ago
by Peter A. Smode

KitsNet Operations:Services:Database:MariaDB: Difference between revisions

Back to version history
Newer edit →
VisualWikitext
Peter A. Smode (talk | contribs)
administrator, Bureaucrats, family, kitsnet_adm, Administrators
2,612 edits
Created page with "Stuff about MariaDB, includign deployment, monitoring, backup and more"
 
Peter A. Smode (talk | contribs)
administrator, Bureaucrats, family, kitsnet_adm, Administrators
2,612 edits
No edit summary
Line 1: Line 1:
Stuff about MariaDB, includign deployment, monitoring, backup and more
Stuff about MariaDB, includign deployment, monitoring, backup and more.
 
 
==Deployments==
MariaDB is deployed in support of applicaitons on these KitsNet systems:
 
*[[KitsNet Operations:Builds:Linux:ciroc]] Mailbox server
*[[KitsNet Operations:Builds:Linux:galliano]] MediaWiki site
*[[KitsNet Operations:Builds:Linux:zaya]] Zabbix monitoring
 
==Monitoring==
MySQL and MariaDB databases are monitored regularly by Zabbix by aleveraging the [https://www.zabbix.com/integrations/mysql#mysql_agent2 MySQL by Zabbix agent 2] capability. this depends upon a user being created within the database server to suport monitoring t, then setting appropriate macros for the server profile on the Zabbix servers.
 
#Create a MySQL user for monitoring<syntaxhighlight lang="shell-session">CREATE USER 'zbx_monitor'@'%' IDENTIFIED BY '<password>';
GRANT REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'%';</syntaxhighlight>
#Set in the {$MYSQL.DSN} macro the data source name of the MySQL instance either session name from Zabbix agent 2 configuration file or URI. '''Examples:''' MySQL1, tcp://localhost:3306, tcp://172.16.0.10, unix:/var/run/mysql.sock For more information about MySQL Unix socket file, see the MySQL documentation <nowiki>https://dev.mysql.com/doc/refman/8.0/en/problems-with-mysql-sock.html</nowiki>.
#f you had set URI in the {$MYSQL.DSN}, define the user name and password in host macros ({$MYSQL.USER} and {$MYSQL.PASSWORD}). Leave macros {$MYSQL.USER} and {$MYSQL.PASSWORD} empty if you use a session name. Set the user name and password in the Plugins.Mysql.<...> section of your Zabbix agent 2 configuration file. For more information about configuring the Zabbix MySQL plugin, see the documentation <nowiki>https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/src/go/plugins/mysql/README.md</nowiki>.
 
==Operations Tooling==
Standardized scripts are deployed in KitsNet to support health checks of MariaDB databases and daily backup operations. These scripts are integrated witht he rest of the KitsNet operational infrastcuture.
 
===Health checks===
I developed the <code>CheckMariaDBIntegrity</code> script with help from ChatGPT. The design remit was for a script which could be used regularly to verify database integrity and lolk for corruption. The script has been incorporated into the <code>KNsysmisc</code> package installed on all KitsNet Linux servers. While the script can be run standalone, it is intended to be executed nightly in a cron job. The best way to implmentet is to create a <code>CheckMariaDBIntegrity-''server''</code>  script that will invoke the <code>CheckMariaDBIntegrity</code> script with appropriate user and password arguments. The <code>CheckMariaDBIntegrity-''server''</code>  script should have a protection mask applied to it that restricts access to <u>root</u> only. In general, the database integrity check should eb scheduled to run afdter the daily backup is completed.
 
The <code>CheckMariaDBIntegrity</code> script is designed to run silently if there are no issues detected, though all activity is noted in syslog with appropriate severities under the daemon facility and tagged with <code>MariaDBIntegrityCheck</code> . This encoding ensures that results of the integrity check will generate events in ConsoleWorks by default when required. The two failure events logged by the scrip are:
 
*ERROR: Could not connect to MariaDB with provided credentials.
*TABLE CHECK ISSUE
 
===Backup===
The MariaDB instances in KitsNet are small enough that simple dump operations are sufficient. In support of this, the <code>backup_localMySQL</code> was developed to execute a database dump to disk. A server specific script woudl be created to set the required arguments for the backup, including access credentials and the output directory for the dump. This script would be specified as a pre-backup script in the definition of the Veeam data backup job for the server, so that the dump of the database is taken immediaztely prior to the server backup operation. Should a restore operation be necessary, this could be preformed directrly from the on-disk directory where the dumps are accumulated. If the dump to be restored is outside this range, the dump coiuld be restored from a Veeam backup and then the dump could be restored.
 
====Restore operations====
Restoration of the database dump can be used to migrate a database, recover from disk corruption or restore a crash-safe copy of the database backup following recovery from a disk failre. In asll these cases, the database must first exist then have the dump written over it. In cases outside of migration, it is probably best to drop the existing tables before importing the dump. Refer to the <code>backup_localMySQL-''server''</code> script for credential and database information.
 
#Restore dumpfile from backup (if necessary). Use Veeam or other means to bring back the generation of the dump file, probably to <code>/var/lib/mysql/dumps</code> . If moving around the dump file, care needs to be taken that the contents of the dump do not get the envdoing changed in transit. Source for the dumps could be the live collection of Veeam backups from the '''BKUP''' server, monthly server backups to external drives, or elsewhere. Each dump is a complete set, so no incrementals or journal replays need be perfromed
#Stop applicaiton. If there is an applicaiton or service dependant upon the database , use <code>systemctl</code> of other appropriate means to stop it while the the database is being reloaded.
#Drop database and recreate (if necesary). Drop exisitng appliciaotn database(s). For the Zabbix installation, it would look like:<syntaxhighlight lang="shell-session">
# mysql -uroot -p
mysql> DROP DATABASE IF EXISTS zabbix;
mysql> CREATE DATABASE zabbix CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
mysql> CREATE USER 'zabbix@localhost' IDENTIFIED BY 'your_password';
mysql> GRANT ALL PRIVILEGES ON zabbix.* TO 'zabbix'@'localhost';
mysql> quit;</syntaxhighlight>
#Restore the database from the dump. Command will generally look like: <code># mysql -u ''user'' -p ''DB_name'' < /var/lib/mysql/dumps/''DB_name''.''Day''.sql</code>
#Reboot server. This will cause the appliciaton to be restarted with the correct data present.
 
==Support==
Here are some handy commands to check out a MariaDB server
====Execute series of database queries to check for match on DB accounts, tables and row counts====
<syntaxhighlight lang="shell-session">
[psmode@ciroc ~]$ sudo  mariadb-check --analyze --all-databases -uroot -p
Enter password:
mysql.column_stats                                Table is already up to date
mysql.columns_priv                                Table is already up to date
mysql.db                                          OK
mysql.event                                        Table is already up to date
mysql.func                                        Table is already up to date
mysql.global_priv                                  OK
mysql.gtid_slave_pos                              OK
mysql.help_category                                OK
mysql.help_keyword                                Table is already up to date
mysql.help_relation                                Table is already up to date
mysql.help_topic                                  Table is already up to date
mysql.index_stats                                  Table is already up to date
mysql.innodb_index_stats                          OK
mysql.innodb_table_stats                          OK
mysql.plugin                                      Table is already up to date
mysql.proc                                        Table is already up to date
mysql.procs_priv                                  Table is already up to date
mysql.proxies_priv                                OK
mysql.roles_mapping                                Table is already up to date
mysql.servers                                      Table is already up to date
mysql.table_stats                                  Table is already up to date
mysql.tables_priv                                  OK
mysql.time_zone                                    Table is already up to date
mysql.time_zone_leap_second                        Table is already up to date
mysql.time_zone_name                              Table is already up to date
mysql.time_zone_transition                        Table is already up to date
mysql.time_zone_transition_type                    Table is already up to date
mysql.transaction_registry                        OK
postfixadmin.admin                                OK
postfixadmin.alias                                OK
postfixadmin.alias_domain                          OK
postfixadmin.config                                OK
postfixadmin.domain                                OK
postfixadmin.domain_admins                        OK
postfixadmin.fetchmail                            OK
postfixadmin.log                                  OK
postfixadmin.mailbox                              OK
postfixadmin.quota                                OK
postfixadmin.quota2                                OK
postfixadmin.vacation                              OK
postfixadmin.vacation_notification                OK
[psmode@ciroc ~]$ mariadb -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.5.22-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database          |
+--------------------+
| dumps              |
| information_schema |
| mysql              |
| performance_schema |
| postfixadmin      |
+--------------------+
5 rows in set (0.000 sec)
 
MariaDB [(none)]> SHOW TABLES;
ERROR 1046 (3D000): No database selected
MariaDB [(none)]> ^DBye
[psmode@ciroc ~]$ mariadb-show -uroot -p
Enter password:
+--------------------+
|    Databases      |
+--------------------+
| dumps              |
| information_schema |
| mysql              |
| performance_schema |
| postfixadmin      |
+--------------------+
[psmode@ciroc ~]$ mariadb-show -uroot -p dumps
Enter password:
Database: dumps
+--------+
| Tables |
+--------+
+--------+
[psmode@ciroc ~]$ mariadb-show -uroot -p information_schema
Enter password:
Database: information_schema
+---------------------------------------+
|                Tables                |
+---------------------------------------+
| ALL_PLUGINS                          |
| APPLICABLE_ROLES                      |
| CHARACTER_SETS                        |
| CHECK_CONSTRAINTS                    |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                              |
| COLUMN_PRIVILEGES                    |
| ENABLED_ROLES                        |
| ENGINES                              |
| EVENTS                                |
| FILES                                |
| GLOBAL_STATUS                        |
| GLOBAL_VARIABLES                      |
| KEYWORDS                              |
| KEY_CACHES                            |
| KEY_COLUMN_USAGE                      |
| OPTIMIZER_TRACE                      |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                              |
| PROCESSLIST                          |
| PROFILING                            |
| REFERENTIAL_CONSTRAINTS              |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                    |
| SESSION_STATUS                        |
| SESSION_VARIABLES                    |
| STATISTICS                            |
| SQL_FUNCTIONS                        |
| SYSTEM_VARIABLES                      |
| TABLES                                |
| TABLESPACES                          |
| TABLE_CONSTRAINTS                    |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                      |
| VIEWS                                |
| CLIENT_STATISTICS                    |
| INDEX_STATISTICS                      |
| INNODB_SYS_DATAFILES                  |
| GEOMETRY_COLUMNS                      |
| INNODB_SYS_TABLESTATS                |
| SPATIAL_REF_SYS                      |
| INNODB_BUFFER_PAGE                    |
| INNODB_TRX                            |
| INNODB_CMP_PER_INDEX                  |
| INNODB_METRICS                        |
| INNODB_LOCK_WAITS                    |
| INNODB_CMP                            |
| THREAD_POOL_WAITS                    |
| INNODB_CMP_RESET                      |
| THREAD_POOL_QUEUES                    |
| TABLE_STATISTICS                      |
| INNODB_SYS_FIELDS                    |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_LOCKS                          |
| INNODB_FT_INDEX_TABLE                |
| INNODB_CMPMEM                        |
| THREAD_POOL_GROUPS                    |
| INNODB_CMP_PER_INDEX_RESET            |
| INNODB_SYS_FOREIGN_COLS              |
| INNODB_FT_INDEX_CACHE                |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_FT_BEING_DELETED              |
| INNODB_SYS_FOREIGN                    |
| INNODB_CMPMEM_RESET                  |
| INNODB_FT_DEFAULT_STOPWORD            |
| INNODB_SYS_TABLES                    |
| INNODB_SYS_COLUMNS                    |
| INNODB_FT_CONFIG                      |
| USER_STATISTICS                      |
| INNODB_SYS_TABLESPACES                |
| INNODB_SYS_VIRTUAL                    |
| INNODB_SYS_INDEXES                    |
| INNODB_SYS_SEMAPHORE_WAITS            |
| INNODB_MUTEXES                        |
| user_variables                        |
| INNODB_TABLESPACES_ENCRYPTION        |
| INNODB_FT_DELETED                    |
| THREAD_POOL_STATS                    |
+---------------------------------------+
[psmode@ciroc ~]$ mariadb-show -uroot -p mysql
Enter password:
Database: mysql
+---------------------------+
|          Tables          |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                    |
| func                      |
| general_log              |
| global_priv              |
| gtid_slave_pos            |
| help_category            |
| help_keyword              |
| help_relation            |
| help_topic                |
| index_stats              |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| roles_mapping            |
| servers                  |
| slow_log                  |
| table_stats              |
| tables_priv              |
| time_zone                |
| time_zone_leap_second    |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| transaction_registry      |
| user                      |
+---------------------------+
[psmode@ciroc ~]$ mariadb-show -uroot -p performance_schema
Enter password:
Database: performance_schema
+------------------------------------------------------+
|                        Tables                        |
+------------------------------------------------------+
| accounts                                            |
| cond_instances                                      |
| events_stages_current                                |
| events_stages_history                                |
| events_stages_history_long                          |
| events_stages_summary_by_account_by_event_name      |
| events_stages_summary_by_host_by_event_name          |
| events_stages_summary_by_thread_by_event_name        |
| events_stages_summary_by_user_by_event_name          |
| events_stages_summary_global_by_event_name          |
| events_statements_current                            |
| events_statements_history                            |
| events_statements_history_long                      |
| events_statements_summary_by_account_by_event_name  |
| events_statements_summary_by_digest                  |
| events_statements_summary_by_host_by_event_name      |
| events_statements_summary_by_program                |
| events_statements_summary_by_thread_by_event_name    |
| events_statements_summary_by_user_by_event_name      |
| events_statements_summary_global_by_event_name      |
| events_transactions_current                          |
| events_transactions_history                          |
| events_transactions_history_long                    |
| events_transactions_summary_by_account_by_event_name |
| events_transactions_summary_by_host_by_event_name    |
| events_transactions_summary_by_thread_by_event_name  |
| events_transactions_summary_by_user_by_event_name    |
| events_transactions_summary_global_by_event_name    |
| events_waits_current                                |
| events_waits_history                                |
| events_waits_history_long                            |
| events_waits_summary_by_account_by_event_name        |
| events_waits_summary_by_host_by_event_name          |
| events_waits_summary_by_instance                    |
| events_waits_summary_by_thread_by_event_name        |
| events_waits_summary_by_user_by_event_name          |
| events_waits_summary_global_by_event_name            |
| file_instances                                      |
| file_summary_by_event_name                          |
| file_summary_by_instance                            |
| global_status                                        |
| host_cache                                          |
| hosts                                                |
| memory_summary_by_account_by_event_name              |
| memory_summary_by_host_by_event_name                |
| memory_summary_by_thread_by_event_name              |
| memory_summary_by_user_by_event_name                |
| memory_summary_global_by_event_name                  |
| metadata_locks                                      |
| mutex_instances                                      |
| objects_summary_global_by_type                      |
| performance_timers                                  |
| prepared_statements_instances                        |
| replication_applier_configuration                    |
| replication_applier_status                          |
| replication_applier_status_by_coordinator            |
| replication_connection_configuration                |
| rwlock_instances                                    |
| session_account_connect_attrs                        |
| session_connect_attrs                                |
| session_status                                      |
| setup_actors                                        |
| setup_consumers                                      |
| setup_instruments                                    |
| setup_objects                                        |
| setup_timers                                        |
| socket_instances                                    |
| socket_summary_by_event_name                        |
| socket_summary_by_instance                          |
| status_by_account                                    |
| status_by_host                                      |
| status_by_thread                                    |
| status_by_user                                      |
| table_handles                                        |
| table_io_waits_summary_by_index_usage                |
| table_io_waits_summary_by_table                      |
| table_lock_waits_summary_by_table                    |
| threads                                              |
| user_variables_by_thread                            |
| users                                                |
+------------------------------------------------------+
[psmode@ciroc ~]$ mariadb-show -uroot -p postfixadmin
Enter password:
Database: postfixadmin
+-----------------------+
|        Tables        |
+-----------------------+
| admin                |
| alias                |
| alias_domain          |
| config                |
| domain                |
| domain_admins        |
| fetchmail            |
| log                  |
| mailbox              |
| quota                |
| quota2                |
| vacation              |
| vacation_notification |
+-----------------------+
</syntaxhighlight><syntaxhighlight lang="mysql">
[psmode@ciroc ~]$ mariadb -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.5.22-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> show full tables from postfixadmin
    -> ;
+------------------------+------------+
| Tables_in_postfixadmin | Table_type |
+------------------------+------------+
| admin                  | BASE TABLE |
| alias                  | BASE TABLE |
| alias_domain          | BASE TABLE |
| config                | BASE TABLE |
| domain                | BASE TABLE |
| domain_admins          | BASE TABLE |
| fetchmail              | BASE TABLE |
| log                    | BASE TABLE |
| mailbox                | BASE TABLE |
| quota                  | BASE TABLE |
| quota2                | BASE TABLE |
| vacation              | BASE TABLE |
| vacation_notification  | BASE TABLE |
+------------------------+------------+
13 rows in set (0.000 sec)
 
MariaDB [(none)]> SELECT table_schema as `DB`, table_name AS `Table`,
    -> ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)`
    -> FROM information_schema.TABLES
    -> ORDER BY (data_length + index_length) DESC;
+--------------------+------------------------------------------------------+-----------+
| DB                | Table                                                | Size (MB) |
+--------------------+------------------------------------------------------+-----------+
| mysql              | help_topic                                          |      0.51 |
| mysql              | help_relation                                        |      0.08 |
| mysql              | transaction_registry                                |      0.06 |
| mysql              | help_keyword                                        |      0.05 |
| postfixadmin      | log                                                  |      0.05 |
| postfixadmin      | alias_domain                                        |      0.05 |
| mysql              | db                                                  |      0.04 |
| mysql              | tables_priv                                          |      0.04 |
| mysql              | proxies_priv                                        |      0.04 |
| mysql              | help_category                                        |      0.04 |
| postfixadmin      | mailbox                                              |      0.03 |
| postfixadmin      | config                                              |      0.03 |
| postfixadmin      | domain_admins                                        |      0.03 |
| mysql              | global_priv                                          |      0.03 |
| postfixadmin      | vacation                                            |      0.03 |
| postfixadmin      | alias                                                |      0.03 |
| mysql              | gtid_slave_pos                                      |      0.02 |
| information_schema | PLUGINS                                              |      0.02 |
| mysql              | time_zone                                            |      0.02 |
| information_schema | EVENTS                                              |      0.02 |
| mysql              | roles_mapping                                        |      0.02 |
| mysql              | innodb_table_stats                                  |      0.02 |
| information_schema | ALL_PLUGINS                                          |      0.02 |
| postfixadmin      | quota2                                              |      0.02 |
| mysql              | func                                                |      0.02 |
| postfixadmin      | admin                                                |      0.02 |
| information_schema | OPTIMIZER_TRACE                                      |      0.02 |
| mysql              | time_zone_name                                      |      0.02 |
| mysql              | proc                                                |      0.02 |
| postfixadmin      | vacation_notification                                |      0.02 |
| information_schema | ROUTINES                                            |      0.02 |
| mysql              | columns_priv                                        |      0.02 |
| information_schema | PARTITIONS                                          |      0.02 |
| mysql              | time_zone_transition_type                            |      0.02 |
| information_schema | TRIGGERS                                            |      0.02 |
| mysql              | innodb_index_stats                                  |      0.02 |
| information_schema | SYSTEM_VARIABLES                                    |      0.02 |
| postfixadmin      | quota                                                |      0.02 |
| mysql              | event                                                |      0.02 |
| postfixadmin      | domain                                              |      0.02 |
| information_schema | PROCESSLIST                                          |      0.02 |
| mysql              | time_zone_leap_second                                |      0.02 |
| mysql              | servers                                              |      0.02 |
| information_schema | COLUMNS                                              |      0.02 |
| information_schema | VIEWS                                                |      0.02 |
| mysql              | plugin                                              |      0.02 |
| postfixadmin      | fetchmail                                            |      0.02 |
| mysql              | column_stats                                        |      0.02 |
| information_schema | PARAMETERS                                          |      0.02 |
| mysql              | time_zone_transition                                |      0.02 |
| mysql              | table_stats                                          |      0.02 |
| mysql              | procs_priv                                          |      0.02 |
| information_schema | CHECK_CONSTRAINTS                                    |      0.02 |
| mysql              | index_stats                                          |      0.02 |
| performance_schema | events_transactions_summary_global_by_event_name    |      0.00 |
| performance_schema | session_connect_attrs                                |      0.00 |
| information_schema | SCHEMATA                                            |      0.00 |
| information_schema | INNODB_LOCKS                                        |      0.00 |
| performance_schema | events_transactions_history_long                    |      0.00 |
| performance_schema | replication_applier_status                          |      0.00 |
| information_schema | INNODB_CMP_RESET                                    |      0.00 |
| performance_schema | events_statements_summary_by_thread_by_event_name    |      0.00 |
| performance_schema | mutex_instances                                      |      0.00 |
| information_schema | KEY_CACHES                                          |      0.00 |
| information_schema | INNODB_CMP_PER_INDEX                                |      0.00 |
| information_schema | INNODB_TABLESPACES_ENCRYPTION                        |      0.00 |
| performance_schema | events_statements_history_long                      |      0.00 |
| performance_schema | memory_summary_by_host_by_event_name                |      0.00 |
| performance_schema | table_lock_waits_summary_by_table                    |      0.00 |
| information_schema | GEOMETRY_COLUMNS                                    |      0.00 |
| information_schema | INNODB_SYS_VIRTUAL                                  |      0.00 |
| performance_schema | events_stages_summary_by_thread_by_event_name        |      0.00 |
| performance_schema | file_summary_by_instance                            |      0.00 |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY                |      0.00 |
| performance_schema | status_by_thread                                    |      0.00 |
| information_schema | USER_PRIVILEGES                                      |      0.00 |
| information_schema | INNODB_SYS_TABLES                                    |      0.00 |
| performance_schema | events_stages_current                                |      0.00 |
| performance_schema | events_waits_summary_by_thread_by_event_name        |      0.00 |
| performance_schema | socket_instances                                    |      0.00 |
| information_schema | TABLES                                              |      0.00 |
| information_schema | INNODB_BUFFER_POOL_STATS                            |      0.00 |
| performance_schema | events_waits_history                                |      0.00 |
| performance_schema | setup_actors                                        |      0.00 |
| information_schema | SESSION_STATUS                                      |      0.00 |
| information_schema | INNODB_CMPMEM                                        |      0.00 |
| performance_schema | events_transactions_summary_by_host_by_event_name    |      0.00 |
| performance_schema | replication_connection_configuration                |      0.00 |
| information_schema | PROFILING                                            |      0.00 |
| information_schema | TABLE_STATISTICS                                    |      0.00 |
| information_schema | THREAD_POOL_STATS                                    |      0.00 |
| performance_schema | events_statements_summary_global_by_event_name      |      0.00 |
| performance_schema | performance_timers                                  |      0.00 |
| information_schema | INNODB_LOCK_WAITS                                    |      0.00 |
| performance_schema | events_statements_summary_by_digest                  |      0.00 |
| performance_schema | memory_summary_by_user_by_event_name                |      0.00 |
| information_schema | GLOBAL_STATUS                                        |      0.00 |
| performance_schema | user_variables_by_thread                            |      0.00 |
| information_schema | SPATIAL_REF_SYS                                      |      0.00 |
| information_schema | INNODB_SYS_SEMAPHORE_WAITS                          |      0.00 |
| mysql              | slow_log                                            |      0.00 |
| performance_schema | events_stages_summary_global_by_event_name          |      0.00 |
| performance_schema | host_cache                                          |      0.00 |
| information_schema | COLUMN_PRIVILEGES                                    |      0.00 |
| performance_schema | table_handles                                        |      0.00 |
| information_schema | CLIENT_STATISTICS                                    |      0.00 |
| information_schema | INNODB_FT_CONFIG                                    |      0.00 |
| performance_schema | events_stages_history_long                          |      0.00 |
| performance_schema | events_waits_summary_global_by_event_name            |      0.00 |
| information_schema | CHARACTER_SETS                                      |      0.00 |
| performance_schema | socket_summary_by_instance                          |      0.00 |
| information_schema | TABLE_CONSTRAINTS                                    |      0.00 |
| information_schema | INNODB_SYS_FOREIGN                                  |      0.00 |
| performance_schema | events_waits_summary_by_account_by_event_name        |      0.00 |
| performance_schema | setup_instruments                                    |      0.00 |
| information_schema | STATISTICS                                          |      0.00 |
| information_schema | INNODB_CMP_PER_INDEX_RESET                          |      0.00 |
| performance_schema | events_transactions_summary_by_user_by_event_name    |      0.00 |
| performance_schema | session_account_connect_attrs                        |      0.00 |
| information_schema | INNODB_BUFFER_PAGE_LRU                              |      0.00 |
| performance_schema | events_transactions_history                          |      0.00 |
| performance_schema | replication_applier_configuration                    |      0.00 |
| information_schema | THREAD_POOL_WAITS                                    |      0.00 |
| performance_schema | events_statements_summary_by_program                |      0.00 |
| performance_schema | metadata_locks                                      |      0.00 |
| information_schema | KEYWORDS                                            |      0.00 |
| information_schema | INNODB_TRX                                          |      0.00 |
| information_schema | user_variables                                      |      0.00 |
| performance_schema | events_statements_history                            |      0.00 |
| performance_schema | memory_summary_by_account_by_event_name              |      0.00 |
| information_schema | ENGINES                                              |      0.00 |
| performance_schema | table_io_waits_summary_by_table                      |      0.00 |
| information_schema | INNODB_SYS_DATAFILES                                |      0.00 |
| information_schema | INNODB_SYS_TABLESPACES                              |      0.00 |
| performance_schema | events_stages_summary_by_host_by_event_name          |      0.00 |
| performance_schema | file_summary_by_event_name                          |      0.00 |
| information_schema | COLLATIONS                                          |      0.00 |
| performance_schema | status_by_host                                      |      0.00 |
| information_schema | INNODB_FT_DEFAULT_STOPWORD                          |      0.00 |
| performance_schema | cond_instances                                      |      0.00 |
| performance_schema | events_waits_summary_by_instance                    |      0.00 |
| performance_schema | setup_timers                                        |      0.00 |
| information_schema | INNODB_FT_INDEX_CACHE                                |      0.00 |
| performance_schema | events_waits_current                                |      0.00 |
| performance_schema | session_status                                      |      0.00 |
| information_schema | SCHEMA_PRIVILEGES                                    |      0.00 |
| information_schema | INNODB_FT_INDEX_TABLE                                |      0.00 |
| performance_schema | events_transactions_summary_by_account_by_event_name |      0.00 |
| performance_schema | replication_applier_status_by_coordinator            |      0.00 |
| information_schema | THREAD_POOL_QUEUES                                  |      0.00 |
| performance_schema | events_statements_summary_by_user_by_event_name      |      0.00 |
| performance_schema | objects_summary_global_by_type                      |      0.00 |
| information_schema | KEY_COLUMN_USAGE                                    |      0.00 |
| information_schema | INNODB_METRICS                                      |      0.00 |
| information_schema | INNODB_FT_DELETED                                    |      0.00 |
| performance_schema | events_statements_summary_by_account_by_event_name  |      0.00 |
| performance_schema | memory_summary_by_thread_by_event_name              |      0.00 |
| information_schema | FILES                                                |      0.00 |
| performance_schema | threads                                              |      0.00 |
| information_schema | INNODB_SYS_TABLESTATS                                |      0.00 |
| information_schema | INNODB_SYS_INDEXES                                  |      0.00 |
| performance_schema | events_stages_summary_by_user_by_event_name          |      0.00 |
| performance_schema | global_status                                        |      0.00 |
| performance_schema | status_by_user                                      |      0.00 |
| information_schema | INNODB_SYS_COLUMNS                                  |      0.00 |
| performance_schema | events_stages_history                                |      0.00 |
| performance_schema | events_waits_summary_by_user_by_event_name          |      0.00 |
| information_schema | APPLICABLE_ROLES                                    |      0.00 |
| performance_schema | socket_summary_by_event_name                        |      0.00 |
| information_schema | TABLESPACES                                          |      0.00 |
| information_schema | INNODB_FT_BEING_DELETED                              |      0.00 |
| performance_schema | events_waits_history_long                            |      0.00 |
| performance_schema | setup_consumers                                      |      0.00 |
| information_schema | SESSION_VARIABLES                                    |      0.00 |
| information_schema | THREAD_POOL_GROUPS                                  |      0.00 |
| mysql              | general_log                                          |      0.00 |
| performance_schema | events_transactions_summary_by_thread_by_event_name  |      0.00 |
| performance_schema | rwlock_instances                                    |      0.00 |
| information_schema | REFERENTIAL_CONSTRAINTS                              |      0.00 |
| information_schema | INNODB_SYS_FIELDS                                    |      0.00 |
| performance_schema | events_transactions_current                          |      0.00 |
| performance_schema | prepared_statements_instances                        |      0.00 |
| information_schema | INNODB_CMP                                          |      0.00 |
| performance_schema | events_statements_summary_by_host_by_event_name      |      0.00 |
| performance_schema | memory_summary_global_by_event_name                  |      0.00 |
| information_schema | GLOBAL_VARIABLES                                    |      0.00 |
| performance_schema | users                                                |      0.00 |
| information_schema | INNODB_BUFFER_PAGE                                  |      0.00 |
| information_schema | INNODB_MUTEXES                                      |      0.00 |
| performance_schema | events_statements_current                            |      0.00 |
| performance_schema | hosts                                                |      0.00 |
| information_schema | ENABLED_ROLES                                        |      0.00 |
| performance_schema | table_io_waits_summary_by_index_usage                |      0.00 |
| information_schema | INDEX_STATISTICS                                    |      0.00 |
| information_schema | USER_STATISTICS                                      |      0.00 |
| performance_schema | events_stages_summary_by_account_by_event_name      |      0.00 |
| performance_schema | file_instances                                      |      0.00 |
| performance_schema | status_by_account                                    |      0.00 |
| information_schema | TABLE_PRIVILEGES                                    |      0.00 |
| information_schema | INNODB_CMPMEM_RESET                                  |      0.00 |
| performance_schema | accounts                                            |      0.00 |
| performance_schema | events_waits_summary_by_host_by_event_name          |      0.00 |
| performance_schema | setup_objects                                        |      0.00 |
| information_schema | SQL_FUNCTIONS                                        |      0.00 |
| information_schema | INNODB_SYS_FOREIGN_COLS                              |      0.00 |
| mysql              | user                                                |      NULL |
+--------------------+------------------------------------------------------+-----------+
206 rows in set (0.015 sec)
 
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> SELECT table_schema as `DB`, table_name AS `Table`,  ROUND(((data_length + index_length) / 1024 / 1), 2) `Size (kB)`  FROM information_schema.TABLES  ORDER BY (data_length + index_length) DESC;
+--------------------+------------------------------------------------------+-----------+
| DB                | Table                                                | Size (kB) |
+--------------------+------------------------------------------------------+-----------+
| mysql              | help_topic                                          |    520.00 |
| mysql              | help_relation                                        |    80.00 |
| mysql              | transaction_registry                                |    64.00 |
| mysql              | help_keyword                                        |    48.00 |
| postfixadmin      | log                                                  |    48.00 |
| postfixadmin      | alias_domain                                        |    48.00 |
| mysql              | tables_priv                                          |    40.00 |
| mysql              | proxies_priv                                        |    40.00 |
| mysql              | help_category                                        |    40.00 |
| mysql              | db                                                  |    40.00 |
| postfixadmin      | domain_admins                                        |    32.00 |
| mysql              | global_priv                                          |    32.00 |
| postfixadmin      | vacation                                            |    32.00 |
| postfixadmin      | alias                                                |    32.00 |
| postfixadmin      | mailbox                                              |    32.00 |
| postfixadmin      | config                                              |    32.00 |
| postfixadmin      | quota2                                              |    16.00 |
| mysql              | func                                                |    16.00 |
| postfixadmin      | admin                                                |    16.00 |
| information_schema | OPTIMIZER_TRACE                                      |    16.00 |
| mysql              | time_zone_name                                      |    16.00 |
| mysql              | proc                                                |    16.00 |
| postfixadmin      | vacation_notification                                |    16.00 |
| information_schema | ROUTINES                                            |    16.00 |
| mysql              | columns_priv                                        |    16.00 |
| information_schema | PARTITIONS                                          |    16.00 |
| mysql              | time_zone_transition_type                            |    16.00 |
| information_schema | TRIGGERS                                            |    16.00 |
| mysql              | innodb_index_stats                                  |    16.00 |
| information_schema | SYSTEM_VARIABLES                                    |    16.00 |
| postfixadmin      | quota                                                |    16.00 |
| mysql              | event                                                |    16.00 |
| postfixadmin      | domain                                              |    16.00 |
| information_schema | PROCESSLIST                                          |    16.00 |
| mysql              | time_zone_leap_second                                |    16.00 |
| information_schema | VIEWS                                                |    16.00 |
| mysql              | servers                                              |    16.00 |
| information_schema | COLUMNS                                              |    16.00 |
| mysql              | plugin                                              |    16.00 |
| postfixadmin      | fetchmail                                            |    16.00 |
| mysql              | column_stats                                        |    16.00 |
| information_schema | PARAMETERS                                          |    16.00 |
| mysql              | time_zone_transition                                |    16.00 |
| mysql              | table_stats                                          |    16.00 |
| mysql              | procs_priv                                          |    16.00 |
| information_schema | CHECK_CONSTRAINTS                                    |    16.00 |
| mysql              | index_stats                                          |    16.00 |
| mysql              | gtid_slave_pos                                      |    16.00 |
| information_schema | PLUGINS                                              |    16.00 |
| mysql              | time_zone                                            |    16.00 |
| information_schema | EVENTS                                              |    16.00 |
| mysql              | roles_mapping                                        |    16.00 |
| mysql              | innodb_table_stats                                  |    16.00 |
| information_schema | ALL_PLUGINS                                          |    16.00 |
| information_schema | INNODB_CMPMEM                                        |      0.00 |
| performance_schema | events_waits_history                                |      0.00 |
| performance_schema | setup_actors                                        |      0.00 |
| information_schema | SESSION_STATUS                                      |      0.00 |
| information_schema | TABLE_STATISTICS                                    |      0.00 |
| performance_schema | events_transactions_summary_by_host_by_event_name    |      0.00 |
| performance_schema | replication_connection_configuration                |      0.00 |
| information_schema | PROFILING                                            |      0.00 |
| information_schema | INNODB_LOCK_WAITS                                    |      0.00 |
| information_schema | THREAD_POOL_STATS                                    |      0.00 |
| performance_schema | events_statements_summary_global_by_event_name      |      0.00 |
| performance_schema | performance_timers                                  |      0.00 |
| performance_schema | user_variables_by_thread                            |      0.00 |
| information_schema | SPATIAL_REF_SYS                                      |      0.00 |
| information_schema | INNODB_SYS_SEMAPHORE_WAITS                          |      0.00 |
| performance_schema | events_statements_summary_by_digest                  |      0.00 |
| performance_schema | memory_summary_by_user_by_event_name                |      0.00 |
| information_schema | GLOBAL_STATUS                                        |      0.00 |
| performance_schema | table_handles                                        |      0.00 |
| information_schema | CLIENT_STATISTICS                                    |      0.00 |
| information_schema | INNODB_FT_CONFIG                                    |      0.00 |
| mysql              | slow_log                                            |      0.00 |
| performance_schema | events_stages_summary_global_by_event_name          |      0.00 |
| performance_schema | host_cache                                          |      0.00 |
| information_schema | COLUMN_PRIVILEGES                                    |      0.00 |
| information_schema | INNODB_SYS_FOREIGN                                  |      0.00 |
| performance_schema | events_stages_history_long                          |      0.00 |
| performance_schema | events_waits_summary_global_by_event_name            |      0.00 |
| information_schema | CHARACTER_SETS                                      |      0.00 |
| performance_schema | socket_summary_by_instance                          |      0.00 |
| information_schema | TABLE_CONSTRAINTS                                    |      0.00 |
| information_schema | INNODB_CMP_PER_INDEX_RESET                          |      0.00 |
| performance_schema | events_waits_summary_by_account_by_event_name        |      0.00 |
| performance_schema | setup_instruments                                    |      0.00 |
| information_schema | STATISTICS                                          |      0.00 |
| information_schema | INNODB_BUFFER_PAGE_LRU                              |      0.00 |
| performance_schema | events_transactions_summary_by_user_by_event_name    |      0.00 |
| performance_schema | session_account_connect_attrs                        |      0.00 |
| information_schema | THREAD_POOL_WAITS                                    |      0.00 |
| performance_schema | events_transactions_history                          |      0.00 |
| performance_schema | replication_applier_configuration                    |      0.00 |
| information_schema | INNODB_TRX                                          |      0.00 |
| information_schema | user_variables                                      |      0.00 |
| performance_schema | events_statements_summary_by_program                |      0.00 |
| performance_schema | metadata_locks                                      |      0.00 |
| information_schema | KEYWORDS                                            |      0.00 |
| performance_schema | table_io_waits_summary_by_table                      |      0.00 |
| information_schema | INNODB_SYS_DATAFILES                                |      0.00 |
| information_schema | INNODB_SYS_TABLESPACES                              |      0.00 |
| performance_schema | events_statements_history                            |      0.00 |
| performance_schema | memory_summary_by_account_by_event_name              |      0.00 |
| information_schema | ENGINES                                              |      0.00 |
| information_schema | INNODB_FT_DEFAULT_STOPWORD                          |      0.00 |
| performance_schema | events_stages_summary_by_host_by_event_name          |      0.00 |
| performance_schema | file_summary_by_event_name                          |      0.00 |
| information_schema | COLLATIONS                                          |      0.00 |
| performance_schema | status_by_host                                      |      0.00 |
| information_schema | INNODB_FT_INDEX_CACHE                                |      0.00 |
| performance_schema | cond_instances                                      |      0.00 |
| performance_schema | events_waits_summary_by_instance                    |      0.00 |
| performance_schema | setup_timers                                        |      0.00 |
| information_schema | INNODB_FT_INDEX_TABLE                                |      0.00 |
| performance_schema | events_waits_current                                |      0.00 |
| performance_schema | session_status                                      |      0.00 |
| information_schema | SCHEMA_PRIVILEGES                                    |      0.00 |
| information_schema | THREAD_POOL_QUEUES                                  |      0.00 |
| performance_schema | events_transactions_summary_by_account_by_event_name |      0.00 |
| performance_schema | replication_applier_status_by_coordinator            |      0.00 |
| information_schema | INNODB_METRICS                                      |      0.00 |
| information_schema | INNODB_FT_DELETED                                    |      0.00 |
| performance_schema | events_statements_summary_by_user_by_event_name      |      0.00 |
| performance_schema | objects_summary_global_by_type                      |      0.00 |
| information_schema | KEY_COLUMN_USAGE                                    |      0.00 |
| performance_schema | threads                                              |      0.00 |
| information_schema | INNODB_SYS_TABLESTATS                                |      0.00 |
| information_schema | INNODB_SYS_INDEXES                                  |      0.00 |
| performance_schema | events_statements_summary_by_account_by_event_name  |      0.00 |
| performance_schema | memory_summary_by_thread_by_event_name              |      0.00 |
| information_schema | FILES                                                |      0.00 |
| performance_schema | status_by_user                                      |      0.00 |
| information_schema | INNODB_SYS_COLUMNS                                  |      0.00 |
| performance_schema | events_stages_summary_by_user_by_event_name          |      0.00 |
| performance_schema | global_status                                        |      0.00 |
| information_schema | INNODB_FT_BEING_DELETED                              |      0.00 |
| performance_schema | events_stages_history                                |      0.00 |
| performance_schema | events_waits_summary_by_user_by_event_name          |      0.00 |
| information_schema | APPLICABLE_ROLES                                    |      0.00 |
| performance_schema | socket_summary_by_event_name                        |      0.00 |
| information_schema | TABLESPACES                                          |      0.00 |
| information_schema | THREAD_POOL_GROUPS                                  |      0.00 |
| performance_schema | events_waits_history_long                            |      0.00 |
| performance_schema | setup_consumers                                      |      0.00 |
| information_schema | SESSION_VARIABLES                                    |      0.00 |
| information_schema | INNODB_SYS_FIELDS                                    |      0.00 |
| mysql              | general_log                                          |      0.00 |
| performance_schema | events_transactions_summary_by_thread_by_event_name  |      0.00 |
| performance_schema | rwlock_instances                                    |      0.00 |
| information_schema | REFERENTIAL_CONSTRAINTS                              |      0.00 |
| information_schema | INNODB_CMP                                          |      0.00 |
| performance_schema | events_transactions_current                          |      0.00 |
| performance_schema | prepared_statements_instances                        |      0.00 |
| performance_schema | users                                                |      0.00 |
| information_schema | INNODB_BUFFER_PAGE                                  |      0.00 |
| information_schema | INNODB_MUTEXES                                      |      0.00 |
| performance_schema | events_statements_summary_by_host_by_event_name      |      0.00 |
| performance_schema | memory_summary_global_by_event_name                  |      0.00 |
| information_schema | GLOBAL_VARIABLES                                    |      0.00 |
| performance_schema | table_io_waits_summary_by_index_usage                |      0.00 |
| information_schema | INDEX_STATISTICS                                    |      0.00 |
| information_schema | USER_STATISTICS                                      |      0.00 |
| performance_schema | events_statements_current                            |      0.00 |
| performance_schema | hosts                                                |      0.00 |
| information_schema | ENABLED_ROLES                                        |      0.00 |
| information_schema | INNODB_CMPMEM_RESET                                  |      0.00 |
| performance_schema | events_stages_summary_by_account_by_event_name      |      0.00 |
| performance_schema | file_instances                                      |      0.00 |
| performance_schema | status_by_account                                    |      0.00 |
| information_schema | TABLE_PRIVILEGES                                    |      0.00 |
| information_schema | INNODB_SYS_FOREIGN_COLS                              |      0.00 |
| performance_schema | accounts                                            |      0.00 |
| performance_schema | events_waits_summary_by_host_by_event_name          |      0.00 |
| performance_schema | setup_objects                                        |      0.00 |
| information_schema | SQL_FUNCTIONS                                        |      0.00 |
| information_schema | INNODB_LOCKS                                        |      0.00 |
| performance_schema | events_transactions_summary_global_by_event_name    |      0.00 |
| performance_schema | session_connect_attrs                                |      0.00 |
| information_schema | SCHEMATA                                            |      0.00 |
| information_schema | INNODB_CMP_RESET                                    |      0.00 |
| performance_schema | events_transactions_history_long                    |      0.00 |
| performance_schema | replication_applier_status                          |      0.00 |
| information_schema | INNODB_CMP_PER_INDEX                                |      0.00 |
| information_schema | INNODB_TABLESPACES_ENCRYPTION                        |      0.00 |
| performance_schema | events_statements_summary_by_thread_by_event_name    |      0.00 |
| performance_schema | mutex_instances                                      |      0.00 |
| information_schema | KEY_CACHES                                          |      0.00 |
| performance_schema | table_lock_waits_summary_by_table                    |      0.00 |
| information_schema | GEOMETRY_COLUMNS                                    |      0.00 |
| information_schema | INNODB_SYS_VIRTUAL                                  |      0.00 |
| performance_schema | events_statements_history_long                      |      0.00 |
| performance_schema | memory_summary_by_host_by_event_name                |      0.00 |
| performance_schema | status_by_thread                                    |      0.00 |
| information_schema | USER_PRIVILEGES                                      |      0.00 |
| information_schema | INNODB_SYS_TABLES                                    |      0.00 |
| performance_schema | events_stages_summary_by_thread_by_event_name        |      0.00 |
| performance_schema | file_summary_by_instance                            |      0.00 |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY                |      0.00 |
| information_schema | INNODB_BUFFER_POOL_STATS                            |      0.00 |
| performance_schema | events_stages_current                                |      0.00 |
| performance_schema | events_waits_summary_by_thread_by_event_name        |      0.00 |
| performance_schema | socket_instances                                    |      0.00 |
| information_schema | TABLES                                              |      0.00 |
| mysql              | user                                                |      NULL |
+--------------------+------------------------------------------------------+-----------+
206 rows in set (0.004 sec)
 
MariaDB [(none)]> select table_name as 'table',
    ->    table_rows as 'rows'
    -> from information_schema.tables
    -> where table_schema = 'postfixadmin'
    -> and table_type = 'BASE TABLE'
    -> order by table_rows desc;
+-----------------------+------+
| table                | rows |
+-----------------------+------+
| log                  |  87 |
| alias                |  37 |
| mailbox              |  15 |
| domain                |    6 |
| alias_domain          |    4 |
| domain_admins        |    1 |
| config                |    1 |
| admin                |    1 |
| quota2                |    0 |
| vacation              |    0 |
| vacation_notification |    0 |
| quota                |    0 |
| fetchmail            |    0 |
+-----------------------+------+
13 rows in set (0.000 sec)
 
MariaDB [(none)]> SELECT User, Db, Host from mysql.db;
+--------------+--------------+-----------+
| User        | Db          | Host      |
+--------------+--------------+-----------+
| postfixadmin | postfixadmin | localhost |
+--------------+--------------+-----------+
1 row in set (0.001 sec)
 
MariaDB [(none)]> SELECT host, user, password FROM mysql.user;
+-----------+--------------+-------------------------------------------+
| Host      | User        | Password                                  |
+-----------+--------------+-------------------------------------------+
| localhost | root        | *7AD796BBF736F50732431C588299E7E53C812457 |
| 127.0.0.1 | root        | *7AD796BBF736F50732431C588299E7E53C812457 |
| ::1      | root        | *7AD796BBF736F50732431C588299E7E53C812457 |
| %        | zbx_monitor  | *2320CC744BAED7546ABFD2E1F206833E35DBB68C |
| localhost | postfixadmin | *55B9A0BA81B08B06C1FA3B6BD1AE3B7AF386AA4E |
| localhost | mariadb.sys  |                                          |
+-----------+--------------+-------------------------------------------+
6 rows in set (0.001 sec)
 
MariaDB [(none)]> DESC mysql.user;
+------------------------+---------------------+------+-----+----------+-------+
| Field                  | Type                | Null | Key | Default  | Extra |
+------------------------+---------------------+------+-----+----------+-------+
| Host                  | char(60)            | NO  |    |          |      |
| User                  | char(80)            | NO  |    |          |      |
| Password              | longtext            | YES  |    | NULL    |      |
| Select_priv            | varchar(1)          | YES  |    | NULL    |      |
| Insert_priv            | varchar(1)          | YES  |    | NULL    |      |
| Update_priv            | varchar(1)          | YES  |    | NULL    |      |
| Delete_priv            | varchar(1)          | YES  |    | NULL    |      |
| Create_priv            | varchar(1)          | YES  |    | NULL    |      |
| Drop_priv              | varchar(1)          | YES  |    | NULL    |      |
| Reload_priv            | varchar(1)          | YES  |    | NULL    |      |
| Shutdown_priv          | varchar(1)          | YES  |    | NULL    |      |
| Process_priv          | varchar(1)          | YES  |    | NULL    |      |
| File_priv              | varchar(1)          | YES  |    | NULL    |      |
| Grant_priv            | varchar(1)          | YES  |    | NULL    |      |
| References_priv        | varchar(1)          | YES  |    | NULL    |      |
| Index_priv            | varchar(1)          | YES  |    | NULL    |      |
| Alter_priv            | varchar(1)          | YES  |    | NULL    |      |
| Show_db_priv          | varchar(1)          | YES  |    | NULL    |      |
| Super_priv            | varchar(1)          | YES  |    | NULL    |      |
| Create_tmp_table_priv  | varchar(1)          | YES  |    | NULL    |      |
| Lock_tables_priv      | varchar(1)          | YES  |    | NULL    |      |
| Execute_priv          | varchar(1)          | YES  |    | NULL    |      |
| Repl_slave_priv        | varchar(1)          | YES  |    | NULL    |      |
| Repl_client_priv      | varchar(1)          | YES  |    | NULL    |      |
| Create_view_priv      | varchar(1)          | YES  |    | NULL    |      |
| Show_view_priv        | varchar(1)          | YES  |    | NULL    |      |
| Create_routine_priv    | varchar(1)          | YES  |    | NULL    |      |
| Alter_routine_priv    | varchar(1)          | YES  |    | NULL    |      |
| Create_user_priv      | varchar(1)          | YES  |    | NULL    |      |
| Event_priv            | varchar(1)          | YES  |    | NULL    |      |
| Trigger_priv          | varchar(1)          | YES  |    | NULL    |      |
| Create_tablespace_priv | varchar(1)          | YES  |    | NULL    |      |
| Delete_history_priv    | varchar(1)          | YES  |    | NULL    |      |
| ssl_type              | varchar(9)          | YES  |    | NULL    |      |
| ssl_cipher            | longtext            | NO  |    |          |      |
| x509_issuer            | longtext            | NO  |    |          |      |
| x509_subject          | longtext            | NO  |    |          |      |
| max_questions          | bigint(20) unsigned | NO  |    | 0        |      |
| max_updates            | bigint(20) unsigned | NO  |    | 0        |      |
| max_connections        | bigint(20) unsigned | NO  |    | 0        |      |
| max_user_connections  | bigint(21)          | NO  |    | 0        |      |
| plugin                | longtext            | NO  |    |          |      |
| authentication_string  | longtext            | NO  |    |          |      |
| password_expired      | varchar(1)          | NO  |    |          |      |
| is_role                | varchar(1)          | YES  |    | NULL    |      |
| default_role          | longtext            | NO  |    |          |      |
| max_statement_time    | decimal(12,6)      | NO  |    | 0.000000 |      |
+------------------------+---------------------+------+-----+----------+-------+
47 rows in set (0.001 sec)
 
MariaDB [(none)]> SELECT User, Db, Host from mysql.db;
+--------------+--------------+-----------+
| User        | Db          | Host      |
+--------------+--------------+-----------+
| postfixadmin | postfixadmin | localhost |
+--------------+--------------+-----------+
1 row in set (0.000 sec)
 
MariaDB [(none)]>  select distinct concat('SHOW GRANTS FOR ', QUOTE(user),'@', QUOTE(host), ';') as query from mysql.user;
+---------------------------------------------+
| query                                      |
+---------------------------------------------+
| SHOW GRANTS FOR 'zbx_monitor'@'%';          |
| SHOW GRANTS FOR 'root'@'127.0.0.1';        |
| SHOW GRANTS FOR 'root'@'::1';              |
| SHOW GRANTS FOR 'mariadb.sys'@'localhost';  |
| SHOW GRANTS FOR 'postfixadmin'@'localhost'; |
| SHOW GRANTS FOR 'root'@'localhost';        |
+---------------------------------------------+
6 rows in set (0.000 sec)
 
MariaDB [(none)]> SHOW GRANTS FOR 'zbx_monitor'@'%';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for zbx_monitor@%                                                                                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT PROCESS, SHOW DATABASES, BINLOG MONITOR, SHOW VIEW, SLAVE MONITOR ON *.* TO `zbx_monitor`@`%` IDENTIFIED BY PASSWORD '*2320CC744BAED7546ABFD2E1F206833E35DBB68C' |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
 
MariaDB [(none)]>  SHOW GRANTS FOR 'root'@'127.0.0.1';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@127.0.0.1                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`127.0.0.1` IDENTIFIED BY PASSWORD '*7AD796BBF736F50732431C588299E7E53C812457' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
 
MariaDB [(none)]>  SHOW GRANTS FOR 'root'@'::1';
+----------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@::1                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`::1` IDENTIFIED BY PASSWORD '*7AD796BBF736F50732431C588299E7E53C812457' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
 
MariaDB [(none)]> SHOW GRANTS FOR 'mariadb.sys'@'localhost';
+----------------------------------------------------------------------------+
| Grants for mariadb.sys@localhost                                          |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mariadb.sys`@`localhost`                            |
| GRANT SELECT, DELETE ON `mysql`.`global_priv` TO `mariadb.sys`@`localhost` |
+----------------------------------------------------------------------------+
2 rows in set (0.000 sec)
 
MariaDB [(none)]> SHOW GRANTS FOR 'postfixadmin'@'localhost';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for postfixadmin@localhost                                                                                                                                                                                                                    |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `postfixadmin`@`localhost` IDENTIFIED BY PASSWORD '*55B9A0BA81B08B06C1FA3B6BD1AE3B7AF386AA4E'                                                                                                                                  |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `postfixadmin`.* TO `postfixadmin`@`localhost` |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
 
MariaDB [(none)]> SHOW GRANTS FOR 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*7AD796BBF736F50732431C588299E7E53C812457' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
 
MariaDB [(none)]>
</syntaxhighlight>
 
==Migrations==

Revision as of 16:09, 19 August 2025

Stuff about MariaDB, includign deployment, monitoring, backup and more.


1 Deployments[edit | edit source]

MariaDB is deployed in support of applicaitons on these KitsNet systems:

2 Monitoring[edit | edit source]

MySQL and MariaDB databases are monitored regularly by Zabbix by aleveraging the MySQL by Zabbix agent 2 capability. this depends upon a user being created within the database server to suport monitoring t, then setting appropriate macros for the server profile on the Zabbix servers.

  1. Create a MySQL user for monitoring
    CREATE USER 'zbx_monitor'@'%' IDENTIFIED BY '<password>';
GRANT REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'%';
  2. Set in the {$MYSQL.DSN} macro the data source name of the MySQL instance either session name from Zabbix agent 2 configuration file or URI. Examples: MySQL1, tcp://localhost:3306, tcp://172.16.0.10, unix:/var/run/mysql.sock For more information about MySQL Unix socket file, see the MySQL documentation https://dev.mysql.com/doc/refman/8.0/en/problems-with-mysql-sock.html.
  3. f you had set URI in the {$MYSQL.DSN}, define the user name and password in host macros ({$MYSQL.USER} and {$MYSQL.PASSWORD}). Leave macros {$MYSQL.USER} and {$MYSQL.PASSWORD} empty if you use a session name. Set the user name and password in the Plugins.Mysql.<...> section of your Zabbix agent 2 configuration file. For more information about configuring the Zabbix MySQL plugin, see the documentation https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/src/go/plugins/mysql/README.md.

3 Operations Tooling[edit | edit source]

Standardized scripts are deployed in KitsNet to support health checks of MariaDB databases and daily backup operations. These scripts are integrated witht he rest of the KitsNet operational infrastcuture.

3.1 Health checks[edit | edit source]

I developed the CheckMariaDBIntegrity script with help from ChatGPT. The design remit was for a script which could be used regularly to verify database integrity and lolk for corruption. The script has been incorporated into the KNsysmisc package installed on all KitsNet Linux servers. While the script can be run standalone, it is intended to be executed nightly in a cron job. The best way to implmentet is to create a CheckMariaDBIntegrity-server script that will invoke the CheckMariaDBIntegrity script with appropriate user and password arguments. The CheckMariaDBIntegrity-server script should have a protection mask applied to it that restricts access to root only. In general, the database integrity check should eb scheduled to run afdter the daily backup is completed.

The CheckMariaDBIntegrity script is designed to run silently if there are no issues detected, though all activity is noted in syslog with appropriate severities under the daemon facility and tagged with MariaDBIntegrityCheck . This encoding ensures that results of the integrity check will generate events in ConsoleWorks by default when required. The two failure events logged by the scrip are:

  • ERROR: Could not connect to MariaDB with provided credentials.
  • TABLE CHECK ISSUE

3.2 Backup[edit | edit source]

The MariaDB instances in KitsNet are small enough that simple dump operations are sufficient. In support of this, the backup_localMySQL was developed to execute a database dump to disk. A server specific script woudl be created to set the required arguments for the backup, including access credentials and the output directory for the dump. This script would be specified as a pre-backup script in the definition of the Veeam data backup job for the server, so that the dump of the database is taken immediaztely prior to the server backup operation. Should a restore operation be necessary, this could be preformed directrly from the on-disk directory where the dumps are accumulated. If the dump to be restored is outside this range, the dump coiuld be restored from a Veeam backup and then the dump could be restored.

3.2.1 Restore operations[edit | edit source]

Restoration of the database dump can be used to migrate a database, recover from disk corruption or restore a crash-safe copy of the database backup following recovery from a disk failre. In asll these cases, the database must first exist then have the dump written over it. In cases outside of migration, it is probably best to drop the existing tables before importing the dump. Refer to the backup_localMySQL-server script for credential and database information.

  1. Restore dumpfile from backup (if necessary). Use Veeam or other means to bring back the generation of the dump file, probably to /var/lib/mysql/dumps . If moving around the dump file, care needs to be taken that the contents of the dump do not get the envdoing changed in transit. Source for the dumps could be the live collection of Veeam backups from the BKUP server, monthly server backups to external drives, or elsewhere. Each dump is a complete set, so no incrementals or journal replays need be perfromed
  2. Stop applicaiton. If there is an applicaiton or service dependant upon the database , use systemctl of other appropriate means to stop it while the the database is being reloaded.
  3. Drop database and recreate (if necesary). Drop exisitng appliciaotn database(s). For the Zabbix installation, it would look like:
    # mysql -uroot -p
mysql> DROP DATABASE IF EXISTS zabbix;
mysql> CREATE DATABASE zabbix CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
mysql> CREATE USER 'zabbix@localhost' IDENTIFIED BY 'your_password';
mysql> GRANT ALL PRIVILEGES ON zabbix.* TO 'zabbix'@'localhost';
mysql> quit;
  4. Restore the database from the dump. Command will generally look like: # mysql -u user -p DB_name < /var/lib/mysql/dumps/DB_name.Day.sql
  5. Reboot server. This will cause the appliciaton to be restarted with the correct data present.

4 Support[edit | edit source]

Here are some handy commands to check out a MariaDB server

4.1 Execute series of database queries to check for match on DB accounts, tables and row counts[edit | edit source]

[psmode@ciroc ~]$ sudo  mariadb-check --analyze --all-databases -uroot -p
Enter password:
mysql.column_stats                                 Table is already up to date
mysql.columns_priv                                 Table is already up to date
mysql.db                                           OK
mysql.event                                        Table is already up to date
mysql.func                                         Table is already up to date
mysql.global_priv                                  OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 Table is already up to date
mysql.help_relation                                Table is already up to date
mysql.help_topic                                   Table is already up to date
mysql.index_stats                                  Table is already up to date
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.plugin                                       Table is already up to date
mysql.proc                                         Table is already up to date
mysql.procs_priv                                   Table is already up to date
mysql.proxies_priv                                 OK
mysql.roles_mapping                                Table is already up to date
mysql.servers                                      Table is already up to date
mysql.table_stats                                  Table is already up to date
mysql.tables_priv                                  OK
mysql.time_zone                                    Table is already up to date
mysql.time_zone_leap_second                        Table is already up to date
mysql.time_zone_name                               Table is already up to date
mysql.time_zone_transition                         Table is already up to date
mysql.time_zone_transition_type                    Table is already up to date
mysql.transaction_registry                         OK
postfixadmin.admin                                 OK
postfixadmin.alias                                 OK
postfixadmin.alias_domain                          OK
postfixadmin.config                                OK
postfixadmin.domain                                OK
postfixadmin.domain_admins                         OK
postfixadmin.fetchmail                             OK
postfixadmin.log                                   OK
postfixadmin.mailbox                               OK
postfixadmin.quota                                 OK
postfixadmin.quota2                                OK
postfixadmin.vacation                              OK
postfixadmin.vacation_notification                 OK
[psmode@ciroc ~]$ mariadb -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.5.22-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| dumps              |
| information_schema |
| mysql              |
| performance_schema |
| postfixadmin       |
+--------------------+
5 rows in set (0.000 sec)

MariaDB [(none)]> SHOW TABLES;
ERROR 1046 (3D000): No database selected
MariaDB [(none)]> ^DBye
[psmode@ciroc ~]$ mariadb-show -uroot -p
Enter password:
+--------------------+
|     Databases      |
+--------------------+
| dumps              |
| information_schema |
| mysql              |
| performance_schema |
| postfixadmin       |
+--------------------+
[psmode@ciroc ~]$ mariadb-show -uroot -p dumps
Enter password:
Database: dumps
+--------+
| Tables |
+--------+
+--------+
[psmode@ciroc ~]$ mariadb-show -uroot -p information_schema
Enter password:
Database: information_schema
+---------------------------------------+
|                Tables                 |
+---------------------------------------+
| ALL_PLUGINS                           |
| APPLICABLE_ROLES                      |
| CHARACTER_SETS                        |
| CHECK_CONSTRAINTS                     |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENABLED_ROLES                         |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEYWORDS                              |
| KEY_CACHES                            |
| KEY_COLUMN_USAGE                      |
| OPTIMIZER_TRACE                       |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| SQL_FUNCTIONS                         |
| SYSTEM_VARIABLES                      |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| CLIENT_STATISTICS                     |
| INDEX_STATISTICS                      |
| INNODB_SYS_DATAFILES                  |
| GEOMETRY_COLUMNS                      |
| INNODB_SYS_TABLESTATS                 |
| SPATIAL_REF_SYS                       |
| INNODB_BUFFER_PAGE                    |
| INNODB_TRX                            |
| INNODB_CMP_PER_INDEX                  |
| INNODB_METRICS                        |
| INNODB_LOCK_WAITS                     |
| INNODB_CMP                            |
| THREAD_POOL_WAITS                     |
| INNODB_CMP_RESET                      |
| THREAD_POOL_QUEUES                    |
| TABLE_STATISTICS                      |
| INNODB_SYS_FIELDS                     |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_LOCKS                          |
| INNODB_FT_INDEX_TABLE                 |
| INNODB_CMPMEM                         |
| THREAD_POOL_GROUPS                    |
| INNODB_CMP_PER_INDEX_RESET            |
| INNODB_SYS_FOREIGN_COLS               |
| INNODB_FT_INDEX_CACHE                 |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_FT_BEING_DELETED               |
| INNODB_SYS_FOREIGN                    |
| INNODB_CMPMEM_RESET                   |
| INNODB_FT_DEFAULT_STOPWORD            |
| INNODB_SYS_TABLES                     |
| INNODB_SYS_COLUMNS                    |
| INNODB_FT_CONFIG                      |
| USER_STATISTICS                       |
| INNODB_SYS_TABLESPACES                |
| INNODB_SYS_VIRTUAL                    |
| INNODB_SYS_INDEXES                    |
| INNODB_SYS_SEMAPHORE_WAITS            |
| INNODB_MUTEXES                        |
| user_variables                        |
| INNODB_TABLESPACES_ENCRYPTION         |
| INNODB_FT_DELETED                     |
| THREAD_POOL_STATS                     |
+---------------------------------------+
[psmode@ciroc ~]$ mariadb-show -uroot -p mysql
Enter password:
Database: mysql
+---------------------------+
|          Tables           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| global_priv               |
| gtid_slave_pos            |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| index_stats               |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| roles_mapping             |
| servers                   |
| slow_log                  |
| table_stats               |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| transaction_registry      |
| user                      |
+---------------------------+
[psmode@ciroc ~]$ mariadb-show -uroot -p performance_schema
Enter password:
Database: performance_schema
+------------------------------------------------------+
|                        Tables                        |
+------------------------------------------------------+
| accounts                                             |
| cond_instances                                       |
| events_stages_current                                |
| events_stages_history                                |
| events_stages_history_long                           |
| events_stages_summary_by_account_by_event_name       |
| events_stages_summary_by_host_by_event_name          |
| events_stages_summary_by_thread_by_event_name        |
| events_stages_summary_by_user_by_event_name          |
| events_stages_summary_global_by_event_name           |
| events_statements_current                            |
| events_statements_history                            |
| events_statements_history_long                       |
| events_statements_summary_by_account_by_event_name   |
| events_statements_summary_by_digest                  |
| events_statements_summary_by_host_by_event_name      |
| events_statements_summary_by_program                 |
| events_statements_summary_by_thread_by_event_name    |
| events_statements_summary_by_user_by_event_name      |
| events_statements_summary_global_by_event_name       |
| events_transactions_current                          |
| events_transactions_history                          |
| events_transactions_history_long                     |
| events_transactions_summary_by_account_by_event_name |
| events_transactions_summary_by_host_by_event_name    |
| events_transactions_summary_by_thread_by_event_name  |
| events_transactions_summary_by_user_by_event_name    |
| events_transactions_summary_global_by_event_name     |
| events_waits_current                                 |
| events_waits_history                                 |
| events_waits_history_long                            |
| events_waits_summary_by_account_by_event_name        |
| events_waits_summary_by_host_by_event_name           |
| events_waits_summary_by_instance                     |
| events_waits_summary_by_thread_by_event_name         |
| events_waits_summary_by_user_by_event_name           |
| events_waits_summary_global_by_event_name            |
| file_instances                                       |
| file_summary_by_event_name                           |
| file_summary_by_instance                             |
| global_status                                        |
| host_cache                                           |
| hosts                                                |
| memory_summary_by_account_by_event_name              |
| memory_summary_by_host_by_event_name                 |
| memory_summary_by_thread_by_event_name               |
| memory_summary_by_user_by_event_name                 |
| memory_summary_global_by_event_name                  |
| metadata_locks                                       |
| mutex_instances                                      |
| objects_summary_global_by_type                       |
| performance_timers                                   |
| prepared_statements_instances                        |
| replication_applier_configuration                    |
| replication_applier_status                           |
| replication_applier_status_by_coordinator            |
| replication_connection_configuration                 |
| rwlock_instances                                     |
| session_account_connect_attrs                        |
| session_connect_attrs                                |
| session_status                                       |
| setup_actors                                         |
| setup_consumers                                      |
| setup_instruments                                    |
| setup_objects                                        |
| setup_timers                                         |
| socket_instances                                     |
| socket_summary_by_event_name                         |
| socket_summary_by_instance                           |
| status_by_account                                    |
| status_by_host                                       |
| status_by_thread                                     |
| status_by_user                                       |
| table_handles                                        |
| table_io_waits_summary_by_index_usage                |
| table_io_waits_summary_by_table                      |
| table_lock_waits_summary_by_table                    |
| threads                                              |
| user_variables_by_thread                             |
| users                                                |
+------------------------------------------------------+
[psmode@ciroc ~]$ mariadb-show -uroot -p postfixadmin
Enter password:
Database: postfixadmin
+-----------------------+
|        Tables         |
+-----------------------+
| admin                 |
| alias                 |
| alias_domain          |
| config                |
| domain                |
| domain_admins         |
| fetchmail             |
| log                   |
| mailbox               |
| quota                 |
| quota2                |
| vacation              |
| vacation_notification |
+-----------------------+

[psmode@ciroc ~]$ mariadb -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.5.22-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show full tables from postfixadmin
    -> ;
+------------------------+------------+
| Tables_in_postfixadmin | Table_type |
+------------------------+------------+
| admin                  | BASE TABLE |
| alias                  | BASE TABLE |
| alias_domain           | BASE TABLE |
| config                 | BASE TABLE |
| domain                 | BASE TABLE |
| domain_admins          | BASE TABLE |
| fetchmail              | BASE TABLE |
| log                    | BASE TABLE |
| mailbox                | BASE TABLE |
| quota                  | BASE TABLE |
| quota2                 | BASE TABLE |
| vacation               | BASE TABLE |
| vacation_notification  | BASE TABLE |
+------------------------+------------+
13 rows in set (0.000 sec)

MariaDB [(none)]> SELECT table_schema as `DB`, table_name AS `Table`,
    -> ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)`
    -> FROM information_schema.TABLES
    -> ORDER BY (data_length + index_length) DESC;
+--------------------+------------------------------------------------------+-----------+
| DB                 | Table                                                | Size (MB) |
+--------------------+------------------------------------------------------+-----------+
| mysql              | help_topic                                           |      0.51 |
| mysql              | help_relation                                        |      0.08 |
| mysql              | transaction_registry                                 |      0.06 |
| mysql              | help_keyword                                         |      0.05 |
| postfixadmin       | log                                                  |      0.05 |
| postfixadmin       | alias_domain                                         |      0.05 |
| mysql              | db                                                   |      0.04 |
| mysql              | tables_priv                                          |      0.04 |
| mysql              | proxies_priv                                         |      0.04 |
| mysql              | help_category                                        |      0.04 |
| postfixadmin       | mailbox                                              |      0.03 |
| postfixadmin       | config                                               |      0.03 |
| postfixadmin       | domain_admins                                        |      0.03 |
| mysql              | global_priv                                          |      0.03 |
| postfixadmin       | vacation                                             |      0.03 |
| postfixadmin       | alias                                                |      0.03 |
| mysql              | gtid_slave_pos                                       |      0.02 |
| information_schema | PLUGINS                                              |      0.02 |
| mysql              | time_zone                                            |      0.02 |
| information_schema | EVENTS                                               |      0.02 |
| mysql              | roles_mapping                                        |      0.02 |
| mysql              | innodb_table_stats                                   |      0.02 |
| information_schema | ALL_PLUGINS                                          |      0.02 |
| postfixadmin       | quota2                                               |      0.02 |
| mysql              | func                                                 |      0.02 |
| postfixadmin       | admin                                                |      0.02 |
| information_schema | OPTIMIZER_TRACE                                      |      0.02 |
| mysql              | time_zone_name                                       |      0.02 |
| mysql              | proc                                                 |      0.02 |
| postfixadmin       | vacation_notification                                |      0.02 |
| information_schema | ROUTINES                                             |      0.02 |
| mysql              | columns_priv                                         |      0.02 |
| information_schema | PARTITIONS                                           |      0.02 |
| mysql              | time_zone_transition_type                            |      0.02 |
| information_schema | TRIGGERS                                             |      0.02 |
| mysql              | innodb_index_stats                                   |      0.02 |
| information_schema | SYSTEM_VARIABLES                                     |      0.02 |
| postfixadmin       | quota                                                |      0.02 |
| mysql              | event                                                |      0.02 |
| postfixadmin       | domain                                               |      0.02 |
| information_schema | PROCESSLIST                                          |      0.02 |
| mysql              | time_zone_leap_second                                |      0.02 |
| mysql              | servers                                              |      0.02 |
| information_schema | COLUMNS                                              |      0.02 |
| information_schema | VIEWS                                                |      0.02 |
| mysql              | plugin                                               |      0.02 |
| postfixadmin       | fetchmail                                            |      0.02 |
| mysql              | column_stats                                         |      0.02 |
| information_schema | PARAMETERS                                           |      0.02 |
| mysql              | time_zone_transition                                 |      0.02 |
| mysql              | table_stats                                          |      0.02 |
| mysql              | procs_priv                                           |      0.02 |
| information_schema | CHECK_CONSTRAINTS                                    |      0.02 |
| mysql              | index_stats                                          |      0.02 |
| performance_schema | events_transactions_summary_global_by_event_name     |      0.00 |
| performance_schema | session_connect_attrs                                |      0.00 |
| information_schema | SCHEMATA                                             |      0.00 |
| information_schema | INNODB_LOCKS                                         |      0.00 |
| performance_schema | events_transactions_history_long                     |      0.00 |
| performance_schema | replication_applier_status                           |      0.00 |
| information_schema | INNODB_CMP_RESET                                     |      0.00 |
| performance_schema | events_statements_summary_by_thread_by_event_name    |      0.00 |
| performance_schema | mutex_instances                                      |      0.00 |
| information_schema | KEY_CACHES                                           |      0.00 |
| information_schema | INNODB_CMP_PER_INDEX                                 |      0.00 |
| information_schema | INNODB_TABLESPACES_ENCRYPTION                        |      0.00 |
| performance_schema | events_statements_history_long                       |      0.00 |
| performance_schema | memory_summary_by_host_by_event_name                 |      0.00 |
| performance_schema | table_lock_waits_summary_by_table                    |      0.00 |
| information_schema | GEOMETRY_COLUMNS                                     |      0.00 |
| information_schema | INNODB_SYS_VIRTUAL                                   |      0.00 |
| performance_schema | events_stages_summary_by_thread_by_event_name        |      0.00 |
| performance_schema | file_summary_by_instance                             |      0.00 |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY                |      0.00 |
| performance_schema | status_by_thread                                     |      0.00 |
| information_schema | USER_PRIVILEGES                                      |      0.00 |
| information_schema | INNODB_SYS_TABLES                                    |      0.00 |
| performance_schema | events_stages_current                                |      0.00 |
| performance_schema | events_waits_summary_by_thread_by_event_name         |      0.00 |
| performance_schema | socket_instances                                     |      0.00 |
| information_schema | TABLES                                               |      0.00 |
| information_schema | INNODB_BUFFER_POOL_STATS                             |      0.00 |
| performance_schema | events_waits_history                                 |      0.00 |
| performance_schema | setup_actors                                         |      0.00 |
| information_schema | SESSION_STATUS                                       |      0.00 |
| information_schema | INNODB_CMPMEM                                        |      0.00 |
| performance_schema | events_transactions_summary_by_host_by_event_name    |      0.00 |
| performance_schema | replication_connection_configuration                 |      0.00 |
| information_schema | PROFILING                                            |      0.00 |
| information_schema | TABLE_STATISTICS                                     |      0.00 |
| information_schema | THREAD_POOL_STATS                                    |      0.00 |
| performance_schema | events_statements_summary_global_by_event_name       |      0.00 |
| performance_schema | performance_timers                                   |      0.00 |
| information_schema | INNODB_LOCK_WAITS                                    |      0.00 |
| performance_schema | events_statements_summary_by_digest                  |      0.00 |
| performance_schema | memory_summary_by_user_by_event_name                 |      0.00 |
| information_schema | GLOBAL_STATUS                                        |      0.00 |
| performance_schema | user_variables_by_thread                             |      0.00 |
| information_schema | SPATIAL_REF_SYS                                      |      0.00 |
| information_schema | INNODB_SYS_SEMAPHORE_WAITS                           |      0.00 |
| mysql              | slow_log                                             |      0.00 |
| performance_schema | events_stages_summary_global_by_event_name           |      0.00 |
| performance_schema | host_cache                                           |      0.00 |
| information_schema | COLUMN_PRIVILEGES                                    |      0.00 |
| performance_schema | table_handles                                        |      0.00 |
| information_schema | CLIENT_STATISTICS                                    |      0.00 |
| information_schema | INNODB_FT_CONFIG                                     |      0.00 |
| performance_schema | events_stages_history_long                           |      0.00 |
| performance_schema | events_waits_summary_global_by_event_name            |      0.00 |
| information_schema | CHARACTER_SETS                                       |      0.00 |
| performance_schema | socket_summary_by_instance                           |      0.00 |
| information_schema | TABLE_CONSTRAINTS                                    |      0.00 |
| information_schema | INNODB_SYS_FOREIGN                                   |      0.00 |
| performance_schema | events_waits_summary_by_account_by_event_name        |      0.00 |
| performance_schema | setup_instruments                                    |      0.00 |
| information_schema | STATISTICS                                           |      0.00 |
| information_schema | INNODB_CMP_PER_INDEX_RESET                           |      0.00 |
| performance_schema | events_transactions_summary_by_user_by_event_name    |      0.00 |
| performance_schema | session_account_connect_attrs                        |      0.00 |
| information_schema | INNODB_BUFFER_PAGE_LRU                               |      0.00 |
| performance_schema | events_transactions_history                          |      0.00 |
| performance_schema | replication_applier_configuration                    |      0.00 |
| information_schema | THREAD_POOL_WAITS                                    |      0.00 |
| performance_schema | events_statements_summary_by_program                 |      0.00 |
| performance_schema | metadata_locks                                       |      0.00 |
| information_schema | KEYWORDS                                             |      0.00 |
| information_schema | INNODB_TRX                                           |      0.00 |
| information_schema | user_variables                                       |      0.00 |
| performance_schema | events_statements_history                            |      0.00 |
| performance_schema | memory_summary_by_account_by_event_name              |      0.00 |
| information_schema | ENGINES                                              |      0.00 |
| performance_schema | table_io_waits_summary_by_table                      |      0.00 |
| information_schema | INNODB_SYS_DATAFILES                                 |      0.00 |
| information_schema | INNODB_SYS_TABLESPACES                               |      0.00 |
| performance_schema | events_stages_summary_by_host_by_event_name          |      0.00 |
| performance_schema | file_summary_by_event_name                           |      0.00 |
| information_schema | COLLATIONS                                           |      0.00 |
| performance_schema | status_by_host                                       |      0.00 |
| information_schema | INNODB_FT_DEFAULT_STOPWORD                           |      0.00 |
| performance_schema | cond_instances                                       |      0.00 |
| performance_schema | events_waits_summary_by_instance                     |      0.00 |
| performance_schema | setup_timers                                         |      0.00 |
| information_schema | INNODB_FT_INDEX_CACHE                                |      0.00 |
| performance_schema | events_waits_current                                 |      0.00 |
| performance_schema | session_status                                       |      0.00 |
| information_schema | SCHEMA_PRIVILEGES                                    |      0.00 |
| information_schema | INNODB_FT_INDEX_TABLE                                |      0.00 |
| performance_schema | events_transactions_summary_by_account_by_event_name |      0.00 |
| performance_schema | replication_applier_status_by_coordinator            |      0.00 |
| information_schema | THREAD_POOL_QUEUES                                   |      0.00 |
| performance_schema | events_statements_summary_by_user_by_event_name      |      0.00 |
| performance_schema | objects_summary_global_by_type                       |      0.00 |
| information_schema | KEY_COLUMN_USAGE                                     |      0.00 |
| information_schema | INNODB_METRICS                                       |      0.00 |
| information_schema | INNODB_FT_DELETED                                    |      0.00 |
| performance_schema | events_statements_summary_by_account_by_event_name   |      0.00 |
| performance_schema | memory_summary_by_thread_by_event_name               |      0.00 |
| information_schema | FILES                                                |      0.00 |
| performance_schema | threads                                              |      0.00 |
| information_schema | INNODB_SYS_TABLESTATS                                |      0.00 |
| information_schema | INNODB_SYS_INDEXES                                   |      0.00 |
| performance_schema | events_stages_summary_by_user_by_event_name          |      0.00 |
| performance_schema | global_status                                        |      0.00 |
| performance_schema | status_by_user                                       |      0.00 |
| information_schema | INNODB_SYS_COLUMNS                                   |      0.00 |
| performance_schema | events_stages_history                                |      0.00 |
| performance_schema | events_waits_summary_by_user_by_event_name           |      0.00 |
| information_schema | APPLICABLE_ROLES                                     |      0.00 |
| performance_schema | socket_summary_by_event_name                         |      0.00 |
| information_schema | TABLESPACES                                          |      0.00 |
| information_schema | INNODB_FT_BEING_DELETED                              |      0.00 |
| performance_schema | events_waits_history_long                            |      0.00 |
| performance_schema | setup_consumers                                      |      0.00 |
| information_schema | SESSION_VARIABLES                                    |      0.00 |
| information_schema | THREAD_POOL_GROUPS                                   |      0.00 |
| mysql              | general_log                                          |      0.00 |
| performance_schema | events_transactions_summary_by_thread_by_event_name  |      0.00 |
| performance_schema | rwlock_instances                                     |      0.00 |
| information_schema | REFERENTIAL_CONSTRAINTS                              |      0.00 |
| information_schema | INNODB_SYS_FIELDS                                    |      0.00 |
| performance_schema | events_transactions_current                          |      0.00 |
| performance_schema | prepared_statements_instances                        |      0.00 |
| information_schema | INNODB_CMP                                           |      0.00 |
| performance_schema | events_statements_summary_by_host_by_event_name      |      0.00 |
| performance_schema | memory_summary_global_by_event_name                  |      0.00 |
| information_schema | GLOBAL_VARIABLES                                     |      0.00 |
| performance_schema | users                                                |      0.00 |
| information_schema | INNODB_BUFFER_PAGE                                   |      0.00 |
| information_schema | INNODB_MUTEXES                                       |      0.00 |
| performance_schema | events_statements_current                            |      0.00 |
| performance_schema | hosts                                                |      0.00 |
| information_schema | ENABLED_ROLES                                        |      0.00 |
| performance_schema | table_io_waits_summary_by_index_usage                |      0.00 |
| information_schema | INDEX_STATISTICS                                     |      0.00 |
| information_schema | USER_STATISTICS                                      |      0.00 |
| performance_schema | events_stages_summary_by_account_by_event_name       |      0.00 |
| performance_schema | file_instances                                       |      0.00 |
| performance_schema | status_by_account                                    |      0.00 |
| information_schema | TABLE_PRIVILEGES                                     |      0.00 |
| information_schema | INNODB_CMPMEM_RESET                                  |      0.00 |
| performance_schema | accounts                                             |      0.00 |
| performance_schema | events_waits_summary_by_host_by_event_name           |      0.00 |
| performance_schema | setup_objects                                        |      0.00 |
| information_schema | SQL_FUNCTIONS                                        |      0.00 |
| information_schema | INNODB_SYS_FOREIGN_COLS                              |      0.00 |
| mysql              | user                                                 |      NULL |
+--------------------+------------------------------------------------------+-----------+
206 rows in set (0.015 sec)

MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> SELECT table_schema as `DB`, table_name AS `Table`,  ROUND(((data_length + index_length) / 1024 / 1), 2) `Size (kB)`  FROM information_schema.TABLES  ORDER BY (data_length + index_length) DESC;
+--------------------+------------------------------------------------------+-----------+
| DB                 | Table                                                | Size (kB) |
+--------------------+------------------------------------------------------+-----------+
| mysql              | help_topic                                           |    520.00 |
| mysql              | help_relation                                        |     80.00 |
| mysql              | transaction_registry                                 |     64.00 |
| mysql              | help_keyword                                         |     48.00 |
| postfixadmin       | log                                                  |     48.00 |
| postfixadmin       | alias_domain                                         |     48.00 |
| mysql              | tables_priv                                          |     40.00 |
| mysql              | proxies_priv                                         |     40.00 |
| mysql              | help_category                                        |     40.00 |
| mysql              | db                                                   |     40.00 |
| postfixadmin       | domain_admins                                        |     32.00 |
| mysql              | global_priv                                          |     32.00 |
| postfixadmin       | vacation                                             |     32.00 |
| postfixadmin       | alias                                                |     32.00 |
| postfixadmin       | mailbox                                              |     32.00 |
| postfixadmin       | config                                               |     32.00 |
| postfixadmin       | quota2                                               |     16.00 |
| mysql              | func                                                 |     16.00 |
| postfixadmin       | admin                                                |     16.00 |
| information_schema | OPTIMIZER_TRACE                                      |     16.00 |
| mysql              | time_zone_name                                       |     16.00 |
| mysql              | proc                                                 |     16.00 |
| postfixadmin       | vacation_notification                                |     16.00 |
| information_schema | ROUTINES                                             |     16.00 |
| mysql              | columns_priv                                         |     16.00 |
| information_schema | PARTITIONS                                           |     16.00 |
| mysql              | time_zone_transition_type                            |     16.00 |
| information_schema | TRIGGERS                                             |     16.00 |
| mysql              | innodb_index_stats                                   |     16.00 |
| information_schema | SYSTEM_VARIABLES                                     |     16.00 |
| postfixadmin       | quota                                                |     16.00 |
| mysql              | event                                                |     16.00 |
| postfixadmin       | domain                                               |     16.00 |
| information_schema | PROCESSLIST                                          |     16.00 |
| mysql              | time_zone_leap_second                                |     16.00 |
| information_schema | VIEWS                                                |     16.00 |
| mysql              | servers                                              |     16.00 |
| information_schema | COLUMNS                                              |     16.00 |
| mysql              | plugin                                               |     16.00 |
| postfixadmin       | fetchmail                                            |     16.00 |
| mysql              | column_stats                                         |     16.00 |
| information_schema | PARAMETERS                                           |     16.00 |
| mysql              | time_zone_transition                                 |     16.00 |
| mysql              | table_stats                                          |     16.00 |
| mysql              | procs_priv                                           |     16.00 |
| information_schema | CHECK_CONSTRAINTS                                    |     16.00 |
| mysql              | index_stats                                          |     16.00 |
| mysql              | gtid_slave_pos                                       |     16.00 |
| information_schema | PLUGINS                                              |     16.00 |
| mysql              | time_zone                                            |     16.00 |
| information_schema | EVENTS                                               |     16.00 |
| mysql              | roles_mapping                                        |     16.00 |
| mysql              | innodb_table_stats                                   |     16.00 |
| information_schema | ALL_PLUGINS                                          |     16.00 |
| information_schema | INNODB_CMPMEM                                        |      0.00 |
| performance_schema | events_waits_history                                 |      0.00 |
| performance_schema | setup_actors                                         |      0.00 |
| information_schema | SESSION_STATUS                                       |      0.00 |
| information_schema | TABLE_STATISTICS                                     |      0.00 |
| performance_schema | events_transactions_summary_by_host_by_event_name    |      0.00 |
| performance_schema | replication_connection_configuration                 |      0.00 |
| information_schema | PROFILING                                            |      0.00 |
| information_schema | INNODB_LOCK_WAITS                                    |      0.00 |
| information_schema | THREAD_POOL_STATS                                    |      0.00 |
| performance_schema | events_statements_summary_global_by_event_name       |      0.00 |
| performance_schema | performance_timers                                   |      0.00 |
| performance_schema | user_variables_by_thread                             |      0.00 |
| information_schema | SPATIAL_REF_SYS                                      |      0.00 |
| information_schema | INNODB_SYS_SEMAPHORE_WAITS                           |      0.00 |
| performance_schema | events_statements_summary_by_digest                  |      0.00 |
| performance_schema | memory_summary_by_user_by_event_name                 |      0.00 |
| information_schema | GLOBAL_STATUS                                        |      0.00 |
| performance_schema | table_handles                                        |      0.00 |
| information_schema | CLIENT_STATISTICS                                    |      0.00 |
| information_schema | INNODB_FT_CONFIG                                     |      0.00 |
| mysql              | slow_log                                             |      0.00 |
| performance_schema | events_stages_summary_global_by_event_name           |      0.00 |
| performance_schema | host_cache                                           |      0.00 |
| information_schema | COLUMN_PRIVILEGES                                    |      0.00 |
| information_schema | INNODB_SYS_FOREIGN                                   |      0.00 |
| performance_schema | events_stages_history_long                           |      0.00 |
| performance_schema | events_waits_summary_global_by_event_name            |      0.00 |
| information_schema | CHARACTER_SETS                                       |      0.00 |
| performance_schema | socket_summary_by_instance                           |      0.00 |
| information_schema | TABLE_CONSTRAINTS                                    |      0.00 |
| information_schema | INNODB_CMP_PER_INDEX_RESET                           |      0.00 |
| performance_schema | events_waits_summary_by_account_by_event_name        |      0.00 |
| performance_schema | setup_instruments                                    |      0.00 |
| information_schema | STATISTICS                                           |      0.00 |
| information_schema | INNODB_BUFFER_PAGE_LRU                               |      0.00 |
| performance_schema | events_transactions_summary_by_user_by_event_name    |      0.00 |
| performance_schema | session_account_connect_attrs                        |      0.00 |
| information_schema | THREAD_POOL_WAITS                                    |      0.00 |
| performance_schema | events_transactions_history                          |      0.00 |
| performance_schema | replication_applier_configuration                    |      0.00 |
| information_schema | INNODB_TRX                                           |      0.00 |
| information_schema | user_variables                                       |      0.00 |
| performance_schema | events_statements_summary_by_program                 |      0.00 |
| performance_schema | metadata_locks                                       |      0.00 |
| information_schema | KEYWORDS                                             |      0.00 |
| performance_schema | table_io_waits_summary_by_table                      |      0.00 |
| information_schema | INNODB_SYS_DATAFILES                                 |      0.00 |
| information_schema | INNODB_SYS_TABLESPACES                               |      0.00 |
| performance_schema | events_statements_history                            |      0.00 |
| performance_schema | memory_summary_by_account_by_event_name              |      0.00 |
| information_schema | ENGINES                                              |      0.00 |
| information_schema | INNODB_FT_DEFAULT_STOPWORD                           |      0.00 |
| performance_schema | events_stages_summary_by_host_by_event_name          |      0.00 |
| performance_schema | file_summary_by_event_name                           |      0.00 |
| information_schema | COLLATIONS                                           |      0.00 |
| performance_schema | status_by_host                                       |      0.00 |
| information_schema | INNODB_FT_INDEX_CACHE                                |      0.00 |
| performance_schema | cond_instances                                       |      0.00 |
| performance_schema | events_waits_summary_by_instance                     |      0.00 |
| performance_schema | setup_timers                                         |      0.00 |
| information_schema | INNODB_FT_INDEX_TABLE                                |      0.00 |
| performance_schema | events_waits_current                                 |      0.00 |
| performance_schema | session_status                                       |      0.00 |
| information_schema | SCHEMA_PRIVILEGES                                    |      0.00 |
| information_schema | THREAD_POOL_QUEUES                                   |      0.00 |
| performance_schema | events_transactions_summary_by_account_by_event_name |      0.00 |
| performance_schema | replication_applier_status_by_coordinator            |      0.00 |
| information_schema | INNODB_METRICS                                       |      0.00 |
| information_schema | INNODB_FT_DELETED                                    |      0.00 |
| performance_schema | events_statements_summary_by_user_by_event_name      |      0.00 |
| performance_schema | objects_summary_global_by_type                       |      0.00 |
| information_schema | KEY_COLUMN_USAGE                                     |      0.00 |
| performance_schema | threads                                              |      0.00 |
| information_schema | INNODB_SYS_TABLESTATS                                |      0.00 |
| information_schema | INNODB_SYS_INDEXES                                   |      0.00 |
| performance_schema | events_statements_summary_by_account_by_event_name   |      0.00 |
| performance_schema | memory_summary_by_thread_by_event_name               |      0.00 |
| information_schema | FILES                                                |      0.00 |
| performance_schema | status_by_user                                       |      0.00 |
| information_schema | INNODB_SYS_COLUMNS                                   |      0.00 |
| performance_schema | events_stages_summary_by_user_by_event_name          |      0.00 |
| performance_schema | global_status                                        |      0.00 |
| information_schema | INNODB_FT_BEING_DELETED                              |      0.00 |
| performance_schema | events_stages_history                                |      0.00 |
| performance_schema | events_waits_summary_by_user_by_event_name           |      0.00 |
| information_schema | APPLICABLE_ROLES                                     |      0.00 |
| performance_schema | socket_summary_by_event_name                         |      0.00 |
| information_schema | TABLESPACES                                          |      0.00 |
| information_schema | THREAD_POOL_GROUPS                                   |      0.00 |
| performance_schema | events_waits_history_long                            |      0.00 |
| performance_schema | setup_consumers                                      |      0.00 |
| information_schema | SESSION_VARIABLES                                    |      0.00 |
| information_schema | INNODB_SYS_FIELDS                                    |      0.00 |
| mysql              | general_log                                          |      0.00 |
| performance_schema | events_transactions_summary_by_thread_by_event_name  |      0.00 |
| performance_schema | rwlock_instances                                     |      0.00 |
| information_schema | REFERENTIAL_CONSTRAINTS                              |      0.00 |
| information_schema | INNODB_CMP                                           |      0.00 |
| performance_schema | events_transactions_current                          |      0.00 |
| performance_schema | prepared_statements_instances                        |      0.00 |
| performance_schema | users                                                |      0.00 |
| information_schema | INNODB_BUFFER_PAGE                                   |      0.00 |
| information_schema | INNODB_MUTEXES                                       |      0.00 |
| performance_schema | events_statements_summary_by_host_by_event_name      |      0.00 |
| performance_schema | memory_summary_global_by_event_name                  |      0.00 |
| information_schema | GLOBAL_VARIABLES                                     |      0.00 |
| performance_schema | table_io_waits_summary_by_index_usage                |      0.00 |
| information_schema | INDEX_STATISTICS                                     |      0.00 |
| information_schema | USER_STATISTICS                                      |      0.00 |
| performance_schema | events_statements_current                            |      0.00 |
| performance_schema | hosts                                                |      0.00 |
| information_schema | ENABLED_ROLES                                        |      0.00 |
| information_schema | INNODB_CMPMEM_RESET                                  |      0.00 |
| performance_schema | events_stages_summary_by_account_by_event_name       |      0.00 |
| performance_schema | file_instances                                       |      0.00 |
| performance_schema | status_by_account                                    |      0.00 |
| information_schema | TABLE_PRIVILEGES                                     |      0.00 |
| information_schema | INNODB_SYS_FOREIGN_COLS                              |      0.00 |
| performance_schema | accounts                                             |      0.00 |
| performance_schema | events_waits_summary_by_host_by_event_name           |      0.00 |
| performance_schema | setup_objects                                        |      0.00 |
| information_schema | SQL_FUNCTIONS                                        |      0.00 |
| information_schema | INNODB_LOCKS                                         |      0.00 |
| performance_schema | events_transactions_summary_global_by_event_name     |      0.00 |
| performance_schema | session_connect_attrs                                |      0.00 |
| information_schema | SCHEMATA                                             |      0.00 |
| information_schema | INNODB_CMP_RESET                                     |      0.00 |
| performance_schema | events_transactions_history_long                     |      0.00 |
| performance_schema | replication_applier_status                           |      0.00 |
| information_schema | INNODB_CMP_PER_INDEX                                 |      0.00 |
| information_schema | INNODB_TABLESPACES_ENCRYPTION                        |      0.00 |
| performance_schema | events_statements_summary_by_thread_by_event_name    |      0.00 |
| performance_schema | mutex_instances                                      |      0.00 |
| information_schema | KEY_CACHES                                           |      0.00 |
| performance_schema | table_lock_waits_summary_by_table                    |      0.00 |
| information_schema | GEOMETRY_COLUMNS                                     |      0.00 |
| information_schema | INNODB_SYS_VIRTUAL                                   |      0.00 |
| performance_schema | events_statements_history_long                       |      0.00 |
| performance_schema | memory_summary_by_host_by_event_name                 |      0.00 |
| performance_schema | status_by_thread                                     |      0.00 |
| information_schema | USER_PRIVILEGES                                      |      0.00 |
| information_schema | INNODB_SYS_TABLES                                    |      0.00 |
| performance_schema | events_stages_summary_by_thread_by_event_name        |      0.00 |
| performance_schema | file_summary_by_instance                             |      0.00 |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY                |      0.00 |
| information_schema | INNODB_BUFFER_POOL_STATS                             |      0.00 |
| performance_schema | events_stages_current                                |      0.00 |
| performance_schema | events_waits_summary_by_thread_by_event_name         |      0.00 |
| performance_schema | socket_instances                                     |      0.00 |
| information_schema | TABLES                                               |      0.00 |
| mysql              | user                                                 |      NULL |
+--------------------+------------------------------------------------------+-----------+
206 rows in set (0.004 sec)

MariaDB [(none)]> select table_name as 'table',
    ->     table_rows as 'rows'
    -> from information_schema.tables
    -> where table_schema = 'postfixadmin'
    -> and table_type = 'BASE TABLE'
    -> order by table_rows desc;
+-----------------------+------+
| table                 | rows |
+-----------------------+------+
| log                   |   87 |
| alias                 |   37 |
| mailbox               |   15 |
| domain                |    6 |
| alias_domain          |    4 |
| domain_admins         |    1 |
| config                |    1 |
| admin                 |    1 |
| quota2                |    0 |
| vacation              |    0 |
| vacation_notification |    0 |
| quota                 |    0 |
| fetchmail             |    0 |
+-----------------------+------+
13 rows in set (0.000 sec)

MariaDB [(none)]> SELECT User, Db, Host from mysql.db;
+--------------+--------------+-----------+
| User         | Db           | Host      |
+--------------+--------------+-----------+
| postfixadmin | postfixadmin | localhost |
+--------------+--------------+-----------+
1 row in set (0.001 sec)

MariaDB [(none)]> SELECT host, user, password FROM mysql.user;
+-----------+--------------+-------------------------------------------+
| Host      | User         | Password                                  |
+-----------+--------------+-------------------------------------------+
| localhost | root         | *7AD796BBF736F50732431C588299E7E53C812457 |
| 127.0.0.1 | root         | *7AD796BBF736F50732431C588299E7E53C812457 |
| ::1       | root         | *7AD796BBF736F50732431C588299E7E53C812457 |
| %         | zbx_monitor  | *2320CC744BAED7546ABFD2E1F206833E35DBB68C |
| localhost | postfixadmin | *55B9A0BA81B08B06C1FA3B6BD1AE3B7AF386AA4E |
| localhost | mariadb.sys  |                                           |
+-----------+--------------+-------------------------------------------+
6 rows in set (0.001 sec)

MariaDB [(none)]> DESC mysql.user;
+------------------------+---------------------+------+-----+----------+-------+
| Field                  | Type                | Null | Key | Default  | Extra |
+------------------------+---------------------+------+-----+----------+-------+
| Host                   | char(60)            | NO   |     |          |       |
| User                   | char(80)            | NO   |     |          |       |
| Password               | longtext            | YES  |     | NULL     |       |
| Select_priv            | varchar(1)          | YES  |     | NULL     |       |
| Insert_priv            | varchar(1)          | YES  |     | NULL     |       |
| Update_priv            | varchar(1)          | YES  |     | NULL     |       |
| Delete_priv            | varchar(1)          | YES  |     | NULL     |       |
| Create_priv            | varchar(1)          | YES  |     | NULL     |       |
| Drop_priv              | varchar(1)          | YES  |     | NULL     |       |
| Reload_priv            | varchar(1)          | YES  |     | NULL     |       |
| Shutdown_priv          | varchar(1)          | YES  |     | NULL     |       |
| Process_priv           | varchar(1)          | YES  |     | NULL     |       |
| File_priv              | varchar(1)          | YES  |     | NULL     |       |
| Grant_priv             | varchar(1)          | YES  |     | NULL     |       |
| References_priv        | varchar(1)          | YES  |     | NULL     |       |
| Index_priv             | varchar(1)          | YES  |     | NULL     |       |
| Alter_priv             | varchar(1)          | YES  |     | NULL     |       |
| Show_db_priv           | varchar(1)          | YES  |     | NULL     |       |
| Super_priv             | varchar(1)          | YES  |     | NULL     |       |
| Create_tmp_table_priv  | varchar(1)          | YES  |     | NULL     |       |
| Lock_tables_priv       | varchar(1)          | YES  |     | NULL     |       |
| Execute_priv           | varchar(1)          | YES  |     | NULL     |       |
| Repl_slave_priv        | varchar(1)          | YES  |     | NULL     |       |
| Repl_client_priv       | varchar(1)          | YES  |     | NULL     |       |
| Create_view_priv       | varchar(1)          | YES  |     | NULL     |       |
| Show_view_priv         | varchar(1)          | YES  |     | NULL     |       |
| Create_routine_priv    | varchar(1)          | YES  |     | NULL     |       |
| Alter_routine_priv     | varchar(1)          | YES  |     | NULL     |       |
| Create_user_priv       | varchar(1)          | YES  |     | NULL     |       |
| Event_priv             | varchar(1)          | YES  |     | NULL     |       |
| Trigger_priv           | varchar(1)          | YES  |     | NULL     |       |
| Create_tablespace_priv | varchar(1)          | YES  |     | NULL     |       |
| Delete_history_priv    | varchar(1)          | YES  |     | NULL     |       |
| ssl_type               | varchar(9)          | YES  |     | NULL     |       |
| ssl_cipher             | longtext            | NO   |     |          |       |
| x509_issuer            | longtext            | NO   |     |          |       |
| x509_subject           | longtext            | NO   |     |          |       |
| max_questions          | bigint(20) unsigned | NO   |     | 0        |       |
| max_updates            | bigint(20) unsigned | NO   |     | 0        |       |
| max_connections        | bigint(20) unsigned | NO   |     | 0        |       |
| max_user_connections   | bigint(21)          | NO   |     | 0        |       |
| plugin                 | longtext            | NO   |     |          |       |
| authentication_string  | longtext            | NO   |     |          |       |
| password_expired       | varchar(1)          | NO   |     |          |       |
| is_role                | varchar(1)          | YES  |     | NULL     |       |
| default_role           | longtext            | NO   |     |          |       |
| max_statement_time     | decimal(12,6)       | NO   |     | 0.000000 |       |
+------------------------+---------------------+------+-----+----------+-------+
47 rows in set (0.001 sec)

MariaDB [(none)]> SELECT User, Db, Host from mysql.db;
+--------------+--------------+-----------+
| User         | Db           | Host      |
+--------------+--------------+-----------+
| postfixadmin | postfixadmin | localhost |
+--------------+--------------+-----------+
1 row in set (0.000 sec)

MariaDB [(none)]>  select distinct concat('SHOW GRANTS FOR ', QUOTE(user),'@', QUOTE(host), ';') as query from mysql.user;
+---------------------------------------------+
| query                                       |
+---------------------------------------------+
| SHOW GRANTS FOR 'zbx_monitor'@'%';          |
| SHOW GRANTS FOR 'root'@'127.0.0.1';         |
| SHOW GRANTS FOR 'root'@'::1';               |
| SHOW GRANTS FOR 'mariadb.sys'@'localhost';  |
| SHOW GRANTS FOR 'postfixadmin'@'localhost'; |
| SHOW GRANTS FOR 'root'@'localhost';         |
+---------------------------------------------+
6 rows in set (0.000 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'zbx_monitor'@'%';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for zbx_monitor@%                                                                                                                                               |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT PROCESS, SHOW DATABASES, BINLOG MONITOR, SHOW VIEW, SLAVE MONITOR ON *.* TO `zbx_monitor`@`%` IDENTIFIED BY PASSWORD '*2320CC744BAED7546ABFD2E1F206833E35DBB68C' |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>  SHOW GRANTS FOR 'root'@'127.0.0.1';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@127.0.0.1                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`127.0.0.1` IDENTIFIED BY PASSWORD '*7AD796BBF736F50732431C588299E7E53C812457' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>  SHOW GRANTS FOR 'root'@'::1';
+----------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@::1                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`::1` IDENTIFIED BY PASSWORD '*7AD796BBF736F50732431C588299E7E53C812457' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'mariadb.sys'@'localhost';
+----------------------------------------------------------------------------+
| Grants for mariadb.sys@localhost                                           |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mariadb.sys`@`localhost`                            |
| GRANT SELECT, DELETE ON `mysql`.`global_priv` TO `mariadb.sys`@`localhost` |
+----------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'postfixadmin'@'localhost';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for postfixadmin@localhost                                                                                                                                                                                                                    |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `postfixadmin`@`localhost` IDENTIFIED BY PASSWORD '*55B9A0BA81B08B06C1FA3B6BD1AE3B7AF386AA4E'                                                                                                                                  |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `postfixadmin`.* TO `postfixadmin`@`localhost` |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*7AD796BBF736F50732431C588299E7E53C812457' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]>

5 Migrations[edit | edit source]

Retrieved from "https://wiki.kitsnet.us/w/index.php?title=KitsNet_Operations:Services:Database:MariaDB&oldid=1858"