MySQL

Импорт базы данных:

mysql -uпользователь -p база < db.sql

Экспорт:

mysqldump -uпользователь -p база > db.sql

Значения «пользователь» и «база» замените на имя пользователя и имя базы данных соответственно.

Для экспорта нескольких баз данных можно воспользоваться вот такой командой:

mysqldump -uroot -p -B база1 база2 база3 > db.sql

Где «база1», «база2», «база3» — имена нужных для копирования баз данных, а «-B» — указание, что производится копирование нескольких баз.

Также утилита mysqldump позволяет сделать экспорт всех баз на сервере в один файл sql. Это делает команда:

mysqldump -uroot -p -A > alldb.sql

Выносим кеш и временные таблицы mysql в память. для этого подключим ram-диск:

mount -t tmpfs -o size=1024M tmpfs /tmp/mysql/ echo 'tmpfs /tmp/mysql tmpfs size=1024M,mode=01770,uid=100,gid=101,noatime 0 0' >> /etc/fstab

Посмотрим uid и gid пользователя mysql

id mysql

uid=100(mysql) gid=101(mysql) группы=101(mysql)

В конфиге Mysql /etc/my.cnf добавляем директиву в блоке [mysqld]:

tmpdir=/tmp/mysql/

И перезапускаем mysql сервер.

увидеть создаваемые временные файлы можно с помощью lsof

lsof /tmp | grep mysql

Кстати, в тот же рамдиск можно запихать кеш eAccelerator/xCache/squid.

Чтобы починить базы данных требуется утилита mysqlcheck

Отремонтировать все

mysqlcheck -Ar-u username -p

Оптимизировать

mysqlcheck -Ao-u username -p

Отремонтировать и оптимизировать

mysqlcheck -Aor-u username -p

Рассмотрим неприятную ситуация, когда вследствие отключения сервера по питанию, была поломана база Mysql с таблицами InnoDB. Это может произойти, например, в случае отключения сервера по питанию или некорректного завершения процесса mysql с вылетом в OOM.

В логах при попытке запуска появляются такие сообщения:

mysqld[10332]:InnoDB:Database page corruption on disk or a failed
mysqld[10332]:InnoDB: file read of page 419.
mysqld[10332]:InnoDB:You may have to recover from a backup.
mysqld[10332]:InnoDB:Itis also possible that your operating
mysqld[10332]:InnoDB: system has corrupted its own file cache
mysqld[10332]:InnoDB:and rebooting your computer removes the
mysqld[10332]:InnoDB: error.

База не запускается. Пробуем сделать дамп базы и смотрим на какой таблице запнулись. Когда дамп пройдет без ошибок, это означает успех.

Ремонт:
В /etc/mysql/my.cnf устанавливаем innodb_force_recovery=3 и запускаем Mysql. В данном режиме нам предоставилась возможность запустить Innodb, но делать с ней практически ничего нельзя. Все сервисы следует остановить, база должна быть максимально свободна.
Создаем рядом такую же таблицу только в формате MyISAM.

create table table_tmp LIKE table

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

insert ignore into table_tmp SELECT *from table where id>=185000and id<187000;

Как только сохранили все что возможно, дропаем table. Если не получается обычным дропом, пробуем сдампить базу без таблицы и попробовать дропнуть базу. Если не получается и так, удаляем frm-файл и стартуем сервис.

После восстановления и успешного дампа убираем строчку в my.cnf и продолжаем работу в штатном режиме.

Mysqldump — это одна из утилит, входящих в пакет с клиентскими программами mysql-client. Используется для создания дампа одной или нескольких баз данных, отдельных таблиц или только их структуры с целью резервирования нужной информации и дальнейшего его восстановления в будущем. Дамп содержит в себе набор SQL-команд, которые выполняются последовательно при разворачивании.

