MYSQL备份与恢复文档.docx
- 文档编号:6085334
- 上传时间:2023-01-03
- 格式:DOCX
- 页数:27
- 大小:22.32KB
MYSQL备份与恢复文档.docx
《MYSQL备份与恢复文档.docx》由会员分享,可在线阅读,更多相关《MYSQL备份与恢复文档.docx(27页珍藏版)》请在冰豆网上搜索。
MYSQL备份与恢复文档
MYSQL备份与恢复
MYSQLDUMP
MYSQLDUMP备份
[root@MySqlAdata]#mysqldump-S/tmp/mysql3307.sock[--databasestest[--tablestest2test3]|-A]-R--user=root-pmysql[-x|--single-transaction|-l]–-quick--flush-logs--master-data=2>xxx.dmp
-S:
指定本地连接SOCKET
--database:
指定需要备份的数据库
-A:
备份所有数据库
-R:
指定备份存储过程和函数
--user:
指定用来连接数据库的用户
-p:
指定用户的名
-x:
给所有表加锁,即指定了-A。
原理使用FLUSHTABLESWITHREADLOCK对所有表进行锁定
-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信息中提取
PS:
如果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.
14052310:
13:
24mysqlbackup:
INFO:
Startinglogscanfromlsn1642271232.
PS:
备份起始的innodb的LSN
14052310:
13:
24mysqlbackup:
INFO:
Copyinglog...
14052310:
13:
24mysqlbackup:
INFO:
Logcopied,lsn1642271244.
14052310:
13:
26mysqlbackup:
INFO:
Copying/mysql/data/mysql/innodb_index_stats.ibd(Antelopefileformat).
……
14052310:
13:
26mysqlbackup:
INFO:
Copying/mysql/data/test/test.ibd(Antelopefileformat).
14052310:
13:
27mysqlbackup:
INFO:
Copying/mysql/data/test/test2.ibd(Antelopefileformat).
14052310:
13:
27mysqlbackup:
INFO:
Copying/mysql/data/test/tt1.ibd(Antelopefileformat).
mysqlbackup:
ProgressinMB:
200400600
14052310:
13:
44mysqlbackup:
INFO:
Completingthecopyofinnodbfiles.
PS:
备份innodb数据文件完毕
14052310:
13:
47mysqlbackup:
INFO:
Preparingtolocktables:
Connectedtomysqldserver.
14052310:
13:
47mysqlbackup:
INFO:
Startingtolockallthetables...
PS:
锁定所有的表,为其它非innodb引擎的表备份开始做准备
14052310:
13:
47mysqlbackup:
INFO:
Alltablesarelockedandflushedtodisk
14052310:
13:
47mysqlbackup:
INFO:
Openingbackupsourcedirectory'/mysql/data/'
14052310:
13:
47mysqlbackup:
INFO:
Startingtobackupallnon-innodbfilesin
PS:
开始备份(使用CP命令)复制非innodb的表
subdirectoriesof'/mysql/data/'
14052310:
13:
47mysqlbackup:
INFO:
Copyingthedatabasedirectory'mysql'
14052310:
13:
47mysqlbackup:
INFO:
Copyingthedatabasedirectory'performance_schema'
14052310:
13:
47mysqlbackup:
INFO:
Copyingthedatabasedirectory'test'
mysqlbackup:
ProgressinMB:
800
14052310:
13:
50mysqlbackup:
INFO:
Completingthecopyofallnon-innodbfiles.
14052310:
13:
53mysqlbackup:
INFO:
Acopieddatabasepagewasmodifiedat1642271244.
(Thisisthehighestlsnfoundonpage)
Scannedloguptolsn1642276760.
Wasabletoparsetheloguptolsn1642276760.
PS;记录复制所有非innodb的表的复制完成的LSN,并将从innodb表开始复制时到非innodb表复制完成后(实际是到开始锁全部表时,因为所全部表后食物不能处理)所产生的innodb-log记录到ibbackup_logfile中
Maximumpagenumberforalogrecord2523
14052310:
13:
53mysqlbackup:
INFO:
Alltablesunlocked
14052310:
13:
53mysqlbackup:
INFO:
AllMySQLtableswerelockedfor5.773seconds.
14052310:
13:
53mysqlbackup:
INFO:
FullBackupoperationcompletedsuccessfully.
14052310:
13:
53mysqlbackup:
INFO:
Backupcreatedindirectory'/mysql/backup'
14052310:
13:
53mysqlbackup:
INFO:
MySQLbinlogposition:
filenamelogbin.000019,position63060
PS:
记录该店的bin-logPOS
-------------------------------------------------------------
ParametersSummary
-------------------------------------------------------------
StartLSN:
1642271232
EndLSN:
1642276760
-------------------------------------------------------------
[root@MySqlAdatadir]#ls
ibbackup_logfileibdata1mysqlperformance_schematest
PS:
注意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.
mysqlbackup:
INFO:
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
mysqlbackup:
INFO:
Usesposix_fadvise()forperformanceoptimization.
mysqlbackup:
INFO:
Creating14bufferseachofsize65536.
14052313:
44:
48mysqlbackup:
INFO:
Apply-logoperationstartswithfollowingthreads
1read-threads1process-threads
14052313:
44:
48mysqlbackup:
INFO:
ibbackup_logfile'screationparameters:
startlsn1642271232,endlsn1642276760,
startcheckpoint1642271244.
InnoDB:
Doingrecovery:
scanneduptologsequencenumber1642276760
mysqlbackup:
INFO:
InnoDB:
Startinganapplybatchoflogrecordstothedatabase...
InnoDB:
Progressinpercent:
0123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899Settinglogfilesizeto2145386496
InnoDB:
ProgressinMB:
10020030040050060070080090010001100120013001400150016001700180019002000
……
14052313:
45:
00mysqlbackup:
INFO:
Wewereabletoparseibbackup_logfileupto
lsn1642276760.
mysqlbackup:
INFO:
LastMySQLbinlogfileposition063060,filenamelogbin.000019
14052313:
45:
00mysqlbackup:
INFO:
Thefirstdatafileis'/mysql/backup/datadir/ibdata1'
andthenewcreatedlogfilesareat'/mysql/backup/datadir'
14052313:
45:
00mysqlbackup:
INFO:
Apply-logoperationcompletedsuccessfully.
14052313:
45:
00mysqlbackup:
INFO:
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
MySQLEnterpriseBackupversion3.8.2[2013/06/18]
Copyright(c)2003,2012,Oracleand/oritsaffiliates.AllRightsReserved.
mysqlbackup:
INFO:
Startingwithfollowingcommandline...
mysqlbackup--defaults-file=/mysql/backup/backup-f
--backup-dir=/mysql/backup--user=mysql--datadir=/mysql/datacopy-back
IMPORTANT:
Pleasecheckthatmysqlbackupruncompletessuccessfully.
Attheendofasuccessful'copy-back'runmysqlbackup
prints"mysqlbackupcompletedOK!
".
--------------------------------------------------------------------
ServerRepositoryOptions:
--------------------------------------------------------------------
datadir=/mysql/data
innodb_data_home_dir=/mysql/data
innodb_data_file_path=ibdata1:
12M:
autoextend
innodb_log_group_home_dir=/mysql/data
innodb_log_files_in_group=3
innodb_log_file_size=2147483648
innodb_page_size=16384
innodb_checksum_algorithm=innodb
innodb_undo_directory=/mysql/backup/datadir
innodb_undo_tablespaces=0
innodb_undo_logs=128
--------------------------------------------------------------------
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=2147483648
innodb_page_size=16384
innodb_checksum_algorithm=innodb
innodb_undo_directory=/mysql/backup/datadir
innodb_undo_tablespaces=0
innodb_undo_logs=128
mysqlbackup:
INFO:
Usesposix_fadvise()forperformanceoptimization.
mysqlbackup:
INFO:
Creating14bufferseachofsize16777216.
14052316:
52:
01mysqlbackup:
INFO:
Copy-backoperationstartswithfollowingthreads
1read-threads1write-threads
14052316:
52:
01mysqlbackup:
INFO:
Copying/mysql/backup/datadir/ibdata1.
14052316:
52:
03mysqlbackup:
INFO:
Copying/mysql/backup/datadir/mysql/innodb_index_stats.ibd.
14052316:
52:
03mysqlbackup:
INFO:
Copying/mysql/backup/datadir/mysql/innodb_table_stats.ibd.
14052316:
52:
03mysqlbackup:
INFO:
Copying/mysql/backup/datadir/mysql/slave_master_info.ibd.
14052316:
52:
03mysqlbackup:
INFO:
Copying/mysql/backup/datadir/mysql/slave_relay_log_info.ibd.
14052316:
52:
03mysqlbackup:
INFO:
Copying/mysql/backup/datadir/mysql/slave_worker_info.ibd.
14052316:
52:
03mysqlbackup:
INFO:
Copying/mysql/backup/datadir/mytest/name2.ibd.
14052316:
52:
03mysqlbackup:
INFO:
Copying/mysql/backup/datadir/test/name.ibd.
14
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MYSQL 备份 恢复 文档
![提示](https://static.bdocx.com/images/bang_tan.gif)