手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

使用 XtraBackup 进行MySQL数据库物理备份

[日期:2015-11-08] 来源:Linux社区  作者:digdeep [字体: ]

4. 部分备份

需要启用 innodb_file_per_table,5.6默认启用。另外在还原时,prepare之后,并不能直接 --copy-back,而只能一个表一个表的import来还原。

[root@localhost xtrabackup]# innobackupex --databases t /backup/xtrabackup/ --user=bkpuser --password=digdeep

[root@localhost xtrabackup]# innobackupex --databases t /backup/xtrabackup/ --user=bkpuser --password=digdeep
151106 15:39:34 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
          At the end of a successful backup run innobackupex
          prints "completed OK!".

151106 15:39:35  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.  sock' as 'bkpuser'  (using password: YES).
151106 15:39:35  version_check Connected to MySQL server
151106 15:39:35  version_check Executing a version check against the server...
151106 15:39:35  version_check Done.
151106 15:39:35 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: 0, socket: /tmp/mysql.sock
Using server version 5.6.26-log
innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (i686) (revision id: 306a2e0)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 10240
xtrabackup: using the following InnoDB configuration:
xtrabackup:  innodb_data_home_dir = ./
xtrabackup:  innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:  innodb_log_group_home_dir = ./
xtrabackup:  innodb_log_files_in_group = 2
xtrabackup:  innodb_log_file_size = 50331648
151106 15:39:35 >> log scanned up to (732817942)
xtrabackup: Generating a list of tablespaces
151106 15:39:35 [01] Copying ./ibdata1 to /backup/xtrabackup//2015-11-06_15-39-34/ibdata1
151106 15:39:36 >> log scanned up to (732817942)
151106 15:39:37 >> log scanned up to (732817942)
151106 15:39:38 [01]        ...done
151106 15:39:38 [01] Copying ./t/city.ibd to /backup/xtrabackup//2015-11-06_15-39-34/t/city.ibd
151106 15:39:38 [01]        ...done
151106 15:39:38 [01] Copying ./t/t.ibd to /backup/xtrabackup//2015-11-06_15-39-34/t/t.ibd
151106 15:39:38 [01]        ...done
151106 15:39:38 >> log scanned up to (732817942)
Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
151106 15:39:38 Executing FLUSH TABLES WITH READ LOCK...
151106 15:39:38 Starting to backup non-InnoDB tables and files
151106 15:39:38 [01] Skipping ./mysql/slave_master_info.ibd.
151106 15:39:38 [01] Skipping ./mysql/columns_priv.frm.
[... ...]
151106 15:39:38 [01] Skipping ./aazj/model_buyers_credit.ibd.
151106 15:39:38 [01] Skipping ./aazj/Users.frm.
151106 15:39:38 [01] Skipping ./aazj/model_recruiting_program.ibd.
151106 15:39:38 [01] Skipping ./aazj/model_model.ibd.
151106 15:39:38 [01] Skipping ./aazj/Customer.frm.
151106 15:39:38 [01] Skipping ./performance_schema/events_waits_summary_by_host_by_event_name.frm.
[... ...]
151106 15:39:38 [01] Skipping ./performance_schema/events_statements_summary_by_account_by_event_name.frm.
151106 15:39:38 [01] Copying ./t/city.frm to /backup/xtrabackup//2015-11-06_15-39-34/t/city.frm
151106 15:39:38 [01]        ...done
151106 15:39:38 [01] Copying ./t/db.opt to /backup/xtrabackup//2015-11-06_15-39-34/t/db.opt
151106 15:39:38 [01]        ...done
151106 15:39:38 [01] Copying ./t/t.frm to /backup/xtrabackup//2015-11-06_15-39-34/t/t.frm
151106 15:39:38 [01]        ...done
151106 15:39:38 Finished backing up non-InnoDB tables and files
151106 15:39:38 [00] Writing xtrabackup_binlog_info
151106 15:39:38 [00]        ...done
151106 15:39:38 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '732817942'
xtrabackup: Stopping log copying thread.
.151106 15:39:38 >> log scanned up to (732817942)

