MySQL系列之十二 备份与恢复

用法：
           shell> mysqldump [options] db_name [tbl_name ...]
           shell> mysqldump [options] --databases db_name ...
           shell> mysqldump [options] --all-databases

选项：
	-A：备份所有库
	-B db_name1,[db_name2,...]：备份指定库
	-E：备份相关的所有event scheduler
	-R：备份所有存储过程和存储函数
	--triggers：备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
	--master-data={1|2}：
		 1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定默认为1
		 2：记录为注释的CHANGE MASTER TO语句，注意：此选项会自动关闭--lock-tables功能，自动打开--lock-all-tables功能（除非开启--single-transaction）
	-F：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A时，会导致刷新多次数据库，在同一时刻执行转储和日志刷新，则应同时使用--flush-logs和-x，--master-data或-single-transaction,此时只刷新一次；建议：和-x，--master-data或 --single-transaction一起使用
	--compact 去掉注释，适合调试，生产不使用
	-d：只备份表结构
	-t：只备份数据，不备份create table
	-n：不备份create database，可被-A或-B覆盖
	--flush-privileges：备份前刷新授权表，备份mysql库或相关时需要使用
	-f：忽略SQL错误，继续执行
	--hex-blob：使用十六进制符号转储二进制列（例如，“abc”变为0x616263），受影响的数据类型包括BINARY， VARBINARY，BLOB，BIT
	-q：不缓存查询，直接输出，加快备份速度

InnoDB建议备份策略：
	mysqldump –uroot –A –F –E –R  --single-transaction --master-data=1 --flush-privileges  --triggers --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

MyISAM建议备份策略：
	mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges  --triggers --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

用法：
	innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/

选项：
    --user：该选项表示备份账号
    --password：该选项表示备份的密码
    --host：该选项表示备份数据库的地址
    --databases：该选项接受的参数为数据名，如果要指定多个数据库，彼此间需要以空格隔开；如："xtra_test dba_test"，同时，在指定某数据库时，也可以只指定其中的某张表。如："mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表
    --defaults-file：该选项指定从哪个文件读取MySQL配置，必须放在命令行第一个选项位置
    --incremental：该选项表示创建一个增量备份，需要指定--incremental-basedir
    --incremental-basedir：该选项指定为前一次全备份或增量备份的目录，与--incremental同时使用
    --incremental-dir：该选项表示还原时增量备份的目录
    --include=name：指定表名，格式：databasename.tablename
    --apply-log：一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。此选项作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
	--use-memory：该选项表示和--apply-log选项一起使用，prepare 备份的时候，xtrabackup做crash recovery分配的内存大小，单位字节。也可(1MB,1M,1G,1GB)，推荐1G
	--export：表示开启可导出单独的表之后再导入其他Mysql中
	--redo-only：此选项在prepare base full backup，往其中merge增量备份时候使用
	--copy-back：做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
	--move-back：这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这个选项移除backup文件，用时候必须小心。使用场景：没有足够的磁盘空间同事保留数据文件和Backup副本

~]# mkdir /backup
~]# systemctl stop mariadb #停止服务
~]# tar Jcf /backup/mariadb_all.tar.xz /var/lib/mysql/ #打包压缩
backup]# systemctl start mariadb

~]# systemctl stop mariadb
~]# rm /var/lib/mysql/ -rf  #将损坏的库删除
~]# cd /backup/
backup]# tar xf mariadb_all.tar.xz  #解压打包的数据库文件
backup]# cp -av var/lib/mysql/ /var/lib/ #还原
backup]# systemctl start mariadb #启动服务，恢复成功

~]# systemctl stop mariadb
~]# rm /var/lib/mysql/ -rf  #将损坏的库删除
~]# cd /backup/
backup]# tar xf mariadb_all.tar.xz  #解压打包的数据库文件
backup]# cp -av var/lib/mysql/ /var/lib/ #还原
backup]# systemctl start mariadb #启动服务，恢复成功

准备lvm环境：
~]# pvcreate /dev/sda5
~]# vgcreate vg0 /dev/sda5
~]# lvcreate -n lv_data -L 10G vg0
~]# lvcreate -n lv_binlog -L 10G vg0
~]# mkfs.xfs /dev/vg0/lv_data
~]# mkfs.xfs /dev/vg0/lv_binlog
~]# mkdir -pv /data/{mysqldb,binlog}  #创建数据目录和二进制日志存放目录
~]# chown -R mysql:mysql /data/
~]# vim /etc/fstab
	UUID=4e3d726a-d420-4c1e-812b-da315012ba86 /data/mysqldb xfs defaults 0 0
	UUID=6dd98866-769f-4369-8738-291fbcc94ca1 /data/binlog xfs defaults 0 0 

