mysql备份和恢复MySQLbackupandrecovery.docx

mysql备份和恢复MySQLbackupandrecovery.docx

《mysql备份和恢复MySQLbackupandrecovery.docx》由会员分享，可在线阅读，更多相关《mysql备份和恢复MySQLbackupandrecovery.docx（6页珍藏版）》请在冰豆网上搜索。

mysql备份和恢复MySQLbackupandrecovery

mysql备份和恢复（MySQLbackupandrecovery）

mysql备份和恢复（MySQLbackupandrecovery）

MySQLbackupandrecovery

ThisarticlediscussesthebackupandrecoverymechanismsforMySQLandhowtomaintaindatatables,includingthetwomaintabletypes:

MyISAMandInnodb,andtheMySQLversionofthearticledesignedfor5.0.22.FreebackuptoolscurrentlysupportedbyMySQL:

mysqldump,mysqlhotcopy,alsocanmakeabackupusingSQLsyntax:

BACKUPTABLEorSELECTINTOOUTFILE,orbinarylogbackup（binlog）,canalsobedirectlycopythedatafilesandrelatedconfigurationfiles.TheMyISAMtableissavedinafileform,soit'srelativelyeasytobackup,andseveralofthemethodsmentionedabovecanbeused.Innodballthetablesarestoredinthesamedataintheibdata1file（possiblymultiplefiles,orfiletablespaceindependent）,relativelygoodsolutionscanbefreebackup,copyadatafile,backupbinlog,ormysqldump.

1,mysqldump

1.1backup

MysqldumpusestheSQLlevelbackupmechanism,whichleadsthedatatableintoSQLscriptfile,whichisrelativelysuitableforupgradingbetweendifferentversionsofMySQL,andthisisthemostcommonlyusedbackupmethod.Nowlet'stalkaboutsomeofthemainparametersofmysqldump:

--compatible=name,ittellsmysqldumpthattheexporteddatawillbecompatiblewitheitherthedatabaseortheolderversionoftheMySQLserver.

ValuescanbeANSI,mysql323,mysql40,PostgreSQL,Oracle,MSSQL,DB2,maxdb,no_key_options,no_tables_options,no_field_options,etc.severalvaluesshouldbeusedandseparatedbycommas.Ofcourse,itisnotguaranteedtobefullycompatible,butcompatibleasmuchaspossible.

Thedataexportedby--complete-insertand-cusesafullINSERTthatcontainsfieldnames,thatis,writeallvaluesinoneline.Doingsoimprovestheinsertionefficiency,butmaybeaffectedbythemax_allowed_packetparametercausingtheinsertiontofail.Therefore,youneedtobecarefulwiththisparameter,atleastIdon'trecommendit.

--default-character-set=charsetspecifiestheexportdatawithwhatcharacterset,ifthedatatableisnotthedefaultLatin1characterset,thenexportthisoptionmustbespecified,otherwiseagainimportdatawillproducegarbled.

--disable-keystoldmysqldumpatthebeginningandendoftheINSERTstatementadded40000ALTERTABLEtableDISABLE/*!

*//*KEYS;and40000ALTERTABLEtableENABLEKEYS!

*/;thisstatementcangreatlyimprovethespeedoftheinsertstatement,becauseitisintheinsertallthedataafterthe

reconstructionoftheindex.ThisoptionisonlysuitableforMyISAMtables.

--extended-insert=true|false,bydefault,mysqldumpopens--complete-insertmode,soifyoudon'twanttouseit,usethisoptionandsetittofalse.

--hex-blobexportsbinarystringfieldsusingthesixteendecimalformat.Ifyouhavebinarydata,youmustusethisoption.ThefieldtypesthataffectareBINARY,VARBINARY,andBLOB.

--lock-all-tables,-x,beforeyoustartexporting,submitrequeststolockalltablesinalldatabasestoensuredataconsistency.Thisisaglobalreadlock,andautomaticallyclosesthe--single-transactionand--lock-tablesoptions.

--lock-tablesissimilarto--lock-all-tables,butlocksthecurrentlyexporteddatatableinsteadoflockingallthetablesunderthelibraryatonce.ThisoptionappliesonlytotheMyISAMtable,andifitisanInnodbtable,youcanusethe--single-transactionoption.

--no-create-info,-tonlyexportsdatawithoutaddingtheCREATETABLEstatement.

--no-data,-ddoesnotexportanydata,onlyexportthedatabasetablestructure.

--opt,thisisjustashortcutoption,equivalenttoaddingthe--add-drop-tables--add-locking--create-option

--disable-keys--extended-insert--lock-tables--quick--set-charsetoptionatthesametime.Thisoptionallowsmysqldumptoexportdataquickly,andtheexporteddatacanbequicklyreturned.Thisoptionisturnedonbydefault,butcanbedisabledby--skip-opt.Notethatifyourunmysqldumpwithoutspecifyingthe--quickor--optoption,youwillputtheentireresultsetinmemory.Problemscanariseifyou

exportlargedatabases.

--quick,

-qthisoptionisusefulwhenexportinglargetables,whichforcemysqldumptoretrieverecordsdirectlyfromtheserverquery,andcachethemdirectlyintomemorywithoutobtainingallrecords.

--routines,-Rexportstoredprocedures,andcustomfunctions.

--single-transactionthisoptionprovidesaBEGINSQLstatementbeforeexportingdata,andBEGINdoesnotblockanyapplicationandguaranteestheconsistencyofthedatabasewhenexporting.Itappliesonlytotransactiontables,suchasInnoDBandBDB.Thisoptionandthe--lock-tablesoptionaremutuallyexclusivebecauseLOCKTABLEScausesanypendingtransactionstobeimplicitlycommitted.Ifyouwanttoexportlargetables,youshouldcomb