mysql数据库备份与恢复

4. mysql数据库备份与恢复

4.1 数据库常用备份方案

冷备份：当数据库服务关闭，把数据库对于的数据目录下的数据文件的拷贝一份 物理备份

热备份：数据库正常运行时，直接对数据库备份数据库的备份方式

数据库备份方案：

全量备份：太繁琐

增量备份:第一次还是先进行全
差异备份

#### >备份方案 特点

全量备份: 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。
数据恢复快。备份时间长

增量备份: 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份,与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象
是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量
备份后所产生的增加和修改的文件，如此类推。没有重复的备份数据,备份时间短
恢复数据时必须按一定的顺序进行，如不按顺序修改，则数据丢失。

差异备份: 备份上一次的完全备份后发生变化的所有文件。
差异备份是指在一次全备份后到进行差异备份的这段时间内
对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

4.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

冷备份，对/opt/data数据移动到其他目录
在查看数据库中数据发现没有

[root@http ~]# mv /opt/data/* /2022/mysql/
[root@http ~]# 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 5
Server version: 5.7.37 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 |
+--------------------+
1 row in set (0.00 sec)



//恢复
[root@http ~]# mv /2022/mysql/* /opt/data/
[root@http ~]# ls /opt/data/
auto.cnf         ibdata1             private_key.pem
ca-key.pem       ib_logfile0         public_key.pem
ca.pem           ib_logfile1         server-cert.pem
client-cert.pem  ibtmp1              server-key.pem
client-key.pem   mysql               sys
http.err         mysql.pid
ib_buffer_pool   performance_schema
[root@http ~]# 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 6
Server version: 5.7.37 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 |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

热备份

进入配置文件添加数据
vi    /ety/my.cnf
[mysqldump]
user = root
password = 123456
//语法
mysqldump --databases   表  >   表.sql

[root@http ~]# mysqldump --databases lty > 20.sql
[root@http ~]# ls
20.sql  anaconda-ks.cfg

//进入数据库删除lty
mysql> drop database lty;
Query OK, 1 row affected (0.01 sec)

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

//恢复
[root@http ~]# mysql -uroot -p123456 < 20.sql 
mysql: [Warning] Using a password on the command line interface can be insecur

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

数据库的全量备份

//备份数据库所有内容

[root@http ~]# mysqldump --all-databases > all.sql
[root@http ~]# ls
20.sql  all.sql  anaconda-ks.cfg
//删除mysql表
mysql> drop database mysql;
Query OK, 31 rows affected, 2 warnings (0.04 sec)

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


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lty                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
[root@http ~]# mysql -uroot -p123456 < all.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> use mysql;
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

## 4.4 差异备份与恢复
### 4.4.1. mysql差异备份
开启MySQL服务器的二进制日志功能

[root@http ~]# vi /etc/my.cnf
[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@http ~]# 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 2
Server version: 5.7.37-log MySQL Community Server (GPL)

Copyright © 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 |
| lty |
| mysql |
| performance_schema |
| sys |
±-------------------+
5 rows in set (0.00 sec)

mysql> show tables from lty;
±--------------+
| Tables_in_lty |
±--------------+
| hh |
±--------------+
1 row in set (0.00 sec)

mysql> select * from lty,hh;
ERROR 1046 (3D000): No database selected
mysql> select * from lty.hh;
±-----±-----+
| id | name |
±-----±-----+
| 1 | w |
| 2 | e |
±-----±-----+
2 rows in set (0.01 sec)

//完全备份
[root@http ~]# mysql_upgrade -uroot -p123456 --force
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
The sys schema is already up to date (version 1.5.2).
Checking databases.
lty.hh OK
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.

[root@http ~]# mysqldump -uroot -p123456 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-201902211406.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

### 4.4.2. mysql差异备份恢复
模拟误删数据

mysql> drop database lty;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
4 rows in set (0.00 sec)
//由上可以看到lty这个数据库已被删除
刷新创建新的二进制日志