151106 15:39:39 Executing UNLOCK TABLES
151106 15:39:39 All tables unlocked
151106 15:39:39 Backup created in directory '/backup/xtrabackup//2015-11-06_15-39-34'
MySQL binlog position: filename 'mysql-bin.000001', position '120'
151106 15:39:39 [00] Writing backup-my.cnf
151106 15:39:39 [00]        ...done
151106 15:39:39 [00] Writing xtrabackup_info
151106 15:39:39 [00]        ...done
xtrabackup: Transaction log of lsn (732817942) to (732817942) was copied.
151106 15:39:39 completed OK!

数据库 t 中只有两个表:city, t 都被备份了。

下面我们来看如何还原:

4.1 部分prepare:

[root@localhost xtrabackup]# innobackupex --apply-log --export /backup/xtrabackup/2015-11-06_15-39-34/ --user=bkpuser --password=digdeep

[root@localhost xtrabackup]# innobackupex --apply-log --export /backup/xtrabackup/2015-11-06_15-39-34/ --user=bkpuser --password=digdeep
151106 15:49:43 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
          At the end of a successful apply-log run innobackupex
          prints "completed OK!".

innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (i686) (revision id: 306a2e0)
xtrabackup: auto-enabling --innodb-file-per-table due to the --export option
xtrabackup: cd to /backup/xtrabackup/2015-11-06_15-39-34/
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(732817942)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:  innodb_data_home_dir = ./
xtrabackup:  innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:  innodb_log_group_home_dir = ./
xtrabackup:  innodb_log_files_in_group = 1
xtrabackup:  innodb_log_file_size = 2097152
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:  innodb_data_home_dir = ./
xtrabackup:  innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:  innodb_log_group_home_dir = ./
xtrabackup:  innodb_log_files_in_group = 1
xtrabackup:  innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Not using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 732817430 and 732817430 in ibdata files do not match the log sequence number 732817942 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: Table aazj/Accounting_journal in the InnoDB data dictionary has tablespace id 117, but tablespace with that id or name does not exi  st. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL auto  matically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
[... ...]
InnoDB: Table mysql/slave_relay_log_info in the InnoDB data dictionary has tablespace id 3, but tablespace with that id or name does not ex  ist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL aut  omatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table mysql/slave_worker_info in the InnoDB data dictionary has tablespace id 5, but tablespace with that id or name does not exist  . Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automa  tically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 732817942
xtrabackup: export option is specified.
xtrabackup: export metadata of table 't/city' to file `./t/city.exp` (2 indexes)
xtrabackup:    name=PRIMARY, id.low=267, page=3
xtrabackup:    name=PK_CITY, id.low=268, page=4
xtrabackup: export metadata of table 't/t' to file `./t/t.exp` (1 indexes)
xtrabackup:    name=GEN_CLUST_INDEX, id.low=131, page=3
xtrabackup: Last MySQL binlog file position 254400, file name mysql-bin.000001

xtrabackup: starting shutdown with innodb_fast_shutdown = 0
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 732957307
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:  innodb_data_home_dir = ./
xtrabackup:  innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:  innodb_log_group_home_dir = ./
xtrabackup:  innodb_log_files_in_group = 2
xtrabackup:  innodb_log_file_size = 50331648
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Not using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=732957307
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 732957708
xtrabackup: starting shutdown with innodb_fast_shutdown = 0
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 732957718
151106 15:49:49 completed OK!

4.2 下面我们将其 import 到一个新的数据库中:

(root@localhost)[t]mysql>create database partial;

(root@localhost)[t]mysql>use partial;

Database changed

(root@localhost)[partial]mysql>create table city like t.city;

(root@localhost)[partial]mysql>alter table partial.city discard tablespace;

然后将 city.exp 和 city.ibd 拷贝到 /var/lib/mysql/partial/ 目录下,并修改权限:

[root@localhost t]# cp city.exp city.ibd /var/lib/mysql/partial/

[root@localhost partial]# chown -R mysql:mysql /var/lib/mysql

然后

(root@localhost)[aazj]mysql>alter table partial.city import tablespace;

Query OK, 0 rows affected, 1 warning (0.11 sec)

(root@localhost)[aazj]mysql>select count(*) from partial.city;

+----------+

| count(*) |

+----------+

|    3285 |

+----------+

1 row in set (0.01 sec)

可以看到import 成功了。部分恢复成功。

低于t表也同样操作就行了。

(root@localhost)[partial]mysql>select count(*) from t;

+----------+

| count(*) |

+----------+

|      11 |

+----------+

1 row in set (0.00 sec)

可以看到,这种部分备份/恢复,操作起来比较麻烦,步骤比较多,还需要一个表一个表的处理。对少数表处理还可以,如果很多表,就不方面了。

5. point-in-time 恢复

利用全备、增量备份最多只能恢复到全备完成时的那一刻,或者增量备份完成时的那一刻的数据。备份之后产生的数据,我们需要结合binlog,来恢复。我们可以从binlog中获得innobackupex最后一次备份完成时的position,它之后的所有的sql,应用完,这些sql,就能将数据库恢复到最新的状态,或者我们想要的某个时间的状态。

1> 先来一个全备:

[root@localhost xtrabackup]# innobackupex /backup/xtrabackup/full --user=bkpuser --password=digdeep

2> 再来一个增量:

将t表数据删除一行:delete from t where i=11;

[root@localhost xtrabackup]# innobackupex --incremental /backup/xtrabackup/incr1/ --incremental-basedir=/backup/xtrabackup/full/2015-11-06_16-26-08 --user=bkpuser --password=digdeep

3> 再来一个增量:

将t表数据删除一行:delete from t where i=10;

[root@localhost xtrabackup]# innobackupex --incremental /backup/xtrabackup/incr2/ --incremental-basedir=/backup/xtrabackup/incr1/2015-11-06_16-31-13/ --user=bkpuser --password=digdeep

4> 备份完成之后,我们再来操作 t 表:

(root@localhost)[t]mysql>delete from t where i>3;

此时的状态:

(root@localhost)[t]mysql>show binary logs;

+------------------+-----------+

| Log_name        | File_size |

+------------------+-----------+

| mysql-bin.000001 |      927 |

| mysql-bin.000002 |      688 |

+------------------+-----------+

2 rows in set (0.00 sec)

(root@localhost)[t]mysql>show master status;

+------------------+----------+--------------+------------------+-------------------+

 | File                      | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 |      688 |              |                  |                  |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

假设此时数据库表数据所在的磁盘发生故障,但是 binlog 文件是好的。那么此时,我们就可以使用上面的全备、增量备份、还有binlog文件一起来将数据库恢复到磁盘发生故障那一刻的最新状态来。

5> 首先从全备、增量备份得到最后一次备份完成时的数据:

1)应用全备的redo log:

[root@localhost xtrabackup]# innobackupex --apply-log --redo-only /backup/xtrabackup/full/2015-11-06_16-26-08 --user=bkpuser --password=digdeep

2)应用第一次增量备份的redo log:

[root@localhost xtrabackup]# innobackupex --apply-log --redo-only /backup/xtrabackup/full/2015-11-06_16-26-08 --incremental-dir=/backup/xtr  abackup/incr1/2015-11-06_16-31-13/ --user=bkpuser --password=digdeep

3)应用第二次增量备份的 redo log,并且仅限回滚(去掉 --redo-only选项):

[root@localhost xtrabackup]# innobackupex --apply-log /backup/xtrabackup/full/2015-11-06_16-26-08 --incremental-dir=/backup/xtrabackup/incr2/2015-11-06_16-33-57/ --user=bkpuser --password=digdeep

此时已经恢复到了最后一次备份完成时的状态了。

我们看一下最后一次增量备份时的 xtrabackup_binlog_info 文件信息:

[root@localhost xtrabackup]# cat incr2/2015-11-06_16-33-57/xtrabackup_binlog_info

mysql-bin.000002        482

可以看到对应的binlog postion为:mysql-bin.000002        482

而崩溃那一刻的binlog postion为: mysql-bin.000002      688

所以我们需要应用 mysql-bin.000002 文件的 482 到 688之间的sql:

4)先 --copy-back

