文章目录
MySQL日志
MySQL 日志管理对于数据库管理员（DBA）和开发人员来说是至关重要的，因为它不仅可以帮助他们监控数据库的运行状态，还能在数据库出现问题时提供调试和恢复的关键信息。
1. 错误日志（Error Log）
- 作用：记录当 MySQL 启动、停止或运行时发生的错误信息。
- 默认状态：默认已开启。
- 配置：通过
log-error参数指定日志的保存位置和文件名。[mysqld] log-error=/usr/local/mysql/data/mysql_error.log - 查看：错误日志通常不需要手动查看，但当数据库启动失败或运行中出现严重错误时，DBA 会查看此日志以诊断问题。
2. 通用查询日志（General Query Log）
- 作用：记录 MySQL 的所有连接和语句，包括每个客户端的连接、断开以及执行的 SQL 语句。
- 默认状态：默认是关闭的。
- 配置：通过
general_log和general_log_file参数开启并指定日志文件。[mysqld] general_log=ON general_log_file=/usr/local/mysql/data/mysql_general.log - 查看：通过 SQL 命令
SHOW VARIABLES LIKE 'general%';可以检查通用查询日志是否开启。 - 用途：主要用于调试和审计，但会记录大量信息，可能影响性能，因此不建议在生产环境中长期开启。
3. 二进制日志（Binary Log, Binlog）
- 作用：记录所有更新了数据或者已经潜在更新了数据的语句（如
INSERT、UPDATE、DELETE等），以及数据定义语句（如CREATE TABLE、ALTER TABLE等）。用于数据恢复和复制。 - 默认状态：默认已开启，但文件名和位置可能因安装而异。
- 配置：通过
log-bin参数指定二进制日志文件的名称前缀。[mysqld] log-bin=mysql-bin - 查看：通过 SQL 命令
SHOW VARIABLES LIKE 'log_bin%';可以检查二进制日志是否开启及其配置。 - 用途：是 MySQL 复制和数据恢复的基础。
4. 慢查询日志（Slow Query Log）
- 作用：记录所有执行时间超过
long_query_time秒的语句，帮助识别和优化性能低下的查询。 - 默认状态：默认是关闭的。
- 配置：通过
slow_query_log、slow_query_log_file和long_query_time参数开启并配置慢查询日志。[mysqld] slow_query_log=ON slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log long_query_time=5 - 查看：通过 SQL 命令
SHOW VARIABLES LIKE '%slow%';和SHOW VARIABLES LIKE 'long_query_time';可以检查慢查询日志是否开启及其配置。 - 用途：性能调优，识别和优化执行时间长的查询。
注意
重启 MySQL 服务
配置修改后，需要重启 MySQL 服务使配置生效：
systemctl restart mysqld
动态修改配置
某些配置（如慢查询日志）可以在 MySQL 运行时通过 SQL 命令动态修改，而无需重启服务：
SET GLOBAL slow_query_log = ON;
注意，动态修改的配置在 MySQL 服务重启后会失效，要永久生效还需修改配置文件。
当然，下面是对您提供的 MySQL 命令和查询的详细讲解：
MySQL日志相关查询指令
1. 登录 MySQL
mysql -u root -p
这个命令用于登录 MySQL 数据库服务器。-u root 指定使用 root 用户进行登录，-p 会提示您输入该用户的密码。成功登录后，您将能够执行 SQL 命令来管理和查询数据库。
2. 查看通用查询日志是否开启
SHOW VARIABLES LIKE 'general%';
这个 SQL 查询用于查看与通用查询日志（General Query Log）相关的系统变量。general_log 变量表示通用查询日志是否开启（ON 或 OFF），general_log_file 变量表示日志文件的路径。
- 如果
general_log的值为ON，则表示通用查询日志已开启。 - 如果
general_log的值为OFF，则表示通用查询日志未开启。
3. 查看二进制日志是否开启
SHOW VARIABLES LIKE 'log_bin%';
这个 SQL 查询用于查看与二进制日志（Binary Log）相关的系统变量。log_bin 变量表示二进制日志是否开启（如果设置了值，则表示开启；如果为空，则表示未开启），log_bin_basename 和 log_bin_index 变量分别表示二进制日志文件的基本名称和索引文件的路径（这些变量可能在某些 MySQL 版本中有所不同，但 log_bin 是关键变量）。
- 如果
log_bin有设置值（如mysql-bin），则表示二进制日志已开启。 - 如果
log_bin为空，则表示二进制日志未开启。
4. 查看慢查询日志是否开启
SHOW VARIABLES LIKE '%slow%';
这个 SQL 查询用于查看与慢查询日志（Slow Query Log）相关的系统变量。slow_query_log 变量表示慢查询日志是否开启（ON 或 OFF），slow_query_log_file 变量表示日志文件的路径。
- 如果
slow_query_log的值为ON，则表示慢查询日志已开启。 - 如果
slow_query_log的值为OFF，则表示慢查询日志未开启。
5. 查看慢查询时间设置
SHOW VARIABLES LIKE 'long_query_time';
这个 SQL 查询用于查看 long_query_time 系统变量的值，它表示慢查询的阈值时间（秒）。只有执行时间超过这个阈值的查询才会被记录到慢查询日志中。
long_query_time的值可以是任何正数，表示秒数。- 默认值可能是 10 秒，但可以根据需要进行调整。
6. 在数据库中设置开启慢查询日志
SET GLOBAL slow_query_log = ON;
这个 SQL 命令用于在 MySQL 运行时动态地开启慢查询日志。注意，这个更改是临时的，仅在当前 MySQL 服务器会话期间有效。要永久地开启慢查询日志，您需要修改 MySQL 的配置文件（如 my.cnf 或 my.ini），并重启 MySQL 服务。
- 执行此命令后，您可以通过再次运行
SHOW VARIABLES LIKE 'slow_query_log';来验证慢查询日志是否已开启。
数据库备份的分类
一、备份类型
-
物理备份
- 定义：直接复制数据库的物理文件，包括数据文件、日志文件、配置文件等。
- 优点：备份和恢复操作相对简单，恢复速度快，能够跨MySQL版本进行恢复。
- 缺点：需要在数据库离线或只读状态下进行，可能影响业务连续性。
- 适用场景：适用于对数据库一致性要求较高，且可以接受短暂停机时间的场景。
-
逻辑备份
- 定义：导出数据库对象（如库、表）的数据和结构，生成SQL语句或其他格式的备份文件。
- 优点：备份结果为ASCII文件，可以编辑；与存储引擎无关，可以通过网络进行备份和恢复。
- 缺点：备份和恢复过程需要MySQL服务器进程参与，可能占用较多时间和空间；浮点数可能会丢失精度；恢复后索引需要重建。
- 适用场景：适用于对数据库一致性要求不是特别高，且需要跨平台或跨版本恢复数据的场景。
二、备份策略
-
完全备份（全量备份）
- 定义：每次都备份完整的库或表数据。
- 优点：恢复时简单直接，只需恢复一个完整的备份文件即可。
- 缺点：占用存储空间大，备份时间长。
- 适用场景：适用于数据量不大，或可以接受较长备份时间的场景。
-
差异备份
- 定义：只备份上一次完全备份后的更新数据。
- 优点：相对于完全备份，差异备份可以节省存储空间和时间。
- 缺点：恢复时需要先恢复完全备份，再恢复差异备份，过程相对复杂。
- 适用场景：适用于数据量较大，且需要频繁备份的场景。
-
增量备份
- 定义：只备份上一次完全备份或增量备份后的更新数据，即每次都只备份最新的数据。
- 优点：进一步节省存储空间和时间，且恢复时只需恢复完全备份和最近的增量备份。
- 缺点：恢复过程相对复杂，需要按照备份顺序依次恢复。
- 适用场景：适用于数据量非常大，且需要频繁备份和恢复的场景。
三、备份工具
-
tar等压缩打包工具
- 用于物理冷备，即将数据库文件压缩打包成备份文件。
- 优点：操作简单，恢复速度快。
- 缺点：需要在数据库离线状态下进行，可能影响业务连续性。
-
MySQL自带的备份工具
- mysqldump：用于逻辑备份，可以生成包含SQL语句的备份文件。支持完全备份、部分备份（如指定数据库、表、数据或结构等）。
- mysqlhotcopy：用于物理热备，但仅支持MyISAM和ARCHIVE表。通过复制数据库文件来创建备份。
-
刷新二进制日志
- 用于增量备份，通过记录二进制日志的位置来实现增量数据的备份和恢复。
-
第三方备份工具Percona XtraBackup（PXB）
- 用于物理热备，支持完全备份和增量备份。
- 优点：备份过程快速可靠，不会打断正在执行的事务；支持在线备份，无需停止数据库服务；提供自动备份验证功能，确保备份数据的可用性。
- 缺点：相对于MySQL自带的备份工具，可能需要额外的安装和配置。
MySQL完全备份与恢复
一、创建示例数据库和表
-- 切换到xy101数据库（如果数据库不存在，需要先创建）
use xy101;
-- 如果info1表不存在，则创建该表
create table if not exists info1 (
id int(4) not null auto_increment, -- 自增主键
name varchar(10) not null, -- 用户名
sex char(10) not null, -- 性别
hobby varchar(50), -- 爱好
primary key (id) -- 主键约束
);
-- 向info1表中插入两条记录
insert into info1 values(1,'user1','male','running');
insert into info1 values(2,'user2','female','singing');
二、MySQL完全备份
1. 物理冷备份与恢复
物理冷备份是在数据库停止服务的情况下进行的备份，因为此时数据库文件不会被修改，保证了数据的一致性。
# 停止MySQL服务
systemctl stop mysqld
# 安装xz压缩工具（如果未安装）
yum -y install xz
# 使用tar命令进行压缩备份，将/usr/local/mysql/data/目录下的所有文件压缩成.tar.xz格式的备份文件
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
# 解压恢复过程（模拟）
# 首先将/usr/local/mysql/data/目录移动到其他位置（模拟数据丢失）
mv /usr/local/mysql/data/ ~
# 然后解压备份文件到/usr/local/mysql/目录下
tar Jxvf /opt/mysql_all_2020-11-22.tar.xz -C /usr/local/mysql/
# 注意：这里的日期“2020-11-22”应该替换为实际的备份文件日期
# 最后将解压后的数据目录移动回原位置（恢复完成）
mv /opt/usr/local/mysql/data/ /usr/local/mysql/
# 注意：这里的路径可能需要根据实际情况进行调整，上面的命令中路径多了一个“/opt/”，应去除
注意：物理备份恢复时，需要确保MySQL的数据目录是空的，或者与备份文件中的数据目录结构一致。
2. mysqldump备份与恢复
mysqldump是MySQL自带的逻辑备份工具，它可以导出数据库的结构和数据，生成SQL脚本文件。
# 完全备份一个或多个完整的库
mysqldump -u root -p --databases xy101 > /opt/xy101.sql
# 这里需要输入root用户的密码，然后mysqldump会将xy101库导出到/opt/xy101.sql文件中
# 完全备份MySQL服务器中所有的库
mysqldump -u root -p --all-databases > /opt/all.sql
# 这将导出MySQL服务器上所有的数据库到/opt/all.sql文件中
# 完全备份指定库中的部分表
mysqldump -u root -p xy101 info1 info2 > /opt/xy101_info1.sql
# 这将导出xy101库中的info1和info2表到/opt/xy101_info1.sql文件中
# 如果使用“-d”选项，则只导出表结构，不导出数据
# 查看备份文件内容（过滤掉注释行和空行）
grep -v "^--" /opt/xy101_info1.sql | grep -v "^/" | grep -v "^$"
三、MySQL完全恢复
恢复数据库之前，需要确保MySQL服务已经启动。
# 启动MySQL服务
systemctl start mysqld
# 恢复数据库（先删除原数据库，再导入备份文件）
mysql -u root -p -e 'drop database xy101;'
# 这里需要输入root用户的密码，然后执行删除数据库的操作
# 查看当前数据库列表（确认数据库已被删除）
mysql -u root -p -e 'SHOW DATABASES;'
# 导入备份文件恢复数据库
mysql -u root -p < /opt/xy101.sql
# 这里需要输入root用户的密码，然后mysqldump会将/opt/xy101.sql文件中的SQL语句执行到MySQL服务器上，恢复数据库
# 再次查看当前数据库列表（确认数据库已恢复）
mysql -u root -p -e 'SHOW DATABASES;'
# 恢复数据表（如果备份文件中只包含表的备份，不包含库的备份）
# 首先删除原数据表（确保目标库已存在）
mysql -u root -p -e 'drop table xy101.info1;'
# 查看xy101库中的表列表（确认表已被删除）
mysql -u root -p -e 'show tables from xy101;'
# 导入备份文件恢复数据表
mysql -u root -p xy101 < /opt/xy101_info1.sql
# 这里需要指定数据库名xy101，因为备份文件中只包含了表的备份，不包含库的备份
# 再次查看xy101库中的表列表（确认表已恢复）
mysql -u root -p -e 'show tables from xy101;'
注意：在恢复数据库或数据表之前，建议先备份当前的数据（如果需要的话），以防万一恢复过程中出现问题导致数据丢失。同时，恢复操作需要谨慎执行，确保备份文件正确无误，且目标数据库或表已正确删除（如果需要的话）。
MySQL增量备份与恢复
MySQL 增量备份
1. 开启二进制日志功能
二进制日志（binlog）是MySQL的增量备份基础，它记录了所有更改数据库数据的SQL语句。
# 编辑MySQL配置文件
vim /etc/my.cnf
# 在[mysqld]部分添加以下配置
[mysqld]
log-bin=mysql-bin # 启用二进制日志，并指定日志文件名前缀
binlog_format = MIXED # 可选，指定二进制日志的记录格式为MIXED（混合模式），包括STATEMENT和ROW两种格式的优点
server-id = 1 # 设置服务器ID，用于主从复制等场景
# 保存并退出编辑器
# 重启MySQL服务以应用配置
systemctl start mysqld
# 检查二进制日志文件是否生成
ls -l /usr/local/mysql/data/mysql-bin.*
2. 进行完全备份
在进行增量备份之前，建议先进行完全备份，以便在需要时可以恢复到某个完整的状态
# 对xy101数据库中的info1表进行完全备份
mysqldump -u root -p xy101 info1 > /opt/xy101_info1_$(date +%F).sql
# 对xy101数据库进行完全备份
mysqldump -u root -p --databases xy101 > /opt/xy101_$(date +%F).sql
3. 生成新的二进制日志文件
通过刷新日志，可以生成新的二进制日志文件，从而记录之后的数据库更改。
# 刷新二进制日志
mysqladmin -u root -p flush-logs
4. 插入新数据
模拟数据的增加或变更。
# 切换到xy101数据库
use xy101;
# 插入新数据
insert into info1 values(3,'user3','male','game');
insert into info1 values(4,'user4','female','reading');
5. 再次生成新的二进制日志文件
再次刷新日志，以记录更多的数据库更改。
# 再次刷新二进制日志
mysqladmin -u root -p flush-logs
6. 查看二进制日志文件的内容
使用mysqlbinlog工具查看二进制日志文件的内容。
# 将二进制日志文件复制到/opt/目录
cp /usr/local/mysql/data/mysql-bin.000002 /opt/
# 使用mysqlbinlog查看日志文件内容
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
MySQL 增量恢复
1. 一般恢复
（1）模拟丢失更改的数据的恢复步骤：
# 切换到xy101数据库
use xy101;
# 删除两条记录以模拟数据丢失
delete from info1 where id=3;
delete from info1 where id=4;
使用mysqlbinlog恢复丢失的数据：
# 使用mysqlbinlog从二进制日志中恢复数据
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p
（2）模拟丢失所有数据的恢复步骤：
# 删除info1表以模拟数据完全丢失
drop table info1;
首先使用完全备份恢复数据，然后使用二进制日志恢复增量数据：
# 使用完全备份恢复数据
mysql -u root -p xy101 < /opt/xy101_info1_2020-11-22.sql
# 使用mysqlbinlog从二进制日志中恢复增量数据
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p
2. 断点恢复
通过指定位置或时间点，可以实现更精细的恢复。
（1）基于位置恢复：
# 仅恢复到操作ID为“623”之前的数据（不恢复user4的数据）
mysqlbinlog --no-defaults --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p
# 仅恢复user4的数据（跳过user3的数据恢复）
mysqlbinlog --no-defaults --start-position='623' /opt/mysql-bin.000002 | mysql -uroot -p
（2）基于时间点恢复：
# 仅恢复到16:41:24之前的数据（不恢复user4的数据）
mysqlbinlog --no-defaults --stop-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 | mysql -uroot -p
# 仅恢复user4的数据（跳过user3的数据恢复）
mysqlbinlog --no-defaults --start-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 | mysql -uroot -p
注意：在进行恢复操作时，请确保已经备份了当前的数据（如果需要的话），以防万一恢复过程中出现问题导致数据进一步丢失。同时，恢复操作需要谨慎执行，确保二进制日志文件正确无误，且目标数据库已处于适当的状态（例如，完全备份已恢复，或表已存在但数据已删除）。
PXB备份与恢复
下载并安装Percona XtraBackup
# 使用wget下载Percona XtraBackup的RPM包
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
# 使用yum安装下载的RPM包
yum install -y percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
XtraBackup工具简介
XtraBackup主要包含两个工具：
xtrabackup：用于热备份InnoDB和XtraDB表数据，不支持其他类型的表或数据表结构。innobackupex：是xtrabackup的Perl封装脚本，支持备份MyISAM表。
常用选项说明
--host: 指定MySQL服务器的主机名或IP地址。
--user: 指定连接MySQL服务器的用户名。
--password: 指定连接MySQL服务器的密码。
--port: 指定MySQL服务器的端口号。
--database: 指定要备份的数据库。
--incremental: 创建增量备份。
--incremental-basedir: 指定包含完整备份的目录。
--incremental-dir: 指定包含增量备份的目录。
--apply-log: 对备份进行预处理操作，应用日志以准备恢复。
创建最小权限备份用户
# 创建一个名为bkuser的用户，密码为bk123456
create user 'bkuser'@'localhost' identified by 'bk123456';
flush privileges; # 刷新授权表
# 回收新用户所有权限
revoke all privileges,grant option from 'bkuser'@'localhost';
flush privileges; # 再次刷新授权表
# 授予必要的权限：刷新权限、锁表、查看服务器状态、进程
grant reload,lock tables,replication client,process ON *.* TO 'bkuser'@'localhost';
flush privileges; # 最后一次刷新授权表
备份操作
备份所有数据库，自动生成日期命名的文件夹
# 创建备份目录
mkdir -p /data/backup/
# 使用innobackupex进行备份，备份文件会放在/data/backup/下以当前时间命名的目录中
innobackupex --defaults-file=/etc/my.cnf --user=bkuser --password=bk123456 --socket=/usr/local/mysql/mysql.sock /data/backup/
备份所有数据库，指定目录名称
# 直接指定备份目录，不使用时间戳
innobackupex --defaults-file=/etc/my.cnf --user=bkuser --password=bk123456 --socket=/usr/local/mysql/mysql.sock --no-timestamp /data/backup/full/
压缩打包备份
# 创建临时目录用于存放压缩前的备份
mkdir /data/backup/temp
# 使用innobackupex进行备份并通过管道传递给gzip进行压缩
innobackupex --defaults-file=/etc/my.cnf --user=bkuser --password=bk123456 --socket=/usr/local/mysql/mysql.sock --stream=tar --no-timestamp /data/backup/temp | gzip > /data/backup/temp/backup.tar.gz
备份到远程主机并压缩
# 配置SSH密钥认证
ssh-keygen
ssh-copy-id 192.168.80.14
# 使用innobackupex进行备份并通过SSH传输到远程主机进行压缩
innobackupex --defaults-file=/etc/my.cnf --user=bkuser --password=bk123456 --socket=/usr/local/mysql/mysql.sock --stream=tar /data/backup | ssh [email protected] "gzip > /opt/backup.tar.gz"
备份指定数据库或表
# 备份指定的数据库
innobackupex --defaults-file=/etc/my.cnf --user=bkuser --password=bk123456 --socket=/usr/local/mysql/mysql.sock --databases="mydb" --no-timestamp /data/backup/mydb_database
# 备份不同库下的不同表
innobackupex --defaults-file=/etc/my.cnf --user=bkuser --password=bk123456 --socket=/usr/local/mysql/mysql.sock --databases="mydb01.test01 mydb02.test02" --no-timestamp /data/backup/mydb_test_tables
# 备份指定库下以特定字母开头的表
innobackupex --defaults-file=/etc/my.cnf --user=bkuser --password=bk123456 --socket=/usr/local/mysql/mysql.sock --include="mydb.xy" --no-timestamp /data/backup/mydb_xy_tables
增量备份
# 完整备份
innobackupex --defaults-file=/etc/my.cnf --user=bkuser --password=bk123456 --socket=/usr/local/mysql/mysql.sock --no-timestamp /data/backup/full/
# 基于完整备份的增量备份
innobackupex --defaults-file=/etc/my.cnf --user=bkuser --password=bk123456 --socket=/usr/local/mysql/mysql.sock --no-timestamp --incremental /data/backup/increment --incremental-basedir=/data/backup/full/
完全恢复
# 停止MySQL服务
systemctl stop mysqld
# 移动原数据目录
mv /usr/local/mysql/data /usr/local/mysql/data_bak1
# 准备备份数据（应用日志）
innobackupex --apply-log /data/backup/full/
# 检查备份状态，确保为full-prepared
cat /data/backup/full/xtrabackup_checkpoints
# 恢复数据
innobackupex --defaults-file=/etc/my.cnf --copy-back /data/backup/full/
# 修改数据目录的拥有者为mysql用户
chown -R mysql:mysql /usr/local/mysql/data
# 启动MySQL服务
systemctl start mysqld
增量恢复
1. 停止 MySQL 服务
# 停止 MySQL 服务以避免数据写入导致的不一致
systemctl stop mysqld
2. 备份当前数据目录
# 将当前的数据目录移动到一个备份位置，以防恢复失败时能够恢复原始数据
mv /usr/local/mysql/data /usr/local/mysql/data_bak2
3. 合并增量备份到全量备份
# 应用全量备份的日志，使其准备好接受增量备份
innobackupex --apply-log --redo-only /data/backup/full/
# 将第一个增量备份应用到全量备份上
innobackupex --apply-log --redo-only /data/backup/full/ --incremental-dir=/data/backup/increment/
# 注意：如果有多个增量备份，需要按照时间顺序逐个应用
4. 完成数据的预备操作
# 最后一个增量备份应用完成后，进行最终的数据预备操作，使数据文件完全一致
innobackupex --apply-log /data/backup/full/
5. 恢复数据
# 将预备好的数据复制回数据目录
innobackupex --defaults-file=/etc/my.cnf --copy-back /data/backup/full/
# 更改数据目录的拥有者为 MySQL 用户
chown -R mysql:mysql /usr/local/mysql/data
# 启动 MySQL 服务
systemctl start mysqld
xtrabackup 备份数据库流程
1. 执行全量备份
# 使用 xtrabackup 工具执行数据库的全量备份
xtrabackup --backup --user=bkuser --password=bk123456 --port=3306 --target-dir=/data/backup/full/
# 这里的 --user, --password, --port 分别指定了数据库用户名、密码和端口号，--target-dir 指定了备份存放的目录
xtrabackup 恢复数据库流程
1. 停止 MySQL 服务
# 停止 MySQL 服务以避免数据写入导致的不一致
systemctl stop mysqld
2. 备份当前数据目录
# 将当前的数据目录移动到一个备份位置，以防恢复失败时能够恢复原始数据
mv /usr/local/mysql/data /usr/local/mysql/data_bak3
3. 准备备份数据
# 对备份数据进行准备操作，使其可以被恢复
xtrabackup --prepare --target-dir=/data/backup/full/
4. 恢复数据
# 将准备好的数据复制回数据目录
xtrabackup --copy-back --target-dir=/data/backup/full/
# 更改数据目录的拥有者为 MySQL 用户
chown -R mysql:mysql /usr/local/mysql/data
# 启动 MySQL 服务
systemctl start mysqld
要点总结
备份类型
| 备份类型 | 描述 |
|---|---|
| 物理备份 | 直接对数据库的物理文件（数据文件、日志文件等）进行备份 |
| 逻辑备份 | 对数据库对象（库、表）的数据以SQL语句的形式导出进行备份 |
备份策略
| 备份策略 | 描述 |
|---|---|
| 完全备份 | 每次都备份完整的库或表数据 |
| 差异备份 | 只备份上一次完全备份后的更新数据 |
| 增量备份 | 只备份上一次完全备份或增量备份后的更新数据 |
备份工具
| 备份工具 | 备份类型 | 描述 |
|---|---|---|
| tar | 完全备份，物理冷备 | 使用tar等方式压缩打包数据库文件 |
| mysqldump | 完全备份，逻辑热备 | MySQL自带的备份工具，导出数据库对象的数据为SQL语句 |
| mysqlhotcopy | 完全备份，逻辑热备 | MySQL自带的备份工具，仅支持MyISAM和ARCHIVE表 |
| 刷新二进制日志 | 增量备份 | 通过刷新二进制日志实现增量备份 |
| Percona XtraBackup | 完全备份、增量备份，物理热备 | 第三方备份工具，支持完全备份和增量备份，且为物理热备 |
完全备份
物理冷备：
- 先关闭mysql数据库
systemctl stop mysqld
- 使用tar命令压缩打包备份数据库的数据目录和文件（看mysql配置文件中的 datadir 配置参数）
恢复：
使用tar命令解压备份文件压缩包，将数据目录进行替换
mysqldump逻辑热备
| 命令 | 功能 | 备注 |
|---|---|---|
mysqldump -u 用户 -p密码 --databases 库1 [库2 ....] > XXX.sql | 备份一个或多个指定的库及库中所有的表 | 备份指定数据库及其所有表 |
mysqldump -u 用户 -p密码 --all-databases > XXX.sql | 备份所有库 | 备份MySQL服务器中所有数据库 |
mysqldump -u 用户 -p密码 库名 > XXX.sql | 只备份指定库中的所有表（不包含库对象本身） | 备份指定数据库中的所有表，不包括创建数据库的语句 |
mysqldump -u 用户 -p密码 库名 表1 [表2 ....] > XXX.sql | 只备份指定库中的一个或多个指定的表（不包含库对象本身） | 备份指定数据库中的特定表，不包括创建数据库的语句 |
使用说明
-u 用户：指定连接MySQL的用户名。-p密码：指定连接MySQL的密码（注意：实际使用时，-p后面直接跟密码可能会带来安全风险，建议使用-p后空格再输入密码，或者使用配置文件等方式来管理密码）。--databases 库名1 [库名2] …：指定要备份的一个或多个数据库名称。--all-databases：备份所有数据库。库名：指定要备份的数据库名称。表1 [表2 …]：指定要备份的表名称。> XXX.sql：将备份数据输出到指定的SQL文件中。-d：只备份表结构-F：备份后生成新的二进制日志文件--lock-tables：备份前锁定表
以下是对MySQL数据库恢复方法的整理，分为两个表格，分别对应两种恢复方法：
恢复
方法一：使用命令行导入
| 命令 | 功能 | 备注 |
|---|---|---|
mysql -u 用户 -p密码 [库名] < XXX.sql | 恢复数据库或表 | 将SQL文件的内容导入到数据库中 |
| `cat XXX.sql | mysql -u 用户 -p密码 [库名]` | 恢复数据库或表 |
方法二：使用MySQL客户端执行
| 步骤 | 命令 | 功能 | 备注 |
|---|---|---|---|
| 1 | 登录MySQL | 连接到MySQL服务器 | 使用mysql -u 用户 -p密码命令登录 |
| 2 | use 库名 | 切换到目标数据库 | 如果SQL文件只包含表结构或数据，需要先切换到目标数据库 |
| 3 | source XXX.sql文件路径 | 执行SQL文件 | 在当前数据库上下文中执行SQL文件，恢复表结构或数据 |
- 对于方法一，可以直接在命令行中使用
mysql命令将SQL文件的内容导入到数据库中。如果指定了库名，则数据将被导入到该库中；如果没有指定库名，则根据SQL文件中的创建库语句创建库并导入数据。 - 对于方法二，需要先登录到MySQL客户端，然后切换到目标数据库（如果需要），最后执行
source命令来恢复表结构或数据。
以下是对MySQL增量备份和恢复方法的整理，以表格形式呈现：
增量备份
| 命令 | 功能 | 备注 |
|---|---|---|
mysqldump -u root -p密码 flush-logs | 刷新二进制日志，准备进行增量备份 | 刷新二进制日志文件，生成新的二进制日志文件用于增量备份 |
查看二进制日志内容
| 命令 | 功能 | 备注 |
|---|---|---|
mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制日志文件路径 | 查看二进制日志内容 | 解码并显示二进制日志文件的内容，方便检查 |
使用二进制日志文件增量恢复
| 命令 | 功能 | 备注 |
|---|---|---|
| `mysqlbinlog --no-defaults 二进制日志文件路径 | mysql -u 用户 -p密码` | 使用二进制日志文件进行增量恢复 |
断点恢复
基于位置点的断点恢复
| 命令 | 功能 | 备注 |
|---|---|---|
mysqlbinlog --no-defaults --start-position='起始位置点' --stop-position='结束位置点' 二进制日志文件路径 | 恢复指定位置范围内的数据 | 使用起始和结束位置点来限定恢复的数据范围 |
基于时间点的断点恢复
| 命令 | 功能 | 备注 |
|---|---|---|
mysqlbinlog --no-defaults --start-datetime='YYYY-mm-dd HH:MM:SS' --stop-datetime='YYYY-mm-dd HH:MM:SS' 二进制日志文件路径 | 恢复指定时间范围内的数据 | 使用起始和结束时间点来限定恢复的数据范围 |
断点恢复原则
| 原则 | 描述 |
|---|---|
| start | 如果要恢复某条SQL语句及其之后的所有数据，就从这个语句的位置点或时间点开始 |
| stop | 如果要恢复到某条SQL语句之前的所有数据，就stop在这个语句前一个的位置点或时间点 |
- 对于基于位置点的恢复，需要知道具体的起始和结束位置点，可以通过查看二进制日志文件来确定。
- 对于基于时间点的恢复，需要知道具体的起始和结束时间点，可以根据业务需求来设定。
- 使用
mysqlbinlog命令结合mysql命令可以实现断点恢复，将指定范围内的二进制日志内容导入到数据库中。