[root@http ~]# ll /opt/data/
total 123008
-rw-r-----. 1 mysql mysql 56 Jul 26 23:07 auto.cnf
-rw-------. 1 mysql mysql 1676 Jul 26 23:07 ca-key.pem
-rw-r–r–. 1 mysql mysql 1112 Jul 26 23:07 ca.pem
-rw-r–r–. 1 mysql mysql 1112 Jul 26 23:07 client-cert.pem
-rw-------. 1 mysql mysql 1676 Jul 26 23:07 client-key.pem
-rw-r-----. 1 mysql mysql 44829 Jul 29 13:23 http.err
-rw-r-----. 1 mysql mysql 738 Jul 29 13:22 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 29 13:29 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 29 13:29 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 26 23:07 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 13:26 ibtmp1
drwxr-x—. 2 mysql mysql 4096 Jul 29 13:22 mysql
-rw-r-----. 1 mysql mysql 308 Jul 29 13:29 mysql_bin.000004
-rw-r-----. 1 mysql mysql 19 Jul 29 13:26 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 Jul 29 13:22 mysql.pid
-rw-r–r–. 1 root root 6 Jul 29 13:22 mysql_upgrade_info
drwxr-x—. 2 mysql mysql 8192 Jul 29 13:22 performance_schema
-rw-------. 1 mysql mysql 1676 Jul 26 23:07 private_key.pem
-rw-r–r–. 1 mysql mysql 452 Jul 26 23:07 public_key.pem
-rw-r–r–. 1 mysql mysql 1112 Jul 26 23:07 server-cert.pem
-rw-------. 1 mysql mysql 1676 Jul 26 23:07 server-key.pem
drwxr-x—. 2 mysql mysql 8192 Jul 26 23:07 sys
[root@http ~]# mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@http ~]# ll /opt/data/
total 123012
-rw-r-----. 1 mysql mysql 56 Jul 26 23:07 auto.cnf
-rw-------. 1 mysql mysql 1676 Jul 26 23:07 ca-key.pem
-rw-r–r–. 1 mysql mysql 1112 Jul 26 23:07 ca.pem
-rw-r–r–. 1 mysql mysql 1112 Jul 26 23:07 client-cert.pem
-rw-------. 1 mysql mysql 1676 Jul 26 23:07 client-key.pem
-rw-r-----. 1 mysql mysql 44829 Jul 29 13:23 http.err
-rw-r-----. 1 mysql mysql 738 Jul 29 13:22 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 29 13:29 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 29 13:29 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 26 23:07 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 13:26 ibtmp1
drwxr-x—. 2 mysql mysql 4096 Jul 29 13:22 mysql
-rw-r-----. 1 mysql mysql 355 Jul 29 13:30 mysql_bin.000004
-rw-r-----. 1 mysql mysql 154 Jul 29 13:30 mysql_bin.000005
-rw-r-----. 1 mysql mysql 38 Jul 29 13:30 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 Jul 29 13:22 mysql.pid
-rw-r–r–. 1 root root 6 Jul 29 13:22 mysql_upgrade_info
drwxr-x—. 2 mysql mysql 8192 Jul 29 13:22 performance_schema
-rw-------. 1 mysql mysql 1676 Jul 26 23:07 private_key.pem
-rw-r–r–. 1 mysql mysql 452 Jul 26 23:07 public_key.pem
-rw-r–r–. 1 mysql mysql 1112 Jul 26 23:07 server-cert.pem
-rw-------. 1 mysql mysql 1676 Jul 26 23:07 server-key.pem
drwxr-x—. 2 mysql mysql 8192 Jul 26 23:07 sys

恢复完全备份

[root@http ~]# mysql -uroot -p123456 < all-201902211406.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| lty |
| mysql |
| performance_schema |
| sys |
±-------------------+
5 rows in set (0.00 sec)
mysql> select * from hh;
±-----±-----+
| id | name |
±-----±-----+
| 1 | w |
| 2 | e |
±-----±-----+
2 rows in set (0.00 sec)

//添加新的内容

