mysql1

FragranceM

　　分别基于mysqldump、lvm2、xtrabackup三种方式实现MySQL备份恢复
　　一、利用mysqldump实施逻辑备份操作
　　1.在/etc/my.cnf中添加如下，开启二进制日志
　　innodb-file-per-table = ON
　　skip-name-resolve=ON
　　log_bin=/var/log/maradb/binlog
　　2.备份数据，锁表，二进制日志做滚动，CHANGE MASTER TO设置为2让语句被注释
　　[root@zj07 ~]# mysqldump -uroot -hlocalhost zrs --lock-tables --flush-logs --master-data=2 > /tmp/zrs.1.sql
　　查看日志存放位置，出现2，即日志发生滚动
　　[root@zj07 ~]# ls /var/log/mariadb
　　binlog.000001  binlog.000002  binlog.index  mariadb.log
　　添加数据库结构，即显示数据库的创建和使用 --databases
　　[root@zj07 ~]# mysqldump -uroot -hlocalhost --databases zrs --lock-tables --flush-logs --master-data=2 > /tmp/zrs.2.sql
　　备份整个服务器的数据库 --all-databases
　　[root@zj07 ~]# mysqldump -uroot -hlocalhost --all-databases --lock-tables --flush-logs --master-data=2 > /tmp/myserver.1.sql
　　3.模拟故障
　　进程崩溃
　　[root@zj07 ~]# killall mysqld mysqld_safe
　　所有的库文件丢失
　　[root@zj07 ~]# rm -rf /var/lib/mysql/*
　　4.恢复备份
　　先启动服务
　　[root@zj07 ~]# systemctl start mariadb
　　查看已经生成修复的库文件
　　[root@zj07 ~]# ls /var/lib/mysql/
　　aria_log.00000001  ibdata1      ib_logfile1  mysql.sock          test
　　aria_log_control   ib_logfile0  mysql        performance_schema
　　因为恢复的过程，也会产生命令记录，导致数据改变，所以强制不让其记录到二进制
　　[root@zj07 ~]# mysql
　　仅设置当前会话即可
　　MariaDB [(none)]> set @@session.sql_log_bin=OFF;
　　恢复数据
　　MariaDB [(none)]> source /tmp/myserver.1.sql
　　再开启二进制记录
　　MariaDB [zrs]> set @@session.sql_log_bin=ON;
　　5.当恢复数据后，需要删除备份，再重新备份，备份数据仅使用一次。
　　二、利用lvm2实现物理备份操作
　　温备份（几乎热备）
　　1.先创建一个分区
　　[root@zj07 ~]# fdisk /dev/sda
　　命令(输入 m 获取帮助)：n
　　Partition type:
　　p   primary (2 primary, 0 extended, 2 free)
　　e   extended
　　Select (default p):
　　Using default response p
　　分区号 (3,4，默认 3)：
　　起始 扇区 (109684736-167772159，默认为 109684736)：
　　将使用默认值 109684736
　　Last 扇区, +扇区 or +size{K,M,G} (109684736-167772159，默认为 167772159)：+20G
　　分区 3 已设置为 Linux 类型，大小设为 20 GiB
　　命令(输入 m 获取帮助)：t
　　分区号 (1-3，默认 3)：3
　　Hex 代码(输入 L 列出所有代码)：8e
　　已将分区“Linux”的类型更改为“Linux LVM”
　　命令(输入 m 获取帮助)：w
　　partprobe一下
　　[root@zj07 ~]# partprobe /dev/sda
　　查看刚才创建的分区是否存在
　　[root@zj07 ~]# cat /proc/partitions
　　做物理卷
　　[root@zj07 ~]# pvcreate /dev/sda3
　　Physical volume "/dev/sda3" successfully created.
　　做卷组
　　[root@zj07 ~]# vgcreate datavg /dev/sda3
　　Volume group "datavg" successfully created
　　创建逻辑卷，指定名字
　　[root@zj07 ~]# lvcreate -L 8G -n mydata datavg
　　Logical volume "mydata" created.
　　查看
　　[root@zj07 ~]# lvs
　　LV     VG     Attr       LSize  Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert
　　root   centos -wi-ao---- 50.00g
　　swap   centos -wi-ao----  2.00g
　　mydata datavg -wi-a-----  8.00g
　　[root@zj07 ~]# ls /dev/mapper/
　　centos-root  centos-swap  control  datavg-mydata
　　[root@zj07 ~]# ls /dev/datavg/
　　mydata
　　格式化
　　[root@zj07 ~]# mke2fs -b 2048 -t ext4 /dev/datavg/mydata
　　[root@zj07 ~]# blkid /dev/datavg/mydata
　　/dev/datavg/mydata: UUID="95e9edc9-257a-49c6-b44b-235f18af371d" TYPE="ext4"
　　创建目录
　　[root@zj07 ~]# mkdir -pv /data/mydata
　　挂载这个lvm到创建的目录下，在配置文件中，添加如下
　　[root@zj07 ~]# vim /etc/fstab
　　UUID="95e9edc9-257a-49c6-b44b-235f18af371d"   /data/mydata  ext4 defaults,acl  0  0
　　查看
　　[root@zj07 ~]# mount -a
　　[root@zj07 ~]# df
　　文件系统                     1K-块    已用     可用 已用% 挂载点
　　/dev/mapper/centos-root   52403200 4552636 47850564    9% /
　　...
　　...
　　...
　　/dev/mapper/datavg-mydata  8190760   12308  7750830    1% /data/mydata
　　更改该目录的属主属组
　　[root@zj07 ~]# chown -R mysql.mysql /data/*
　　修改配置文件
　　[root@zj07 ~]# vim /etc/my.cnf
　　[mysqld]
　　datadir=/data/mydata
　　socket=/data/mydata/mysql.sock
　　2.模拟故障
　　进程崩溃
　　[root@zj07 ~]# killall mysqld mysqld_safe
　　所有的库文件丢失
　　[root@zj07 ~]# rm -rf /var/lib/mysql/*
　　3.恢复备份
　　先启动服务
　　[root@zj07 ~]# systemctl start mariadb
　　当更改sock文件后，进入mysql会提示错误，可以如下设置配置文件，即可。
　　[root@zj07 ~]# mysql
　　ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
　　[root@zj07 ~]# vim .my.cnf
　　[client]
　　socket='/data/mydata/mysql.sock'
　　关闭二进制日志记录，恢复备份，再开启二进制日志记录。
　　MariaDB [(none)]> set @@session.sql_log_bin=OFF;
　　MariaDB [(none)]> source /tmp/myserver.1.sql;
　　MariaDB [zrs]> set @@session.sql_log_bin=ON;
　　为了测试，先创建一个表，插入数据
　　MariaDB [zrs]> create table if not exists tbl5 (UID tinyint unsigned not null auto_increment primary key,Username varchar(100),Age tinyint unsigned);
　　MariaDB [zrs]> insert into tbl5 (Username,Age) values ('Zhang san',30),('Li si',27);
　　给表加一个读锁
　　MariaDB [zrs]> flush tables with read lock;
　　创建快照卷
　　[root@zj07 ~]# lvcreate -L 5G -s -p r -n data_backup /dev/datavg/mydata
　　Using default stripesize 64.00 KiB.
　　Logical volume "data_backup" created.
　　立马释放锁
　　MariaDB [zrs]> unlock tables;
　　创建目录
　　[root@zj07 ~]# mkdir -pv /mnt/backup
　　挂载
　　[root@zj07 ~]# mount /dev/datavg/data_backup /mnt/backup
　　查看
　　[root@zj07 ~]# ls /mnt/backup/
　　aria_log.00000001  ibdata1      ib_logfile1  mysql.sock          test
　　aria_log_control   ib_logfile0  mysql        performance_schema  zrs
　　创建备份目录
　　[root@zj07 ~]# mkdir /tmp/backup
　　将文件全部备份到该目录
　　[root@zj07 ~]# cd /mnt/backup
　　[root@zj07 backup]# cp -a * /tmp/backup/
　　删除不需要的文件
　　[root@zj07 backup]# rm -f /tmp/backup/mysql.sock
　　记录下面的二进制数值，以备之后恢复数据
　　MariaDB [zrs]> show master status;
　　+---------------+----------+--------------+------------------+
　　| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
　　+---------------+----------+--------------+------------------+
　　| binlog.000012 |    759 |         |            |
　　+---------------+----------+--------------+------------------+
　　4.模拟故障
　　[root@zj07 ~]# killall mysqld mysqld_safe
　　挂载并删除快照卷
　　[root@zj07 ~]# umount /mnt/backup/
　　[root@zj07 ~]# lvremove  /data/datavg/data_backup
　　删除目录下的文件
　　[root@zj07 ~]# rm -rf /data/mydata/*
　　5.下面开始进行物理备份还原
　　[root@zj07 ~]# cd /tmp/backup/
　　[root@zj07 backup]# cp -a * /data/mydata/
　　启动服务
　　[root@zj07 backup]# systemctl start mariadb
　　[root@zj07 backup]# mysql
　　MariaDB [(none)]> use zrs;
　　MariaDB [zrs]> select * from tbl5;
　　+-----+-----------+------+
　　| UID | Username | Age  |
　　+-----+-----------+------+
　　|  1 | Zhang san|   30 |
　　|  2 | Li si   |   27 |
　　+-----+-----------+------+
　　发现刚才新建的数据并没有恢复
　　利用二进制日志恢复
　　[root@zj07 ~]# mysqlbinlog --start-position=759 /var/log/mariadb/binlog.000012 > /tmp/lvm.sql
　　关闭二进制记录功能
　　MariaDB [zrs]> set @@session.sql_log_bin=OFF;
　　利用二进制日志恢复数据
　　MariaDB [zrs]> . /tmp/lvm.sql
　　开启二进制记录功能
　　MariaDB [zrs]> set @@session.sql_log_bin=ON;
　　三、利用xtrabackup实现物理备份操作
　　1.安装软件
　　yum install percona-xtrabackup
　　2.
　　创建备份目录
　　[root@zj07 ~]# mkdir /data/backup
　　更改属组属主
　　[root@zj07 ~]# chown mysql. /data/backup/
　　完全备份
　　[root@zj07 ~]# innobackupex --user=root --host=localhost --socket=/data/mydata/mysql.sock /data/backup/
　　查看，以时间命名创建目录
　　[root@zj07 ~]# ls /data/backup/
　　2018-01-15_16-48-43
　　为了做增量备份，先修改表内容，如下
　　MariaDB [zrs]> insert into tbl5 (username,age) values ('Qi qi',55);
　　MariaDB [zrs]> delete from tbl5 where UID between 1 and 2;
　　做增量备份，指明基于哪一次完全备份
　　[root@zj07 ~]# innobackupex --user=root --socket=/data/mydata/mysql.sock --incremental /data/backup/ --incremental-basedir=/data/backup/2018-01-15_16-48-43
　　查看，已经生成增量备份
　　[root@zj07 ~]# ls /data/backup/
　　2018-01-15_16-48-43  2018-01-15_17-10-47
　　如何区分呢，查看该配置文件
　　[root@zj07 ~]# cat /data/backup/2018-01-15_16-48-43/xtrabackup_checkpoints
　　backup_type = full-backuped
　　from_lsn = 0
　　to_lsn = 1611662
　　last_lsn = 1611662
　　compact = 0
　　recover_binlog_info = 0
　　[root@zj07 ~]# cat /data/backup/2018-01-15_17-10-47/xtrabackup_checkpoints
　　backup_type = incremental
　　from_lsn = 1611662
　　to_lsn = 1613212
　　last_lsn = 1613212
　　compact = 0
　　recover_binlog_info = 0
　　为了再次做增量备份，再次修改表内容，如下
　　MariaDB [zrs]>  create table if not exists tbl7 (UID tinyint unsigned not null auto_increment primary key,Username varchar(100),Age tinyint unsigned);
　　MariaDB [zrs]> insert into tbl7 (Username,Age) values ('name11',98),('name22',99);
　　基于第二次增量备份，再次做增量
　　[root@zj07 ~]# innobackupex --user=root --socket=/data/mydata/mysql.sock --incremental /data/backup/ --incremental-basedir=/data/backup/2018-01-15_17-10-47
　　[root@zj07 ~]# ls /data/backup/
　　2018-01-15_16-48-43  2018-01-15_17-10-47  2018-01-15_17-38-09
　　删除数据
　　MariaDB [zrs]> delete from tbl7 where UID between 1 and 2;
　　注意：如果在最后一个备份结果集之后，还有其他的数据修改操作，还需要依靠在最后一次备份操作之后的二进制日志的内容来实现数据的完整恢复。
　　3.模拟故障
　　[root@zj07 ~]# killall mysqld mysqld_safe
　　[root@zj07 ~]# cd /data
　　[root@zj07 data]# rm -rf mydata/*
　　4.恢复数据
　　准备工作
　　[root@zj07 ~]# innobackupex --apply-log --redo-only /data/backup/2018-01-15_16-48-43/
　　[root@zj07 ~]# innobackupex --apply-log --redo-only /data/backup/2018-01-15_16-48-43/ --incremental-dir=/data/backup/2018-01-15_17-10-47
　　[root@zj07 ~]# innobackupex --apply-log /data/backup/2018-01-15_16-48-43/ --incremental-dir=/data/backup/2018-01-15_17-38-09
　　恢复工作
　　[root@zj07 ~]# innobackupex --copy-back /data/backup/2018-01-15_16-48-43/
　　查看
　　[root@zj07 ~]# ls /data/mydata/
　　ibdata1  performance_schema  xtrabackup_binlog_pos_innodb  zrs
　　mysql    test                xtrabackup_info
　　因为在第二次增量备份后，又删除了两个数据，所以需要借助binlog恢复
　　[root@zj07 ~]# cat /data/backup/2018-01-15_17-38-09/xtrabackup_binlog_info
　　binlog.000013   1123
　　保存
　　[root@zj07 ~]# mysqlbinlog --start-position=1123 /var/log/mariadb/binlog.000013 > /tmp/last.sql
　　修改权限
　　[root@zj07 ~]# chown mysql. -R /data/mydata/*
　　重启服务
　　[root@zj07 data]# systemctl start mariadb
　　查看，该表的内容还在
　　MariaDB [zrs]> select * from tbl7;
　　+-----+----------+------+
　　| UID | Username | Age |
　　+-----+----------+------+
　　|   1 | name11 |  98 |
　　|   2 | name22 |  99 |
　　+-----+----------+------+
　　关闭二进制日志记录功能
　　MariaDB [zrs]> set @@session.sql_log_bin=OFF;
　　恢复
　　MariaDB [zrs]> . /tmp/last.sql
　　开启二进制日志记录功能
　　MariaDB [zrs]> set @@session.sql_log_bin=ON;
　　查看，该表的内容已经没有了
　　MariaDB [zrs]> select * from tbl7;
　　Empty set (0.01 sec)
　　总结：
　　MySQL的备份和恢复：
　　mysqldump：逻辑备份 + 温备|热备 + binlog
　　lvm2：物理备份 + 温备(几乎热备) + binlog
　　Xtrabackup：物理备份 + 温备|热备 + （完全+增量） + binlog