При запуске mysqldump в качестве аргумента передается название базы данных либо ее определенные таблицы или перечисляются несколько баз с помощью ключа «—databases -B» либо все «—all-databases -A». Также можно указывать дополнительные опции, наиболее полезными из которых являются:
—quick -q – дамп непосредственно направляется на stdout (стандартный вывод — экран), не используя буфер;
—opt – соответствует заданию опций —quick —add-drop-table —add-locks —extended-insert —lock-tables, которые максимально ускоряют создание дампа;
—add-drop-table – в полученном дампе перед SQL-командами создания добавляется команда удаления таблицы (drop table);
—add-locks – добавляются строки для блокирования «LOCK TABLES» и разблокирования «UNLOCK TABLE» таблиц при их создании чтобы не получить какие-либо противоречия в результате;
—extended-insert -e – используется определенный синтаксис SQL-команды INSERT для более быстрого разворачивания данных;
—lock-tables -l – заблокировать все таблицы перед стартом дампа, что благоприятно влияет на паралельные вставки при типе таблиц MyISAM;
—all -a – включить все MySQL-специфичные параметры SQL-команды CREATE;
—complete-insert -с – используется в команде INSERT задание имен столбца;
—force -f – не останавливаться, если получаем SQL-ошибку;
—no-data -d – для получения дампа только структуры таблиц;
—no-create-db -n – ‘CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name;’ не будет помещена в вывод;
—no-create-info -t – не писать информацию для создания таблицы (CREATE TABLE);
—password=password -ppassword, -P 3306 —port=3306, -S /var/lib/mysql/mysql.sock —socket= /tmp/mysql.sock, -u user —user=user – указать пароль, порт или сокет, пользователь для подключения к mysql-серверу;

В самом обычном и стандартном случае используется:

mysqldump —opt database > backup_database.sql – для создания дампа БД database;
mysql database < backup_database.sql – для подключения к серверу mysqld и БД database с помощью клиента mysql, разворачивая туда созданный дамп.
Также возможен непостредственный перенос данных на другой сервер с помощью задания в командной строки:
mysqldump —opt database|mysql —host=remote_mysqld -C database
а если необходимо получить полную копию сервера баз данных вместе с базой mysql, хранящей информацию о пользователях и доступе, нужно остановить СУБД, заархивировать каталог данных и развернуть архив на другом сервере с аналогичной версией mysqld.

Пример скрипта для создания дампа определенных БД с помщью mysqldump:

touch mysql_backup.sh
chmod u+x mysql_backup.sh
cat > mysql_backup.sh

#!/bin/sh
# задаем переменные, с помощью которых потом будет удобно вносить
#корректировки
DATE=`/bin/date "+%Y%m%d"`
dbs="DB1 DB2"
dump_user="root"
dump_pass="password123"
dest_dir="/tmp/backup"
dump_flags="--add-drop-table --add-locks --all --extended-insert
--quick --force"
# собственно сам процесс:
for db in $dbs
do
echo "Dumping now MySQL database $db..."
/usr/bin/mysqldump --user=$dump_user --password=$dump_pas
$dump_flags -B $db >> $dest_dir/$db.$DATE.sql
tar czvf $dest_dir/$db.$DATE.tgz $dest_dir/$db.$DATE.sql
echo "$db sql-file and tgz-archive locate in $dest_dir"
done
exit
^D

Можно еще бекапить бд сразу в архив.

gzip:

mysqldump -uuser -ppass dbase | gzip -cf9 > dbase_date.sql.gz

bzip2:

mysqldump -uuser -ppass dbase | bzip2  > dbase_date.sql.bz2

Для восстановления базы из архива.

gzip:

gunzip < dbase_date.sql.gz | mysql -u root -p dbase

bzip2:

bunzip2 < dbase_date.sql.bz2 | mysql -u root -p dbase

Пример использование некоторых параметров

Для того чтобы сделать дамп несколько баз данных, необходимо использовать параметр --databases (или сокращенно -B), пример:

mysqldump -uroot -h192.168.1.22 -p -B database1 database2 database3 > databases.sql

Например, нам нужны данные с «продакшен версии базы» для «версии разработчика», то есть нам нужна «песочница». Выбираем не более 100 записей:

mysqldump -uroot -h192.168.1.22 -p --where="true limit 100" database > database.sql

