How to run shell commands from the inside MySQL command line

You need to use \! before shell command


MariaDB [(none)]> \! date;
Sun Sep  9 03:18:38 CEST 2018
MariaDB [(none)]> \! ls -d public_html;
MariaDB [(none)]> \! touch 1.txt
MariaDB [(none)]> \! ls 1.txt
MariaDB [(none)]> \! uptime;
 03:19:40 up 8 days, 11:45,  8 users,  load average: 4.92, 5.07, 4.89
MariaDB [(none)]> 
Read the rest

How to fix innodb_table_stats and innodb_index_stats has length mismatch

To fix annoying warnings like:

[Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade

Just go ahead and run mysql_upgrade.… Read the rest

How to tune/calculate table_open_cache, open_files_limit, table_definition_cache, table_open_cache_instances

Check current value of open_tables and opened_tables


show global status  like 'open%';


MariaDB [(none)]> show global status  like 'open%';
| Variable_name            | Value    |
| Open_files               | 2258     |
| Open_streams             | 0        |
| Open_table_definitions   | 1888     |
| Open_tables              | 2000     |
| Opened_files             | 10504934 |
| Opened_plugin_libraries  | 28       |
| Opened_table_definitions | 488898   |
| Opened_tables            | 542079   |
| Opened_views             | 18497    |

Find out Table cache hit rate


show global variables like 'table_open_cache';


MariaDB [(none)]> show global variables like 'table_open_cache';
| Variable_name    | Value |
| table_open_cache | 2000  |
1 row in set (0.00 sec)


Table cache hit rate = table_open_cache*100/Opened_tables


In general it should be more than 50%

Find out total tables of your databases


Read the rest

How to find WordPress admin users over mysql command line

How to show WordPress admin user using mysql command prompt

select * from wp_users u INNER JOIN wp_usermeta m ON m.user_id = u.ID where meta_value like '%administrator%';


SELECT u.ID, u.user_login, u.user_nicename, u.user_email FROM wp_users u INNER JOIN wp_usermeta m ON m.user_id
Read the rest

ClientExec how to reset password via mysql command line

PhpMyAdmin solution:

--  Login to your phpmyadmin.
--  To the left select the ClientExec Database created.
--  Click the "user_customuserfields" table.
--  Search for the userid 100
--  There will be a row with a value that starts with sha256: - Edit this row and delete the entire entry, example:
sha256:1000:gvoyL9UXv+lUXbdp+0+2cmJ3GpIZD7w:kWuARQ6K4HWNaopY9eQry4Wqga5MSRD5 ( DELETE THIS )
--  From the tables populated on the left, select the "users" table.
Read the rest

How to show a real multisite WordPress super admin users via MySQL command line

For multisite WordPress version run the following commands:

use database_name;
SELECT * FROM wp_sitemeta where meta_key='site_admins';

This will also allow you to know which users can access plugins for management… Read the rest

Changing Drupal’s theme via mysql command line

Download for example "garland" theme to ./sites/all/themes folder from


UPDATE dr_system SET status=1 WHERE name = 'garland';


UPDATE dr_variable SET value='s:7:"garland"' WHERE name = 'theme_default';
TRUNCATE dr_cache;
TRUNCATE dr_cache_bootstrap;
TRUNCATE dr_cache_block;

Here is Drush method

drush vset theme_default garland
Read the rest