mysql数据库备份与恢复
一、数据库常用备份方案
数据库备份方案：
- 全量备份
- 增量备份
- 差异备份
| 备份方案 | 特点 |
|---|---|
| 全量备份 | 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长 |
| 增量备份 | 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象 是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 |
| 差异备份 | 备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。 |
二、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.00 sec)
mysql> use school;
Database changed
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| house |
| student |
+------------------+
2 rows in set (0.00 sec)
[root@localhost ~]# ls
anaconda-ks.cfg
[root@localhost ~]# mysqldump -uroot -p -h192.168.200.39 --all-databases > all-0904.sql
Enter password:
mysqldump: Got error: 1130: Host '192.168.200.39' is not allowed to connect to this MySQL server when trying to connect
[root@localhost ~]# ls
all-0904.sql anaconda-ks.cfg
//备份school库的house表和student表
[root@localhost ~]# mysqldump -uroot -p -h192.168.200.39 --all-databases > all-0904.sql
Enter password:
[root@localhost ~]# ls
all-0904.sql anaconda-ks.cfg
[root@localhost ~]# mysqldump -uroot -p -h192.168.200.39 school house student > table_0904.sql
Enter password:
[root@localhost ~]# ls
all-0904.sql anaconda-ks.cfg table_0904.sql
//备份school库
[root@localhost ~]# mysqldump -uroot -p -h192.168.200.39 --databases school > db_school0904.sql
Enter password:
[root@localhost ~]# ls
all-0904.sql anaconda-ks.cfg db_school0904.sql table_0904.sql
//模拟误删school数据库
mysql> drop database school;
Query OK, 2 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
三、mysql数据恢复
//恢复school数据库
[root@localhost ~]# ls
all-0904.sql anaconda-ks.cfg db_school0904.sql table_0904.sql
[root@localhost ~]# mysql -uroot -p -h192.168.200.39 < db_school0904.sql
Enter password:
[root@localhost ~]# mysql -uroot -p -h192.168.200.39 -e 'show databases;'
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
//模拟恢复school库的house表和student表
mysql> use school;
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 tables;
+------------------+
| Tables_in_school |
+------------------+
| house |
| student |
+------------------+
2 rows in set (0.00 sec)
//先删除house表和student表
mysql> drop table house;
Query OK, 0 rows affected (0.01 sec)
mysql> drop table student;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)
//在恢复school库的house表和student表
mysql> source table_0904.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
......
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| house |
| student |
+------------------+
2 rows in set (0.00 sec)
//模拟删除整个数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database school;
Query OK, 2 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
//恢复整个数据库
[root@localhost ~]# ls
all-0904.sql anaconda-ks.cfg db_school0904.sql table_0904.sql
[root@localhost ~]# mysql -uroot -p -h192.168.200.39 < all-0904.sql
Enter password:
[root@localhost ~]# mysql -uroot -p -h192.168.200.39 -e 'show databases;'
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
四、差异备份与恢复
mysql差异备份
开启MySQL服务器的二进制日志功能
[root@localhost ~]# vi /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
basedir = /opt/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 ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
对数据库进行完全备份
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.39-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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 |
| school |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use school;
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 tables;
+------------------+
| Tables_in_school |
+------------------+
| house |
| student |
+------------------+
2 rows in set (0.00 sec)
mysql> select * from house;
+------+-------------+
| id | room_number |
+------+-------------+
| 1 | 101 |
| 2 | 201 |
| 3 | 301 |
| 4 | 401 |
| 5 | 501 |
+------+-------------+
5 rows in set (0.00 sec)
mysql> select * from student;
+----+------------+------+----------+------+--------+
| id | name | age | province | sex | height |
+----+------------+------+----------+------+--------+
| 1 | zhangsan | 20 | hubei | m | 180 |
| 2 | limei | 20 | hubei | f | 165 |
| 3 | wangermazi | 19 | hunan | m | 175 |
| 4 | natasha | 18 | shanxi | f | 160 |
+----+------------+------+----------+------+--------+
4 rows in set (0.00 sec)
//完全备份
[root@localhost ~]# mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20230904.sql
Enter password:
[root@localhost ~]# ll
total 1740
-rw-r--r--. 1 root root 882093 Sep 4 14:57 all-0904.sql
-rw-r--r--. 1 root root 882240 Sep 4 16:06 all-20230904.sql
-rw-------. 1 root root 1196 Aug 28 15:41 anaconda-ks.cfg
-rw-r--r--. 1 root root 2927 Sep 4 15:02 db_school0904.sql
-rw-r--r--. 1 root root 2782 Sep 4 14:59 table_0904.sql
//增加新内容
mysql> insert into house values(6,601),(7,701);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from house;
+------+-------------+
| id | room_number |
+------+-------------+
| 1 | 101 |
| 2 | 201 |
| 3 | 301 |
| 4 | 401 |
| 5 | 501 |
| 6 | 601 |
| 7 | 701 |
+------+-------------+
7 rows in set (0.00 sec)
mysql> update house set room_number = 606 where id = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from house;
+------+-------------+
| id | room_number |
+------+-------------+
| 1 | 101 |
| 2 | 201 |
| 3 | 301 |
| 4 | 401 |
| 5 | 501 |
| 6 | 606 |
| 7 | 701 |
+------+-------------+
7 rows in set (0.00 sec)
mysql差异备份恢复
模拟误删数据
[root@localhost ~]# mysql -uroot -p -h192.168.200.39 -e 'drop database school';
Enter password:
[root@localhost ~]# mysql -uroot -p -h192.168.200.39 -e 'show databases';
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
//由上可以看到school这个数据库已被删除
刷新创建新的二进制日志
[root@localhost ~]# ll /opt/data
total 123000
-rw-r-----. 1 mysql mysql 56 Aug 29 18:11 auto.cnf
-rw-------. 1 mysql mysql 1676 Aug 29 18:11 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Aug 29 18:11 ca.pem
-rw-r--r--. 1 mysql mysql 1112 Aug 29 18:11 client-cert.pem
-rw-------. 1 mysql mysql 1680 Aug 29 18:11 client-key.pem
-rw-r-----. 1 mysql mysql 621 Sep 4 15:57 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648 Sep 4 16:16 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Aug 29 18:11 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Sep 4 16:16 ibdata1
-rw-r-----. 1 mysql mysql 12582912 Sep 4 16:06 ibtmp1
-rw-r-----. 1 mysql mysql 40359 Sep 4 15:57 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 Sep 4 15:52 mysql
-rw-r-----. 1 mysql mysql 5 Sep 4 15:57 mysql.pid
-rw-r-----. 1 mysql mysql 866 Sep 4 16:16 mysql_bin.000003
-rw-r-----. 1 mysql mysql 19 Sep 4 16:06 mysql_bin.index
drwxr-x---. 2 mysql mysql 8192 Aug 29 18:11 performance_schema
-rw-------. 1 mysql mysql 1676 Aug 29 18:11 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Aug 29 18:11 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 Aug 29 18:11 server-cert.pem
-rw-------. 1 mysql mysql 1676 Aug 29 18:11 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Aug 29 18:11 sys
[root@localhost ~]# mysqladmin -uroot -p flush-logs
Enter password:
[root@localhost ~]# ll /opt/data/
total 123004
-rw-r-----. 1 mysql mysql 56 Aug 29 18:11 auto.cnf
-rw-------. 1 mysql mysql 1676 Aug 29 18:11 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Aug 29 18:11 ca.pem
-rw-r--r--. 1 mysql mysql 1112 Aug 29 18:11 client-cert.pem
-rw-------. 1 mysql mysql 1680 Aug 29 18:11 client-key.pem
-rw-r-----. 1 mysql mysql 621 Sep 4 15:57 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648 Sep 4 16:18 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Aug 29 18:11 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Sep 4 16:18 ibdata1
-rw-r-----. 1 mysql mysql 12582912 Sep 4 16:06 ibtmp1
-rw-r-----. 1 mysql mysql 40359 Sep 4 15:57 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 Sep 4 15:52 mysql
-rw-r-----. 1 mysql mysql 5 Sep 4 15:57 mysql.pid
-rw-r-----. 1 mysql mysql 913 Sep 4 16:18 mysql_bin.000003
-rw-r-----. 1 mysql mysql 154 Sep 4 16:18 mysql_bin.000004
-rw-r-----. 1 mysql mysql 38 Sep 4 16:18 mysql_bin.index
drwxr-x---. 2 mysql mysql 8192 Aug 29 18:11 performance_schema
-rw-------. 1 mysql mysql 1676 Aug 29 18:11 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Aug 29 18:11 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 Aug 29 18:11 server-cert.pem
-rw-------. 1 mysql mysql 1676 Aug 29 18:11 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Aug 29 18:11 sys
恢复完全备份
[root@localhost ~]# ls
all-0904.sql all-20230904.sql anaconda-ks.cfg db_school0904.sql table_0904.sql
[root@localhost ~]# mysql -uroot -p < all-20230904.sql
Enter password:
[root@localhost ~]# mysql -uroot -p -e 'show databases';
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
[root@localhost ~]# mysql -uroot -p12345678 -e 'show tables from school';
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+
| Tables_in_school |
+------------------+
| house |
| student |
+------------------+
[root@localhost ~]# mysql -uroot -p12345678 -e 'select * from school.house';
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------------+
| id | room_number |
+------+-------------+
| 1 | 101 |
| 2 | 201 |
| 3 | 301 |
| 4 | 401 |
| 5 | 501 |
+------+-------------+
[root@localhost ~]# mysql -uroot -p12345678 -e 'select * from school.student';
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------------+------+----------+------+--------+
| id | name | age | province | sex | height |
+----+------------+------+----------+------+--------+
| 1 | zhangsan | 20 | hubei | m | 180 |
| 2 | limei | 20 | hubei | f | 165 |
| 3 | wangermazi | 19 | hunan | m | 175 |
| 4 | natasha | 18 | shanxi | f | 160 |
+----+------------+------+----------+------+--------+
恢复差异备份
[root@localhost ~]# ll /opt/data/
total 124024
-rw-r-----. 1 mysql mysql 56 Aug 29 18:11 auto.cnf
-rw-------. 1 mysql mysql 1676 Aug 29 18:11 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Aug 29 18:11 ca.pem
-rw-r--r--. 1 mysql mysql 1112 Aug 29 18:11 client-cert.pem
-rw-------. 1 mysql mysql 1680 Aug 29 18:11 client-key.pem
-rw-r-----. 1 mysql mysql 621 Sep 4 15:57 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648 Sep 4 16:46 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Aug 29 18:11 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Sep 4 16:46 ibdata1
-rw-r-----. 1 mysql mysql 12582912 Sep 4 16:06 ibtmp1
-rw-r-----. 1 mysql mysql 40359 Sep 4 15:57 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 Sep 4 16:44 mysql
-rw-r-----. 1 mysql mysql 5 Sep 4 15:57 mysql.pid
-rw-r-----. 1 mysql mysql 913 Sep 4 16:18 mysql_bin.000003
-rw-r-----. 1 mysql mysql 862420 Sep 4 16:44 mysql_bin.000004
-rw-r-----. 1 mysql mysql 38 Sep 4 16:18 mysql_bin.index
drwxr-x---. 2 mysql mysql 8192 Aug 29 18:11 performance_schema
-rw-------. 1 mysql mysql 1676 Aug 29 18:11 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Aug 29 18:11 public_key.pem
drwxr-x---. 2 mysql mysql 92 Sep 4 16:44 school
-rw-r--r--. 1 mysql mysql 1112 Aug 29 18:11 server-cert.pem
-rw-------. 1 mysql mysql 1676 Aug 29 18:11 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Aug 29 18:11 sys
//检查误删数据库的位置在什么地方
mysql> show binlog events;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000003 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.39-log, Binlog ver: 4 |
| mysql_bin.000003 | 123 | Previous_gtids | 1 | 154 | |
| mysql_bin.000003 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000003 | 219 | Query | 1 | 293 | BEGIN |
| mysql_bin.000003 | 293 | Table_map | 1 | 344 | table_id: 143 (school.house) |
| mysql_bin.000003 | 344 | Write_rows | 1 | 397 | table_id: 143 flags: STMT_END_F |
| mysql_bin.000003 | 397 | Xid | 1 | 428 | COMMIT /* xid=485 */ |
| mysql_bin.000003 | 428 | Anonymous_Gtid | 1 | 493 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000003 | 493 | Query | 1 | 567 | BEGIN |
| mysql_bin.000003 | 567 | Table_map | 1 | 618 | table_id: 143 (school.house) |
| mysql_bin.000003 | 618 | Update_rows | 1 | 672 | table_id: 143 flags: STMT_END_F |
| mysql_bin.000003 | 672 | Xid | 1 | 703 | COMMIT /* xid=487 */ |
| mysql_bin.000003 | 703 | Anonymous_Gtid | 1 | 768 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000003 | 768 | Query | 1 | 866 | drop database school //此处就是删除数据库的位置，对应的pos位置是768|
| mysql_bin.000003 | 866 | Rotate | 1 | 913 | mysql_bin.000004;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
//使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=768 /opt/data/mysql_bin.000003 |mysql -uroot -p12345678
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p12345678 -e 'select * from school.house';
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------------+
| id | room_number |
+------+-------------+
| 1 | 101 |
| 2 | 201 |
| 3 | 301 |
| 4 | 401 |
| 5 | 501 |
| 6 | 606 |
| 7 | 701 |
+------+-------------+
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
