MySQL 백업 및 복구를 위한 4가지 암호 해독 방법

백업은 무엇보다 중요합니다. 오늘은 일반적으로 사용되는 몇 가지 백업 방법과 복구 단계를 요약하겠습니다.

1.mysqldump

일상 업무에서는 mysqldump 명령을 사용하여 SQL 형식의 덤프 파일을 생성하여 데이터베이스를 백업합니다. 또는 데이터를 내보내고 데이터 마이그레이션 및 활성/대기 설정과 같은 작업을 수행할 수 있습니다. mysqldump는 원본 데이터베이스 개체 정의와 테이블 데이터를 복사하여 실행 가능한 SQL 문 집합을 생성하는 논리적 백업 도구입니다. 기본적으로 insert 문이 생성되며, 다른 구분 기호가 포함된 출력이나 XML 형식의 파일도 생성될 수 있습니다.

으아악

일상 사용량을 간단히 살펴보겠습니다.
모든 데이터베이스 백업:

으아악

지정된 데이터베이스 백업:

으아악

하나의 데이터만 백업하는 경우 –databases를 생략하고 직접 작성할 수 있습니다: mysqldump test > dump.sql. 그러나 추가하지 않으면 데이터베이스 덤프 출력에 데이터베이스 생성이 포함되지 않습니다. use 문을 사용하면 이 매개변수를 추가하지 않고도 다른 이름을 가진 데이터베이스로 직접 가져올 수 있습니다.

물론 특정 테이블만 백업할 수도 있습니다:

으아악

몇 가지 간단한 사용법을 이해한 후 몇 가지 매개변수에 집중해 보겠습니다.

• master-data는 백업을 통해 복원된 인스턴스 간의 복제 관계를 설정하는 데 사용되는 백업 데이터의 Binlog 위치와 Binlog 파일 이름을 가져옵니다. 이 매개변수는 기본적으로 활성화됩니다.
• dump-slave는 슬레이브에 데이터를 덤프하고 새 슬레이브를 생성하는 데 사용됩니다. mysqldump를 사용할 때 테이블을 잠그기 때문에 대부분의 경우 내보내기 작업은 일반적으로 읽기 전용 대기 데이터베이스에서 수행됩니다. 기본 데이터베이스의 Relay_Master_Log_File 및 Exec_Master_Log_Pos를 얻으려면 이 매개 변수를 사용해야 합니다. 이 매개변수는 5.7에서만 사용할 수 있습니다. 5.7
이후에만 사용할 수 있습니다.
• no-data, -d는 데이터를 내보내지 않고 데이터베이스 테이블 구조만 내보냅니다

mysqldump를 사용할 때 테이블이 잠긴다고 방금 설명했습니다. 잠금 메커니즘을 자세히 살펴보겠습니다.

두 개의 창을 열고 첫 번째 창에서 mysqldump -uroot -pxxxxx –master-data=2 –databases dbname > /tmp/dbnamedate +%F.sql을 실행합니다. 그런 다음 두 번째 창에 로그인하고 show 프로세스를 사용합니다. 명령은 현재 덤프 세션이 실행되고 있음을 확인할 수 있습니다.

SELECT /!40001 SQL_NO_CACHE / * FROM table_name; 이 SQL이 no_cache 모드에서 데이터를 쿼리하는 것을 볼 수 있습니다.

그런 다음 동일한 테이블에서 select를 실행했는데 차단된 것을 발견했습니다. 커서가 반환되지 않습니다.

일반적으로 이런 종류의 파일을 발견하면 파일이 잠겨 있는지 궁금합니다. 확인을 위해 잠금 정보를 확인하고 덤프 프로세스가 실제로 잠겨 있는지 확인하겠습니다.

일반적으로 이런 종류의 파일을 발견하면 파일이 잠겨 있는지 궁금합니다. 확인을 위해 잠금 정보를 확인하고 덤프 프로세스가 실제로 잠겨 있는지 확인하겠습니다.

특정 General_log를 열고 당시 작업을 살펴보겠습니다.

으아악

(열려 있는 모든 테이블을 닫고 테이블 잠금 해제가 명시적으로 실행될 때까지 데이터베이스의 모든 테이블에 읽기 잠금을 추가합니다. 이 작업은 데이터 백업 중에 자주 사용됩니다.)

으아악

(–master-data=2를 사용했기 때문입니다)

그래서 이 시간에는 시계가 잠기게 됩니다.

–master-data 매개변수(mysqldump -uroot -pxx –databases db > /tmp/dbnamedate +%F.sql)를 추가하지 않으면 mysql은 백업할 각 테이블에 대해 LOCK TABLES table_name1 READ,LOCK을 표시합니다. TABLES table_name2 READ, 읽기 차단이 없습니다.

