MySQL backup copy-2

About MySQL-1
This article describes backup recovery, master-Slave,

4. mysql Backup recovery

MySQL backup is divided into cold and hot spare, using cold standby need to shut down the server, in production is not recommended to do so, backup is also known as logical backup and RAW file backup, back-up files can be divided into full standby and incremental backup, logical backup is not particularly ideal for incremental backup.

Starting from here we prepare two machines: System Cenos7.4 mysql:5.7

4.1. Mysqldump Backup operation

1、全备备份： [[email protected] tmp]#  mysqldump -uroot -pxiong123 -A > 20180510.sql恢复： [[email protected] tmp]#  mysql -uroot -pxiong123 < 20180510.sql2、备份单个库备份： [[email protected] tmp]# mysqldump -uroot -p extmail > extmail.20180510.sql     备份文件头部： Host: localhost    Database: extmail    先删除extmail这个库  mysql> drop database extmail;    恢复操作： [[email protected] tmp]# mysql -uroot -pxiong123 extmail < extmail.20180510.sql             删除数据库，直接恢复会报这个错误，我们需要先创建这个库，然后再进行恢复                    ERROR 1049 (42000): Unknown database ‘extmail‘ 完整单库恢复    mysql> create database extmail;    

   

3、备份库中的单个表备份：语法： -u用户 -p密码 库名 表名             [[email protected] tmp]# mysqldump -uroot -p extmail alias > ext_alias.sql恢复：        1、删除  mysql> drop table extmail.alias;        2、恢复 [[email protected] tmp]# mysql -uroot -pxiong123 extmail < ext_alias.sql        3、查看 mysql> use extmail;                      mysql> show tables;

4.2, Xtrbackup

-click I download-version: percona-xtrabackup-24-2.4.11-1.el7.x86_64.rpm

4.2.1, function

能实现的功能：    非阻塞备份innodb等事务引擎数据库、    备份myisam表会阻塞(需要锁)、    支持全备、增量备份、压缩备份、    快速增量备份(xtradb，原理类似于oracle：tracking 上次备份之后发生修改的page.)、    percona支持归档redo log的备份、    percona5.6+支持轻量级的backup-lock替代原来重量级的FTWRL，此时即使备份非事务引擎表也不会阻塞innodb的DML语句了、    支持加密备份、流备份(备份到远程机器)、并行本地备份、并行压缩、并行加密、并行应用备份期间产生的redo日志、并行copy-back    支持部分备份，只备份某个库，某个表    支持部分恢复    支持备份单个表分区    支持备份速度限制，指备份产生的IO速度的限制    支持point-in-time恢复    支持compat备份，也即使不备份索引数据，索引在prepare时--rebuild-indexs    支持备份buffer pool    支持单表export, import到其它库    支持 rsync 来缩短备份非事务引擎表的锁定时间

4.2.1, fully prepared

yum安装  依赖包 libev、rsync        [[email protected] tmp]# yum -y install percona-xtrabackup-24-2.4.11-1.el7.x86_64.rpm前期准备：1、创建一个用户备份的用户         mysql> grant replication client, reload, lock tables, process on *.* to ‘xtrb‘@‘%‘ identified by ‘xtrb‘;         mysql> flush privileges;2、创建一个备份目录 /data/backup                  [[email protected] mysql]# mkdir /data/backup                  [[email protected] mysql]# chown mysql.mysql !$3、创建一个数据库mysql> create database test;mysql> use test;mysql> create table te1 (id int primary key auto_increment,name varchar(25));mysql> insert into te1 (name) values (‘xiong1‘),(‘xddf2‘),(‘sa3‘);

   

备份：1、全备    [[email protected] mysql]#  innobackupex --defaults-file=/etc/my.cnf --user xtrb --password xtrb --host 192.168.9.224 /data/backup/

备份完成之后是直接以当前时间来命名[[email protected] mysql]# ls /data/backup/    2018-05-10_15-54-29以下是备份的文件，需要将属主属组设置成mysql，方便恢复

Document Description Important

Recovery operations
The--apply-log parameter is required for recovery, which is to roll back uncommitted transactions and synchronize the committed transactions to the data file to keep the data files consistent;

[[email protected] 2018-05-10_15-54-29]# innobackupex --defaults-file=/etc/my.cnf --user xtrb --password xtrb --host 192.168.9.224 --apply-log /data/backup/2018-05-10_15-54-29/出现如下信息说明成功

