mysqldump生成SQL脚本方式备份数据库

备份脚本：mysql-dump.sh

 

#!/bin/bash
export MYSQL_HOME=/usr/local/Percona-Server-5.5.21-rel25.0-227.Linux.x86_64/
export PATH=$MYSQL_HOME/bin:$PATH

mysql_dump_dir=/data/backup/mysqldump
mysql_username="YOURS"
mysql_password="YOURS"
mysql_databases="DBNAME1 DBNAME2 DBNAME3"

timeStart=$(date '+%Y%m%d%H%M%S')
sqlfile=$mysql_dump_dir/dump-$timeStart.sql
mysqldump --opt --user=$mysql_username --password=$mysql_password -B $mysql_databases --max_allowed_packet=1048576 --net_buffer_length=16384 > $sqlfile 

 

恢复时：使用root账号登陆到mysql命令行，执行：

 

source /data/backup/mysqldump/dump-20120629181412.sql

 

或者直接在linux的终端输入以下命令也可以

 

mysql -uYOURS -pYOURS -e"source /data/backup/mysqldump/dump-20120629181412.sql"

 

mysqldump参数：

Format Option File Description Introduced Removed

--add-drop-database	add-drop-database	Add a DROP DATABASE statement before each CREATE DATABASE statement
--add-drop-table	add-drop-table	Add a DROP TABLE statement before each CREATE TABLE statement
--add-locks	add-locks	Surround each table dump with LOCK TABLES and UNLOCK TABLES statements
--all-databases	all-databases	Dump all tables in all databases
--allow-keywords	allow-keywords	Allow creation of column names that are keywords
--apply-slave-statements	apply-slave-statements	Include STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output	5.5.3
--bind-address=ip_address	bind-address	Use the specified network interface to connect to the MySQL Server	5.5.8
--comments	comments	Add comments to the dump file
--compact	compact	Produce more compact output
--compatible=name[,name,...]	compatible	Produce output that is more compatible with other database systems or with older MySQL servers
--complete-insert	complete-insert	Use complete INSERT statements that include column names
--create-options	create-options	Include all MySQL-specific table options in CREATE TABLE statements
--databases	databases	Dump several databases
--debug[=debug_options]	debug	Write a debugging log
--debug-check	debug-check	Print debugging information when the program exits
--debug-info	debug-info	Print debugging information, memory and CPU statistics when the program exits
--default-auth=plugin	default-auth=plugin	The authentication plugin to use	5.5.9
--default-character-set=charset_name	default-character-set	Use charset_name as the default character set
--delayed-insert	delayed-insert	Write INSERT DELAYED statements rather than INSERT statements
--delete-master-logs	delete-master-logs	On a master replication server, delete the binary logs after performing the dump operation
--disable-keys	disable-keys	For each table, surround the INSERT statements with statements to disable and enable keys
--dump-date	dump-date	Include dump date as "Dump completed on" comment if --comments is given
--dump-slave[=value]	dump-slave	Include CHANGE MASTER statement that lists binary log coordinates of slave's master	5.5.3
--events	events	Dump events from the dumped databases
--extended-insert	extended-insert	Use multiple-row INSERT syntax that include several VALUES lists
--fields-enclosed-by=string	fields-enclosed-by	This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-escaped-by	fields-escaped-by	This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-optionally-enclosed-by=string	fields-optionally-enclosed-by	This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-terminated-by=string	fields-terminated-by	This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--first-slave	first-slave	Deprecated; use --lock-all-tables instead		5.5.3
--flush-logs	flush-logs	Flush the MySQL server log files before starting the dump
--flush-privileges	flush-privileges	Emit a FLUSH PRIVILEGES statement after dumping the mysql database
--help		Display help message and exit
--hex-blob	hex-blob	Dump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263)
--host	host	Host to connect to (IP address or hostname)
--ignore-table=db_name.tbl_name	ignore-table	Do not dump the given table
--include-master-host-port	include-master-host-port	Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with --dump-slave	5.5.3
--insert-ignore	insert-ignore	Write INSERT IGNORE statements rather than INSERT statements
--lines-terminated-by=string	lines-terminated-by	This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--lock-all-tables	lock-all-tables	Lock all tables across all databases
--lock-tables	lock-tables	Lock all tables before dumping them
--log-error=file_name	log-error	Append warnings and errors to the named file
--master-data[=value]	master-data	Write the binary log file name and position to the output
--max_allowed_packet=value	max_allowed_packet	The maximum packet length to send to or receive from the server
--net_buffer_length=value	net_buffer_length	The buffer size for TCP/IP and socket communication
--no-autocommit	no-autocommit	Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements
--no-create-db	no-create-db	This option suppresses the CREATE DATABASE statements
--no-create-info	no-create-info	Do not write CREATE TABLE statements that re-create each dumped table
--no-data	no-data	Do not dump table contents
--no-set-names	no-set-names	Same as --skip-set-charset
--no-tablespaces	no-tablespaces	Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output
--opt	opt	Shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.
--order-by-primary	order-by-primary	Dump each table's rows sorted by its primary key, or by its first unique index
--password[=password]	password	The password to use when connecting to the server
--pipe		On Windows, connect to server using a named pipe
--plugin-dir=path	plugin-dir=path	The directory where plugins are located	5.5.9
--port=port_num	port	The TCP/IP port number to use for the connection
--quick	quick	Retrieve rows for a table from the server a row at a time
--quote-names	quote-names	Quote identifiers within backtick characters
--replace	replace	Write REPLACE statements rather than INSERT statements
--result-file=file	result-file	Direct output to a given file
--routines	routines	Dump stored routines (procedures and functions) from the dumped databases
--set-charset	set-charset	Add SET NAMES default_character_set to the output
--single-transaction	single-transaction	This option issues a BEGIN SQL statement before dumping data from the server
--skip-add-drop-table	skip-add-drop-table	Do not add a DROP TABLE statement before each CREATE TABLE statement
--skip-add-locks	skip-add-locks	Do not add locks
--skip-comments	skip-comments	Do not add comments to the dump file
--skip-compact	skip-compact	Do not produce more compact output
--skip-disable-keys	skip-disable-keys	Do not disable keys
--skip-extended-insert	skip-extended-insert	Turn off extended-insert
--skip-opt	skip-opt	Turn off the options set by --opt
--skip-quick	skip-quick	Do not retrieve rows for a table from the server a row at a time
--skip-quote-names	skip-quote-names	Do not quote identifiers
--skip-set-charset	skip-set-charset	Suppress the SET NAMES statement
--skip-triggers	skip-triggers	Do not dump triggers
--skip-tz-utc	skip-tz-utc	Turn off tz-utc
--ssl-ca=file_name	ssl-ca	The path to a file that contains a list of trusted SSL CAs
--ssl-capath=dir_name	ssl-capath	The path to a directory that contains trusted SSL CA certificates in PEM format
--ssl-cert=file_name	ssl-cert	The name of the SSL certificate file to use for establishing a secure connection
--ssl-cipher=cipher_list	ssl-cipher	A list of allowable ciphers to use for SSL encryption
--ssl-key=file_name	ssl-key	The name of the SSL key file to use for establishing a secure connection
--ssl-verify-server-cert	ssl-verify-server-cert	The server's Common Name value in its certificate is verified against the host name used when connecting to the server
--tab=path	tab	Produce tab-separated data files
--tables	tables	Override the --databases or -B option
--triggers	triggers	Dump triggers for each dumped table
--tz-utc	tz-utc	Add SET TIME_ZONE='+00:00' to the dump file
--user=user_name	user	The MySQL user name to use when connecting to the server
--verbose		Verbose mode
--version		Display version information and exit
--where='where_condition'	where	Dump only rows selected by the given WHERE condition
--xml	xml	Produce XML output