一、备份命令

 

 

1、xbstream 流式边备份边传输

###############################################################################

这里假设：需要将10.10.10.10机器上3306端口的mysql服务备份到10.10.10.11机器上保存着。

###############################################################################

在需要被备份的机器10.10.10.10上执行，但需要等10.10.10.11上先执行nc：

[work@10.10.10.10 tmp]$ innobackupex --defaults-file=/home/work/mysql/etc/my.cnf   --stream=xbstream --user,backup --password=123456  --host=127.0.0.1  --port=3306 --slave-info  /tmp/ | nc  10.10.10.11  5200

###################################################################

在保存备份文件的机器10.10.10.11上先执行

[work@10.10.10.11 tmp]$ nc -l  5200 --recv-only |xbstream  -x  -C  /home/work/tmp/

###################################################################

备份完成后，在保存备份文件的机器10.10.10.11上执行，进行恢复操作。
[work@10.10.10.11 tmp]$ innobackupex --apply-log /home/work/tmp/

 

 

远程备份：

1，远程存储备份机器上执行：nc和xbstream 

shell> ulimit -n 1000000; nc -ld 24268 |xbstream -x -C /home/work/2021-09-06_10-16-16

 

2，目标mysql实例上执行备份并传输：innobackupex 和nc

shell> cd /home/work/tmp_3306/2021-09-06_10-16-19/; 

shell> innobackupex --defaults-file=/mysql_3306/etc/my.cnf --user=backup --password=123456 --host=127.0.0.1 --slave-info --ftwrl-wait-timeout=60 --ftwrl-wait-query-type=all --ibbackup=xtrabackup --stream=xbstream --tmpdir=/home/2021-09-06_10-16-19/ . 2> /home/work/xtra.log | nc 10.10.10.11 24268

 

3，恢复日志：

备份完成后，在保存备份文件的机器10.10.10.11上执行，进行恢复操作。
[work@10.10.10.11 tmp]$ innobackupex --apply-log  /home/work/2021-09-06_10-16-16
# 下面命令也可以：
shell> xtrabackup --defaults-file=/home/work/10.10.10.10_3306/2021-09-06_10-16-16/backup-my.cnf --use-memory=12147483648 --prepare --apply-log-only --target-dir=/home/work/10.10.10.10_3306/2021-09-06_10-16-16

 

 

 命令详解：

备份机：表示mysql实例所在的机器，这是需要被备份的机器

--defaults-file：必须放在innobackupex命令后作为第一个参数。

--stream=tar：表示tar格式流备份

--slave-info：表示在备份从库时,可以使用该参数，加上--slave-info备份目录下会多生成一个xtrabackup_slave_info 文件, 这里会保存主日志文件以及偏移, 文件内容类似于:CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=0。

/home/work/tmp：表示临时文件存放地方

|gzip  > full.tar.gz ：表示将innobackupex命令的输出通过管道作为gzip命令的输入，最后将其

 

 

 

5.7.25版本的mysql备份：

####################################################################################

shell> innobackupex --defaults-file=/home/work/mysql_3306/etc/my.cnf   --stream=tar --user=mysqlbackup --password=123456 --host=127.0.0.1 --slave-info  /home/work/tmp |gzip  > full.tar.gz

解压：
shell> tar -xzvf   full.tar.gz   -C  ./tmp
# 进入解压目录执行：
shell> innobackupex  --apply-log   .
#######################################################################################

 

5.5.31的mysql版本备份：

##################

shell> innobackupex --defaults-file=/home/work/mysql/etc/my.cnf    --user=mysqlbackup --password=123456 --host=127.0.0.1 --slave-info  ./

# 进入解压目录执行：
shell> innobackupex  --apply-log   .


#########################################################################################

 

边备份边传输：

新机器
nc -l  9999 --recv-only|xbstream  -x  -C .
# 结束完一定要apply log
innobackupex  --apply-log   .

备份机器
innobackupex --defaults-file=/home/work/mysql/etc/my.cnf   --stream=xbstream --user=mysqlbackup --password=123456 --host=127.0.0.1 --slave-info  /tmp/ | nc 新机器hostname  9999

 

 