mysql> insert into hh (id,name) values (3,‘r’),(4,‘y’);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from hh;
±-----±-----+
| id | name |
±-----±-----+
| 1 | w |
| 2 | e |
| 3 | r |
| 4 | y |
±-----±-----+
4 rows in set (0.00 sec)

//误删除
[root@http ~]# mysql -uroot -p123456 -e ‘drop database lty;’
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@http ~]# mysql -uroot -p123456 -e ‘show databases;’
mysql: [Warning] Using a password on the command line interface can be insecure.
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+

由上可以看到hehe这个数据库已被删除
刷新创建新的二进制日志
[root@http ~]# ll /opt/data/
total 123028
-rw-r-----. 1 mysql mysql 56 Jul 26 23:07 auto.cnf
-rw-------. 1 mysql mysql 1676 Jul 26 23:07 ca-key.pem
-rw-r–r–. 1 mysql mysql 1112 Jul 26 23:07 ca.pem
-rw-r–r–. 1 mysql mysql 1112 Jul 26 23:07 client-cert.pem
-rw-------. 1 mysql mysql 1676 Jul 26 23:07 client-key.pem
-rw-r-----. 1 mysql mysql 61695 Jul 29 16:21 http.err
-rw-r-----. 1 mysql mysql 830 Jul 29 16:21 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 29 16:30 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 29 16:30 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 26 23:07 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 16:22 ibtmp1
drwxr-x—. 2 mysql mysql 4096 Jul 29 16:16 mysql
-rw-r-----. 1 mysql mysql 571 Jul 29 16:29 mysql_bin.000009
-rw-r-----. 1 mysql mysql 19 Jul 29 16:22 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 Jul 29 16:21 mysql.pid
-rw-r–r–. 1 root root 6 Jul 29 13:22 mysql_upgrade_info
drwxr-x—. 2 mysql mysql 8192 Jul 29 13:22 performance_schema
-rw-------. 1 mysql mysql 1676 Jul 26 23:07 private_key.pem
-rw-r–r–. 1 mysql mysql 452 Jul 26 23:07 public_key.pem
-rw-r–r–. 1 mysql mysql 1112 Jul 26 23:07 server-cert.pem
-rw-------. 1 mysql mysql 1676 Jul 26 23:07 server-key.pem
drwxr-x—. 2 mysql mysql 8192 Jul 26 23:07 sys

刷新创建新的二进制日志
[root@http ~]# mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@http ~]# ll /opt/data/
total 123032
-rw-r-----. 1 mysql mysql 56 Jul 26 23:07 auto.cnf
-rw-------. 1 mysql mysql 1676 Jul 26 23:07 ca-key.pem
-rw-r–r–. 1 mysql mysql 1112 Jul 26 23:07 ca.pem
-rw-r–r–. 1 mysql mysql 1112 Jul 26 23:07 client-cert.pem
-rw-------. 1 mysql mysql 1676 Jul 26 23:07 client-key.pem
-rw-r-----. 1 mysql mysql 61695 Jul 29 16:21 http.err
-rw-r-----. 1 mysql mysql 830 Jul 29 16:21 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 29 16:30 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 29 16:30 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 26 23:07 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 16:22 ibtmp1
drwxr-x—. 2 mysql mysql 4096 Jul 29 16:16 mysql
-rw-r-----. 1 mysql mysql 618 Jul 29 16:31 mysql_bin.000009
-rw-r-----. 1 mysql mysql 154 Jul 29 16:31 mysql_bin.000010
-rw-r-----. 1 mysql mysql 38 Jul 29 16:31 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 Jul 29 16:21 mysql.pid
-rw-r–r–. 1 root root 6 Jul 29 13:22 mysql_upgrade_info
drwxr-x—. 2 mysql mysql 8192 Jul 29 13:22 performance_schema
-rw-------. 1 mysql mysql 1676 Jul 26 23:07 private_key.pem
-rw-r–r–. 1 mysql mysql 452 Jul 26 23:07 public_key.pem
-rw-r–r–. 1 mysql mysql 1112 Jul 26 23:07 server-cert.pem
-rw-------. 1 mysql mysql 1676 Jul 26 23:07 server-key.pem
drwxr-x—. 2 mysql mysql 8192 Jul 26 23:07 sys

