MySQL第六天（主从同步，数据读写分离）
day05一、数据备份与恢复-增量备份和增量恢复
a.binlog日志介绍（二进制文件）：使用Mysql数据库服务日志文件类型的一种。记录除查询之外的sql命令。默认没启用
查询的sql包括：selectdesc show
不是查询的命令：insert update delete create drop grant revoke
b.启用binlog日志
如何启用
日志默认存放位置
/var/lib/mysql
日志默认名称
主机名-bin.000001
51-bin.000001 （大于500M自动生成000002）
51-bin.index#索引文件
修改日志存储位置和名称？
mkdir /logdir
chown mysql/logdir
setenforce 0
vim /etc/my.cnf
server_id=51
#log-bin
log-bin=/logdir/plj ##指定日志文件位置
binlog-format=”mixed” ###指定日志保存类型，显示sql命令的完整格式，若不添加只会记录命令
#systemctl restart mysqld
查看日志文件内容（用命令查看，因为是二进制文件）
]#mysqlbinlog binlog日志| grep-i insert
]#mysqlbinlog binlog日志
e.日志文件记录sql命令的方式
时间点--start-datetime="yyyy-mm-dd hh:mm:ss"
--stop-datetime="yyyy-mm-dd hh:mm:ss"
偏移量--start-position=数字
--stop-position=数字
]#mysqlbinlog [选项]binlog日志 | mysql -uroot -p123456
日志默认存放位置/var/lib/mysql
日志默认名称 主机-bin.000001
51-bin.000001 binlog日志文件（大于500M自动生成000002）
51-bin.index索引文件
f.执行日志文件中的sql命令恢复数据
#mysqlbinlog [选项] 日志名 | mysql -uroot -p
mysql> deletefrom db3.user where> # mysqlbinlog--start-position=328 --stop-position=1102 plj.000001 | mysql -uroot -p654321
g.手动创建新的文件
mysql>flush logs;#刷新一次生成一个 哪个编号大日志往哪个存
mysql> system ls /logdir;
plj.000001plj.000002plj.index
# mysql -uroot -p123456 -e "flush logs" #命令行直接执行sql命令
mysql: Using a password on the command line interface can be insecure.
# ls
plj.000001plj.000002plj.000003plj.index
# ls
plj.000001plj.000002plj.000003 plj.index
# systemctl restart mysqld
# ls
plj.000001plj.000002plj.000003plj.000004 plj.index
]#systemctl restart mysqld
#]mysqldump -uroot -p654321 --flush-logs db3 > db3.sql #备份时生成新的日志文件
f.删除日志文件
f.1 使用sql命令删除
mysql> reset master;
Query OK, 0 rows affected (0.21 sec)
mysql> system ls /logdir;
plj.000001plj.index
mysql> purge master logs to "plj.000005"; #删除指定000005编号之前的日志所有index（不包括000005日志文件)
Query OK, 0 rows affected (0.14 sec)
mysql> system ls /logdir;
plj.000005plj.index
f.2 使用系统删除
rm -rf日志文件
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++==
6.2 安装第3方软件提供的命令，对数据做增量备份
# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
警告：libev-4.15-1.el6.rf.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥> 准备中... #################################
软件包 libev-4.15-1.el6.rf.x86_64 已经安装
# rpm -ivh percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
警告：percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm: 头V4 DSA/SHA1 Signature, 密钥> 准备中... #################################
软件包 percona-xtrabackup-24-2.4.7-1.el7.x86_64 已经安装
# rpm -qa|grep percona
percona-xtrabackup-24-2.4.7-1.el7.x86_64
# rpm -ql percona-xtrabackup-24-2.4.7-1.el7.x86_64
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/share/doc/percona-xtrabackup-24-2.4.7
/usr/share/doc/percona-xtrabackup-24-2.4.7/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz
完全备份
# innobackupex --user root --password 123456--databases="mysql sys performance_schema db5"/alldir--no-timestamp##/alldir为备份目标目录，且必须为空，如果需要日期目录，不需要--no-timestamp
# ls /alldir
backup-my.cnfdb5ib_buffer_poolibdata1mysqlperformance_schemasysxtrabackup_binlog_infoxtrabackup_checkpointsxtrabackup_infoxtrabackup_logfile
完全恢复（/var/lib/mysql必须为空）
# rm -rf /var/lib/mysql
# mkdir /var/lib/mysql
# innobackupex --user root --password 123456--databases="mysql sys performance_schema db5" --apply-log/alldir/##恢复日志
# innobackupex --user root --password 123456--databases="mysql sys performance_schema db5" --copy-back/alldir/##恢复数据
# ls /var/lib/mysql
db5ib_buffer_poolibdata1ib_logfile0ib_logfile1ibtmp1mysqlperformance_schemasysxtrabackup_binlog_pos_innodbxtrabackup_info
# chown -R mysql:mysql/var/lib/mysql
# ls -ld /var/lib/mysql
drwxr-xr-x. 6 mysql mysql 4096 2月27 04:24 /var/lib/mysql
# systemctl restart mysqld
# mysql -uroot -p123456
mysql>
增量备份：
1.有一次完全备份
]#innobackupex --user root--password 123456
--databases="mysql sys performance_schema db5"/fullbak--no-timestamp
insertintodb5.t1 values(400);
select* from db5.t1;
2.增量备份：改变数据后实行增量备份（备份新生成的数据）
]#innobackupex --user root--password 123456
--databases="mysql sys performance_schema db5"
--incremental/onedir
--incremental-basedir=/fullbak--no-timestamp
insertintodb5.t1 values(500);
select* from db5.t1;
3.第二次增量备份改变数据后实行增量备份
# innobackupex--user root --password 123456 --databases="mysql sys performance_schema db5"--incremental /twodir --incremental-basedir=/onedir--no-timestamp
增量恢复数据？(第一次，第二次，第三次备份的序列号和数据，恢复（合并）到第一次备份目录下##序列号和数据合并)
--applay-log恢复日志
--redo-only 合并日志
--incremental-dir=目录名恢复数据时，第几次增量备份时使用的备份目录
1.清空数据库目录
rm -rf /var/lib/mysql
mkdir/var/lib/mysql
2.恢复日志(按顺序恢复：完全备份，第二次增量备份，第三次增量备份，第四次一直往后推的顺序合并)
]#innobackupex --user root--password 123456
--databases="mysql sys performance_schema db5"
--apply-log--redo-only/fullbak
]#innobackupex --user root--password 123456
--databases="mysql sys performance_schema db5"
--apply-log--redo-only/fullbak --incremental-dir="/onedir"
innobackupex --user root--password 123456
--databases="mysql sys performance_schema db5"
--apply-log--redo-only/fullbak --incremental-dir="/twodir"
3.拷贝备份数据到数据库目录下
]#innobackupex --user root--password 123456
--databases="mysql sys performance_schema db5"
--copy-back/fullbak
4.修改数据库目录的所有者所有组
]#chown -R mysql:mysql/var/lib/mysql
5.重启数据库服务
systemctlstopmysqld
6 查看数据
mysql -uroot -p123456 db5
mysql>show tables;
]#innobackupex --user root--password 123456
--databases="mysql sys performance_schema db5"
--incremental/firstdir
--incremental-basedir=/fullbak--no-timestamp
增量备份工作原理？
innobackupex 对innodb存储引擎的能够实现增量备份。
innodb存储引擎有事务日志文件记录sql操作和数据信息
/var/lib/mysql/
ib_logfile0 ##存放sql命令操作
ib_logfile1 ##存放sql命令操作
ibdata1 ##存放数据信息
LSN(序列号) ##ib_logfile0存放的sql命令操作从哪到哪变成序列号从哪到哪（范围）
备份目录下：
xtrabackup_checkpoints (记录LSN) ib_logfile0存放的sql命令操作从哪到哪变成序列号从哪到哪（范围）,用于与上次备份的lsn文件做比较
xtrabackup_logfile (记录SQL)
ibdata1 （数据信息）
数据 在对应库的文件夹里
从完全备份文件中恢复某个表的记录
1.1做完全备份
# innobackupex --user root --password 123456 --databases="db6"/db6allbak--no-timestamp
# ls/db6allbak/db6/
a.frma.ibdb.frmb.ibddb.opt
# ls /var/lib/mysql/db6
a.frma.ibdb.frmb.ibddb.opt
模拟删除：
mysql>drop table db6.a;
2.2 恢复步骤
--export
import
mysql>alter table 库名.表名discard
1导出表信息
innobackupex--userroot--password123456--databases="db6"--apply-log--export/db6allbak##db6allbak为导出表目标目录
]#ls/db6allbak/db6/
a.cfga.expa.frma.ibdb.cfgb.expb.frmb.ibddb.opt
2.建表，删除表空间（表结构必须和删除之前的结构一样）
mysql> create table db6.a(name char(10));
Query OK, 0 rows affected (0.29 sec)
mysql>alter table db6.a discard tablespace; ##删除空表间文件ibd
# ls /var/lib/mysql/db6/
a.frmb.frmb.ibdc.frmc.ibddb.opt
4.备份的表信息文件拷贝到数据库目录下，并修改所有者和组为mysql
]#cp/db6allbak/db6/a.{cfg,exp,ibd}/var/lib/mysql/db6/
]#chownmysql:mysql/var/lib/mysql/db6/a.*
# ls /var/lib/mysql/db6/
a.cfga.expa.frma.ibdb.frmb.ibdc.frmc.ibddb.opt
5.导入表空间
mysql>altertable db6.aimporttablespace;
6.查看表记录
mysql>select * from db6.a
+++++++++++++++++++++++++++++++=
页:
[1]