Механизмы резервного копирования и восстановления MySQL

задняя часть база данных MySQL SQL

1. Стратегия резервного копирования и восстановления

Есть несколько факторов, которые следует учитывать при выполнении операции резервного копирования или восстановления:

  • Определите, является ли механизм хранения резервной копии таблицы транзакционным или нетранзакционным.Два разных метода резервного копирования механизма хранения различаются по обработке согласованности данных.

  • Решите, использовать ли полное или инкрементное резервное копирование. Преимущество полного резервного копирования заключается в том, что резервная копия сохраняет самую последнюю резервную копию, и восстановление может занять меньше времени; недостаток в том, что если объем данных велик, это займет много времени и вызовет долгосрочную нагрузку на система. В отличие от добавочного резервного копирования, требуется резервное копирование только ежедневных добавочных журналов, время резервного копирования короткое, а давление нагрузки небольшое.

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

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

  • Убедитесь, что MySQL включает опцию log-bin.С помощью binlog MySQL может выполнять полное восстановление, восстановление на определенный момент времени или восстановление на основе местоположения, когда это необходимо.

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

2. Логическое резервное копирование и восстановление

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

1. Резервное копирование

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

// 备份指定的数据库或者数据库中的某些表  
shell> mysqldump [options] db_name [tables]  

// 备份指定的一个或多个数据库  
shell> mysqldump [options] --database DB1 [DB2,DB3...]  

// 备份所有数据库  
shell> mysqldump [options] --all-database

Если таблицы в базе данных не указаны, по умолчанию экспортируются все таблицы во всех базах данных.

Пример:
1. Сделайте резервную копию всех баз данных:
shell>mysqldump -uroot -p --all-database > all.sql
2. Проверка резервной копии базы данных
shell>mysqldump -uroot -p test > test.sql
3. Сделайте резервную копию таблицы emp в тестовой базе данных.
shell> mysqldump -uroot -p test emp > emp.sql
4. Сделайте резервную копию таблиц emp и dept в базе данных test.
shell> mysqldump -uroot -p test emp dept > emp_dept.sql
5. Сделайте резервную копию всех таблиц в тестовой базе данных в виде текста с разделителями-запятыми, создайте резервную копию в /tmp:
shell> mysqlddump -uroot -p -T /tmp test emp --fields-terminated-by ','
shell> more emp.txt  

1,z1
2,z2
3,z3
4,z4

Уведомление:Чтобы обеспечить согласованность резервного копирования данных,myisam 存储引擎在备份时需要加上 -l 参数, означает добавление блокировок чтения ко всем таблицам.Во время резервного копирования все таблицы доступны только для чтения и не могут быть обновлены. Но для механизма хранения транзакций можно использовать лучшую опцию --single-transaction, эта опция позволяет механизму хранения innodb получить моментальный снимок, чтобы можно было гарантировать согласованность данных резервной копии.

2. Полное восстановление

Восстановление mysqldump так же просто, как выполнение резервной копии в качестве входных данных:

mysql -uroot -p db_name < backfile

Уведомление, данные не полные после восстановления бэкапа, и лог выполненный после бекапа нужно переделать:

mysqlbinlog binlog-file | mysql -uroot -p
Завершите пример резервного копирования и восстановления mysqldump:
1. В 2:00 создайте резервную копию базы данных:
root@bogon:/usr/local/mysql/bin$ ./mysqldump -uroot -p -l -F t2 > t2.dmp
Enter password:

в-l 参数表示给所有表加读锁，-F 表示生成一个新的日志文件, в это время данные таблицы emp в t2 следующие:

# 为了便于测试，执行 reset master 删除所有 binlog。
MySQL [(none)]> reset master;
Query OK, 0 rows affected (0.00 sec)

# 此时只有一个 binlog 日志文件   mysql-bin.000001
MySQL [t2]> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)
2. После завершения резервного копирования вставьте новые данные:
# 因为上一步执行是加入了 -F 选项, 所以接下来的操作会被记录到新的二进制文件，即名为 mysql-bin.000002 的文件
MySQL [t2]> insert into test values (3,'c');
Query OK, 1 row affected (0.00 sec)

