【数据库备份完整版】物理备份、逻辑备份，mysqldump、mysqlbinlog的备份方法

本文主要是介绍【数据库备份完整版】物理备份、逻辑备份，mysqldump、mysqlbinlog的备份方法，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

一、物理备份

物理备份通常涉及复制数据库文件到另一个位置，以确保数据的安全。以下是使用物理备份进行操作的一般步骤：

1. 确保数据库实例处于一致的状态：

   • 对于支持事务的存储引擎（如InnoDB），可以通过执行 FLUSH TABLES WITH READ LOCK 来锁定所有表，确保数据一致性。
   • 对于不支持事务的存储引擎（如MyISAM），可能需要使用 LOCK TABLES 对特定表进行锁定。

2. 停止数据库服务（可选）：

   • 对于不支持热备份的存储引擎，或者为了确保备份的一致性，可能需要停止数据库服务。

3. 复制数据文件：

   • 确定数据库的数据文件位置。在MySQL中，这通常是 /var/lib/mysql 或由配置文件指定的其他位置。

   • 使用文件复制命令（如 cp 或 rsync）复制数据文件到备份位置。例如：

     cp -a /var/lib/mysql/* /path/to/backup/
     

   • 或使用 rsync 来提高效率和减少错误：

   rsync -a /var/lib/mysql/* /path/to/backup/
   

4. 复制日志文件（如果需要）：

   • 对于InnoDB存储引擎，可能还需要复制日志文件（如 ib_logfile*）。

5. 释放锁并重启服务（如果需要）：

   • 如果之前锁定了表或停止了服务，备份完成后需要释放锁并重启数据库服务：

     UNLOCK TABLES;
     

   • 然后重启数据库服务：

     	sudo systemctl start mysql
     

6. 验证备份：

   • 检查备份文件是否完整，可以通过比较文件大小、检查文件列表或使用校验和（checksum）。

7. 测试备份：

   • 在一个安全的环境中测试备份，确保可以成功恢复数据。

8. 安排定期备份：

   • 根据需要安排定期的物理备份，可以使用 cron 作业或其他调度工具。

9. 安全存储备份：

   • 将备份文件存储在安全的位置，最好是在不同的物理位置，以防原始数据丢失或损坏。

10. 文档化备份过程：

    • 记录备份过程和恢复指南，确保在需要时可以快速准确地执行恢复操作。

请注意，物理备份的具体步骤可能会根据你使用的数据库版本、配置和存储引擎而有所不同。此外，有些数据库管理系统提供了专用的备份工具（如 MySQL 的 Percona XtraBackup），这些工具可以提供更高级的备份功能，包括热备份和增量备份。

二、逻辑备份

1.mysqldump和binlog备份的方式：

• mysqldump用于将整个或部分数据库导出为可执行的SQL文件，也可以用于导入或还原数据库，它可以创建一个数据库的逻辑备份，包括表结构和数据
• binlog（二进制日志）是一种事务日志，记录了对数据库进行的每个更改操作，如插入、更新、删除等。它用于增量备份和数据恢复，可以重放这些更改以还原到之前的状态。

mysqldump和binlog的主要区别如下：

• 数据格式：mysqldump生成的备份文件是以文本形式保存的SQL语句，可以读取和修改。binlog则是以二进制格式保存的事务日志，不能直接查看或修改，需要专门的工具（如mysqlbinlog）进行解析和分析。
• 备份范围：mysqldump可以备份整个数据库或特定的表，以及备份时可以选择备份的数据内容（例如只备份表结构、只备份数据等）。而binlog会记录所有的更改操作，包括对表结构和数据的更改，因此可以用来恢复到任何一个时间点之前的状态。
• 使用场景：mysqldump适用于定期完整备份数据库或移植数据库，以便将数据导入到不同的MySQL服务器。binlog则适用于增量备份和恢复，可以用于故障恢复、数据同步、主从复制等场景。

2.mysqldump完整备份与恢复数据

2.1 mysqldump概念

mysqldump命令可以将数据库中指定或所有的库、表、视图、存储过程导出为SQL脚本（可跨服务器进行备份转储等）。
mysqldump备份恢复原理：通过先查出需要备份的库及表、视图、存储过程的结构，在SQL脚本中生成CREATE语句。然后将表中的所有记录转换成INSERT语句并写入SQL脚本中。还原时，使用CREATE语句来创建数据库、表、存储过程等，使用INSERT语句来给表插入（恢复）数据。

2.2 mysqldump备份

1.备份单个数据库(如：不需要密码提示，则可以在-p后写上密码（-p和密码之间不能有空格）)
mysqldump -u root -p [database_name] > database_name.sql
2.备份指定数据库的指定表–tables
mysqldump -u root -p [database_name] --tables [table1] [table2] > database_name.sql导出指定表的导出文本中没有创建数据库语句。
3.备份多个数据库–databases
mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql
4.备份所有数据库 --all-databases, 加时间戳-$(date +%Y%m%d)

mysqldump -u root -p --all-databases > database_name-$(date +%Y%m%d).sql

5.条件导出：只导出id小于10的数据
mysqldump -u root -p [database_name] --tables [table1] --where="id<10" > database_name.sql
导出的sql中不包含drop table,create table: --no-create-info
mysqldump -u root -p --no-create-info [database_name] --tables [table1] --where="id<10" > database_name.sql
6.生成新的binlog文件-F
希望导出数据之后生成一个新的binlog文件,只需要加上-F参数即可
mysqldump -u root -p [database_name] -F > database_name.sql
7.只导出表结构不导出数据–no-data
mysqldump -u root -p --no-data [database_name] > database_name.sql
8.对远程数据库进行导出，则可以在-h后写上数据库服务器名称或IP地址，-P3306端口
mysqldump -h servername -P3306 -u root -ppassword database_name > database_name.sql
例：将h1服务器中的db1数据库的所有数据导入到h2中的db2数据库中，db2的数据库必须存在否则会报错:
mysqldump --host=h1 -uroot -proot -C --databases db1 |mysql --host=h2 -uroot -proot db2
加上-C参数可以启用压缩传递。
9.导出到远程数据库中
mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name通过管道将输出传送到远程主机上的mysql客户端，并将其导入名为 的数据库中。
10.将主库的binlog位置和文件名追加到导出数据的文件中–dump-slave
mysqldump -u root -p --dump-slave=1 [database_name] > database_name.sql
注意：–dump-slave命令如果当前服务器是从服务器那么使用该命令会执行stop slave来获取master binlog的文件和位置，等备份完后会自动执行start slave启动从服务器。但是如果是大的数据量备份会给从和主的延时变的更大，使用–dump-slave获取到的只是当前的从服务器的数据执行到的主的binglog的位置是（relay_mater_log_file,exec_master_log_pos),而不是主服务器当前的binlog执行的位置，主要是取决于主从的数据延时。
该参数在在从服务器上执行，相当于执行show slave status。当设置为1时，将会以CHANGE MASTER命令输出到数据文件；设置为2时，会在change前加上注释。
该选项将会打开–lock-all-tables，除非–single-transaction被指定。
当–dump-slave=1时：

当–dump-slave=2时：

–master-data不会停止当前服务器的主从服务：该参数和–dump-slave方法一样，只是它是记录的是当前服务器的binlog，相当于执行show master status，状态（file,position)的值。
11.导出存储过程和自定义函数–routines,-R
mysqldump -u root -p --routines [database_name] > database_name.sql
12.创建压缩的mysql数据库备份
如果数据库大小非常大，最好压缩输出。为此，只需将输出通过管道传输到gzip实用程序，然后将其重定向到文件mysqldump -u root -p database_name | gzip > database_name.sql.gz

# 压缩备份
mysqldump -uroot -p -P3306 -q -Q --set-gtid-purged=OFF --default-character-set=utf8 --hex-blob --skip-lock-tables --databases database_name 2>/database_name.err | gzip >/database_name.sql.gz
# 命令解释
#1. -q 和 --skip-lock-tables 选项来减少备份过程中对生产环境的影响。
#2. -Q 和 --hex-blob 选项来确保备份文件的兼容性和二进制数据的准确性。
#3.使用 gzip 对备份数据进行压缩，以节省存储空间。
#4. 2>/database_name.err：将标准错误重定向到名为 database_name.err 的文件中，用于后续的错误排查。# 还原
gunzip -c database_name.sql.gz | mysql -uroot -p -vvv -P3306 --default-character-set=utf8 database_name 1> database_name.log 2>database_name.err#命令解释
#1. 使用 gunzip -c 解压缩 database_name.sql.gz 文件，但不生成解压缩后的文件，而是将内容输出到标准输出。
#2. 将解压缩后的标准输出通过管道传递给 mysql 命令。
# -vvv：详细模式，提供更多输出信息。三个 v 表示更高级别的详细输出
#3. mysql 命令接收输入的数据，并将其作为SQL语句导入到名为 database_name 的数据库中。
#4. 将导入过程中的标准输出和标准错误分别记录到 database_name.log 和 database_name.err 文件中，用于后续的日志审查和错误排查

13.其他参数可选：

• 常用选项：
  • –no-create-db， —取消创建数据库sql(默认存在)
  • –no-create-info，—取消创建表sql(默认存在)
  • –no-data —不导出数据(默认导出)
  • –add-drop-database —增加删除数据库sql（默认不存在）
  • –skip-add-drop-table —取消每个数据表创建之前添加drop数据表语句(默认每个表之前存在drop语句)
  • –skip-add-locks —取消在每个表导出之前增加LOCK TABLES（默认存在锁）
  • –skip-comments —注释信息(默认存在)
• 不返回数据,添加“-d”命令参数；
• 不返回表结构，添加"-t"参数；
• 筛选表数据：–where=“a=1 and b<>4”;
• 指定mysqldump字符集： --default-character-set=gb2312；
• –all-databases , -A
• 导出全部数据库。
  mysqldump -uroot -p --all-databases
• –all-tablespaces , -Y
  导出全部表空间。
  mysqldump -uroot -p --all-databases --all-tablespaces
• –no-tablespaces , -y
  不导出任何表空间信息。
  mysqldump -uroot -p --all-databases --no-tablespaces
• –add-drop-database
  每个数据库创建之前添加drop数据库语句。
  mysqldump -uroot -p --all-databases --add-drop-database
• –add-drop-table
  每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用–skip-add-drop-table取消选项)
  mysqldump -uroot -p --all-databases (默认添加drop语句)
  mysqldump -uroot -p --all-databases --skip-add-drop-table (取消drop语句)
• –add-locks
  在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态，使用–skip-add-locks取消选项)
  mysqldump -uroot -p --all-databases (默认添加LOCK语句)
  mysqldump -uroot -p --all-databases --skip-add-locks (取消LOCK语句)
• –allow-keywords
  允许创建是关键词的列名字。这由表名前缀于每个列名做到。
  mysqldump -uroot -p --all-databases --allow-keywords
• –apply-slave-statements
  在’CHANGE MASTER’前添加’STOP SLAVE’，并且在导出的最后添加’START SLAVE’。
  mysqldump -uroot -p --all-databases --apply-slave-statements
• –character-sets-dir
  字符集文件的目录
  mysqldump -uroot -p --all-databases --character-sets-dir=/usr/local/mysql/share/mysql/charsets
• –comments
  附加注释信息。默认为打开，可以用–skip-comments取消
  mysqldump -uroot -p --all-databases (默认记录注释)
  mysqldump -uroot -p --all-databases --skip-comments (取消注释)
• –compatible
  导出的数据将和其它数据库或旧版本的MySQL 相兼容。值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等，
  要使用几个值，用逗号将它们隔开。它并不保证能完全兼容，而是尽量兼容。
  mysqldump -uroot -p --all-databases --compatible=ansi
• –compact
  导出更少的输出信息(用于调试)。去掉注释和头尾等结构。可以使用选项：–skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys
  mysqldump -uroot -p --all-databases --compact
• –complete-insert, -c
  使用完整的insert语句(包含列名称)。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。
  mysqldump -uroot -p --all-databases --complete-insert
• –compress, -C
  在客户端和服务器之间启用压缩传递所有信息
  mysqldump -uroot -p --all-databases --compress
• –create-options, -a
  在CREATE TABLE语句中包括所有MySQL特性选项。(默认为打开状态)
  mysqldump -uroot -p --all-databases
• –databases, -B
  导出几个数据库。参数后面所有名字参量都被看作数据库名。
  mysqldump -uroot -p --databases test mysql
• –debug
  输出debug信息，用于调试。默认值为：d:t,/tmp/mysqldump.trace
  mysqldump -uroot -p --all-databases --debug
  mysqldump -uroot -p --all-databases --debug=” d:t,/tmp/debug.trace”
• –debug-check
  检查内存和打开文件使用说明并退出。
  mysqldump -uroot -p --all-databases --debug-check
• –debug-info
  输出调试信息并退出
  mysqldump -uroot -p --all-databases --debug-info
• –default-character-set
  设置默认字符集，默认值为utf8
  mysqldump -uroot -p --all-databases --default-character-set=utf8
• –delayed-insert
  采用延时插入方式（INSERT DELAYED）导出数据
  mysqldump -uroot -p --all-databases --delayed-insert
• –delete-master-logs
  master备份后删除日志. 这个参数将自动激活–master-data。
  mysqldump -uroot -p --all-databases --delete-master-logs
• –disable-keys
  *对于每个表，用/*!40000 ALTER TABLE tbl_name DISABLE KEYS /;和/!40000 ALTER TABLE tbl_name ENABLE KEYS /;语句引用INSERT语句。这样可以更快地导入dump出来的文件，因为它是在插入所有行后创建索引的。该选项只适合MyISAM表，默认为打开状态。
  **mysqldump -uroot -p --all-databases **
• –dump-slave
  该选项将主的binlog位置和文件名追加到导出数据的文件中(show slave status)。设置为1时，将会以CHANGE MASTER命令输出到数据文件；设置为2时，会在change前加上注释。该选项将会打开–lock-all-tables，除非–single-transaction被指定。该选项会自动关闭–lock-tables选项。默认值为0。
  mysqldump -uroot -p --all-databases --dump-slave=1
  mysqldump -uroot -p --all-databases --dump-slave=2
• –master-data
  该选项将当前服务器的binlog的位置和文件名追加到输出文件中(show master status)。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE MASTER命令前添加注释信息。该选项将打开–lock-all-tables 选项，除非–single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的–single-transaction选项）。该选项自动关闭–lock-tables选项。
  mysqldump -uroot -p --host=localhost --all-databases --master-data=1;
  mysqldump -uroot -p --host=localhost --all-databases --master-data=2;
• –events, -E
  导出事件。
  mysqldump -uroot -p --all-databases --events
• –extended-insert, -e
  使用具有多个VALUES列的INSERT语法。这样使导出文件更小，并加速导入时的速度。默认为打开状态，使用–skip-extended-insert取消选项。
  mysqldump -uroot -p --all-databases
  mysqldump -uroot -p --all-databases–skip-extended-insert (取消选项)
• –fields-terminated-by
  导出文件中忽略给定字段。与–tab选项一起使用，不能用于–databases和–all-databases选项
  mysqldump -uroot -p test test --tab=”/home/mysql” --fields-terminated-by=”#”
• –fields-enclosed-by
  输出文件中的各个字段用给定字符包裹。与–tab选项一起使用，不能用于–databases和–all-databases选项
  mysqldump -uroot -p test test --tab=”/home/mysql” --fields-enclosed-by=”#”
• –fields-optionally-enclosed-by
  输出文件中的各个字段用给定字符选择性包裹。与–tab选项一起使用，不能用于–databases和–all-databases选项
  mysqldump -uroot -p test test --tab=”/home/mysql” --fields-enclosed-by=”#” --fields-optionally-enclosed-by =”#”
• –fields-escaped-by
  输出文件中的各个字段忽略给定字符。与–tab选项一起使用，不能用于–databases和–all-databases选项
  mysqldump -uroot -p mysql user --tab=”/home/mysql” --fields-escaped-by=”#”
• –flush-logs
  开始导出之前刷新日志。
  请注意：假如一次导出多个数据库(使用选项–databases或者–all-databases)，将会逐个数据库刷新日志。除使用–lock-all-tables或者–master-data外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，如果打算同时导出和刷新日志应该使用–lock-all-tables 或者–master-data 和–flush-logs。
  mysqldump -uroot -p --all-databases --flush-logs
• –flush-privileges
  在导出mysql数据库之后，发出一条FLUSH PRIVILEGES 语句。为了正确恢复，该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。
  mysqldump -uroot -p --all-databases --flush-privileges
• –force
  在导出过程中忽略出现的SQL错误。
  mysqldump -uroot -p --all-databases --force
• –help
  显示帮助信息并退出。
  mysqldump --help
• –hex-blob
  使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用该选项。影响到的字段类型有BINARY、VARBINARY、BLOB。
  mysqldump -uroot -p --all-databases --hex-blob
• –host, -h
  需要导出的主机信息
  mysqldump -uroot -p --host=localhost --all-databases
• –ignore-table
  不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：–ignore-table=database.table1 --ignore-table=database.table2 ……
  mysqldump -uroot -p --host=localhost --all-databases --ignore-table=mysql.user
• –include-master-host-port
  **在–dump-slave产生的’CHANGE MASTER TO…‘语句中增加’MASTER_HOST=，MASTER_PORT=’ **
  mysqldump -uroot -p --host=localhost --all-databases --include-master-host-port
• –insert-ignore
  在插入行时使用INSERT IGNORE语句.
  mysqldump -uroot -p --host=localhost --all-databases --insert-ignore
• –lines-terminated-by
  输出文件的每行用给定字符串划分。与–tab选项一起使用，不能用于–databases和–all-databases选项。
  mysqldump -uroot -p --host=localhost test test --tab=”/tmp/mysql” --lines-terminated-by=”##”
• –lock-all-tables, -x
  提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭–single-transaction 和–lock-tables 选项。
  mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables
• –lock-tables, -l
  开始导出前，锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，–single-transaction是一个更好的选择，因为它根本不需要锁定表。
  请注意当导出多个数据库时，–lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
  mysqldump -uroot -p --host=localhost --all-databases --lock-tables
• –log-error
  附加警告和错误信息到给定文件
  mysqldump -uroot -p --host=localhost --all-databases --log-error=/tmp/mysqldump_error_log.err
• –max_allowed_packet
  服务器发送和接受的最大包长度。
  mysqldump -uroot -p --host=localhost --all-databases --max_allowed_packet=10240
• –net_buffer_length
  TCP/IP和socket连接的缓存大小。
  mysqldump -uroot -p --host=localhost --all-databases --net_buffer_length=1024
• –no-autocommit
  使用autocommit/commit 语句包裹表。
  mysqldump -uroot -p --host=localhost --all-databases --no-autocommit
• –no-create-db, -n
  只导出数据，而不添加CREATE DATABASE 语句。
  mysqldump -uroot -p --host=localhost --all-databases --no-create-db
• –no-create-info, -t
  只导出数据，而不添加CREATE TABLE 语句。
  mysqldump -uroot -p --host=localhost --all-databases --no-create-info
• –no-data, -d
  不导出任何数据，只导出数据库表结构。
  mysqldump -uroot -p --host=localhost --all-databases --no-data
• –no-set-names, -N
  等同于–skip-set-charset
  mysqldump -uroot -p --host=localhost --all-databases --no-set-names
• –opt
  等同于–add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys 该选项默认开启, 可以用–skip-opt禁用.
  mysqldump -uroot -p --host=localhost --all-databases --opt
• –order-by-primary
  **如果存在主键，或者第一个唯一键，对每个表的记录进行排序。在导出MyISAM表到InnoDB表时有效，但会使得导出工作花费很长时间。 **
  mysqldump -uroot -p --host=localhost --all-databases --order-by-primary
• –password, -p
  连接数据库密码
• –pipe(windows系统可用)
  使用命名管道连接mysql
  mysqldump -uroot -p --host=localhost --all-databases --pipe
• –port, -P
  连接数据库端口号
• –protocol
  使用的连接协议，包括：tcp, socket, pipe, memory.
  mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp
• –quick, -q
  不缓冲查询，直接导出到标准输出。默认为打开状态，使用–skip-quick取消该选项。
  **mysqldump -uroot -p --host=localhost --all-databases **
  mysqldump -uroot -p --host=localhost --all-databases --skip-quick
• –quote-names,-Q
  使用（`）引起表和列名。默认为打开状态，使用–skip-quote-names取消该选项。
  mysqldump -uroot -p --host=localhost --all-databases
  mysqldump -uroot -p --host=localhost --all-databases --skip-quote-names
• –replace
  使用REPLACE INTO 取代INSERT INTO.
  mysqldump -uroot -p --host=localhost --all-databases --replace
• –result-file, -r
  直接输出到指定文件中。该选项应该用在使用回车换行对（\r\n）换行的系统上（例如：DOS，Windows）。该选项确保只有一行被使用。
  mysqldump -uroot -p --host=localhost --all-databases --result-file=/tmp/mysqldump_result_file.txt
• –routines, -R
  导出存储过程以及自定义函数。
  mysqldump -uroot -p --host=localhost --all-databases --routines
• –set-charset
  添加’SET NAMES default_character_set’到输出文件。默认为打开状态，使用–skip-set-charset关闭选项。
  **mysqldump -uroot -p --host=localhost --all-databases **
  mysqldump -uroot -p --host=localhost --all-databases --skip-set-charset
• –single-transaction
  该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和–lock-tables 选项是互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用–quick 选项。
  mysqldump -uroot -p --host=localhost --all-databases --single-transaction
• –dump-date
  将导出时间添加到输出文件中。默认为打开状态，使用–skip-dump-date关闭选项。*
  mysqldump -uroot -p --host=localhost --all-databases
  mysqldump -uroot -p --host=localhost --all-databases --skip-dump-date
• –skip-opt
  禁用–opt选项.
  mysqldump -uroot -p --host=localhost --all-databases --skip-opt
• –socket,-S
  指定连接mysql的socket文件位置，默认路径/tmp/mysql.sock
  mysqldump -uroot -p --host=localhost --all-databases --socket=/tmp/mysqld.sock
• –tab,-T
  为每个表在给定路径创建tab分割的文本文件。注意：仅仅用于mysqldump和mysqld服务器运行在相同机器上。注意使用–tab不能指定–databases参数
  mysqldump -uroot -p --host=localhost test test --tab=“/home/mysql”
• –tables
  覆盖–databases (-B)参数，指定需要导出的表名，在后面的版本会使用table取代tables。
  mysqldump -uroot -p --host=localhost --databases test --tables test
• –triggers
  导出触发器。该选项默认启用，用–skip-triggers禁用它。
  mysqldump -uroot -p --host=localhost --all-databases --triggers
• –tz-utc
  在导出顶部设置时区TIME_ZONE=‘+00:00’ ，以保证在不同时区导出的TIMESTAMP 数据或者数据被移动其他时区时的正确性。
  mysqldump -uroot -p --host=localhost --all-databases --tz-utc
• –user, -u
  指定连接的用户名。
• –verbose, --v
  输出多种平台信息。
• –version, -V
  输出mysqldump版本信息并退出
• –where, -w
  只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。
  mysqldump -uroot -p --host=localhost --all-databases --where=” user=’root’”
• –xml, -X
  导出XML格式.
  mysqldump -uroot -p --host=localhost --all-databases --xml
• –plugin_dir
  客户端插件的目录，用于兼容不同的插件版本。
  mysqldump -uroot -p --host=localhost --all-databases --plugin_dir=”/usr/local/lib/plugin”
• –default_auth
  客户端插件默认使用权限。
  mysqldump -uroot -p --host=localhost --all-databases --default-auth=”/usr/local/lib/plugin/”

2.3 数据恢复

# 备份文件无创建数据库信息时
mysql -u root -p -e "create database database_name";
mysql -u root -p database_name < database_name.sql# --databases会备份创建数据库的语句，数据库名可不写
mysql -u root -p  < database_name.sql

2.4 使用 Cron 自动执行备份

（1）在用户主目录中创建一个名为.my.cnf的文件，配置用户名和密码：
sudo nano ~/.my.cnf
在.my.cnf 文件中添加以下内容：
bash [client] user = dbuser password = dbpasswd
限制凭据文件的权限：
chmod 600 ~/.my.cnf
（2）创建备份的目录
（3）添加cron 作业：

crontab -e# 每天凌晨 3 点创建mydb数据库的备份
0 3 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +\%Y\%m\%d).sql

2.5 information_schema 和 mysql 这两个特殊数据库，一般不需备份：

1. information_schema：提供了关于当前MariaDB服务器上所有其他数据库的元数据。它包含了数据库、表、列、索引等的详细信息。information_schema 是动态生成的，不需要备份，因为它可以从其他数据库的元数据中重建。
2. mysql：是系统表，用于存储用户账号、权限、服务器状态等信息。它包含了访问控制和服务器运行所需的关键信息。

对于是否需要备份这些数据库，考虑以下因素：

• 安全性：如果你需要恢复整个MariaDB服务器，包括用户权限和服务器配置，那么备份mysql数据库是必要的。
• 恢复需求：如果你只需要恢复用户数据，而不需要恢复服务器配置或用户权限，那么可能不需要备份mysql数据库。
• 系统信息：如果你需要保留数据库的创建时间、字符集等信息，information_schema 可以提供这些信息，但通常不需要备份，因为这些信息可以从其他数据库的元数据中重建。

2.6 附件：备份mysql容器的数据库脚本

#!/bin/bash# 数据库列表
databases=("aux_db" "fqxs_db" "test" "temp")# 备份目录
backup_dir="/backupfile/mariadb_backup"
container_dir="/var/backup_file"# 检查目录是否存在
if [ ! -d "$backup_dir" ]; thenmkdir -p "$backup_dir"
fi# 输入用户名
read -p "请输入数据库用户名：" usernameread -s -p "请输入数据库密码：" password
echo# 遍历数据库，执行备份
for db in "${databases[@]}"; doecho "正在备份数据库：$db"# 使用mysqldump命令备份docker exec -it mariadb2 /bin/bash -c "mysqldump -u "$username" -p"$password" --routines --triggers --databases "$db"  > "$container_dir/$db-$(date "+%Y%m%d%H%M%S").sql""echo "数据库 $db 备份完成。"
doneecho "所有数据库备份完毕。"docker cp mariadb2:/var/backup_file  $backup_direcho "数据库备份已迁移/backup_file/mariadb_backup下。"

3. binlog增量备份和恢复

3.1 binlog概念

二进制日志(binnary log)以事件形式记录了对MySQL数据库执行更改的所有操作。
binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE、DROP等）以及表数据修改（INSERT、UPDATE、DELETE、TRUNCATE等）的二进制日志。不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但可以通过查询通用日志来查看MySQL执行过的所有语句。
binlog有两个常用的使用场景：

• 主从复制：master节点开启binlog，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
• 数据恢复：通过mysqlbinlog工具来恢复数据。

3.2 开启binlog

MySQL5.7版本binlog默认不开启，MySQL8默认开启binlog；登录MySQL后，查看binlog状态sql如下：
show variables like'%log_bin%';
ON为开启，OFF为未开启。
开启方法：
（1）修改MySQL配置文件，linux中配置文件为my.conf，window下为my.ini：
添加配置项：

log-bin=mysql-bin
server-id=1

修改完重启数据库服务！
（2）查看show variables like'%log_bin%'

log_bin为ON说明这个参数是开启的，就是说系统是记录了bin log的
log_bin_basename配置了bin log的文件路径及文件前缀名
log_bin_index配置了bin log索引文件的路径

（3）查看日志列表
show master logs;
根据log_bin_basename的路径查看binlog具体文件。

3.3 使用binlog日志恢复数据

原理：当数据库发生变化时，binlog会记录数据库中的所有变化；需要恢复的时候可以根据binlog中的开始位置和结束位置还原本部分操作；结束位置一般是数据被破坏或删除之前的位置。
（1）执行**flush logs**，当需要恢复数据时，为了防止恢复数据后影响最新业务，需要执行flush logs，产生一个新的binlog文件，此时旧的binlog文件不会再有写入；
（2）恢复时需要在binlog中找到两个位置：

• 数据恢复的起始位置
• 数据恢复的结束位置

通过mysqlbinlog将binlog转为sql，查询具体位置：

mysqlbinlog --set-charset=utf-8 /var/lib/mysql/mysql-bin.000001 > backuptmp.sql

找位置为219

3.4 通过mysqlbinlog执行恢复操作：

mysqlbinlog -v /var/lib/mysql/mysql-bin.000001 --start-position=219 --stop-position=982 | mysql -uroot -p123456# /var/lib/mysql/mysql-bin.000001       要操作binlog文件
# --start-position=219                  数据恢复的起始位置
# --stop-position=982                   数据恢复的结束位置
# mysql -uroot -p123456                 数据恢复需要登录数据库

最后，进入数据库核验恢复结果！

三、物理备份与逻辑备份选择

物理备份和逻辑备份各有优势和适用场景，选择哪种备份方式取决于具体需求和环境。以下是两种备份方式的优缺点概述：

物理备份

优点：

1. 速度：通常备份和恢复速度快，因为它直接复制数据文件。
2. 一致性：可以确保在备份过程中数据库文件的一致性，特别是对于不支持事务的存储引擎。
3. 硬件无关性：某些工具如Percona XtraBackup可以创建硬件无关的备份，便于跨平台恢复。

缺点：

1. 版本依赖：可能需要在相同或兼容的MySQL版本之间进行恢复。
2. 文件大小：备份文件可能较大，占用更多存储空间。
3. 环境依赖：可能需要在相同或相似的硬件和操作系统环境中恢复。

逻辑备份

优点：

1. 灵活性：可以在不同的硬件、操作系统和数据库版本之间进行恢复。
2. 可读性：备份文件是SQL语句，易于理解和编辑。
3. 细粒度：可以对单个数据库或表进行备份。

缺点：

1. 速度：备份和恢复过程可能较慢，因为它需要通过数据库服务器来导出数据。
2. 性能影响：备份过程中可能会对数据库性能产生一定影响。
3. 文件大小：备份文件可能较大，特别是对于大型数据库。

更常用

在实际应用中，mysqldump 工具进行的逻辑备份是非常常见的，因为它简单、灵活，并且可以轻松地在不同环境中迁移和恢复数据。
然而，对于大型数据库或需要快速恢复的场景，物理备份可能更受青睐，因为它速度快且对在线操作影响小。
最终，选择哪种备份方法应基于以下考虑：

• 数据库的大小和复杂性。
• 恢复时间目标（RTO）和点目标恢复（RPO）。
• 硬件和软件环境的兼容性。
• 维护和监控备份的资源和能力。

在许多情况下，组织可能会结合使用物理和逻辑备份策略，以实现数据保护和业务连续性的最佳实践。

这篇关于【数据库备份完整版】物理备份、逻辑备份，mysqldump、mysqlbinlog的备份方法的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---