[root@localhost mysql]# innobackupex --copy-back /backup/xtrabackup/full/2015-11-06_16-26-08 --user=bkpuser --password=digdeep

修改权限:

[root@localhost ~]# chown -R mysql:mysql /var/lib/mysql

启动msyqld:  mysqld_safe --user=mysql &

然后验证,t 表的数据,应该有i<10 & i>3 的数据:

(root@localhost)[t]mysql>select * from t;

+------+

| i    |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|    5 |

|    6 |

|    7 |

|    8 |

|    9 |

+------+

9 rows in set (0.00 sec)

如我们所期待的结果一样。说明到此时,前面的操作完全是正确的。

5)应用 mysql-bin.000002 文件的 482 到 688之间的sql

[root@localhost mysql]#  mysqlbinlog /backup/xtrabackup/mysql-bin.000002 --start-position=482 > bin.sql

(root@localhost)[(none)]mysql>source /var/lib/mysql/bin.sql

然后在查看 t 表数据:

(root@localhost)[t]mysql>select * from t;

+------+

| i    |

+------+

|    1 |

|    2 |

|    3 |

+------+

3 rows in set (0.00 sec)

一切完美完成,数据库被我们回复到了最新的状态。

6. innobackupex 选项优化/最佳实践

6.1 优化FTWRL锁:

在备份非innodb数据库时,会使用:flush tables with read lock 全局锁锁住整个数据库。如果数据库中有一个长查询在运行,那么FTWRL就不能获得,会被阻塞,进而阻塞所有的DML操作。此时即使我们kill掉FTWRL全局锁也是无法从阻塞中恢复出来的。另外在我们成功的获得了FTWRL全局锁之后,在copy非事务因为的文件的过程中,整个数据库也是被锁住的。所以我们应该让FTWRL的过程尽量的短。(在copy非事务引擎数据的文件时,会阻塞innodb事务引擎。当然也会阻塞所有其他非事务引擎。)

1> 防止阻塞:

innobackupex 提供了多个选项来避免发生阻塞:

  --ftwrl-wait-timeout=# 替换 --lock-wait-timeout

                      This option specifies time in seconds that innobackupex

                      should wait for queries that would block FTWRL before

                      running it. If there are still such queries when the

                      timeout expires, innobackupex terminates with an error.

                      Default is 0, in which case innobackupex does not wait

                      for queries to complete and starts FTWRL immediately.

  --ftwrl-wait-threshold=# 替换 --lock-wait-threshold

                      This option specifies the query run time threshold which

                      is used by innobackupex to detect long-running queries

                      with a non-zero value of --ftwrl-wait-timeout. FTWRL is

                      not started until such long-running queries exist. This

                      option has no effect if --ftwrl-wait-timeout is 0.

                      Default value is 60 seconds.

--lock-wait-timeout=60 该选项表示:我们在FTWRL时,如果有长查询,那么我们可以最多等待60S的时间,如果60秒之内长查询执行完了,我们就可以成功执行FTWRL了,如果60秒之内没有执行完,那么就直接报错退出,放弃。默认值为0

--lock-wait-threshold=10 该选项表示运行了多久的时间的sql当做长查询;对于长查询最多再等待 --lock-wait-timeout 秒。

--kill-long-queries-timeout=10 该选项表示发出FTWRL之后,再等待多时秒,如果还有长查询,那么就将其kill掉。默认为0,not to kill.

--kill-long-query-type={all|select} 该选项表示我们仅仅kill select语句,还是kill所有其他的类型的长sql语句。

这几个选项,我们没有必要都是有,一般仅仅使用 --lock-wait-timeout=60 就行了。

注意 --lock-* 和 --kill-* 选项的不同,一个是等待多时秒再来执行FTWRL,如果还是不能成功执行就报错退出;一个是已经执行了FTWRL,超时就进行kill。

2> 缩短FTWRL全局锁的时间:

--rsync 使用该选项来缩短备份非事务引擎表的锁定时间,如果需要备份的数据库和表数量很多时,可以加快速度。

