How to calculate innodb_buffer_pool_size value

Run to the following command to start calculation:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

Example:

mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 60295069
Server version: 10.3.34-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Read the rest

How to check if JSON_ARRAYAGG supported

Run the following simple query

with t1 as ( select 0 as a ) select version(), JSON_ARRAYAGG(a) as j from t1 where a > 0;

Example:

MariaDB [(none)]> with t1 as ( select 0 as a ) select version(), JSON_ARRAYAGG(a) as j from t1 where a > 0;
+-----------------+------+
| version() | j |
+-----------------+------+
| 10.5.13-MariaDB | NULL |
+-----------------+------+
1 row in set (0.002 sec)

If it doesn’t support you should get:

MariaDB [(none)]> with t1 as ( select 0 as a ) select version(), JSON_ARRAYAGG(a) as j from t1 where a > 0;
ERROR 1305 (42000): FUNCTION JSON_ARRAYAGG does not exist

 

Source: https://mariadb.com/kb/en/json_arrayagg/… Read the rest

How to fix a login issue after WordPress Multisite split

The slave installation will lose users

wp user list

+—-+————+————–+————+—————–+——-+
| ID | user_login | display_name | user_email | user_registered | roles |
+—-+————+————–+————+—————–+——-+
+—-+————+————–+————+—————–+——-+

And you won’t be able to create new admin user because you don’t have appropriate MySQL tables

wp user create admin [email protected]
Read the rest

How to enable Slow Query Log

Append into /etc/my.cnf the following:

slow_query_log = 1
slow-query_log_file = /var/log/mysql-slow.log
long_query_time = 2

Create /var/log/mysql-slow.log log file and set it as mysql user:

touch /var/log/mysql-slow.log
chown mysql:mysql /var/log/mysql-slow.log

Restart MySQL server:

/scripts/restartsrv_mysql

Monitor the logs using mysqldumpslow command:

mysqldumpslow -a /var/log/mysql-slow.log
Read the rest

Mysqldump error: The user specified as a definer does not exist

Error:

mysqldump myuser_db > myuser_db.sql

mysqldump: Got error: 1449: “The user specified as a definer (‘user_db’@’localhost’) does not exist” when using LOCK TABLES

To pass this error try to use –single-transaction

mysqldump --single-transaction myuser_db > myuser_db.sql

Should be good now

Source: https://stackoverflow.com/questions/26583103/mysqldump-got-error-1449/26583311Read the rest

How to fix Unknown collation utf8mb4_unicode_520_ci error while importing data from an sql dump

Error:

ERROR 1273 (HY000) at line 849: Unknown collation: ‘utf8mb4_unicode_520_ci’

To fix simply replace utf8mb4_unicode_520_ci to utf8mb4_unicode_ci inside your SQL dump

Example:

sed -i 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g' yoursqldumphere.sql

or

replace 'utf8mb4_unicode_520_ci' 'utf8mb4_unicode_ci' -- yoursqldumphere.sql
Read the rest

How to reset MODX admin password over MySQL cli

You need to run the following query inside your MySQL cli

UPDATE modx_users SET hash_class = 'hashing.modMD5', password = MD5('3f8c369a185a95cfd198e5e') WHERE username = 'admin';

Note, update mysql table prefix, password, username to yours.

Source: https://docs.modx.com/3.x/en/building-sites/client-proofing/security/troubleshooting-security/resetting-a-user-password-manuallyRead the rest

How to add new admin Joomla user via mysql command line

Update prefix to your own

INSERT INTO `js_users` (`name`, `username`, `password`, `params`, `registerDate`, `lastvisitDate`, `lastResetTime`) VALUES ('Administrator2', 'admin2', 'd2064d358136996bd22421584a7cb33e:trd7TvKHx6dMeoMmBVxYmg0vuXEA4199', '', NOW(), NOW(), NOW());

INSERT INTO `js_user_usergroup_map` (`user_id`,`group_id`) VALUES (LAST_INSERT_ID(),'8');

You now should be able to login your dashboard with the following credentials:

Username: admin2
Password: secret

You will have to reset these credentials once you logged in your dashboard as its not secure to have it

 

Source: https://docs.joomla.org/How_do_you_recover_or_reset_your_admin_password%3FRead the rest