MySQL cheat sheet

\"MySQLWhen 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:

Creating a database structure without data:

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:

Creating a database structure without data:

 

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

Creating a backup and archiving it straight away:

Creating a backup with specifying its date:

Restoring a database from a backup file to another database:

Restoring a backup archive to a database:

or

Creating a new database:

It’s handy to use a backup with additional options -Q -c -e, i.e.

 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:

It’s also possible to view a list of database tables:

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:

 

 

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: 

 

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.

Leave a Reply

Your email address will not be published. Required fields are marked *