马哥 35_04 _MySQL系列之十六——使用xtrabackup进行数据库备份有大用

星期四, 2020-10-22 08:04 — adminshiping1

set sql_log_bin=0 先禁用二进制日志,,,,,,,,备份完后,,,,,,,set sql_log_bin=1 再启用

innodb_support_xa=true; 支持分布式事务的功能,它应该是启用的

sync_binlog=# 多久同步二进制日志到磁盘文件 ,,,0表示不同步(是提交时才同步?),,,,任何正数值都表示对二进制每多少次写操作之后同步一次;;;;备份时,建议设定为 1 ,,,,,会使得二进制日志写入到磁盘的时候,能够以安全的方式进行,不会因为我们的备份导致二进制日志文件的损坏

flush logs会滚动二进制日志

percona公司提供的 xtrabackup

innodb官方提供的工具 ibbackup: (一个服务器授权5000$,很贵)

InnoDB online physical backup 能够实现对 InnoDB 存储引擎实现在线的物理备份,而且能够以安全的方式进行,

full:完全备份

increament:增量备份

对MyISAM只能实现温备份( warm backup ),full备份,不能实现increament备份

mysqldump:50G的数据库,备份的时候,无所谓,可能恢复的时候,要得半个小时,

小型数据库适用 mysqldump ,

所以大数据库,使用裸备份(物理备份) 最理想,我们还要在线备份

LVM几乎是热备份,但不是完全意义上的热备份,所以施加锁的时间,可能比较长一点,我们目前的操作中无法实现脚本自动化执行的

LVM-->mylvmbackup 脚本,别人开发的自动备份的,开源的perl脚本,(perl script),它有个配置文件,能够执行自动化的对放在逻辑卷上的数据文件以快照方式进行备份,,,但是它是几乎热备,不是完全意义上的热备有些信息比如 show master status 等,mylvmbackup能够帮助我们获取,

percona公司提供的 xtrabackup 类似于 ibbackup(因为 ibbackup 太贵了)

xtrabackup 是开源的,功能上不比 ibbackup 逊色;;比ibbackup 更强的备份特性,支持数据流失备份,支持数据流失备份的时候进行压缩

可对xtradb存储引擎进行备份 xtradb是xtrabackup专用的存储引擎,是InnoDB的增强版,所以可以使用xtradb来完全替代innodb,在编译安装mysql之前,把原有的innodb的存储引擎的源代码删了,去percona官方下载xtradb的存储引擎的源代码放在存储引擎的目录里面,并将其更名为innodb,就可以编译mysql的时候,自动启用xtradb,而非原有的innodb,,,,xtradb比innodb改进很多 (源码编译时,建议用xtradb替换innodb ) 支持全量备份,增量备份,在备份的同时,会自动保存各种相关的数据文件,相关的信息文件(比如 show master status);;;几乎全自动化的备份工具

http://www.percona.com

可对innodb存储引擎进行备份

http://www.percona.com

https://www.percona.com/software/mongodb/percona-backup-for-mongodb

xtraback 与 ibbackup 在命令行的使用上是非常近似的

xtraback 的使用

# innobackupex --user=DBUSER --password=DBUSERPASS [--host=localhost ] [--port=PORT ] /path/to/BACKUP-DIR

--port=PORT 端口号

#这是备份所有数据库, /path/to/BACKUP-DIR 是备份的位置

它类似于mysql服务器的客户端工具,要求mysql服务器要在线,需要指定账号,密码以后才能进行备份的

必须要有备份权限的用户才能执行备份,

要授权 reload,lock tables,replication client

通常都是在服务器所在的那台主机进行备份,否则要借助于远程备份,还需要其它的工具

当我们使用root管理员用户时,默认--user也不用指了

--apply-log 准备工作;;;能够实现事务的一致性

--copy-back 还原数据

--incremental 实现增量备份 --incremental-basedir=BASEDIR 这里BASEDIR 指的是完全备份的位置

如果第二次增量备份那么就是 --incremental-basedir=BASEDIR(这里BASEDIR是上一次增量备份的文件的位置)

# 马哥说关于部分备份以后再说,,, 现在不需要掌握

xtrabackup 如何想备份单张表 ,必须要设定每表一个表空间(innodb_file_per_table) 和支持扩展的导入功能( innodb_expand_import ,,,备份服务器其实这个不需要启用)

导出单张表,需要备份的基础上导出,不是在数据库的基础上导出,所以此前先做一次完全备份,,他依赖于完全备份能够导出一个表

