189 8069 5689
数据库备份概述
创新互联公司是专业的名山网站建设公司，名山接单;提供成都网站设计、网站建设,网页设计,网站设计,建网站,PHP网站建设等专业做网站服务;采用PHP框架,可快速的进行名山网站开发网页制作和功能扩展;专业做搜索引擎喜爱的网站,专业的做网站团队,希望更多企业前来合作!
概述：将数据库中存在的现有数据，进行存放成为副本数据，可解决数据容灾；
提高系统的高可用性和灾难恢复性，数据崩溃时，以最小代价重新恢复数据；
数据丢失的原因：程序错误、人为错误、磁盘错误、天灾人祸
数据库备份的分类
物理备份
指对数据库操作系统的物理文件（数据文件、日志文件）等备份
冷备份
必须在数据库关闭状态下进行备份，能够更好保证数据库的完整性
热备份
能够在数据库处于正常运行的情况下备份，能够更高的保证服务的可用性
逻辑备份
指对数据库的逻辑组件（数据库、表、数据对象）进行备份
完全备份
将数据进行完整的备份，包含完整的库、表、索引、视图等，需要花费更长的时间
差异备份
备份自上次完全备份到现在发生改变的数据库内容，备份的文件比完整备份的文件小，备份速度更快
增量备份
备份至上次完全备份或增量备份后被修改的数据库内容
数据备份以及恢复
物理备份（冷备份）
[root@host50 backup]# cp -rp /var/lib/MySQL/dumptest1/ /opt/backup/dumptest1 [root@host50 backup]# ls dumptest1 [root@host50 backup]# ls dumptest1/ db.opt test.frm test.ibd [root@host50 backup]# ls /var/lib/mysql/dumptest1/ db.opt test.frm test.ibd [root@host50 backup]# tar -zcvf mysql-backup-$(date +%F).tar.gz dumptest1/ dumptest1/ dumptest1/db.opt dumptest1/test.frm dumptest1/test.ibd [root@host50 backup]# ls dumptest1 mysql-backup-2019-07-07.tar.gz //dumptest1库备份并且压缩完成 [root@host50 backup]# rm -dfr /var/lib/mysql/dumptest1/ //删除mysql数据库dumptest1库 [root@host50 backup]# systemctl restart mysqld [root@host50 backup]# mysql -uroot -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.17 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; //查看所删库已经不在 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | transtb | +--------------------+ 5 rows in set (0.00 sec) [root@host50 backup]# tar -zxvf mysql-backup-2019-07-07.tar.gz -C /var/lib/mysql/ //恢复数据库 dumptest1/ dumptest1/db.opt dumptest1/test.frm dumptest1/test.ibd [root@host50 backup]# systemctl restart mysqld [root@host50 backup]# mysql -uroot -p1234566 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) [root@host50 backup]# mysql -uroot -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.17 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | dumptest1 | | mysql | | performance_schema | | sys | | transtb | +--------------------+ 6 rows in set (0.00 sec) mysql> use dumptest1; 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 * from test; //库恢复成功 +-----+ | job | +-----+ | aa | | bb | | c | | dd | +-----+ 4 rows in set (0.00 sec)
逻辑备份（热备份）
库名表示方式
- --all-databases 或 -A 所有库
- 数据库名 单个库
- 数据库名 表名 单张表
- -B 数据库1 数据库2 多个库
注意事项
- 无论备份还是恢复，都要验证用户权限
[root@host50 backup]# mysqldump -uroot -p --all-databases > /opt/backup/alldb.sql //备份所有库
Enter password:
[root@host50 backup]# ls
alldb.sql
[root@host50 backup]# grep -vE '^/|^-|^$' alldb.sql | head -15 //查看库中部分内容
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dumptest1` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `dumptest1`;
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`job` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES ('aa'),('bb'),('c'),('dd');
UNLOCK TABLES;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `mysql`;
DROP TABLE IF EXISTS `columns_priv`;
CREATE TABLE `columns_priv` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
[root@host50 backup]# mysqldump -uroot -p dumptest1 > /opt/backup/dumptest1.sql //只备份一个库
Enter password:
[root@host50 backup]# ls
alldb.sql dumptest1.sql
[root@host50 backup]# mysqldump -uroot -p -B dumptest1 transtb> /opt/backup/dumptest1+transdb.sql //备份指定多个库
Enter password:
[root@host50 backup]# ls
alldb.sql dumptest1.sql dumptest1+transdb.sql使用MYSQL命令从备份中恢复
通常不建议直接覆盖旧库，而是采用建立新库并导入逻辑备份的方式执行恢复，待新库正常后即可废弃或删除旧库。
[root@host50 backup]# mysql -uroot -p dumptest2 < /opt/backup/dumptest1.sql Enter password: [root@host50 backup]# mysql -uroot -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.7.17 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select * from dumptest2.test; +-----+ | job | +-----+ | aa | | bb | | c | | dd | +-----+ 4 rows in set (0.00 sec)
binlog日志概述
二进制日志用途及配置方式
| 类型 | 用途 | 配置 |
| 二进制日志 | 记录所有更改数据的操作 | log_bin=[dir/name] server_id=数字 max_binlog_size=数字m |
采用binlog日志的好处
-记录除查询之外的所有SQL命令
-可用于数据恢复
-配置MySQL主从同步的必要条件
binlog相关文件
- 主机名-bin.index 记录已有日志文件名
- 主机名-bin.000001 第一个二进制日志
- 主机名-bin.000002 第二个二进制日志
例：
配置/etc/my.cnf，并重启服务
[root@host50 backup]# vim /etc/my.cnf [mysqld] .. .. log-bin-index=mysql-bin //启用二进制日志，并指定前缀 server_id=1 binlog_format=STATEMENT //在Mysql5.7中，binlog日志格式默认为ROW，但它不记录sql语句上下文相关信息。需要将binlog日志格式修改为STATEMENT [root@host50 backup]# systemctl restart mysqld [root@host50 backup]# ls /var/lib/mysql/mysql-bin.* //新启用binlog后，每次启动MySQl服务都会新生成一份日志文件 /var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.index [root@host50 backup]# ls /var/lib/mysql/mysql-bin.* //重启MySQL服务程序，或者执行SQL操作“FLUSH LOGS;”，会生成一份新的日志： /var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.index /var/lib/mysql/mysql-bin.000002 [root@host50 backup]# cat /var/lib/mysql/mysql-bin.index //mysql-bin.index文件记录了当前保持的二进制文件列表 ./mysql-bin.000001 ./mysql-bin.000002
使用binlog日志恢复表记录
mysql> create database binlogdb; //创建库 表，并删除后恢复 Query OK, 1 row affected (0.01 sec) mysql> use binlogdb; Database changed mysql> create table tb1( -> id int(4), -> name varchar(20)); Query OK, 0 rows affected (0.00 sec) mysql> insert into tb1 values -> (1,'aa'), -> (2,'bb'), -> (3,'cc'); Query OK, 3 rows affected (0.21 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from tb1; +------+------+ | id | name | +------+------+ | 1 | aa | | 2 | bb | | 3 | cc | +------+------+ 3 rows in set (0.00 sec) mysql> delete from tb1; Query OK, 3 rows affected (0.00 sec) mysql> select * from tb1; Empty set (0.00 sec) [root@host50 backup]# mysqlbinlog /var/lib/mysql/host50-bin.000003 //查看binlog日志确认恢复时间 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #190707 1:49:26 server id 1 end_log_pos 123 CRC32 0x92d92fbb Start: binlog v 4, server v 5.7.17-log created 190707 1:49:26 # Warning: this binlog is either in use or was not closed properly. BINLOG ' Jt8gXQ8BAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA Absv2ZI= '/*!*/; # at 123 #190707 1:49:26 server id 1 end_log_pos 154 CRC32 0x12dd5e8b Previous-GTIDs # [empty] # at 154 #190707 1:53:03 server id 1 end_log_pos 219 CRC32 0xba04792a Anonymous_GTID last_committed=0 sequence_number=1 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #190707 1:53:03 server id 1 end_log_pos 325 CRC32 0x651624b7 Query thread_id=6 exec_time=0 error_code=0 SET TIMESTAMP=1562435583/*!*/; SET @@session.pseudo_thread_id=6/*!*/; 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 binlogdb /*!*/; # at 325 #190707 1:53:43 server id 1 end_log_pos 390 CRC32 0x13116bdf Anonymous_GTID last_committed=1 sequence_number=2 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 390 #190707 1:53:43 server id 1 end_log_pos 518 CRC32 0x431ff6ab Query thread_id=6 exec_time=0 error_code=0 use `binlogdb`/*!*/; SET TIMESTAMP=1562435623/*!*/; create table tb1( id int(4), name varchar(20)) /*!*/; # at 518 #190707 1:54:23 server id 1 end_log_pos 583 CRC32 0x8dc7fcdb Anonymous_GTID last_committed=2 sequence_number=3 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 583 #190707 1:54:23 server id 1 end_log_pos 670 CRC32 0x58cc7317 Query thread_id=6 exec_time=0 error_code=0 SET TIMESTAMP=1562435663/*!*/; BEGIN /*!*/; # at 670 #190707 1:54:23 server id 1 end_log_pos 803 CRC32 0xf0848df1 Query thread_id=6 exec_time=0 error_code=0 SET TIMESTAMP=1562435663/*!*/; insert into tb1 values (1,'aa'), (2,'bb'), (3,'cc') /*!*/; # at 803 #190707 1:54:23 server id 1 end_log_pos 834 CRC32 0x01790e76 Xid = 17 COMMIT/*!*/; # at 834 #190707 1:54:55 server id 1 end_log_pos 899 CRC32 0xba024a34 Anonymous_GTID last_committed=3 sequence_number=4 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 899 #190707 1:54:55 server id 1 end_log_pos 986 CRC32 0xc99b2859 Query thread_id=6 exec_time=0 error_code=0 SET TIMESTAMP=1562435695/*!*/; BEGIN /*!*/; # at 986 #190707 1:54:55 server id 1 end_log_pos 1083 CRC32 0xda0eb644 Query thread_id=6 exec_time=0 error_code=0 SET TIMESTAMP=1562435695/*!*/; delete from tb1 /*!*/; # at 1083 #190707 1:54:55 server id 1 end_log_pos 1114 CRC32 0xd72d7ba3 Xid = 19 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*/; [root@host50 backup]# mysqlbinlog --start-datetime="2019-07-07 1:54:23" --stop-datetime="2019-07-07 1:54:55" /var/lib/mysql/host50-bin.000003 | mysql -uroot -p123456 mysql> select * from binlogdb.tb1; +------+------+ | id | name | +------+------+ | 1 | aa | | 2 | bb | | 3 | cc | +------+------+ 3 rows in set (0.00 sec)
生产环境 Mysql 数据库备份策略
策略设计思路：
1.数据更新频繁，则应该进行较为频繁的备份；
2.数据较为重要，则在有适当更新时进行备份；
3.在数据库压力小的时段进行全量备份；
备份方案：
1.在每周末的某个时段使用mysqldump进行重要数据库的全量备份（最好备份单个数据库，
而不是all）；
2.在平时每天的晚上时段将二进制日志文件终止（前提需要根据数据记录的生成数量合理指
定单个二进制文件的大小），这样每隔24小时会生成一个二进制日志文件；
3.每周的全量备份文件和每天的二进制日志文件相加，就是这一周内数据库的整体内容；
4.出现故障时， 首先恢复全量备份， 可以执行mysqlbinlog命令结合实际情况根据position值
或者时间点进行恢复，确保万无一失；