MySQL · 工具 · mysqldump

语法

1
2
3

Usage: mysqldump [OPTIONS] database [tables] > [backupfile.sql]
       mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] > [backupfile.sql]
       mysqldump [OPTIONS] --all-databases [OPTIONS] > [backupfile.sql]

备份

# 备份所有库
mysqldump -u<user> -p<password> --all-databases > backup.sql

# 备份指定库
mysqldump -u<user> -p<password> --databases school hospital > backup.sql

# 备份指定表
mysqldump -u<user> -p<password> school table_name > backup.sql

# 导出表结构，无数据
mysqldump -u<user> -p<password> --all--database --no-data > backup.sql

# 备份存储过程
mysqldump --user=sre --password='xx' --single-transaction --master-data=2 -n -d -t -R user > dump.sql

# 按条件备份
mysqldump -usre -p'xx' -t user uc_event_logs_bak --where="id > 46005503" > uc_event_logs_bak2.sql

# 后台执行任务
nohup mysql -usre -p'xx' user < uc_event_logs_bak2.sql 2> myout.file &

# 备份InnoDB
mysqldump --socket=mysql.sock --user=user --password=pass --single-transaction --flush-logs --master-data=2 --all-databases --extended-insert --routines --events > dump.sql

# 备份Myisam
mysqldump --socket=mysql.sock --user=user --password=pass --lock-all-tables --flush-logs --master-data=2 --all-databases --extended-insert --routines --events > dump.sql

## --socket: sock文件
## --user: 用户名
## --password: 密码
## --single-transaction: 备份期间不加锁
## --flush-logs: 开始导出之前刷新日志
## --master-data: 该选项将 binlog 的位置和文件名追加到输出文件中
## --all-databases: 备份所有库
## --extended-insert: 使用具有多个VALUES列的INSERT语法，这样使导出文件更小，并加速导入的速度
## --routines: 导出存储过程以及自定义函数
## --events: 导出事件
## --triggers: 导出触发器，该选项默认启用
## 默认导出视图

Table：mysqldump Options

Option Name	Description
–add-drop-database	Add DROP DATABASE statement before each CREATE DATABASE statement
–add-drop-table	Add DROP TABLE statement before each CREATE TABLE statement
–add-drop-trigger	Add DROP TRIGGER statement before each CREATE TRIGGER statement
–add-locks	Surround each table dump with LOCK TABLES and UNLOCK TABLES statements
–all-databases	Dump all tables in all databases
–allow-keywords	Allow creation of column names that are keywords
–apply-slave-statements	Include STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output
–bind-address	Use specified network interface to connect to MySQL Server
–comments	Add comments to dump file
–compact	Produce more compact output
–compatible	Produce output that is more compatible with other database systems or with older MySQL servers
–complete-insert	Use complete INSERT statements that include column names
–compress	Compress all information sent between client and server
–create-options	Include all MySQL-specific table options in CREATE TABLE statements
–databases	Interpret all name arguments as database names
–debug	Write debugging log
–debug-check	Print debugging information when program exits
–debug-info	Print debugging information, memory, and CPU statistics when program exits
–default-auth	Authentication plugin to use
–default-character-set	Specify default character set
–defaults-extra-file	Read named option file in addition to usual option files
–defaults-file	Read only named option file
–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
–events	Dump events from dumped databases
–extended-insert	Use multiple-row INSERT syntax
–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
–help	Display help message and exit
–host	Host on which MySQL server is located
–ignore-error	Ignore specified errors
–ignore-table	Do not dump given 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
–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
–no-autocommit	Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements
–no-create-db	Do not write CREATE DATABASE statements
–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
–opt	Shorthand for –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	Password to use when connecting to server
–pipe	Connect to server using named pipe (Windows only)
–port	TCP/IP port number for connection
–print-defaults	Print default options
–quick	Retrieve rows for a table from the server a row at a time
–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
–set-charset	Add SET NAMES default_character_set to output
–set-gtid-purged	Whether to add SET @@GLOBAL.GTID_PURGED to output
–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-set-charset	Do not write SET NAMES statement
–skip-triggers	Do not dump triggers
–socket	Unix socket file or Windows named pipe to use
–tables	Override –databases or -B option
–triggers	Dump triggers for each dumped table
–user	MySQL user name to use when connecting to server
–verbose	Verbose mode
–version	Display version information and exit
–where	Dump only rows selected by given WHERE condition
–xml	Produce XML output

恢复

登录情况下：source 备份文件
不登录情况下：mysql -u -p 库名 < xx.sql

案例

# 全局只读锁
flush tables with read lock;

# 备份
mysqldump -uroot -pHaier@123 --all-databases --triggers --routines --events --single-transaction --master-data=2 > all_databases.sql

# 传输
scp master.sql root@x.x.x.x:/tmp

# 导入
mysql -uroot -p'xx' < all_databases.sql