How to run shell commands from the inside MySQL command line

You need to use \! before shell command

Examples:

MariaDB [(none)]> \! date;
Sun Sep  9 03:18:38 CEST 2018
MariaDB [(none)]> \! ls -d public_html;
public_html
MariaDB [(none)]> \! touch 1.txt
MariaDB [(none)]> \! ls 1.txt
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

Run:

show global status  like 'open%';

Output:

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

Run:

show global variables like 'table_open_cache';

Output:

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

Calculating:

Table cache hit rate = table_open_cache*100/Opened_tables

2000×100÷542079=0.37%

In general it should be more than 50%

Find out total tables of your databases

Run:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
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%';

or

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:

mysql
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

https://www.drupal.org/project/garland

Then:

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

Then:

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