MySQL [t2]> insert into test values (4,'d');
Query OK, 1 row affected (0.00 sec)
3. Внезапный сбой базы данных (на самом деле малый партнер удаляет базу данных, чтобы потренироваться в игре), и доступ к данным невозможен. Необходимо восстановить резервную копию:

Удалите библиотеку и запустите:

# 这里为了便于测试，不把删库操作记入日志，当前 session 设置 sql_log_bin 为 off。
# 删库后，执行 flush logs，让后续的 binlog 到新的文件中，即名为 mysql-bin.000003中
MySQL [t2]> set sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)

MySQL [t2]> show variables like "%sql_log_bin%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

MySQL [t2]> drop database t2;
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> flush logs;
Query OK, 0 rows affected (0.22 sec)


MySQL [t2]> drop database t2;
Query OK, 3 rows affected (0.23 sec)

MySQL [(none)]> exit;
Bye

Восстановление данных:

root@bogon:/usr/local/mysql/bin# ./mysql -e "create database t2"   
root@bogon:/usr/local/mysql/bin# ./mysql t2 < t2.dmp 

*******************************************************************
MySQL [t2]> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)
4. Используйте mysqlbinlog для восстановления binglog с момента создания резервной копии mysqldump.

По содержанию предыдущей операции видно, что операция от момента резервного копирования до момента удаления базы записана в файле mysql-bin.000002

root@bogon:/usr/local/mysql/bin# ./mysqlbinlog --no-defaults /data/mysql/mysql-bin.000002 | ./mysql t2

*******************************************************
MySQL [t2]> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

Пока восстановление данных проходит успешно.

3. Восстановление на определенный момент времени

Из-за неправильной работы, такой как удаление таблицы по ошибке, в настоящее время бесполезно использовать полное восстановление, потому что в журнале все еще есть заявления о неправильной работе, нам нужно восстановить состояние до неправильной операции, а затем пропустить инструкция misoperation, а затем возобновить выполнение инструкции, выполненной позже, для завершения восстановления. Этот тип восстановления называется неполным восстановлением.В MySQL неполное восстановление делится на восстановление на момент времени и восстановление на основе местоположения. Шаги операции для восстановления на момент времени:

(1) Если в 10:00 произойдет сбой, вы можете использовать следующую инструкцию для восстановления данных до сбоя с помощью резервного копирования и binlog:
shell>mysqlbinlog --stop-date="2017-09-30 9:59:59" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword
(2) Пропустите момент времени во время сбоя и продолжите выполнение следующего двоичного журнала, чтобы завершить восстановление.
shell>mysqlbinlog --start-date="2017-09-30 10:01:00" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword

4. Восстановление на основе местоположения

Аналогично восстановлению на момент времени, но более точно, поскольку в один и тот же момент времени может выполняться множество операторов SQL. Этапы восстановления следующие:

(1) Выполните команду в оболочке:
shell>mysqlbinlog --start-date="2017-09-30 9:59:59" --stop-date="2017-09-30 10:01:00" /data/mysql/mysql-bin.123456 > /tmp/mysql_restore.sql

Эта команда создаст небольшой текстовый файл в каталоге /tmp, отредактирует этот файл и узнает номера позиций до и после сообщения об ошибке, например, номера позиций 368312 и 368315 соответственно.

(2) После восстановления предыдущего файла резервной копии в командной строке необходимо ввести следующее:
shell>mysqlbinlog --stop-position="368312" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword  
shell>mysqlbinlog --start-position="368315" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword

Первая строка выше возобновит все транзакции с того места, где они были остановлены. Следующая строка возобновит все транзакции с заданной начальной позиции до конца двоичного журнала. Поскольку выходные данные mysqlbinlog включают оператор set timestamp, предшествующий каждой записи оператора sql, восстановленные данные и соответствующий журнал mysql будут отражать выполнение транзакции.原时间。

3. Физическое резервное копирование и восстановление

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

1. Холодное резервное копирование

Холодное резервное копирование на самом деле является методом остановки служб баз данных и файлов данных cp. (В принципе не рассматривайте этот метод)

2. Горячее резервное копирование

В MySQL способы горячего резервного копирования для разных движков хранения также различаются.

(1) механизм хранения myisam

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

  • Использование инструмента mysqlhotcopy

// mysqlhotcopy 是 MySQL 的一个自带的热备份工具  
shell> mysqlhotcopy db_name [/path/to/new_directory]

  • Ручное копирование таблицы блокировки

