数据库备份概述
短信预约 -IT技能 免费直播动态提醒
数据库备份概述
概述：将数据库中存在的现有数据，进行存放成为副本数据，可解决数据容灾；
提高系统的高可用性和灾难恢复性，数据崩溃时，以最小代价重新恢复数据；
数据丢失的原因：程序错误、人为错误、磁盘错误、天灾人祸
数据库备份的分类
物理备份
指对数据库操作系统的物理文件（数据文件、日志文件）等备份
冷备份
必须在数据库关闭状态下进行备份，能够更好保证数据库的完整性
热备份
能够在数据库处于正常运行的情况下备份，能够更高的保证服务的可用性
逻辑备份
指对数据库的逻辑组件（数据库、表、数据对象）进行备份
完全备份
将数据进行完整的备份，包含完整的库、表、索引、视图等，需要花费更长的时间
差异备份
备份自上次完全备份到现在发生改变的数据库内容，备份的文件比完整备份的文件小，备份速度更快
增量备份
备份至上次完全备份或增量备份后被修改的数据库内容
数据备份以及恢复
物理备份（冷备份）
[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 `dumptest1` ;
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 `mysql` ;
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日志确认恢复时间
;
;
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;
;
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' ;
DELIMITER ;
# End of log file
;
;
[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 值
或者时间点进行恢复，确保万无一失；
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/41.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
