4.4.1 Резервное копирование баз данных

Поскольку таблицы MySQL хранятся в виде файлов, то резервное копирование выполняется легко. Чтобы резервная копия была согласованной, выполните на выбранных таблицах LOCK TABLES, а затем FLUSH TABLES для этих таблиц (см. разделы section 6.7.2 Синтаксис команд LOCK TABLES/UNLOCK TABLES и see section 4.5.3 Синтаксис команды FLUSH). При этом требуется блокировка только на чтение; поэтому другие потоки смогут продолжать запросы на таблицах в то время, пока будут создаваться копии файлов из каталога базы данных. Команда FLUSH TABLE обеспечивает гарантию того, что все активные индексные страницы будут записаны на диск прежде, чем начнется резервное копирование.

Если есть необходимость провести резервное копирование на уровне SQL, то можно воспользоваться SELECT INTO OUTFILE или BACKUP TABLE (см. разделы section 6.4.1 Синтаксис оператора SELECT и see section 4.4.2 Синтаксис BACKUP TABLE).

Существует еще один способ создать резервную копию базы данных - использовать программу mysqldump или сценарий mysqlhotcopy (см. разделы section 4.8.5 mysqldump, Получение дампов данных и структуры таблицы и see section 4.8.6 mysqlhotcopy, Копирование баз данных и таблиц MySQL). Для этого нужно выполнить следующие действия:

1. Сделать полное резервное копирование баз данных:

   shell> mysqldump --tab=/path/to/some/dir --opt --full
   
   или
   
   shell> mysqlhotcopy database /path/to/some/dir
   

   Можно также просто скопировать табличные файлы (файлы `*.frm', `*.MYD' и `*.MYI') в тот момент, когда сервер не проводит никаких обновлений. Этот метод используется в сценарии mysqlhotcopy.
2. Если mysqld выполняется, остановить его, и затем запустить с опцией --log-update[=file_name] (see section 4.9.3 Журнал обновлений (update)). В файлах журнала обновлений находится информация, необходимая для того, чтобы повторить в базе данных последовательность изменений, внесенных с момента выполнения mysqldump.

Если дело дошло до восстановления, сначала надо попробовать восстановить таблицы с помощью REPAIR TABLE или myisamchk -r - это должно сработать в 99,9% случаев. Если myisamchk не даст результата, попробуйте применить следующую процедуру (эти действия применимы только в случае, если MySQL запускался с --log-update (see section 4.9.3 Журнал обновлений (update))):

1. Восстановите исходный вариант по копии, сделанной в mysqldump.
2. Выполните следующую команду, чтобы повторить обновления из бинарного журнала:

   shell> mysqlbinlog hostname-bin.[0-9]* | mysql
   

   Если используется журнал обновлений, то можно применить:

   shell> ls -1 -t -r hostname.[0-9]* | xargs cat | mysql
   

ls используется для того, чтобы расположить все файлы журнала обновлений в правильном порядке.

Можно проводить избирательное резервное копирование посредством SELECT * INTO OUTFILE 'file_name' FROM tbl_name, а восстановление - при помощи LOAD DATA INFILE 'file_name' REPLACE ... Чтобы избежать повторения записей, в таблице должен быть первичный или уникальный ключ. Ключевое слово REPLACE задает замену старых записей новыми в случае, когда новая запись в значении уникального ключа повторяет старую.

Если в системе, где выполняется резервное копирование, возникают проблемы с производительностью, то решить их можно, установив репликацию и выполняя резервное копирование на подчиненном сервере вместо головного (see section 4.10.1 Введение).

Пользователи файловой системы Veritas могут поступить следующим образом:

1. Из клиента (или Perl) выполнить: FLUSH TABLES WITH READ LOCK.
2. Из другого shell выполнить: mount vxfs snapshot.
3. Из первого клиента выполнить: UNLOCK TABLES.
4. Скопировать файлы из образа.
5. Демонтировать образ.

User Comments

I found that when using the "SELECT * FROM %
TABLE% INTO OUTFILE '%filename%' " syntax, my
access privileges to the directory structure
where based on the user under which the mySQL
database server was running, not the mysql client.

A filename without a fully qualified path was
saved in the /usr/local/var directory - the
location for my database files.

Also, if you connect to the database server from
a remote host, the files are created on the
server as opposed to the connecting client.

Remember - MySQL operates over sockets and so
sending the entire database to the client is what
SELECT is for. If the client wants the data then
they should SELECT it and save it.

Isn't backing up to a local drive what you would
usually do? Imagine dialing in from your laptop to
manually run a backup and having mysqld trying to
dump many mb of data across the dialup connection.
If you want to backup to a different machine's
drive, just mount the intended destination drive via
SMB/NFS/AFS/whatever and then backup to that drive.

We use the following script to backup
all databases on our Linux/MySQL server to a
remote Windows machine using Samba. It works well
and run as a cron job requires minimial
administration.
------------------
<PRE>
#!/bin/sh

# Define some variables.
BackupServer="SMBServer"
BackupShare="SMBShare"
BackupUser="SMBUser"
BackupPW="SMBPassword"

# Create the temporary copy directory
mkdir /tmp/mysql

# Get the list of MySQL Databases & copy them

for var in `find /var/lib/mysql/ -type d | \
sed -e "s/\/var\/lib\/mysql\///"`; do
mysqlhotcopy -q "$var" /tmp/mysql
done

# Tar/gzip data

date=`date -I`
tar czf /tmp/mysql-$date.tar.gz -C /tmp/ mysql/

# Move the data to FS1
smbclient //$BackupServer/$BackupShare \
$BackupPW -d0 -W WORKGROUP -U $BackupUser \
-c "put /tmp/mysql-$date.tar.gz \
mysql-$date.tar.gz"

# Delete temp files

rm -fR /tmp/mysql/
rm -f /tmp/mysql-$date.tar.gz
</PRE>

The command above, "mysqldump --
tab=/path/to/some/dir --opt --full"
doesn't work with .52.

--full is not recognized
--all-databases which is part of --opt, can't be used
with --tab

The "mysqlhotcopy" does not seem to work with
innodb-tables, as it needs *MYI or other table-files for
copying. only the *FRM of my innoDB-tables were copied.

On large databases, use -q when doing a mysqldump. Otherwise, the mysqldump process will buffer the data, and you'll end up with a larger process than need be.

One more thing. You can speed up restore by dumping table definition and table data into different files. When your database needs a restore, drop the database, recreate table definitions, drop all indices, load data, recreate indeces using "alter table" syntax. Be sure to add all indeces for a table in one alter table statement, since altering indices involves temporary tables.

Add your own comment.