这时我们模拟数据挂掉了，直接干掉原先的mysql1、先停掉它    [[email protected] 2018-05-10_15-54-29]# service mysqld stop2、弄走原先的数据文件    [[email protected] 2018-05-10_15-54-29]# mv /data/mysql{,.bak}3、恢复数据文件    [[email protected] 2018-05-10_15-54-29]# pwd    /data/backup/2018-05-10_15-54-29    [[email protected] 2018-05-10_15-54-29]# mkdir /data/mysql    [[email protected] 2018-05-10_15-54-29]# mv * /data/mysql    [[email protected] 2018-05-10_15-54-29]# chown mysql.mysql /data/mysql -R4、启动服务    [[email protected] 2018-05-10_15-54-29]# service mysqld start5、查看数据库    mysql> show databases;            Database： test   没问题

4.2.2 Incremental Backup

1、先进行一次全备    [[email protected] backup]#  innobackupex --defaults-file=/etc/my.cnf --user xtrb --password xtrb --host 192.168.9.224 /data/backup/2、插入新的数据    mysql> insert into test.te1 (name) values (‘5sf‘),(‘adf‘),(‘555sf‘),(‘3adf‘),(‘5555sf‘),(‘3adadf‘),(‘55ad55sf‘),(‘3adadaf‘);

  

3、增量备份[[email protected] backup]# innobackupex --defaults-file=/etc/my.cnf --user xtrb --password xtrb --host 192.168.9.224 --no-timestamp --incremental /data/backup/2018-05-10_16-23-27-incre-1 --incremental-basedir /data/backup/2018-05-10_16-23-27/

4, the recovery of 4.1, the first to complete the recovery of 4.2, and then the recovery is required to add--redo-only--redo-only means to roll forward only the transactions that have been committed in the Xtrabackup log, do not roll back those transactions that did not commit the information 4.3, the most The latter process is to restore the whole of the complete, then you can remove the--redo-only, which means that you need to roll back the transaction recovery steps that have not yet been committed: 1, fully prepared recovery [[email protected] backup]# Innobackupex --DEFAULTS-FILE=/ETC/MY.CNF--user xtrb--password xtrb--host 192.168.9.224--apply-log--redo-only/data/backup/ 2018-05-10_16-23-27xtrabackup:starting shutdown with Innodb_fast_shutdown = 1innodb:starting shutdown ... Innodb:shutdown completed;  Log sequence number 2597453innodb:number of pools:1180510 16:41:43 completed ok!2, added recovery [[email protected] backup]# Innobackupex--defaults-file=/etc/my.cnf--user xtrb--password xtrb--host 192.168.9.224--apply-log--redo-only/data/ backup/2018-05-10_16-23-27--incremental-dir=/data/backup/2018-05-10_16-23-27-incre-1/180510 16:42:43 [xx] ... done 180510 16:42:43 [xx] copying/data/backup/2018-05-10_16-23-27-incre-1//xtrabackup_info to./xtrabackup_info180510 16:42:43 [xx] ... done180510 16:42: Completed ok!3, whole recovery without--redo-only[[email protected] backup]# Innobackupex--defaults-file=/etc/my.cnf-- User xtrb--password xtrb--host 192.168.9.224--apply-log/data/backup/2018-05-10_16-23-27xtrabackup:using the Following InnoDB configuration for recovery:xtrabackup:innodb_data_home_dir =. Xtrabackup:innodb_data_file_path = IB Data1:12m:autoextendxtrabackup:innodb_log_group_home_dir =. Xtrabackup:innodb_log_files_in_group = 2 2 files Xtraba Ckup:innodb_log_file_size = 50331648 You can see the log file size innodb:fts optimize thread exiting. Innodb:starting shutdown ... Innodb:shutdown completed; Log sequence number 2608421 180510 16:43:51 completed ok!4, test recovery process [[email protected] data]# Cd/data[[email pro Tected] data]# service mysqld stop[[email protected] data]# mv mysql{,.bak}[[email protected] data]# mkdir Mysql[[email protected] data]# mv backup/2018-05-10_16-23-27/* mysql/[[email protected] data]# chown Mysql.mysql MYSQL-R[[EMAIL PROtected] data]# service mysqld start 

MySQL backup copy-2