Skip to content

Mysql Advanced

Common Issues and Tips

  • 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]";

Insert Row

MySQL
INSERT INTO table_name(column_1,column_2,column_3) VALUES (value_1,value_2,value_3);

The number of columns and values should match; otherwise, it fails.

MySQL
INSERT INTO table_name (column_1, column_2, column_3, ...)
VALUES
      (value_list_1),
      (value_list_2),
      ...
      (value_list_n);

Optimize

Bash
mysqlcheck -o <db_schema_name>
Bash
mysqlcheck -o --all-databases
  • remote
Bash
mysqlcheck -h<host> -u <user> -p<password> -o <database>