# innobackupex --apply-log --export /path/to/backup 能够导出所有表以.exp 结尾 (.exp export)

在目标服务器上(这两个都要启用 innodb_file_per_table 和 innodb_expand_import )创建一张表,保证引擎是innodb ,,,,删除这个表的表空间, alter table mydatabaes.mytable discard tablespace ;;;

将备份的备份表的ibd 和 exp文件放到目标服务器的data目录下,

再修复导入表 alter table mydatabase.mytable import tablespace 会自动识别同表名.exp的文件,进行导入

xtrabackup_binlog_pos_innodb #二进制日志文件及用于innodb或xtradb表的二进制日志文件的当前的position ,,,这个信息已经保存在 xtrabackup_binlog_info 文件里了这两个文件的内容好像是一样的

backup-my.cnf #备份命令用到的配置文件中的选项信息

innobackupex 是一个脚本,在内部封装了xtrabackup里面的几个命令行二进制工具,所以命令行使用起来很简化

我看到的

[root@localhost data]# wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.0.0/RPM/rhel5/i386/percona-xtrabackup-2.0.0-417.rhel5.i386.rpm

# 可能会提示无法建立 SSL 连接。

先下载到windows本地吧,然后再从本地移到linux

[root@mail ~]# rpm -ivh percona-xtrabackup-2.0.0-417.rhel5.i386.rpm

warning: percona-xtrabackup-2.0.0-417.rhel5.i386.rpm: Header V4 DSA signature: NOKEY, key ID cd2efd2a

error: Failed dependencies:

mysql is needed by percona-xtrabackup-2.0.0.417.rhel5.i386

(提示mysql被需要?? 我安装一下mysql-devel 开发包吧)

[root@mail ~]# yum install mysql-devel

#安装开发包后,就可以安装 percona-xtrabackup-2.0.0-417.rhel5.i386.rpm 了

[root@mail ~]# rpm -ivh percona-xtrabackup-2.0.0-417.rhel5.i386.rpm

warning: percona-xtrabackup-2.0.0-417.rhel5.i386.rpm: Header V4 DSA signature: NOKEY, key ID cd2efd2a

Preparing... ########################################### [100%]

1:percona-xtrabackup ########################################### [100%]

[root@mail ~]#

[root@mail ~]# rpm -ql percona-xtrabackup # 看安装后生成了哪些文件

# 大多是帮助文件在/usr/share 下面

/usr/bin/innobackupex #这是一个脚本

#下面三个是分别专用于不同版本的mysql server的,因为不同版本的mysql server上,它的innodb存储引擎的版本是不相同的,所以它们有专用于各种版本的innodb存储引擎的命令行工具,但是它们将这些不同完全通过innobackupex的工具给封装起来了,所以它隐藏了各种不同工具的细节,对我们而言,只需要使用innobackupex这个工具就可以了

/usr/bin/xtrabackup

/usr/bin/xtrabackup_51

/usr/bin/xtrabackup_55

[root@mail ~]# man innobackupex #不行

Cannot open the message catalog "man" for locale "zh_CN.UTF-8"

(NLSPATH="<none>")

No manual entry for innobackupex

[root@mail ~]# innobackupex -h #不行

Option h is ambiguous (help, host)

innobackupex: Bad command line arguments

[root@mail ~]#

[root@mail ~]# innobackupex --help

Options:

--apply-log

Prepare a backup in BACKUP-DIR by applying the transaction log file

named "xtrabackup_logfile" located in the same directory. Also,

create new transaction logs. The InnoDB configuration is read from

the file "backup-my.cnf".

--compress

This option instructs xtrabackup to compress backup copies of InnoDB

data files. It is passed directly to the xtrabackup child process.

Try 'xtrabackup --help' for more details.

--compress-threads

This option specifies the number of worker threads that will be used

for parallel compression. It is passed directly to the xtrabackup

child process. Try 'xtrabackup --help' for more details.

--copy-back

Copy all the files in a previously made backup from the backup

directory to their original locations.

--databases=LIST

This option specifies the list of databases that innobackupex should

back up. The option accepts a string argument. The list is of the

form "databasename1[.table_name1] databasename2[.table_name2] . .

.". If this option is not specified, all databases containing MyISAM

and InnoDB tables will be backed up. Please make sure that

--databases contains all of the InnoDB databases and tables, so that

all of the innodb.frm files are also backed up. In case the list is

very long, this can be specified in a file, and the full path of the

file can be specified instead of the list. (See option

--tables-file.)

--defaults-file=[MY.CNF]

This option specifies what file to read the default MySQL options

from. The option accepts a string argument. It is also passed

