MySQL备份恢复
一、介绍
备份时候需要考虑的有：备份周期，备份策略，备份工具；
1.1 检查备份可用性
crontab -l 查看备份任务；
查看备份脚本；
查看备份路径；
查看备份日志，检查备份文件；
1.2定期备份演练
1.3数据库迁移升级
二、Mysqldump备份
2.1 mysqldump参数
连接数据库参数 -u -p -S -h P
基础备份参数： -A 备份所有的库与表 -B 可以指定多个库 -R (–routines:导出存储过程以及自定义函数) –triggers (默认导出触发器，使用–skip-triggers屏蔽导出) --events, -E导出事件。
全备：
[root@#localhost ~]# mysqldump -uroot -p -A >/tmp/fullback.sql
备份指定的数据库
[root@#localhost ~]# mysqldump -uroot -p -B school care2 > /tmp/back.sql
备份库下的表
[root@#localhost ~]# mysqldump -uroot -p school student > /tmp/table.sql
核心备份参数：
--master-data=2
(1)记录备份时刻的binlog信息（以注释的形式记录二进制的信息）；
（2）自动锁表（单独使用该参数都要进行锁表备份）
不加--single-transaction,温备份
加 --single-transaction,对于Innodb的表，不锁表备份，非Innodb的表，锁表备份
--single-transaction
对于Innodb的表进行一致性快照备份，不锁表。
2.2故障恢复演练
模拟mysqldump全备+binlog恢复删除的数据库；
创建实验数据库：
mysql> select @@sql_log_bin; +---------------+ | @@sql_log_bin | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec) mysql> create database yu charset utf8mb4; Query OK, 1 row affected (0.06 sec)
创建实验表插入数据：
mysql> create table t1(id int); Query OK, 0 rows affected (0.03 sec) mysql> insert into t1 values(1); Query OK, 1 row affected (0.08 sec) mysql> insert into t1 values(2); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values(3); Query OK, 1 row affected (0.04 sec) mysql> insert into t1 values(4); Query OK, 1 row affected (0.03 sec) mysql> insert into t1 values(5); Query OK, 1 row affected (0.02 sec)
备份数据：
[root@#localhost tmp]# mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers > /tmp/full2.sql
备份后继续插入数据：
mysql> insert into t1 values(22);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values(23);
Query OK, 1 row affected (0.02 sec)
mysql> update t1 set id=100 where id=2;
Query OK, 1 row affected (0.03 sec)
查看备份中记录备份结束后事务开始的位置：
-- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=1766;
导出从备份后开始的binlog
[root@#localhost data]# mysqlbinlog --start-position=1766 --stop-position=2488 mysql-bin.000007>/tmp/full3.sql
找其他的一个数据库恢复测试：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.04 sec) mysql> source /tmp/full2.sql;
mysql> source /tmp/full3.sql
全备+binlog恢复完成！
三、物理备份 XtraBackup
3.1安装XtraBackup
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm yum -y install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
3.2备份过程
1.针对非Innodb表，进行锁表备份，copy所有非Innodb表文件；
2.针对Innodb表，立即触发CKPT，立即记录一个LSN，copy所有innodb表相关的文件（ibdata1，ibd，frm），并且将备份过程中产生的新数据变化的部分redo一起备份走，并记录最新的LSN；
恢复过程：
在恢复时候，xdb会调用Innodb引擎的csr过程，将数据和redo的LSN追平，
3.3备份语句
1、给数据库来一次全备：
innobackupex --user=root --password=Password@123 --host=192.168.43.4 --p=3306 --no-timestamp /backup/full
2、新增数据，准备增量备份：
mysql> show tables; +--------------+ | Tables_in_yu | +--------------+ | t1 | +--------------+ 1 row in set (0.00 sec) mysql> create table t2(id int); Query OK, 0 rows affected (0.04 sec) mysql> insert into t2 values(2),(3),(4); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0
3、进行一次增量备份：
innobackupex --user=root --password=Password@123 --host=192.168.43.4 --p=3306 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/incr1
4、查看增量和全备的信息：
[root@#localhost full]# cat xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 2875667 last_lsn = 2875676 compact = 0 recover_binlog_info = 0 [root@#localhost full]# cd .. [root@#localhost backup]# cd incr1/ [root@#localhost incr1]# cat xtrabackup_checkpoints backup_type = incremental from_lsn = 2875667 to_lsn = 2881475 last_lsn = 2881484 compact = 0 recover_binlog_info = 0
全备的 last_lsn=2875676-9=287567 这个是增量的from_lsn
5、继续模拟数据变化，准备下一次增量
mysql> create table t3(id int); Query OK, 0 rows affected (0.07 sec) mysql> insert into t3 values(3),(4),(5); Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0
6、在来一次增量备份
innobackupex --user=root --password=Password@123 --host=192.168.43.4 --p=3306 --no-timestamp --incremental --incremental-basedir=/backup/incr1 /backup/incr2
7、增量备份后在添加一点数据
mysql> create table t4(id int); Query OK, 0 rows affected (0.09 sec)
8、在这个步骤后发生数据丢失，现在模拟全备+增量1+增量2+binlog模式恢复所有数据
[root@#localhost mysql]# mv data data.bak
9、恢复前的准备
整理full备份
[root@#localhost ~]# innobackupex --apply-log --redo-only /backup/full
合并第一次增量到全备中
[root@#localhost ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/incr1 /backup/full
合并第二次增量到全备中，这次不用加redo-only
[root@#localhost ~]# innobackupex --apply-log --incremental-dir=/backup/incr2 /backup/full
观察日志：
最后一次整理全备：
[root@#localhost ~]# innobackupex --apply-log /backup/full
查看第二次增量的binlog信息，查看增量备份后，日志的起始日志位置，假设binlog没有丢失，截取增量后的binlog日志，进行恢复
[root@#localhost incr2]# cat xtrabackup_info uuid = 1063e108-0a69-11ed-91a1-000c29d9caa0 name = tool_name = innobackupex tool_command = --user=root --password=... --host=192.168.43.4 --p=3306 --no-timestamp --incremental --incremental-basedir=/backup/incr1 /backup/incr2 tool_version = 2.4.12 ibbackup_version = 2.4.12 server_version = 5.7.16-log start_time = 2022-07-23 17:23:01 end_time = 2022-07-23 17:23:08 lock_time = 0 binlog_pos = filename 'mysql-bin.000007', position '3851' innodb_from_lsn = 2881475 innodb_to_lsn = 2887300 partial = N incremental = Y format = file compact = N compressed = N encrypted = N
截取最后的binlog日志
[root@#localhost data.bak]# mysqlbinlog --start-position=3851 --stop-position=4009 mysql-bin.000007>/backup/full4.sql
恢复备份数据
[root@#localhost mysql]# cp -a /backup/full/* /data/mysql/data/ [root@#localhost mysql]# chown -R mysql. data [root@#localhost mysql]# systemctl restart mysqld
恢复binlog日志
mysql> set sql_log_bin=0;
mysql> source /backup/full4.sql
查看数据完全恢复
mysql> show tables; +--------------+ | Tables_in_yu | +--------------+ | t1 | | t2 | | t3 | | t4 | +--------------+ 4 rows in set (0.00 sec)
mysql> set sql_log_bin=1;
三、mysqldump备份数据库-原来探究
在my.cnf中加入mysqldump的参数
参数
基础备份参数： -A 备份所有的库与表 -B 可以指定多个库 -R (–routines:导出存储过程以及自定义函数) –triggers (默认导出触发器，使用–skip-triggers屏蔽导出) --events, -E导出事件。
--master-data=2
(1)记录备份时刻的binlog信息（以注释的形式记录二进制的信息）；
（2）自动锁表（单独使用该参数都要进行锁表备份）
不加--single-transaction,温备份
加 --single-transaction,对于Innodb的表，不锁表备份，非Innodb的表，锁表备份
--single-transaction
对于Innodb的表进行一致性快照备份，不锁表。
[root@mysql ~]# mysqldump -uroot -p --single-transaction -B db1 > db01.bak
Enter password:
3.1、使用generallog查看原理
首先将log_output设置为table
(root@localhost) [mysql]> set global log_output='table'; Query OK, 0 rows affected (0.00 sec)
将general_log设置为1，记录所有操作
(root@localhost) [mysql]> set global general_log=1; Query OK, 0 rows affected (0.00 sec)
接下来我们做一个备份
[root@mysql ~]# mysqldump -uroot -p --single-transaction -B db1 > db01.bak
Enter password:
先关闭日志
(root@localhost) [mysql]> set global general_log=0; Query OK, 0 rows affected (0.00 sec)
查看general_log记录了很多日志，找出他的threadid
(root@localhost) [mysql]> (root@localhost) [mysql]> select * from general_log;
通过查看日志查看备份执行过程
(root@localhost) [mysql]> select thread_id,left(argument,88) from general_log where thread_id=10; +-----------+------------------------------------------------------------------------------------------+ | thread_id | left(argument,88) | +-----------+------------------------------------------------------------------------------------------+ | 10 | root@localhost on using Socket | | 10 | /*!40100 SET @@SQL_MODE='' */ | | 10 | /*!40103 SET TIME_ZONE='+00:00' */ | | 10 | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ | | 10 | START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ | | 10 | SHOW VARIABLES LIKE 'gtid\_mode' | | 10 | UNLOCK TABLES | | 10 | SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM IN | | 10 | SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZ | | 10 | SHOW VARIABLES LIKE 'ndbinfo\_version' | | 10 | db1 | | 10 | SHOW CREATE DATABASE IF NOT EXISTS `db1` | | 10 | SAVEPOINT sp | | 10 | show tables | | 10 | show table status like 'aa' | | 10 | SET SQL_QUOTE_SHOW_CREATE=1 | | 10 | SET SESSION character_set_results = 'binary' | | 10 | show create table `aa` | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | show fields from `aa` | | 10 | show fields from `aa` | | 10 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `aa` | | 10 | SET SESSION character_set_results = 'binary' | | 10 | use `db1` | | 10 | select @@collation_database | | 10 | SHOW TRIGGERS LIKE 'aa' | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | ROLLBACK TO SAVEPOINT sp | | 10 | show table status like 'ab' | | 10 | SET SQL_QUOTE_SHOW_CREATE=1 | | 10 | SET SESSION character_set_results = 'binary' | | 10 | show create table `ab` | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | show fields from `ab` | | 10 | show fields from `ab` | | 10 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `ab` | | 10 | SET SESSION character_set_results = 'binary' | | 10 | use `db1` | | 10 | select @@collation_database | | 10 | SHOW TRIGGERS LIKE 'ab' | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | ROLLBACK TO SAVEPOINT sp | | 10 | show table status like 'bc' | | 10 | SET SQL_QUOTE_SHOW_CREATE=1 | | 10 | SET SESSION character_set_results = 'binary' | | 10 | show create table `bc` | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | show fields from `bc` | | 10 | show fields from `bc` | | 10 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `bc` | | 10 | SET SESSION character_set_results = 'binary' | | 10 | use `db1` | | 10 | select @@collation_database | | 10 | SHOW TRIGGERS LIKE 'bc' | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | ROLLBACK TO SAVEPOINT sp | | 10 | show table status like 'cc' | | 10 | SET SQL_QUOTE_SHOW_CREATE=1 | | 10 | SET SESSION character_set_results = 'binary' | | 10 | show create table `cc` | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | show fields from `cc` | | 10 | show fields from `cc` | | 10 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `cc` | | 10 | SET SESSION character_set_results = 'binary' | | 10 | use `db1` | | 10 | select @@collation_database | | 10 | SHOW TRIGGERS LIKE 'cc' | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | ROLLBACK TO SAVEPOINT sp | | 10 | show table status like 'd' | | 10 | SET SQL_QUOTE_SHOW_CREATE=1 | | 10 | SET SESSION character_set_results = 'binary' | | 10 | show create table `d` | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | show fields from `d` | | 10 | show fields from `d` | | 10 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `d` | | 10 | SET SESSION character_set_results = 'binary' | | 10 | use `db1` | | 10 | select @@collation_database | | 10 | SHOW TRIGGERS LIKE 'd' | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | ROLLBACK TO SAVEPOINT sp | | 10 | show table status like 'da' | | 10 | SET SQL_QUOTE_SHOW_CREATE=1 | | 10 | SET SESSION character_set_results = 'binary' | | 10 | show create table `da` | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | show fields from `da` | | 10 | show fields from `da` | | 10 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `da` | | 10 | SET SESSION character_set_results = 'binary' | | 10 | use `db1` | | 10 | select @@collation_database | | 10 | SHOW TRIGGERS LIKE 'da' | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | ROLLBACK TO SAVEPOINT sp | | 10 | show table status like 'de' | | 10 | SET SQL_QUOTE_SHOW_CREATE=1 | | 10 | SET SESSION character_set_results = 'binary' | | 10 | show create table `de` | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | show fields from `de` | | 10 | show fields from `de` | | 10 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `de` | | 10 | SET SESSION character_set_results = 'binary' | | 10 | use `db1` | | 10 | select @@collation_database | | 10 | SHOW TRIGGERS LIKE 'de' | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | ROLLBACK TO SAVEPOINT sp | | 10 | show table status like 'f' | | 10 | SET SQL_QUOTE_SHOW_CREATE=1 | | 10 | SET SESSION character_set_results = 'binary' | | 10 | show create table `f` | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | show fields from `f` | | 10 | show fields from `f` | | 10 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `f` | | 10 | SET SESSION character_set_results = 'binary' | | 10 | use `db1` | | 10 | select @@collation_database | | 10 | SHOW TRIGGERS LIKE 'f' | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | ROLLBACK TO SAVEPOINT sp | | 10 | show table status like 'passd' | | 10 | SET SQL_QUOTE_SHOW_CREATE=1 | | 10 | SET SESSION character_set_results = 'binary' | | 10 | show create table `passd` | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | show fields from `passd` | | 10 | show fields from `passd` | | 10 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `passd` | | 10 | SET SESSION character_set_results = 'binary' | | 10 | use `db1` | | 10 | select @@collation_database | | 10 | SHOW TRIGGERS LIKE 'passd' | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | ROLLBACK TO SAVEPOINT sp | | 10 | show table status like 'stu' | | 10 | SET SQL_QUOTE_SHOW_CREATE=1 | | 10 | SET SESSION character_set_results = 'binary' | | 10 | show create table `stu` | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | show fields from `stu` | | 10 | show fields from `stu` | | 10 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `stu` | | 10 | SET SESSION character_set_results = 'binary' | | 10 | use `db1` | | 10 | select @@collation_database | | 10 | SHOW TRIGGERS LIKE 'stu' | | 10 | SHOW CREATE TRIGGER `trg_upd_score` | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | ROLLBACK TO SAVEPOINT sp | | 10 | show table status like 't6' | | 10 | SET SQL_QUOTE_SHOW_CREATE=1 | | 10 | SET SESSION character_set_results = 'binary' | | 10 | show create table `t6` | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | show fields from `t6` | | 10 | show fields from `t6` | | 10 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `t6` | | 10 | SET SESSION character_set_results = 'binary' | | 10 | use `db1` | | 10 | select @@collation_database | | 10 | SHOW TRIGGERS LIKE 't6' | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | ROLLBACK TO SAVEPOINT sp | | 10 | show table status like 'tb\_article' | | 10 | SET SQL_QUOTE_SHOW_CREATE=1 | | 10 | SET SESSION character_set_results = 'binary' | | 10 | show create table `tb_article` | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | show fields from `tb_article` | | 10 | show fields from `tb_article` | | 10 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb_article` | | 10 | SET SESSION character_set_results = 'binary' | | 10 | use `db1` | | 10 | select @@collation_database | | 10 | SHOW TRIGGERS LIKE 'tb\_article' | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | ROLLBACK TO SAVEPOINT sp | | 10 | show table status like 'tb\_article2' | | 10 | SET SQL_QUOTE_SHOW_CREATE=1 | | 10 | SET SESSION character_set_results = 'binary' | | 10 | show create table `tb_article2` | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | show fields from `tb_article2` | | 10 | show fields from `tb_article2` | | 10 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb_article2` | | 10 | SET SESSION character_set_results = 'binary' | | 10 | use `db1` | | 10 | select @@collation_database | | 10 | SHOW TRIGGERS LIKE 'tb\_article2' | | 10 | SET SESSION character_set_results = 'utf8' | | 10 | ROLLBACK TO SAVEPOINT sp | | 10 | RELEASE SAVEPOINT sp | | 10 | | +-----------+------------------------------------------------------------------------------------------+ 199 rows in set (0.01 sec)
从日志中可以看出，备份的数据是任务开始时候的数据，比如备份执行了30分钟，期间有数据插入，插入的数据不备份，
| 10 | START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ |
