MySQL数据库备份与恢复

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           |
+--------------------+
| hhh                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zyq                |
+--------------------+
6 rows in set (0.00 sec)

mysql> use zyq;
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_zyq |
+---------------+
| student       |
+---------------+
1 row in set (0.01 sec)

[root@zyq ~]#: mysqldump -uroot -pPssword@_ --all-databases > data/all-$(date '+%Y%m%d').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@zyq ~]#: ll data/
total 1260
-rw-r--r-- 1 root root 1286791 Dec 10 16:19 all-20231210.sql
[root@zyq ~]#: du -sh data/all


备份zyq库的student表
[root@zyq ~]#: mysqldump -uroot -pPssword@_ zyq student > data/table-$(date '+%Y%m%d').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@zyq ~]#: ll data/
total 1264
-rw-r--r-- 1 root root 1286791 Dec 10 16:19 all-20231210.sql
-rw-r--r-- 1 root root    2084 Dec 10 16:30 table-20231210.sql

备份zyq库
[root@zyq ~]#: mysqldump -uroot -pPssword@_ zyq --databases zyq > data/zyq-$(date '+%Y%m%d').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@zyq ~]#: ll data/
total 1268
-rw-r--r-- 1 root root 1286791 Dec 10 16:19 all-20231210.sql
-rw-r--r-- 1 root root    2084 Dec 10 16:30 table-20231210.sql
-rw-r--r-- 1 root root    3319 Dec 10 16:32 zyq-20231210.sql


模拟误删zyq数据库
mysql> drop database zyq;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hhh                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql数据库恢复

恢复zyq数据库
[root@zyq ~]#: mysql -uroot -pPssword@_ < data/zyq-20231210.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@zyq ~]#: mysql -uroot -pPssword@_ -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| hhh                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zyq                |
+--------------------+



恢复zyq库的student表
mysql> use zyq;
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> source data/table-20231210.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)

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_zyq |
+---------------+
| student       |
+---------------+
1 row in set (0.01 sec)

差异备份与恢复

mysql差异备份

开启MySQL服务器的二进制日志功能，//rocky linux8.0默认开启
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /tmp/mysql.pid
skip-name-resolve

server-id=1         //设置服务器标识符
log-bin=mysql_bin    //开启二进制日志功能

[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

对数据库进行完全备份
[root@zyq data]#: mysqldump -uroot -pPssword@_ --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202312104.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
WARNING: --delete-master-logs is deprecated and will be removed in a future version. Use --delete-source-logs instead.

[root@zyq data]#: ls
all-202312104.sql  all-20231210.sql  table-20231210.sql  zyq-20231210.sql

增加新内容
mysql> show tables;
+---------------+
| Tables_in_zyq |
+---------------+
| student       |
+---------------+
1 row in set (0.00 sec)

mysql> create table runtime(id int not null,name varchar(20),age tinyint(4));
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_zyq |
+---------------+
| runtime       |
| student       |
+---------------+
2 rows in set (0.00 sec)

mysql差异备份恢复

模拟误删数据
mysql> drop database zyq;
Query OK, 2 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hhh                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)