directly to xtrabackup's --defaults-file option. See the xtrabackup

documentation for details.

--export

This option is passed directly to xtrabackup's --export option. It

enables exporting individual tables for import into another server.

See the xtrabackup documentation for details.

--extra-lsndir=DIRECTORY

This option specifies the directory in which to save an extra copy

of the "xtrabackup_checkpoints" file. The option accepts a string

argument. It is passed directly to xtrabackup's --extra-lsndir

option. See the xtrabackup documentation for details.

--force-tar

This option forces the use of tar when creating a streamed backup,

rather than tar4ibd, which is the default.

--help

This option displays a help screen and exits.

--host=HOST

This option specifies the host to use when connecting to the

database server with TCP/IP. The option accepts a string argument.

It is passed to the mysql child process without alteration. See

mysql --help for details.

--ibbackup=IBBACKUP-BINARY

This option specifies which xtrabackup binary should be used. The

option accepts a string argument. IBBACKUP-BINARY should be the

command used to run XtraBackup. The option can be useful if the

xtrabackup binary is not in your search path or working directory.

If this option is not specified, innobackupex attempts to determine

the binary to use automatically. By default, "xtrabackup" is the

command used. However, when option --copy-back is specified,

"xtrabackup_51" is the command used. And when option --apply-log is

specified, the binary is used whose name is in the file

"xtrabackup_binary" in the backup directory, if that file exists.

--include=REGEXP

This option is a regular expression to be matched against table

names in databasename.tablename format. It is passed directly to

xtrabackup's --tables option. See the xtrabackup documentation for

details.

--incremental

This option tells xtrabackup to create an incremental backup, rather

than a full one. It is passed to the xtrabackup child process. When

this option is specified, either --incremental-lsn or

--incremental-basedir can also be given. If neither option is given,

option --incremental-basedir is passed to xtrabackup by default, set

to the first timestamped backup directory in the backup base

directory.

--incremental-basedir=DIRECTORY

This option specifies the directory containing the full backup that

is the base dataset for the incremental backup. The option accepts a

string argument. It is used with the --incremental option.

--incremental-dir=DIRECTORY

This option specifies the directory where the incremental backup

will be combined with the full backup to make a new full backup. The

option accepts a string argument. It is used with the --incremental

option.

--incremental-lsn

This option specifies the log sequence number (LSN) to use for the

incremental backup. The option accepts a string argument. It is used

with the --incremental option. It is used instead of specifying

--incremental-basedir. For databases created by MySQL and Percona

Server 5.0-series versions, specify the LSN as two 32-bit integers

in high:low format. For databases created in 5.1 and later, specify

the LSN as a single 64-bit integer.

--no-lock

Use this option to disable table lock with "FLUSH TABLES WITH READ

LOCK". Use it only if ALL your tables are InnoDB and you DO NOT CARE

about the binary log position of the backup.

--no-timestamp

This option prevents creation of a time-stamped subdirectory of the

BACKUP-ROOT-DIR given on the command line. When it is specified, the

backup is done in BACKUP-ROOT-DIR instead.

--parallel=NUMBER-OF-THREADS

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.

--password=WORD

This option specifies the password to use when connecting to the

database. It accepts a string argument. It is passed to the mysql

child process without alteration. See mysql --help for details.

--port=PORT

This option specifies the port to use when connecting to the

database server with TCP/IP. The option accepts a string argument.

It is passed to the mysql child process. It is passed to the mysql

child process without alteration. See mysql --help for details.

--redo-only

This option is passed directly to xtrabackup's --apply-log-only

option. This forces xtrabackup to skip the "rollback" phase and do a

"redo" only. This is necessary if the backup will have incremental

changes applied to it later. See the xtrabackup documentation for

details.

--remote-host=HOSTNAME

This option is DEPRECATED and will be removed in Percona XtraBackup

2.1. In Percona XtraBackup 2.0 and later, you should use streaming

backups instead. This option specifies the remote host on which the

backup files will be created, by using an ssh connection. The option

accepts a string argument.

--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)

--scpopt=SCP-OPTIONS

This option specifies the command line options to pass to scp when

the option --remost-host is specified. The option accepts a string

argument. If the option is not specified, the default options are

"-Cp -c arcfour".

--sshopt=SSH-OPTIONS

This option specifies the command line options to pass to ssh when

the option --remost-host is specified. The option accepts a string

argument.

--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.

--socket=SOCKET

This option specifies the socket to use when connecting to the local

database server with a UNIX domain socket. The option accepts a

string argument. It is passed to the mysql child process without