--rsync          Uses the rsync utility to optimize local file transfers.

                      When this option is specified, innobackupex uses rsync to

                      copy all non-InnoDB files instead of spawning a separate

                      cp for each file, which can be much faster for servers

                      with a large number of databases or tables.  This option

                      cannot be used together with --stream.

3> 并行优化:

  --parallel=# 在备份阶段,压缩/解压阶段,加密/解密阶段,--apply-log,--copy-back 阶段都可以并行     

                      On backup, this option specifies the number of threads

                      the xtrabackup child process should use to back up files

                      concurrently.  The option accepts an integer argument. It

                      is passed directly to xtrabackup's --parallel option. See

                      the xtrabackup documentation for details.

4> 内存优化:

  --use-memory=# 在crash recovery 阶段,也就是 --apply-log 阶段使用该选项

                      This option accepts a string argument that specifies the

                      amount of memory in bytes for xtrabackup to use for crash

                      recovery while preparing a backup. Multiples are

                      supported providing the unit (e.g. 1MB, 1GB). It is used

                      only with the option --apply-log. It is passed directly

                      to xtrabackup's --use-memory option. See the xtrabackup

                      documentation for details.

3> 备份slave:

--safe-slave-backup

                      Stop slave SQL thread and wait to start backup until

                      Slave_open_temp_tables in "SHOW STATUS" is zero. If there

                      are no open temporary tables, the backup will take place,

                      otherwise the SQL thread will be started and stopped

                      until there are no open temporary tables. The backup will

                      fail if Slave_open_temp_tables does not become zero after

                      --safe-slave-backup-timeout seconds. The slave SQL thread

                      will be restarted when the backup finishes.

--safe-slave-backup-timeout=#

                      How many seconds --safe-slave-backup should wait for

                      Slave_open_temp_tables to become zero. (default 300)

--slave-info  This option is useful when backing up a replication slave

                      server. It prints the binary log position and name of the

                      master server. It also writes this information to the

                      "xtrabackup_slave_info" file as a "CHANGE MASTER"

                      command. A new slave for this master can be set up by

                      starting a slave server on this backup and issuing a

                      "CHANGE MASTER" command with the binary log position

                      saved in the "xtrabackup_slave_info" file.

 
7. 备份原理:
1)innobackupex 是perl写的脚本,它调用xtrabackup来备份innodb数据库。而xtrabackup是C语言写的程序,它调用了innodb的函数库和mysql客户端的函数库。innodb函数库提供了向数据文件应用的redo log的功能,而mysql客户端函数库提供了解析命令行参数的功能。innobackupex备份innodb数据库的功能,都是通过调用 xtrabackup --backup和xtrabackup --prepare来完成的。我们没有必要直接使用xtrabackup来备份,通过innobackupex更方便。xtrabakup 通过跳转到datadir目录,然后通过两个线程来完成备份过程:

1> log-copy thread: 备份开始时,该后台线程一直监控redo log(每秒check一次redo log),将redo log的修改复制到备份之后的文件 xtrabackup_logfile 中。如果redo log生成极快时,有可能log-copy线程跟不上redo log的产生速度,那么在redo log文件切换进行覆盖时,xtrabakcup会报错。

2> data-file-copy thread: 前后有一个复制data file的线程,注意这里并不是简单的复制,而是调用了innodb函数库,像innodb数据库那样打开数据文件,进行读取,然后每次复制一个page,然后对page进行验证,如果验证错误,会最多重复十次。

当数据文件复制完成时,xtrabackup 停止log-copy 线程,并建立一个文件 xtrabackup_checkpoints记录备份的类型,开始时的lsn和结束时的lsn等信息。

而备份生成的 xtrabackup_binlog_info 文件则含义备份完成时对应的binlog的position信息,类似于:mysql-bin.000002        120

在备份开始时记录下LSN,然后一个线程复制数据文件,一个线程监控redo log,复制在备份过程中新产生的redo log。虽然我们的到的数据文件显然不是一致性的,但是利用innodb的crash-recovery功能,应用备份过程中产生的redo log文件,就能得到备份完成时那一刻对应的一致性的数据。

注意复制数据文件分成了两个过程:

