MySQL commands cheatsheet

For PostgreSQL we already have a cheatsheet. Time to make one for MySQL.

Okey, for schema here I am refer to an exact database name. Just briefly without explanation as some commands are self-descriptive. Btw, I think that everyone who works with Databases should have it’s own cheat sheet.

Working with mysql console

Logging to console

mysql -u <user> -p <password> -h <host> -p <port>

General commands

\?                  -- help usage
use <schema>        -- Connect to database
show tables;        -- List tables (within selected DB)
desc
<table_name>   -- List columns in table
\q                  -- Exiting from console

Commenting (usefull in *.sql scripts) :

SELECT 1;  # This comment continues to the end of line
SELECT 1; -- This comment continues to the end of line
SELECT 1 /* this is a multiline
in-line comment */ + 1;

Additional useful commands

show tables like '%_%_%';
create schema `<schema>` ;
rename table <table_1> to <table_2>;
create table <table_1> like <table_2>;
show create table <table_1>; -- Get CREATE TABLE query for table <table_1>
insert into <table_1> select * from <table_2> where <condition>;

Creating Tables

CREAT TABLE test_table {
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` varchar(512) NOT NULL DEFAULT '',
  PRMIMARY KEY (ID)
} ENGINE=MyISAM DEFAULT CHARSET=utf8;

Locking Tables

Table locking is widely used technique of allowing only single client to access the tables for read/write

LOCK TABLES a READ, b WRITE;
... work with a and b
UNLOCK TABLES;

Passwords/ grants

Changing password for MySQL database user :

UPDATE USER SET password=PASSWORD("NEW_PASSWORD") WHERE USER='root';

Showing grants and granting access to users :

SHOW GRANTS;
GRANT ALL PRIVILEGES ON <schema>.* TO 'root'@'192.168.1.1' IDENTIFIED BY 'NEW_PASSWORD';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'NEW_PASSWORD';

Don’t forget to update new grants on-fly with FLUSH PRIVILEGES

FLUSH PRIVILEGES;

Exporting tables

For dumping mysql I prefer using the following mysql_dump command :

$ time mysqldump --host <host> --port <port> -u <user> -p <pass> <schema> \
<table1> <table2> ... | gzip > `date +schema.sql.%Y%m%d.%H%M%S.gz`

time here is just to measure metrics.

Consider also using very useful --compatible=<name> mysql parameter to specify target export. E.g –compatible=postgresql if you want then to import data in PostgreSQL database.

Importing tables

(1) Just source the file

$ mysql -u root -ppassword -h <hostname> <schema_name>
mysql> source /opt/piupiupiu.sql

(2) or pipe it in

$ mysql -u root -ppassword -h <hostname> <schema_name> < /opt/piupiupiu.sql

Optimizing tables

OPTIMIZE TABLE <table>;   /* Clear gaps (defragmentation), update stats and sort indexes */
ANALYZE TABLE <table>;    /* Update optimizator statistice */