alteration. See mysql --help for details.

--stream=[tar|. . .]

This option specifies the format in which to do the streamed backup.

The option accepts a string argument. The backup will be done to

STDOUT in the specified format. Currently, the only supported

formats are tar and xbstream. This option is passed directly to

xtrabackup's --stream option.

--tables-file=FILE

This option specifies the file in which there are a list of names of

the form database. The option accepts a string argument.table, one

per line. The option is passed directly to xtrabackup's

--tables-file option.

--throttle=IOS

This option specifies a number of I/O operations (pairs of

read+write) per second. It accepts an integer argument. It is passed

directly to xtrabackup's --throttle option.

--tmpdir=DIRECTORY

This option specifies the location where a temporary file will be

stored. The option accepts a string argument. It should be used when

--remote-host or --stream is specified. For these options, the

transaction log will first be stored to a temporary file, before

streaming or copying to a remote host. This option specifies the

location where that temporary file will be stored. If the option is

not specifed, the default is to use the value of tmpdir read from

the server configuration.

--use-memory=B

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.

--user=NAME

This option specifies the MySQL username used when connecting to the

server, if that's not the current user. The option accepts a string

argument. It is passed to the mysql child process without

alteration. See mysql --help for details.

--version

This option displays the xtrabackup version and copyright notice and

then exits.

[root@mail ~]#

[root@mail ~]# innobackupex --user=root /backup #进行备份

This software is published under

the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

201024 13:46:53 innobackupex: Starting mysql with options: --user='root' --unb uffered --

201024 13:46:53 innobackupex: Connected to database with mysql child process (p id=24599)

201024 13:46:59 innobackupex: Connection to database server closed

IMPORTANT: Please check that the backup run completes successfully. #检查

At the end of a successful backup run innobackupex

prints "completed OK!".

innobackupex: Using mysql Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (i386) using readline 5.1

innobackupex: Created backup directory /backup/2020-10-24_13-46-59

201024 13:46:59 innobackupex: Starting mysql with options: --user='root' --unb uffered --

201024 13:46:59 innobackupex: Connected to database with mysql child process (pid=24622)

201024 13:47:01 innobackupex: Connection to database server closed

201024 13:47:01 innobackupex: Starting ibbackup with command: xtrabackup_55 --backup --suspend-at-end --target-dir=/backup/2020-10-24_13-46-59

innobackupex: Waiting for ibbackup (pid=24628) to suspend

innobackupex: Suspend file '/backup/2020-10-24_13-46-59/xtrabackup_suspended'

xtrabackup_55 version 2.0.0 for Percona Server 5.5.16 Linux (i686) (revision id: 417)

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /mydata2/data

xtrabackup: Target instance is assumed as followings.

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 = 5242880

201024 13:47:01 InnoDB: Using Linux native AIO

201024 13:47:01 InnoDB: Warning: allocated tablespace 17, old maximum was 9

>> log scanned up to (1717164)

[01] Copying ./ibdata1 to /backup/2020-10-24_13-46-59/ibdata1

[01] ...done

[01] Copying ./studb/tutors.ibd to /backup/2020-10-24_13-46-59/./studb/tutors.ib d

[01] ...done

[01] Copying ./studb/students.ibd to /backup/2020-10-24_13-46-59/./studb/student s.ibd

[01] ...done

[01] Copying ./studb/courses.ibd to /backup/2020-10-24_13-46-59/./studb/courses. ibd

[01] ...done

[01] Copying ./jiaowu/tutors.ibd to /backup/2020-10-24_13-46-59/./jiaowu/tutors. ibd

[01] ...done

[01] Copying ./jiaowu/students.ibd to /backup/2020-10-24_13-46-59/./jiaowu/stude nts.ibd

[01] ...done

[01] Copying ./jiaowu/courses.ibd to /backup/2020-10-24_13-46-59/./jiaowu/course s.ibd

[01] ...done

[01] Copying ./test2/test.ibd to /backup/2020-10-24_13-46-59/./test2/test.ibd

[01] ...done

[01] Copying ./students/student.ibd to /backup/2020-10-24_13-46-59/./students/st udent.ibd

[01] ...done

[01] Copying ./students/courses.ibd to /backup/2020-10-24_13-46-59/./students/co urses.ibd

[01] ...done

[01] Copying ./cactidb/testdb.ibd to /backup/2020-10-24_13-46-59/./cactidb/testd b.ibd

[01] ...done

[01] Copying ./testdb/tb1.ibd to /backup/2020-10-24_13-46-59/./testdb/tb1.ibd