二、5.5.31版本mysql的备份案例（81G的data）

开始备份：

shell> innobackupex --defaults-file=/home/work/mysql/etc/my.cnf    --user=mysqlbackup --password=123456 --host=127.0.0.1 --slave-info  ./

 

备份日志：



#########################################################################################################################################################

开始备份。

#########################################################################################################################################################

[work@igoodful tmp]$ innobackupex --defaults-file=/home/work/mysql/etc/my.cnf    --user=backup --password=123456 --host=127.0.0.1 --slave-info  ./      

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

200618 09:51:27  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/home/work/mysql/etc/my.cnf;mysql_read_default_group=xtrabackup;host=127.0.0.1' as 'backup'  (using password: YES).
200618 09:51:27  innobackupex: Connected to MySQL server
200618 09:51:27  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!".

innobackupex:  Using server version 5.5.31-log

innobackupex: Created backup directory /home/work/tmp/2020-06-18_09-51-27

200618 09:51:27  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/home/work/mysql/etc/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/home/work/tmp/2020-06-18_09-51-27 --tmpdir=/home/work/mysql/tmp --extra-lsndir='/home/work/mysql/tmp'
innobackupex: Waiting for ibbackup (pid=2130) to suspend
innobackupex: Suspend file '/home/work/tmp/2020-06-18_09-51-27/xtrabackup_suspended_2'

xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/work/mysql/data
xtrabackup: open files limit requested 0, set to 150240
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 268435456
xtrabackup: using O_DIRECT
>> log scanned up to (266620108746)
xtrabackup: Generating a list of tablespaces
[01] Copying ./ibdata1 to /home/work/tmp/2020-06-18_09-51-27/ibdata1
[01]        ...done
[01] Copying ./galaxy_availability/FDS_AVAILABILITY_MINUTE.ibd to /home/work/tmp/2020-06-18_09-51-27/galaxy_availability/FDS_AVAILABILITY_MINUTE.ibd
[01]        ...done
>> log scanned up to (266620108746)
[01] Copying ./galaxy_availability/EMR_FUSION_AVAILABILITY_MINUTE.ibd to /home/work/tmp/2020-06-18_09-51-27/galaxy_availability/EMR_FUSION_AVAILABILITY_MINUTE.ibd
[01]        ...done
[01] Copying ./galaxy_availability/FDS_FUSION_AVAILABILITY_DAY.ibd to /home/work/tmp/2020-06-18_09-51-27/galaxy_availability/FDS_FUSION_AVAILABILITY_DAY.ibd
[01]        ...done
[01] Copying ./galaxy_availability/SDS_FUSION_AVAILABILITY_DAY.ibd to /home/work/tmp/2020-06-18_09-51-27/galaxy_availability/SDS_FUSION_AVAILABILITY_DAY.ibd
[01]        ...done
[01] Copying ./awsind_resource_management/auth_user.ibd to /home/work/tmp/2020-06-18_09-51-27/awsind_resource_management/auth_user.ibd
[01]        ...done
[01] Copying ./awsind_resource_management/apps.ibd to /home/work/tmp/2020-06-18_09-51-27/awsind_resource_management/apps.ibd
>> log scanned up to (266620108746)
>> log scanned up to (266620108746)
>> log scanned up to (266620108746)
>> log scanned up to (266620428552)
>> log scanned up to (266620430368)
[01]        ...done
[01] Copying ./awsind_resource_management/appTrackingUrl.ibd to /home/work/tmp/2020-06-18_09-51-27/awsind_resource_management/appTrackingUrl.ibd
[01]        ...done
[01] Copying ./awsind_resource_management/bills.ibd to /home/work/tmp/2020-06-18_09-51-27/awsind_resource_management/bills.ibd
>> log scanned up to (266620431271)
[01]        ...done
[01] Copying ./awsind_resource_management/cluster_availability.ibd to /home/work/tmp/2020-06-18_09-51-27/awsind_resource_management/cluster_availability.ibd
[01]        ...done
[01] Copying ./awsind_resource_management/containers.ibd to /home/work/tmp/2020-06-18_09-51-27/awsind_resource_management/containers.ibd
[01]        ...done
[01] Copying ./awsind_resource_management/operations.ibd to /home/work/tmp/2020-06-18_09-51-27/awsind_resource_management/operations.ibd
[01]        ...done
[01] Copying ./awsind_resource_management/bills_v2.ibd to /home/work/tmp/2020-06-18_09-51-27/awsind_resource_management/bills_v2.ibd
[01]        ...done
[01] Copying ./awsind_resource_management/xiaomi_teams.ibd to /home/work/tmp/2020-06-18_09-51-27/awsind_resource_management/xiaomi_teams.ibd
[01]        ...done
[01] Copying ./awsind_resource_management/auth_team.ibd to /home/work/tmp/2020-06-18_09-51-27/awsind_resource_management/auth_team.ibd
[01]        ...done
[01] Copying ./awsind_resource_management/__apps_old.ibd to /home/work/tmp/2020-06-18_09-51-27/awsind_resource_management/__apps_old.ibd
>> log scanned up to (266620574530)
>> log scanned up to (266620574530)
>> log scanned up to (266620574530)
>> log scanned up to (266620577856)
..............
>> log scanned up to (266620579134)
>> log scanned up to (266620580930)
>> log scanned up to (266620582722)
>> log scanned up to (266620584094)
[01]        ...done
[01] Copying ./awsind_resource_management/containers_summary.ibd to /home/work/tmp/2020-06-18_09-51-27/awsind_resource_management/containers_summary.ibd
[01]        ...done
[01] Copying ./awsind_resource_management/clusters.ibd to /home/work/tmp/2020-06-18_09-51-27/awsind_resource_management/clusters.ibd
[01]        ...done
[01] Copying ./awsind_resource_management/queues.ibd to /home/work/tmp/2020-06-18_09-51-27/awsind_resource_management/queues.ibd
[01]        ...done
[01] Copying ./awsind_resource_management/cluster_state.ibd to /home/work/tmp/2020-06-18_09-51-27/awsind_resource_management/cluster_state.ibd
>> log scanned up to (266620585450)
[01]        ...done
[01] Copying ./awsind_resource_management/bills_v3.ibd to /home/work/tmp/2020-06-18_09-51-27/awsind_resource_management/bills_v3.ibd
>> log scanned up to (266620586840)
[01]        ...done
[01] Copying ./awsind_resource_management/schedules.ibd to /home/work/tmp/2020-06-18_09-51-27/awsind_resource_management/schedules.ibd
[01]        ...done
[01] Copying ./awsind_resource_management/monitor.ibd to /home/work/tmp/2020-06-18_09-51-27/awsind_resource_management/monitor.ibd
>> log scanned up to (266620587297)
>> log scanned up to (266620587297)
>> log scanned up to (266620587297)
>> log scanned up to (266620587297)

xtrabackup: Creating suspend file '/home/work/tmp/2020-06-18_09-51-27/xtrabackup_suspended_2' with pid '2130'

200618 10:00:03  innobackupex: Continuing after ibbackup has suspended
200618 10:00:03  innobackupex: Executing FLUSH TABLES WITH READ LOCK...
200618 10:00:03  innobackupex: All tables locked and flushed to disk

200618 10:00:03  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/home/work/mysql/data/'
innobackupex: Backing up files '/home/work/mysql/data//galaxy_availability/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (23 files)
innobackupex: Backing up files '/home/work/mysql/data//druid/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (11 files)
innobackupex: Backing up files '/home/work/mysql/data//awsind_resource_management/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (25 files)
>> log scanned up to (266622132062)
innobackupex: Backing up files '/home/work/mysql/data//owl2_azmb/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (32 files)
innobackupex: Backing up file '/home/work/mysql/data//dba_metadata/karma.frm'
innobackupex: Backing up file '/home/work/mysql/data//dba_metadata/cluster.frm'
innobackupex: Backing up file '/home/work/mysql/data//dba_metadata/db.opt'
innobackupex: Backing up files '/home/work/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)
innobackupex: Backing up file '/home/work/mysql/data//sparksql/grant_history.frm'
innobackupex: Backing up file '/home/work/mysql/data//sparksql/sparksql_queue_review.frm'
innobackupex: Backing up file '/home/work/mysql/data//sparksql/kudu_table_reviews.frm'
innobackupex: Backing up file '/home/work/mysql/data//sparksql/history.frm'
innobackupex: Backing up file '/home/work/mysql/data//sparksql/db.opt'
innobackupex: Backing up file '/home/work/mysql/data//sparksql/sparksql_queue.frm'
innobackupex: Backing up files '/home/work/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
innobackupex: Backing up files '/home/work/mysql/data//hbase_manager/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (16 files)
200618 10:00:04  innobackupex: Finished backing up non-InnoDB tables and files

