备份考虑的问题
业务连续性
备份完整性
备份种类
逻辑备份
特点：建表、建库、插入数据等操作所执行的语句（DDL DML DCL）,适用于中小型数据库，效率相对较低，对存储引擎有要求。
工具：
mysqldump #官方
mydumper #第三方
物理备份
特点：文件系统层面备份。直接复制数据库文件，适用于大型数据库环境，不受存储引擎限制，但不能恢复到不同的Mysql版本。
工具：
tar、cp #完全备份，需要停服务。
lvm snapshot #完全备份，热备份。数据库目录必须为逻辑卷，对整个目录逻辑卷做快照，为热备份，不需停服务，只要锁表即可
xtrabackup #完全/增量/差异都可以，热备，只限于Innodb 等引擎。
inbackup
完全备份
对整个数据库备份
增量备份
周日全备份，周一在周日基础上做增量备份，周二在周一基础上做增量备份以此类推。
差异备份
周日全备份，周一、周二..都在周日基础上做增量备份
各个备份工具使用
注意各个备份工具都是基于某个时间点对数据做的备份，备份恢复也只是恢复到了备份时间点的数据库的状态，由于数据库处于动态变化中，所以想要完全恢复数据，还要结合Binlog日志去恢复。
tar 备份
备份过程
停止数据库 systemctl stop mysqld
记录binlog 位置 mysql -p'asdfgfdf' -e 'show master status' > /backup/position.txt #binlog恢复时用到
备份数据库文件 tar -zcvf /var/lib/mysql.tar.gz /var/lib/mysql
启动数据库 systemctl start mysqld
还原过程
停止数据库 systemctl stop mysqld
清理旧数据 rm -rf /var/lib/mysql/* #如果是使用中的数据库，需要删除旧的数据，再导入备份数据
导入数据库文件 tar -xf mysql-all-back.tar.gz -C /var/lib/mysql/
启动数据库 systemctl start mysqld
binlog 恢复
lvm 快照备份
备份过程
1、加全局锁与创建快照 echo "flush tables with read lock;SYSTEM lvcreate -s -n mysql_snap -L 1G /dev/mysql_vg/mysql_lv" | mysql -p'asdfgfdf' 2、记录binlog位置 mysql -p'asdfgfdf' -e 'show master status' > /backup/position.txt #记录binlog 日志位置 3、从快照中备份： mount -o ro,nouuid /dev/mysql_vg/mysql_snap /mnt #如果是xfs 文件系统必须加nouuid 选项 cd /mnt tar -zcf /backup/`date +%F`_mysql_all_backup.tar.gz ./* 4、删除快照： cd;umount /mnt lvremove -f /dev/mysql_vg/mysql_snap
恢复
同上tar
binlog 恢复
Notice
lvm 快照的前提是mysql 数据目录必须挂载在逻辑卷上，如果没有需要迁移
lvm 快照的前提是mysql 数据目录必须挂载在逻辑卷上，如果没有需要创建逻辑卷并迁移数据库目录到逻辑卷上面 迁移过程 systemctl stop mysqld mkfs.xfs /dev/mysql_vg/mysql_lv mount /dev/mysql_vg/mysql_lv /mnt/ #将逻辑卷进行临时挂载 cp -a /var/lib/mysql/* /mnt #拷贝所有数据到新的逻辑卷分区中 umount /mnt #取消临时挂载 echo "/dev/mysql_vg/mysql_lv /var/lib/mysql xfs defaults 0 0" > /etc/fstab #将已有数据的逻辑卷永久挂载 mount -a systemctl start mysqld
关于锁表操作
问题：如果是手动锁表，必须在执行完快照创建后再退出登录的会话，否则锁表操作会随着会话退出而失效，如果是脚本执行锁表也会有此现象。如下 mysql -p'asdfgfdf' -e 'flush tables with read lock;' #注意脚本执行此语句锁住表后会自动退出mysql 登录会话，锁表操作就失效了，所以锁表操作与快照创建操作要一并执行在会话退出之前。 解决：echo "flush tables with read lock;SYSTEM lvcreate -s -n mysql_snap -L 1G /dev/mysql_vg/mysql_lv" | mysql -p'asdfgfdf' 另外：快照必须与被备份的逻辑卷位于同一个VG上面，避免空间不足可以指定其与源lv 同样大小
xtrabackup 备份
安装（2.4.15）
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum -y install percona-xtrabackup-24.x86_64
完全备份
innobackupex --defaults-file=/etc/my.cnf --user=root --password="123456" --backup /root #根据配置文件中路径备份，无需停服务，备份目录中记录了binglog日志位置 ll /root/2017-12-04_13-57-29/ 恢复 systemctl stop mysqld innobackupex --apply-log /root/2017-12-04_13-57-29/ innobackupex --defaults-file=/etc/my.cnf --copy-back /root/2017-12-04_13-57-29/ binlog 恢复 systemctl start mysqld
增量备份
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --incremental /backup/ --incremental-basedir=/root/2017-12-04_13-57-29 #--incremental /backup/ 指定增量备份文件备份的目录 #--incremental-basedir 指定上一次全备或增量备份的目录 根据前一天的增量再次做增备 innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --incremental /backup/ --incremental-basedir=/root/2017-12-05_13-57-29 恢复 先把增量恢复到全备中 innobackupex --apply-log --redo-only /root/2017-12-04_13-57-29/ innobackupex --apply-log --redo-only /root/2017-12-04_13-57-29/ --incremental-dir=/backup/2017-12-05_09-27-06/ 恢复全部数据 innobackupex --defaults-file=/etc/my.cnf --copy-back /root/2017-12-04_13-57-29/ 也可以通过cp /root/2017-12-04_13-57-29/ /var/lib/mysql/ 替代上面命令
差异备份
命令一样，指定的目录始终为完全备份
注意：无论哪种备份都要先记录binlog 日志位置
mysql -p'asdfgfdf' -e 'show master status' > /backup/position.txt #binlog 日志恢复时用到
mysqldump备份
mysqldump 备份语句
--master-data=1/2 #1不注释，2 注释
备份过程
flush tables with read lock; #会话别退出，执行完mysqldump后再退出
mysqldump -h192.168.1.100 -p3306 -uroot -ppassword --all-databases --master-data=1 > /data/backup/all_db.sql #--master-data 记录binlog 位置，方便binlog恢复查找。
unlock tables;
恢复数据库
如果是旧数据库需要进行前4步 1.停止数据库 2.清理数据rm -rf /var/lib/mysql/* #注意不需要删除日志文件（slowlog binlog log） 3.启动数据库 #数据库必须是启动的 4.改密码 5.SET SQL_LOG_BIN=0 #暂停binlog 日志,恢复时记录不必要的binlog ，所以可以暂停，当然不是必须的 6.登录musql执行source /backup/2017-12-05_09-27-06.sql 或 mysql -p'asdfgfdf' < /backup/2017-12-05_09-27-06.sql 注意：暂停binlog与执行恢复语句必须在同一会话下进行，参考lvm 快照备份加锁操作。 7.刷新授权 flush logs; 8.binlog 恢复
mysqldump 恢复数据如果仅仅恢复某一个数据库，可以直接删除数据库再恢复，无需停服务。
数据备份（之备份表中数据，不备份数据库和表结构）
备份数据
select * from test.school into outfile '/back/school.txt'
模拟误删除数据
delect from test.school
恢复数据 也把在excel中的数据通过语句导入mysql 表中
load data infile '/back/school.txt' into table test.school;
Notice
5.7以后mysql 安全增强了，必须指定一个安全目录，导出数据到指定目录才可以 vi /etc/my.cnf secure-file-priv=/back chown mysql.mysql /back -R
备份导出的几种文件格式
mysql -uroot -p'asdfgfdf' -e 'select * from test.school' > /back/school.txt mysql -uroot -p'asdfgfdf' --xml -e 'select * from test.school' > /back/school.xml mysql -uroot -p'asdfgfdf' --html -e 'select * from test.school' > /back/school.html
执行导入或者导出语句时也可以添加以下限制条件
FIELDS TERMINATED BY ',' //定义字段分隔符，不加默认就是空格 OPTIONALLY ENCLOSED BY "" // 定义字符串使用什么括号括起来 LINES TERMINATED BY ' '; //定义换行符