MySQL第六天（主从同步，数据读写分离）

色婆ijqwj

　　day05
　　一、数据备份与恢复-增量备份和增量恢复
　　a.binlog日志介绍（二进制文件）：使用Mysql数据库服务日志文件类型的一种。记录除查询之外的sql命令。默认没启用
　　查询的sql包括：select  desc 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> delete  from db3.user where>　　[root@db1 logdir]# mysqlbinlog  --start-position=328 --stop-position=1102 plj.000001 | mysql -uroot -p654321
　　g.手动创建新的文件
　　mysql>flush logs;  #刷新一次生成一个 哪个编号大日志往哪个存
　　mysql> system ls /logdir;
　　plj.000001  plj.000002  plj.index
　　[root@db1 logdir]# mysql -uroot -p123456 -e "flush logs"              #命令行直接执行sql命令
　　mysql: [Warning] Using a password on the command line interface can be insecure.
　　[root@db1 logdir]# ls
　　plj.000001  plj.000002  plj.000003  plj.index
　　[root@db1 logdir]# ls
　　plj.000001  plj.000002  plj.000003   plj.index
　　[root@db1 logdir]# systemctl restart mysqld
　　[root@db1 logdir]# ls
　　plj.000001  plj.000002  plj.000003  plj.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.000001  plj.index
　　mysql> purge master logs to "plj.000005";   #删除指定000005编号之前的日志所有index（不包括000005日志文件)
　　Query OK, 0 rows affected (0.14 sec)
　　mysql> system ls /logdir;
　　plj.000005  plj.index
　　f.2 使用系统删除
　　rm -rf  日志文件
　　++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++==
　　6.2 安装第3方软件提供的命令，对数据做增量备份
　　[root@db1 09.mysql]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm

　　警告：libev-4.15-1.el6.rf.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥>　　准备中...                          ################################# [100%]
　　软件包 libev-4.15-1.el6.rf.x86_64 已经安装
　　[root@db1 09.mysql]# 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, 密钥>　　准备中...                          ################################# [100%]
　　软件包 percona-xtrabackup-24-2.4.7-1.el7.x86_64 已经安装
　　[root@db1 09.mysql]# rpm -qa  |grep percona
　　percona-xtrabackup-24-2.4.7-1.el7.x86_64
　　[root@db1 09.mysql]# 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
　　完全备份
　　[root@db1 09.mysql]# innobackupex   --user root --password 123456  --databases="mysql sys performance_schema db5"  /alldir  --no-timestamp  ##/alldir为备份目标目录，且必须为空，如果需要日期目录，不需要--no-timestamp
　　[root@db1 09.mysql]# ls /alldir
　　backup-my.cnf  db5  ib_buffer_pool  ibdata1  mysql  performance_schema  sys  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info  xtrabackup_logfile
　　完全恢复（/var/lib/mysql必须为空）
　　[root@db1 09.mysql]# rm -rf /var/lib/mysql
　　[root@db1 09.mysql]# mkdir /var/lib/mysql
　　[root@db1 09.mysql]# innobackupex   --user root --password 123456  --databases="mysql sys performance_schema db5" --apply-log  /alldir/  ##恢复日志
　　[root@db1 09.mysql]# innobackupex   --user root --password 123456  --databases="mysql sys performance_schema db5" --copy-back  /alldir/  ##恢复数据
　　[root@db1 09.mysql]# ls /var/lib/mysql
　　db5  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  ibtmp1  mysql  performance_schema  sys  xtrabackup_binlog_pos_innodb  xtrabackup_info
　　[root@db1 09.mysql]# chown -R mysql:mysql  /var/lib/mysql
　　[root@db1 09.mysql]# ls -ld /var/lib/mysql
　　drwxr-xr-x. 6 mysql mysql 4096 2月  27 04:24 /var/lib/mysql
　　[root@db1 09.mysql]# systemctl restart mysqld
　　[root@db1 09.mysql]# mysql -uroot -p123456
　　mysql>
　　增量备份：
　　1.有一次完全备份
　　]#innobackupex --user root  --password 123456
　　--databases="mysql sys performance_schema db5"  /fullbak  --no-timestamp
　　insert  into  db5.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
　　insert  into  db5.t1 values(500);
　　select  * from db5.t1;
　　3.第二次增量备份  改变数据后实行增量备份
　　[root@db1 09.mysql]# 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.重启数据库服务
　　systemctl  stop  mysqld
　　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做完全备份
　　[root@db1 ~]# innobackupex --user root --password 123456 --databases="db6"  /db6allbak  --no-timestamp
　　[root@db1 ~]# ls  /db6allbak/db6/
　　a.frm  a.ibd  b.frm  b.ibd  db.opt
　　[root@db1 ~]# ls /var/lib/mysql/db6
　　a.frm  a.ibd  b.frm  b.ibd  db.opt
　　模拟删除：
　　mysql>drop table db6.a;
　　2.2 恢复步骤
　　--export
　　import
　　mysql>alter table 库名.表名  discard
　　1导出表信息
　　innobackupex  --user  root  --password  123456  --databases="db6"  --apply-log  --export  /db6allbak  ##db6allbak为导出表目标目录
　　]#ls  /db6allbak/db6/
　　a.cfg  a.exp  a.frm  a.ibd  b.cfg  b.exp  b.frm  b.ibd  db.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
　　[root@db1 sys]# ls /var/lib/mysql/db6/
　　a.frm  b.frm  b.ibd  c.frm  c.ibd  db.opt
　　4.备份的表信息文件拷贝到数据库目录下，并修改所有者和组为mysql
　　]#cp  /db6allbak/db6/a.{cfg,exp,ibd}  /var/lib/mysql/db6/
　　]#chown  mysql:mysql  /var/lib/mysql/db6/a.*
　　[root@db1 sys]# ls /var/lib/mysql/db6/
　　a.cfg  a.exp  a.frm  a.ibd  b.frm  b.ibd  c.frm  c.ibd  db.opt
　　5.导入表空间
　　mysql>alter  table db6.a  import  tablespace;
　　6.查看表记录
　　mysql>select * from db6.a
　　+++++++++++++++++++++++++++++++=