配置数据库，模拟生成大量数据：
~]# yum install mariadb-server -y
~]# vim /etc/my.cnf
    [mysqld]
    datadir = /data/mysqldb  #指定数据库存放路径
    log_bin = /data/binlog/mariadb-bin  #开启二进制日志记录，并且存放到指定路径
    innodb_file_per_table = ON  #开启每个表单独的表空间
~]# systemctl start mariadb
~]# mysql  #连接数据库，这里省略了用户名和密码，以下都是如此
MariaDB [(none)]> CREATE DATABASE school;  #创建一个测试的库
MariaDB [(none)]> use school
MariaDB [school]> CREATE TABLE testtb (id int auto_increment primary key,name char(30),age int default 20);  #创建一张数据表
MariaDB [school]> DELIMITER //  #修改语句结束符为“//”
MariaDB [school]> CREATE PROCEDURE pro_testtb()  #写一个存储过程，目的是生成十万条记录测试用
    -> BEGIN
    -> declare i int;
    -> set i = 1;
    -> while i < 100000
    -> do INSERT INTO testtb(name,age) VALUES (CONCAT('testuser',i),i);
    -> SET i = i + 1;
    -> END while;
    -> END//
MariaDB [school]> DELIMITER ;  #记得将语句结束符再改回来
MariaDB [school]> CALL pro_testtb;  #调用存储过程来
MariaDB [school]> SELECT COUNT(*) FROM testtb;  #查看一下表中有十万条记录
+----------+
| COUNT(*) |
+----------+
|    99999 |
+----------+

开始备份：
MariaDB [school]> FLUSH TABLES WITH READ LOCK;  #备份前切记锁表，防止用户继续写入
MariaDB [school]> FLUSH LOGS;  #滚动一下二进制日志
MariaDB [school]> SHOW MASTER LOGS;  #查看二进制日志的位置
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |     30334 |
| mariadb-bin.000002 |   1038814 |
| mariadb-bin.000003 |  29178309 |
| mariadb-bin.000004 |       528 |
| mariadb-bin.000005 |       245 |  #将此出记录下来，我们后边需要用到
+--------------------+-----------+
~]# lvcreate -L 5G -n lv_mysql_snap -s -p r /dev/vg0/lv_data  #需要再开一个终端创建快照，不要退出mysql终端
MariaDB [school]> UNLOCK TABLES;  #创建快照后第一时间解锁，小心用户投诉
~]# mount -o nouuid,norecovery /dev/vg0/lv_mysql_snap /mnt/  #将快照挂载到/mnt
~]# cp -av /mnt/ /backup  #拷贝数据到备份目录
~]# umount /mnt/
~]# lvremove /dev/vg0/lv_mysql_snap  #拷贝完成后即时删除快照，影响服务器性能，到此完全备份完成~

再加点数据：
MariaDB [school]> CALL pro_testtb;  #让我们模拟再来插入十万条数据
MariaDB [school]> SELECT COUNT(*) FROM testtb;
+----------+
| COUNT(*) |
+----------+
|   199998 |  #现在是二十万条记录数据了
+----------+

模拟数据库损坏：
~]# rm -rf /data/mysqldb"这行

导入备份：
~]# rm -rf /data/mysqldb" 删除，撤销误删除操作
MariaDB [(none)]> SET sql_log_bin=0;  #先临时关闭二进制日记记录功能
MariaDB [(none)]> source /backup/binlog.sql  #导入增量备份之后的最新数据
查看确认一下数据有没有恢复完整，把my.cnf中的skip_networking删除，重启服务
到此已经恢复到了最新的状态~

~]# innobackupex --include="testdb.testlog" /backup  #备份表数据
~]# mysql -e 'SHOW CREATE TABLE testdb.testlog' > /backup/desc_testdb_testlog.sql  #备份表空间
~]# mysql -e 'DROP TABLE testdb.testlog'  #模拟故障，删除testlog表

~]# innobackupex --apply-log --export /backup/2018-06-14_17-47-02/  #整理表数据
~]# vim /backup/desc_testdb_testlog.sql  #编辑创建表空间的语句，删除以下字段
    Table   Create Table
    testlog
~]# mysql testdb < /backup/desc_testdb_testlog.sql  #导入表空间
~]# mysql testdb -e 'DESC testlog'  #查看是否导入成功
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(30) | YES  |     | NULL    |                |
| age   | int(11)  | YES  |     | 20      |                |
+-------+----------+------+-----+---------+----------------+
~]# mysql -e 'ALTER TABLE testdb.testlog DISCARD TABLESPACE'  #清除表空间
~]# cd /backup/2018-06-14_17-47-02/testdb/
testdb]# cp testlog.cfg testlog.exp testlog.ibd /var/lib/mysql/testdb/  #将表数据复制到库目录
~]# chown -R mysql:mysql /var/lib/mysql/testdb/  #修改所属者和所属组
~]# mysql -e 'ALTER TABLE testdb.testlog IMPORT TABLESPACE'  #导入表空间