Или нам нужна только структура, без данных:

mysqldump -uroot -h192.168.1.22 -p --no-data database > database.sql

Делаем дамп только триггеров, процедур и событий:

mysqldump --no-create-info --no-data --triggers --routines --events -uroot -p database | gzip > ~/database.sql.gz

Шпаргалка по параметрам Приведу некоторые параметры, которые могут понадобится при работе с утилитой mysqldump.

--add-drop-database

Добавляет оператор DROP DATABASE перед каждым оператором CREATE DATABASE.

--add-drop-table

Добавляет оператор DROP TABLE перед каждым оператором CREATE TABLE.

--add-locks

Добавляет оператор LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL).

--all-databases, -A

Сохраняет все таблицы из всех баз данных, которые находятся под управлением текущего сервера.

--allow-keywords

Разрешить создавать имена столбцов, которые совпадают с ключевыми словами. Отсутствие конфликтов обеспечивается прибавлением имени таблицы в качестве префикса к имени каждого столбца.

--comments, -i

Данный параметр позволяет добавить в дамп дополнительную информацию, такую, как версия mysqldump, версия MySQL, имя хоста, на котором расположен сервер MySQL.

--compact

Данный параметр требует от mysqldump создать дамп, используя как можно более компактный формат. Параметр является противоположным --comments.

--compatible=name

Параметр генерирует вывод, который совместим с другими СУБД или более старыми версиями MySQL. Вместо ключевого слова name можно использовать: «ansi», «mysql323», «mysql40», «postgresql», «oracle», «mssql», «db2», «maxdb», «no_key_options», «no_table_options», «no_field_options». Можно использовать несколько значений, разделив их запятыми.

--complete-insert, -c

Используется полная форма оператора INSERT (с именами столбцов).

--create-options

Добавляет дополнительную информацию в операторы CREATE TABLE. Это может быть тип таблицы, начальное значение AUTO_INCREMENT и другие параметры.

--databases, -B

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

--delayed

Использовать команду INSERT DELAYED при вставке строк.

--delete-master-logs

На главном сервере репликации автоматически удаляются бинарные логи (logbin) после того, как дамп был успешно создан при помощи mysqldump. Этот параметр автоматически включает параметр «--master-data».

--disable-keys, -K

Для каждой таблицы, окружает оператор INSERT выражениями /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; и /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; в выводе результата дампа. Это ускорит загрузку данных на сервер для таблиц типа MyISAM, так как индексы создаются после внесения всех данных.

--extended-insert, -e

Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода).

--flush-logs, -F

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

--force, -f

Продолжать даже если в процессе создания дампа произошла ошибка.

--hex-blob

Параметр позволяет представить бинарные данные в полях типа BINARY, VARBINARY, BLOB и BIT в шестнадцатеричном формате. Так последовательность «abc» будет заменена на 0×616263.

--ignore-table=db_name.tbl_name

Позволяет игнорировать таблицу tbl_name базы данных db_name при создании дампа. Если из дампа необходимо исключить несколько таблиц, необходимо использовать несколько параметров «--ignore-table», указывая по одной таблице в каждом из параметров.

--insert-ignore

Добавляет ключевое слово IGNORE в оператор INSERT.

--lock-all-tables, -x

Указание этого параметра приводит к блокировке всех таблиц во всех базах данных на время создания полного дампа всех баз данных.

--lock-tables, -l

Указание этого параметра приводит к блокировке таблиц базы данных, для которой создается дамп.

--no-autocommit

Включает все операторы INSERT, относящиеся к одной таблице, в одну транзакцию, что приводит к увеличению скорости загрузки данных.

--no-create-db, -n

Подавляет создание в дампе операторов CREATE DATABASE, которые автоматически добавляются при использовании параметров --databases и --all-databases.

--no-data, -d

Подавляет создание операторов INSERT в дампе, что может быть полезно при создании дампа структуры базы данных без самих данных.

--opt