[01] ...done

[01] Copying ./mydb/testdb.ibd to /backup/2020-10-24_13-46-59/./mydb/testdb.ibd

[01] ...done

201024 13:47:05 innobackupex: Continuing after ibbackup has suspended

201024 13:47:05 innobackupex: Starting mysql with options: --user='root' --unb uffered --

201024 13:47:05 innobackupex: Connected to database with mysql child process (p id=24642)

>> log scanned up to (1717164)

201024 13:47:07 innobackupex: Starting to lock all tables...

>> log scanned up to (1717164)

201024 13:47:17 innobackupex: All tables locked and flushed to disk

201024 13:47:17 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,

innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in

innobackupex: subdirectories of '/mydata2/data'

innobackupex: Backing up file '/mydata2/data/studb/students.frm'

innobackupex: Backing up file '/mydata2/data/studb/sct.frm'

innobackupex: Backing up file '/mydata2/data/studb/courses.frm'

innobackupex: Backing up file '/mydata2/data/studb/db.opt'

innobackupex: Backing up file '/mydata2/data/studb/tutors.frm'

innobackupex: Backing up file '/mydata2/data/jiaowu/students.frm'

innobackupex: Backing up file '/mydata2/data/jiaowu/sct.frm'

innobackupex: Backing up file '/mydata2/data/jiaowu/courses.frm'

innobackupex: Backing up file '/mydata2/data/jiaowu/db.opt'

innobackupex: Backing up file '/mydata2/data/jiaowu/tutors.frm'

innobackupex: Backing up files '/mydata2/data/performance_schema/*.{frm,MYD,MYI, MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)

innobackupex: Backing up files '/mydata2/data/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,A RM,ARZ,CSM,CSV,opt,par}' (72 files)

innobackupex: Backing up file '/mydata2/data/hellodb2/db.opt'

innobackupex: Backing up file '/mydata2/data/test2/db.opt'

innobackupex: Backing up file '/mydata2/data/test2/test.frm'

innobackupex: Backing up file '/mydata2/data/students/test1.frm'

innobackupex: Backing up file '/mydata2/data/students/test2.MYD'

innobackupex: Backing up file '/mydata2/data/students/test1.MYD'

innobackupex: Backing up file '/mydata2/data/students/test2.frm'

innobackupex: Backing up file '/mydata2/data/students/courses.frm'

innobackupex: Backing up file '/mydata2/data/students/test2.MYI'

innobackupex: Backing up file '/mydata2/data/students/db.opt'

innobackupex: Backing up file '/mydata2/data/students/student.frm'

innobackupex: Backing up file '/mydata2/data/students/test1.MYI'

innobackupex: Backing up file '/mydata2/data/cactidb/testdb.frm'

innobackupex: Backing up file '/mydata2/data/cactidb/db.opt'

innobackupex: Backing up file '/mydata2/data/hellodb/db.opt'

innobackupex: Backing up file '/mydata2/data/mydb2/db.opt'

innobackupex: Backing up file '/mydata2/data/testdb/db.opt'

innobackupex: Backing up file '/mydata2/data/testdb/tb1.frm'

innobackupex: Backing up file '/mydata2/data/edb/db.opt'

innobackupex: Backing up file '/mydata2/data/mydb/testdb.frm'

innobackupex: Backing up file '/mydata2/data/mydb/db.opt'

201024 13:47:17 innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files

innobackupex: Resuming ibbackup

xtrabackup: The latest check point (for incremental): '1717164'

xtrabackup: Stopping log copying thread.

.>> log scanned up to (1717164)

xtrabackup: Transaction log of lsn (1717164) to (1717164) was copied.

201024 13:47:19 innobackupex: All tables unlocked

201024 13:47:19 innobackupex: Connection to database server closed

innobackupex: Backup created in directory '/backup/2020-10-24_13-46-59' #最后指定了以时间命名的目录

innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 107

201024 13:47:19 innobackupex: completed OK! # 这里显示备份成功

[root@mail ~]#

[root@mail ~]# cd /backup/

[root@mail backup]# ls

2020-10-24_13-46-59 full-backup-2020-10-22 increment-2020-10-22-15-06-55.sql

[root@mail backup]# cd 2020-10-24_13-46-59/

[root@mail 2020-10-24_13-46-59]# pwd

/backup/2020-10-24_13-46-59

[root@mail 2020-10-24_13-46-59]# ls

backup-my.cnf(备份的配置文件) hellodb2 mydb2 students xtrabackup_binary

cactidb ibdata1(表空间文件) mysql test xtrabackup_binlog_info(二进制日志信息)

