mysql 备份表无法恢复_Mysql 备份与恢复 mysqldump

mysqldump 是 Mysql 逻辑备份的 Mysql 客户端工具，用于生成一组SQL语句，通过执行这些生成的SQL语句来重现原始的数据库对象定义和表数据。可以对一个或多个MySQL数据库进行备份或转移到另一个数据库服务器。mysqldump命令还可以生成CSV、其他分隔符文本或XML格式的数据文件。

默认不带参数的导出，导出SQL文件内容大概如下：

shell> mysqldump [ options ] db_name [ tbl_name . . . ] # 备份数据库下面的一个或多个表 shell> mysqldump [ options ] --databases db_name . . . # 备份一个或多个数据库 shell> mysqldump [ options ] --all-databases # 全库备份 mysqldump 可选的 Options：

Option 名称	描述
--add-drop-database	在创建数据库命令之前增加删除数据库命令
--add-drop-table	在创建表之前增加删除表命令
--add-drop-trigger	在创建触发器之前踢增加删除触发期命令
--add-locks	在导出表时锁定表，完成后解锁
--all-databases	出数据库服务器中全部数据库和表
--allow-keywords	允许创建关键词的列名字。
--apply-slave-statements	集群导出时增加开启和关闭slave命令
--bind-address	使用特定的IP连接数据库
--character-sets-dir	字符集所在目录
--column-statistics	添加 ANALYZE TABLE 命令
--comments	导出表注释
--compact	紧凑输出
--compatible	生成兼容性导出文件
--complete-insert	生成包含字段名的插入语句
--compress	启用压缩
--compression-algorithms	压缩算法
--create-options	在CREATE TABLE语句中包括所有MySQL特性选项。(默认为打开状态)
--databases	数据库名称
--debug	输出debug log
--debug-check	检查内存和打开文件使用说明并退出。
--debug-info	输出调试信息并退出，包括内存，CPU统计信息
--default-auth	默认验证方式
--default-character-set	特殊字符集
--defaults-extra-file	Read named option file in addition to usual option files
--defaults-file	Read only named option file
--defaults-group-suffix	Option group suffix value
--delete-master-logs	On a master replication server, delete the binary logs after performing the dump operation
--disable-keys	For each table, surround INSERT statements with statements to disable and enable keys
--dump-date	Include dump date as "Dump completed on" comment if --comments is given
--dump-slave	Include CHANGE MASTER statement that lists binary log coordinates of slave's master
--enable-cleartext-plugin	Enable cleartext authentication plugin
--events	Dump events from dumped databases
--extended-insert	Use multiple-row INSERT syntax
--fields-enclosed-by	This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA
--fields-escaped-by	This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA
--fields-optionally-enclosed-by	This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA
--fields-terminated-by	This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA
--flush-logs	Flush MySQL server log files before starting dump
--flush-privileges	Emit a FLUSH PRIVILEGES statement after dumping mysql database
--force	Continue even if an SQL error occurs during a table dump
--get-server-public-key	Request RSA public key from server
--help	Display help message and exit
--hex-blob	Dump binary columns using hexadecimal notation
--host	数据库所在地址，远程导出时指定地址
--ignore-error	忽略特定错误
--ignore-table	导出时忽略指定表
--include-master-host-port	Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with --dump-slave
--insert-ignore	Write INSERT IGNORE rather than INSERT statements
--lines-terminated-by	This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA
--lock-all-tables	Lock all tables across all databases
--lock-tables	Lock all tables before dumping them
--log-error	Append warnings and errors to named file
--login-path	Read login path options from .mylogin.cnf
--master-data	Write the binary log file name and position to the output
--max-allowed-packet	Maximum packet length to send to or receive from server
--net-buffer-length	Buffer size for TCP/IP and socket communication
--network-timeout	Increase network timeouts to permit larger table dumps
--no-autocommit	Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements
--no-create-db	禁止生成创建database 语句
--no-create-info	Do not write CREATE TABLE statements that re-create each dumped table
--no-data	Do not dump table contents
--no-defaults	Read no option files
--no-set-names	Same as --skip-set-charset
--no-tablespaces	Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output
--opt	一系列默认的参数，包括： --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset
--order-by-primary	Dump each table's rows sorted by its primary key, or by its first unique index
--password	数据库访问密码
--pipe	Connect to server using named pipe (Windows only)
--plugin-dir	Directory where plugins are installed
--port	数据库指定端口
--print-defaults	Print default options
--protocol	Connection protocol to use
--quick	Retrieve rows for a table from the server a row at a time
--quote-names	Quote identifiers within backtick characters
--replace	Write REPLACE statements rather than INSERT statements
--result-file	Direct output to a given file
--routines	Dump stored routines (procedures and functions) from dumped databases
--server-public-key-path	Path name to file containing RSA public key
--set-charset	Add SET NAMES default_character_set to output
--set-gtid-purged	Whether to add SET @@GLOBAL.GTID_PURGED to output
--shared-memory-base-name	Name of shared memory to use for shared-memory connections
--show-create-skip-secondary-engine	Exclude SECONDARY ENGINE clause from CREATE TABLE statements
--single-transaction	Issue a BEGIN SQL statement before dumping data from server
--skip-add-drop-table	Do not add a DROP TABLE statement before each CREATE TABLE statement
--skip-add-locks	Do not add locks
--skip-comments	Do not add comments to dump file
--skip-compact	Do not produce more compact output
--skip-disable-keys	Do not disable keys
--skip-extended-insert	Turn off extended-insert
--skip-opt	Turn off options set by --opt
--skip-quick	Do not retrieve rows for a table from the server a row at a time
--skip-quote-names	Do not quote identifiers
--skip-set-charset	Do not write SET NAMES statement
--skip-triggers	Do not dump triggers
--skip-tz-utc	Turn off tz-utc
--socket	Unix socket file or Windows named pipe to use
--ssl-ca	File that contains list of trusted SSL Certificate Authorities
--ssl-capath	Directory that contains trusted SSL Certificate Authority certificate files
--ssl-cert	File that contains X.509 certificate
--ssl-cipher	Permissible ciphers for connection encryption
--ssl-crl	File that contains certificate revocation lists
--ssl-crlpath	Directory that contains certificate revocation-list files
--ssl-fips-mode	Whether to enable FIPS mode on client side
--ssl-key	File that contains X.509 key
--ssl-mode	Desired security state of connection to server
--tab	Produce tab-separated data files
--tables	Override --databases or -B option
--tls-ciphersuites	Permissible TLSv1.3 ciphersuites for encrypted connections
--tls-version	Permissible TLS protocols for encrypted connections
--triggers	Dump triggers for each dumped table
--tz-utc	Add SET TIME_ZONE='+00:00' to dump file
--user	数据库登陆用户名
--verbose	导出时显示详细信息
--version	Display version information and exit
--where	Dump only rows selected by given WHERE condition
--xml	Produce XML output
--zstd-compression-level	Compression level for connections to server that use zstd compression

几个简单的例子：
1. 指定数据库备份到某dump文件(转储文件)中：

mysqldump -uroot -p123 test > test.dump2.指定远程数据库备份到某dump文件(转储文件)中

mysqldump --host=host1 --opt sourceDb

3.从备份文件恢复数据库

mysql [database name] < [backup file name]