thanks and noted ;)
mysqlmymonlite.sh 0.5.4 has been released. You can download from here. Install video here. Changes of note are: update for defaults-extra-file support for mysql 5.6 update mysqltuner.pl 1.5.1 mod version with MariaDB 10 and Aria Engine support. I no longer need to patch mysqltuner.pl for Aria Engine support as upstream master has added support for it alone with other goodies. There's still some mods I added for warning/messages to clarify them a bit more i.e. memory usage Examples for mysqlmymolite.sh 0.5.4 mysqlreport and mysqltuner outputs Code: ./mysqlmymonlite.sh please see help for all options available ./mysqlmymonlite.sh --help install mysqlmymonlite.sh at /root chmod +x mysqlmymonlite.sh ./mysqlmymonlite.sh --help ./mysqlmymonlite.sh check ./mysqlmymonlite.sh run ./mysqlmymonlite.sh mysql ./mysqlmymonlite.sh vmstat ./mysqlmymonlite.sh showcreate ./mysqlmymonlite.sh showindex ./mysqlmymonlite.sh vbshowtables ./mysqlmymonlite.sh dblist ./mysqlmymonlite.sh mysqlreport ./mysqlmymonlite.sh mysqlfullreport ./mysqlmymonlite.sh mysqltuner ./mysqlmymonlite.sh psmem ./mysqlmymonlite.sh pschema Code: ./mysqlmymonlite.sh mysqlreport ------------------------------------------------------------- System MySQL monitoring stats mysqlmymonlite.sh - 0.5.4 mysqlmymon.com compiled by George Liu (eva2000) vbtechsupport.com ------------------------------------------------------------- Report Generated: Tue Aug 25 14:30:16 UTC 2015 ---------------------------- mysqlreport output ---------------------------- MariaDB 10.0.21-MariaDB uptime 14 17:58:6 Tue Aug 25 14:30:17 2015 __ Key _________________________________________________________________ Buffer used 60.00k of 32.00M %Used: 0.18 Current 5.85M %Usage: 18.28 Write hit 58.86% Read hit 99.64% __ Questions ___________________________________________________________ Total 1.32k 0.0/s DMS 971 0.0/s %Total: 73.45 -Unknown 275 0.0/s 20.80 QC Hits 246 0.0/s 18.61 Com_ 222 0.0/s 16.79 COM_QUIT 158 0.0/s 11.95 Slow 1 s 0 0/s 0.00 %DMS: 0.00 Log: DMS 971 0.0/s 73.45 SELECT 788 0.0/s 59.61 81.15 UPDATE 144 0.0/s 10.89 14.83 INSERT 34 0.0/s 2.57 3.50 DELETE 5 0.0/s 0.38 0.51 REPLACE 0 0/s 0.00 0.00 Com_ 222 0.0/s 16.79 show_fields 60 0.0/s 4.54 show_status 32 0.0/s 2.42 set_option 29 0.0/s 2.19 __ Rows ________________________________________________________________ Rows 17.72k 0.0/s Using idx 610 0.0/s %Index: 3.44 Rows/question 13.40 __ SELECT and Sort _____________________________________________________ Scan 159 0.0/s %SELECT: 20.18 Range 2 0.0/s 0.25 Full join 0 0/s 0.00 Range check 0 0/s 0.00 Full rng join 0 0/s 0.00 Sort scan 16 0.0/s Sort range 3 0.0/s Sort mrg pass 0 0/s __ Query Cache _________________________________________________________ Memory usage 17.15k of 16.00M %Usage: 0.10 Block Fragmnt 100.00% Hits 246 0.0/s Inserts 365 0.0/s Insrt:Prune 365:1 0.0/s Hit:Insert 0.67:1 __ Table Locks _________________________________________________________ Waited 0 0/s %Total: 0.00 Immediate 711 0.0/s __ Tables ______________________________________________________________ Open 82 of 2048 %Cache: 4.00 Opened 29 0.0/s __ Connections _________________________________________________________ Max used 1 of 300 %Max: 0.33 Total 160 0.0/s __ Created Temp ________________________________________________________ Disk table 60 0.0/s %Disk: 44.12 Table 136 0.0/s Size: 32.0M File 6 0.0/s __ Threads _____________________________________________________________ Running 1 of 1 Created 1 0.0/s Slow 0 0/s Cached 0 of 64 %Hit: 99.38 __ Aborted _____________________________________________________________ Clients 0 0/s Connects 0 0/s __ Bytes _______________________________________________________________ Sent 1.04M 0.8/s Received 388.59k 0.3/s __ InnoDB Buffer Pool __________________________________________________ Usage 2.48M of 47.98M %Usage: 5.18 Read hit 73.77% Pages Free 2.91k %Total: 94.82 Data 159 5.18 %Drty: 0.00 Misc 0 0.00 Latched 0 0.00 Reads 610 0.0/s From disk 160 0.0/s %Disk: 26.23 Ahead Rnd 0 0/s Writes 1 0.0/s Wait Free 0 0/s %Wait: 0.00 Flushes 1 0.0/s LRU 0 0/s %Flush: 0.00 __ InnoDB Lock _________________________________________________________ Waits 0 0/s Current 0 Time acquiring Total 0 ms Average 0 ms Max 0 ms Trx history 0 __ InnoDB Data, Pages, Rows ____________________________________________ Data Reads 173 0.0/s Writes 5 0.0/s fsync 5 0.0/s Pending Reads 0 Writes 0 fsync 0 Pages Created 0 0/s Read 159 0.0/s Written 1 0.0/s Rows Deleted 0 0/s Inserted 0 0/s Read 0 0/s Updated 0 0/s __ Aria Pagecache ______________________________________________________ Buffer used 16.00k of 8.00M %Used: 0.20 Current 336.00k %Usage: 4.10 Write hit 100.00% Read hit 88.06% Report Complete: Tue Aug 25 14:30:17 UTC 2015 ---------------------------- Code: ./mysqlmymonlite.sh mysqltuner ------------------------------------------------------------- System MySQL monitoring stats mysqlmymonlite.sh - 0.5.4 mysqlmymon.com compiled by George Liu (eva2000) vbtechsupport.com ------------------------------------------------------------- Report Generated: Tue Aug 25 14:30:27 UTC 2015 ------------------------------------------------- mysqltuner output ------------------------------------------------- >> MySQLTuner 1.5.1 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Modified by George Liu (eva2000) at http://vbtechsupport.com/ >> Run with '--help' for additional options and output filtering [OK] Currently running supported MySQL version 10.0.21-MariaDB [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM [!!] InnoDB is enabled but isn't being used [OK] Total fragmented tables: 0 -------- Security Recommendations ------------------------------------------- [OK] There is no anonymous account in all database users [OK] All database users have passwords assigned [!!] There is not basic password file list ! -------- Performance Metrics ------------------------------------------------- [--] Up for: 14d 17h 58m 16s (1K q [0.001 qps], 175 conn, TX: 1M, RX: 392K) [--] Reads / Writes: 81% / 19% [--] Binary logging is disabled [--] Total buffers: 152.0M global + 1.0M per thread (300 max threads) [OK] Maximum reached memory usage: 153.0M (14.94% of installed RAM) [OK] Maximum possible memory usage: 461.4M (45.06% of installed RAM) [OK] Slow queries: 0% (0/1K) [OK] Highest usage of available connections: 0% (1/300) [OK] Aborted connections: 0.00% (0/175) [OK] Query cache efficiency: 23.6% (246 cached / 1K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 19 sorts) [!!] Temporary tables created on disk: 40% (60 on disk / 147 total) [OK] Thread cache hit rate: 99% (1 created / 175 connections) [OK] Table cache hit rate: 282% (82 open / 29 opened) [OK] Open file limit used: 0% (58/8K) [OK] Table locks acquired immediately: 100% (711 immediate / 711 locks) -------- MyISAM Metrics ----------------------------------------------------- [!!] Key buffer used: 18.3% (6M used / 33M cache) [OK] Key buffer size / total MyISAM indexes: 32.0M/122.0K [OK] Read Key buffer hit rate: 99.6% (1K cached / 7 reads) [!!] Write Key buffer hit rate: 58.9% (457 cached / 188 writes) -------- InnoDB Metrics ----------------------------------------------------- [--] InnoDB is disabled. [!!] InnoDB Storage engine is disabled. InnoDB is the default storage engine -------- AriaDB Metrics ----------------------------------------------------- [--] AriaDB is disabled. -------- Replication Metrics ------------------------------------------------- [--] No replication slave(s) for this server. [--] This is a standalone server.. -------- Recommendations ----------------------------------------------------- General recommendations: Add skip-innodb to MySQL configuration to disable InnoDB When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: tmp_table_size (> 32M) max_heap_table_size (> 32M) Report Complete: Tue Aug 25 14:30:27 UTC 2015 ----------------------------
Installed. Working good, very good job as allways @eva2000 . For mysqltuner only recommendation is to optimise fragmented tables, but on innodb I do not know is that have any effect at all
Feature request, Can you please add an option to only backup database schema? like dblist option. I'm on the process of comparing my current DB structure to default XF, and seems I have something weird differences .
that's why i created my mysqlmymonlite.sh script mysqladmin_shell.sh Shell based Addon | Centmin Mod Community the showcreate option allows you to view all database table's create statements and/or save to individual text files which you can use file comparison tools to compare 2 different database's schemas i.e. https://community.centminmod.com/posts/136/ showindex https://community.centminmod.com/posts/137/ example for showcreate display to screen Code: ./mysqlmymonlite.sh showcreate What is your mysql database name ? wp20447db_21269 Do you want to display all wp20447db_21269 tables' schema (how table was created) ? [y/n]y Do you want save output to text file ? Answering no will output only to screen. [y/n]n *************************** 1. row *************************** Table: 2555_commentmeta Create Table: CREATE TABLE `2555_commentmeta` ( `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `comment_id` bigint(20) unsigned NOT NULL DEFAULT '0', `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `meta_value` longtext COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`meta_id`), KEY `comment_id` (`comment_id`), KEY `meta_key` (`meta_key`(191)) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci *************************** 1. row *************************** Table: 2555_comments Create Table: CREATE TABLE `2555_comments` ( `comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `comment_post_ID` bigint(20) unsigned NOT NULL DEFAULT '0', `comment_author` tinytext COLLATE utf8mb4_unicode_ci NOT NULL, `comment_author_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `comment_author_url` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `comment_author_IP` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `comment_content` text COLLATE utf8mb4_unicode_ci NOT NULL, `comment_karma` int(11) NOT NULL DEFAULT '0', `comment_approved` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '1', `comment_agent` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `comment_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `comment_parent` bigint(20) unsigned NOT NULL DEFAULT '0', `user_id` bigint(20) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`comment_ID`), KEY `comment_post_ID` (`comment_post_ID`), KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`), KEY `comment_date_gmt` (`comment_date_gmt`), KEY `comment_parent` (`comment_parent`), KEY `comment_author_email` (`comment_author_email`(10)) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci *************************** 1. row *************************** Table: 2555_links Create Table: CREATE TABLE `2555_links` ( `link_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `link_url` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `link_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `link_image` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `link_target` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `link_description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `link_visible` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Y', `link_owner` bigint(20) unsigned NOT NULL DEFAULT '1', `link_rating` int(11) NOT NULL DEFAULT '0', `link_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `link_rel` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `link_notes` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, `link_rss` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', PRIMARY KEY (`link_id`), KEY `link_visible` (`link_visible`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci *************************** 1. row *************************** Table: 2555_options Create Table: CREATE TABLE `2555_options` ( `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `option_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `option_value` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `autoload` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'yes', PRIMARY KEY (`option_id`), UNIQUE KEY `option_name` (`option_name`), KEY `autoload_idx` (`autoload`) ) ENGINE=MyISAM AUTO_INCREMENT=135 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci *************************** 1. row *************************** Table: 2555_postmeta Create Table: CREATE TABLE `2555_postmeta` ( `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `post_id` bigint(20) unsigned NOT NULL DEFAULT '0', `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `meta_value` longtext COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`meta_id`), KEY `post_id` (`post_id`), KEY `meta_key` (`meta_key`(191)) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci *************************** 1. row *************************** Table: 2555_posts Create Table: CREATE TABLE `2555_posts` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `post_author` bigint(20) unsigned NOT NULL DEFAULT '0', `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `post_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `post_title` text COLLATE utf8mb4_unicode_ci NOT NULL, `post_excerpt` text COLLATE utf8mb4_unicode_ci NOT NULL, `post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish', `comment_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open', `ping_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open', `post_password` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `post_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `to_ping` text COLLATE utf8mb4_unicode_ci NOT NULL, `pinged` text COLLATE utf8mb4_unicode_ci NOT NULL, `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `post_content_filtered` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0', `guid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `menu_order` int(11) NOT NULL DEFAULT '0', `post_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post', `post_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `comment_count` bigint(20) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`), KEY `post_name` (`post_name`(191)), KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`), KEY `post_parent` (`post_parent`), KEY `post_author` (`post_author`) ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci *************************** 1. row *************************** Table: 2555_term_relationships Create Table: CREATE TABLE `2555_term_relationships` ( `object_id` bigint(20) unsigned NOT NULL DEFAULT '0', `term_taxonomy_id` bigint(20) unsigned NOT NULL DEFAULT '0', `term_order` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`object_id`,`term_taxonomy_id`), KEY `term_taxonomy_id` (`term_taxonomy_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci *************************** 1. row *************************** Table: 2555_term_taxonomy Create Table: CREATE TABLE `2555_term_taxonomy` ( `term_taxonomy_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `term_id` bigint(20) unsigned NOT NULL DEFAULT '0', `taxonomy` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `description` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `parent` bigint(20) unsigned NOT NULL DEFAULT '0', `count` bigint(20) NOT NULL DEFAULT '0', PRIMARY KEY (`term_taxonomy_id`), UNIQUE KEY `term_id_taxonomy` (`term_id`,`taxonomy`), KEY `taxonomy` (`taxonomy`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci *************************** 1. row *************************** Table: 2555_termmeta Create Table: CREATE TABLE `2555_termmeta` ( `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `term_id` bigint(20) unsigned NOT NULL DEFAULT '0', `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `meta_value` longtext COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`meta_id`), KEY `term_id` (`term_id`), KEY `meta_key` (`meta_key`(191)) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci *************************** 1. row *************************** Table: 2555_terms Create Table: CREATE TABLE `2555_terms` ( `term_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `slug` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `term_group` bigint(10) NOT NULL DEFAULT '0', PRIMARY KEY (`term_id`), KEY `slug` (`slug`(191)), KEY `name` (`name`(191)) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci *************************** 1. row *************************** Table: 2555_usermeta Create Table: CREATE TABLE `2555_usermeta` ( `umeta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(20) unsigned NOT NULL DEFAULT '0', `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `meta_value` longtext COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`umeta_id`), KEY `user_id` (`user_id`), KEY `meta_key` (`meta_key`(191)) ) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci *************************** 1. row *************************** Table: 2555_users Create Table: CREATE TABLE `2555_users` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_login` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `user_pass` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `user_nicename` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `user_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `user_url` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `user_activation_key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `user_status` int(11) NOT NULL DEFAULT '0', `display_name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', PRIMARY KEY (`ID`), KEY `user_login_key` (`user_login`), KEY `user_nicename` (`user_nicename`) ) ENGINE=MyISAM AUTO_INCREMENT=142852 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci if saving to to files create /home/dbschemas directory and input path when prompted where you want to save files Code: mkdir /home/dbschemas ./mysqlmymonlite.sh showcreate What is your mysql database name ? wp20447db_21269 Do you want to display all wp20447db_21269 tables' schema (how table was created) ? [y/n]y Do you want save output to text file ? Answering no will output only to screen. [y/n]y Enter directory path where you want to save the text file i.e. /home/username /home/dbschemas saving to: /home/dbschemas/wp20447db_21269-2555_commentmeta-showcreate.txt saving to: /home/dbschemas/wp20447db_21269-2555_comments-showcreate.txt saving to: /home/dbschemas/wp20447db_21269-2555_links-showcreate.txt saving to: /home/dbschemas/wp20447db_21269-2555_options-showcreate.txt saving to: /home/dbschemas/wp20447db_21269-2555_postmeta-showcreate.txt saving to: /home/dbschemas/wp20447db_21269-2555_posts-showcreate.txt saving to: /home/dbschemas/wp20447db_21269-2555_term_relationships-showcreate.txt saving to: /home/dbschemas/wp20447db_21269-2555_term_taxonomy-showcreate.txt saving to: /home/dbschemas/wp20447db_21269-2555_termmeta-showcreate.txt saving to: /home/dbschemas/wp20447db_21269-2555_terms-showcreate.txt saving to: /home/dbschemas/wp20447db_21269-2555_usermeta-showcreate.txt saving to: /home/dbschemas/wp20447db_21269-2555_users-showcreate.txt ******************************************************* wp20447db_21269 table schema saved at: /home/dbschemas ******************************************************* Jan 10 03:39 539 wp20447db_21269-2555_commentmeta-showcreate.txt Jan 10 03:39 1.6K wp20447db_21269-2555_comments-showcreate.txt Jan 10 03:39 1.2K wp20447db_21269-2555_links-showcreate.txt Jan 10 03:39 602 wp20447db_21269-2555_options-showcreate.txt Jan 10 03:39 541 wp20447db_21269-2555_postmeta-showcreate.txt Jan 10 03:39 1.9K wp20447db_21269-2555_posts-showcreate.txt Jan 10 03:39 524 wp20447db_21269-2555_termmeta-showcreate.txt Jan 10 03:39 481 wp20447db_21269-2555_term_relationships-showcreate.txt Jan 10 03:39 541 wp20447db_21269-2555_terms-showcreate.txt Jan 10 03:39 703 wp20447db_21269-2555_term_taxonomy-showcreate.txt Jan 10 03:39 544 wp20447db_21269-2555_usermeta-showcreate.txt Jan 10 03:39 1.1K wp20447db_21269-2555_users-showcreate.txt contents of /home/dbschemas/wp20447db_21269-2555_options-showcreate.txt Code: *************************** 1. row *************************** Table: 2555_options Create Table: CREATE TABLE `2555_options` ( `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `option_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `option_value` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `autoload` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'yes', PRIMARY KEY (`option_id`), UNIQUE KEY `option_name` (`option_name`), KEY `autoload_idx` (`autoload`) ) ENGINE=MyISAM AUTO_INCREMENT=135 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci if you save 2 databases files to 2 separate directories, you can then use file comparison tool to compare both
Updated mysqltuner.pl to 1.6.4 bundle for mysqlmymonlite.sh. To update just remove old version and rerun mysqltuner option Code: rm /root/mysqltuner.pl ./mysqlmymonlite.sh mysqltuner Code: /root/tools/mysqlmymonlite.sh mysqltuner ------------------------------------------------------------- System MySQL monitoring stats mysqlmymonlite.sh - 0.5.4 mysqlmymon.com compiled by George Liu (eva2000) vbtechsupport.com ------------------------------------------------------------- Report Generated: Wed Feb 3 10:36:33 UTC 2016 ------------------------------------------------- mysqltuner output ------------------------------------------------- mysqltuner.pl [found] >> MySQLTuner 1.6.4 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Modified by George Liu (eva2000) at http://vbtechsupport.com/ >> Run with '--help' for additional options and output filtering [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 10.0.23-MariaDB [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM [--] Data in InnoDB tables: 2G (Tables: 24) [!!] Total fragmented tables: 24 -------- Security Recommendations ------------------------------------------- [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [!!] There is no basic password file list! -------- CVE Security Recommendations --------------------------------------- [--] Skipped due to --cvefile option undefined -------- Performance Metrics ------------------------------------------------- [--] Up for: 6h 52m 35s (491 q [0.020 qps], 188 conn, TX: 985K, RX: 52K) [--] Reads / Writes: 100% / 0% [--] Binary logging is disabled [--] Total buffers: 416.0M global + 1.0M per thread (300 max threads) [OK] Maximum reached memory usage: 417.0M (40.73% of installed RAM) [OK] Maximum possible memory usage: 725.4M (70.84% of installed RAM) [OK] Slow queries: 0% (0/491) [OK] Highest usage of available connections: 0% (1/300) [OK] Aborted connections: 0.53% (1/188) [!!] Query cache efficiency: 0.0% (0 cached / 199 selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 10 sorts) [OK] Temporary tables created on disk: 0% (0 on disk / 132 total) [OK] Thread cache hit rate: 99% (1 created / 188 connections) [OK] Table cache hit rate: 246% (106 open / 43 opened) [OK] Open file limit used: 0% (58/8K) [OK] Table locks acquired immediately: 100% (90 immediate / 90 locks) -------- MyISAM Metrics ------------------------------------------------------ [!!] Key buffer used: 18.3% (6M used / 33M cache) [OK] Key buffer size / total MyISAM indexes: 32.0M/122.0K [OK] Read Key buffer hit rate: 95.0% (40 cached / 2 reads) -------- InnoDB Metrics ------------------------------------------------------ [--] InnoDB is enabled. [!!] InnoDB buffer pool / data size: 192.0M/2.7G [!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1). [OK] InnoDB Used buffer: 91.67% (11263 used/ 12287 total) [!!] InnoDB Read buffer efficiency: 31.01% (5209 hits/ 16799 total) [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total) [OK] InnoDB log waits: 0.00% (0 waits / 1 writes) -------- ThreadPool Metrics -------------------------------------------------- [--] ThreadPool stat is disabled. -------- AriaDB Metrics ------------------------------------------------------ [--] AriaDB is disabled. -------- TokuDB Metrics ------------------------------------------------------ [--] TokuDB is disabled. -------- Galera Metrics ------------------------------------------------------ [--] Galera is disabled. -------- Replication Metrics ------------------------------------------------- [--] No replication slave(s) for this server. [--] This is a standalone server.. -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Variables to adjust: query_cache_limit (> 512K, or use smaller result sets) innodb_buffer_pool_size (>= 2G) if possible. innodb_buffer_pool_instances (=1) Report Complete: Wed Feb 3 10:36:33 UTC 2016 ----------------------------
Just a reminder for this useful mysqlmymonlite.sh tool I wrote which you can download from mysqlmymon.com or via simple command line for Centmin Mod LEMP servers Code (Text): mkdir -p /root/tools; cd /root/tools; rm -rf /root/mysqltuner.pl; rm -rf mysqlmymonlite.sh; wget http://mysqlmymon.com/download/mysqlmymonlite.zip; unzip -o mysqlmymonlite.zip; mv centos/mysqlmymonlite.sh .; rm -rf mysqlmymonlite.zip centos centos_whm debian changelog*; chmod +x mysqlmymonlite.sh script itself and tools will be at /root/tools Code (Text): ls -lah /root/tools/ total 96K drwxr-xr-x 2 root root 4.0K May 3 12:54 . dr-xr-x--- 7 root root 4.0K May 3 01:06 .. -rw-r--r-- 1 root root 137 Apr 17 2013 cronjob logging.txt -rw-r--r-- 1 root root 391 May 2 11:13 ex_stats_print.c -rw-r--r-- 1 root root 3.5K Aug 10 2013 install.sh -rwxr-xr-x 1 root root 58K Jul 14 2015 mysqlmymonlite.sh -rw-r--r-- 1 root root 13K Aug 8 2013 readme.txt Code (Text): /root/tools/mysqlmymonlite.sh please see help for all options available ./mysqlmymonlite.sh --help install mysqlmymonlite.sh at /root chmod +x mysqlmymonlite.sh ./mysqlmymonlite.sh --help ./mysqlmymonlite.sh check ./mysqlmymonlite.sh run ./mysqlmymonlite.sh mysql ./mysqlmymonlite.sh vmstat ./mysqlmymonlite.sh showcreate ./mysqlmymonlite.sh showindex ./mysqlmymonlite.sh vbshowtables ./mysqlmymonlite.sh dblist ./mysqlmymonlite.sh mysqlreport ./mysqlmymonlite.sh mysqlfullreport ./mysqlmymonlite.sh mysqltuner ./mysqlmymonlite.sh psmem ./mysqlmymonlite.sh pschema read first few posts of this thread for usage examples Code (Text): /root/tools/mysqlmymonlite.sh mysqlreport ------------------------------------------------------------- System MySQL monitoring stats mysqlmymonlite.sh - 0.5.5 mysqlmymon.com compiled by George Liu (eva2000) centminmod.com ------------------------------------------------------------- Report Generated: Wed May 3 12:57:07 UTC 2017 ---------------------------- mysqlreport output ---------------------------- MariaDB 10.1.22-MariaDB uptime 1 2:8:26 Wed May 3 12:57:07 2017 __ Key _________________________________________________________________ Buffer used 4.00k of 32.00M %Used: 0.01 Current 5.85M %Usage: 18.27 Write hit 50.00% Read hit 66.67% __ Questions ___________________________________________________________ Total 78 0.0/s Com_ 30 0.0/s %Total: 38.46 COM_QUIT 29 0.0/s 37.18 DMS 23 0.0/s 29.49 -Unknown 4 0.0/s 5.13 Slow 1 s 0 0/s 0.00 %DMS: 0.00 Log: DMS 23 0.0/s 29.49 SELECT 22 0.0/s 28.21 95.65 UPDATE 1 0.0/s 1.28 4.35 REPLACE 0 0/s 0.00 0.00 DELETE 0 0/s 0.00 0.00 INSERT 0 0/s 0.00 0.00 Com_ 30 0.0/s 38.46 show_variab 11 0.0/s 14.10 show_status 5 0.0/s 6.41 show_databa 4 0.0/s 5.13 __ Rows ________________________________________________________________ Rows 7.13k 0.1/s Using idx 9 0.0/s %Index: 0.13 Rows/question 91.42 __ SELECT and Sort _____________________________________________________ Scan 24 0.0/s %SELECT: 109.09 Range 0 0/s 0.00 Full join 0 0/s 0.00 Range check 0 0/s 0.00 Full rng join 0 0/s 0.00 Sort scan 0 0/s Sort range 0 0/s Sort mrg pass 0 0/s __ Query Cache _________________________________________________________ Memory usage 17.15k of 16.00M %Usage: 0.10 Block Fragmnt 100.00% Hits 0 0/s Inserts 1 0.0/s Insrt:Prune 1:1 0/s Hit:Insert 0.00:1 __ Table Locks _________________________________________________________ Waited 0 0/s %Total: 0.00 Immediate 48 0.0/s __ Tables ______________________________________________________________ Open 12 of 2048 %Cache: 0.59 Opened 18 0.0/s __ Connections _________________________________________________________ Max used 1 of 300 %Max: 0.33 Total 31 0.0/s __ Created Temp ________________________________________________________ Disk table 0 0/s %Disk: 0.00 Table 24 0.0/s Size: 32.0M File 6 0.0/s __ Threads _____________________________________________________________ Running 1 of 1 Created 1 0.0/s Slow 0 0/s Cached 0 of 64 %Hit: 96.77 __ Aborted _____________________________________________________________ Clients 0 0/s Connects 0 0/s __ Bytes _______________________________________________________________ Sent 234.57k 2.5/s Received 7.68k 0.1/s __ InnoDB Buffer Pool __________________________________________________ Usage 2.45M of 47.98M %Usage: 5.11 Read hit 63.00% Pages Free 2.91k %Total: 94.89 Data 157 5.11 %Drty: 0.00 Misc 0 0.00 Latched 0 0.00 Reads 427 0.0/s From disk 158 0.0/s %Disk: 37.00 Ahead Rnd 0 0/s Writes 1 0.0/s Flushes 1 0.0/s Wait Free 0 0/s %Wait: 0.00 __ InnoDB Lock _________________________________________________________ Waits 0 0/s Current 0 Time acquiring Total 0 ms Average 0 ms Max 0 ms __ InnoDB Data, Pages, Rows ____________________________________________ Data Reads 175 0.0/s Writes 5 0.0/s fsync 5 0.0/s Pending Reads 0 Writes 0 fsync 0 Pages Created 0 0/s Read 157 0.0/s Written 1 0.0/s Rows Deleted 0 0/s Inserted 0 0/s Read 0 0/s Updated 0 0/s __ Aria Pagecache ______________________________________________________ Buffer used 0 of 8.00M %Used: 0.00 Current 352.00k %Usage: 4.30 Write hit 0.00% Read hit 0.00% Report Complete: Wed May 3 12:57:08 UTC 2017 ---------------------------- Code (Text): /root/tools/mysqlmymonlite.sh mysqltuner ------------------------------------------------------------- System MySQL monitoring stats mysqlmymonlite.sh - 0.5.5 mysqlmymon.com compiled by George Liu (eva2000) centminmod.com ------------------------------------------------------------- Report Generated: Wed May 3 13:07:52 UTC 2017 ------------------------------------------------- mysqltuner output ------------------------------------------------- >> MySQLTuner 1.7.1 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Modified by George Liu (eva2000) at https://centminmod.com/ >> Run with '--help' for additional options and output filtering [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 10.1.22-MariaDB [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [--] Log file: /var/log/mysqld.log(0B) [OK] Log file /var/log/mysqld.log exists [OK] Log file /var/log/mysqld.log is readable. [!!] Log file /var/log/mysqld.log is empty [OK] Log file /var/log/mysqld.log is smaller than 32 Mb [OK] /var/log/mysqld.log doesn't contain any warning. [OK] /var/log/mysqld.log doesn't contain any error. [--] 0 start(s) detected in /var/log/mysqld.log [--] 0 shutdown(s) detected in /var/log/mysqld.log -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE [!!] InnoDB is enabled but isn't being used [OK] Total fragmented tables: 0 -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [!!] There is no basic password file list! -------- CVE Security Recommendations -------------------------------------------------------------- [--] Skipped due to --cvefile option undefined -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 1d 2h 19m 12s (214 q [0.002 qps], 83 conn, TX: 634K, RX: 21K) [--] Reads / Writes: 98% / 2% [--] Binary logging is disabled [--] Physical Memory : 2.0G [--] Max MySQL memory : 386.7M [--] Other process memory: 157.9M [--] Total buffers: 152.0M global + 801.0K per thread (300 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 152.8M (7.46% of installed RAM) [OK] Maximum possible memory usage: 386.7M (18.88% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (0/214) [OK] Highest usage of available connections: 0% (1/300) [OK] Aborted connections: 0.00% (0/83) [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance [!!] Query cache may be disabled by default due to mutex contention. [!!] Query cache efficiency: 0.0% (0 cached / 73 selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2 sorts) [OK] No joins without indexes [OK] Temporary tables created on disk: 1% (1 on disk / 60 total) [OK] Thread cache hit rate: 98% (1 created / 83 connections) [OK] Table cache hit rate: 93% (82 open / 88 opened) [OK] Open file limit used: 0% (59/262K) [OK] Table locks acquired immediately: 100% (52 immediate / 52 locks) -------- Performance schema ------------------------------------------------------------------------ [--] Performance schema is disabled. [--] Memory used by P_S: 0B [--] Sys schema isn't installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 2 thread(s). [--] Using default value is good enough for your version (10.1.22-MariaDB) -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 18.3% (6M used / 33M cache) [OK] Key buffer size / total MyISAM indexes: 32.0M/124.0K [!!] Read Key buffer hit rate: 81.8% (22 cached / 4 reads) [!!] Write Key buffer hit rate: 50.0% (8 cached / 4 writes) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is disabled. [!!] InnoDB Storage engine is disabled. InnoDB is the default storage engine -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is enabled. [OK] Aria pagecache size / total Aria indexes: 8.0M/1B [OK] Aria pagecache hit rate: 98.4% (61 cached / 1 reads) -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled. -------- RocksDB Metrics --------------------------------------------------------------------------- [--] RocksDB is disabled. -------- Spider Metrics ---------------------------------------------------------------------------- [--] Spider is disabled. -------- Connect Metrics --------------------------------------------------------------------------- [--] Connect is disabled. -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled. -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] This is a standalone server. -------- Recommendations --------------------------------------------------------------------------- General recommendations: Add skip-innodb to MySQL configuration to disable InnoDB Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 Performance should be activated for better diagnostics Consider installing Sys schema from https://github.com/mysql/mysql-sys Variables to adjust: query_cache_size (=0) query_cache_type (=0) query_cache_limit (> 512K, or use smaller result sets) performance_schema = ON enable PFS Report Complete: Wed May 3 13:07:53 UTC 2017 ----------------------------
Does this tool in any way auto update mysqltuner when a new version is out or the rest tools that include?
mysqlmymonlite.sh isn't auto updated itself but only tool you need to update is usually mysqltuner.pl but it's a modified version of the official with my own mods so usually will be updated when you run the install command Code (Text): mkdir -p /root/tools; cd /root/tools; rm -rf /root/mysqltuner.pl; rm -rf mysqlmymonlite.sh; wget http://mysqlmymon.com/download/mysqlmymonlite.zip; unzip -o mysqlmymonlite.zip; mv centos/mysqlmymonlite.sh .; rm -rf mysqlmymonlite.zip centos centos_whm debian changelog*; chmod +x mysqlmymonlite.sh or if you run the update command in the included /root/tools/install.sh Code (Text): cd /root/tools/ chmod +x /root/tools/install.sh /root/tools/install.sh update
Hi @eva2000 I did the install of the script with this code: Code: mkdir -p /root/tools; cd /root/tools; rm -rf /root/mysqltuner.pl; rm -rf mysqlmymonlite.sh; wget --no-check-certificate http://mysqlmymon.com/download/mysqlmymonlite.zip; unzip -o mysqlmymonlite.zip; mv centos/mysqlmymonlite.sh .; rm -rf mysqlmymonlite.zip centos centos_whm debian changelog*; chmod +x mysqlmymonlite.sh Code installed, everything work fine except this command: Code: ./mysqlmymonlite.sh mysqltuner When running this, there is no output, this is what I received: Code: [10:37][root@abc tools]# ./mysqlmymonlite.sh mysqltuner ------------------------------------------------------------- System MySQL monitoring stats mysqlmymonlite.sh - 0.5.7 mysqlmymon.com compiled by George Liu (eva2000) centminmod.com ------------------------------------------------------------- Report Generated: Fri Nov 12 10:37:30 UTC 2021 ------------------------------------------------- mysqltuner output ------------------------------------------------- Report Complete: Fri Nov 12 10:37:30 UTC 2021 ---------------------------- Seem it doesn't work: - I installed & reinstalled & updated, no work. - Checked to make sure mysqltuner.pl file available in root. - My vps is 2 core, 2 gb ram, centos 7, centmin updated. - Tried to run with mysql having been running for a few days or have just started after reboot, still no work. - Mysql shows no error log. Is there any other way to make this work? Thank you.
Should be fixed if you try running again. Problem was on mysqlmymon.com side for mysqltuner.pl download