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
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
- -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.
- 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
- 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.
- 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
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`)
- 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.
- 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.