Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » Articles » MySQL cheat sheet

MySQL cheat sheet

Author: Dicentis Date: 1-11-2011, 18:15

When developing an internet site, you may often see developers making recurring mistakes when designing databases.

Today I decided to publish own FAQ answers for working with MySQL.


 

Working with MySQL backups



Creating a backup:

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


Creating a database structure without data:
mysqldump --no-data - u USER -pPASSWORD DATABASE > /path/to/file/schema.sql

 


In case you need to create a dump of only one or several tables:

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


Creating a backup and archiving it straight away:
mysqldump -u USER -pPASSWORD DATABASE | gzip > /path/to/outputfile.sql.gz


Creating a backup with specifying its date:
mysqldump -u USER -pPASSWORD DATABASE | gzip > `date +/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz` 


Restoring a database from a backup file to another database:
mysql -u USER -pPASSWORD DATABASE < /path/to/dump.sql


Restoring a backup archive to a database:
gunzip < /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE

or

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


Creating a new database:
mysqladmin -u USER -pPASSWORD create NEWDATABASE


It’s handy to use a backup with additional options -Q -c -e, i.e.
mysqldump -Q -c -e -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql
 
where:

  • -Q wraps (quotes) names with reverse quotes;
  • -c makes full insertion including column names;
  • -e makes extended insertion. The resulting file takes less space on a hard drive and less time to be made.


For viewing a list of databases the following command can be used:

mysqlshow -u USER -pPASSWORD


It’s also possible to view a list of database tables:
mysqlshow -u USER -pPASSWORD DATABASE


For InnoDB tables you have to add --single-transaction, it guarantees data integrity in the backup. 
For tables MyISAN  it’s not relevant as they do not support transactions. 

See details

 

General facts

 

  • It’s useful to create own user for every database on a production server.
  • Any of UTF8 encodings can be selected.
  • In most of cases it’s better to use the InnoDB engine.
  • In php it’s better to forget the mysql extension which is quite old and use the pdo or mysqli extension where possible.
  • A new copy of MySQL may be setup and optimized at any time.
  • It’s not recommended to grant a direct access to a MySQL database. Instead of this you can do this via specifying certain ports:
    ssh -fNL LOCAL_PORT:localhost:3306 REMOTE_USER@REMOTE_HOST

 

 

Working with data

 

Numbers

 

  • On 32-bit systems, practically there is no sense to set the ‘UNSIGNED’ property for the ‘INTEGER’ type since such big numbers are not supported in php. On 64-bit systems, php supports big numbers up to MySQL signed BIGINT.
  • Related tables («Foreign keys») have to have a full matching of their keys structures. I.e. if we have a table with the «INTEGER UNSIGNED DEFAULT 0 NOT NULL» field, the other table has to have the same field.
  • To store Boolean values, TINYINT(1) has to be used.
  • But it is better to keep the money in DECIMAL(10, 2) where the first number specifies the number of all symbols including the comma and the second – the number of symbols after the comma. It means that DECIMAL(10,2) can keep all together 9999999,99.

Strings

 

  • In older versions (up to 5.0.3), VARCHAR was limited by 255 symbols, but now it’s possible to specify up to 65535 symbols.
  • Remember that the TEXT type is limited only by 64 kbytes, so, to save huge books, use «LONGTEXT»
  • The most relevant encoding for your database is UTF8

Dates


Don’t forget that:

  • DATE, TIME, DATETIME — are outputted as strings, so searching and comparison of dates are performed via conversions
  • TIMESTAMP — exists as UNIX_TIMESTAMP, and you can specify to automatically update a column
  • When comparing the DATETIME and TIMESTAMP data types, do not forget to perform conversions, for instance: 
    SELECT * FROM table WHERE `datetime` = DATE(`timestamp`)

 

Enumerations

 

  • You have to use the ENUM type for enumerations.
  • The correct using is: ENUM('one', 'two', 'three')
  • It’s possible to use a default value as you do it for any string.
  • In a database, the field with the enumeration is kept as a number, so the working speed is high enough.
  • The amount of enumerations ~ 65 thousand.

dev.mysql.com/doc/refman/4.1/en/storage-requirements.html

Optimization

 

  • If queries freeze, you can turn on logging for slow queries in /etc/mysql/my.cnf (my.ini on windows)
  • And then optimize queries using EXPLAIN
  • And it’s handy to watch query executions using profiler for MySQL (real-time query profiler).


Please let me know if you notice inaccuracy or you wish to share your advices or notes.


Tags: backup, Tips

Dear visitor, you went to the site as unregistered user.
We recommend you to Register or Sign In.

Comments:

Leave a comment
Information
Members of Guests cannot leave comments.