Материал: Письменные лекции по дисциплине «Базы данных»

Лекция 6. Администрирование баз данных

●Резервирование и восстановление вручную

●Команды GRANT и REVOKE;

●Утилита mysqldump;

●Утилита mysqlhotcopy;

●Утилита mysqlcheck.

6.1. Резервирование и восстановление вручную

Поскольку таблицы MySQL хранятся в виде файлов, то резервное копирование выполняется легко. Чтобы резервная копия была согласованной, нужно выполнить на выбранных таблицах LOCK TABLES, а

затем FLUSH TABLES. При этом требуется блокировка только на чтение; поэтому другие потоки смогут продолжать запросы на таблицах в то время, пока будут создаваться копии файлов из каталога базы данных. Команда FLUSH TABLE обеспечивает гарантию того, что все активные индексные страницы будут записаны на диск прежде, чем начнется резервное копирование.

Синтаксис LOCK TABLES:

LOCK TABLES tbl_name {READ [LOCAL] | [LOW_PRIORITY] WRITE} [, tbl_name {READ [LOCAL] | [LOW_PRIORITY] WRITE} ...]

...

UNLOCK TABLES

Различие между READ LOCAL и READ состоит в том, что READ LOCAL

позволяет выполнять неконфликтующие команды INSERT во время существования блокировки.

Можно использовать блокировки LOW_PRIORITY WRITE, позволяющие другим потокам получать блокировки READ в то время, как основной поток находится в состоянии ожидания блокировки WRITE.

Пример LOCK TABLES:

● // Блокировка table1 на чтение и table2 на запись

LOCK TABLES table1 READ, table2 WRITE;

// Какие-либо действия

UNLOCK TABLES; // Разблокировка

Т. е. никакой другой поток управления не может обновить таблицу table1 с блокировкой READ или прочитать таблицу table2 с блокировкой WRITE.

Синтаксис FLUSH:

FLUSH flush_option [,flush_option] ...

Примеры FLUSH:

● FLUSH TABLES — закрываются все открытые таблицы и принудительно закрываются все используемые таблицы. Также сбрасывается кэш запросов.

● FLUSH [TABLE | TABLES] tbl_name [,tbl_name...] —

производится сброс только указанных таблиц.

● FLUSH TABLES WITH READ LOCK — блокировать все таблицы во всех базах данных блокировкой READ.

Рабочий пример:

WHERE customer_id=some_id; mysql> UNLOCK TABLES;

Без использования LOCK TABLES существует вероятность того, что какой-либо иной поток управления может вставить новую строку в таблицу trans между выполнением операций SELECT и UPDATE.

Если есть необходимость провести резервное копирование на уровне

SQL, то можно воспользоваться SELECT ... INTO OUTFILE или BACKUP TABLE:

●Оператор SELECT может быть представлен в форме SELECT ...

INTO OUTFILE 'file_name'. Эта разновидность команды SELECT

осуществляет запись выбранных строк в файл, указанный в file_name. Данный файл создается на сервере и до этого не должен

существовать (таким образом, предотвращается разрушение таблиц и файлов). Форма SELECT ... INTO OUTFILE главным образом предназначена для выполнения очень быстрого дампа таблицы на серверном компьютере.

●BACKUP TABLE tbl_name[,tbl_name...] TO '/path/to/backup/directory' — копирует в каталог резервного

копирования тот минимум табличных файлов, который достаточен для восстановления таблицы, после сброса на диск всех изменений. Работает только для таблиц MyISAM. В процессе резервного копирования будет установлена блокировка чтения отдельно для каждой таблицы на время ее копирования. Если необходимо сделать резервное копирование в виде мгновенного образа нескольких таблиц, необходимо сначала запросить LOCK TABLES установки блокировки чтения для каждой таблицы в группе.

Восстановление:

●LOAD DATA INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE table1 — читает строки из текстового файла file_name.txt и вставляет их в таблицу table1. Команда LOAD DATA INFILE

является дополнительной к SELECT ... INTO OUTFILE.

●RESTORE TABLE tbl_name [,tbl_name...] FROM '/path/to/backup/directory' — восстанавливает таблицу(ы) из

резервной копии, созданной с помощью BACKUP TABLE. Существующие таблицы не перезаписываются: при попытке восстановления поверх существующей таблицы будет выдана

ошибка. Восстановление занимает больше времени, нежели BACKUP

— из-за необходимости повторного построения индекса. Чем больше в таблице будет ключей, тем больше времени заберет реконструкция. Эта команда, так же как и BACKUP TABLE, в настоящее время работает только для таблиц MyISAM.

6.2. Команды 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 [{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 ...]

GRANT включен в MySQL начиная с версии 3.22.11 и выше. В более ранних версиях MySQL оператор GRANT ничего не выполняет.

Команды GRANT и REVOKE позволяют системным администраторам создавать пользователей MySQL, а также предоставлять права пользователям или лишать их прав на четырех уровнях привилегий:

1. Глобальный уровень. Глобальные привилегии применяются ко всем базам данных на указанном сервере. Эти привилегии хранятся в таблице mysql.user.

2.Уровень базы данных. Привилегии базы данных применяются ко всем таблицам указанной базы данных. Эти привилегии хранятся в таблицах mysql.db и mysql.host.

3.Уровень таблицы. Привилегии таблицы применяются ко всем столбцам указанной таблицы. Эти привилегии хранятся в таблице

mysql.tables_priv.

4. Уровень столбца. Привилегии столбца применяются к отдельным столбцам указанной таблицы. Эти привилегии хранятся в таблице

mysql.columns_priv.

Если привилегии предоставляются пользователю, которого не существует, то этот пользователь создается.

В таблице приведен список возможных значений параметра priv_type для операторов GRANT и REVOKE: