mysqldump生成SQL脚本方式备份数据库
备份脚本：mysql-dump.sh#!/bin/bashexport MYSQL_HOME=/usr/local/Percona-Server-5.5.21-rel25.0-227.Linux.x86_64/export PATH=$MYSQL_HOME/bin:$PATHmysql_dump_dir=/data/backup/mysqldumpmysql_username="YOURS"mysql_password="YOURS"mysql_databases="DBNAME1 DBNAME2 DBNAME3"timeStart=$(date '+%Y%m%d%H%M%S')sqlfile=$mysql_dump_dir/dump-$timeStart.sqlmysqldump --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参数：
FormatOption FileDescriptionIntroducedRemoved--add-drop-databaseadd-drop-databaseAdd a DROP DATABASE statement before each CREATE DATABASE statement--add-drop-tableadd-drop-tableAdd a DROP TABLE statement before each CREATE TABLE statement--add-locksadd-locksSurround each table dump with LOCK TABLES and UNLOCK TABLES statements--all-databasesall-databasesDump all tables in all databases--allow-keywordsallow-keywordsAllow creation of column names that are keywords--apply-slave-statementsapply-slave-statementsInclude STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output5.5.3--bind-address=ip_addressbind-addressUse the specified network interface to connect to the MySQL Server5.5.8--commentscommentsAdd comments to the dump file--compactcompactProduce more compact output--compatible=name[,name,...]compatibleProduce output that is more compatible with other database systems or with older MySQL servers--complete-insertcomplete-insertUse complete INSERT statements that include column names--create-optionscreate-optionsInclude all MySQL-specific table options in CREATE TABLE statements--databasesdatabasesDump several databases--debug[=debug_options]debugWrite a debugging log--debug-checkdebug-checkPrint debugging information when the program exits--debug-infodebug-infoPrint debugging information, memory and CPU statistics when the program exits--default-auth=plugindefault-auth=pluginThe authentication plugin to use5.5.9--default-character-set=charset_namedefault-character-setUse charset_name as the default character set--delayed-insertdelayed-insertWrite INSERT DELAYED statements rather than INSERT statements--delete-master-logsdelete-master-logsOn a master replication server, delete the binary logs after performing the dump operation--disable-keysdisable-keysFor each table, surround the INSERT statements with statements to disable and enable keys--dump-datedump-dateInclude dump date as "Dump completed on" comment if --comments is given--dump-slave[=value]dump-slaveInclude CHANGE MASTER statement that lists binary log coordinates of slave's master5.5.3--eventseventsDump events from the dumped databases--extended-insertextended-insertUse multiple-row INSERT syntax that include several VALUES lists--fields-enclosed-by=stringfields-enclosed-byThis option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE--fields-escaped-byfields-escaped-byThis option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE--fields-optionally-enclosed-by=stringfields-optionally-enclosed-byThis option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE--fields-terminated-by=stringfields-terminated-byThis option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE--first-slavefirst-slaveDeprecated; use --lock-all-tables instead5.5.3--flush-logsflush-logsFlush the MySQL server log files before starting the dump--flush-privilegesflush-privilegesEmit a FLUSH PRIVILEGES statement after dumping the mysql database--helpDisplay help message and exit--hex-blobhex-blobDump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263)--hosthostHost to connect to (IP address or hostname)--ignore-table=db_name.tbl_nameignore-tableDo not dump the given table--include-master-host-portinclude-master-host-portInclude MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with --dump-slave5.5.3--insert-ignoreinsert-ignoreWrite INSERT IGNORE statements rather than INSERT statements--lines-terminated-by=stringlines-terminated-byThis option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE--lock-all-tableslock-all-tablesLock all tables across all databases--lock-tableslock-tablesLock all tables before dumping them--log-error=file_namelog-errorAppend warnings and errors to the named file--master-data[=value]master-dataWrite the binary log file name and position to the output--max_allowed_packet=valuemax_allowed_packetThe maximum packet length to send to or receive from the server--net_buffer_length=valuenet_buffer_lengthThe buffer size for TCP/IP and socket communication--no-autocommitno-autocommitEnclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements--no-create-dbno-create-dbThis option suppresses the CREATE DATABASE statements--no-create-infono-create-infoDo not write CREATE TABLE statements that re-create each dumped table--no-datano-dataDo not dump table contents--no-set-namesno-set-namesSame as --skip-set-charset--no-tablespacesno-tablespacesDo not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output--optoptShorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.--order-by-primaryorder-by-primaryDump each table's rows sorted by its primary key, or by its first unique index--password[=password]passwordThe password to use when connecting to the server--pipeOn Windows, connect to server using a named pipe--plugin-dir=pathplugin-dir=pathThe directory where plugins are located5.5.9--port=port_numportThe TCP/IP port number to use for the connection--quickquickRetrieve rows for a table from the server a row at a time--quote-namesquote-namesQuote identifiers within backtick characters--replacereplaceWrite REPLACE statements rather than INSERT statements--result-file=fileresult-fileDirect output to a given file--routinesroutinesDump stored routines (procedures and functions) from the dumped databases--set-charsetset-charsetAdd SET NAMES default_character_set to the output--single-transactionsingle-transactionThis option issues a BEGIN SQL statement before dumping data from the server--skip-add-drop-tableskip-add-drop-tableDo not add a DROP TABLE statement before each CREATE TABLE statement--skip-add-locksskip-add-locksDo not add locks--skip-commentsskip-commentsDo not add comments to the dump file--skip-compactskip-compactDo not produce more compact output--skip-disable-keysskip-disable-keysDo not disable keys--skip-extended-insertskip-extended-insertTurn off extended-insert--skip-optskip-optTurn off the options set by --opt--skip-quickskip-quickDo not retrieve rows for a table from the server a row at a time--skip-quote-namesskip-quote-namesDo not quote identifiers--skip-set-charsetskip-set-charsetSuppress the SET NAMES statement--skip-triggersskip-triggersDo not dump triggers--skip-tz-utcskip-tz-utcTurn off tz-utc--ssl-ca=file_namessl-caThe path to a file that contains a list of trusted SSL CAs--ssl-capath=dir_namessl-capathThe path to a directory that contains trusted SSL CA certificates in PEM format--ssl-cert=file_namessl-certThe name of the SSL certificate file to use for establishing a secure connection--ssl-cipher=cipher_listssl-cipherA list of allowable ciphers to use for SSL encryption--ssl-key=file_namessl-keyThe name of the SSL key file to use for establishing a secure connection--ssl-verify-server-certssl-verify-server-certThe server's Common Name value in its certificate is verified against the host name used when connecting to the server--tab=pathtabProduce tab-separated data files--tablestablesOverride the --databases or -B option--triggerstriggersDump triggers for each dumped table--tz-utctz-utcAdd SET TIME_ZONE='+00:00' to the dump file--user=user_nameuserThe MySQL user name to use when connecting to the server--verboseVerbose mode--versionDisplay version information and exit--where='where_condition'whereDump only rows selected by the given WHERE condition--xmlxmlProduce XML output
页:
[1]