《云计算》-MySql基础：数据备份与恢复
1 数据备份与恢复
1.1 问题
本案例要求熟悉MySQL的备份与恢复，完成以下任务操作：
逻辑备份工具 mysqldump 使用mysql 恢复数据库121.2 步骤
实现此案例需要按照如下步骤进行。
步骤一：使用mysqldump进行逻辑备份
1）备份MySQL服务器上的所有库
将所有的库备份为mysql-all.sql文件：
[root@dbsvr1 ~]# mysqldump -u root -p --all-databases > /root/alldb.sql Enter password: //验证口令 [root@dbsvr1 mysql]# file /root/alldb.sql //确认备份文件类型 /root/alldb.sql: UTF-8 Unicode English text, with very long lines1234查看备份文件alldb.sql的部分内容：
[root@dbsvr1 ~]# grep -vE '^/|^-|^$' /root/alldb.sql | head -15 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `home` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `home`; DROP TABLE IF EXISTS `biao01`; CREATE TABLE `biao01` ( `id` int(2) NOT NULL, `name` varchar(8) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; LOCK TABLES `biao01` WRITE; UNLOCK TABLES; DROP TABLE IF EXISTS `biao02`; CREATE TABLE `biao02` ( `id` int(4) NOT NULL, `name` varchar(8) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; .. ..1234567891011121314151617注意：若数据库都使用MyISAM存储引擎，可以采用冷备份的方式，直接复制对应的数据库目录即可；恢复时重新复制回来就行。
2）只备份指定的某一个库
将userdb库备份为userdb.sql文件：
[root@dbsvr1 ~]# mysqldump -u root -p userdb > userdb.sql Enter password: //验证口令12查看备份文件userdb.sql的部分内容：
[root@dbsvr1 ~]# grep -vE '^/|^-|^$' /root/userdb.sql DROP TABLE IF EXISTS `stu_info`; CREATE TABLE `stu_info` ( `name` varchar(12) NOT NULL, `gender` enum('boy','girl') DEFAULT 'boy', `age` int(3) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; LOCK TABLES `stu_info` WRITE; .. ..1234567893）同时备份指定的多个库
同时备份mysql、userdb库，保存为mysql+userdb.sql文件：
[root@dbsvr1 ~]# mysqldump -u root -p -B mysql userdb > mysql+test+userdb.sql Enter password: //验证口令12查看备份文件userdb.sql的部分内容：
[root@dbsvr1 ~]# grep '^CREATE DATA' /root/mysql+userdb.sql CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */; CREATE DATABASE /*!32312 IF NOT EXISTS*/ `userdb` /*!40100 DEFAULT CHARACTER SET latin1 */;123步骤二：使用mysql命令从备份中恢复数据库、表
以恢复userdb库为例，可参考下列操作。通常不建议直接覆盖旧库，而是采用建立新库并导入逻辑备份的方式执行恢复，待新库正常后即可废弃或删除旧库。
1）创建名为userdb2的新库
mysql> CREATE DATABASE userdb2; Query OK, 1 row affected (0.00 sec)122）导入备份文件，在新库中重建表及数据
[root@dbsvr1 ~]# mysql -u root -p userdb2 < /root/userdb.sql Enter password: //验证口令123）确认新库正常，启用新库
mysql> USE userdb2; //切换到新库 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SELECT sn,username,uid,gid,homedir //查询数据，确认可用 -> FROM userlist LIMIT 10; +----+----------+-----+-----+-----------------+ | sn | username | uid | gid | homedir | +----+----------+-----+-----+-----------------+ | 1 | root | 0 | 0 | /root | | 2 | bin | 1 | 1 | /bin | | 3 | daemon | 2 | 2 | /sbin | | 4 | adm | 3 | 4 | /var/adm | | 5 | lp | 4 | 7 | /var/spool/lpd | | 6 | sync | 5 | 0 | /sbin | | 7 | shutdown | 6 | 0 | /sbin | | 8 | halt | 7 | 0 | /sbin | | 9 | mail | 8 | 12 | /var/spool/mail | | 10 | operator | 11 | 0 | /root | +----+----------+-----+-----+-----------------+ 10 rows in set (0.00 sec)1234567891011121314151617181920214）废弃或删除旧库
mysql> DROP DATABASE userdb; Query OK, 2 rows affected (0.09 sec)122 使用binlog日志
2.1 问题利用binlog恢复库表，要求如下：
启用binlog日志 创建db1库tb1表，插入3条记录 删除tb1表中刚插入的3条记录 使用mysqlbinlog恢复删除的3条记录12342.2 步骤
实现此案例需要按照如下步骤进行。
步骤一：启用binlog日志
1）调整/etc/my.cnf配置，并重启服务
[root@dbsvr1 ~]# vim /etc/my.cnf [mysqld] .. .. log-bin-index=mysql-bin //启用二进制日志，并指定前缀 server_id=1 binlog_format=STATEMENT //在Mysql5.7中，binlog日志格式默认为ROW，但它不记录sql语句上下文相关信息。需要将binlog日志格式修改为STATEMENT .. .. [root@dbsvr1 ~]# systemctl restart mysqld.service1234567892）确认binlog日志文件
新启用binlog后，每次启动MySQl服务都会新生成一份日志文件：
[root@dbsvr1 ~]# ls /var/lib/mysql/mysql-bin.* /var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.index12其中mysql-bin.index文件记录了当前保持的二进制文件列表：
[root@dbsvr1 ~]# cat /var/lib/mysql/mysql-bin.index ./mysql-bin.00000112重启MySQL服务程序，或者执行SQL操作“FLUSH LOGS;”，会生成一份新的日志：
[root@dbsvr1 ~]# ls /var/lib/mysql/mysql-bin.* /var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.index /var/lib/mysql/mysql-bin.000002 [root@dbsvr1 ~]# cat /var/lib/mysql/mysql-bin.index ./mysql-bin.000001 ./mysql-bin.000002123456步骤二：利用binlog日志重做数据库操作
1）执行数据库表添加操作
创建db1·库tb1表，表结构自定义：
mysql> CREATE DATABASE db1; Query OK, 1 row affected (0.05 sec) mysql> USE db1; Database changed mysql> CREATE TABLE tb1( -> id int(4) NOT NULL,name varchar(24) -> ); Query OK, 0 rows affected (0.28 sec)12345678插入3条表记录：
mysql> INSERT INTO tb1 VALUES -> (1,'Jack'), -> (2,'Kenthy'), -> (3,'Bob'); Query OK, 3 rows affected (0.12 sec) Records: 3 Duplicates: 0 Warnings: 0123456确认插入的表记录数据：
mysql> SELECT * FROM tb1; +----+--------+ | id | name | +----+--------+ | 1 | Jack | | 2 | Kenthy | | 3 | Bob | +----+--------+ 3 rows in set (0.00 sec)1234567892）删除前一步添加的3条表记录
执行删除所有表记录操作：
mysql> DELETE FROM tb1; Query OK, 3 rows affected (0.09 sec)12确认删除结果：
mysql> SELECT * FROM tb1; Empty set (0.00 sec)12步骤三：通过binlog日志恢复表记录
binlog会记录所有的数据库、表更改操作，所以可在必要的时候重新执行以前做过的一部分数据操作，但对于启用binlog之前已经存在的库、表数据将不适用。
根据上述“恢复被删除的3条表记录”的需求，应通过mysqlbinlog工具查看相关日志文件，找到删除这些表记录的时间点，只要恢复此前的SQL操作（主要是插入那3条记录的操作）即可。
1）查看mysql-bin.000002日志内容
[root@dbsvr1 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000002 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #170412 12:05:32 server id 1 end_log_pos 123 CRC32 0x6d8c069c Start: binlog v 4, server v 5.7.17-log created 170412 12:05:32 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' jKftWA8BAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAACMp+1YEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AZwGjG0= '/*!*/; # at 123 #170412 12:05:32 server id 1 end_log_pos 154 CRC32 0x17f50164 Previous-GTIDs # [empty] # at 154 #170412 12:05:59 server id 1 end_log_pos 219 CRC32 0x4ba5a976 Anonymous_GTID last_committed=0 sequence_number=1 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #170412 12:05:59 server id 1 end_log_pos 310 CRC32 0x5b66ae13 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1491969959/*!*/; SET @@session.pseudo_thread_id=3/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; CREATE DATABASE db1 /*!*/; # at 310 #170412 12:06:23 server id 1 end_log_pos 375 CRC32 0x2967cc28 Anonymous_GTID last_committed=1 sequence_number=2 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 375 #170412 12:06:23 server id 1 end_log_pos 502 CRC32 0x5de09aae Query thread_id=3 exec_time=0 error_code=0 use `db1`/*!*/; SET TIMESTAMP=1491969983/*!*/; CREATE TABLE tb1( id int(4) NOT NULL,name varchar(24) ) /*!*/; # at 502 #170412 12:06:55 server id 1 end_log_pos 567 CRC32 0x0b8cd418 Anonymous_GTID last_committed=2 sequence_number=3 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 567 #170412 12:06:55 server id 1 end_log_pos 644 CRC32 0x7e8f2fa0 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1491970015/*!*/; BEGIN /*!*/; # at 644 #170412 12:06:55 server id 1 end_log_pos 772 CRC32 0x4e3f728e Query thread_id=3 exec_time=0 error_code=0 //插入表记录的起始时间点 SET TIMESTAMP=1491970015/*!*/; INSERT INTO tb1 VALUES(1,'Jack'),(2,'Kenthy'), (3,'Bob') /*!*/; # at 772 #170412 12:06:55 server id 1 end_log_pos 803 CRC32 0x6138b21f Xid = 10 //确认事务的时间点 COMMIT/*!*/; # at 803 #170412 12:07:24 server id 1 end_log_pos 868 CRC32 0xbef3f472 Anonymous_GTID last_committed=3 sequence_number=4 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 868 #170412 12:07:24 server id 1 end_log_pos 945 CRC32 0x5684e92c Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1491970044/*!*/; BEGIN /*!*/; # at 945 #170412 12:07:24 server id 1 end_log_pos 1032 CRC32 0x4c1c75fc Query thread_id=3 exec_time=0 error_code=0 //删除表记录的时间点 SET TIMESTAMP=1491970044/*!*/; DELETE FROM tb1 /*!*/; # at 1032 #170412 12:07:24 server id 1 end_log_pos 1063 CRC32 0xccf549b2 Xid = 12 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980812） 执行指定Pos节点范围内的sql命令恢复数据
根据上述日志分析，只要恢复从2014.01.12 20:12:14到2014.01.12 20:13:50之间的操作即可。可通过mysqlbinlog指定时间范围输出，结合管道交给msyql命令执行导入重做：
[root@dbsvr1 ~]# mysqlbinlog \ --start-datetime="2017-04-12 12:06:55" \ --stop-datetime="2017-04-12 12:07:23" \ /var/lib/mysql/mysql-bin.000002 | mysql -u root -p Enter password: //验证口令123453）确认恢复结果
mysql> SELECT * FROM db1.tb1; +----+--------+ | id | name | +----+--------+ | 1 | Jack | | 2 | Kenthy | | 3 | Bob | +----+--------+ 3 rows in set (0.00 sec)123456789
作者：Wang cheng zhi