恢复完全备份

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| lty |
| mysql |
| performance_schema |
| sys |
±-------------------+
5 rows in set (0.00 sec)
//这里可见之前新添加的数据没有了
mysql> select * from hh;
±-----±-----+
| id | name |
±-----±-----+
| 1 | w |
| 2 | e |
±-----±-----+
2 rows in set (0.00 sec)

恢复差异备份

[root@http ~]# ll /opt/data/
total 124052
-rw-r-----. 1 mysql mysql 56 Jul 26 23:07 auto.cnf
-rw-------. 1 mysql mysql 1676 Jul 26 23:07 ca-key.pem
-rw-r–r–. 1 mysql mysql 1112 Jul 26 23:07 ca.pem
-rw-r–r–. 1 mysql mysql 1112 Jul 26 23:07 client-cert.pem
-rw-------. 1 mysql mysql 1676 Jul 26 23:07 client-key.pem
-rw-r-----. 1 mysql mysql 61695 Jul 29 16:21 http.err
-rw-r-----. 1 mysql mysql 830 Jul 29 16:21 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 29 16:31 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 29 16:31 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 26 23:07 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 16:22 ibtmp1
drwxr-x—. 2 mysql mysql 48 Jul 29 16:31 lty
drwxr-x—. 2 mysql mysql 4096 Jul 29 16:31 mysql
-rw-r-----. 1 mysql mysql 618 Jul 29 16:31 mysql_bin.000009
-rw-r-----. 1 mysql mysql 856004 Jul 29 16:31 mysql_bin.000010
-rw-r-----. 1 mysql mysql 38 Jul 29 16:31 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 Jul 29 16:21 mysql.pid
-rw-r–r–. 1 root root 6 Jul 29 13:22 mysql_upgrade_info
drwxr-x—. 2 mysql mysql 8192 Jul 29 13:22 performance_schema
-rw-------. 1 mysql mysql 1676 Jul 26 23:07 private_key.pem
-rw-r–r–. 1 mysql mysql 452 Jul 26 23:07 public_key.pem
-rw-r–r–. 1 mysql mysql 1112 Jul 26 23:07 server-cert.pem
-rw-------. 1 mysql mysql 1676 Jul 26 23:07 server-key.pem
drwxr-x—. 2 mysql mysql 8192 Jul 26 23:07 sys

//检查误删数据库的位置在什么地方
[root@http ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.37-log MySQL Community Server (GPL)

Copyright © 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 binlog events in ‘mysql_bin.000009’;
±-----------------±----±---------------±----------±---------
| Log_name | Pos | Event_type | Server_id | End_log_p
±-----------------±----±---------------±----------±---------
| mysql_bin.000009 | 4 | Format_desc | 1 | 1
| mysql_bin.000009 | 123 | Previous_gtids | 1 | 1
| mysql_bin.000009 | 154 | Anonymous_Gtid | 1 | 2
| mysql_bin.000009 | 219 | Query | 1 | 2
| mysql_bin.000009 | 290 | Table_map | 1 | 3
| mysql_bin.000009 | 337 | Write_rows | 1 | 3
| mysql_bin.000009 | 386 | Xid | 1 | 4
| mysql_bin.000009 | 417 | Anonymous_Gtid | 1 | 4
| mysql_bin.000009 | 482 | Query | 1 | 5
| mysql_bin.000009 | 571 | Rotate | 1 | 6
±-----------------±----±---------------±----------±---------
10 rows in set (0.00 sec)

//使用mysqlbinlog恢复差异备份
[root@http ~]# mysqlbinlog --stop-position=482 /opt/data/mysql_bin.000009 |mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@http ~]# mysql -uroot -p123456 -e ‘select * from lty.hh;’
mysql: [Warning] Using a password on the command line interface can be insecure.
±-----±-----+
| id | name |
±-----±-----+
| 1 | w |
| 2 | e |
| 3 | r |
| 4 | y |
±-----±-----+
50 |
±-----±------±-----+