目录
1.数据库常用备份方案
数据库备份方案:
1.全量备份
2.增量备份
3.差异备份
备份方案 | 特点 |
全量备份 | 1.全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 |
增量备份 | 1.增量备份是指在一次全备份或上一次增量备份后,以后每次的备份只需备 份与前一次相比增加和者被修改的文件。这就意味着,第一次增量备份的对 象是进行全备后所产生的增加和修改的文件;第二次增量备份的对象是进行 第一次增量,备份后所产生的增加和修改的文件,如此类推。 |
差异备份 | 1.备份上一次的完全备份后发生变化的所有文件。 修改文件的备份。在进行恢复时,我们只需对第一次全量备份和最后一次差异 备份进行恢复。 |
2.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
2.1 查看数据库,其中zmq库的表,以及表内容
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zmq |
+--------------------+
5 rows in set (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_zmq |
+---------------+
| hobby |
| student |
+---------------+
2 rows in set (0.00 sec)
mysql> select * from student;
+----+-----------+------+------+----------+
| id | name | age | sex | hobby_id |
+----+-----------+------+------+----------+
| 1 | 龙星佑 | 21 | 男 | 1 |
| 2 | 邹梦桥 | 22 | 男 | 2 |
| 3 | 万小丽 | 18 | 女 | 3 |
| 4 | 陈蕾 | 19 | 女 | 2 |
| 5 | 刘丰皓 | 22 | 男 | 4 |
| 6 | 王良喜 | 23 | 男 | 1 |
+----+-----------+------+------+----------+
6 rows in set (0.00 sec)
mysql> select * from hobby;
+------+------------+
| id | hobby_name |
+------+------------+
| 1 | 羽毛球 |
| 2 | 乒乓球 |
| 3 | 篮球 |
| 4 | 足球 |
| 5 | 短跑 |
| 6 | 跳高 |
| 7 | 自习 |
+------+------------+
7 rows in set (0.00 sec)
2.2 备份所有数据库
1.# 授权root用户在所有位置上远程登录访问zmq数据库
mysql> grant all on *.* TO 'root'@'%' IDENTIFIED BY '2664218545Z';
Query OK, 0 rows affected, 1 warning (0.00 sec)
2.# 备份所有数据库
[root@zmq ~]# mysqldump -uroot -p -h127.0.0.1 --all-databases > all-202309041542.sql
Enter password:
[root@zmq ~]# ls
all-202309041542.sql anaconda-ks.cfg
3.# 备份zmq数据库
[root@zmq ~]# mysqldump -uroot -p -h127.0.0.1 --databases zmq > zmq-202309041600.sql
Enter password:
[root@zmq ~]# ls
all-202309041542.sql anaconda-ks.cfg
zmq-202309041600.sql
4.# 备份zmq库中student,hobby表
[root@zmq ~]# mysqldump -uroot -p -h127.0.0.1 zmq student hobby > table-202309041600.sql
Enter password:
[root@zmq ~]# ls
all-202309041542.sql table-202309041600.sql
anaconda-ks.cfg zmq-202309041600.sql
5.# 模拟误删zmq数据库
mysql> drop database zmq;
Query OK, 2 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
2.3 MySQL数据恢复
[root@zmq ~]# ls
all-202309041542.sql table-202309041600.sql
anaconda-ks.cfg zmq-202309041600.sql
1.# 恢复zmq库
[root@zmq ~]# mysql -uroot -p -h127.0.0.1 < zmq-202309041600.sql
Enter password:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zmq |
+--------------------+
5 rows in set (0.00 sec)
2.# 模拟误删student,hobby表
mysql> drop table student,hobby;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)
3.# 恢复表
mysql> source table-202309041600.sql
Query OK, 0 rows affected (0.00 sec)
....省略
mysql> show tables;
+---------------+
| Tables_in_zmq |
+---------------+
| hobby |
| student |
+---------------+
2 rows in set (0.00 sec)
mysql> select * from student;
+----+-----------+------+------+----------+
| id | name | age | sex | hobby_id |
+----+-----------+------+------+----------+
| 1 | 龙星佑 | 21 | 男 | 1 |
| 2 | 邹梦桥 | 22 | 男 | 2 |
| 3 | 万小丽 | 18 | 女 | 3 |
| 4 | 陈蕾 | 19 | 女 | 2 |
| 5 | 刘丰皓 | 22 | 男 | 4 |
| 6 | 王良喜 | 23 | 男 | 1 |
+----+-----------+------+------+----------+
6 rows in set (0.00 sec)
3.差异备份与恢复
3.1 差异备份
1.# 开启MySQL服务器的二进制日志功能
[root@zmq ~]# vim /etc/my.cnf
[root@zmq ~]# 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@zmq ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.Logging to '/opt/data/zmq.err'.
SUCCESS!
2.# 查看现有所有库,zmq库中的表以及表内容
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zmq |
+--------------------+
5 rows in set (0.00 sec)
mysql> use zmq;
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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zmq |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from student;
+----+-----------+------+------+----------+
| id | name | age | sex | hobby_id |
+----+-----------+------+------+----------+
| 1 | 龙星佑 | 21 | 男 | 1 |
| 2 | 邹梦桥 | 22 | 男 | 2 |
| 3 | 万小丽 | 18 | 女 | 3 |
| 4 | 陈蕾 | 19 | 女 | 2 |
| 5 | 刘丰皓 | 22 | 男 | 4 |
| 6 | 王良喜 | 23 | 男 | 1 |
+----+-----------+------+------+----------+
6 rows in set (0.00 sec)
mysql> select * from hobby;
+------+------------+
| id | hobby_name |
+------+------------+
| 1 | 羽毛球 |
| 2 | 乒乓球 |
| 3 | 篮球 |
| 4 | 足球 |
| 5 | 短跑 |
| 6 | 跳高 |
| 7 | 自习 |
+------+------------+
7 rows in set (0.00 sec)
3.# 完全备份所有库
[root@zmq ~]# mysqldump -uroot -p2664218545Z --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202309041617.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
# 这里的警告是告诉你数据库密码这样使用不安全
[root@zmq ~]# ll
total 1756
-rw-r--r--. 1 root root 890255 Sep 4 03:49 all-202309041542.sql
-rw-r--r--. 1 root root 890407 Sep 4 04:17 all-202309041617.sql
-rw-------. 1 root root 1088 Jul 27 09:40 anaconda-ks.cfg
-rw-r--r--. 1 root root 2799 Sep 4 03:56 table-202309041600.sql
-rw-r--r--. 1 root root 2933 Sep 4 03:53 zmq-202309041600.sql
4.# 对zmq中student表进行数据修改
# 插入一条新的数据
# 修改其中一位用户的hobby_id
mysql> insert into student(name,age,sex,hobby_id) value('程福兴',25,'男',2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-----------+------+------+----------+
| id | name | age | sex | hobby_id |
+----+-----------+------+------+----------+
| 1 | 龙星佑 | 21 | 男 | 1 |
| 2 | 邹梦桥 | 22 | 男 | 2 |
| 3 | 万小丽 | 18 | 女 | 3 |
| 4 | 陈蕾 | 19 | 女 | 2 |
| 5 | 刘丰皓 | 22 | 男 | 4 |
| 6 | 王良喜 | 23 | 男 | 1 |
| 7 | 程福兴 | 25 | 男 | 2 |
+----+-----------+------+------+----------+
7 rows in set (0.00 sec)
mysql> update student set hobby_id = 5 where name = '邹梦桥';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+-----------+------+------+----------+
| id | name | age | sex | hobby_id |
+----+-----------+------+------+----------+
| 1 | 龙星佑 | 21 | 男 | 1 |
| 2 | 邹梦桥 | 22 | 男 | 5 |
| 3 | 万小丽 | 18 | 女 | 3 |
| 4 | 陈蕾 | 19 | 女 | 2 |
| 5 | 刘丰皓 | 22 | 男 | 4 |
| 6 | 王良喜 | 23 | 男 | 1 |
| 7 | 程福兴 | 25 | 男 | 2 |
+----+-----------+------+------+----------+
7 rows in set (0.00 sec)
3.2 MySQL差异数据恢复
1.# 模拟误删数据
# 这里误删zmq库
mysql> drop database zmq;
Query OK, 2 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
2.# 刷新日志,创建新的二进制日志文件
[root@zmq ~]# ll /opt/data
total 122988
-rw-r-----. 1 mysql mysql 56 Sep 1 03:02 auto.cnf
-rw-r-----. 1 mysql mysql 24396 Sep 4 04:14 c82.err
-rw-------. 1 mysql mysql 1676 Sep 1 03:02 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Sep 1 03:02 ca.pem
-rw-r--r--. 1 mysql mysql 1112 Sep 1 03:02 client-cert.pem
-rw-------. 1 mysql mysql 1676 Sep 1 03:02 client-key.pem
-rw-r-----. 1 mysql mysql 550 Sep 4 04:14 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Sep 4 04:29 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Sep 4 04:29 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Sep 1 03:02 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Sep 4 04:21 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Sep 1 03:02 mysql
-rw-r-----. 1 mysql mysql 908 Sep 4 04:26 mysql_bin.000002
-rw-r-----. 1 mysql mysql 19 Sep 4 04:17 mysql_bin.index
-rw-r-----. 1 mysql mysql 7 Sep 4 04:14 mysql.pid
drwxr-x---. 2 mysql mysql 8192 Sep 1 03:02 performance_schema
-rw-------. 1 mysql mysql 1680 Sep 1 03:02 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Sep 1 03:02 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 Sep 1 03:02 server-cert.pem
-rw-------. 1 mysql mysql 1676 Sep 1 03:02 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Sep 1 03:02 sys
-rw-r-----. 1 mysql mysql 3774 Sep 4 04:14 zmq.err
3.# 使用mysqladmin命令刷新二进制日志文件
[root@zmq ~]# mysqladmin -uroot -p2664218545Z flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@zmq ~]# ll /opt/data
total 122992
-rw-r-----. 1 mysql mysql 56 Sep 1 03:02 auto.cnf
-rw-r-----. 1 mysql mysql 24396 Sep 4 04:14 c82.err
-rw-------. 1 mysql mysql 1676 Sep 1 03:02 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Sep 1 03:02 ca.pem
-rw-r--r--. 1 mysql mysql 1112 Sep 1 03:02 client-cert.pem
-rw-------. 1 mysql mysql 1676 Sep 1 03:02 client-key.pem
-rw-r-----. 1 mysql mysql 550 Sep 4 04:14 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Sep 4 04:29 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Sep 4 04:29 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Sep 1 03:02 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Sep 4 04:21 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Sep 1 03:02 mysql
-rw-r-----. 1 mysql mysql 955 Sep 4 04:29 mysql_bin.000002
-rw-r-----. 1 mysql mysql 154 Sep 4 04:29 mysql_bin.000003
-rw-r-----. 1 mysql mysql 38 Sep 4 04:29 mysql_bin.index
-rw-r-----. 1 mysql mysql 7 Sep 4 04:14 mysql.pid
drwxr-x---. 2 mysql mysql 8192 Sep 1 03:02 performance_schema
-rw-------. 1 mysql mysql 1680 Sep 1 03:02 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Sep 1 03:02 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 Sep 1 03:02 server-cert.pem
-rw-------. 1 mysql mysql 1676 Sep 1 03:02 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Sep 1 03:02 sys
-rw-r-----. 1 mysql mysql 3774 Sep 4 04:14 zmq.err
# 这里可以看到新增了一个名为mysql_bin.000003 的文件,他就是新的二进制日志文件
4.# 恢复完全备份
[root@zmq ~]# mysql -uroot -p2664218545Z < all-202309041617.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
4.# 查看数据库,zmq库恢复了
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zmq |
+--------------------+
5 rows in set (0.00 sec)
4.# 查看表中数据表的内容,发现对数据做的修改都没了
mysql> use zmq;
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 student;
+----+-----------+------+------+----------+
| id | name | age | sex | hobby_id |
+----+-----------+------+------+----------+
| 1 | 龙星佑 | 21 | 男 | 1 |
| 2 | 邹梦桥 | 22 | 男 | 2 |
| 3 | 万小丽 | 18 | 女 | 3 |
| 4 | 陈蕾 | 19 | 女 | 2 |
| 5 | 刘丰皓 | 22 | 男 | 4 |
| 6 | 王良喜 | 23 | 男 | 1 |
+----+-----------+------+------+----------+
6 rows in set (0.00 sec)
6.# 找到误删数据库的日志文件
mysql> show binlog events in 'mysql_bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.42-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids | 1 | 154 | |
| mysql_bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 219 | Query | 1 | 290 | BEGIN |
| mysql_bin.000002 | 290 | Table_map | 1 | 347 | table_id: 144 (zmq.student) |
| mysql_bin.000002 | 347 | Write_rows | 1 | 409 | table_id: 144 flags: STMT_END_F |
| mysql_bin.000002 | 409 | Xid | 1 | 440 | COMMIT /* xid=498 */ |
| mysql_bin.000002 | 440 | Anonymous_Gtid | 1 | 505 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 505 | Query | 1 | 576 | BEGIN |
| mysql_bin.000002 | 576 | Table_map | 1 | 633 | table_id: 144 (zmq.student) |
| mysql_bin.000002 | 633 | Update_rows | 1 | 723 | table_id: 144 flags: STMT_END_F |
| mysql_bin.000002 | 723 | Xid | 1 | 754 | COMMIT /* xid=500 */ |
| mysql_bin.000002 | 754 | Anonymous_Gtid | 1 | 819 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 819 | Query | 1 | 908 | drop database zmq |
| mysql_bin.000002 | 908 | Rotate | 1 | 955 | mysql_bin.000003;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
7.# 查看表中内容发现删除数据库的位置在908,所以想要恢复到修改数据位置应该使用差异数据恢复,恢复到位置819
[root@zmq ~]# mysqlbinlog --stop-position=819 /opt/data/mysql_bin.000002 |mysql -uroot -p2664218545Z
mysql: [Warning] Using a password on the command line interface can be insecure.
# 恢复成功
# 查看student表中内容
mysql> select * from student;
+----+-----------+------+------+----------+
| id | name | age | sex | hobby_id |
+----+-----------+------+------+----------+
| 1 | 龙星佑 | 21 | 男 | 1 |
| 2 | 邹梦桥 | 22 | 男 | 5 |
| 3 | 万小丽 | 18 | 女 | 3 |
| 4 | 陈蕾 | 19 | 女 | 2 |
| 5 | 刘丰皓 | 22 | 男 | 4 |
| 6 | 王良喜 | 23 | 男 | 1 |
| 7 | 程福兴 | 25 | 男 | 2 |
+----+-----------+------+------+----------+
7 rows in set (0.00 sec)
8.# 恢复成功