1. 概述
1.1 mysqldump工具
mysqldump是MySQL自带的一个命令行工具，可以用来备份数据库或表的结构和数据。它通过将数据库结构和数据导出成一个文本文件来实现备份功能。mysqldump生成的备份文件是一个SQL脚本，可以使用mysql命令直接执行还原。
1.2 mysqldump语法
mysqldump的基本语法格式如下：
mysqldump [options] db_name [table_name1 ...] mysqldump [options] --databases db_name1 [db_name2 db_name3...] mysqldump [options] --all-databases
其中：
options: 指定备份选项，如用户名、密码、备份格式等db_name: 要备份的数据库名table_name1: 要备份的表名，可选，不指定则备份整个数据库
--databases和--all-databases选项用于备份多个数据库。
1.3 常用备份选项
mysqldump提供了很多备份选项：
-u, --user=name # 指定连接MySQL服务器的用户名 -p, --password[=name] # 指定连接MySQL服务器的密码 -h, --host=name # 指定要连接的MySQL服务器IP或域名 -P, --port= # 指定连接的MySQL服务器端口号 --single-transaction # 该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如InnoDB和BDB。本选项和--lock-tables 选项是互斥的，为LOCK TABLES 会使任何挂起的事务隐含提交。 --quick # 不缓冲查询，直接导出到标准输出。默认为打开状态，使用--skip-quick取消该选项。 --lock-tables # 备份前，锁定所有数据库表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，--single-transaction是一个更好的选择，因为它根本不需要锁定表。 --routines, -R # 导出存储过程以及自定义函数。 --triggers # 导出触发器。该选项默认启用，用--skip-triggers禁用它。 --default-character-set # 指定导出数据时采用何种字符集，如utf8、gbk等。
2. 备份示例
2.1 备份全部数据库
要备份MySQL服务器上的所有数据库,可以使用--all-databases或-A选项。例如:
mysqldump -u root -p --all-databases > home/mysql_backup/all.sql
这会将所有数据库的数据和结构导出到home/mysql_backup下的all.sql文件中。
2.2 备份指定的数据库
2.2.1 指定单个数据库
要备份单个数据库，只需在mysqldump命令后指定数据库名即可。例如，备份名为mydb的数据库:
mysqldump -u root -p mydb > mydb.sql
这会将mydb数据库的数据和结构导出到mydb.sql文件中。
2.2.2 指定多个数据库
要同时备份多个数据库，可以使用--databases或-B选项，并在其后列出要备份的数据库名，数据库名之间用空格隔开。例如，备份db1、db2、db3三个数据库:
mysqldump -u root -p --databases db1 db2 db3 > db1_db2_db3.sql
这会将db1、db2、db3三个数据库的数据和结构导出到db1_db2_db3.sql文件中。
--databases选项后面指定的数据库名必须存在，否则mysqldump会报错。如果要忽略不存在的数据库，可以再加上--force或-f选项，例如：
mysqldump -u root -p --force --databases db1 db2 db3 > db1_db2_db3.sql
这样即使db1、db2、db3中有不存在的数据库，mysqldump也会继续备份其他存在的数据库，而不会中断备份过程。
2.3 备份指定数据库的指定表
2.3.1 备份指定 单个表
要备份指定数据库中的单个表，可以在mysqldump命令后依次指定数据库名和表名，中间用空格隔开。例如，备份mydb数据库中的mytable表:
mysqldump -u root -p mydb mytable > mydb_mytable.sql
这会将mydb数据库中mytable表的数据和结构导出到mydb_mytable.sql文件中。
2.3.2 备份指定 多个表
要备份指定数据库中的多个表，可以在mysqldump命令后先指定数据库名，然后列出要备份的表名，表名之间用空格隔开。例如，备份mydb数据库中的table1、table2、table3三个表:
mysqldump -u root -p mydb table1 table2 table3 > mydb_tables.sql
这会将mydb数据库中table1、table2、table3三个表的数据和结构导出到mydb_tables.sql文件中。
2.3.3 备份指定表中 满足条件的数据
要备份指定表中满足特定条件的数据，可以在mysqldump命令中使用--where或-w选项，并在其后指定筛选条件。例如，备份mydb数据库的mytable表中id大于100的数据:
mysqldump -u root -p mydb mytable --where="id > 100" > mydb_mytable_partial.sql
这会将mydb数据库mytable表中id大于100的数据导出到mydb_mytable_partial.sql文件中。
--where选项只能用于备份表数据，不能用于备份表结构。如果要同时备份表结构和满足条件的表数据，可以先备份整个表，然后再用--where选项备份部分数据，最后将两个备份文件合并。例如:
# 备份整个表的结构和数据 mysqldump -u root -p mydb mytable > mydb_mytable.sql # 备份满足条件的表数据 mysqldump -u root -p mydb mytable --where="id > 100" --no-create-info > mydb_mytable_partial.sql
然后可以使用文本编辑器将mydb_mytable_partial.sql文件中的数据插入语句复制到mydb_mytable.sql文件中相应的位置。
2.4 备份数据库是忽略指定表
在备份数据库时，如果想排除某些表，可以使用--ignore-table选项。该选项的语法为--ignore-table=database.table，其中database为数据库名，table为要忽略的表名。如果要忽略多个表，可以多次使用该选项。例如，备份mydb数据库时忽略table1和table2表:
mysqldump -u root -p --ignore-table=mydb.table1 --ignore-table=mydb.table2 mydb > mydb_backup.sql
这会备份mydb数据库中除了table1和table2之外的所有表。
2.5 备份数据库是忽略指定表
与忽略指定表相反，如果只想备份数据库中的某些表，可以在mysqldump命令后先指定数据库名，然
后列出要备份的表名，表名之间用空格隔开。这实际上就是前面提到的"备份指定数据库的指定表"。例如，只备份mydb数据库中的table1和table2表:
mysqldump -u root -p mydb table1 table2 > mydb_partial_backup.sql
这会备份mydb数据库中的table1和table2表，其他表会被忽略。
2.6 备份时压缩输出
mysqldump支持直接将备份输出压缩，以节省磁盘空间和加快备份速度。压缩输出可以使用gzip、
bzip2等压缩工具，具体取决于您的系统中安装了哪些压缩工具。要启用压缩输出，可以在mysqldump命令中使用--compress或-C选项。例如，使用gzip压缩备份mydb数据库：
mysqldump -u root -p --compress mydb | gzip > mydb_backup.sql.gz
这会将mydb数据库备份并压缩为mydb_backup.sql.gz文件。
如果要使用其他压缩工具，可以将mysqldump的输出通过管道传递给相应的压缩命令。例如，使用bzip2压缩备份：
mysqldump -u root -p mydb | bzip2 > mydb_backup.sql.bz2
使用压缩输出会增加CPU的负载，因为压缩过程需要消耗额外的计算资源。如果CPU资源紧张，可能会影响备份速度。在这种情况下，可以考虑先备份到未压缩的文件，然后再用单独的命令对备份文件进行压缩。例如：
mysqldump -u root -p mydb > mydb_backup.sql gzip mydb_backup.sql
2.7 备份时生成带删除表语句的sql文件
默认情况下，mysqldump生成的备份文件中不包含删除表的语句。如果需要在备份文件中包含删除表的语句，可以使用--add-drop-table选项。例如：
mysqldump -u root -p --add-drop-table mydb > mydb_with_drop.sql
这会在导出的mydb_with_drop.sql文件中，在每个表的创建语句之前添加一条删除表的语句，类似于：
DROP TABLE IF EXISTS `mytable`; CREATE TABLE `mytable` ( ... ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
这样，在使用该备份文件恢复数据库时，如果表已经存在，就会先删除原有的表，然后再创建新表并导入数据。这可以确保恢复后的表结构与备份时完全一致。
2.8 备份时生成新建表结构语句的sql文件
默认情况下，mysqldump生成的备份文件中既包含表结构的创建语句，也包含表数据的插入语句。如果只需要备份表结构而不需要备份表数据，可以使用--no-data或-d选项。例如：
mysqldump -u root -p --no-data mydb > mydb_structure.sql
这会导出mydb数据库中所有表的结构，但不会导出表数据，生成的mydb_structure.sql文件中只包含创建表的语句，类似于：
DROP TABLE IF EXISTS `mytable`; CREATE TABLE `mytable` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
如果要同时排除表结构的创建语句，只生成删除表的语句，可以再加上--add-drop-table --skip-add-drop-table选项，例如：
mysqldump -u root -p --no-data --add-drop-table --skip-add-drop-table mydb > mydb_drop.sql
这会导出mydb数据库中所有表的删除语句，生成的mydb_drop.sql文件中只包含删除表的语句，类似于：
DROP TABLE IF EXISTS `mytable`;
3. 备份远程数据库
mysqldump不仅可以备份本地数据库，也可以备份远程数据库。备份远程数据库时，需要指定以下选项：
-h,--host=name：指定要备份的远程数据库所在的主机名或IP地址。-P,--port=port：指定远程数据库的端口号，默认为3306。-u,--user=name：指定有权限备份远程数据库的用户名。-p,--password[=name]：指定用户的密码。如果不写=name，mysqldump会提示输入密码。
例如，备份一个IP地址为192.168.1.100，端口为默认的3306，用户名为backup，密码为123456，名为mydb的数据库：
mysqldump -h 192.168.1.100 -u backup -p123456 mydb > mydb.sql
如果远程数据库开启了SSL连接，还需要指定相关的SSL选项：
--ssl：启用SSL连接。--ssl-ca=name：指定CA证书文件的路径。--ssl-cert=name：指定客户端公钥证书文件的路径。--ssl-key=name：指定客户端私钥文件的路径。
例如：
mysqldump -h 192.168.1.100 -u backup -p123456 --ssl --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem mydb > mydb.sql
备份远程数据库时需要注意：
1. 确保备份用户有足够的权限
备份远程数据库时，指定的用户必须拥有对所备份数据库的SELECT权限。如果备份整个远程数据库实例，则用户还需要SHOW DATABASES权限。
这列提供一个流程步骤，供参考之：
以拥有GRANT权限的用户身份(如root用户)登录到远程MySQL服务器：
mysql -h remote_host -u root -p
创建一个专门用于备份的用户，例如backup_user，并设置密码：
CREATE USER 'backup_user'@'%' IDENTIFIED BY 'password';
授予backup_user对所需备份的数据库的SELECT权限，例如对mydb数据库授权：
GRANT SELECT ON mydb.* TO 'backup_user'@'%';
（如果需要备份多个数据库，可以重复执行GRANT语句，对每个数据库分别授权。）
如果backup_user需要备份整个MySQL实例，则还需要授予SHOW DATABASES权限：
GRANT SHOW DATABASES ON *.* TO 'backup_user'@'%';
然后刷新权限表，使授权生效：
FLUSH PRIVILEGES;
现在。backup_user就拥有了备份所需的权限。在使用mysqldump备份远程数据库时,可以指定该用户：
mysqldump -h remote_host -u backup_user -p mydb > mydb.sql
如果备份整个MySQL实例：
mysqldump -h remote_host -u backup_user -p --all-databases > all_databases.sql
另外，在实际生产环境中除了SELECT和SHOW DATABASES权限外，备份用户不应该被授予其他权限，如INSERT、UPDATE、DELETE等，以遵循最小权限原则，降低数据安全风险。
2. 确保网络连接稳定
由于备份数据需要通过网络传输，因此网络的稳定性和传输速度会影响备份的效率和完整性。在备份前，最好检查一下到远程数据库的网络连接是否稳定。
3. 考虑网络安全性
通过网络备份数据存在一定的安全隐患。建议采用以下措施提高安全性：
- 为备份专门创建一个备份用户，只赋予必要的权限。
- 在可能的情况下，使用SSL加密连接。
- 如果备份的数据很敏感，考虑在备份完成后立即加密备份文件。
4. 注意备份文件的传输和存储
备份完成后，还需要将备份文件从远程主机传输到本地或其他存储位置。传输过程同样需要注意安全性，可以使用scp等加密传输工具。备份文件的存储也需要有适当的访问控制措施。
4. 忽略选项
在使用mysqldump备份数据库时，有时我们可能不需要备份某些对象，如建库语句、建表语句、删除表语句、数据、触发器等。mysqldump提供了一些选项来忽略这些对象。
4.1 备份时忽略建库、建表语句
如果只需要备份数据，而不需要备份建库和建表语句，可以使用--no-create-db或-n选项忽略建库语句，使用--no-create-info或-t选项忽略建表语句。例如:
mysqldump -u root -p --no-create-db --no-create-info mydb > mydb_data.sql
这会将mydb数据库的数据导出到mydb_data.sql文件中，但不包含建库和建表语句。
4.2 备份时忽略删除表的语句
默认情况下，mysqldump会在导出的SQL文件中添加DROP TABLE IF EXISTS语句，用于在导入数据前删除已存在的同名表。如果不需要这些删除表的语句，可以使用--skip-add-drop-table选项忽略它们。例如:
mysqldump -u root -p --skip-add-drop-table mydb > mydb_no_drop.sql
这会将mydb数据库的结构和数据导出到mydb_no_drop.sql文件中，但不包含DROP TABLE IF EXISTS语句。
4.3 备份时忽略数据，只备份表结构
如果只需要备份表结构，而不需要备份数据，可以使用--no-data或-d选项忽略数据。例如:
mysqldump -u root -p --no-data mydb > mydb_structure.sql
这会将mydb数据库的表结构导出到mydb_structure.sql文件中，但不包含任何数据。
4.4 备份时忽略触发器
默认情况下，mysqldump会备份表的触发器。如果不需要备份触发器，可以使用--skip-triggers选项忽略它们。例如：
mysqldump -u root -p --no-data --skip-triggers mydb > mydb_structure_no_triggers.sql
5. 存储过程和事件的说明
从MySQL 8.0起，默认不再备份存储过程和事件。如果需要备份 存储过程 和 事件，需要使用--routines、--events选项。
备份存储过程
使用--routines选项备份存储过程和函数：
mysqldump -u root -p --routines dbname > dbname.sql
备份事件
使用--events选项备份事件：
mysqldump -u root -p --events dbname > dbname.sql
还原存储过程和事件
mysql -u root -p dbname < dbname.sql
6. 定时备份
定时备份是指通过计划任务或定时任务，按照预设的时间间隔自动执行数据库备份操作，以确保数据库能够持续、稳定、安全地运行。
6.1 Linux下使用crontab定时备份
在Linux系统中，可以使用crontab实现定时备份MySQL数据库。crontab是Linux的内置服务，允许用户在指定的时间间隔执行特定的命令或脚本。
备份脚本
首先，创建一个备份脚本，例如backup_mysql.sh，内容如下:
#!/bin/bash # 设置MySQL连接信息 DB_USER="root" DB_PASSWORD="your_password" DB_NAME="your_database" # 设置备份文件名,包含日期时间戳 BACKUP_FILE="backup_$(date +%Y%m%d_%H%M%S).sql" # 执行mysqldump命令备份数据库 mysqldump -u$DB_USER -p$DB_PASSWORD $DB_NAME > /path/to/backup/$BACKUP_FILE
根据你的实际情况修改脚本中的MySQL连接信息、备份文件名格式和备份文件存储路径。
设置脚本文件权限
为了能够执行备份脚本，需要给脚本文件添加可执行权限：
chmod +x /path/to/backup/backup_mysql.sh
crontab定时任务
执行以下命令编辑当前用户的crontab定时任务：
crontab -e
这会打开一个文本编辑器，在其中添加定时备份任务，例如:
# 每天凌晨2点执行备份脚本 0 2 * * * /path/to/backup/backup_mysql.sh
其中，0 2 * * *表示每天凌晨2点执行一次备份脚本。可以根据需要调整执行时间。可以在备份脚本中添加日志记录功能，或者在备份文件存储目录中查看是否生成了新的备份文件，以验证定时备份是否正常执行。
编辑完成后，保存文件并退出编辑器。这会自动将定时任务安装到crontab服务中。
需要注意的是，crontab定时任务是以安装它的用户的身份运行的。因此，备份脚本中使用的MySQL用户必须有足够的权限执行备份操作。
注意备份文件存储目录必须对crontab任务的用户有写入权限，否则备份会失败。
6.2 Windows下使用计划任务定时备份
在Windows系统下，可以使用任务计划程序创建定时备份任务，自动执行mysqldump命令备份数据库。
备份脚本
首先，我们可以创建一个PowerShell脚本来执行MySQL数据库备份，然后在批处理文件中调用该脚本。PowerShell脚本文件(如backup.ps1)，内容如下：
$backupDir = "C:\mysql_backup" $dateTime = Get-Date -Format "yyyyMMdd_HHmmss" $backupFile = Join-Path $backupDir "all_databases_$dateTime.sql" mysqldump -u root -p123456 --all-databases > $backupFile
这个脚本会将所有数据库备份到C:\mysql_backup目录下，备份文件名包含当前日期和时间。
请根据实际情况修改脚本中的备份目录、MySQL用户名和密码等信息。
在批处理中执行ps1文件
创建一个批处理文件(如run_backup.bat)，内容如下：
@echo off powershell.exe -ExecutionPolicy Bypass -File "C:\backup.ps1"
这个批处理文件会调用PowerShell命令行，并执行C:\backup.ps1脚本。-ExecutionPolicy Bypass参数用于绕过PowerShell的执行策略限制，以允许运行未签名的脚本。请将C:\backup.ps1替换为实际的备份脚本路径。
创建计划任务
按照前面步骤中的说明，在任务计划程序中创建一个新任务。在"操作"选项卡中，将"程序或脚本"设置为批处理文件的完整路径(如C:\run_backup.bat)，而不是直接指定PowerShell脚本。
7. 数据恢复
数据恢复是指将备份文件中的数据重新导入到MySQL数据库中，以恢复数据库到备份时的状态。
7.1 使用mysql命令恢复数据
可以使用mysql命令直接执行备份文件中的SQL语句来恢复数据。例如：
mysql -u username -p database_name < backup_file.sql
其中，username为MySQL用户名，database_name为要恢复的目标数据库名，backup_file.sql为备份文件路径。
7.2 使用source命令恢复数据
可以先登录MySQL，然后使用SOURCE命令执行备份文件。这种方式适合备份文件较大的情况，可以避免内存不足的错误。现在，先登录MySQL：
mysql -u username -p
然后使用SOURCE命令执行备份文件：
mysql> SOURCE /path/to/backup_file.sql
上面这个命令是在MySQL交互环境中执行的，SOURCE命令则是MySQL提供的一个额外的功能，用于批量执行SQL语句。它读取指定的SQL脚本文件，并将文件中的SQL语句逐条执行，就像这些语句是在MySQL命令行中直接输入的一样。但是需要指出，SOURCE本身并不属于SQL语言，而是MySQL提供的一个额外的命令。
7.3 使用cat命令和管道恢复数据
还可以使用cat命令读取备份文件，并通过Linux管道将其传递给mysql命令执行：
cat backup_file.sql | mysql -u username -p database_name
例如：
cat mydb.sql | mysql -u root -p mydb
7.4 恢复到新数据库
如果要将备份文件恢复到一个新的数据库，需要先创建该数据库，然后再执行恢复操作。先创建新数据库：
mysql> CREATE DATABASE new_database;
然后使用上述任意一种方式恢复备份文件到新数据库，例如：
mysql -u username -p new_database < backup_file.sql
或者：
cat backup_file.sql | mysql -u username -p new_database
