mysql备份和恢复MySQLbackupandrecoveryWord文件下载.docx
- 文档编号:16053183
- 上传时间:2022-11-18
- 格式:DOCX
- 页数:6
- 大小:20.46KB
mysql备份和恢复MySQLbackupandrecoveryWord文件下载.docx
《mysql备份和恢复MySQLbackupandrecoveryWord文件下载.docx》由会员分享,可在线阅读,更多相关《mysql备份和恢复MySQLbackupandrecoveryWord文件下载.docx(6页珍藏版)》请在冰豆网上搜索。
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- mysql 备份 恢复 MySQLbackupandrecovery