No edit summary |
No edit summary |
||
| (One intermediate revision by the same user not shown) | |||
| Line 14: | Line 14: | ||
#Create a MySQL user for monitoring<syntaxhighlight lang="shell-session">CREATE USER 'zbx_monitor'@'%' IDENTIFIED BY '<password>'; | #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> | 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 | #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. | ||
#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 | #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. | ||
==Operations Tooling== | ==Operations Tooling== | ||
| Line 45: | Line 45: | ||
#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> | #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. | #Reboot server. This will cause the appliciaton to be restarted with the correct data present. | ||
=== phpMyAdmin === | |||
Some servers also have [https://www.phpmyadmin.net/ phpMyAdmin] installed as a web-accessible management and monitoring solution for MariaDB. When installed, it can be accessed with the partent URL of phpMyAdmin (e.g. http://wiki.lan.kitsnet.us/phpMyAdmin). Credentials used to access will be those from the MariaDB databases. | |||
==Support== | ==Support== | ||
| Line 1,009: | Line 1,012: | ||
==Migrations== | ==Migrations== | ||
[[Category:Database]] | |||
Latest revision as of 09:26, 9 September 2026
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:
- KitsNet Operations:Builds:Linux:ciroc Mailbox server
- KitsNet Operations:Builds:Linux:galliano MediaWiki site
- KitsNet Operations:Builds:Linux:zaya Zabbix monitoring
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.
- 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'@'%';
- 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.
- 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.
- 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 - Stop applicaiton. If there is an applicaiton or service dependant upon the database , use
systemctlof 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:
# 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;
- 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 - Reboot server. This will cause the appliciaton to be restarted with the correct data present.
3.3 phpMyAdmin[edit | edit source]
Some servers also have phpMyAdmin installed as a web-accessible management and monitoring solution for MariaDB. When installed, it can be accessed with the partent URL of phpMyAdmin (e.g. http://wiki.lan.kitsnet.us/phpMyAdmin). Credentials used to access will be those from the MariaDB databases.
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)]>
