溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
數(shù)據(jù)恢復(fù)篇
XtraBackup工具介紹：
現(xiàn)在我們給MySQL做備份的時(shí)候經(jīng)常會(huì)考慮到數(shù)據(jù)量的增長(zhǎng),數(shù)據(jù)量較小的時(shí)候用mysqldump,隨著數(shù)據(jù)量越來越大mysqldump也就不太合適了.第一不支持增量備份,第二恢復(fù)的時(shí)候也較慢.這里推薦使用Percona公司的XtraBackup.
簡(jiǎn)單介紹一下熱備份工具XtraBackup,它是Percona公司推出的一款熱備份工具,備份的時(shí)候不影響數(shù)據(jù)讀寫操作,是商業(yè)工具HotBackup的一個(gè)替代(現(xiàn)在應(yīng)該叫MySQL Enterprise Backup這個(gè)工具不止可以備份InnoDB還可以備份MyISAM等等)
XtraBackup有兩個(gè)工具:xtrabackup和innobackupex.
xtrabackup本身只能備份InnoDB和XtraDB,不能備份MyISAM.
innobackupex本身是Hot Backup腳本修改而來,同時(shí)可以備份MyISAM和InnoDB,但是備份MyISAM需要加讀鎖.
#安裝percona-xtrabackup rpm軟件包。
[root@localhost data]# rpm -ivh percona-xtrabackup-2.3.2-1.el6.x86_64.rpm warning: percona-xtrabackup-2.3.2-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY error: Failed dependencies: libev.so.4()(64bit) is needed by percona-xtrabackup-2.3.2-1.el6.x86_64 perl(DBD::mysql) is needed by percona-xtrabackup-2.3.2-1.el6.x86_64
#安裝依賴：
yum install perl-DBD-MySQL
#安裝libev包
到http://rpmfind.net/linux/rpm2html/search.php?query=libev.so.4%28%29%2864bit%29&submit=Search+...&system=&arch= 下載操作系統(tǒng)對(duì)應(yīng)的libev包，注意系統(tǒng)號(hào)與版本位數(shù)
[root@master ~]# wget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/libev-4.15-1.el6.rf.x86_64.rpm --2016-05-17 12:54:43-- ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/libev-4.15-1.el6.rf.x86_64.rpm => “l(fā)ibev-4.15-1.el6.rf.x86_64.rpm” Resolving rpmfind.net... 195.220.108.108 Connecting to rpmfind.net|195.220.108.108|:21... connected. Logging in as anonymous ... Logged in! ==> SYST ... done. ==> PWD ... done. ==> TYPE I ... done. ==> CWD (1) /linux/dag/redhat/el6/en/x86_64/dag/RPMS ... done. ==> SIZE libev-4.15-1.el6.rf.x86_64.rpm ... 74692 ==> PASV ... done. ==> RETR libev-4.15-1.el6.rf.x86_64.rpm ... done. Length: 74692 (73K) (unauthoritative) 100%[===============================================================================================================>] 74,692 187K/s in 0.4s 2016-05-17 12:54:46 (187 KB/s) - “l(fā)ibev-4.15-1.el6.rf.x86_64.rpm” saved [74692]
安裝RHEL6對(duì)應(yīng)的libev-4.15-1.el6.rf.x86_64.rpm包
[root@master ~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm warning: libev-4.15-1.el6.rf.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 6b8d79e6: NOKEY Preparing... ########################################### [100%] 1:libev ########################################### [100%]
#安裝percona-xtrabackup軟件包：
[root@localhost data]# rpm -ivh percona-xtrabackup-2.3.2-1.el6.x86_64.rpm warning: percona-xtrabackup-2.3.2-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY Preparing... ########################################### [100%] percona-xtrabackup ########################################### [100%]
操作過程如圖：
#備份恢復(fù)時(shí)數(shù)據(jù)庫必須停止的。
[root@localhost ~]# /etc/init.d/mysqld stop Shutting down MySQL.. [ OK ]
#上篇fpm封裝好的二進(jìn)制mysql，純凈。
注意：備份時(shí)候MySQL必須是運(yùn)行狀態(tài)，而在做數(shù)據(jù)恢復(fù)時(shí)候服務(wù)必須是處于停滯狀態(tài)。
重要參數(shù)：
--defaults-file=/etc/my.cnf：備份時(shí)，指定配置文件
--socket=/tmp/mysql.sock：備份時(shí)，連接到備份的mysql服務(wù)器所使用的 套接字文件
--user=root：連接備份mysqld所使用的用戶
--password=hello：連接備份mysqld所使用的用戶密碼
--no-timestamp：默認(rèn)情況下，備份目錄會(huì)使用以當(dāng)前時(shí)間為名稱創(chuàng)建一個(gè)備份目錄，加上此參數(shù)則不會(huì)做這個(gè)動(dòng)作
--apply-log：創(chuàng)建ib_logfile文件，并恢復(fù)備份期間產(chǎn)生的redo日志到ib_logfile和表空間中。注意：若備份期間產(chǎn)生了提交的事務(wù)日志，則apply-log操作會(huì)將未提交的事務(wù)回滾
--copy-back：執(zhí)行恢復(fù)數(shù)據(jù)到目標(biāo)mysqld，但是目標(biāo)mysqld的數(shù)據(jù)目錄不 能用—datadir指定，這一點(diǎn)不同與meb，而是需要使用—defaults-file指 定一個(gè)配置文件，在配置文件中指定恢復(fù)的datadir。與meb不同，恢復(fù)前必 須清空目錄數(shù)據(jù)目錄
--use-memory=1G：備份時(shí)xtrabackup可用的內(nèi)存大小，取決于操作系統(tǒng)可用的內(nèi)存量，默認(rèn)為100M，使用大的內(nèi)存可加速備份的速度
--incremental：表示為增量備份，增量備份只對(duì)innodb表有效，對(duì)于non-innodb表，例如myisam則無效，因?yàn)槊看卧隽總浞荻紩?huì)拷貝myisam相關(guān)的所有文件，不管上次備份到這次備份有沒有發(fā)生更改；而對(duì)于innodb表，增量備份則只會(huì)備份上次備份到這次備份所更改的表
--incremental-basedir=/lxw/full-back/：基于上一次的備份目錄做增量 備份
--incremental-dir=/lxw/incr-backup/：用于將增量備份恢復(fù)到全備時(shí)指定增量備份的目錄
--redo-only：增量備份恢復(fù)到之前的備份時(shí)，除了最后一個(gè)增量備份外，其他的增量備份最好指定該參數(shù)
--databases=’dba.t1 mysql performance_schema lxw.t1’：用于部分備份，可以用空格分開，指定備份哪些庫的所有表，或者哪些庫的某些表
--export：在執(zhí)行apply-log操作時(shí)，將應(yīng)用信息保存到文件中，在恢復(fù)部分備份時(shí)有用
--compact：采用壓縮備份
--rebuild-indexes：解壓縮
----記錄備份信息(如備份命令、備份開始和完成時(shí)間、binlog position、備份開始和完成的lsn等
注意：備份使用的innobackupex是2.3.1 恢復(fù)可以使用innobackupex2.3.x等小版本，但是不能夸大版本，否則會(huì)有問題。
新建一個(gè)數(shù)據(jù)目錄，和原數(shù)據(jù)目錄同樣的名字：
mkdir /data/mysqldata
#為了練習(xí)使用percona-xtrabackup全量備份了線上的mysql，scp到新機(jī)器上恢復(fù)練習(xí)。
備份完備份的時(shí)間目錄下都有xtrabackup_info 文件，記錄了你備份時(shí)的動(dòng)作。
[root@localhost 2017-03-27_04-00-01]# cat xtrabackup_info uuid = c4c25074-1260-11e7-9a6a-069e3400093a name = tool_name = innobackupex tool_command = --defaults-file=/etc/my.cnf --socket=/usr/local/mysql56/data/mysqld.sock --host=localhost --user=backuser --password=... /usr/local/mysql56/mysql_data_backup/o2o/full_backup/ tool_version = 2.3.3#工具的版本 ibbackup_version = 2.3.3 server_version = 5.6.25-log start_time = 2017-03-27 04:00:02 end_time = 2017-03-27 04:14:08 lock_time = 0 binlog_pos = filename 'mysql-bin.000285', position '764217961' innodb_from_lsn = 0 innodb_to_lsn = 393358640191 partial = N incremental = N format = file compact = N compressed = N encrypted = N
#首先恢復(fù)事務(wù)，也可以加--user=：
[root@localhost data]# innobackupex --apply-log --use-memory=10M 2017-03-27_04-00-01/ 170331 01:28:54 innobackupex: Starting the apply-log operation IMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!". innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 306a2e0) xtrabackup: cd to /data/2017-03-27_04-00-01/ xtrabackup: This target seems to be not prepared yet. xtrabackup: xtrabackup_logfile detected: size=26689536, start_lsn=(393334934174) xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 26689536 xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 26689536 xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 10485760 bytes for buffer pool (set by --use-memory parameter) InnoDB: Using atomics to ref count buffer pool pages InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Memory barrier is not used InnoDB: Compressed tables use zlib 1.2.3 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, size = 10.0M InnoDB: Completed initialization of buffer pool InnoDB: Highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 393334934174 InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages InnoDB: from the doublewrite buffer... InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Doing recovery: scanned up to log sequence number 393340176896 (22%) InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Doing recovery: scanned up to log sequence number 393345419776 (44%) InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Doing recovery: scanned up to log sequence number 393350662656 (66%) InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Doing recovery: scanned up to log sequence number 393355905536 (88%) InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Doing recovery: scanned up to log sequence number 393358640191 (99%) InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: 128 rollback segment(s) are active. InnoDB: Waiting for purge to start InnoDB: 5.6.24 started; log sequence number 393358640191 xtrabackup: Last MySQL binlog file position 764217961, file name mysql-bin.000285 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 393358641472 xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 50331648 InnoDB: Using atomics to ref count buffer pool pages InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Memory barrier is not used InnoDB: Compressed tables use zlib 1.2.3 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, size = 10.0M InnoDB: Completed initialization of buffer pool InnoDB: Setting log file ./ib_logfile101 size to 48 MB InnoDB: Setting log file ./ib_logfile1 size to 48 MB InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 InnoDB: New log files created, LSN=393358641472 InnoDB: Highest supported file format is Barracuda. InnoDB: 128 rollback segment(s) are active. InnoDB: Waiting for purge to start InnoDB: 5.6.24 started; log sequence number 393358641676 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 393358644153 170331 01:29:33 completed OK!
#恢復(fù)數(shù)據(jù)，也可以加--defaults-file=/etc/my.cnf --rsync 也可以去掉
innobackupex --copy-back --rsync 2017-03-27_04-00-01/ ........ ......... 170331 03:03:04 [01] ...done 170331 03:03:04 [01] Copying ./ecos_notice/tbl_notice_file.ibd to /data/mysqldata/ecos_notice/tbl_notice_file.ibd 170331 03:03:04 [01] ...done 170331 03:03:04 [01] Copying ./stest/tbl_test_user_info.ibd to /data/mysqldata/stest/tbl_test_user_info.ibd 170331 03:03:04 [01] ...done 170331 03:03:04 [01] Copying ./stest/tbl_test_user_info.frm to /data/mysqldata/stest/tbl_test_user_info.frm 170331 03:03:04 [01] ...done 170331 03:03:04 [01] Copying ./stest/db.opt to /data/mysqldata/stest/db.opt 170331 03:03:04 [01] ...done 170331 03:03:04 completed OK!
授權(quán)恢復(fù)數(shù)據(jù)的目錄為mysql用戶和mysql組：
[root@localhost data]# ll total 5624 drwx------ 14 root root 4096 Mar 31 02:33 2017-03-27_04-00-01 -rw-r--r-- 1 root root 74692 Mar 31 01:10 libev-4.15-1.el6.rf.x86_64.rpm drwxr-xr-x 13 mysql mysql 4096 Mar 31 02:42 mysql drwxr-xr-x 14 root root 4096 Mar 31 03:03 mysqldata drwxr-xr-x 2 mysql mysql 4096 Mar 31 02:47 mysqldata20170330 -rw-r--r-- 1 root root 5664452 Mar 31 01:09 percona-xtrabackup-2.3.2-1.el6.x86_64.rpm
#授權(quán)mysql用戶和mysql組
[root@localhost data]# chown mysql:mysql mysqldata -R #授權(quán)mysql用戶和mysql組
#mysql啟動(dòng)跳過授權(quán)用戶表
[root@localhost data]# cat /etc/my.cnf [mysqld] basedir=/data/mysql datadir=/data/mysqldata port=3306 socket=/tmp/mysql.sock user=mysql innodb_file_per_table=on skip_name_resolve=on symbolic-links=0 init-connect='SET NAMES utf8' character-set-server = utf8 max_connections = 5000 max_connect_errors = 100000 skip-grant-tables #添加啟動(dòng)mysql跳過用戶授權(quán)表 [mysqld_safe] log-error=/data/mysql/mysqld.log pid-file=/data/mysql/mysqld.pid、
#啟動(dòng)mysql服務(wù)：
[root@localhost data]# /etc/init.d/mysqld start Starting MySQL... [ OK ] [root@localhost data]# cd mysqldata [root@localhost mysqldata]# ls 2017-03-31_02-33-37 ecos ecoslog ibdata1 localhost.localdomain.pid stest xtrabackup_info appversion ecosantispam ecos_notice ib_logfile0 mysql test auto.cnf ecos_delivery ecoswallet ib_logfile1 performance_schema xtrabackup_binlog_pos_innodb
#如果忘記了登錄mysql的用戶密碼可以跳過用戶認(rèn)證表，當(dāng)然也可以加在my.cnf文件中：
/etc/rc.d/init.d/mysqld start --skip-grant-tables; #跳過權(quán)限表啟動(dòng)mysql不用密碼；
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼
