mysql的备份恢复-创新互联

一、mysql的备份
1、mysqldump
逻辑备份，支持温备，热备，使用mysqldump完全备份，binlog增量备份
mysqldump -utest -ptest -h292.168.0.204 test > database-date +'%Y-%m-%d'
1）、备份innodb引擎的数据库时
mysqldump --single-transaction --master-data=2 --flush-log -u -p >mysql.bak
--single-transaction 启动一个大的事务进行dump
--master-data=# 记录change-master-to
=2时记录为注释，1不注释
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=614;
--flush-logs 重新又新的二进制日志记录
2)还原备份
在恢复数据的时候会生成新的二进制日志，如果不需要记录需进行一下设置
set session sql_log_bin=0;
然后导入完全备份的数据 source
然后 set session sql_log_bin=1
mysqlbinlog --start-position=# --stop-position=# binlog.00000# | mysql -u -p
二、xtarbackup
1、xtarbackup的备份
版本innobackupex version 2.4.8 Linux (x86_64) (revision id: 97330f7)
参数说明
--compress：该选项表示压缩innodb数据文件的备份。
--compress-threads：该选项表示并行压缩worker线程的数量。
--compress-chunk-size：该选项表示每个压缩线程worker buffer的大小，单位是字节，默认是64K。
--encrypt：该选项表示通过ENCRYPTION_ALGORITHM的算法加密innodb数据文件的备份，目前支持的算法有ASE128,AES192,AES256。
--encrypt-threads：该选项表示并行加密的worker线程数量。
--encrypt-chunk-size：该选项表示每个加密线程worker buffer的大小，单位是字节，默认是64K。
--encrypt-key：该选项使用合适长度加密key，因为会记录到命令行，所以不推荐使用。
--encryption-key-file：该选项表示文件必须是一个简单二进制或者文本文件，加密key可通过以下命令行命令生成：openssl rand -base64 24。
--include：该选项表示使用正则表达式匹配表的名字[db.tb]，要求为其指定匹配要备份的表的完整名称，即databasename.tablename。
--user：该选项表示备份账号。
--password：该选项表示备份的密码。
--port：该选项表示备份数据库的端口。
--host：该选项表示备份数据库的地址。
--databases：该选项接受的参数为数据名，如果要指定多个数据库，彼此间需要以空格隔开；如："xtra_test dba_test"，同时，在指定某数据库时，也可以只指定其中的某张表。如："mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表。此外，此选项也可以接受一个文件为参数，文件中每一行为一个要备份的对象。
--tables-file：该选项表示指定含有表列表的文件，格式为database.table，该选项直接传给--tables-file。
--socket：该选项表示mysql.sock所在位置，以便备份进程登录mysql。
--no-timestamp：该选项可以表示不要创建一个时间戳目录来存储备份，指定到自己想要的备份文件夹。
--ibbackup：该选项指定了使用哪个xtrabackup二进制程序。IBBACKUP-BINARY是运行percona xtrabackup的命令。这个选项适用于xtrbackup二进制不在你是搜索和工作目录，如果指定了该选项,innoabackupex自动决定用的二进制程序。
--slave-info：该选项表示对slave进行备份的时候使用，打印出master的名字和binlog pos，同样将这些信息以change master的命令写入xtrabackup_slave_info文件。可以通过基于这份备份启动一个从库。
--safe-slave-backup：该选项表示为保证一致性复制状态，这个选项停止SQL线程并且等到show status中的slave_open_temp_tables为0的时候开始备份，如果没有打开临时表，bakcup会立刻开始，否则SQL线程启动或者关闭知道没有打开的临时表。如果slave_open_temp_tables在--safe-slave-backup-timeount（默认300秒）秒之后不为0，从库sql线程会在备份完成的时候重启。
--rsync：该选项表示通过rsync工具优化本地传输，当指定这个选项，innobackupex使用rsync拷贝非Innodb文件而替换cp，当有很多DB和表的时候会快很多，不能--stream一起使用。
--kill-long-queries-timeout：该选项表示从开始执行FLUSH TABLES WITH READ LOCK到kill掉阻塞它的这些查询之间等待的秒数。默认值为0，不会kill任何查询，使用这个选项xtrabackup需要有Process和super权限。
--kill-long-query-type：该选项表示kill的类型，默认是all，可选select。
--ftwrl-wait-threshold：该选项表示检测到长查询，单位是秒，表示长查询的阈值。
--ftwrl-wait-query-type：该选项表示获得全局锁之前允许那种查询完成，默认是ALL，可选update。
--galera-info：该选项表示生成了包含创建备份时候本地节点状态的文件xtrabackup_galera_info文件，该选项只适用于备份PXC。
--stream：该选项表示流式备份的格式，backup完成之后以指定格式到STDOUT，目前只支持tar和xbstream。
--defaults-file：该选项指定了从哪个文件读取MySQL配置，必须放在命令行第一个选项的位置。
--defaults-extra-file：该选项指定了在标准defaults-file之前从哪个额外的文件读取MySQL配置，必须在命令行的第一个选项的位置。一般用于存备份用户的用户名和密码的配置文件。
----defaults-group：该选项表示从配置文件读取的组，innobakcupex多个实例部署时使用。
--no-lock：该选项表示关闭FTWRL的表锁，只有在所有表都是Innodb表并且不关心backup的binlog pos点，如果有任何DDL语句正在执行或者非InnoDB正在更新时（包括mysql库下的表），都不应该使用这个选项，后果是导致备份数据不一致，如果考虑备份因为获得锁失败，可以考虑--safe-slave-backup立刻停止复制线程。
--tmpdir：该选项表示指定--stream的时候，指定临时文件存在哪里，在streaming和拷贝到远程server之前，事务日志首先存在临时文件里。在 使用参数stream=tar备份的时候，你的xtrabackup_logfile可能会临时放在/tmp目录下，如果你备份的时候并发写入较大的话 xtrabackup_logfile可能会很大(5G+)，很可能会撑满你的/tmp目录，可以通过参数--tmpdir指定目录来解决这个问题。
--history：该选项表示percona server 的备份历史记录在percona_schema.xtrabackup_history表。
--incremental：该选项表示创建一个增量备份，需要指定--incremental-basedir。
--incremental-basedir：该选项表示接受了一个字符串参数指定含有full backup的目录为增量备份的base目录，与--incremental同时使用。
--incremental-dir：该选项表示增量备份的目录。
--incremental-force-scan：该选项表示创建一份增量备份时，强制扫描所有增量备份中的数据页。
--incremental-lsn：该选项表示指定增量备份的LSN，与--incremental选项一起使用。
--incremental-history-name：该选项表示存储在PERCONA_SCHEMA.xtrabackup_history基于增量备份的历史记录的名字。Percona Xtrabackup搜索历史表查找最近（innodb_to_lsn）成功备份并且将to_lsn值作为增量备份启动出事lsn.与innobackupex--incremental-history-uuid互斥。如果没有检测到有效的lsn，xtrabackup会返回error。
--incremental-history-uuid：该选项表示存储在percona_schema.xtrabackup_history基于增量备份的特定历史记录的UUID。
--close-files：该选项表示关闭不再访问的文件句柄，当xtrabackup打开表空间通常并不关闭文件句柄目的是正确的处理DDL操作。如果表空间数量巨大，这是一种可以关闭不再访问的文件句柄的方法。使用该选项有风险，会有产生不一致备份的可能。
--compact：该选项表示创建一份没有辅助索引的紧凑的备份。
--throttle：该选项表示每秒IO操作的次数，只作用于bakcup阶段有效。apply-log和--copy-back不生效不要一起用。
1）连接服务器权限
MariaDB [hellodb]> CREATE USER 'backuser'@'localhost' IDENTIFIED BY 'smile';
MariaDB [hellodb]> GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT,PROCESS ON *.* TO 'backuser'@'localhost';
MariaDB [hellodb]>FLUSH PRIVILEGES;
授权时特别注意 localhost 不能用 %
官方文档中的权限描述：