Параметр предназначен для оптимизации скорости резервирования данных и является сокращением, включающим следующие опции: --quick --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --set-charset. Начиная с MySQL 4.1, параметр --opt используется по умолчанию, т.е. все вышеперечисленные параметры включаются по умолчанию, даже если они не указываются. Для того чтобы исключить такое поведение, необходимо воспользоваться параметров --skip-opt

--order-by-primary

Указание параметра приводит к тому. что каждая таблица сортируется по первичному ключу или первому уникальному индексу.

--port, -P

Номер TCP порта, используемого для подключения к хосту.

--protocol={TCP|SOCKET|PIPE|MEMORY}

Параметр позволяет задать протокол подключения к серверу.

--quick, -q

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

--quote-names, -Q

Помещает имена баз данных, таблиц и столбцов в обратные апострофы `. Начиная с MySQL 4.1, данный параметр включен по умолчанию.

--replace

Добавляет ключевое слово REPLACE в оператор INSERT. Данный параметр впервые появился в MySQL 5.1.3.

--result-file=/path/to/file, -r /path/to/file

Параметр направляет дамп в файл file. Этот параметр особенно удобен в Windows, без использования командной строки. когда можно перенаправить результат в файл при помощи последовательностей > и >>.

--routines, -R

Данный параметр создает дамп хранимых процедур и функций. Доступен с MySQL 5.1.2.

--single-transaction

Параметр создает дамп в виде одной транзакции.

--skip-comments

Данный параметр позволяет подавить вывод в дамп дополнительной информации.

--socket=/path/to/socket, -S /path/to/socket

Файл сокета для подсоединения к localhost.

--tab=/path/, -T /path/

При использовании этого параметра в каталоге path для каждой таблицы создаются два отдельных файла: tbl_name.sql, содержащий оператор CREATE TABLE, и tbl_name.txt, который содержит данные таблиц, разделенные символом табуляции. Формат данных может быть переопределен явно с помощью параметров --fields-xxx и --lines-xxx.

--tables

Перекрывает действия параметра --databases (-B). Все аргументы, следующие за этим параметром, трактуются как имена таблиц.

--triggers

Создается дамп триггеров. Этот параметр включен по умолчанию. для его отключения следует использовать параметр --skip-triggers.

--events, -E

Создается дамп событий. Смотрите MySQL Event Scheduler или встроенный диспетчер событий в MySQL.

--tz-utc

при использовании данного параметра в дамп будет добавлен оператор вида SET TIME_ZONE='+00:00', который позволит обмениваться дампа в различных временных зонах.

--verbose, -v

Расширенный режим вывода. Вывод более детальной информации о работе программы.

--version, -V

Вывести информацию о версии программы.

--where='where-condition', -w 'where-condition'

Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны.

--xml, -X

Представляет дамп базы данных в виде XML.

--first-slave, -x

Блокирует все таблицы во всех базах данных.

--debug=..., -#

Отслеживать прохождение программы (для отладки).

--help

Вывести справочную информацию и выйти из программы.

Еще пару слов о бекапе в MySQL

mysqlhotcopy для MyISAM

Для быстрого резервирования БД с типом таблиц ISAM и MyISAM можно использовать «mysqlhotcopy», которая скопирует файлы *.frm, *.MYD и *.MYI:

# mysqlhotcopy db_name /path/to/dir

Для InnoDB не подойдет данный способ, потому что при этом типе не обязательно все файлы будут храниться в директории базы данных.

xtrabackup для InnoDB

Для InnoDB есть xtrabackup, рекомендую посмотреть! UPD: XtraBackup — резервное копирование для innoDB

Бин-лог и репликации

Для репликации «mysqldump» не предназначена, для этого есть бин-лог (--log-bin):

# mysqlbinlog binlog.[0-9]* | mysql

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

Как извлечь нужную базу данных из полного бекапа если в одном файле больше 20 разных баз данных?

base_name — Имя Нужной базы.
all_base.sql — Имя MySQL файла с базами.
mybase.sql — Имя Нужной базы.

sed -n -e '/CREATE DATABASE.*base_name/,/CREATE DATABASE/p' all_base.sql > mybase.sql