MySQL cheat sheets

Often, when you develop a site, you notice how developers are attacking the same rake when designing a database.

Today I decided to publish my cheat sheets to the most common errors when working with MySQL.

Work with backups

mysqldump -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql


mysqldump --no-data - u USER -pPASSWORD DATABASE > /path/to/file/schema.sql


mysqldump -u USER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 > /path/to/file/dump_table.sql


mysqldump -u USER -pPASSWORD DATABASE | gzip > /path/to/outputfile.sql.gz


mysqldump -u USER -pPASSWORD DATABASE | gzip > `date +/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz`


mysql -u USER -pPASSWORD DATABASE < /path/to/dump.sql


gunzip < /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE

zcat /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE


mysqladmin -u USER -pPASSWORD create NEWDATABASE

-Q -c -e, ..
mysqldump -Q -c -e -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql, :

• -Q
• -c ,
• -e .

:
mysqlshow -u USER -pPASSWORD

:
mysqlshow -u USER -pPASSWORD DATABASE

InnoDB --single-transaction, .
MyISAN , .

• , UTF8
• InnoDB
• php mysql - pdo mysqli
• MySQL
• MySQL .
  ssh -fNL LOCAL_PORT:localhost:3306 REMOTE_USER@REMOTE_HOST

• 32- INTEGER UNSIGNED, php .
  64- , php , MySQL BIGINT .
• («Foreign keys») . .. «INTEGER UNSIGNED DEFAULT 0 NOT NULL»
• , TINYINT(1)
• DECIMAL(10, 2), , , — . , DECIMAL(10,2) 9999999,99

• ( 5.0.3) VARCHAR 255 , 65535
• , TEXT 64 , « » «LONGTEXT»
• UTF8

,

• DATE, TIME, DATETIME — ,
• TIMESTAMP — UNIX_TIMESTAMP,
• DATETIME TIMESTAMP, , :
  SELECT * FROM table WHERE `datetime` = DATE(`timestamp`)

• ENUM
• : ENUM('', '', '')
• -,
• , —
• ~ 65

dev.mysql.com/doc/refman/4.1/en/storage-requirements.html
help.scibit.com/mascon/masconMySQL_Field_Types.html

• , /etc/mysql/my.cnf
• EXPLAIN
• mytop

, , .