비잠금 방식이 있나요? 실제로는 –single-transaction을 사용하여 백업 작업을 트랜잭션에 넣는 것입니다.

단일 트랜잭션 매개변수를 사용한 Mysqldump 백업 프로세스:

MySQL 버전 5.6이라면

백업 사이에는 먼저 FLUSH TABLES WITH READ LOCK을 수행한 다음 트랜잭션 수준을 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ로 설정하고 백업을 위해 트랜잭션 시작을 시작합니다. 이때 백업 프로세스는 먼저 저장점을 생성합니다. , 데이터베이스의 테이블을 순차적으로 백업합니다. 백업이 완료된 후 데이터 일관성을 보장하기 위해 이전 저장점으로 롤백됩니다.

如果是5.7版本的MySQL

备份前的操作相同，只是没有了savepoint。

不过不管是哪个版本，只有InnoDB表是在一个一致性的状态。其它的任何MyISAM表或内存表是没有用的。 mysqldump的优势是可以查看或者编辑十分方便，它也可以灵活性的恢复之前的数据。它也不关心底层的存储引擎，既适用于支持事务的，也适用于不支持事务的表。不过它不能作为一个快速备份大量的数据或可伸缩的解决方案。如果数据库过大,即使备份步骤需要的时间不算太久,但有可能恢复数据的速度也会非常慢,因为它涉及的SQL语句插入磁盘I/O,创建索引等等。 对于大规模的备份和恢复,更合适的做法是物理备份,复制其原始格式的数据文件,可以快速恢复:如果你的表主要是InnoDB表,或者如果你有一个InnoDB和MyISAM表,可以考虑使用MySQL的mysqlbackup命令备份。

恢复操作：

先看一下当前的数据：

dbadmin@test 11:10:34>select * from t;
+——-+
|  id  |
+——-+
|  1  |
+——-+
1 row in set (0.00 sec)

备份；

mysqldump -uroot -proot@1234 –master-data=1 test >test.sql

模拟增量操作

dbadmin@test 11:15:17>insert into t values (2);

Query OK, 1 row affected (0.00 sec)

dbadmin@test 11:15:36>select * from t; +——+ | id | +——+ | 1 | | 2 | +——+ 2 rows in set (0.00 sec)

模拟误操作：

dbadmin@test 11:15:41>truncate table t;

Query OK, 0 rows affected (0.01 sec)

dbadmin@test 11:16:14>select * from t;

Empty set (0.00 sec)

模拟恢复操作：

step 1：找到误操作的log position

dbadmin@test 11:20:57>show master logs;

dbadmin@(none) 11:21:37>show binlog events in ‘mysql-bin.000004’;

查看可以看到是444。

step 2：恢复到备份

dbadmin@test 11:16:25>source test.sql
dbadmin@test 11:17:26>select from t;
+——-+
|  id  |
+——-+
|  1  |
+——-+
1 row in set (0.00 sec)

step 3: 因为我们在备份的时候使用了master-data的参数，所以可以直接看到备份时候的最后位置，然后应用中间的log。查看可以看到是187。

我们使用mysqlbinlog得到这一段时间的操作，其实我们也可以用这个工具得到操作后使用sed进行undo的操作。

mysqlbinlog –start-position=187 –stop-position=444 mysql-bin.000004 > increment.sql

dbadmin@test 11:44:37>source /u01/my3307/log/increment.sql dbadmin@test 11:44:50>select from t; +——+ | id | +——+ | 1 | | 2 | +——+

至此数据恢复。

二、mysqlbackup

mysqlbackup是Oracle公司提供的针对企业的备份软件，全名叫做MySQL Enterprise Backup，是一个收费的软件。

我们简单来看一下这个工具的使用。

查看所有的帮助：

我这里只是截取了一小部分，这个帮助很长，参数很多，功能很全，是oracle官方主推的备份方式。

全量备份

mysqlbackup –user=root –password=ucjmh –databases=’t1′ –encrypt-password=1 –with-timestamp –backup-dir=/u01/backup/ backup

解释一下参数：

• databases 要备份的数据库
• with-timestamp 产生一个当前时间的备份目录。mysqlbackup这个工具要求一个空目录才能做备份。所以这个会常用。
• backup-dir 备份的目录 。
• compress：压缩备份 这个提供了多种压缩方法和压缩级别。1–9,压缩比依次递增。

backup 是备份的方式， 一共有如下几种方式，我会在一个恢复案例里把常用的几个都用到。

