文章目录
- 一、数据库常用备份方案
- 二、mysql 备份工具mysqldump
- 差异备份与恢复
一、数据库常用备份方案
数据库备份方案：
- 全量备份
- 增量备份
- 差异备份
备份方案 | 特点 |
全量备份 | 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝,数据恢复快,备份时间长。 |
增量备份 | 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。没有重复的备份数,备份时间短,恢复数据时必须按一定的顺序进行 |
差异备份 | 备份上一次的完全备份后发生变化的所有文件。差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复 |
**全量备份：把所有数据或应用都备份一遍 数据恢复快但是备份时间长
增量备份：当备份或增量备份后 在进行增量备份，只会备份和上次不同的数据（增加修改的数据）备份时间短但是恢复数据需要按顺序
差异备份：和上一次备份做对比 备份发生变化的文件 恢复数据时只需要对第一次全量备份 和最后一次差异备份进行恢复**
二、mysql 备份工具mysqldump
//语法：
mysqldump [OPTIONS] database [tables ...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
//常用的OPTIONS：
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机，请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307全量备份
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.07 sec)
mysql> use school;
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| tb_course |
| tb_students_info |
+------------------+
#备份全库
[root@localhost ~]# mysqldump -uroot -p'runtime123!' --all-databases > all-$(date '+%Y%m%d%H%M%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20220630045011.sql initial-setup-ks.cfg
#备份表 指定库内的指定表
[root@localhost ~]# mysqldump -uroot -p'runtime123!' school tb_course > tb_course.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20220630045011.sql anaconda-ks.cfg initial-setup-ks.cfg tb_course.sql test.yml
#备份指定库
[root@localhost ~]# mysqldump -uroot -p'runtime123!' school > school.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20220630045011.sql initial-setup-ks.cfg school.sql test.yml
anaconda-ks.cfg tb_course.sqlmysql数据恢复
删除school库
mysql> drop database school;
Query OK, 2 rows affected (0.71 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)恢复所有库
[root@localhost ~]# mysql -uroot -p'runtime123!' < all-20220630045011.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p'runtime123!' -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+删除表
mysql> drop table tb_course;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| tb_students_info |
+------------------+
1 row in set (0.00 sec)
#恢复表 可以将表恢复到指定库中
[root@localhost ~]# mysql -uroot -p'runtime123!' school < tb_course.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| tb_course |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)差异备份与恢复
mysql 差异备份
开启MySQL服务器的二进制日志功能
[root@localhost local]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
server-id=1
log-bin=mysql_bin //添加这两行
[root@localhost ~]# systemctl restart mysqld.service //重启服务让其生效完全备份
#完全备份
[root@localhost ~]# mysqldump -uroot -p'runtime123!' --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-$(date '+%Y%m%d%H%M%S').sql
#添加数据
mysql> select * from tb_course
-> ;
+----+-------------+
| id | course_name |
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | Java |
| 2 | MySQL |
| 3 | Pytho,n |
| 4 | Go |
| 5 | C++ |
| 7 | Html |
+----+-------------+
6 rows in set (0.01 sec)
mysql> insert tb_course (course_name) values ('Linux');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | Java |
| 2 | MySQL |
| 3 | Pytho,n |
| 4 | Go |
| 5 | C++ |
| 7 | Html |
| 8 | Linux |
+----+-------------+
7 rows in set (0.00 sec)
#更新数据
mysql> update tb_course set course_name = 'Php' where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | Java |
| 2 | MySQL |
| 3 | Php |
| 4 | Go |
| 5 | C++ |
| 7 | Html |
| 8 | Linux |
+----+-------------+
7 rows in set (0.00 sec)
#删除表
mysql> drop table tb_students_info;
Query OK, 0 rows affected (0.10 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| tb_course |
+------------------+
1 row in set (0.00 sec)删除数据库
mysql> drop database school;
Query OK, 1 row affected (0.10 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)刷新创建新的二进制日志
[root@localhost data]# mysqladmin -uroot -p'runtime123!' flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]# ll
总用量 123032
-rw-r-----. 1 mysql mysql 56 6月 30 08:04 auto.cnf
-rw-------. 1 mysql mysql 1680 6月 30 08:04 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 6月 30 08:04 ca.pem
-rw-r--r--. 1 mysql mysql 1112 6月 30 08:04 client-cert.pem
-rw-------. 1 mysql mysql 1680 6月 30 08:04 client-key.pem
-rw-r-----. 1 mysql mysql 531 6月 30 09:35 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 6月 30 09:36 ibdata1
-rw-r-----. 1 mysql mysql 50331648 6月 30 09:36 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 6月 30 08:04 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 6月 30 09:44 ibtmp1
-rw-r-----. 1 mysql mysql 68254 6月 30 09:47 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 6月 30 08:04 mysql
-rw-r-----. 1 mysql mysql 1119 6月 30 09:47 mysql_bin.000004
-rw-r-----. 1 mysql mysql 154 6月 30 09:47 mysql_bin.000005
-rw-r-----. 1 mysql mysql 38 6月 30 09:47 mysql_bin.index
-rw-r-----. 1 mysql mysql 8 6月 30 08:35 mysql.pid
drwxr-x---. 2 mysql mysql 8192 6月 30 08:04 performance_schema
-rw-------. 1 mysql mysql 1680 6月 30 08:04 private_key.pem
-rw-r--r--. 1 mysql mysql 452 6月 30 08:04 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 6月 30 08:04 server-cert.pem
-rw-------. 1 mysql mysql 1676 6月 30 08:04 server-key.pem
drwxr-x---. 2 mysql mysql 8192 6月 30 08:04 sys恢复完全备份
[root@localhost ~]# mysql -uroot -pruntime123! < all-20220630094428.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
#恢复到未修改前
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use school;
Database changed
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| tb_course |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)恢复差异备份
#找到删除库的位置
mysql> show binlog events in 'mysql_bin.000004';
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
| mysql_bin.000004 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql_bin.000004 | 123 | Previous_gtids | 1 | 154 | |
| mysql_bin.000004 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000004 | 219 | Query | 1 | 293 | BEGIN |
| mysql_bin.000004 | 293 | Table_map | 1 | 350 | table_id: 176 (school.tb_course) |
| mysql_bin.000004 | 350 | Write_rows | 1 | 396 | table_id: 176 flags: STMT_END_F |
| mysql_bin.000004 | 396 | Xid | 1 | 427 | COMMIT /* xid=959 */ |
| mysql_bin.000004 | 427 | Anonymous_Gtid | 1 | 492 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000004 | 492 | Query | 1 | 566 | BEGIN |
| mysql_bin.000004 | 566 | Table_map | 1 | 623 | table_id: 176 (school.tb_course) |
| mysql_bin.000004 | 623 | Update_rows | 1 | 680 | table_id: 176 flags: STMT_END_F |
| mysql_bin.000004 | 680 | Xid | 1 | 711 | COMMIT /* xid=961 */ |
| mysql_bin.000004 | 711 | Anonymous_Gtid | 1 | 776 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000004 | 776 | Query | 1 | 909 | use `school`; DROP TABLE `tb_students_info` /* generated by server */ |
| mysql_bin.000004 | 909 | Anonymous_Gtid | 1 | 974 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000004 | 974 | Query | 1 | 1072 | drop database school |
| mysql_bin.000004 | 1072 | Rotate | 1 | 1119 | mysql_bin.000005;pos=4 |
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
17 rows in set (0.00 sec)
#使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=974 /opt/data/mysql_bin.000004 | mysql -uroot -p'runtime123!'
mysql: [Warning] Using a password on the command line interface can be insecure.
#数据恢复到修改后的
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | Java |
| 2 | MySQL |
| 3 | Php |
| 4 | Go |
| 5 | C++ |
| 7 | Html |
| 8 | Linux |
+----+-------------+
7 rows in set (0.00 sec)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