200618 10:00:04  innobackupex: Failed to get master binlog coordinates from SHOW SLAVE STATUS
200618 10:00:04  innobackupex: This means that the server is not a replication slave. Ignoring the --slave-info option
200618 10:00:04  innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
200618 10:00:04  innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '266622099673'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (266622132062)

xtrabackup: Creating suspend file '/home/work/tmp/2020-06-18_09-51-27/xtrabackup_log_copied' with pid '2130'
xtrabackup: Transaction log of lsn (266620104647) to (266622132062) was copied.
200618 10:00:05  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/home/work/tmp/2020-06-18_09-51-27'
innobackupex: MySQL binlog position: filename 'mysql-bin.000550', position 794971537
200618 10:00:05  innobackupex: Connection to database server closed
200618 10:00:05  innobackupex: completed OK!
###############################################################################################################

备份完毕。

###############################################################################################################
[work@igoodful tmp]$ ls
2020-06-18_09-51-27
[work@igoodful tmp]$ cd 2020-06-18_09-51-27/
[work@igoodful 2020-06-18_09-51-27]$ ls
awsind_resource_management  backup-my.cnf  dba_metadata  druid  galaxy_availability  hbase_manager  ibdata1  mysql  owl2_azmb  performance_schema  sparksql  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info  xtrabackup_logfile

##################################################################################################################

在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。

因此，此时数据文件仍处理不一致状态。--apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。

###################################################################################################################


 

应用log：

shell> innobackupex  --apply-log   .

日志如下：

[work@igoodful 2020-06-18_09-51-27]$ innobackupex  --apply-log   .

####################################################################################################################
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

200618 10:02:01  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!".


200618 10:02:01  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/home/work/tmp/2020-06-18_09-51-27/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/home/work/tmp/2020-06-18_09-51-27

xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: cd to /home/work/tmp/2020-06-18_09-51-27
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2277376, start_lsn=(266620104647)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2277376
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2277376
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: 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: Log scan progressed past the checkpoint lsn 266620104647
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: Doing recovery: scanned up to log sequence number 266622132062 (100%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 
                             71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 794971537, file name /home/work/mysql/log/mysql-bin.000550
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.22 started; log sequence number 266622132062

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 794971537, file name /home/work/mysql/log/mysql-bin.000550

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 266622132466

200618 10:02:04  innobackupex: Restarting xtrabackup with command: xtrabackup  --defaults-file="/home/work/tmp/2020-06-18_09-51-27/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/home/work/tmp/2020-06-18_09-51-27
for creating ib_logfile*

xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: cd to /home/work/tmp/2020-06-18_09-51-27
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 268435456
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 268435456
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: 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 256 MB
InnoDB: Progress in MB: 100 200
InnoDB: Setting log file ./ib_logfile1 size to 256 MB
InnoDB: Progress in MB: 100 200
InnoDB: Setting log file ./ib_logfile2 size to 256 MB
InnoDB: Progress in MB: 100 200
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=266622132466
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.22 started; log sequence number 266622132748

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 794971537, file name /home/work/mysql/log/mysql-bin.000550

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 266622133278
200618 10:02:09  innobackupex: completed OK!

######################################################################

日志应用完毕，此时的备份就可以完全用于恢复了。

######################################################################

[work@igoodful 2020-06-18_09-51-27]$

 

 

 

 

三、5.7.25的mysql备份

 

 

 

 

 

 

 

################################