刷新创建新的二进制日志
[root@zyq ~]#: ll /opt/data/
total 94680
-rw-r-----. 1 mysql mysql       56 Dec  5 17:24  auto.cnf
-rw-r-----  1 mysql mysql      567 Dec 10 17:10  binlog.000010
-rw-r-----  1 mysql mysql       16 Dec 10 17:02  binlog.index
-rw-------. 1 mysql mysql     1705 Dec  5 17:24  ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Dec  5 17:24  ca.pem
-rw-r--r--. 1 mysql mysql     1112 Dec  5 17:24  client-cert.pem
-rw-------. 1 mysql mysql     1705 Dec  5 17:24  client-key.pem
drwxr-x---  2 mysql mysql       25 Dec  7 20:48  hhh
-rw-r-----. 1 mysql mysql   196608 Dec 10 17:12 '#ib_16384_0.dblwr'
-rw-r-----. 1 mysql mysql  8585216 Dec  5 17:24 '#ib_16384_1.dblwr'
-rw-r-----  1 mysql mysql     4013 Dec  8 14:03  ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Dec 10 17:10  ibdata1
-rw-r-----  1 mysql mysql 12582912 Dec 10 15:52  ibtmp1
drwxr-x---. 2 mysql mysql     4096 Dec 10 15:52 '#innodb_redo'
drwxr-x---. 2 mysql mysql      187 Dec 10 15:52 '#innodb_temp'
drwxr-x---. 2 mysql mysql      143 Dec  5 17:24  mysql
-rw-r-----. 1 mysql mysql 29360128 Dec 10 17:10  mysql.ibd
-rw-r-----  1 mysql mysql        5 Dec 10 15:52  mysql.pid
drwxr-x---. 2 mysql mysql     8192 Dec  5 17:24  performance_schema
-rw-------. 1 mysql mysql     1705 Dec  5 17:24  private_key.pem
-rw-r--r--. 1 mysql mysql      452 Dec  5 17:24  public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Dec  5 17:24  server-cert.pem
-rw-------. 1 mysql mysql     1705 Dec  5 17:24  server-key.pem
drwxr-x---. 2 mysql mysql       28 Dec  5 17:24  sys
-rw-r-----. 1 mysql mysql 16777216 Dec 10 17:12  undo_001
-rw-r-----. 1 mysql mysql 16777216 Dec 10 17:12  undo_002
-rw-r-----. 1 mysql mysql    14193 Dec 10 17:04  zyq.err

[root@zyq ~]#: mysqladmin -uroot -pPssword@_ flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@zyq ~]#: ll /opt/data/
total 94684
-rw-r-----. 1 mysql mysql       56 Dec  5 17:24  auto.cnf
-rw-r-----  1 mysql mysql      611 Dec 10 17:13  binlog.000010
-rw-r-----  1 mysql mysql      157 Dec 10 17:13  binlog.000011
-rw-r-----  1 mysql mysql       32 Dec 10 17:13  binlog.index
-rw-------. 1 mysql mysql     1705 Dec  5 17:24  ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Dec  5 17:24  ca.pem
-rw-r--r--. 1 mysql mysql     1112 Dec  5 17:24  client-cert.pem
-rw-------. 1 mysql mysql     1705 Dec  5 17:24  client-key.pem
drwxr-x---  2 mysql mysql       25 Dec  7 20:48  hhh
-rw-r-----. 1 mysql mysql   196608 Dec 10 17:13 '#ib_16384_0.dblwr'
-rw-r-----. 1 mysql mysql  8585216 Dec  5 17:24 '#ib_16384_1.dblwr'
-rw-r-----  1 mysql mysql     4013 Dec  8 14:03  ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Dec 10 17:13  ibdata1
-rw-r-----  1 mysql mysql 12582912 Dec 10 15:52  ibtmp1
drwxr-x---. 2 mysql mysql     4096 Dec 10 15:52 '#innodb_redo'
drwxr-x---. 2 mysql mysql      187 Dec 10 15:52 '#innodb_temp'
drwxr-x---. 2 mysql mysql      143 Dec  5 17:24  mysql
-rw-r-----. 1 mysql mysql 29360128 Dec 10 17:10  mysql.ibd
-rw-r-----  1 mysql mysql        5 Dec 10 15:52  mysql.pid
drwxr-x---. 2 mysql mysql     8192 Dec  5 17:24  performance_schema
-rw-------. 1 mysql mysql     1705 Dec  5 17:24  private_key.pem
-rw-r--r--. 1 mysql mysql      452 Dec  5 17:24  public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Dec  5 17:24  server-cert.pem
-rw-------. 1 mysql mysql     1705 Dec  5 17:24  server-key.pem
drwxr-x---. 2 mysql mysql       28 Dec  5 17:24  sys
-rw-r-----. 1 mysql mysql 16777216 Dec 10 17:12  undo_001
-rw-r-----. 1 mysql mysql 16777216 Dec 10 17:12  undo_002
-rw-r-----. 1 mysql mysql    14416 Dec 10 17:13  zyq.err

恢复完全备份

[root@zyq ~]#: mysql -uroot -pPssword@_ < data/all-202312104.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@zyq ~]#: mysql -uroot -pPssword@_ -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| hhh                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zyq                |
+--------------------+

