Skip to content

Mysql Advanced

Common User

If database gets locked

MySQL
UPDATE DATABASECHANGELOGLOCK SET LOCKED=0, LOCKGRANTED=null, LOCKEDBY=null where ID=1;

Increase Max Connections:

MySQL
SET GLOBAL max_connections = 750;

Check Connected Threads:

MySQL
show status where `variable_name` = 'Threads_connected';

Show how much space are DBs using:

MySQL
select table_schema, sum((data_length+index_length)/1024/1024/1024) AS G from information_schema.tables group by 1;
MySQL
SELECT table_schema "DB name", sum( data_length + index_length ) / 1024 / 1024 / 1024 "DB size in GB", sum( data_free )/ 1024 /
1024 / 1024 "free/reclaimable space in GB" FROM information_schema.TABLES GROUP BY table_schema;

Update Examples:

Update Password

MySQL
update TableCredentials set password = "$2a$10$iL5PhyBOaezbVkeweSneh.PMd8Y025Sk0De5lQYC8sJaHwOQISuca" where username = "[email protected]";