一个是复制innodb事务引擎的数据文件,是不需要持有锁的;另一个是复制非事务引擎的数据文件和table的定义文件.frm,复制这些文件时,是需要先通过FTWRL,然后在进行复制的,所以会导致整个数据库被阻塞。

增量备份时,是通过对表进行全扫描,比较LSN,如果该page的LSN大于上一次别分时的LSN,那么就将该page复制到table_name.ibd.delta文件中。回复时.delta会和redo log应用到全备是的数据文件中。

增量备份在恢复时,除了最后一次增量备份文件之外,其它的增量备份在应用时,只能前滚,不能执行回滚操作,因为没有提交的事务,可能在下一个增量备份中进行了提交,如果你在上一个增量备份时回滚了,那么下一个增量备份应用时,显然就报错了,因为他无法提交事务,该事务以及被回滚了。

8. 总结:

1)权限:

备份需要两个层面的权限,Linux层面的权限,mysql层面的权限。

2)全备和恢复

全备:innobackupex  /backup/xtrabackup/full --user=bkpuser --password=digdeep

应用日志进行prepare: innobackupex --apply-log /backup/xtrabackup/full/2015-11-05_22-38-55/ --user=bkpuser --password=digdeep

关闭mysqld:

copy-back: innobackupex --copy-back /backup/xtrabackup/full/2015-11-05_22-38-55/ --user=bkpuser --password=digdeep

修改权限:chown -R mysql:mysql /var/lib/mysql

3)增量备份和恢复:

全备:

innobackupex --user=bkpuser --password=digdeep /backup/xtrabackup/full

第一次增量备份:

innobackupex --incremental /backup/xtrabackup/incr1/ --incremental-basedir=/backup/xtrabackup/full/2015-11-06_11-29-51/

--user=bkpuser --password=digdeep

第二次增量备份:

innobackupex --incremental /backup/xtrabackup/incr2 --incremental-basedir=/backup/xtrabackup/incr1/2015-11-06_11-33-16/

--user=bkpuser --password=digdeep

恢复:

应用全备redo log:

innobackupex --apply-log --redo-only /backup/xtrabackup/full/2015-11-06_11-29-51/ --user=bkpuser --password=digdeep

应用第一次增量备份的redo log:

innobackupex --apply-log --redo-only /backup/xtrabackup/full/2015-11-06_11-29-51/ --incremental-dir=/backup/xtrabackup/incr1/2015-11-06_11-33-16/

--user=bkpuser --password=digdeep

应用第二次(最后一次)增量备份的redo log:

innobackupex --apply-log /backup/xtrabackup/full/2015-11-06_11-29-51/ --incremental-dir=/backup/xtrabackup/incr2/2015-11-06_11-43-22/

--user=bkpuser --password=digdeep

关闭mysqld,

innobackupex --copy-back /backup/xtrabackup/full/2015-11-06_11-29-51/ --user=bkpuser --password=digdeep

4)部分备份

innobackupex --databases t /backup/xtrabackup/ --user=bkpuser --password=digdeep

innobackupex --apply-log --export /backup/xtrabackup/2015-11-06_15-39-34/ --user=bkpuser --password=digdeep

新建表结构:create table city like t.city;

alter table partial.city discard tablespace;

然后将 city.exp 和 city.ibd 拷贝到 /var/lib/mysql/partial/ 目录下,并修改权限:chown -R mysql:mysql /var/lib/mysql

alter table partial.city import tablespace;

5)point-in-time 恢复

在--copy-back之后,引用binlog文件

mysqlbinlog /backup/xtrabackup/mysql-bin.000002 --start-position=482 > bin.sql

(root@localhost)[(none)]mysql>source bin.sql

6) innobackupex 选项优化/最佳实践

--ftwrl-wait-timeout=60 防止发生阻塞

--rsync 减少FTWRL时间 缩短备份非事务引擎表的锁定时间

--parallel=4  开并行

--use-memory=4G crash recovery 期间使用的内存

XtraBackup 的详细介绍请点这里
XtraBackup 的下载地址请点这里

本文永久更新链接地址http://www.linuxidc.com/Linux/2015-11/124941.htm

linux
相关资讯       Xtrabackup 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款