X-Files Wiki

MariaDB MySQL Cheat Sheet

Show all current connections with host names sorted by db

SELECT DB,USER,HOST,STATE FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY DB DESC;

Show All Open Queries And Transactions

SHOW FULL PROCESSLIST;

Allow Remote Connection Non-Specific

GRANT <privilige level> ON *.* TO 'username'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Allow Remote Connection Specific

GRANT <privilige level> ON dbname.* TO username@'xx.xx.xx.xx' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Kill A Query Or Transaction

kill <Id>;

Create User

CREATE USER 'username' IDENTIFIED BY 'password';

Delete User

DROP USER 'username'@'host';

Show Password Hash For Given Password

SELECT PASSWORD('password_to_hash');

Show Logged In Users

SELECT USER(),CURRENT_USER();

Show All Users And Password Hash

SELECT host, user, password FROM mysql.user;

Note: For MySQL replace password with authentication_string

Lock An Account

ALTER USER 'username'@'localhost' ACCOUNT LOCK;

Unlock an account

ALTER USER 'username'@'localhost' ACCOUNT UNLOCK;

Export DB Schema

mysqldump -u root -p --no-data dbname > schema.sql

Import DB Schema

mysql -u root -p dbname < schema.sql

Show Binlog Events

SHOW BINLOG EVENTS IN 'mariadb-bin.000001' FROM 114404251 LIMIT 10;

Show InnoDB Pool Buffer Size

show variables like 'innodb_buffer%';

Convert Myisam To InnoDB

SET @DATABASE_NAME = 'name_of_your_db';
SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

You will get a list of tables to alter that you can paste into mysql.

Create A Database Backup

Click [here] for info regarding this topic.

Clear ib_logfiles

Click [here] for info regarding this topic.

Fixing Slave Error 1146

Click [here] for info regarding this topic.