edb jiaowu performance_schema test2 xtrabackup_checkpoints

hellodb mydb studb testdb xtrabackup_logfile

[root@mail 2020-10-24_13-46-59]#

[root@mail 2020-10-24_13-46-59]# file xtrabackup_binlog_info #看二进制日志信息的格式

xtrabackup_binlog_info: ASCII text

[root@mail 2020-10-24_13-46-59]#

[root@mail 2020-10-24_13-46-59]# cat xtrabackup_binlog_info #备份这一刻,二进制日志文件名及位置 # 107之前的是开头,还原时整个文件都用得上

mysql-bin.000001 107

[root@mail 2020-10-24_13-46-59]#

[root@mail 2020-10-24_13-46-59]# file xtrabackup_binary

xtrabackup_binary: ASCII text, with no line terminators

[root@mail 2020-10-24_13-46-59]#

[root@mail 2020-10-24_13-46-59]# cat xtrabackup_binary #表示备份的时候使用哪个命令真正执行的备份,,,还原的时候也使用这个工具进行还原

xtrabackup_55

[root@mail 2020-10-24_13-46-59]# file xtrabackup_logfile #这是个纯数据文件

xtrabackup_logfile: data

[root@mail 2020-10-24_13-46-59]#

[root@mail 2020-10-24_13-46-59]# file xtrabackup_checkpoints #文本文件

xtrabackup_checkpoints: ASCII text

[root@mail 2020-10-24_13-46-59]#

[root@mail 2020-10-24_13-46-59]# cat xtrabackup_checkpoints

backup_type = full-backuped #备份的类型完全备份

from_lsn = 0 #表示从0号开始 #表示从哪个版本号,逻辑号码进行的备份 (lsn指的是innodb的每一个数据块(innodb存储的数据要存储到磁盘上,它有存储数据块的,每一个数据块都有一个日志序列号,innodb存储引擎会在自己内部维持记录着当前每一个数据块的日志序列号,如果这个块中的数据发生了改变, 这个号码会加1,往前走一次的,所以下次会根据这个做增量备份,如果某一块发生了改变,就备份下这个块,如果没有发生改变,就不备份块了,,,所以它能够对 innodb存储引擎做增量备份就是这个原因))

to_lsn = 1717164 # 到 1717164 号结束 #如果下次做了增删除的操作,这个号码会发生改变的

last_lsn = 1717164 # 所以下次增量备份从1717164 号开始

[root@mail 2020-10-24_13-46-59]#

xtrabackupex 备份完以后不能直接拿来恢复的,因为备份出来的文件,有些事务只提交了一半,所以直接拿来直接恢复的话,启动mysql的时候会自动完成一些修复操作,,,为了避免这一过程,使用xtrabackup所备份出来的文件,我们需要在备份完成后完成一些准备工作,然后才能用于恢复(这些准备工作主要涉及到将哪些已经提交的事务从日志文件同步到数据文件,将尚未提交的事务做回滚)

(使用 --apply-log就可以完成准备工作了,,将哪些已经提交的事务从日志文件同步到数据文件,将尚未提交的事务做回滚),,,,,,所以准备工作完成后,就是一个干净的可以直接用于启动的数据文件

[root@mail ~]# innobackupex --apply-log /backup/2020-10-24_13-46-59/ 准备工作

.................................................

准备好之后,数据备份好后,数据又发生了改变怎么办?意味着我们使用二进制日志做即时点还原

mysql> use jiaowu

Database changed

mysql>

mysql> insert into tutors (tname) values ('stu005'); #插入了新数据

Query OK, 1 row affected (0.06 sec)

mysql> insert into tutors (tname) values ('stu006'); #插入了新数据

Query OK, 1 row affected (0.00 sec)

mysql>

备份二进制日志

我们要确保二进制日志是安全的,一定要把二进制日志文件与数据文件分开存放,而且最好放在 raid1 带有镜像功能的设备上,,,,,我们甚至还要对它做远程镜像,比如基于drdb做远程镜像

[root@mail ~]# cd /mydata2/data

[root@mail data]# ls

cactidb ib_logfile0 mydb performance_schema testdb

edb ib_logfile1 mydb2 studb

hellodb jiaowu mysql students

hellodb2 mail.magedu.com.err mysql-bin.000001 test

ibdata1 mail.magedu.com.pid mysql-bin.index test2

[root@mail data]#

直接备份一下打开一个正在使用的二进制日志文件,它老是会发出警告信息,所以我们

mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)

mysql>

[root@mail data]# pwd

