FAQ MySQL

Домашняя страница MySQL

• Параметры my.cnf - примеры конфигураций MySQL с учетом количества ядер и ОЗУ
• MySQL Error устраняем типичные ошибки

Читайте также:

• MariaDB против MySQL: Какую СУБД выбрать?
• Как настроить репликацию для MariaDB и MySQL
• Простая установка LAMP сервера (Linux Apache PHP MySQL)
• Простая установка LEMP (LNMP) сервера (Linux Nginx PHP MySQL)
• Установка MySQL Server Ubuntu
• Инсталляция MySQL на CentOS 7 и выше
• Adminer вместо phpMyAdmin
• Универсальная установка phpMyAdmin в любой Linux, BSD
• PHP mysqli и MySQL: проверка соединения с сервером и подключения к базе

• Как бэкапить данные в MySQL
• Автоматического резервирование БД MySql при помощи AutoMySQLBackup
• Теория и практика использования программы mysqldump
• Хранимые процедуры, функции и триггеры
• PHP mysqli и MySQL: проверка соединения с сервером и подключения к базе
• MySQLi или MySQL
• Как создать резервную копию и восстановить базы данных MySQL в cPanel
• Типы данных MySQL: DATETIME, DATE, TIMESTAMP.

База данных — это обертка вокруг движка хранения данных. Она занимается приемом запросов и управлением ими, кэшированием и прочими обслуживающими функциями, обеспечивая работу с низкоуровневым API движка. Последний, в свою очередь, собственно и хранит данные (на диске или в памяти), работает с операционной системой и обеспечивает выдачу нужных выборок по запросу от сервера. Если раньше СУБД (связка "сервер + движок") была монолитная, то теперь во всех системах используется структура с плагинами. Движок в такой организации является просто модулем, а сам сервер не зависит от системы хранения данных. В последних редакциях классического MySQL также используется плагинная архитектура. Поэтому встроенный движок InnoDB (правда, обычно устаревшей версии) можно легко заменить на модуль другого проекта, который часто будет лучше. В альтернативных мускулу разработках, в том числе MariaDB или Drizzle, все движки изначально выполнены как плагины.

• Вывести информацию о движках, которые поддерживает ваш сервер MySQL. Обратите внимание на столбец Support, если указано значение "NO" тип хранения данных не поддерживается (не включен).

# mysql -uroot -p
Server version: 5.5.29-0ubuntu0.12.04.2 (Ubuntu)

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

Краткое описание современных движков хранения данных в MySQL - совместимых СУБД.

• Оптимизация InnoDB основного движка хранения данных MySQL — основной движок для MySQL, который с версии 5.5 стал дефолтным. Поддерживает транзакции, репликацию, построчную блокировку. В отличие от таблиц MyISAM, где для каждой таблицы создается один файл данных, данные InnoDB в настройках по умолчанию хранятся в больших совместно используемых файлах. То есть данные для всех таблиц и всех баз данных хранятся в одном файле.
• MyISAM — не поддерживает транзакции и внешние связи (foreign key), но зато может похвастаться полнотекстовыми индексами и быстротой вставки данных. На Select запросах MyISAM медленнее, чем InnoDB. Долгое время был стандартным для всех версий MySQL, а потому до сих пор является самым популярным.
• MERGE — MyISAM движок для разнесения данных в одной таблице на несколько разных.
• CVS — специализированный движок на случай, когда требуется хранить и обрабатывать большие массивы строковых данных, разделяемых запятой. Данные можно править обычным текстовым редактором.
• MEMORY — движок, использующийся для хранения данных не на диске, а в памяти. Информация из базы доступна только во время работы сервера, но это дает колоссальный прирост в производительности.
• Federated/FederatedX — этот движок специализируется на прозрачном разнесении данных по нескольким серверам (физическим) на уровне таблицы.
• PBXT — призванный заменить InnoDB новый движок, в котором реализованы полная поддержка транзакций, многоверсионность, автоматическая обработка дедлоков. Движок оптимизирован для большого количества одновременных транзакций.
• Blackhole — служебный движок, представляющий собой, по сути, /dev/null для СУБД и фактически не производящий никаких записей на диск. Используется для репликации.
• Archive — используется в тех случаях, когда необходимо хранить большие массивы данных без изменений. Для эффективности хранения используется сжатие, что приводит к медлительности во время выборок. Движок хорошо подходит для долговременного хранения логов и другой служебной информации.
• XtraDB — расширенная и исправленная в некоторых проблемных местах InnoDB от компании Percona.
• BlitzDB — еще одна замена для MyISAM с хорошей производительностью за счет встроенного построчного кэширования и автоматического восстановления после сбоев. Движок не поддерживает транзакции.
• NDB — движок для кластера, обладающий, впрочем, кучей проблем и удручающе плохой производительностью.
• Falcon — легендарный движок от компании MySQL AB, разрабатываемый еще со времен Sun, когда было принято решение заменить оракловский InnoDB.
• SphinxSE — полнотекстовый движок от создателя поискового сервера Sphinx. Лучший вариант для полнотекстового поиска и индексации по правилам русского языка. Легко оперирует терабайтами данных, обеспечивая при этом все возможности современной БД.
• Aria — замена для MyISAM с поддержкой транзакций и улучшенной работой с памятью. Движок гарантирует целостность данных и при этом не уступает в скорости MyISAM.
• BDB(BerkeleyDB) - для совместимости с BerkeleyDB.