Backup operations: backup, backup-and-apply-log, backup-to-image

Update operations: apply-log, apply-incremental-backup

Restore operations: copy-back, copy-back-and-apply-log

Validation operation: validate

Single-file backup operations: image-to-backup-dir, backup-dir-to-image, list-image, extract

其实，在大多数情况下,单个文件备份,使用backup-to-image命令创建,性能优于backup。buckup这个命令只执行一个完整的备份过程的初始阶段。需要通过再次运行mysqlbackup运用apply-log 命令，使备份一致。

mysqlbackup –user=root –password=ucjmh –databases=’t1′ –encrypt-password=1 –with-timestamp –backup-dir=/u01/backup/2017-04-28_12-49-35/ apply-log

当然你可以直接用backup-and-apply-log 不过这个时候的备份将不能用于增量了。

增量备份：

mysqlbackup –user=root –password=ucjmh –databases=’t1′ –encrypt-password=1 –with-timestamp –backup-dir=/u01/backup/ –incremental –incremental-base=dir:/u01/backup/2017-04-28_12-49-35 –incremental-backup-dir=/u01/backup/incremental backup

这个是基于上次的备份做的备份，当然也可以基于某一个log position之后做。

–incremental：代表增量备份；

–incremental-base：上次全备的目录；

–incremental-backup-dir：增量备份的保存的目录

大致梳理一下操作步骤，来了解一下恢复的原理：

首先检测并应用全备事务日志文件（这里是因为我备份的时候用的是backup而不是backup-and-apply-log），然后基于全备去应用增量的log。这个时候如果有多次增量备份也可以（基于LSN点向后应用）。 所有的都应用完成之后就是一个可以直接cp的数据库了。

个人感觉这个工具比xtrabackup好用，但是xtrabackup是开源的，所以市场占有量才会大，才会更有名，更多人用吧。

三、mysqlhotcopy

mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库.它是备份数据库或单个表最快的途径,完全属于物理备份,但只能用于备份MyISAM存储引擎和ARCHIVE引擎，并且是一个服务器命令，只能运行在数据库目录所在的机器上.与mysqldump备份不同,mysqldump属于逻辑备份,备份时是执行的sql语句.使用mysqlhotcopy命令前需要要安装相应的软件依赖包. 因为这个功能很弱，我们只简单的介绍一个怎么用：

备份一个库

mysqlhotcopy db_name [/path/to/new_directory]

备份一张表

mysqlhotcopy db_name./table_name/ /path/to/new_directory

更详细的使用可以使用perldoc mysqlhotcopy查看。

四、xtrabackup/innobackupex

Percona XtraBackup是一款基于MySQL的热备份的开源实用程序，它可以备份5.1到5.7版本上InnoDB,XtraDB,MyISAM存储引擎的表， Xtrabackup有两个主要的工具：xtrabackup、innobackupex 。

（1）xtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表

（2）innobackupex则封装了xtrabackup，是一个脚本封装，所以能同时备份处理innodb和myisam，但在处理myisam时需要加一个读锁。

首先我们先来简单的了解一下xtrabackup是怎么工作的。xtrabackup基于innodb的crash-recovery（实例恢复）功能，先copy innodb的物理文件（这个时候数据的一致性是无法满足的），然后进行基于redo log进行恢复，达到数据的一致性。

我们还是简单来看一下日常工作中具体的使用：

全备：

xtrabackup –backup –target-dir=/data/backup/base

可以先看到

在备份过程中,可以看到很多输出显示数据文件被复制,以及日志文件线程反复扫描日志文件和复制。

同样的，它也输出了当前的binlog filename和position，如果有gtid(同样也会输出) 可以用于搭建主从。最后一行一定会是你的lsn被copy的信息。 这是因为每次启动备份，都会记录170429 12:54:10 >> log scanned up to (1676085)），然后开始拷贝文件，一般来讲数据库越大拷贝文件是要花费越长的时间，所以说这期间一般情况都会有新的操作，所以说所有文件也可能记录的并不是一个时间点的数据， 为了解决数据这个问题，XtraBackup 就会启动一个后台进程来每秒1次的观测mysql的事务日志，直到备份结束。而且把事务日志中的改变记录下来。我们知道事物日志是会重用的（redo log）,所以这个进程会把redolog写到自己的日志文件xtrabackup_log，这个后台监控进程会记录所有的事务日志的改变，用于保证数据一致性所。

增量备份：

当我们做过全量备份以后会在目录下产生xtrabackup_checkpoints的文件 这里面记录了lsn和备份方式，我们可以基于这次的全量做增量的备份。

$cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1676085
last_lsn = 1676085
compact = 0
recover_binlog_info = 0
xtrabackup –backup –target-dir=/data/backup/inc1 –incremental-basedir=/data/backup/base

这个时候xtrabackup也是去打开了xtrabackup_checkpoints文件进行上一次备份的信息查看。这个时候去查看增量备份的xtrabackup_checkpoints也记录了这些信息。

$cat xtrabackup_checkpoints backup_type = incremental from_lsn = 1676085 to_lsn = 1676085 last_lsn = 1676085 compact = 0 recover_binlog_info = 0

这也意味着你可以在增量的备份上继续增量的备份。

同样的，xtrabackup也支持压缩（–compress）、加密（–encrypt）、并行(–parallel)等操作，但是和mysqlbackup不同的是这个没有同时的备份binlog,而mysqlbackup是备份了binlog的。

我们来模拟一个恢复的过程深入的了解一下原理。

查看当前数据：

dbadmin@test 03:04:33>select from t;
+——-+
|  id  |
+——-+
|  1  |
+——-+
1 row in set (0.00 sec)

全量备份

$xtrabackup –backup –target-dir=/data/backup/base

模拟增量数据

dbadmin@test 03:07:16>select from t;
+——-+
|  id  |
+——-+
|  1  |
|  2  |
+——-+
2 rows in set (0.00 sec)

进行增量备份：

$xtrabackup –backup –target-dir=/data/backup/inc1 –incremental-basedir=/data/backup/base

模拟无备份操作：

dbadmin@test 03:09:42>select * from t;
+——-+
|  id  |
+——-+
|  1  |
|  2  |
|  3  |
+——-+
3 rows in set (0.00 sec)

模拟误操作：

dbadmin@test 03:09:45>truncate table t; Query OK, 0 rows affected (0.00 sec)

模拟恢复操作：

step 1：找到误操作的log position

dbadmin@test 03:10:19>show master logs;

dbadmin@test 03:10:47>show binlog events in ‘mysql-bin.000001’;

1333

我们需要分别对全量、增量备份各做一次prepare操作。

xtrabackup –prepare –apply-log-only –target-dir=/data/backup/base

增量

xtrabackup –prepare –apply-log-only –target-dir=/data/backup/base / –incremental-dir=/data/backup/inc1

如果我们使用它自带的还原命令的时候就要先把data目录给清空。不然就会报如下的错误

$innobackupex –copy-back /data/backup/base/

170429 15:37:19 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.

At the end of a successful copy-back run innobackupex prints “completed OK!”.

innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7) Original data directory /u01/my3307/data is not empty!

当然我们大多数据时候是不会在原来的实例上做操作的，都会把相应的备份在奇他的实例上进行恢复，然后再导出导入到误操作的实例。这里我们直接清掉目录，然后再次运行，查看恢复后的数据：

dbadmin@test 03:41:56>select * from t;
+——-+
|  id  |
+——-+
|  1  |
|  2  |
+——-+
2 rows in set (0.00 sec)

同样的被恢复的目录里会多出来两个文件，一个是xtrabackup_binlog_pos_innodb，一个是xtrabackup_info。在这两个文件中都可以看到你最后的log,pos。在info里还可以看到lsn。我们基于这个pos再进行binlog的重演，恢复在binlog没有被备份的数据。

1076
$mysqlbinlog mysql-bin.000001 –start-position=1076 –stop-position=1333 -vv >increment.sql
dbadmin@test 03:51:25>source /u01/my3307/log/increment.sql dbadmin@test 03:51:34>select * from t;
+——-+
|  id  |
+——-+
|  1  |
|  2  |
|  3  |
+——-+
3 rows in set (0.00 sec)

至此数据恢复完成。

五、直接复制整个数据库目录

MySQL还有一种非常简单的备份方法，就是将MySQL中的数据库文件直接复制出来。这是最简单，速度最快的方法。 不过在此之前，要先将服务器停止，这样才可以保证在复制期间数据库的数据不会发生变化。如果在复制数据库的过程中还有数据写入，就会造成数据不一致。这种情况在开发环境可以，但是在生产环境中很难允许备份服务器。

注意：这种方法不适用于InnoDB存储引擎的表，而对于MyISAM存储引擎的表很方便。同时，还原时MySQL的版本最好相同。 只所以提这一点是因为当有停机窗口时，搭建主从的时候，这个往往是最快的。

一般生产环境的备份都会用percona-xtrabackup或者mysqlbackup，结合自己的情况，选择合适的备份策略，适时拿出来验证备份的有效性。

위 내용은 MySQL 백업 및 복구를 위한 4가지 암호 해독 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!