2）备份
innobackupex -ubackuser -psmile /backup
备份后在/backup 文件夹下生成一个当前时间戳的文件夹

xtrabackup_binlog_info 记录mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置
[root@node4 2017-12-04_15-56-40]# cat xtrabackup_binlog_info
binlog.000010 1814 0-1-56
MariaDB [hellodb]> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000010 | 1814 | | |
+---------------+----------+--------------+------------------+
xtrabackup_checkpoints 记录备份类型，日志序列号（LSN），及备份状态
[root@node4 2017-12-04_15-56-40]# cat xtrabackup_checkpoints
backup_type = full-backuped（当--apply-log后 backup_type = full-prepared）
from_lsn = 0
to_lsn = 1899901
last_lsn = 1899910
compact = 0
recover_binlog_info = 0
ib_buffer_pool， buffer pool 中的热数据，当设置 innodb_buffer_pool_dump_at_shutdown=1 ，在关闭 MySQL 时，会把内存中的热数据保存在磁盘里 ib_buffer_pool 文件中，位于数据目录下。
ibdata1，备份的共享表空间文件；
xtrabackup_info，记录备份的基本信息，uuid、备份命令、备份时间、binlog、LSN、以及其他加密压缩等信息。
[root@node4 2017-12-04_15-56-40]# cat xtrabackup_info
uuid = abd4d399-d8c8-11e7-b084-000c293e4e76
name =
tool_name = innobackupex
tool_command = -ubackuser -psmile /backup
tool_version = 2.4.8
ibbackup_version = 2.4.8
server_version = 10.2.10-MariaDB-log
start_time = 2017-12-04 15:56:40
end_time = 2017-12-04 15:56:44
lock_time = 0
binlog_pos = filename 'binlog.000010', position '1814', GTID of the last change '0-1-56'
innodb_from_lsn = 0
innodb_to_lsn = 1899901
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
xtrabackup_logfile，备份的重做日志文件。
backup-my.cnf，备份命令用到的配置选项信息
2、还原
还原之前需prepare备份文件
innobackupex --apply-log [--use-memory=B]
[--defaults-file=MY.CNF]
[--export] [--redo-only] [--ibbackup=IBBACKUP-BINARY]
BACKUP-DIR
--apply-log：该选项表示同xtrabackup的--prepare参数,一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据 文件仍处理不一致状态。--apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。
--use-memory：该选项表示和--apply-log选项一起使用，prepare 备份的时候，xtrabackup做crash recovery分配的内存大小，单位字节。也可(1MB,1M,1G,1GB)，推荐1G。
--defaults-file：该选项指定了从哪个文件读取MySQL配置，必须放在命令行第一个选项的位置。
--export：这个选项表示开启可导出单独的表之后再导入其他Mysql中。
--redo-only：这个选项在prepare base full backup，往其中merge增量备份（但不包括最后一个）时候使用。