[root@zyq ~]#: mysql -uroot -pPssword@_ -e 'show tables from zyq;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+
| Tables_in_zyq |
+---------------+
| student       |
+---------------+

恢复差异备份

[root@zyq ~]#: ll /opt/data/
total 95912
-rw-r-----. 1 mysql mysql       56 Dec  5 17:24  auto.cnf
-rw-r-----  1 mysql mysql      611 Dec 10 17:13  binlog.000010
-rw-r-----  1 mysql mysql  1261528 Dec 10 17:15  binlog.000011
-rw-r-----  1 mysql mysql       32 Dec 10 17:13  binlog.index
-rw-------. 1 mysql mysql     1705 Dec  5 17:24  ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Dec  5 17:24  ca.pem
-rw-r--r--. 1 mysql mysql     1112 Dec  5 17:24  client-cert.pem
-rw-------. 1 mysql mysql     1705 Dec  5 17:24  client-key.pem
drwxr-x---  2 mysql mysql       25 Dec 10 17:15  hhh
-rw-r-----. 1 mysql mysql   196608 Dec 10 17:15 '#ib_16384_0.dblwr'
-rw-r-----. 1 mysql mysql  8585216 Dec  5 17:24 '#ib_16384_1.dblwr'
-rw-r-----  1 mysql mysql     4013 Dec  8 14:03  ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Dec 10 17:15  ibdata1
-rw-r-----  1 mysql mysql 12582912 Dec 10 15:52  ibtmp1
drwxr-x---. 2 mysql mysql     4096 Dec 10 17:15 '#innodb_redo'
drwxr-x---. 2 mysql mysql      187 Dec 10 15:52 '#innodb_temp'
drwxr-x---. 2 mysql mysql      143 Dec  5 17:24  mysql
-rw-r-----. 1 mysql mysql 29360128 Dec 10 17:15  mysql.ibd
-rw-r-----  1 mysql mysql        5 Dec 10 15:52  mysql.pid
drwxr-x---. 2 mysql mysql     8192 Dec  5 17:24  performance_schema
-rw-------. 1 mysql mysql     1705 Dec  5 17:24  private_key.pem
-rw-r--r--. 1 mysql mysql      452 Dec  5 17:24  public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Dec  5 17:24  server-cert.pem
-rw-------. 1 mysql mysql     1705 Dec  5 17:24  server-key.pem
drwxr-x---. 2 mysql mysql       28 Dec  5 17:24  sys
-rw-r-----. 1 mysql mysql 16777216 Dec 10 17:15  undo_001
-rw-r-----. 1 mysql mysql 16777216 Dec 10 17:15  undo_002
drwxr-x---  2 mysql mysql       25 Dec 10 17:15  zyq
-rw-r-----. 1 mysql mysql    15308 Dec 10 17:16  zyq.err

检查误删数据库的位置在什么地方
mysql> show binlog events in 'binlog.000010';
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                            |
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------+
| binlog.000010 |   4 | Format_desc    |         1 |         126 | Server ver: 8.0.35, Binlog ver: 4                                                               |
| binlog.000010 | 126 | Previous_gtids |         1 |         157 |                                                                                                 |
| binlog.000010 | 157 | Anonymous_Gtid |         1 |         234 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                            |
| binlog.000010 | 234 | Query          |         1 |         389 | use `zyq`; create table runtime(id int not null,name varchar(20),age tinyint(4)) /* xid=2373 */ |
| binlog.000010 | 389 | Anonymous_Gtid |         1 |         466 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                            |
| binlog.000010 | 466 | Query          |         1 |         567 | drop database zyq /* xid=2375 */                                                                |
| binlog.000010 | 567 | Rotate         |         1 |         611 | binlog.000011;pos=4                                                                             |
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)


使用mysqlbinlog恢复差异备份
[root@zyq ~]#:  mysqlbinlog --stop-position=466 /opt/data/binlog.000010 | mysql -uroot -pPssword@_
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@zyq ~]#: mysql -uroot -pPssword@_ -e 'show tables from zyq;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+
| Tables_in_zyq |
+---------------+
| runtime       |
| student       |
+---------------+