一、备份类型：
物理备份：直接对数据库的物理文件（数据文件、日志文件等）进行备份
逻辑备份：对数据库对象（库、表）的数据以SQL语句的形式导出进行备份
二、备份策略：
完全备份：每次备份都备份完整的库或表数据
差异备份：只备份上一次完全备份后的更新数据
增量备份：只备份上一次完全备份或增量备份后的更新数据
三、备份工具：
tar压缩打包（完全备份，物理冷备）
mysqldump（完全备份，逻辑热备）
mysqlhotcopy（完全备份，逻辑热备，仅支持MyISAM和ARCHIVE引擎表）
二进制日志（增量备份）
PXB XtraBackup innobackupex（完全备份、增量备份，物理热备）
四、完全备份 ：
（1）MySQL完全备份与恢复
- 优点：备份与恢复操作简单方便
- 缺点：数据存在大量的重复；占用大量的备份空间；备份与恢复时间长
物理冷备：先关闭数据库，使用 tar 命令压缩打包备份数据库的数据目录和文件 mysql/data/
mysqldump 逻辑热备
mysqldump -u root -p密码 --databases 库1 [库2 ....] > XXX.sql #备份一个或多个指定的库及库中所有的表
mysqldump -u root -p密码 --all-databases > XXX.sql #备份所有库
mysqldump -u root -p密码 库名 > XXX.sql #只备份指定库中的所有表（不包含库对象本身）
mysqldump -u root -p密码 库名 表1 [表2 ....] > XXX.sql #只备份指定库中的一个或多个指定的表（不包含库对象本身）
（2）数据库完全备份分类
1. 物理冷备份与恢复
- 关闭MySQL数据库
- 使用tar命令直接打包数据库文件夹.·直接替换现有MySQL目录即可
2. mysqldump备份与恢复
MySQL自带的备份工具，可方便实现对MySQL的备份
可以将指定的库、表导出为SQL脚本
使用命令mysql导入备份的数据
(3）MySQL物理冷备份及恢复
先关闭数据库，然后打包备份相关数据库文件（192.168.18.50）
[root@l5 ~]# systemctl stop mysqld
[root@l5 ~]# cd /usr/local/
[root@l5 local]# ls
bin etc games include lib lib64 libexec mysql sbin share src
[root@l5 local]# cd mysql/
[root@l5 mysql]# ls
bin docs lib man README share usr
data include LICENSE mysql-test README-test support-files
[root@l5 mysql]# tar zcf /opt/mysql-backup-$(date +%Y%m%d) data/
[root@l5 mysql]# cd /opt/
[root@l5 opt]# ls
mysql mysql-backup-20240701 rh
mysql-5.7.44 mysql-boost-5.7.44.tar.gz
[root@l5 opt]# 
恢复数据库
mysql -u root -p密码 < sql文件路径
cat sql文件路径 | mysql -u root -p密码192.168.18.50主机将备份数据库文件远程复制给192.168.18.10主机
192.168.18.10
[root@l5 opt]# scp mysql-backup-20240701 192.168.18.10:/opt
The authenticity of host '192.168.18.10 (192.168.18.10)' can't be established.
ECDSA key fingerprint is SHA256:22t/sEC//ZKbNJxo78sJ8GKh58ZnstgFhW+eXMzeU6Q.
ECDSA key fingerprint is MD5:7f:a2:03:67:f4:67:4c:2e:21:e3:9e:8f:9a:e0:7c:ab.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.18.10' (ECDSA) to the list of known hosts.
root@20.0.0.20's password:
mysql-backup-20240701 100% 1529KB 93.0MB/s 00:00
[root@l5 opt]192.168.18.10（刚装的mysql系统，里面没有任何数据）
[root@l1 ~]# cd /opt/
[root@l1 opt]# ls
mysql mysql-backup-20240701 mysql-boost-5.7.44.tar.gz rh
[root@l1 opt]# tar xf mysql-backup-20240701
[root@l1 opt]# ls
data mysql mysql-backup-20240701 mysql-boost-5.7.44.tar.gz rh
[root@l1 opt]# mv /usr/local/mysql/data/ /usr/local/mysql/data_bak
[root@l1 opt]# mv data/ /usr/local/mysql/
[root@l1 opt]# ls /usr/local/mysql/
bin include mysqld.pid README usr
data lib mysql.sock README-test
data_bak LICENSE mysql.sock.lock share
docs man mysql-test support-files
[root@l1 opt]# systemctl restart mysqld
[root@l1 opt]#[root@l1 opt]# mysql -u root -pabc123
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.44 Source distribution
Copyright (c) 2000, 2023, 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 |
| xy |
+--------------------+
5 rows in set (0.00 sec)
mysql> use xy;
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_xy |
+--------------+
| home |
| xy011 |
| xy101 |
| xy1010 |
| xy102 |
| xy103 |
| xy104 |
| xy105 |
| xy106 |
| xy107 |
| xy108 |
| xy109 |
| xy88 |
+--------------+
13 rows in set (0.00 sec)
mysql>
(4）mysqldump进行逻辑备份
mysqldump备份数据库
备份指定数据库中的文件：
mysqldump -u用户名 -p密码 数据库名 > 指定路径的绝对路径/数据库名.sql
现有一个数据库xy
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xy |
+--------------------+
5 rows in set (0.00 sec)
mysql> use xy;
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_xy |
+--------------+
| home |
| xy011 |
| xy101 |
| xy1010 |
| xy102 |
| xy103 |
| xy104 |
| xy105 |
| xy106 |
| xy107 |
| xy108 |
| xy109 |
| xy88 |
+--------------+
13 rows in set (0.00 sec)
mysql>
对库xy(192.168.18.50)进行备份并查看，发现只会保存表的数据
[root@l5 opt]# systemctl start mysqld.service
[root@l5 opt]# mkdir backup
[root@l5 opt]# cd backup/
[root@l5 backup]# ls
xy.sql
[root@l5 backup]# cat xy.sql | grep -v "^--" | grep -v "^/" | grep -v "^$"
DROP TABLE IF EXISTS `home`;
CREATE TABLE `home` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
LOCK TABLES `home` WRITE;
UNLOCK TABLES;
DROP TABLE IF EXISTS `xy011`;
......太多了这里就省略一点，嘿
备份指定数据库和文件：
备份指定数据库和文件：
mysqldump -u用户名 -p密码 --databases 数据库名 > 指定路径的绝对路径/数据库名.sql
加上 --databases 发现保存的是库和表的数据
[root@l5 backup]# mysqldump -u root -pabc123 --databases xy > /opt/backup/xy1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@l5 backup]# cat xy1.sql | grep -v "^--" | grep -v "^/" | grep -v "^$"
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `xy` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `xy`;
DROP TABLE IF EXISTS `home`;
CREATE TABLE `home` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
。。。。。内容太多了依旧省略
备份多个库及文件：
备份多个库及文件
mysqldump -u用户名 -p密码 --databases 数据库1 数据库2 > 指定路径的绝对路径/数据库名.sql
有三个库：xy、xy1、xy2
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xy |
| xy1 |
| xy2 |
+--------------------+
7 rows in set (0.00 sec)
mysql>
对这三个xy、xy1、xy2进行备份并查看
[root@l5 backup]# mysqldump -u root -pabc123 --databases xy xy1 xy2> /opt/backup/xy2.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@l5 backup]# cat xy2.sql | grep "^USE"
USE `xy`;
USE `xy1`;
USE `xy2`;
[root@l5 backup]#
备份所有数据库文件：
备份所有数据库文件：
mysqldump -u用户名 -p密码 --all-databases > 指定路径的绝对路径/数据库名.sql
[root@l5 backup]# mysqldump -u root -pabc123 --all-databases > /opt/backup/all-databases.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@l5 backup]# cat all-databases.sql | grep "^USE"
USE `mysql`;
USE `xy`;
USE `xy1`;
USE `xy2`;
[root@l5 backup]#
2）mysqldump备份数据表
备份指定数据库中的表数据文件：
mysqldump -u用户名 -p密码 数据库名 表名 > 指定路径的绝对路径/数据库名_表名.sql
备份xy库的xy表并查看
[root@l5 backup]# mysqldump -u root -pabc123 xy > /opt/backup/xy_xy.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@l5 backup]# cat xy_xy.sql
-- MySQL dump 10.13 Distrib 5.7.44, for Linux (x86_64)
--
-- Host: localhost Database: xy
-- ------------------------------------------------------
-- Server version 5.7.44
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESUL
。。。。。内容太多了这里就省略恢复数据库：
使用mysqldump导出的脚本，可使用导入的方法
- source命令
- mysql命令
使用source恢复数据库的步骤
- 登录到MySQL数据库
- 执行source 备份sql脚本的路径
source 恢复的示例
MySQL[(none)]>source /backup/all-data.sql
完全恢复：
1）先登录数据库，再执行 source sql文件路径 （如何sql文件里只备份了表，需要先 use 切换库再执行 source）
2）mysql -u root -p密码 < sql文件路径 cat sql文件路径 | mysql -u root -p密码 #恢复库
mysql -u root -p密码 库名 < sql文件路径 cat sql文件路径 | mysql -u root -p密码 库名 #恢复表
（1）使用source命令恢复数据库
删除xy库
mysql> drop database xy;
Query OK, 13 rows affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xy1 |
| xy2 |
+--------------------+
6 rows in set (0.00 sec)
mysql>
使用source恢复库
mysql> source /opt/backup/xy1.sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xy |
| xy1 |
| xy2 |
+--------------------+
7 rows in set (0.00 sec)
mysql>
（2）使用mysql命令恢复数据库
使用mysql命令恢复数据
mysql -u 用户名 -p [密码]<库备份脚本的路径
mysql命令恢复的示例
mysql -u root -p </backup/all-data.sql
1）重定向方式恢复数据库
删除库xy
mysql> drop database xy;
Query OK, 13 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xy1 |
| xy2 |
+--------------------+
6 rows in set (0.00 sec)
mysql>
重定向恢复xy库
[root@l5 backup]# mysql -u root -pabc123 < /opt/backup/xy1.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@l5 backup]# mysql -u root -pabc123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xy |
| xy1 |
| xy2 |
+--------------------+
[root@l5 backup]#
2） 管道符方式恢复数据库
删除xy库
[root@l5 backup]# mysql -u root -pabc123
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 13
Server version: 5.7.44 Source distribution
Copyright (c) 2000, 2023, 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> drop database xy;
Query OK, 13 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xy1 |
| xy2 |
+--------------------+
6 rows in set (0.00 sec)
mysql>
管道符恢复xy库
[root@l5 backup]# cat /opt/backup/xy1.sql | mysql -u root -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@l5 backup]# mysql -u root -pabc123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xy |
| xy1 |
| xy2 |
+--------------------+
[root@l5 backup]#
（3）恢复表操作
- 恢复表时同样可以使用source或者mysql命令
- source恢复表的操作与恢复库的操作相同
- 当备份文件中只包含表的备份，而不包括创建库的语句时，必须指定库名，且目标库必须存在
mysql -u 用户名 -p[密码]<表备份脚本的路径
mysql -u root -p mysql < /backup/mysql-user.sql
- 在生产环境中，可以使用Shel脚本自动实现定时备份
若只想恢复xy库中的xy表，但库中没有xy库，无法恢复表
[root@l5 backup]# mysql -u root -pabc123
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 16
Server version: 5.7.44 Source distribution
Copyright (c) 2000, 2023, 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> drop database xy;
Query OK, 13 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xy1 |
| xy2 |
+--------------------+
6 rows in set (0.00 sec)
mysql>
需要先创建库再恢复表
[root@l5 backup]# mysql -u root -pabc123 -e 'create database xy;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@l5 backup]# mysql -u root -pabc123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xy |
| xy1 |
| xy2 |
+--------------------+
[root@l5 backup]# mysql -u root -pabc123 xy < /opt/backup/xy_xy.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@l5 backup]# mysql -u root -pabc123 -e 'show tables from xy;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+
| Tables_in_xy |
+--------------+
| home |
| xy011 |
| xy101 |
| xy1010 |
| xy102 |
| xy103 |
| xy104 |
| xy105 |
| xy106 |
| xy107 |
| xy108 |
| xy109 |
| xy88 |
+--------------+
[root@l5 backup]#
七、MySQL增量备份与恢复
增量备份
#通过刷新二进制日志实现增量备份
mysqladmin -u root -p密码 flush-logs
#查看二进制日志内容
mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制文件路径
#使用二进制日志增量恢复
mysqlbinlog --no-defaults 二进制文件路径 | mysql -u root -p密码
（1）二进制日志文件
- MySQL没有提供直接的增量备份方法
- 可通过MySQL提供的二进制日志间接实现增量备份
- MySQL二进制日志对备份的意义
- 二进制日志保存了所有更新或者可能更新数据库的操作
- 二进制日志在启动MySQL服务器后开始记录，并在文件达到max binlog size所设置的大小或者接收到flush logs命令后重新创建新的日志文件
- 只需定时执行fushlogs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份
编辑 /etc/my.cnf 文件内容，末行添加以下内容（192.168.18.50）
[root@l5 backup]# vim /etc/my.cnf
vim /etc/my.cnf
------------在“server-id”行下面添加mysql数据库的日志信息---------
server-id = 1
---------------------------------------------------------------
#错误日志
log-error=/usr/local/mysql/data/mysql_error.log
#通用查询日志
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#二进制日志
log_bin=mysql_bin
#慢查询日志
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=2
重启mysql并查看日志
systemctl restart mysqld
mysql -uroot -pabc123
刷新生成二进制日志文件的两种方式：
（1）重启数据库服务：systemctl restart mysqld;
（2）flush-logs命令刷新：mysqladmin -uroot -pabc123 flush-logs；
二进制日志(binlog)有3种不同的记录格式：
（1）STATEMENT(基于SQL语句)：默认格式是STATEMENT。该方式记录语句快，占用内存空间少。但高并发情况下会导致记录日志顺序紊乱，造成恢复数据时发生偏差。
（2）ROW(基于行)：基于数据内容行进行记录，不仅记录执行的命令语句，还会记录命令影响的相关数据行。
（3）MIXED(混合模式)：高并发情况下ROW方式进行记录，一般情况下采用STATEMENT方式进行记录。
转换格式，查看指定序列号的二进制数据日志文件
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.000003
--no-defaults：不使用默认格式进行查看
--base64-output=decode-rows：使用base64密码格式进行转换，-rows：按行进行输出
-v：显示详细输出过程
测试
设置两台服务器的记录格式为row和statement（192.168.18.50/192.168.18.10）
192.168.18.50（row格式记录了所有修改的内容）
[root@l5 data]# vim /etc/my.cnf
[root@l5 data]#
##末行添加此内容
binlog_format=row
[root@l5 data]# systemctl restart mysqld.service
[root@l5 data]# mysql -u root -pabc123
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.44-log Source distribution
Copyright (c) 2000, 2023, 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> use xy;
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 xy108;
+------+------+------+------+
| id | name | age | sex |
+------+------+------+------+
| 1 | my | 18 | 女 |
| 2 | yf | 88 | 女 |
| 3 | tmt | 99 | 女 |
| 4 | yzl | 800 | 女 |
+------+------+------+------+
4 rows in set (0.00 sec)
mysql> update xy108 set age=21 where name='my';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from xy108;
+------+------+------+------+
| id | name | age | sex |
+------+------+------+------+
| 1 | my | 21 | 女 |
| 2 | yf | 88 | 女 |
| 3 | tmt | 99 | 女 |
| 4 | yzl | 800 | 女 |
+------+------+------+------+
4 rows in set (0.00 sec)
mysql> exit
Bye
[root@l5 data]#
[root@l5 data]# cat mysql_bin.index
./mysql_bin.000001
./mysql_bin.000002
./mysql_bin.000003
./mysql_bin.000004
./mysql_bin.000005
./mysql_bin.000006
[root@l5 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.000006
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240701 22:57:47 server id 1 end_log_pos 123 CRC32 0xbde51cab Start: binlog v 4, server v 5.7.44-log created 240701 22:57:47 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#240701 22:57:47 server id 1 end_log_pos 154 CRC32 0xb024ae82 Previous-GTIDs
# [empty]
# at 154
#240701 23:03:18 server id 1 end_log_pos 219 CRC32 0x8915cc39 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#240701 23:03:18 server id 1 end_log_pos 289 CRC32 0x1c0bd840 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1719846198/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1437073414/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 289
#240701 23:03:18 server id 1 end_log_pos 342 CRC32 0xe2ca3ba1 Table_map: `xy`.`xy108` mapped to number 119
# at 342
#240701 23:03:18 server id 1 end_log_pos 410 CRC32 0x1aa2a135 Update_rows: table id 119 flags: STMT_END_F
### UPDATE `xy`.`xy108`
### WHERE
### @1=1
### @2='my'
### @3=18
### @4='女'
### SET
### @1=1
### @2='my'
### @3=21
### @4='女'
# at 410
#240701 23:03:18 server id 1 end_log_pos 481 CRC32 0x9fa49c40 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1719846198/*!*/;
COMMIT
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@l5 data]#
192.168.18.10（statement格式以SQL语句记录）
[root@l1 ~]# vim /etc/my.cnf
##在文件末行添加内容
log_bin=mysql_bin
binlog_format=statement
[root@l1 ~]# systemctl restart mysqld.service
[root@l1 ~]# mysql -u root -pabc123
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.44-log Source distribution
Copyright (c) 2000, 2023, 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>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xy |
+--------------------+
5 rows in set (0.00 sec)
mysql> use xy;
Database changed
mysql> show tables;
+--------------+
| Tables_in_xy |
+--------------+
| home |
| xy011 |
| xy101 |
| xy1010 |
| xy102 |
| xy103 |
| xy104 |
| xy105 |
| xy106 |
| xy107 |
| xy108 |
| xy109 |
| xy88 |
+--------------+
13 rows in set (0.00 sec)
mysql> select * from xy106;
Empty set (0.00 sec)
mysql> desc xy106;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | NO | PRI | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(2) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> insert into xy106(id, name, sex, age) values(1, 'scj', '男', 20);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from xy106;
+------+------+------+------+
| id | name | age | sex |
+------+------+------+------+
| 1 | scj | 20 | 男 |
+------+------+------+------+
1 row in set (0.00 sec)
mysql> quit
Bye
[root@l1 opt]#
[root@l1 opt]# cd /usr/local/mysql/data
[root@l1 data]# ls
auto.cnf ib_buffer_pool mysql public_key.pem
ca-key.pem ibdata1 mysql_bin.000001 server-cert.pem
ca.pem ib_logfile0 mysql_bin.index server-key.pem
client-cert.pem ib_logfile1 performance_schema sys
client-key.pem ibtmp1 private_key.pem xy
[root@l1 data]# cat mysql_bin.index
./mysql_bin.000001
[root@l1 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240701 23:12:57 server id 1 end_log_pos 123 CRC32 0x5fb3563d Start: binlog v 4, server v 5.7.44-log created 240701 23:12:57 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#240701 23:12:57 server id 1 end_log_pos 154 CRC32 0x87eaf5e0 Previous-GTIDs
# [empty]
# at 154
#240701 23:19:21 server id 1 end_log_pos 219 CRC32 0x4ce07e29 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#240701 23:19:21 server id 1 end_log_pos 294 CRC32 0xcf8959c0 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1719847161/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 294
#240701 23:19:21 server id 1 end_log_pos 429 CRC32 0x01b59e50 Query thread_id=2 exec_time=0 error_code=0
use `xy`/*!*/;
SET TIMESTAMP=1719847161/*!*/;
insert into xy106(id, name, sex, age) values(1, 'scj', '男', 20)
/*!*/;
# at 429
#240701 23:19:21 server id 1 end_log_pos 460 CRC32 0x6da86ec9 Xid = 42
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@l1 data]#
（2）实例：完全备份+增量备份恢复数据库
完全备份+增量备份
准备测试表格
[root@l5 data]# mysql -u root -pabc123
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 3
Server version: 5.7.44-log Source distribution
Copyright (c) 2000, 2023, 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 |
| xy |
| xy1 |
| xy2 |
+--------------------+
7 rows in set (0.00 sec)
mysql> use xy;
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 xy108;
+------+------+------+------+
| id | name | age | sex |
+------+------+------+------+
| 1 | my | 21 | 女 |
| 2 | yf | 88 | 女 |
| 3 | tmt | 99 | 女 |
| 4 | yzl | 800 | 女 |
+------+------+------+------+
4 rows in set (0.00 sec)
mysql>
创建完全备份和增量备份的保存目录
[root@l5 data]# cd ~
[root@l5 ~]# mkdir back-all
[root@l5 ~]# mkdir back-everyday
[root@l5 ~]# ls
anaconda-ks.cfg back-everyday 公共 视频 文档 音乐
back-all initial-setup-ks.cfg 模板 图片 下载 桌面
[root@l5 ~]#
备份数据库xy和库中的表xy108
[root@l5 ~]# mysqldump -u root -pabc123 --databases xy > /root/back-all/test_$(date +%Y%m%d).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@l5 ~]# mysqldump -u root -pabc123 xy xy108 > /root/back-all/test_xy_$(date +%Y%m%d).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@l5 ~]# ls back-all/
test_20240702.sql test_xy_20240702.sql
[root@l5 ~]#
mysql_bin.000005 为现在操作保存位置
[root@l5 ~]# cd /usr/local/mysql/data/
[root@l5 data]# ls
auto.cnf ib_logfile1 mysql_bin.000006 server-cert.pem
ca-key.pem ibtmp1 mysql_bin.index server-key.pem
ca.pem mysql mysql_error.log sys
client-cert.pem mysql_bin.000001 mysql_general.log xy
client-key.pem mysql_bin.000002 mysql_slow_query.log xy1
ib_buffer_pool mysql_bin.000003 performance_schema xy2
ibdata1 mysql_bin.000004 private_key.pem
ib_logfile0 mysql_bin.000005 public_key.pem
[root@l5 data]# mysqladmin -u root -pabc123 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@l5 data]# ls
auto.cnf ib_logfile1 mysql_bin.000006 public_key.pem
ca-key.pem ibtmp1 mysql_bin.000007 server-cert.pem
ca.pem mysql mysql_bin.index server-key.pem
client-cert.pem mysql_bin.000001 mysql_error.log sys
client-key.pem mysql_bin.000002 mysql_general.log xy
ib_buffer_pool mysql_bin.000003 mysql_slow_query.log xy1
ibdata1 mysql_bin.000004 performance_schema xy2
ib_logfile0 mysql_bin.000005 private_key.pem
[root@l5 data]#
备份
[root@l5 data]# cp mysql_bin.000005 /root/back-everyday/mysql_bin.$(date +%Y%m%d)
[root@l5 data]# ls /root/back-everyday/
mysql_bin.20240702
[root@l5 data]#
插入两条数据做增量备份
[root@l5 data]# mysql -u root -pabc123
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 9
Server version: 5.7.44-log Source distribution
Copyright (c) 2000, 2023, 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> use xy;
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 xy108;
+------+------+------+------+
| id | name | age | sex |
+------+------+------+------+
| 1 | my | 21 | 女 |
| 2 | yf | 88 | 女 |
| 3 | tmt | 99 | 女 |
| 4 | yzl | 800 | 女 |
+------+------+------+------+
4 rows in set (0.00 sec)
mysql> insert into xy108(id, name, sex, age) values(4, 'ctt', '女', 20);
Query OK, 1 row affected (0.00 sec)
mysql> insert into xy108(id, name, sex, age) values(6, 'cww', '女', 20);
Query OK, 1 row affected (0.00 sec)
mysql>
刷新生成二进制文件复制到指定目录
[root@l5 data]# mysqladmin -u root -pabc123 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@l5 data]# ls
auto.cnf ib_logfile1 mysql_bin.000006 private_key.pem
ca-key.pem ibtmp1 mysql_bin.000007 public_key.pem
ca.pem mysql mysql_bin.000008 server-cert.pem
client-cert.pem mysql_bin.000001 mysql_bin.index server-key.pem
client-key.pem mysql_bin.000002 mysql_error.log sys
ib_buffer_pool mysql_bin.000003 mysql_general.log xy
ibdata1 mysql_bin.000004 mysql_slow_query.log xy1
ib_logfile0 mysql_bin.000005 performance_schema xy2
[root@l5 data]# cp mysql_bin.000005 /root/back-everyday/mysql_bin.20240625
[root@l5 data]# ls /root/back-everyday/
mysql_bin.20240625 mysql_bin.20240702
[root@l5 data]#
插入第三次数据
mysql> use xy;
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> insert into xy108(id, name, sex, age) values(7, 'zyr', '男', 20);
Query OK, 1 row affected (0.00 sec)
mysql> insert into xy108(id, name, sex, age) values(8, 'tj', '男', 20);
Query OK, 1 row affected (0.00 sec)
mysql> select * from xy108;
+------+------+------+------+
| id | name | age | sex |
+------+------+------+------+
| 1 | my | 21 | 女 |
| 2 | yf | 88 | 女 |
| 3 | tmt | 99 | 女 |
| 4 | yzl | 800 | 女 |
| 4 | ctt | 20 | 女 |
| 6 | cww | 20 | 女 |
| 7 | zyr | 20 | 男 |
| 8 | tj | 20 | 男 |
+------+------+------+------+
8 rows in set (0.00 sec)
刷新生成二进制文件复制到指定目录
[root@l5 data]# mysqladmin -u root -pabc123 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@l5 data]# cp mysql_bin.000007 /root/back-everyday/mysql_bin.20240626
[root@l5 data]#
插入第三次数据
mysql> use xy;
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> insert into xy108(id, name, sex, age) values(9, 'zyr', '男', 20);
Query OK, 1 row affected (0.00 sec)
mysql> insert into xy108(id, name, sex, age) values(10, 'tj', '男', 20);
Query OK, 1 row affected (0.01 sec)
mysql> select * from xy108;
+------+------+------+------+
| id | name | age | sex |
+------+------+------+------+
| 1 | my | 21 | 女 |
| 2 | yf | 88 | 女 |
| 3 | tmt | 99 | 女 |
| 4 | yzl | 800 | 女 |
| 4 | ctt | 20 | 女 |
| 6 | cww | 20 | 女 |
| 7 | zyr | 20 | 男 |
| 8 | tj | 20 | 男 |
| 9 | zyr | 20 | 男 |
| 10 | tj | 20 | 男 |
+------+------+------+------+
10 rows in set (0.00 sec)
mysql>
刷新生成二进制文件复制到指定目录
[root@l5 data]# mysqladmin -u root -pabc123 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@l5 data]# ls
auto.cnf ibtmp1 mysql_bin.000008 public_key.pem
ca-key.pem mysql mysql_bin.000009 server-cert.pem
ca.pem mysql_bin.000001 mysql_bin.000010 server-key.pem
client-cert.pem mysql_bin.000002 mysql_bin.index sys
client-key.pem mysql_bin.000003 mysql_error.log xy
ib_buffer_pool mysql_bin.000004 mysql_general.log xy1
ibdata1 mysql_bin.000005 mysql_slow_query.log xy2
ib_logfile0 mysql_bin.000006 performance_schema
ib_logfile1 mysql_bin.000007 private_key.pem
[root@l5 data]# cp mysql_bin.000007 /root/back-everyday/mysql_bin.2024062
7
[root@l5 data]#
完全恢复+增量恢复
删除xy库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xy |
| xy1 |
| xy2 |
+--------------------+
7 rows in set (0.01 sec)
mysql> drop database xy;
Query OK, 13 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xy1 |
| xy2 |
+--------------------+
6 rows in set (0.00 sec)
mysql>
将库数据恢复
[root@l5 data]# mysql -u root -pabc123 < /root/back-all/test_20240702.sqlmysql: [Warning] Using a password on the command line interface can be insecure.
[root@l5 data]# mysql -u root -pabc123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xy |
| xy1 |
| xy2 |
+--------------------+
[root@l5 data]# mysql -u root -pabc123 -e 'select * from xy.xy108;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+------+------+
| id | name | age | sex |
+------+------+------+------+
| 1 | my | 21 | 女 |
| 2 | yf | 88 | 女 |
| 3 | tmt | 99 | 女 |
| 4 | yzl | 800 | 女 |
+------+------+------+------+
[root@l5 data]#
增量恢复
[root@l5 data]# cd /root/back-everyday/
[root@l5 back-everyday]# ls
mysql_bin.20240625 mysql_bin.20240627
mysql_bin.20240626 mysql_bin.20240702
[root@l5 back-everyday]# mysqlbinlog --no-defaults mysql_bin.20240625 | mysql -u root -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@l5 back-everyday]# mysql -u root -pabc123 -e 'select * from xy.xy108;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+------+------+
| id | name | age | sex |
+------+------+------+------+
| 1 | my | 21 | 女 |
| 2 | yf | 88 | 女 |
| 3 | tmt | 99 | 女 |
| 4 | yzl | 800 | 女 |
+------+------+------+------+
[root@l5 back-everyday]# mysqlbinlog --no-defaults mysql_bin.20240626 | mysql -u root -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@l5 back-everyday]# mysql -u root -pabc123 -e 'select * from xy.xy108;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+------+------+
| id | name | age | sex |
+------+------+------+------+
| 1 | my | 21 | 女 |
| 2 | yf | 88 | 女 |
| 3 | tmt | 99 | 女 |
| 4 | yzl | 800 | 女 |
| 4 | ctt | 20 | 女 |
| 6 | cww | 20 | 女 |
+------+------+------+------+
[root@l5 back-everyday]#
（3）断点恢复
删除id>=4（mysql_bin.20240626 为上个实验第三次插入数据id为6、7的增量备份文件）
[root@l5 back-everyday]# mysql -u root -pabc123 -e 'select * from xy.xy108;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+------+------+
| id | name | age | sex |
+------+------+------+------+
| 1 | my | 21 | 女 |
| 2 | yf | 88 | 女 |
| 3 | tmt | 99 | 女 |
| 4 | yzl | 800 | 女 |
| 4 | ctt | 20 | 女 |
| 6 | cww | 20 | 女 |
+------+------+------+------+
[root@l5 back-everyday]# mysql -u root -pabc123 -e 'delete from xy.xy108 where id>=4;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@l5 back-everyday]# mysql -u root -pabc123 -e 'select * from xy.xy108;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+------+------+
| id | name | age | sex |
+------+------+------+------+
| 1 | my | 21 | 女 |
| 2 | yf | 88 | 女 |
| 3 | tmt | 99 | 女 |
+------+------+------+------+
[root@l5 back-everyday]# cd /root/back-everyday/
[root@l5 back-everyday]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.20240626 > binlog-0626
[root@l5 back-everyday]# ls
binlog-0626 mysql_bin.20240626 mysql_bin.20240702
mysql_bin.20240625 mysql_bin.20240627
[root@l5 back-everyday]# 八、断点恢复：
基于位置点恢复
mysqlbinlog --no-defaults --start-position='开始位置点' --stop-position='结束位置点' 二进制文件路径 | mysql -u root -p密码基于时间点恢复
mysqlbinlog --no-defaults --start-datetime='YYYY-mm-dd HH:MM:SS' --stop-datetime='YYYY-mm-dd HH:MM:SS' 二进制文件路径 | mysql -u root -p密码模拟实验删除id>=7要求只恢复id为8-9的值
[root@l5 back-everyday]# mysql -u root -pabc123 -e 'select * from xy.xy108;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+------+------+
| id | name | age | sex |
+------+------+------+------+
| 1 | my | 21 | 女 |
| 2 | yf | 88 | 女 |
| 3 | tmt | 99 | 女 |
| 6 | c | 20 | 男 |
| 5 | b | 20 | 男 |
| 4 | aa | 20 | 男 |
| 7 | d | 20 | 男 |
| 8 | e | 20 | 男 |
| 9 | f | 20 | 男 |
+------+------+------+------+
[root@l5 back-everyday]# mysql -u root -pabc123 -e 'delete from xy.xy108 where id >=7';
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@l5 back-everyday]# mysql -u root -pabc123 -e 'select * from xy.xy108;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+------+------+
| id | name | age | sex |
+------+------+------+------+
| 1 | my | 21 | 女 |
| 2 | yf | 88 | 女 |
| 3 | tmt | 99 | 女 |
| 6 | c | 20 | 男 |
| 5 | b | 20 | 男 |
| 4 | aa | 20 | 男 |
+------+------+------+------+
[root@l5 back-everyday]#
[root@l5 back-everyday]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.20240627 > binlog-0626
[root@l5 back-everyday]# ls
binlog-0626 mysql_bin.20240625 mysql_bin.20240626 mysql_bin.20240627 mysql_bin.20240702
[root@l5 back-everyday]#
1）基于位置恢复
mysqlbinlog --no-defaults --start-position='开始位置点' --stop-position='结束位置点' 二进制文件路径 | mysql -u root -p密码只恢复id为7的内容
左边是查看 binlog-0626 文件的内容
只恢复id为6的内容
删除刚恢复的第七行内容
[root@zx1 back-everyday]# mysql -u root -pabc123 -e 'delete from zx.zx where id=7;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@zx1 back-everyday]# mysql -u root -pabc123 -e 'select * from zx.zx;' mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+------+------+
| id | name | sex | age |
+----+------+------+------+
| 1 | scj | 男 | 20 |
| 2 | zx | 男 | 21 |
| 3 | tc | 男 | 20 |
+----+------+------+------+
[root@zx1 back-everyday]#恢复第6行内容
增量备份数据多的时候，可以 --start-position='开始位置' --stop-position='结束位置' 来恢复指定的一段数据
2）基于时间恢复
只恢复id为7的内容
只恢复id为6的内容
增量备份数据多的时候，可以 --start-datetime='开始时间' --stop-datetime='结束时间' 来恢复指定的一段数据
5、如何备份的数据库
通过xtrabackup对所有数据库做完全备份，使用mysqldump针对某个库或者某个表做完全备份，还可以通过二进制或xtrabackup来做增量备份。
九、断点恢复的原则：
如果要恢复到某条sql语句之前的所有数据，就stop在这个语句的位置点或时间点之前
如果要恢复某条sql语句及其之后的所有数据，就从这个语句的位置点或时间点开始start
扫一扫
4299
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
