Заметки начинающего администратора СУБД MySQL.

Автор: Stanislav Krylov
Оригинал: s-krylov.hotbox.ru

ПРО ЧТО "СТАТЬЯ"

Решил сделать заметку по СУБД MySQL, в которой я постарался выделить самый важный «минимум» по администрированию СУБД MySQL, который интересовал меня на тот момент, когда я впервые установил СУБД MySQL. Заметка содержит ответы и решения стандартных административных задач, которые могут возникнуть перед любым начинающим администратором СУБД MySQL. Эта заметка не освобождает от прочтения родной документации по MySQL! :)

ПРИСТУПАЯ.

ОС на которой я начал осваивать премудрости администрирования СУБД MySQL - FreeBSD 5.2 , СУБД MySQL я устанавливал из «портов»:
/usr/ports/databases/mysql50-server
/usr/ports/databases/mysql50-client

версия MySQL у меня была следующая:
shell#> mysqld -V
shell#> mysqld Ver 5.0.3-beta for portbld-freebsd5.2 on i386 (FreeBSD port: mysql-server-5.0.3_1)

также версию можно просмотреть командой:
mysql#> \s

ОГЛАВЛЕНИЕ

И так для себя я выделил несколько главных пунктов по администрированию MySQL, которые интересовали меня как начинающего администратора при первом знакомстве с этой СУБД они и будут у меня содержанием оглавления.

  • Основные понятия и компоненты MySQL.
  • Основные программы и утилиты MySQL
  • Запуск и останов сервера.
  • Работа с учетными записями пользователей MySQL.
  • Создание резервной копии баз данных (БД).
  • Обнаружение, исправление ошибок и восстановление БД после сбоя.
  • Создание профилактических мер и подготовка к самому худшему к сбою.
  • Настройка основных параметров сервера.
  • Работа нескольких серверов MySQL на одном компьютере.
  • Советы по повышению безопасности MySQL.
  • Что я читал для написания этой заметки.
  • ОСНОВНЫЕ ПОНЯТИЯ И КОМПОНЕНТЫ MySQL.

    каталог данных - содержит всю информацию, которая управляется сервером «mysqld» (базы данных, таблицы, файлы состояния). Место расположение «каталога данных» можно задать при запуске сервера с помощью опции:
    -h|--datadir=path Path to the database root.

    Определить текущие расположение «каталог данных» можно с помощью команды:
    shell#> mysqladmin variables | grep datadir

    база данных - каждая БД представляет собой подкаталог в каталоге «каталога данных»

    таблица - это три специальных файла размещенных внутри каталога «базы данных» для каждой таблицы. файлы состояний MySQL .pid - PID процесса сервера. Изменяется с помощью опции --pid-file
    .err - В этот журнал ведется запись события запуска и останова сервера, а также записи об ошибках.
    .log - В этот журнал ведется запись событий подключения/отключения и информации о запросах. Изменяется с помощью опции -l|--log
    .nnn - Журнал, в который ведется запись текста всех запросов, изменяющих содержимое или структуры таблиц. Задается помощью опции --log-bin | --log-update

    Для включения «журнала обновлений» в новых версиях используется опция --log-bin. Журнал представляет собой бинарный файл и содержит всю информацию в более эффективном формате. В нем имеется информация о времени выполнения каждого обновляющего базу запроса. Просмотр этого бинарного журнала осуществляется с помощью утилиты «mysqlbinlog».

    ОСНОВНЫЕ ПРОГРАММЫ И УТИЛИТЫ MySQL.

    mysqld собственно сам сервер/демон MySQL.
    mysql клиент для работы с сервером MySQL.
    mysqladmin программа для выполнения административных функций.
    myisamchk программа для проверки и восстановления MyISAM таблиц.
    mysqldump консольный клиент для создания «дампов» или резервных копии БД, таблиц и хранимых данных.
    perror по номеру ошибки выводит на экран описание этой ошибки.
    mysqld_safe скрипт для запуска mysqld в системах UNIX.
    Перечень некоторых идущих в дистрибутиве утилит можно найти в «man mysql» в топике «SEE ALSO».

    Полезные команды/запросы программы клиента mysql.

    Подключение к серверу MySQL на котором хранятся БД и выполнения запросов к БД осуществляется с помощью клиента «mysql». Синтаксис для подключения следующий:
    shell#> mysql -h [hostname] -P [порт] --protocol=[tcp|socket|pipe|memory] -u [username] -p[пароль] [имя_БД]

    Мой список стандартных запросов/команд, которыми мне часто приходилось пользоваться при работе с «mysql».
    SHOW DATABASES; выводит список всех БД обслуживаемых сервером. Аналогичную информацию можно получить, запустив утилиту «mysqlshow».
    USE [имя_БД] делает базу данных [имя_БД] «текущей» (активной).
    SHOW TABLES; выводит список всех таблиц в «текущей» БД Аналогичную информацию можно получить, запустив утилиту «mysqlshow [имя_БД]».
    DESCRIBE [имя_таблицы]; выводит описание таблицы [имя_таблицы] (имена столбцов, типы данных, и т.п). Аналогичную информацию можно получить, запустив утилиту «mysqlshow [имя_БД] [имя_таблицы]»
    CREATE DATABASE [имя_БД]; создает БД с именем [имя_БД]
    SELECT DATABASE(); выводит текущую БД
    SELECT USER(); выводит имя (username) текущего пользователя
    SELECT VERSION(); выводит информацию о версии сервера «mysqld»
    TRUNCATE TABLE [имя_таблицы]; удаляет из таблицы [имя_таблицы] все строки
    SELECT выбирает и возвращает строки из заданных таблиц, но лучше ознакомиться со всеми возможностями этого запроса в документации MySQL.

    Более подробное описание синтаксиса и назначения операторов можно найти в документации устанавливаемой вместе с пакетом mysql. FreeBSD чтобы найти все установленные файлы какого либо пакета, можно воспользоваться командой:
    shell#> pkg_info -xL [имя_пакета]

    среди этих файлов есть файлы документации:
    /usr/local/share/doc/mysql/manual.html
    /usr/local/share/doc/mysql/manual.txt
    /usr/local/share/doc/mysql/manual_toc.html

    либо также можно воспользоваться БД mysql, в которой есть таблицы, хранящие краткую справку помощи, чтобы найти справку по нужному оператору надо выполнить соответствующий запрос SELECT.

    Пример:
    mysql#> USE mysql;
    mysql#> SELECT description, example FROM help_topic WHERE name="SHOW";
    поиск описания и примеров синтаксиса оператора SHOW.

    ЗАПУСК И ОСТАНОВ СЕРВЕРА.

    Этот пункт полезен, для того чтобы правильно запускать демона mysqld, конечно демон можно просто запускать «ручками» или закинуть в каталог «автозагрузки», но меня интересовали решения более красивые:
    1. запуск демона mysqld при старте системы с использованием конфигурационного файла rc.conf
    2. запуск/останов/перезапуск демона mysqld вручную

    Запуск демона mysqld при старте системы.

    Так как я устанавливал mysql из «портов» то после установки у меня появился файл: /usr/local/etc/rc.d/mysql-server.sh Это специальный скрипт для системы «rc.d», которая появилась в FreeBSD 5.X. С помощью этого скрипта можно воспользоваться главным конфигурационным файлом FreeBSD rc.conf для запуска mysqld при старте системы и задания нужных опций, с которыми демон должен стартовать. Именно этим файлом я воспользуюсь для ответа на первый вопрос:
    shell#> mv /usr/local/etc/rc.d/mysql-server.sh  /etc/rc.d/mysql
    shell#> chmod  555  /etc/rc.d/mysql
    shell#> chown  root:wheel  /etc/rc.d/mysql

    затем в файле «/etc/rc.d/mysql» нужно изменить строчку:
    # KEYWORD: shutdown

    на строчку:
    # KEYWORD: FreeBSD

    а в файле /«etc/rc.conf» добавить строчку:
    mysql_enable="YES"

    Для mysqld можно включить в /etc/rc.conf дополнительные опции, описание которых можно просмотреть в самом скрипте «/etc/rc.d/mysql», а также опции, которые поддерживаются самой системой «rc.d» можно посмотреть в«man rc.subr». ( * )

    Запуск/останов/перезапуск демона mysqld вручную.

    Если было выполнено то, что написано выше то демон mysqld, можно контролировать с помощью системы «rc.d»:
    shell#> /etc/rc.d/mysql [fast|force](start|stop|restart|rcvar|status|poll)

    или можно воспользоваться утилитами самого дистрибутива mysqladmin ( ** ), mysqld_safe
    Останов:
    shell#> mysqladmin shutdown
    или
    shell#> kill  `cat  <путь_к_pid_файлу_mysqld>`
    если где находиться "pid" файл незнаем, то ищем PID процесса с помощью:
    shell#> ps  -auxww  |  grep  mysql  |  grep  -v  grep

    Запуск:
    shell#> /usr/local/bin/mysqld_safe  &
    При таком запуске «mysqld_safe» запустить «mysqld» с параметрами по умолчанию (большая часть, которых зависит от того, как и куда устанавливался "mysqld" и от опций указанных в конфигурационном файле «my.cnf»).
    Но можно самому передать нужные опции «mysqld», указав эти опции в качестве параметров «mysqld_safe». Список возможных опций можно посмотреть «man mysqld» и «man mysqld_safe» если опции нельзя передать с помощью параметров, то их можно указать в конфигурационном файле «my.cnf».

    Список полезных опций, которыми мне приходилось часто пользоваться при работе с «mysqld_safe»:
    --defaults-file путь и имя конфигурационного файла «my.conf».
    --ledir где будет искаться исполняемый файл «mysqld»
    --basedir путь до установочной директории «mysqld», обычно все «относительные» пути будут определяться от этой директории.
    --user имя пользователя под которым будет работать демон «mysqld».
    --datadir путь к «каталогу данных».
    --pid-file путь и имя PID файла.
    --log куда протоколировать события.
    --err-log куда протоколировать ошибки (man mysqld_safe)

    РАБОТА С УЧЕТНЫМИ ЗАПИСЯМИ ПОЛЬЗОВАТЕЛЕЙ MySQL.

    Идентификация и права доступа.

    MySQL идентификация осуществляется по имени «хоста» и имени «пользователя», а проверка прав доступа к данным осуществляется два этапа:
    1. сервер проверяет, разрешено ли пользователю вообще подключаться к «mysqld» демону.
    2. если 1-й этап прошел успешно, то сервер начинает, проверяет каждый «запрос» пользователя на наличие привилегий для выполнения этого «запроса».
    За выполнение 1-го этапа отвечает таблица «user» в БД «mysql», т.е чтобы пользователь смог подключиться к серверу «mysqld» для этого пользователя должна существовать соответствующая запись в таблице «user». Выполнение 2-го этапа осуществляется с помощью БД «mysql» таблиц «user», «db» и «host», «tables_priv», «columns_priv».

    Таблицы «user», «db» и «host», «tables_priv», «columns_priv» организуют привилегии четырех уровней.
    Глобальный уровень:
    Глобальные привилегии применяются ко всем базам данных на указанном сервере. Эти привилегии хранятся в таблице «user».
    Уровень базы данных:
    Привилегии базы данных применяются ко всем таблицам указанной базы данных. Эти привилегии хранятся в таблицах «db» и «host».
    Уровень таблицы:
    Привилегии таблицы применяются ко всем столбцам указанной таблицы. Эти привилегии хранятся в таблице «tables_priv».
    Уровень столбца:
    Привилегии столбца применяются к отдельным столбцам указанной таблицы. Эти привилегии хранятся в таблице «columns_priv».

    Во всех таблицах «user», «db» и «host», «tables_priv», «columns_priv» поля можно условно разделить на два типа:
    1. поля контекста.
    2. поля привилегий.
    Поля контекста определяют область действия каждой из записей в таблицах, т.е. контекст, к которому имеет отношение та или иная запись.
    Для таблицы «user» контекстные поля следующие: Host,User,Password
    Для таблицы «db» контекстные поля следующие: Host,Db,User
    Для таблицы «host» контекстные поля следующие: Host, Db
    Для таблицы «tables_priv» контекстные поля следующие: Host,Db,User,Table_name
    Для таблицы «columns_priv» контекстные поля следующие: Host,Db,User,Table_name, Column_name

    Для более понятного понимания значения «контекста» рассмотрим следующий пример: Допустим в БД «mysql» в таблице «tables_priv» есть следующая запись.
    Host Db User Table_name Grantor Timestamp Table_priv Column_priv
    % testdb User1 testtb root@localhost YYY-MM-DD hh:mm:ss Select
    Контекстные поля для таблицы «tables_priv» следующие «Host», «Db», «User», «Table_name» - и так расшифровываем запись исходя из контекста:

    С любого компьютера «%» к БД «testdb» содержащей таблицу «testtb» ; пользователю с учетной записью «user1» выдана привилегия «уровня таблицы» , которая разрешает пользователю «user1» выполнять запрос «SELECT» .

    Поля привилегий определяют привилегии пользователей относительно контекста. Ниже представлена «таблица 1» возможных привилегий с кратким описанием назначений этих привилегий, с указанием типа привилегии для оператора GRANT/REVOKE и уровнем на, котором привилегия существует.

    таблица 1.
    название привилегии для операторов GRANT/REVOKE Название столбца/значения уровень глобальный уровень базы данных уровень таблиц уровень столбцов привилегия влияет на: описание
    SELECT Select_priv X X X X tables без комментариев
    INSERT Insert_priv X X X X tables без комментариев
    UPDATE Update_priv X X X X tables без комментариев
    DELETE Delete_priv X X X tables без комментариев
    CREATE Create_priv X X X databases, tables, or indexes Привилегия CREATE позволяет создавать БД, таблицы
    DROP Drop_priv X X X databases, tables, or indexes Привилегия DROP позволяет удалять БД, таблицы
    RELOAD Reload_priv X server administration Позволяет выполнять reload, refresh, flush-privileges, flush-hosts, flush-logs, and flush-tables в команде mysqladmin
    SHUTDOWN Shutdown_priv X server administration Позволяет выполнять опцию shutdown в команде mysqladmin
    PROCESS Process_priv X server administration Привилегия PROCESS может быть использована для просмотра открытого текста запросов выполняющихся в данный момент, включая запросы на установку или изменение паролей.
    FILE File_priv X file access on server host Привилегия FILE дает вам право читать и записывать файлы на сервере с помощью операторов LOAD DATA INFILE и SELECT ... INTO OUTFILE. Любой пользователь, которому предоставлена такая привилегия, имеет право прочитать или записать любой файл, который может прочитать или записать сервер MySQL. Пользователь также может прочитать любой файл в каталоге текущей базы данных. Однако существующие файлы перезаписывать нельзя.
    GRANT Grant_priv X X X databases, tables, or stored routines Привилегия GRANT позволяет пользователям передавать свои привилегии другим пользователям. Два пользователя с неодинаковыми привилегиями, имея привилегию GRANT, способны объединить свои привилегии
    REFERENCES References_priv X X X X unused Не используется
    INDEX Index_priv X X X tables Привилегия ALTER может быть использована для переименования таблиц и разрушения таким образом всей системы привилегий.
    ALTER Alter_priv X X X tables Привилегия ALTER может быть использована для переименования таблиц и разрушения таким образом всей системы привилегий.
    SHOW DATABASES Show_db_priv X server administration Привилегия SHOW DATABASES разрешает пользователям (account) просматривать имена баз данных используя оператор SHOW DATABASE.
    SUPER Super_priv X server administration Позволяет использовать kill в mysqladmin
    CREATE TEMPORARY TABLES Create_tmp_table_priv X X server administration Привилегия CREATE TEMPORARY TABLES разрешает использование слова TEMPORARY в операторе CREATE TABLE.
    LOCK TABLES Lock_tables_priv X X server administration Позволяет явно использовать оператор LOCK TABLES для блокировки таблиц к которым есть привилегия SELECT.
    EXECUTE Execute_priv X X stored routines
    REPLICATION SLAVE Repl_slave_priv X server administration
    REPLICATION CLIENT Repl_client_priv X server administration Привилегия REPLICATION CLIENT разрешает использовать операторы SHOW MASTER STATUS и SHOW SLAVE STATUS.
    CREATE VIEW Create_view_priv X X views
    SHOW VIEW Show_view_priv X X views
    CREATE ROUTINE Create_routine_priv X X stored routines
    ALTER ROUTINE Alter_routine_priv X X stored routines
    CREATE USER Create_user_priv X server administration

    Так как в таблицах user,host,db поля привилегий имеют тип ENUM('N','Y') т.е значение каждого поля может быть или «Y» или «N», а в таблицах tables_priv, columns_priv поля привилегий имеют тип SET то столбец «название столбца/значения» в «таблице 1» содержит просто название столбца для таблиц с типом поля ENUM, а для таблиц с типом поля SET значение, которое может содержать это поле.

    Создание MySQL пользователей и назначение привилегий.

    Создавать/удалять пользователей MySQL можно используя, операторы CREATE USER, DROP USER: CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'] [, user [IDENTIFIED BY [PASSWORD] 'password']] DROP USER user [, user] ... http://dev.mysql.com/doc/refman/5.0/en/adding-users.html
    http://dev.mysql.com/doc/refman/4.0/ru/adding-users.html

    Назначать/отнимать привилегии лучше используя, операторы GRANT и REVOKE. GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...] ON {tbl_name | * | *.* | db_name.*} TO user_name [IDENTIFIED BY [PASSWORD] 'password'] [, user_name [IDENTIFIED BY 'password'] ...] [REQUIRE NONE | [{SSL| X509}] [CIPHER cipher [AND]] [ISSUER issuer [AND]] [SUBJECT subject]] [WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # | MAX_UPDATES_PER_HOUR # | MAX_CONNECTIONS_PER_HOUR #]] REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...] ON {tbl_name | * | *.* | db_name.*} FROM user_name [, user_name ...] Пример установки привилегий:
    mysql#> GRANT  [тип_привилегии]  ON  [уровень_привилегии]  TO  [имя_пользователя]  IN  IDENTIFIED  BY  '[пароль]';
    mysql#> GRANT  ALL  ON  *.*  TO  "newuser@%.firma.lan"  IN  IDENTIFIED  BY  'qwerty';

    Где:
    [тип_привилегии] - это устанавливаемые привилегии (см. таблица 1, 1-ый столбец), но также существует дополнительная привилегия «ALL» при указании, которой пользователю устанавливаются сразу все привилегии за исключением привилегии «GRANT OPTION»

    [уровень_привилегий] - может быть следующим:
    глобальный уровень - задается как *.*;
    уровень базы данных - задается как [имя_БД].*;
    уровень таблицы - задается как [имя_БД].[имя_таблицы];
    уровень столбца - показано на примере ниже:

    mysql#> GRANT  [тип_привилегии]  [имя_столбца]  ON  [имя_БД].[имя_таблицы]

    [имя_пользователя] - возможен следующий синтаксис для имени пользователя:
    "username@hostname" при использовании такого синтаксиса пользователь «username» сможет подключаться к серверу mysqld только с компьютера с именем «hostname». Возможно, также при задании имени компьютера использовать символ «%», который означает «любой». В примере выше, создается пользователь «newuser» (если такого пользователя до этого не существовала), которому разрешается подключаться с любого компьютера домена «firma.lan» и присваиваются привилегии «глобального уровня» - (*.*). Пароль для входа назначается «qwerty».

    Поиск разрешения привилегии идет следующим образом:
    «use» => «db» & «host» => «tables_priv» => «columns_priv»
    или на языке алгебры логики:
    «user» OR («db» AND «host») OR «tables_priv» OR «columns_priv»

    Если это описать более понятным языком, то если, хоть в одной из указанных таблиц существует разрешение на привилегию для пользователя, то пользователь сможет ей воспользоваться.

    Когда устанавливается, какая либо привилегия на «глобальном уровне» то пользователь может воспользоваться этой привилегией в любой БД, поэтому привилегии на «глобальном уровне» лучше не выдавать, или выдавать только доверенным пользователям.

    Сменить пароль можно с помощь оператора SET PASSWORD (***):
    SET  PASSWORD  =  PASSWORD('some password') SET  PASSWORD  FOR  user  =  PASSWORD('some password') Первый строчка меняет пароль текущему пользователю, а вторая пользователю с именем «user».
    Пример:
    mysql#> SET  PASSWORD  FOR  'username'@'%.loc.gov'  =  PASSWORD('новый_пароль');

    Или также пароль можно сменить с помощью утилиты «mysqladmin»:
    shell#> mysqladmin  -p[старый_пароль]  -u  [пользователь]  password  [новый_пароль]

    СОЗДАНИЕ РЕЗЕРВНОЙ КОПИИ БАЗ ДАННЫХ (БД).

    Для создания копии БД можно использовать утилиту «mysqldump» или перл скрипт «mysqlhotcopy».

    mysqldump

    «mysqldump» - консольный клиент для «бэкапа» или создания «дампов» БД MySQL, таблиц или данных находящихся в этих таблицах. «Дамп» помещается в текстовый файл и выглядит как набор операторов MySQL необходимых для нового воссоздания БД. Синтаксис можно посмотреть «man mysqldump». Часто используемы и наиболее полезные опции (ИМХО):
    --opt аккумулирует в себя сразу несколько опций:
    --add-drop-table
    --add-locks
    --all
    --extended-insert
    --quick
    --lock-tables
    В документации рекомендуется использовать опцию «--opt» при создание копий БД объясняется это тем, что в этой опции содержится опция «--quick» без которой вся выборка в начале загружается в память, а это в свою очередь может создать проблемы при выборки из больших БД.
    -B | --databases создает «дамп» нескольких БД, имена БД задаются виде аргументов
    Пример:
    shell#> mysqldump  --opt  -B  mysql  test
    создаст «дамп» БД mysql и test.
    -A | --all-databases создает «дамп» всех БД тоже самое, что и опция -B, в которой указаны имена всех БД.
    -T | --tab=[путь] для каждой заданной таблицы создает файл a table_name.sql, содержащий SQL CREATE команды для создания таблицы, и файл table_name.txt с данными таблицы и размещает их в [путь]. Работает только если «mysqldump» запущен на тойже машине, что и mysqld.
    -v | --verbose в описание не нуждается
    -F | --flush-logs (****) сброс данных журнала из буфера MySQL-сервера перед началом выполнения «дампа» в файл «журнала обновлений». По умолчанию эта опция сбрасывает данные буфера в журнал обновлений для каждой БД, для которой делается «дамп» тем самым если мы делаем «дамп» для N БД, то будет создано N файлов «журнала обновлений». Если нужно избежать создания для каждой БД отдельного сброса данных «журнала обновлений» то опцию «-F» нужно использовать совместно с опцией «--lock-all-tables» тогда при сбросе буфера для всех таблиц будет использован только один файл «журнал обновлений». Сброс данных в файл «журнала обновлений» обозначает, что будет создан новый файл «журнала обновлений» с новым именем [имя_файл.nnnnnn] где: nnnnnn это порядковый номер «журнала обновлений».

    Также новый файл «журнала обновлений» создается:
    1. при каждом перезапуске сервера
    2. при выполнении команды «flush-logs»
    3. при достижение размера файла >= «max_binlog_size»
    --ignore-table=[bd_name].[tb_name] исключить из «дампа» таблицу с именем [tb_name], чтобы исключить несколько таблиц нужно для каждой таблицы задавать опцию --ignore-table
    -x | --lock-all-tables блокирует все таблицы всех БД. Это осуществляется с помощью установки «global read lock» во время выполнения всего «дампа». Автоматически выключает опции «--single-transaction» и «--lock-tables».
    --add-drop-table эта опция означает, что в созданном «дамп» - файле будет обязательно присутствовать оператор «drop table» перед каждой восстанавливаемой таблицей. На понятном: если восстанавливаемая из «дампа» таблица уже существует в БД то перед началом восстановления она будет удалена из БД и создана заново операторами из «дамп» файла.
    --add-locks перед созданием «дампа» таблицы над этой таблицей выполняется оператор LOCK TABLES по окончанию создания «дампа» выполняется оператор UNLOCK TABLE.
    --all в документации написано «Включает все опции создания объектов, специфичные для MySQL», что скрывается за этой фразой я не знаю :(.
    --extended-insert эта опция позволяет использовать более быстрый режим для оператора INSERT, который заключается в том, что будет использован оператор INSERT с многострочным синтаксисом. Почему будет быстрее можно почитать здесь: http://dev.mysql.com/doc/mysql/ru/insert-speed.html
    --quick указав эту опцию, запросы не «буферизируются», а напрямую посылаются на STDUOT. Я это понимаю так:
    Если бы использовалась «буферизация» то запросы бы помешались в «буфер» до тех пор, пока буфер не будет заполнен до нужного состояния и только, потом это все выводиться на STDOUT. Соответственно буферизация замедляет процесс вывода данных и загружает память при больших БД. Поэтому опция «--quick» и повышает скорость выполнения запросов за счет отключения «буфера».
    --lock-tables блокирует все таблицы блокировкой READ LOCAL. Различие между READ LOCAL и READ состоит в том, что READ LOCAL позволяет выполнять неконфликтующие операторы INSERT во время существования блокировки.

    После прочтения «манулов» для себя я решил запускать «mysqldump» со следующими опциями:
    shell#> mysqldump  --ignore-table=db.table  -x  -F  --opt  -A  >  /[путь_куда_делать_дамп]/[имя_файла_дампа].sql

    Опцию «-F» я использую, чтобы во время «дампа» создавался новый файл «журнала обновлений» это позволит мне в будущем, в случае сбоя, при восстановлении данных использовать только файлы текущего «журнала обновлений», который хранит протокол последних изменений БД после создания её последний резервной копии утилитой «mysqldump».

    Опцию «-x» для того чтобы создавался только один новый файл «журнала обновлений», если не поставить эту опцию то «журналы обновлений» будут создаваться для каждой БД, для которой делается «дамп», а это неудобно потому, что всё равно протоколирование изменений ведется только в один файл «журнала обновлений». (ИМХО)

    Опция «-A» («дамп» всех БД) почти по той же причине, что и опцию «-x» Я не знаю как при восстановлении с использованием «журнала обновлений» выделять из этого журнала изменения только для нужной БД.

    Опция «--ignore-table». Если в моей БД есть таблицы, которые не важны для меня, а исключение их из «дампа» увеличит производительность и уменьшит время создания «дампа». То я исключаю такие таблицы с помощью этой опции. Также для «неважных» БД данных лучше отключить протоколирование в «журнал обновлений». Для этого можно использовать опции «binlog-do-db» или «binlog-ignore-db».

    Опцию «--opt» использую потому, что её рекомендуют использовать :)

    Восстановление БД из «дамп» файлов

    Восстанавливать информацию из «дамп» файлов созданных с помощью утилиты «mysqldump» можно следующими командами:
    shell#> cat  /<путь_до_дамп_файла>/<имя_дамп_файла>  |  mysql
    Или когда «дамп» сделан для определенной БД:
    shell#> mysql  db_name  <  db-backup-file.sql

    «mysqlhothopy» - это скрипт написанный на языке perl для быстрого в режиме on-line резервного копирования БД и таблиц MySQL. Для просмотра подробной справки по mysqlhothopy нужно выполнить команду:
    shell#> perldoc  mysqlhotcopy.sh

    Для себя я решил, что буду использовать для резервного копирования БД только утилиту «mysqldump» так, как для меня важно, чтобы можно было указывать копирования для всех БД, а не только для определенных, как это надо делать в утилите «mysqlhotcopy.sh». Также на мой выбор повлияло то, что для работы утилиты «mysqlhotcopy.sh» необходимо устанавливать дополнительные perl-модули, а мне делать это лениво. (*5*)

    ОБНАРУЖЕНИЕ, ИСПРАВЛЕНИЕ ОШИБОК И ВОССТАНОВЛЕНИЕ БД ПОСЛЕ СБОЯ.

    Процедура обнаружения и исправления ошибок состоит из следующих этапов:
    1. Проверка таблиц на наличие ошибок. Если ошибки в таблице отсутствуют, процедура завершается. В любом другом случае возникает необходимость их устранения.
    2. Перед началом исправления создается копия файлов таблиц на случай негативного развития событий.
    3. Попытка исправления таблицы
    4. Если попытка оказывается неудачной, остается лишь восстанавливать базу данных их архива («дампа») и если есть, то из «журналов обновлений».
    Для использования пункта (4) (восстановление с минимальными потерями) нужно обязательно включить журнал обновлений (опция «--log-bin») и систематически создавать резервные копии БД утилитой «mysqldump» или «mysqlhotcopy».

    Список номеров - описаний ошибок, по которым можно предположить, что таблица повреждена:
    perror 126 127 132 134 135 136 141 144 145

    Перед проверкой и восстановлением таблиц при работающем сервере «mysqld» следует обновить «кеш-память» таблиц командой «mysqladmin flush-tables»

    Проверять и восстанавливать MyISAM таблицы можно с помощью утилиты «myisamchk», а также можно использовать операторы CHECK и REPAIR.

    Синтаксис «myisamchk» можно посмотреть командой: «myisamchk --help | less » вкратце это выглядит так:
    shell#>myisamchk  [список_опций]  [имя_таблицы] ...

    Проверка таблиц на наличие ошибок.

    В качестве [имя_таблицы] может выступать, как и имя проверяемой таблицы, так и имя файла её индекса. Для определения сразу нескольких таблиц определенного каталога можно воспользоваться следующим синтаксисом:
    shell#> myisamchk  список_опций_проверки  *.MYI

    Где «список_опций_проверки»:
    -c | --check обычная проверка (по умолчанию)
    -e | --extend-check более тщательная проверка
    -m | --medium-check детальная проверка (самая долгая)

    Можно проверить все таблицы во всех базах данных, если задать шаблон вместе с путем к каталогу данных MySQL:
    shell#> myisamchk  /path/to/datadir/*/*.MYI

    Исправление таблиц содержащих ошибки.

    Для исправления ошибок можно пользоваться следующими командами:

    восстановление без модификации файла данных (.MYD);
    shell> myisamchk  - -quick  [имя_таблицы]
    если проблема осталась не решённой то:

    может исправить большинство проблем за исключением несовпадения ключей;
    shell> myisamchk  - -recover  [имя_таблицы]
    если проблема осталась не решённой то:

    использует старый метод восстановления, медленней чем «--recover», но может исправить некоторые случаи, в которых не помогает опция «--recover».
    shell> myisamchk  - -safe-recover  [имя_таблицы]

    Восстановление INDEX файла таблицы (*.MYI).

    1. перейти в каталог БД, содержащий файлы поврежденной таблицы.
    2. скопировать файл данных таблицы (*.MYD) в безопасное место.
    3. запустить «mysql» и выполнить следующие команды:
      mysql#> use  [имя_БД];
      mysql#> SET  AUTOCOMMIT=1;
      mysql#> TRUNCATE  TABLE  [имя_восстанавливаемой_таблицы];
      mysql#> quit;
    4. скопировать файл данных таблицы (*.MYD) обратно в каталог БД.
    5. выполнить команду:
      shell#> myisamchk  -r  -q  [имя_таблицы]
    6. затем после восстановления выполнить операторы:
      mysql#> use  [имя_БД];
      mysql#> FLUSH  TABLE [имя_таблицы];
      mysql#> quit;
      Или перезапустить демон "mysqld".
    разбор пункта #3,6.
    Оператор «use [имя_БД]» - делает активной БД [имя_БД].
    Оператор «SET AUTOCOMMIT=1» - команда не обязательная (её можно упустить), устанавливает значение переменной «AUTOCOMMIT» равным единице (*6*).
    Оператор «TRUNCATE TABLE» - опустошает полностью таблицу. Этот оператор похож на «DELETE», который удаляет все строки в таблице.

    Оператор «FLUSH TABLE» принудительно закрывает открытую таблицу, также сбрасывается кэш запросов. Для принудительного закрытия всех таблиц можно использовать оператор «FLUSH TABLES».

    разбор пункта #5
    «myisamchk -r -q [имя_таблицы]» запускает быстрое восстановление таблицы («-q» ключ означает быстрое восстановление, при этом исправляются только файл индексов, а файл данных не изменяется)

    Восстановление файла описания таблицы (*.frm).

    Чтобы воcсоздать файл описаний таблицы, его можно восстановить из архива (если архив создавался), или заново с помощью оператора «CREATE TABLE».
    1. скопировать файл данных таблицы (*.MYD) в безопасное место
    2. восстанавливаем файл из архива или заново создать таблицу с помощью оператора «СREATE TABLE»
    3. снова запускам процедуру восстановления «myisamchk -r -q [имя_таблицы]»

    Работа с блокировками таблиц во время ремонта.

    Сервер MySQL использует два вида блокировок:
    1. внутренняя блокировка
    2. внешняя блокировка (на уровне файловой системы)
    1-я применяется чтобы избежать взаимного влияния запросов клиентов (пример: не позволяет «SELECT» одного клиента выдать не правильные данные из-за одновременной запроса «UPDATE» другого клиента).
    2-я не позволяет внешним программам изменять файлы таблиц, пока с ними работает сервер «mysqld».

    От того включена ли внешняя блокировка будет зависеть, как запускать утилиту «myisamchk» (если нельзя включить (2) то нужно использовать (1)). Просмотреть состояние внешней блокировки можно с помощью команды «SHOW VARIABLES» переменная «skip_external_locking» или «skip_locking» будет содержать значение «ON» или «OFF». Значение «ON» означает, что внешняя блокировка отключена, значение «OFF» наоборот. Для того чтобы изменить значение переменной «skip_external_locking» нужно запустить сервер «mysqld» с соответствующей опцией (по умолчанию во FreeBSD 5.2 значение «ON»).

    Если значение «skip_external_locking» равно «ON» (внешняя блокировка отключена) то для выполнения проверки или восстановления с помощью утилиты «myisamchk» лучше приостановить работу сервера «mysqld» или воспользоваться внутренним механизмом блокировки (закрыть на время доступ к проверяемым таблицам с помощью «mysql» команды «LOCK TABLES»). Для того чтобы воспользоваться внутренним механизмом блокировки для команды «myisamchk» следует:
    1. запустить «mysql» и выполнить команду «LOCK TABLES» для нужной (исправляемой) таблицы
    2. не завершая работы «mysql» запустить утилиту «myisamchk» с нужными опциями проверки/исправления
    3. по окончанию работы утилиты «myisamchk» нужно вернуться к сессии «mysql» выполнить команду «FLUSH TABLES» и снять блокировку «UNLOCK TABLES»
    Механизм блокировки может отличаться в зависимости от режима работы утилиты ясно, что если утилита »myisamchk» запущена только в режиме проверки данных то клиентам можно оставить право на чтения таблицы но, а если утилита будет восстанавливать таблицу то клиентам нужно запретить что либо изменять в таблице.
    использовать механизм блокировки «LOCK» лучше в следующей последовательности:
    1. «LOCK TABLE [имя_таблицы] [READ | WRITE]». Если установлено «READ» для некоторой таблицы, то только этот поток (и все другие потоки) могут читать из данной таблицы. Если для некоторой таблицы установлена блокировка «WRITE», тогда только этот поток, содержащий блокировку, может осуществлять операции чтения «READ» и записи «WRITE» заблокированной таблицей. Остальные потоки блокируются. То есть простыми словами если нам нужно просто проверить таблицу, то устанавливаем блокировку «READ», но а если исправить, то устанавливаем «WRITE».
    2. после того, как закончили проверку/ремонт таблицы, выполняем команду «FLUSH TABLES» для того чтобы сбросить кэш таблиц.
    3. выполнить команду «UNLOCK TABLES» когда нужно будет снять блокировку.

    СОЗДАНИЕ ПРОФИЛАКТИЧЕСКИХ МЕР И ПОДГОТОВКА К САМОМУ ХУДШЕМУ «СБОЮ».

    На основе выше изложенного я выбрал для себя некоторую стратегию подготовки к случаям сбоя MySQL. Стратегия эта очень проста, не нова и заключается в следующем:
    Создание превентивных (профилактических) мер, суть которых выявить сбойные таблицы как можно быстрее и предупредить об этом администратора MySQL. Превентивные меры будут следующие:
    1. проверка таблиц при загрузке системы (полезно при сбоях питания)
    2. проверка таблиц по расписанию во время работы сервера «mysqld».
    3. создание резервных копий БД.
    И конечно самым главным пунктом будет создание резервных копий БД по заданному расписанию (пункт 3).

    Если включена опция «--log-bin | --log-update» то есть, включен «журнал обновлений» то все изменения БД записываются в этот журнал и его можно использовать совместно с созданной резервной копией БД чтобы уменьшить количество потерянных «записей».

    Было бы не плохо, если после создания каждой резервной копии БД «журнал обновлений» обнулялся - это сделает восстановление более ясным так, как в случае сбоя нам для воссоздания БД с минимальным числом потерянных записей необходимо иметь только два слагаемых:

    «последний_журнал_обновлений» + «последняя_резервная_копия_БД» = «БД_с_минимальным_количеством_потерянных_записей»

    ИМХО лучше с помощью опций «--binlog-do-db», «--binlog-ignore-db» исключить из протоколирования БД для, которых восстановление не важно.

    Вот как я решил организовать три пункта своей стратегии:

    пункт #1 «проверка таблиц при загрузке системы»
    Так как для загрузки демона MySQL я использую «rc.d» систему FreeBSD, то логично было бы, встроить проверку таблиц в скрипт «/etc/rc.d/mysql» это также даcт возможность вынести нужные опции проверки в главный конфигурационный файл FreeBSD - /etc/rc.conf.

    Конечно, можно не встраивать проверку таблиц в стартовый скрипт «/etc/rc.d/mysql», помня то - что «/etc/rc.d/mysql» просто запускает другой скрипт «mysqld_safe», а тот в свою очередь уже имеет нужные строчки по проверке таблиц их только нужно найти и раскомментировать. Но мне хотелось, чтоб этой проверкой я мог управлять через «rc.conf».

    Пример: моего «/etc/rc.d/mysql»:
    4 #!/bin/sh 5 # PROVIDE: mysql 6 # REQUIRE: NETWORKING SERVERS 7 # BEFORE: DAEMON 8 # KEYWORD: FreeBSD 9 # 10 # Add the following line to /etc/rc.conf to enable mysql: 11 # mysql_enable (bool): Set to "NO" by default. 12 # Set it to "YES" to enable MySQL. 13 # mysql_limits (bool): Set to "NO" by default. 14 # Set it to yes to run `limits -e -U mysql` 15 # just before mysql starts. 16 # mysql_dbdir (str): Default to "/var/db/mysql" 17 # Base database directory. 18 # mysql_args (str): Custom additional arguments to be passed 19 # to mysqld_safe (default empty). 20 # 21 . /etc/rc.subr 22 name="mysql" 23 rcvar=`set_rcvar` 24 load_rc_config $name 25 : ${mysql_enable="NO"} 26 : ${mysql_limits="NO"} 27 : ${mysql_dbdir="/var/db/mysql"} 28 : ${mysql_args=""} 29 : ${mysql_myisamck="NO"} 30 : ${mysql_myisamck_args="-s -C"} 31 : ${mysql_myisamck_mail_subj="MySQL databases include error!!!"} 32 : ${mysql_myisamck_mail_toaddr="root@localhost"} 33 myisamchk_path="/usr/local/bin/myisamchk" 34 mysql_user="mysql" 35 mysql_limits_args="-e -U ${mysql_user}" 36 pidfile="${mysql_dbdir}/`/bin/hostname`.pid" 37 command="/usr/local/bin/mysqld_safe" 38 command_args="--defaults-extra-file=${mysql_dbdir}/my.cnf --user=${mysql_user} --datadir=${mysql_dbdir} --pid-file=${pidfile} ${mysql_args} > /dev/null &" 39 procname="/usr/local/libexec/mysqld" 40 start_precmd="${name}_prestart" 41 mysql_install_db="/usr/local/bin/mysql_install_db" 42 mysql_install_db_args="--ldata=${mysql_dbdir}" 43 mysql_create_auth_tables() 44 { 45 eval $mysql_install_db $mysql_install_db_args >/dev/null 46 [ $? -eq 0 ] && chown -R ${mysql_user}:${mysql_user} ${mysql_dbdir} 47 } 48 mysql_prestart() 49 { 50 if [ ! -d "${mysql_dbdir}/mysql/." ]; then 51 mysql_create_auth_tables || return 1 52 fi 53 if checkyesno mysql_limits; then 54 eval `/usr/bin/limits ${mysql_limits_args}` 2>/dev/null 55 fi 56 if checkyesno mysql_myisamck; then 57 echo "Start verification MySQL databases..." 58 SAVE_IFS="$IFS" 59 IFS="" 60 errmes=`eval ${myisamchk_path} ${mysql_myisamck_args} ${mysql_dbdir}/*/*.MYI` 61 IFS="$SAVE_IFS" 62 echo "End verification MySQL databases." 63 if [ "$errmes" ]; then 64 echo "$errmes" | /usr/bin/mail -s "${mysql_myisamck_mail_subj}" "${mysql_myisamck_mail_toaddr}" 65 fi 66 return 0 67 fi 68 } 69 run_rc_command "$1"
    Красным шрифтом я выделил фрагменты кода, которые я добавил в скрипт «/etc/rc.d/mysql», который был создан при установке MySQL. Смысл моих строчек очень прост:

    Строчки "29-32" это задание значений по умолчанию для опций, которые можно прописать в файле «/etc/rc.conf» и с их помощью управлять поведением «myisamchk».

    mysql_myisamck
    Может принимать значения YES или NO. Если значение YES то пред запуском «mysqld» будет проходить проверка всех таблиц в «каталоге данных».

    mysql_myisamck_args
    Строка аргументов, которые будут переданы утилите «myiasmchk». По умолчанию заданы аргументы «-s -C». «-s» - обозначает работу в «тихом» режиме то есть на STDOUT будет выводиться информация, только в случае если в таблицах будет найдена ошибка. «-С» обозначает, что проверка будет происходить только тех таблица в которых были изменения после последней проверки.

    mysql_myisamck_mail_subj
    Текстовая строка, которая будет темой в письме уведомлении, которое будет отправляться, в случае если в таблицах будет найдена ошибка.

    mysql_myisamck_mail_toaddr
    Адрес электронной почты, куда будет послано письмо уведомление.

    Строка "33" задает путь к утилите «myisamchk».
    Строки "56-67" собственно и есть код проверки таблиц и отправки письма в случае нахождения ошибок в таблицах.

    пункт #2 «проверка таблиц по расписанию во время работы сервера»
    Реализовать пункта #2 можно разными способами, лично я решил это сделать так:
    1. написать свой скрипт проверки таблиц.
    2. в «crontab» прописать расписание выполнения этого скрипта.
    Первое уже почти сделано в «пункте #1» нужно только файл «/etc/rc.d/mysql» скопировать в другое место переименовать его и удалить не нужные строчки:
    shell#> cp  /etc/rc.d/mysql  /etc/mysqld/cronchmysql.sh

    Пример: вот как у меня выглядит «/etc/mysqld/cronchmysql.sh»:
    #!/bin/sh : ${mysql_dbdir="/var/db/mysql"} : ${mysql_myisamck_args="-s -c"} : ${mysql_myisamck_mail_subj="Cron verification: MySQL databases include error!!!"} : ${mysql_myisamck_mail_toaddr="root@localhost"} command_path="/usr/local/bin/myisamchk" MAILTO="" SAVE_IFS="$IFS" IFS="" errmes=`eval ${command_path} ${mysql_myisamck_args} ${mysql_dbdir}/*/*.MYI 2>&1` IFS="$SAVE_IFS" if [ "$errmes" ]; then echo "$errmes" | /usr/bin/mail -s "${mysql_myisamck_mail_subj}" "${mysql_myisamck_mail_toaddr}" fi exit 0;
    Жирным шрифтом выделено то, что нужно/можно подправить под свои нужды. Что означают эти переменные описано в «пункте #1» за исключением переменной «mysql_dbdir» в ней задаётся путь к «каталогу данных».

    Второе еще проще, чем первое придумываем расписание, по которому будет запускать проверку таблиц. У меня БД ночью почти не используются, поэтому я решил проверку по расписанию назначить один раз в неделю (суббота) в 4 часа утра. Для этого добавил в «crontab» следующею строчку:
    0  4  *  *  6  /etc/mysqld/cronchmysql.sh
    Для редактирования «crontab» нужно использовать команду «crontab -e".

    пункт #3 «создание резервных копий БД»
    Для реализации пункта #3 я буду использовать описанную выше утилиту «mysqldump». и как для пункта #2.1 снова будет написан небольшой скрипт, который будет реализовывать следующие действия:
    1. вызывать «mysqldump» для создания копии всех баз данных с обязательным обнулением «журнала обновлений»
    2. созданная копия БД будет сжиматься с помощью «bzip2».
    3. администратору будет высылаться на электронную почту уведомление, содержащее отчет о выполненных действиях и дополнительную информацию.
    Пример: вот как выглядит мой скприт для создания бэкапа «/etc/mysqld/cronbkmysql.sh».
    #!/bin/sh path_bzip2="/usr/bin/bzip2" path_mysqldump="/usr/local/bin/mysqldump" opt_mysqldump="-x -F --opt -A" backup_dst_dir="/backup/mysql-db" mysql_dbdir="/var/db/mysql" mail_subj="MySQL databases have been backuped!" mail_toaddr="root@localhost" ################################################ umask 0066 MAILTO="" date_dmy=`date "+%d%m%Y"` cd ${mysql_dbdir} date_HM=`date "+%H:%M:%S"` eval "${path_mysqldump} ${opt_mysqldump} 2> ${backup_dst_dir}/dbbackup.out | ${path_bzip2} > ${backup_dst_dir}/${date_dmy}-db.sql.bz2" date_HMend=`date "+%H:%M:%S"` jupdate=`tail -n 1 ${mysql_dbdir}/*.index 2>/dev/null` dbdirsize=`du -hc ${mysql_dbdir}` archsize=`du -hc ${backup_dst_dir}/${date_dmy}-db.sql.bz2 | tail -n 1` echo "--------------------[ ${date_dmy} ]-------------------" >> ${backup_dst_dir}/dbbackup.out echo -e "New name update juurnal it is:\t${jupdate}" >> ${backup_dst_dir}/dbbackup.out echo -e "Backup starting:\t\t${date_HM}" >> ${backup_dst_dir}/dbbackup.out echo -e "Backup end:\t\t\t${date_HMend}" >> ${backup_dst_dir}/dbbackup.out echo -e "Archiv folder:\t\t\t${backup_dst_dir}" >> ${backup_dst_dir}/dbbackup.out echo -e "Archiv file:\t\t\t${date_dmy}-db.sql.bz2" >> ${backup_dst_dir}/dbbackup.out echo -e "Archiv file size:\t\t${archsize}" >> ${backup_dst_dir}/dbbackup.out echo -e "DB diretory size:\n${dbdirsize}" >> ${backup_dst_dir}/dbbackup.out echo "---------------------------------------------------" >> ${backup_dst_dir}/dbbackup.out cat ${backup_dst_dir}/dbbackup.out | /usr/bin/mail -s "${mail_subj}" "${mail_toaddr}" exit 0;
    То что выделено жирным шрифтом эти переменные нужно подправить под свои конкретные настройки и нужды. Остается только снова придумать расписание выполнения скрипта и создать соответствующую запись в «crontab».

    НАСТРОЙКА ОСНОВНЫХ ПАРАМЕТРОВ СЕРВЕРА.

    Настраивать параметры работы сервера можно с помощью конфигурационного файла или опций командной строки. Для меня интересны только следующие опции/параметры:
    --bind-address=IP
    --port=#
    --character-sets-dir=[path]
    --chroot=[path]
    --datadir=[path]
    --log[=file]
    --pid-file=[path]
    --skip-name-resolve
    --skip-networking
    --socket=path
    --user=[user_name]

    Основными для меня являются потому, что изменяя эти опции мы влияем на основные свойства сервера такие, как путь к «каталогу данных», IP адрес и порт на котором демон «mysqld» слушает и т.п.

    Описание каждой этой опции есть в документации MySQL (http://dev.mysql.com/doc/refman/4.0/ru/command-line-options.html) поэтому я опишу только несколько опций, которые полезно использовать в частных случаях:

    --skip-name-resolve
    эту опцию полезно использовать, когда в сети существуют «проблемы» с DNS, при включении этой опции демон «mysqld» не будет преобразовывать IP адреса в их канонические имена. Для использования этой опции, нужно помнить, что в таблице «User» в поле «host» надо использовать IP адреса, а не DNS имена хостов.

    --skip-networking
    эту опцию полезно включать, если вы решили не предоставлять доступ по сети к базам данных. При включении этой опции соединиться сервером можно будет, только используя UNIX SOCKET.

    Для меня удобней всего задавать параметры в конфигурационном файле (http://dev.mysql.com/doc/refman/4.0/ru/adding-users.html). Примеры стандартных конфигурационных файлов можно найти после установки MySQL на жестком диске:
    /usr/local/share/mysql/my-large.cnf
    /usr/local/share/mysql/my-medium.cnf
    /usr/local/share/mysql/my-small.cnf

    РАБОТА НЕСКОЛЬКИХ СЕРВЕРОВ MySQL НА ОДНОМ КОМПЬЮТЕРЕ.

    Официальное мнение читаем здесь http://dev.mysql.com/doc/refman/4.0/ru/installing-many-servers.html.
    Иногда бывает нужно запустить несколько MySQL серверов на одно компьютере, например для тестирования новой версии MySQL или для экспериментов и т.п. Это просто сделать, используя утилиту «mysqld_safe» указав ей соответствующий конфигурационный файл в котором можно/нужно задать основные опции.

    Пример:
    Первый сервер читает конфигурационный файл /etc/mysqld3306.cnf, в котором в секции [mysqld] указано что: port = 3306 socket = /tmp/mysql.sock а с помощью опций «mysqld_safe» также установлен «каталог данных» /var/db/mysql.

    Для запуска второго сервера создадим для этого сервера новый «каталог данных» используя скрипт «mysql_install_db», который устанавливается при установке сервера mysqld, новый конфигурационный файл «/etc/mysqld3307.cnf», и нового системного пользователя «mysql3307»:
    shell#> /stand/sysinstall
    shell#> /usr/local/bin/mysql_install_db  --ldata=/var/db/mysql3307  --user=mysql3307
    shell#> chown  -R  mysql3307:mysql3307  /var/db/mysql3307
    shell#> cp  /etc/mysqld3306.cnf  /etc/mysqld3307.cnf

    Опция «--ldata=/var/db/mysql3307» задает место, где будет создан «каталог данных», опция «--user=mysql3307» задет имя пользователя которому будет разрешено работать с «каталогом данных». Пользователя «mysql3307» я создал с помощью /stand/sysinstall.

    Затем нужно отредактировать файл /etc/mysqld3307.cnf изменить в секции [mysqld] строчки на: port = 3307 socket = /tmp/mysql3307.sock И запустить «mysqld_safe» со следующими опциями (*7*):
    shell#> mysqld_safe  --defaults-file=/etc/mysqld3307/mysqld3307.cnf  --datadir=/var/db/mysql3307  --user=mysql3307  -ledir=/usr/local/libexec  &

    После проделанных действий будет запущен еще один демон «mysqld» работающий с привилегиями системного пользователя «mysql3307». С этим сервером можно будет соединиться по TCP порту 3307 или через UNIX SOCKET /tmp/mysql3307.sock , «каталог данных» обслуживаемый этим сервером находиться в папке «/var/db/mysql3307». Конечно, можно уменьшить число задаваемых опций для «mysqld_safe» задав эти опции в конфигурационном файле, но я для примера задал опции для «mysqld_safe» и частично задал опции в конфигурационнном файле «/etc/mysqld3307/mysqld3307.cnf» (*8*).

    СОВЕТЫ ПО ПОВЫШЕНИЮ БЕЗОПАСНОСТИ MySQL.

    Перед тем как начать перечислять советы приведу список полезных ссылок по безопасности MySQL:

    Securing MySQL step-by-step: http://www.securityfocus.com/infocus/1726
    Secure MySQL Database Design: http://www.securityfocus.com/infocus/1667
    Securing Your MySQL Installation: http://www.kitebird.com/articles/ins-sec.html
    и естественно нужно просмотреть http://dev.mysql.com/doc/refman/5.0/en/security.html


    Советы:

    ЧТО Я ЧИТАЛ ДЛЯ НАПИСАНИЯ ЭТОЙ ЗАМЕТКИ.

    «Читал» сильно сказано :), но внимательно просматривал будет в точку.
    (*) В действительности скрипт «/etc/rc.d/mysql» запускает другой скрипт «mysqld_safe» который в свою очередь запускает демон «mysqld» с параметрами, которые ему передаются с помощью опции «command_args» заданной в rc скрипте. Также в скрипте «/etc/rc.d/mysql» осуществляется проверка на наличие в «каталоге данных» базы данных mysql и таблиц аутентификации если они отсутствуют то создается БД mysql с нужными таблицами.

    (**) Чтобы воспользоваться утилитой «mysqladmin» для взаимодействия с сервером «mysqld», нужно чтобы утилита могла соединиться с сервером с помощью одного из доступных протоколов [tcp|socket|pipe|memory] и с «нужными» правами доступа. По умолчанию в *NIX утилита соединяется через протокол «socket». Переопределить параметры можно с помощью ключей «mysqladmin» или с помощью соответствующего конфигурационного файла «my.cnf».

    (***) Если забыт пароль для учетной записи «root» сервера «mysql» то его можно сменить следующим образом:
    перезапустить сервер с опцией «--skip-grant-tables» эта опция заставляет сервер mysql не использовать таблицы привилегий. То есть разрешает полный доступ всех ко всем БД. сменить для пользователя «root» пароль оператором «UPDATE user SET Password=PASSWORD("<новый_пароль>") WHERE User="root";». выполнить команду «mysqladmin flush-privileges», которая перезагрузит таблицу привилегий (grant).

    (****) В «журнале обновлений» протоколируются изменения всех таблиц для всех БД, чтобы управлять для каких БД делать протоколирование, для каких нет, можно воспользоваться опциями «--binlog-do-db», «--binlog-ignore-db». Эти опции можно задать в конфигурационном файле mysql. Опция «binlog-do-db = [имя_БД]» указывает mysqld ввести протоколирование только для [имя_БД], опция «binlog-ignore-db = [имя_БД]» исключает [имя_БД] из протоколируемых БД. Протоколирование всех БД ведется в один и тот же файл «журнала обновлений» текущие имя файла журнала обновлений можно посмотреть в «каталоге данных» по умолчанию этот файл имеет расширение .index.

    (*5*) Для проверка установлен ли перл модуль DBI можно воспользоваться командой:
    shell#>perl -MDBI -e 1
    установить модуль DBI можно из порта:
    /usr/ports/databases/p5-DBI

    (*6*) Переменная «AUTOCOMMIT = {0 | 1}» не описана в стандартных переменных, которые можно просмотреть с помощью команды «SHOW VARIABLES». Для просмотра значения переменной «AUTOCOMMIT» можно выполнить оператор: «SELECT @@AUTOCOMMIT;». Список нестандартных переменных, и как их изменять, можно просмотреть http://dev.mysql.com/doc/mysql/en/set-option.html. Назначение этой переменной - установка «autocommit» режима. Если значение «1» то все сделанные изменения применяются немедленно. Если установлено значение «0» то можно использовать оператор «COMMIT» для принятия транзакции или «ROLLBACK» для отмены транзакции. При изменение режима «AUTOCOMMIT» из «0» в «1», MySQL автоматически выполняет «COMMIT» для любых транзакций.

    (*7*) Рекомендую обратить внимание на «mysqld_safe» опции: «--defaults-extra-file=path» и «--defaults-file=path» в «man mysqld_safe».

    (*8*) Используя опцию «--ledir» можно запускать разные версии демонов «mysqld».

    (*9*) Операторы «LOAD DATA INFILE» и «SELECT . INTO OUTFILE» опасны тем, что возможно выполнить следующий «ФИНТ»:

    mysql#> create table foo( line blob );
    mysql#> load data infile '/etc/passwd' into table foo;
    mysql#> select * from foo;
    или
    mysql#> select load_file('/etc/passwd');

    поэтому если эти операторы разрешены то необходимо их ограничить используя опцию «--chroot»

    (*10*) Если пользователь наделен привилегией «Grant_priv» то он может предоставлять другим пользователям свои прова. Например: Если предоставить одному пользователю только привилегию SELECT, а второму SELECT и GRANT то второй пользователь может сделать первого пользователя "более продвинутым".

    Writed by Krylov Stanislav aka Dragon
    (November 2005)