MYSQL备份与恢复文档Word文档格式.docx
- 文档编号:19083486
- 上传时间:2023-01-03
- 格式:DOCX
- 页数:27
- 大小:22.32KB
MYSQL备份与恢复文档Word文档格式.docx
《MYSQL备份与恢复文档Word文档格式.docx》由会员分享,可在线阅读,更多相关《MYSQL备份与恢复文档Word文档格式.docx(27页珍藏版)》请在冰豆网上搜索。
-l:
给要导出的表,即指定了—tables。
原理为使用了LOCKTABLExxxxREAD进行锁定
如果导出多张表,则会在一条语句中进行多个表的锁定。
--single-transaction:
不加锁,开启SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD隔离级别,UNLOCK所有的表(UNLOCK为session级别的,即自己UNLOCK自己)
STARTTRANSACTION/*!
40100WITHCONSISTENTSNAPSHOT
--quick:
如果有大表推荐使用该选项。
默认情况是先把数据全部载入内存中,再显示出来。
使用quick的话,就分批载入并显示
--flush-logs:
在开始导出实际数据前,进行了一次flushlog操作,保证后面的日志为新的binlog
--master-data=2:
以注释的方式将当前(未使用flush-logs)或新的binlog(使用flush-log)的master_log_pos信息记录到dump中。
使用1用于创建SLAVE用,包含CHANGEMASTERTO语句
恢复
RESTORE:
[root@MySqlAdata]#mysql-uroot-pmysql[dbname]<
1.sql
使用—tables选项导出后再倒入需要指定dbname
RECOVER
[root@MySqlAdata]#mysqlbinloglogbin.000002logbin.000003--start-position=15213--stop-position=18685|mysql–uroot–pmysql-S/tmp/mysql3307.sock
----start-position:
设置第一个binlog文件上的POS为起始POS
--stop-position:
设置最后一个binlog文件上的POS为终止POS
PS:
注意mysqlbinlog识别的文件顺序为命令行中的文件顺序,所有文件顺序要按数字大小排列好
---文件和POS从backup文件中的masterinfo信息中提取
如果binlog_format为ROW类型,想查看其中BINLOG的内容的话可加入-vv选项
MYSQLBACKUP
MBM全量备份与还原
备份
将mysqlback的bin目录添加入PATH环境变量中
全备:
[root@MySqlAbackup]#mysqlbackup--defaults-file=/etc/f--backup-dir=/mysql/backup--user=root--password=mysql--socket=/tmp/mysql3307.sockbackup
……
14052310:
13:
24mysqlbackup:
INFO:
Foundcheckpointatlsn1642271244.
Startinglogscanfromlsn1642271232.
PS:
备份起始的innodb的LSN
Copyinglog...
Logcopied,lsn1642271244.
26mysqlbackup:
Copying/mysql/data/mysql/innodb_index_stats.ibd(Antelopefileformat).
Copying/mysql/data/test/test.ibd(Antelopefileformat).
27mysqlbackup:
Copying/mysql/data/test/test2.ibd(Antelopefileformat).
Copying/mysql/data/test/tt1.ibd(Antelopefileformat).
mysqlbackup:
ProgressinMB:
200400600
44mysqlbackup:
Completingthecopyofinnodbfiles.
备份innodb数据文件完毕
47mysqlbackup:
Preparingtolocktables:
Connectedtomysqldserver.
Startingtolockallthetables...
锁定所有的表,为其它非innodb引擎的表备份开始做准备
Alltablesarelockedandflushedtodisk
Openingbackupsourcedirectory'
/mysql/data/'
Startingtobackupallnon-innodbfilesin
开始备份(使用CP命令)复制非innodb的表
subdirectoriesof'
Copyingthedatabasedirectory'
mysql'
performance_schema'
test'
800
50mysqlbackup:
Completingthecopyofallnon-innodbfiles.
53mysqlbackup:
Acopieddatabasepagewasmodifiedat1642271244.
(Thisisthehighestlsnfoundonpage)
Scannedloguptolsn1642276760.
Wasabletoparsetheloguptolsn1642276760.
PS;
记录复制所有非innodb的表的复制完成的LSN,并将从innodb表开始复制时到非innodb表复制完成后(实际是到开始锁全部表时,因为所全部表后食物不能处理)所产生的innodb-log记录到ibbackup_logfile中
Maximumpagenumberforalogrecord2523
Alltablesunlocked
AllMySQLtableswerelockedfor5.773seconds.
FullBackupoperationcompletedsuccessfully.
Backupcreatedindirectory'
/mysql/backup'
MySQLbinlogposition:
filenamelogbin.000019,position63060
记录该店的bin-logPOS
-------------------------------------------------------------
ParametersSummary
StartLSN:
1642271232
EndLSN:
1642276760
[root@MySqlAdatadir]#ls
ibbackup_logfileibdata1mysqlperformance_schematest
注意BACKUP操作并不复制innodb-log只是将备份时间内事物所产生的innodblogblock记录到ibbakup_log文件中
应用INNODB-LOG
[root@MySqlAdatadir]#mysqlbackup--backup-dir=/mysql/backupapply-log
MySQLEnterpriseBackupversion3.8.2[2013/06/18]
Copyright(c)2003,2012,Oracleand/oritsaffiliates.AllRightsReserved.
Startingwithfollowingcommandline...
mysqlbackup--backup-dir=/mysql/backupapply-log
IMPORTANT:
Pleasecheckthatmysqlbackupruncompletessuccessfully.
Attheendofasuccessful'
apply-log'
runmysqlbackup
prints"
mysqlbackupcompletedOK!
"
.
--------------------------------------------------------------------
BackupConfigOptions:
datadir=/mysql/backup/datadir
innodb_data_home_dir=/mysql/backup/datadir
innodb_data_file_path=ibdata1:
12M:
autoextend
innodb_log_group_home_dir=/mysql/backup/datadir
innodb_log_files_in_group=3
innodb_log_file_size=2145386496
innodb_page_size=16384
innodb_checksum_algorithm=innodb
innodb_undo_directory=/mysql/backup/datadir
innodb_undo_tablespaces=0
innodb_undo_logs=128
Usesposix_fadvise()forperformanceoptimization.
Creating14bufferseachofsize65536.
14052313:
44:
48mysqlbackup:
Apply-logoperationstartswithfollowingthreads
1read-threads1process-threads
ibbackup_logfile'
screationparameters:
startlsn1642271232,endlsn1642276760,
startcheckpoint1642271244.
InnoDB:
Doingrecovery:
scanneduptologsequencenumber1642276760
InnoDB:
Startinganapplybatchoflogrecordstothedatabase...
Progressinpercent:
0123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899Settinglogfilesizeto2145386496
10020030040050060070080090010001100120013001400150016001700180019002000
45:
00mysqlbackup:
Wewereabletoparseibbackup_logfileupto
lsn1642276760.
LastMySQLbinlogfileposition063060,filenamelogbin.000019
Thefirstdatafileis'
/mysql/backup/datadir/ibdata1'
andthenewcreatedlogfilesareat'
/mysql/backup/datadir'
Apply-logoperationcompletedsuccessfully.
Fullbackuppreparedforrecoverysuccessfully.
恢复数据文件
先暂停MYSQL服务
[root@MySqlAdata]#servicemysqldstop
ShuttingdownMySQL..[OK]
开始进行恢复操作
[root@MySqlAdata]#mysqlbackup--defaults-file=/mysql/backup/backup-f--backup-dir=/mysql/backup--user=mysql--datadir=/mysql/datacopy-back
mysqlbackup--defaults-file=/mysql/backup/backup-f
--backup-dir=/mysql/backup--user=mysql--datadir=/mysql/datacopy-back
copy-back'
ServerRepositoryOptions:
datadir=/mysql/data
innodb_data_home_dir=/mysql/data
innodb_log_group_home_dir=/mysql/data
innodb_log_file_size=2147483648
Creating14bufferseachofsize16777216.
14052316:
52:
01mysqlbackup:
Copy-backoperationstartswithfollowingthreads
1read-threads1write-threads
Copying/mysql/backup/datadir/ibdata1.
03mysqlbackup:
Copying/mysql/backup/datadir/mysql/innodb_index_stats.ibd.
Copying/mysql/backup/datadir/mysql/innodb_table_stats.ibd.
Copying/mysql/backup/datadir/mysql/slave_master_info.ibd.
Copying/mysql/backup/datadir/mysql/slave_relay_log_info.ibd.
Copying/mysql/backup/datadir/mysql/slave_worker_info.ibd.
Copying/mysql/backup/datadir/mytest/name2.ibd.
Copying/mysql/backup/datadir/test/name.ibd.
14
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MYSQL 备份 恢复 文档