• DBeaver Community Free Universal Database Tool - бесплатный многоплатформенный инструмент для работы с базами данных для разработчиков, администраторов баз данных, аналитиков и всех, кому необходимо работать с базами данных. Поддерживает все популярные базы данных: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Apache Hive, Phoenix, Presto и др.
• MySQL Navigator.
• HeidiSQL в Linux запускаем при помощи Wine.
• MySQL Workbench старое название MySQL GUI Tools - пакет утилит для администрирования баз данных MySQL.
• SQLyog Community Edition мощный инструмент с графическим интерфейсом для управления серверами и базами данных MySQL и MariaDB в физических, виртуальных и облачных средах. В Linux запускается c помощью Wine.

• Подключиться к MySQL на localhost на нестандартном порту

mysql -u root -p -P 3308 -S /tmp/mysql5.sock

• Удаленное подключение к mysql-серверу с помощью консоли

mysql -h xxx.xxx.xxx.xxx -u root -p my_db  

Пример вывода значения переменных MySQL:

mysql -e "SHOW VARIABLES LIKE '%'\G;" | grep innodb_buffer_pool_size  -B 1 -A 1

• SHOW DATABASES; - список баз данных
• SHOW TABLES [FROM db_name]; - список таблиц в базе
• SHOW COLUMNS FROM таблица [FROM db_name]; - список столбцов в таблице
• SHOW CREATE TABLE table_name; - показать структуру таблицы в формате "CREATE TABLE"
• SHOW INDEX FROM tbl_name; - список индексов
• SHOW GRANTS FOR user [FROM db_name]; - привилегии для пользователя.
• SHOW VARIABLES; - значения системных переменных
• SHOW FULL PROCESSLIST;
  • SHOW PROCESSLIST; - статистика по mysqld процессам. Удалить процесс: KILL thread_id
• SHOW STATUS; - общая статистика
• SHOW TABLE STATUS [FROM db_name]; - статистика по всем таблицам в базе

Команда DESCRIBE предоставляет информацию о столбцах таблицы.

• DESCRIBE таблица; сокращенный вариант команды SHOW COLUMNS FROM

Поддержка транзакций в сервере MySQL реализуется при помощи обработчиков транзакционных таблиц типов InnoDB и BDB. Таблицы InnoDB обеспечивают соответствие требованиям ACID.

Однако для таблиц нетранзакционных типов, таких как MyISAM, в MySQL используется иная парадигма обеспечения целостности данных, получившая название атомарные операции. Атомарные операции в сравнении с транзакциями часто обеспечивают такую же или даже лучшую целостность при более высокой производительности. Поскольку сервер MySQL поддерживает обе парадигмы, пользователь может выбирать между скоростью, которую обеспечивают атомарные операции, и транзакционными возможностями для своих приложений. Такой выбор может быть сделан для каждой таблицы отдельно.

Пример использования подготовленных запросов (Prepared Statements) для PHP

mysql_query("PREPARE myinsert FROM
'INSERT INTO
mytable (name, price)
VALUES (?, ?)'");
for ($i = 0; $i < 1000; $i++){
mysql_query("SET @name = 'Товар # $i'");
mysql_query("SET @price = " . ($i * 10));
mysql_query("EXECUTE myinsert USING @name, @price");
}