MySQL crib

Often, when you develop a website, you notice how the developers come up against the same rake when designing a database.

Today I decided to publish my cribs, on the most common errors when working with MySQL.

Working with Backups

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

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

If you want to dump only one or more tables
mysqldump -u USER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 >/path/to/file/dump_table.sql

Create a backup and immediately archive it
mysqldump -u USER -pPASSWORD DATABASE | gzip >/path/to/outputfile.sql.gz

Creation of a backup with the indication of its date
mysqldump -uUSER -pPASSWORD DATABASE | gzip > `date +/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz`

Fill backup in the database
mysql -u USER -pPASSWORD DATABASE </path/to/dump.sql

Fill archive backup in the database
gunzip </path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE
or like this
zcat/path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE

Create a new database
mysqladmin -u USER -pPASSWORD create NEWDATABASE

It is convenient to use backup with additional options -Q -c -e,
mysqldump -Q -c -e -u USER -pPASSWORD DATABASE >/path/to/file/dump.sql, where:

• -Q Wraps names with backquotes
• -c makes a full insert, including column names
• -e makes an extended insert. The resulting file is smaller and it's a little faster

To view the list of databases, you can use the command:
mysqlshow -u USER -pPASSWORD

And you can also see the list of database tables:
mysqlshow -u USER -pPASSWORD DATABASE

For InnoDB tables, you need to add --single-transaction, this ensures the integrity of the backup data.
For MyISAN tables, this is not relevant, because they do not support transactionality.

Details

General facts

• It is useful for each base on the battle server to create its own user
• The encoding of the database can be any, if it is UTF8
• In most cases it's better to use the InnoDB engine
• In php, it's better to forget about a very old mysql extension and, if possible, to use pdo or mysqli
• A new copy of MySQL can always be configured and optimized
• Without a special need, you should not open MySQL outside. Instead, you can make port forwarding
  ssh -fNL LOCAL_PORT:localhost:3306 REMOTE_USER@REMOTE_HOST

Working with data

Numbers

• Do not put the UNSIGNED property on the INTEGER type unless you know what to do. Out of the box, php does not support numbers of this large size
• Related tables ("Foreign keys") should have a complete similarity in the key structure. Those. If we have "INTEGER UNSIGNED DEFAULT 0 NOT NULL" on one table for the field, then the same should be indicated on the other
• To store Boolean values, you need to use TINYINT (1)
• And the money is better stored in DECIMAL (10, 2), where the first number denotes the number of all characters, including commas, and the second - the number of decimal places. In total, we will get that DECIMAL (10.2) can save 9999999.99

Rows

• In older versions (up to 5.0.3) VARCHAR was limited to 255 characters, but now you can specify up to 65535 characters
• Remember that the TEXT type is limited to only 64 kilobits, so that to save War and Peace use LONGTEXT
• The most correct encoding for your UTF8 database

Dates

Do not forget,

• DATE, TIME, DATETIME - are output as strings, so the search and comparison of dates occurs through conversion
• TIMESTAMP - stored as UNIX_TIMESTAMP, and you can specify to automatically update the column
• When comparing the DATETIME and TIMESTAMP data types, do not forget to do the type conversion, for example:
   SELECT * FROM `table` WHERE` datetime`=DATE (`timestamp`)

Enumerations

• For enums correctly use the ENUM type
• Correctly written like this: ENUM ('mom', 'soap', 'frame')
• You can set the default value, as for any string
• In the database, the field with the enumeration is stored as a number, so the speed of work is staggeringly high
• Number of transfers ~ 65 thousand

Debugging

• If queries slow, then you can turn on the log for slow queries in/etc/mysql/my.cnf
• And then optimize your requests through EXPLAIN
• And it's convenient to watch requests through the mytop program