mysql – Page 3 – Blog

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

August 18, 2018 at 6:01 pm (last edited March 7, 2019 at 3:55 pm)

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 show max_user_connections limit

1213 admin

July 24, 2018 at 12:11 am

show global variables like '%connections%';

Example:

MariaDB [(none)]> show global variables like '%connections%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| extra_max_connections | 1     |
| max_connections       | 500   |
| max_user_connections  | 70    |
+-----------------------+-------+

… Read the rest

How to find WordPress admin users over mysql command line

1194 admin

June 24, 2018 at 8:02 am (last edited July 1, 2018 at 6:34 pm)

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

1099 admin

October 5, 2017 at 10:42 pm

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

1060 admin

February 12, 2017 at 2:14 pm (last edited February 12, 2017 at 2:28 pm)

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

863 admin

May 7, 2015 at 6:18 pm (last edited May 7, 2015 at 6:23 pm)

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

Joomla reset password using mysql command line

794 admin

January 2, 2015 at 3:53 pm (last edited January 2, 2015 at 4:37 pm)

Means you have a root previlegies:

mysql database_name;

mysql> update jos_users set password = MD5("strong_password_here") where id = 111;

id = 111 – must match to the account for which you want to reset password

Done!

… Read the rest

How to reset forgotten mysql root password

763 admin

October 3, 2014 at 7:57 pm (last edited February 20, 2015 at 3:45 am)

Start mysql server with skip-grant-tables

mysqld_safe --skip-grant-tables

mysql

use mysql;
update user set Password=PASSWORD('new-password') where user='root';
flush privileges;

Done!

Now kill mysql processes and restart your mysql server… Read the rest

How to disable/enable all wordpress plugin via mysql/shell command lines

752 admin

September 5, 2014 at 3:01 pm (last edited January 2, 2015 at 7:55 pm)

In order to view active plugins run:

mysql

use database;

SELECT * FROM wp_options WHERE option_name = "active_plugins";

From shell command line

mysql --host=localhost --user=database_user --password=dbpassword database -e 'SELECT * FROM wp_options WHERE option_name = "active_plugins";'

Save active plugins to file

mysql --host=localhost --user=database_user --password=dbpassword database -e 'SELECT * FROM wp_options WHERE option_name = "active_plugins";' |grep active_plugins |awk '{print $3}' > active_plugins.txt

… Read the rest