MySQL Cheat Sheet

On Linux

CTRL+L

mysql -uUSERNAME -p -h hostname -P 3306

Assuming that you are connecting to the localhost and that MySQL is running on the default port of 3306, you can use a shorter command

mysql -uUSERNAME -p

Run this command to allow Perl to connect to Database server while still having SELinux enabled.

semanage boolean -m --on httpd_can_network_connect_db

In this example we allow the user 'testuser' on remote PC workstation1.example.com to access everything on the database testdb. We also set the password to be 123456;

GRANT ALL PRIVILEGES ON testdb.* to 'testuser'@'db.example.com' IDENTIFIED BY '123456';

SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES GROUP BY table_schema ; 

SELECT user FROM mysql.user;

SELECT user,host FROM mysql.user;

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'PASSWORD';

DROP USER 'testuser'@'localhost';

SET PASSWORD FOR 'testuser'@'%' = PASSWORD('cleartextpassword');

SET PASSWORD FOR 'testuser'@'localhost' = PASSWORD('cleartextpassword');

SHOW GRANTS;

SHOW GRANTS FOR 'root'@'localhost';

GRANT ALL ON testdb.* TO 'testuser'@'localhost';
flush privileges;

GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO 'testuser'@'localhost';
flush privileges;

REVOKE UPDATE, DELETE ON testdb.* FROM 'testuser'@'localhost';
flush privileges;

SHOW DATABASES;

CREATE DATABASE testdb;

 DROP DATABASE testdb;

SHOW TABLES FROM testdb

SHOW COLUMNS FROM testdb;

UPDATE databasename.tablename SET columnname="some_value" WHERE columnname="value";

INSERT INTO databasename.tablename (columnname1, columnname2) VALUES ("value1", 132);

select a.col1, a.col2,  (select count(*) from table2 b where b.col1=a.col1) from table1 as a group by col1;