还原
innobackupex --copy-back [--defaults-file=MY.CNF] [--defaults-group=GROUP-NAME] BACKUP-DIR
innobackupex --move-back [--defaults-file=MY.CNF] [--defaults-group=GROUP-NAME] BACKUP-DIR
--copy-back：做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir。
--move-back：这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这个选项移除backup文件，用时候必须小心。使用场景：没有足够的磁盘空间同事保留数据文件和Backup副本
注意：
1.datadir目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定，否则--copy-backup选项不会覆盖
2.在restore之前,必须shutdown MySQL实例，你不能将一个运行中的实例restore到datadir目录中
3.由于文件属性会被保留，大部分情况下你需要在启动实例之前将文件的属主改为mysql，这些文件将属于创建备份的用户
chown -R mysql:mysql /data1/dbrestore
以上需要在用户调用Innobackupex之前完成
--force-non-empty-directories：指定该参数时候，使得innobackupex --copy-back或--move-back选项转移文件到非空目录，已存在的文件不会被覆盖。如果--copy-back和--move-back文件需要从备份目录拷贝一个在datadir已经存在的文件，会报错失败。

3、增量备份
1)全备 全备操作如上
2)在全备基础上增备

[root@node4 2017-12-05_10-35-33]# innobackupex -ubackuser -psmile --incremental --incremental-basedir=/backup/2017-12-05_10-35-33 /backup/

3)还原先--apply-log 且--redo-only全备 然后在准备好的全备上--apply-log和--redo-only增备 最后一次无需--redo-only

[root@node4 2017-12-05_10-35-33]# innobackupex --apply-log --redo-only /backup/2017-12-05_10-35-33

 innobackupex --apply-log --incremental-dir=/backup/2017-12-05_10-47-40/ /backup/2017-12-05_10-35-33

备份报错：
innobackupex version 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mydata
xtrabackup: open files limit requested 0, set to 1024
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
InnoDB: Number of pools: 1
InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: File ./ib_logfile0: 'open' returned OS error 71. Cannot continue operation
InnoDB: Cannot continue operation.

报此错需要指定--defaults-file 并且配置文件中需指定datadir和basedir并重启mysql

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。