/mydata2/data

[root@mail data]# ls

cactidb ib_logfile0 mydb mysql-bin.000003 test

edb ib_logfile1 mydb2 mysql-bin.index test2

hellodb jiaowu mysql performance_schema testdb

hellodb2 mail.magedu.com.err mysql-bin.000001 studb

ibdata1 mail.magedu.com.pid mysql-bin.000002 students

[root@mail data]#

[root@mail data]# cp mysql-bin.000001 /root

[root@mail data]#

下面来模拟下mysql数据库损坏

[root@mail data]# service mysqld stop

Shutting down MySQL.. [确定]

[root@mail data]# pwd

/mydata2/data

[root@mail data]# rm -rf ./*

[root@mail data]#

xtrabackup 的还原是要求 mysql服务器处于正常启动状态的 (马哥又说他说错了, 恢复时是不需要mysql处于正常启动状态的)

所以要先初始化mysql

[root@mail ~]# cd /usr/local/mysql

[root@mail mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata2/data/

Installing MySQL system tables...

Filling help tables...

To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

./bin/mysqladmin -u root password 'new-password'

./bin/mysqladmin -u root -h mail.magedu.com password 'new-password'

Alternatively you can run:

./bin/mysql_secure_installation

which will also give you the option of removing the test

databases and anonymous user created by default. This is

strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

cd ./mysql-test ; perl mysql-test-run.pl

Please report any problems with the ./bin/mysqlbug script!

[root@mail mysql]#

启动 mysql数据库

[root@mail mysql]# service mysqld start

Starting MySQL..... [确定]

[root@mail mysql]#

要想从一个完全备份中恢复数据,,使用 --copy-back 选项

马哥说刚刚他说错了,他又说使用 xtrabackup 恢复时不需要mysql处于运行状态的

下面继续模拟损坏 mysql

[root@mail mysql]# cd /mydata2/data/

[root@mail data]# ls

ibdata1 mail.magedu.com.err mysql-bin.000001 mysql-bin.index

ib_logfile0 mail.magedu.com.pid mysql-bin.000002 performance_schema

ib_logfile1 mysql mysql-bin.000003 test

[root@mail data]# service mysqld stop

Shutting down MySQL. [确定]

[root@mail data]#

[root@mail data]# pwd

/mydata2/data

[root@mail data]# ls

ibdata1 mail.magedu.com.err mysql-bin.000002 performance_schema

ib_logfile0 mysql mysql-bin.000003 test

ib_logfile1 mysql-bin.000001 mysql-bin.index

[root@mail data]# rm -rf ./*

[root@mail data]#

[root@mail ~]# innobackupex --copy-back /backup/2020-10-24_13-46-59/ # 进行还原 --copy-back 选项还要指定备份目录 #这里恢复证实了,不需要启动mysql服务器

[root@mail ~]# cd /mydata2/data/

[root@mail data]# ls

cactidb ib_logfile0 mysql test2

edb ib_logfile1 performance_schema testdb

hellodb jiaowu studb xtrabackup_binlog_pos_innodb(多了这个文件,没影响)

hellodb2 mydb students xtrabackup_checkpoints(多了这个文件,没影响)

ibdata1 mydb2 test

[root@mail data]#

再强调一次 innobackupex 恢复时,不需要启动 mysql数据库

但是恢复过来的属主属组可能不正确,所以需要把属主属组改为 mysql

[root@mail data]# pwd

/mydata2/data

[root@mail data]# ls -la

总计 28830

drwxr-xr-x 16 mysql mysql 3072 10-24 15:45 .

drwxr-xr-x 4 root root 1024 2019-07-12 ..

drwxr-xr-x 2 root root 1024 10-24 15:45 cactidb

drwxr-xr-x 2 root root 1024 10-24 15:45 edb

drwxr-xr-x 2 root root 1024 10-24 15:45 hellodb

drwxr-xr-x 2 root root 1024 10-24 15:45 hellodb2

-rw-r----- 1 root root 18874368 10-24 14:44 ibdata1

-rw-r--r-- 1 root root 5242880 10-24 15:45 ib_logfile0

-rw-r--r-- 1 root root 5242880 10-24 15:45 ib_logfile1

drwxr-xr-x 2 root root 1024 10-24 15:45 jiaowu

drwxr-xr-x 2 root root 1024 10-24 15:45 mydb

drwxr-xr-x 2 root root 1024 10-24 15:45 mydb2

drwxr-xr-x 2 root root 3072 10-24 15:45 mysql

drwxr-xr-x 2 root root 1024 10-24 15:45 performance_schema

drwxr-xr-x 2 root root 1024 10-24 15:45 studb

drwxr-xr-x 2 root root 1024 10-24 15:45 students

drwxr-xr-x 2 root root 1024 10-24 15:45 test

drwxr-xr-x 2 root root 1024 10-24 15:45 test2

drwxr-xr-x 2 root root 1024 10-24 15:45 testdb

-rw-r--r-- 1 root root 23 10-24 15:45 xtrabackup_binlog_pos_innodb

-rw-r--r-- 1 root root 77 10-24 15:45 xtrabackup_checkpoints

[root@mail data]#

[root@mail data]# pwd

/mydata2/data

[root@mail data]# chown -R mysql.mysql ./* #改属主属组为 mysql

[root@mail data]# ll

总计 28824

drwxr-xr-x 2 mysql mysql 1024 10-24 15:45 cactidb

drwxr-xr-x 2 mysql mysql 1024 10-24 15:45 edb

drwxr-xr-x 2 mysql mysql 1024 10-24 15:45 hellodb

drwxr-xr-x 2 mysql mysql 1024 10-24 15:45 hellodb2

-rw-r----- 1 mysql mysql 18874368 10-24 14:44 ibdata1

-rw-r--r-- 1 mysql mysql 5242880 10-24 15:45 ib_logfile0

-rw-r--r-- 1 mysql mysql 5242880 10-24 15:45 ib_logfile1

drwxr-xr-x 2 mysql mysql 1024 10-24 15:45 jiaowu

drwxr-xr-x 2 mysql mysql 1024 10-24 15:45 mydb

drwxr-xr-x 2 mysql mysql 1024 10-24 15:45 mydb2

drwxr-xr-x 2 mysql mysql 3072 10-24 15:45 mysql

drwxr-xr-x 2 mysql mysql 1024 10-24 15:45 performance_schema

drwxr-xr-x 2 mysql mysql 1024 10-24 15:45 studb

drwxr-xr-x 2 mysql mysql 1024 10-24 15:45 students

drwxr-xr-x 2 mysql mysql 1024 10-24 15:45 test

drwxr-xr-x 2 mysql mysql 1024 10-24 15:45 test2

drwxr-xr-x 2 mysql mysql 1024 10-24 15:45 testdb

-rw-r--r-- 1 mysql mysql 23 10-24 15:45 xtrabackup_binlog_pos_innodb

-rw-r--r-- 1 mysql mysql 77 10-24 15:45 xtrabackup_checkpoints

[root@mail data]#

#mysql 可以启动了

[root@mail data]# service mysqld start

Starting MySQL. [确定]

[root@mail data]#

连上 mysql

mysql> show databases;

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

| Database |

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

| information_schema |

| cactidb |

| edb |

| hellodb |

| hellodb2 |

| jiaowu |

| mydb |

| mydb2 |

| mysql |

| performance_schema |

| studb |

| students |

| test |

| test2 |

| testdb |

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

15 rows in set (0.01 sec)

mysql>

mysql> use jiaowu

Database changed

mysql> select * from tutors; # stu005 stu006都没有

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

| tid | tname | gender | age |

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

| 1 | HongQigong | M | 93 |

| 2 | HuangYaoshi | M | 63 |

| 3 | Miejueshitai | F | 72 |

| 4 | OuYangfend | M | 76 |

| 5 | YiDeng | M | 90 |

| 6 | YuCanghai | M | 56 |

| 7 | Jinlunfawang | M | 67 |

| 8 | HuYidao | M | 42 |

| 9 | NingZhongze | F | 49 |

| 10 | tom | F | 30 |

| 11 | DingDian | M | 25 |

| 12 | HuFei | M | 70 |

| 13 | stu111 | F | 60 |

| 14 | stu22 | F | 50 |

| 15 | stu100 | M | 30 |

| 16 | stu001 | NULL | NULL |

| 17 | stu002 | NULL | NULL |

| 18 | stu0003 | NULL | NULL |

| 19 | stu0004 | NULL | NULL |

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

19 rows in set (0.00 sec)

mysql>

要进行二进制还原把 stu0005 stu0006 弄过来

/root/mysql-bin.000001 整个文件都用得上(因为上面的position 是从107开始的,107之前的全是头部信息) #把/root/mysql-bin.000001 读取出来保存为 /tmp/abc.sql的文件

[root@mail data]# mysqlbinlog /root/mysql-bin.000001 > /tmp/abc.sql

[root@mail data]#

mysql> set sql_log_bin=0; #禁用二进制日志