mysql 数据备份（mysqldump）

---

mysql 数据备份（mysqldump）

 

 

*************************

mysqldump 使用

 

命令格式

root@7a72b2ffd688:/# mysqldump --help
mysqldump  Ver 8.0.25 for Linux on x86_64 (MySQL Community Server - GPL)
Copyright (c) 2000, 2021, 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.

Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]                          #导出指定数据库下的表
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]     #导出多个数据库所有表
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]                  #导出所有的数据库

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 
#配置文件的读取顺序：/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 


The following groups are read: mysqldump client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file,
                        except for login file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#
                        Also read groups with concat(group, suffix)
--login-path=#          Read this path from the login file.


#用户名、密码、端口
  -u, --user=name     User for login if not current user.
  -p, --password[=name] 
                      Password to use when connecting to server. If password is
                      not given it's solicited on the tty.
  -P, --port=#        Port number to use for connection.


#导出所有数据库
  -A, --all-databases Dump all the databases. This will be same as --databases
                      with all databases selected.

#导出指定的数据库，多个数据库用空格间隔
  -B, --databases     Dump several databases. Note the difference in usage; in
                      this case no tables are given. All name arguments are
                      regarded as database names. 'USE db_name;' will be
                      included in the output.

#导出数据库下指定的表
  --tables            Overrides option --databases (-B).


#导出所有的表命名空间
  -Y, --all-tablespaces 
                      Dump all the tablespaces.

#不导出表的命名空间
  -y, --no-tablespaces 
                      Do not dump any tablespace information.

#创建数据库语句前，添加删除语句，默认不添加
  --add-drop-database Add a DROP DATABASE before each create.

#创建表语句前，添加删除语句，默认添加
  --add-drop-table    Add a DROP TABLE before each create.
                      (Defaults to on; use --skip-add-drop-table to disable.)

#创建trigger前添加删除语句，默认不添加
  --add-drop-trigger  Add a DROP TRIGGER before each create.

# insert语句前锁表
  --add-locks         Add locks around INSERT statements.
                      (Defaults to on; use --skip-add-locks to disable.)
  --allow-keywords    Allow creation of column names that are keywords.


# 从服务器上使用该参数时，执行stop slave，
  输出此时从机复制的master binary log的偏移量、文件名称
  备份完成后，执行start slave，从原位置开始主从复制
# 1 输出change master命令
# 2 在change master命令前加注释
# 该参数会锁定所有表（--loc-all-tables），除非使用--single-transactions参数
  --dump-slave[=#]    This causes the binary log position and filename of the
                      master to be appended to the dumped data output. Setting
                      the value to 1, will printit as a CHANGE MASTER command
                      in the dumped data output; if equal to 2, that command
                      will be prefixed with a comment symbol. This option will
                      turn --lock-all-tables on, unless --single-transaction is
                      specified too (in which case a global read lock is only
                      taken a short time at the beginning of the dump - don't
                      forget to read about --single-transaction below). In all
                      cases any action on logs will happen at the exact moment
                      of the dump.Option automatically turns --lock-tables off.

#主机上执行，不会停止主从服务，输出binary log的偏移量、文件名称
# 1 输出change master命令
# 2 在change master前加注释
# 该参数会锁定所有表（--lock-all-tables）,除非使用--single-transactions参数
  --master-data[=#]   This causes the binary log position and filename to be
                      appended to the output. If equal to 1, will print it as a
                      CHANGE MASTER command; if equal to 2, that command will
                      be prefixed with a comment symbol. This option will turn
                      --lock-all-tables on, unless --single-transaction is
                      specified too (in which case a global read lock is only
                      taken a short time at the beginning of the dump; don't
                      forget to read about --single-transaction below). In all
                      cases, any action on logs will happen at the exact moment
                      of the dump. Option automatically turns --lock-tables
                      off.

# 导出数据时，创建一致性的数据快照，目前只支持innoDB存储引擎
# 使用该命令时，其他连接不能使用alter table、drop table、rename table、truncate table语句
# 该参数会自动关闭 --lock-tables
  --single-transaction 
                      Creates a consistent snapshot by dumping all tables in a
                      single transaction. Works ONLY for tables stored in
                      storage engines which support multiversioning (currently
                      only InnoDB does); the dump is NOT guaranteed to be
                      consistent for other storage engines. While a
                      --single-transaction dump is in process, to ensure a
                      valid dump file (correct table contents and binary log
                      position), no other connection should use the following
                      statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
                      TRUNCATE TABLE, as consistent snapshot is not isolated
                      from them. Option automatically turns off --lock-tables.


  --apply-slave-statements 
                      Adds 'STOP SLAVE' prior to 'CHANGE MASTER' and 'START
                      SLAVE' to bottom of dump.
  --bind-address=name IP address to bind to.
  --character-sets-dir=name 
                      Directory for character set files.
  --column-statistics Add an ANALYZE TABLE statement to regenerate any existing
                      column statistics.
                      (Defaults to on; use --skip-column-statistics to disable.)
  -i, --comments      Write additional information.
                      (Defaults to on; use --skip-comments to disable.)
  --compatible=name   Change the dump to be compatible with a given mode. By
                      default tables are dumped in a format optimized for
                      MySQL. The only legal mode is ANSI.Note: Requires MySQL
                      server version 4.1.0 or higher. This option is ig