 |
Частичное восстановление данных MySQL из бэкапа, созданного с использованием LVM (доп. ссылка 1) |
Автор: Андрей Татаранович
[комментарии]
|
| | Я не буду описывать процесс создания резервной копии MySQL с применением менеджера томов LVM. В интернете хватает описаний этой методики. Предположим у вас уже есть бэкап, который содержит бинарные файлы баз данных. В моем случае они хранятся на выделенном сервере. Использование бинарных файлов позволяет быстро восстановить все базы на момент создания резервной копии, но вот что делать если нужно восстановить только часть баз или только одну, или же только пару таблиц или несколько удаленных записей из таблиц?
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
|
| |
 |
|
|
Рекомендации по оптимальному использованию типов данных в MySQL |
Автор: gara
[комментарии]
|
| | Материал основан на рекомендациях, данных в книге "MySQL Оптимизация производительности", в которой подробно рассказано почему каждая рекомендация работает так, а не иначе. В заметке лишь приведены ключевые моменты, в стиле "Делайте так!". Все нижесказанное будет относиться к MySQL 5.1 и выше. Движок InnoDB. Для MyISAM также верно почти все нижесказанное.
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
|
| |
|
|
|
Отладка долго выполняющихся транзакций в MySQL (доп. ссылка 1) |
[комментарии]
|
| | Включение лога долго выполняющихся запросов в MySQL (настройка log-slow-queries) не спасает при необходимости выявления транзакций, находящихся длительное время в незакрытом состоянии. Транзакции, внутри которых были изменены данные, но которые остаются висеть без коммита, достаточно трудно выявить и сопоставить с источником (в списке активных запросов по "SHOW PROCESSLIST" они не видны), в то время как они могут привести к разнообразным проблемам с блокировками и неудачному завершению других операций после истечения таймаута.
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
|
| |
|
|
|
Расширенный анализ эффективности индексов в MySQL (доп. ссылка 1) |
[комментарии]
|
| | Для детального анализа особенностей использования индексов в MySQL удобно использовать утилиту mk-index-usage, входящую в пакет [[http://code.google.com/p/maatkit/ Maatkit]] (написан на языке Perl). По умолчанию утилита выявляет неиспользуемые индексы и предлагает готовые конструкции ALTER для их удаления.
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
|
| |
|
|
|
Профилирование запросов в MySQL (доп. ссылка 1) |
Автор: Василий Лукьянчиков
[комментарии]
|
| | Начиная с версии 5.0.37 в MySQL Community Server появился удобный инструмент для профилирования запросов - директивы "SHOW PROFILES" и "SHOW PROFILE".
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
|
| |
|
|
|
Резервное копирование MySQL с использованием LVM снапшотов в Debian Linux (доп. ссылка 1) |
[комментарии]
|
| | Утилита mylvmbackup (http://lenz.homelinux.org/mylvmbackup/) позволяет автоматизировать создание резервных копий БД MySQL с минимальным временем простоя базы. Процесс создания бэкапа состоит из следующих фаз: установка блокировки на таблицы, инициирование сброса кэшей на диск, создание LVM снапшота директории с MySQL таблицами, снятие блокировки. Так как время создания снапшота очень мало, простой базы сводится к минимуму при полном сохранении целостности.
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
|
| |
|
|
|
Как уменьшить время завершения работы СУБД MySQL при использовании InnoDB (доп. ссылка 1) |
[комментарии]
|
| | Иногда для завершения работы MySQL сервера с таблицами в формате InnoDB требуется слишком много времени,
из-за необходимости сброса всех буферов. Если процесс завершить принудительно, то данные не потеряются,
но при следующем запуске будет инициирован значительно более долгий процесс восстановления из лога транзакций.
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
|
| |
|
|
|
Выбор типа хранилища MySQL: MyISAM или Innodb ? (доп. ссылка 1) (доп. ссылка 2) |
Автор: Matt Yonkovit
[комментарии]
|
| | Если вы создаете БД по обстоятельствам и не уверены как база будет использоваться, выбирайте Innodb.
Innodb следует использовать:
Когда взаимодействие с базой имеет характер OLTP (http://ru.wikipedia.org/wiki/OLTP)
Когда требуются транзакции.
Когда нужна высокая надежность хранения и быстрое восстановление после сбоя.
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
|
| |
|
|
|
Временное ведение лога всех запросов к MySQL (доп. ссылка 1) |
[комментарии]
|
| | Использование опций конфигурации log-slow-queries и general_log, позволяющих вести полный лог
медленных или всех запросов, требует перезапуска mysql для включения или выключения ведения логов,
что неудобно в ситуации, когда нужно проанализировать запросы только в текущий момент.
Для анализа запросов (не через локальный сокет) на лету можно воспользоваться сетевым сниффером.
Перехватываем и записываем срез трафика MySQL в файл:
tcpdump -i eth0 port 3306 -s 1500 -w tcpdump.out
Выделяем из дампа SQL запросы, используя утилиту tshark из комплекта сниффера
Wireshark (http://www.wireshark.org/):
tshark -r tcpdump.out -d tcp.port==3306,mysql -T fields -e mysql.query > query_log.out
Удаляем из полученного лога пустые и неинформативные строки:
cat query_log.out | grep -vE "^(commit.*|autocommit.*|rollback.*|)$" | awk '{print $0 ";"}' > query_log_no_blank.out
Полученный лог удобно анализировать утилитой mysqlsla (http://hackmysql.com/mysqlsla)
|
| |
|
|
|
Автоматический тюнинг MySQL сервера при помощи ПО MySQLTuner (доп. ссылка 1) |
[комментарии]
|
| | MySQLTuner (http://rackerhacker.com/mysqltuner/) представляет собой Perl скрипт,
анализирующий статистику работы MySQL сервера и на ее основе дающий рекомендации по оптимизации настойки СУБД.
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
|
| |
|
|
|
Синхронизация master и slave MySQL, несмотря на конфликтные запросы |
Автор: Dmitry Molchanov
[комментарии]
|
| | Иногда возникает необходимость догнать мастер несмотря на конфликтные запросы из relay-лога.
Вместо того чтобы, для разрешения конфликтов, вручную выполнять 2 команды
можно в крайних ситуациях использовать скрипт:
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
|
| |
|
|
|
Восстановление данных с поврежденной или случайно удаленной MySQL таблицы (доп. ссылка 1) (доп. ссылка 2) |
[обсудить]
|
| | Для восстановления случайно удаленной таблицы можно использовать для резервного копирования пакет
Zmanda Recovery Manager (http://mysqlbackup.zmanda.com/), имеющий поддержку восстановления
недостающей в бэкапе информации из бинарного лога изменений (MySQL binary log).
Ведение лога транзакций включается в секции "[mysqld]" файла конфигурации:
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
|
| |
|
|
|
Как проверить корректность изменений в файле конфигурации MySQL (доп. ссылка 1) |
Автор: mysqlperformanceblog
[комментарии]
|
| | Иногда возникают ситуации требующие предварительной проверки корректности
файла конфигурации MySQL, что-то похожее на "configtest" в apache.
Анализ файла конфигурации на наличие синтаксических ошибок производится при выполнении
mysqld --help
|
| |
|
|
|
Вычисление размера таблиц MySQL (доп. ссылка 1) (доп. ссылка 2) |
Автор: mysqlperformanceblog.com
[комментарии]
|
| | Запрос для расчета размера таблиц начиная с MySQL 5.0:
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
|
| |
|
|
|
Оптимизация MySQL для работы с большой Innodb базой. (доп. ссылка 1) (доп. ссылка 2) |
[комментарии]
|
| | innodb_buffer_pool_size - чем больше, тем лучне, например 70-80% от размера ОЗУ,
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
|
| |
|
|
|
Бэкап больших MyISAM таблиц без длительной блокировки в MySQL (доп. ссылка 1) |
Автор: jabrusli
[комментарии]
|
| | mysqldump лочит таблицы на запись и во время дампа база фактически простаивает.
Решения:
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
|
| |
|
|
|
Включение лога медленных запросов в MySQL |
[обсудить]
|
| | в [mysqld] секции my.cnf:
# Минимальное время запроса, которое не будет помещено в лог.
long_query_time=10
# Файл в котором будут отображаться слишком долгие запросы.
# можно использовать опцию -mysqld -log-slow-queries=file
log-slow-queries=/var/log/mysqld/slowquery.log
Для ведения полного лога всех запросов, нужно использовать опцию mysqld --log=allquery.log
|
| |
|
|
|
Решение проблем с кодировкой при переносе дампа из mysql 4.1 в 5.0 (доп. ссылка 1) |
Автор: Alex Kuklin
[обсудить]
|
| | В debian/testing (и наверное не только) mysqld собран с default charset latin1,
что приводит к потере данных при загрузке дампов в utf8 независимо от настроек конкретной базы.
Лекарство:
в разделе [mysqld] в /etc/mysql/my.cnf
character_set_server = utf8
collation_server = utf8_general_ci
Вариант 2:
Если база данных в utf8 и в дампе с ней нет SET NAMES utf8; делаю так
cat mysql_dump.sql | mysql -u user -p dbname --default-character-set=utf8
|
| |
|
|
|
Пример настройки master-master репликации в MySQL (доп. ссылка 1) |
[комментарии]
|
| | Шаг 1. Устанавливаем MySQL на два сервера:
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
|
| |
|
|
|
Техника изменения забытого административного MySQL пароля (доп. ссылка 1) |
Автор: Steve
[комментарии]
|
| | Останавливаем MySQL:
/etc/init.d/mysql stop
Запускаем в режиме без проверки прав доступа:
/usr/bin/mysqld_safe --skip-grant-tables &
Соединяемся как root без пароля:
mysql --user=root mysql
Обновляем пароль:
mysql> update user set Password=PASSWORD('новый пароль') WHERE User='root';
mysql> flush privileges;
Завершаем работу mysqld_safe:
fg
"Ctrl+c"
Запускаем MySQL в обычном режиме.
/etc/init.d/mysql start
|
| |
|
|
|
Автоматизация послеустановочной настройки MySQL |
Автор: Alexey Tsvetnov
[комментарии]
|
| | #!/bin/sh # # mysql-after-setup # Copyright (c) 2006 Alexey Tsvetnov, vorakl@fbsd.kiev.ua # Version: 1.4
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
|
| |
|
|
|
|
|
|
|
FreeBSD 5.3 + MySQL на сервере с несколькими CPU |
Автор: Z
[комментарии]
|
| | Для оптимизации выполнения данной БД на сервере с несколькими процессорами (и hyperthreding) после
конфигурирования FreeBSD c SMP рекомендую поставить linuxthreads и собрать MySQL с его поддержкой.
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
|
| |
|
|
|
Ограничение пользователей в MySQL (доп. ссылка 1) |
[обсудить]
|
| | MySQL 3.23.x, /etc/my.cnf, блок " [mysqld]":
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
|
| |
|
|
|
Как посмотреть список таблиц и их структуру в MySQL |
[комментарии]
|
| | 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; - статистика по mysqld процессам
SHOW STATUS; - общая статистика
SHOW TABLE STATUS [FROM db_name]; - статистика по всем таблицам в базе
|
| |
|
|
|
Как изменить пароль для пользователя в MySQL |
[комментарии]
|
| | Свой пароль можно поменять через:
SET PASSWORD = PASSWORD('пароль')
Пароль определенного пользователя можно поменять через:
SET PASSWORD FOR логин@localhost = PASSWORD('пароль');
SET PASSWORD FOR логин@"%" = PASSWORD('пароль');
тоже самое делают:
UPDATE mysql.user SET Password=PASSWORD('пароль') WHERE User='логин' AND Host='localhost';
FLUSH PRIVILEGES;
или GRANT USAGE ON БД.* TO логин@localhost IDENTIFIED BY 'пароль';
или mysqladmin -u логин password пароль
|
| |
|
|
|
Как оптимизировать выполнение INSERT запросов в MySQL (доп. ссылка 1) |
[обсудить]
|
| | Для того чтобы INSERT операции не влияли на производительность SELECT запросов
используют конструкции:
"INSERT DELAYED" и "INSERT LOW_PRIORITY" (UPDATE LOW_PRIORITY)
Позволяют отложить включение данных до тех пор, пока не завершаться текущие
операции чтения из базы,
при том, что если накопилось несколько "DELAYED", то данные будут добавлены одним блоком.
При DELAYED управление возвращается сразу, LOW_PRIORITY возвращает управление
только после завершения записи.
Для увеличения скорости выполнения большого числа INSERT/UPDATE/DELETE
рекомендуется группировать несколько операторов в рамках одного лока или транзакции:
LOCK TABLES table WRITE; (BEGIN;)
INSERT'ы ...... (но не много, чтобы не столкнуться с deadlock)
UNLOCK TABLES; (COMMIT;)
|
| |
|
|
|
Как посмотреть статистику работы PostgreSQL и MySQL |
[обсудить]
|
| | Чем сейчас занимается SQL сервер:
MySQL: mysqladmin processlist
PostgreSQL: select * from pg_stat_activity;
select * from pg_stat_database;
Общая статистика по работе сервера:
MySQL: mysqladmin extended-status; mysqladmin status
PostgreSQL: select * from pg_stats;
|
| |
|
|
|
Как увеличить производительность выполнения mysqldump (доп. ссылка 1) |
[комментарии]
|
| | При дампе очень больших таблиц mysqldump пытается использовать очень большой объем ОЗУ.
Чтобы он этого не делал нужно использовать опцию "-q".
|
| |
|
|
|
Как определить и исправить повреждение MySQL базы. |
[обсудить]
|
| | Для тестирования повреждений рекомендуется выполнять:
myisamchk tables[.MYI]
Параметры уровня проверки:
--medium-check - средний
--extend-check - расширенный
В crontab:
35 0 * * 0 /usr/local/mysql/bin/myisamchk --fast --silent /path/to/datadir/*/*.MYI
Внимание, myisamchk нужно запускать при _не_ запущенном mysqld, иначе нужно
использовать утилиту mysqlcheck
(mysqlcheck --repair --analyze --optimize --all-databases --auto-repair)
Восстановление таблицы:
REPAIR TABLE tbl_name или myisamchk -r table_name или myisamchk --safe-recover table_name
|
| |
|
|
|
Как произвести оптимизацию хранилища в MySQL (аналог vacuum в psql) |
[обсудить]
|
| | Почистить "дырки" (дефрагментация), обновить статистику и отсортировать индексы:
OPTIMIZE TABLE имя_таблицы;
или использовать: myisamchk --quick --check-only-changed --sort-index --analyze
Внимание, myisamchk нужно запускать при _не_ запущенном mysqld, иначе нужно
использовать утилиту mysqlcheck
(mysqlcheck --repair --analyze --optimize --all-databases --auto-repair)
Апдейт статистики оптимизатора:
ANALYZE TABLE имя_таблицы;
или использовать: myisamchk --analyze
Рекомендуется регулярно выполнять:
isamchk -r --silent --sort-index -O sort_buffer_size=16M db_dir/*.ISM
myisamchk -r --silent --sort-index -O sort_buffer_size=16M db_dir/*.MYI
|
| |
|
|
|
Советы по бэкапу данных в MySQL |
[комментарии]
|
| | Бэкап структуры:
mysqldump --all --add-drop-table [--all-databases] --force [--no-data] [-c] \
--password=password --user=user [база] [таблицы] > backup_file
( -c - формировать в виде полных INSERT.
--all-databases - бэкап всех баз, --no-data - бэкап только структуры таблиц в базах, [таблицы] - бэкапить только указанные таблицы.)
Восстановление: mysql < backupfile
(для прямой вставки из текстового файла можно воспользоваться mysqlimport)
(для анализа структуры базы, например, списка таблиц: mysqlshow <база>)
|
| |
|
|
|
Как обеспечить корректную работу MySQL с русскими символами при сортировке и выборке данных. |
[комментарии]
|
| | В /etc/my.cnf вписать в блоке [mysqld]:
default-character-set=koi8_ru (или cp1251)
При работе с базой можно выставить рабочую кодировку через:
SET CHARACTER SET koi8_ru
|
| |
|
|
|
Как добавить нового пользователя или БД в MySQL |
[комментарии]
|
| | GRANT ALL PRIVILEGES ON БД.* TO пользователь@localhost IDENTIFIED BY 'пароль';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP PRIVILEGES ON БД.таблица TO
пользователь@'%.domain.ru' IDENTIFIED BY 'пароль';
или
insert into user (host,user,password) values ('localhost', 'пользователь', password('пароль'));
insert into db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,
Drop_priv,Grant_priv,References_priv,Index_priv,Alter_priv)
values ('localhost','БД','пользователь','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
flush privileges;
или воспользоваться скриптом mysql_setpermission
|
| |
|
|
|
Как изменить забытый MySQL административный пароль
|
[обсудить]
|
| | 1. перезапустить mysqld с опцией --skip-grant-tables
2. mysqladmin -h хост -u пользователь password 'новый пароль'
|
| |
|
|