// 在 mysqlhotcopy 使用不正常的情况下，可以用手工来做热备份

mysql>flush tables for read;

cp 数据文件到备份目录即可，
(2) механизм хранения innodb (написан отдельно)

Используйте сторонние инструменты ibbackup, xtrabackup, innobacupex

4. Импорт и экспорт таблиц

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

1. Экспорт

В некоторых случаях для некоторых конкретных целей часто необходимо экспортировать данные в таблицу как обычный текст данных, разделенный некоторыми символами, а не операторы sql:

  • Используется для отображения в формате Excel;

  • Просто, чтобы сэкономить место для резервного копирования;

  • Для быстрой загрузки данных скорость загрузки данных загрузки более чем в 20 раз выше, чем при обычной загрузке sql.

Используйте команду select ...into outfile... для экспорта данных, конкретный синтаксис выглядит следующим образом:
mysql> select * from tablename into outfile 'target_file' [option];

где параметр option может быть следующими опциями:

fields terminated by 'string'                   // 字段分隔符，默认为制表符'\t'
fields [optionally] enclosed by 'char'          // 字段引用符，如果加 optionally 选项则只用在 char、varchar 和 text 等字符型字段上，默认不使用引用符  
fields escaped by ‘char’                        // 转移字符、默认为 '\'  
lines starting by 'string'                      // 每行前都加此字符串，默认''  
lines terminated by 'string'                    // 行结束符，默认为'\n'  

# char 表示此符号只能是单个字符，string表示可以是字符串。

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

MySQL [t2]> select * from test into outfile '/data/mysql/outfile.txt' fields terminated by "," enclosed by '"';
Query OK, 4 rows affected (0.02 sec)

zj@bogon:/data/mysql$ more outfile.txt 
"1","a","helloworld"
"2","b","helloworld"
"3","c","helloworld"
"4","d","helloworld"

Обнаружено, что первый столбец имеет числовой тип.Если вы не хотите заключать поле в кавычки, оператор изменяется на:

MySQL [t2]> select * from test into outfile '/data/mysql/outfile2.txt' fields terminated by "," optionally  enclosed by '"';
Query OK, 4 rows affected (0.03 sec)

zj@bogon:/data/mysql$ more outfile2.txt 
1,"a","helloworld"
2,"b","helloworld"
3,"c","helloworld"
4,"d","helloworld"

Тестовые escape-символы.Символы, которые необходимо экранировать в данных экспорта MySQL, в основном включают следующие три категории:

  • сам escape-символ

  • разделитель полей

  • разделитель записей

MySQL [t2]> update test set content = '\\"##!aa' where  id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [t2]> select * from test into outfile '/data/mysql/outfile3.txt' fields terminated by "," optionally enclosed by '"';
Query OK, 4 rows affected (0.03 sec)

*******************************************
zj@bogon:/data/mysql$ more outfile3.txt 
1,"a","\\\"##!aa"
2,"b","helloworld"
3,"c","helloworld"
4,"d","helloworld"

  • Когда команда экспорта содержит символ ссылки на поле, символы, содержащие сам escape-символ и символ ссылки на поле в данных, должны быть экранированы;

  • Когда команда экспорта не содержит ссылок на поля, символы в данных, которые сами содержат escape-символы, и разделители полей должны быть экранированы.

Уведомление:Если выходной файл, сгенерированный командой select... into outfile..., имеет файл с таким же именем в целевом каталоге, он не будет успешно создан, и исходный файл не будет автоматически перезаписан.

Конкретный синтаксис для экспорта данных в виде текста с помощью mysqldump выглядит следующим образом:

mysqldump -u username -T target_dir dbname tablename [option]

где параметр option может быть следующими опциями:

  • --fields-terminated-by=имя (разделитель полей);

  • --fields-enclosed-by=имя (символ ссылки на поле);

  • --fields-Optionally-enclosed-by=name (символ ссылки на поле, используется только в символьных полях, таких как char, varchar и test);

  • --fields-escaped-by=имя (экранирующий символ);

  • --lines-terminated-by=name (разделитель записи);

пример:

root@bogon:/usr/local/mysql/bin# ./mysqldump -uroot -p -T /data/mysql/dump t2 test --fields-terminated-by ',' --fields-optionally-enclosed-by '"'

