centos 数据库恢复

centos 数据库恢复
一，冷备通过cp拷贝恢复
拷贝全部目录的方法只适合两个同版本数据库，如果两个版本不一致，将会报错
1.    先把两边的数据库服务停掉
systemctl stop mariadb.service
2.    压缩数据库目录 完成备份
tar zcvf mysql.tar.gz /var/lib/mysql
scp mysql.tar.gz 192.168.19.11:/root/
3.    恢复 首先解压备份
tar xvf mysql.tar.gz
4.    然后复制到对应目录
#mv mysql/* /var/lib/mysql/
5.    启动服务
systemctl start mariadb.service 
6.    验证是否成功 
MariaDB [(none)]> select user,host,password from mysql.user where user='xiapi';
+-------+-----------+-------------------------------------------+
| user  | host      | password                                  |
+-------+-----------+-------------------------------------------+
| xiapi | localhost | *A02AA727CF2E8C5E6F07A382910C4028D65A053A |
+-------+-----------+-------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> 
二，通过mysqldump加二进制日志恢复
这种方法可以拷贝在不同版本上。
1.    首先二级制日志独立存放
[mysqld] 
log-bin=/data/mysql/mysql-bin
2.    完全备份，并记录备份的二进制位置 
mysqldump -A --master-data=2 | gzip > /backup/all_`date +%F`.sql.gz
-A代表备份全部数据库 ，-B代表备份指定数据库
3.    模拟完全备份后，有数据做了更改，将来用二进制日志恢复
#修改数据库 
insert students (name,age,gender)value('mage',20,'M');
insert students (name,age,gender)value('wang',22,'M'); 
4.    模拟数据库盘损坏
rm -rf /var/lib/mysql/* 
5.    解压数据库
cd /backup 
gzip -d all_2019-11-25.sql.gz 
6.    启动数据库
mysql_install_db --user=mysql  重新生成数据库文件
 systemctl start mariadb 启动数据库 
7.    还原数据库
MariaDB [(none)]>set sql_log_bin=0; 
MariaDB [(none)]>source /data/all_2019-11-25.sql 
8.    二进制日志的备份
[root@centos8 ~]#grep '^-- CHANGE MASTER TO' /data/all_2019-11-25.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=328;
查看完全备份的时候，二进制文件位置，以后恢复的时候，代表从这之后的二进制文件，是为完全备份的。
9.    二进制日志的恢复
[root@centos8 mysql]#mysqlbinlog mysql-bin.000001 --start-position=328 > /backup/inc.sql 
如果有多个二进制文件，可采取追加的方式
[root@centos8 mysql]#mysqlbinlog mysql-bin.000002 >> /backup/inc.sql 
设置session级变量，暂停二进制文件的记录，然后恢复二进制日志的数据
MariaDB [(none)]>set sql_log_bin=0; 
MariaDB [(none)]>source /backup/inc.sql 
10.    分库备份
[root@centos8 ~]#mysql -uroot -e 'show databases'|sed -rn '/^(Database|information_schema|performance_schema)$/!s#(.*)#mysqldump -B \1 | gzip > /data/\1.sql.gz#p' |bash 
 
[root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$' | sed -rn 's# (.*)#mysqldump -B \1 | gzip > /data/\1.sql.gz#p' |bash 
 
[root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'|while read db;do mysqldump -B $db | gzip > /data/$db.sql.gz;done 
 
[root@centos8 ~]#for db in `mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'`;do mysqldump -B $db | gzip > /data/$db.sql.gz;done 
11.    误删除某表之后进行恢复
a.    暂停数据库
b.    首先恢复删除表前最近的一次完全备份
c.    然后备份从完全备份到现在的二进制日志
d.    二进制日志找到删表操作，进行注释，然后恢复
三，mysqldump 创建通用选项
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html 
mysqldump, mydumper, phpMyAdmin Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件 mysqldump:是MySQL的客户端命令，通过mysql协议连接至mysql服务器进行备份
-A, --all-databases 
#备份所有数据库，含create database 
-B, --databases db_name… 
#指定备份的数据库，包括create database语句 
-E, --events：
#备份相关的所有event scheduler 
-R, --routines：
#备份所有存储过程和自定义函数 
--triggers：
#备份表相关触发器，默认启用,用--skip-triggers，不备份触发器 
--default-character-set=utf8 
#指定字符集 
--master-data[=#]： 
#此选项须启用二进制日志
#1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1 
#2：记录为注释的CHANGE MASTER TO语句 #此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启-- single-transaction） 
-F, --flush-logs 
#备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文 件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和-- single-transaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志 
--compact 
#去掉注释，适合调试，生产不使用 
-d, --no-data 
#只备份表结构 
-t, --no-create-info 
#只备份数据,不备份create table 
-n,--no-create-db 
#不备份create database，可被-A或-B覆盖 
--flush-privileges 
#备份mysql或相关时需要使用 
-f, --force 
#忽略SQL错误，继续执行
--hex-blob 
#使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY，BLOB，BIT的数 据类型的列时使用，避免乱码 
-q, --quick 
#不缓存查询，直接输出，加快备份速度 
四，xtrabackup备份工具
xtrabackup的新版变化 
xtrabackup版本升级到2.4后，相比之前的2.1有了比较大的变化：innobackupex 功能全部集成到 xtrabackup 里面，只有一个 binary程序，另外为了兼容考虑，innobackupex作为 xtrabackup 的软链 接，即xtrabackup现在支持非Innodb表备份，并且 Innobackupex 在下一版本中移除，建议通过 xtrabackup替换innobackupex
1.    备份选项
--user：#该选项表示备份账号 
--password：#该选项表示备份的密码 
--host：#该选项表示备份数据库的地址
--databases：#该选项接受的参数为数据库名，如果要指定多个数据库，彼此间需要以空格隔开； 
如："xtra_test dba_test"，同时，在指定某数据库时，也可以只指定其中的某张表。 
如："mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表 
--defaults-file：#该选项指定从哪个文件读取MySQL配置，必须放在命令行第一个选项位置 
--incremental：#该选项表示创建一个增量备份，需要指定--incremental-basedir 
--incremental-basedir：#该选项指定为前一次全备份或增量备份的目录，与--incremental同时使用 
--incremental-dir：#该选项表示还原时增量备份的目录 
--include=name：#指定表名，格式：databasename.tablename 
2.    预准备选项
--apply-log：
#一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚 未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。此选项作 用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态 -
-use-memory：
#和--apply-log选项一起使用，当prepare 备份时，做crash recovery分配的内存 大小，单位字节，也可1MB,1M,1G,1GB等，推荐1G 
--export：#表示开启可导出单独的表之后再导入其他Mysql中 
--redo-only：#此选项在prepare base full backup，往其中合并增量备份时候使用，但不包括对最 后一个增量备份的合并 
3.    还原选项
--copy-back：#做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir 
--move-back：#这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这 个选项移除backup文件，用时候必须小心。使用场景：没有足够的磁盘空间同事保留数据文件和Backup副本 
--force-non-empty-directories #指定该参数时候，使得innobackupex --copy-back或--moveback选项转移文件到非空目录，已存在的文件不会被覆盖。如果--copy-back和--move-back文件需要从 备份目录拷贝一个在datadir已经存在的文件，会报错失败
4.    注意事项
1. datadir 目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定，否则- -copy-back选项不会覆盖 
2. 在restore之前,必须shutdown MySQL实例，不能将一个运行中的实例restore到datadir目录中 
3. 由于文件属性会被保留，大部分情况下需要在启动实例之前将文件的属主改为mysql，这些文件将 属于创建备份的用户, 执行chown -R mysql:mysql /data/mysql,以上需要在用户调用 innobackupex之前完成 
五，xtrabackup 完全备份
装的数据库是mysql 5.7版本，yum安装的xtrabackup是2.3.6版本，使用的时候提示版本不支持
https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/  xtrabackup8版本
https://www.percona.com/downloads/Percona-XtraBackup-LATEST/  xtrabackup低版本
自己下载了一个percona-xtrabackup-24-2.4.16-1.el7.x86_64，支持，可用
 yum history info 8
 yum history undo 8
找到安装的动作，进行取消安装
 yum install percona-xtrabackup-24-2.4.16-1.el7.x86_64.rpm 
安装rpm包，并解决依赖，可能需要epel源
备份
1.    #/backup目录不需事先创建，但是建议自己创建。
xtrabackup --user=root --password=xiapi --backup --target-dir=/backup
还原
2.    预准备：确保数据一致，提交完成的事务，回滚未完成的事务 
xtrabackup --prepare --target-dir=/backup/
3.    复制到数据库目录 注意：数据库目录必须为空，MySQL服务不能启动 
（不用指定copy的指定目录，默认拷贝在配置文件，datadir指定的目录下）
xtrabackup --copy-back --target-dir=/backup/
4.    还原属性 
chown -R mysql.mysql data/
5.    启动服务 
service mysqld start
六，xtrabackup 完全备份+增量备份
备份过程 
1.    完全备份： 
mkdir /backup/
xtrabackup -uroot -pxiapi --backup --target-dir=/backup/base
2.    第一次修改数据
模拟修改数据
3.    第一次增量备份 
xtrabackup -uroot -pxiapi --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
4.    拷贝到安全位置
还原过程 
1.    预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务 
xtrabackup --prepare --apply-log-only --target-dir=/backup/base
2.    合并第1次增量备份到完全备份， 如果有多次增量备份，则最后一次不要加 --apply-log-only
xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc1
3.    复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动 
service mysqld stop
xtrabackup --copy-back --target-dir=/backup/base 
4.    还原属性：
chown -R mysql.mysql /mysql/data/
5.    启动服务：
service mysqld start