**************** test.txt **********************
zj@bogon:/data/mysql/dump$ more test.txt 
1,"a","\\\"##!aa"
2,"b","helloworld"
3,"c","helloworld"
4,"d","helloworld"

***************** test.sql *********************
zj@bogon:/data/mysql/dump$ more test.sql 
-- MySQL dump 10.13  Distrib 5.7.18, for Linux (x86_64)
--
-- Host: localhost    Database: t2
-- ------------------------------------------------------
-- Server version    5.7.18-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `test`
--

DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `content` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-09-25 11:14:06

Можно обнаружить, что параметры и синтаксис mysqldump и select... into outfile... очень похожи, за исключением еще одного файла сценария создания таблицы. На самом деле, mysqldump фактически вызывает интерфейс, предоставляемый последним, и добавляет к нему некоторые новые функции.

2. Импорт (импорт обычного текста данных, экспортированного с помощью select... в outfile или mysqldump)

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

Используйте команду "load data infile...", конкретный синтаксис выглядит следующим образом.
mysql> load data [local]infile 'filename' into table tablename [option]

option может быть одним из следующих вариантов:

  • поля заканчиваются строкой (разделитель полей, по умолчанию табуляция 't');

  • поля [необязательно] заключены в 'char' (символ ссылки на поле, если добавлена ​​необязательная опция, она используется только в символьных полях, таких как char varchar text. По умолчанию символ ссылки не используется);

  • поля, экранированные 'char' (экранирующий символ, по умолчанию '')

  • строки, начинающиеся с 'string' (добавляйте эту строку перед каждой строкой, по умолчанию '')

  • строки, заканчивающиеся «строкой» (разделитель строк, по умолчанию «n»)

  • игнорировать числовые строки (игнорировать первые несколько строк данных во входном файле)

  • (col_name_or_user_var,...) (загружать данные в порядке и количестве полей в списке);

  • set col_name = expr,... Преобразуйте столбец в определенное значение, а затем загрузите его.

Значения полей и строк точно такие же, как и в предыдущем select...into outfile.... Разница в том, что есть несколько разных опций. Следующий пример загружает данные из файла 'test.txt' в таблицу контрольная работа:

// 清空表 test  
MySQL [t2]> truncate table test;
Query OK, 0 rows affected (0.07 sec)

MySQL [t2]> load data infile '/data/mysql/outfile.txt' into table test fields terminated by ',' enclosed by '"';
Query OK, 4 rows affected (0.10 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0


MySQL [t2]> select * from test;
+------+------+------------+
| id   | name | content    |
+------+------+------------+
|    1 | a    | helloworld |
|    2 | b    | helloworld |
|    3 | c    | helloworld |
|    4 | d    | helloworld |
+------+------+------------+
4 rows in set (0.00 sec)

Если вы не хотите загружать первые две строки файла, вы можете сделать следующее:

MySQL [t2]> truncate table test;
Query OK, 0 rows affected (0.02 sec)

MySQL [t2]> load data infile '/data/mysql/outfile.txt' into table test fields terminated by ',' enclosed by '"' ignore 2 lines;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

MySQL [t2]> select * from test;
+------+------+------------+
| id   | name | content    |
+------+------+------------+
|    3 | c    | helloworld |
|    4 | d    | helloworld |
+------+------+------------+
2 rows in set (0.02 sec)

Реализовано с помощью mysqldump

грамматика:

shell> mysqlimport -uroot -p [--local] dbname order_tab.txt [option]

где параметр option может быть следующими опциями:

  • --fields-terminated-by=имя (разделитель полей)

  • --fields-enclosed-by=имя (символ ссылки на поле)

  • --fields-Optionally-enclosed-by=name (ссылка на поле, используется только в символьных полях, таких как char, varchar, text и т. д.)

  • --fields-escaped-by=имя (экранирующий символ)

  • --lines-terminated-by=name (разделитель записи)

  • --ignore-lines=число (игнорировать первые несколько строк)

Уведомление:
Если импорт и экспорт являются кроссплатформенными (windows и linux), то обратите внимание на установку параметра line-terminated-by, установите его как line-terminated-by='rn' на windows и установите его как line-terminated -by= в Linux 'n'.

Категории