下面小編就為大家帶來一篇淺談mysql 針對單張表的備份與還原。小編覺得挺不錯的，現在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧
A、MySQL 備份工具xtrabackup 的安裝
1. percona 官方xtrabackup 的二進制版本；二進制版本解壓就能用了。
2. 解壓xtrabackup & 創建連接
tar?-xzvf?percona-xtrabackup-2.3.4- Linux -x86_64.tar.gz?-C?/usr/local/ ln?-s?/usr/local/percona-xtrabackup-2.3.4?/usr/local/xtrabackup
3. 設置PATH環境mysql
export?PATH=/usr/local/xtrabackup/bin/:$PATH
B、在mysql中創建一個用戶備份的用戶 & 授權
1. 創建用戶
????create?user?backuper@'localhost'?ident if ied?by?'backup123'; ????????create?user?backuper@'127.0.0.1'?identified?by?'backup123';
2. 授權
grant?reload,lock?tables,replication?client,process, super ?on?*.*?to?'backuper'@'localhost'; ????????grant?create,insert,select?on?percona_schema.xtrabackup_history?to?'backuper'@'localhost'; ????????grant?reload,lock?tables,replication?client,process,super?on?*.*?to?'backuper'@'127.0.0.1'; ????????grant?create,insert,select?on?percona_schema.xtrabackup_history?to?'backuper'@'127.0.0.1';
C、備份前的檢查，這一步的主要目地是在之后做還原操作時，驗證還原是不是有效；(生產是沒有這一步的,
1.
select?*?from?tempdb.dictmajor;??? select?*?from?dictmajor; ????????+--------------+-----------------+???????? ????????|?column_value?|?column_mean?????|???????? ????????+--------------+-----------------+???????? ????????|????????????1?|?漢語言文學??????|???????? ????????|????????????2?|?精算????????????|???????? ????????|????????????3?|?生物制藥????????|???????? ????????|????????????4?|?材料化學????????|???????? ????????|????????????5?|?商務英語????????|???????? ????????|????????????6?|?考古????????????|???????? ????????|????????????7?|?外交????????????|???????? ????????|????????????8?|?導游????????????|???????? ????????+--------------+-----------------+
D、備份tempdb.dictmajor 表
1. mysql
innobackupex?--host=127.0.0.1?--user=backuper?--password=backup123?--port=3306?--include='tempdb.dictmajor'?/tmp/tempdb
2. 備份完成后會在備份目錄(/tmp/tempdb) 下生成用當前時間命名的目錄，里面保存的就是備份文件
???tree?/tmp/tempdb/ ????????/tmp/tempdb/ ????????└──?2016-09-10_18-25-16 ????????????├──?backup-my.cnf ????????????├──?ibdata1 ????????????├──?tempdb ????????????│???├──?dictmajor.frm ????????????│???└──?dictmajor.ibd ????????????├──?xtrabackup_binlog_info ????????????├──?xtrabackup_checkpoints ????????????├──?xtrabackup_info ????????????└──?xtrabackup_logfile
???????????
E、備份完成后就可以mysqltempdb.dictmajor表了(注意這里一定要保存一份表的定義，還原時會用到)
mysql>drop?table?tempdb.dictmajor;
F、為了得到一個一致的備份集 在還原操作前還要進行一次日志的前滾和回滾
1. 前滾&回滾日志
innobackupex?--apply-log?--export?/tmp/tempdb/2016-09-10_18-25-16/
2.? 與前滾& 回滾前的對比
??tree?/tmp/tempdb/ ????????/tmp/tempdb/ ????????└──?2016-09-10_18-25-16 ????????????├──?backup-my.cnf ????????????├──?ibdata1 ????????????├──?ib_logfile0 ????????????├──?ib_logfile1 ????????????├──?tempdb ????????????│???├──?dictmajor.cfg ????????????│???├──?dictmajor.exp ????????????│???├──?dictmajor.frm ????????????│???└──?dictmajor.ibd ????????????├──?xtrabackup_binlog_info ????????????├──?xtrabackup_binlog_pos_innodb ????????????├──?xtrabackup_checkpoints ????????????├──?xtrabackup_info ????????????└──?xtrabackup_logfile
???????????
G、還原tempdb.dictmajor表
1. 創建 tempdb.dictmajor表
??create?table?dictmajor( ????????column_value?tinyint?not?null, ????????column_mean?varchar(32)?not?null, ????????constraint?pkdictmajor?primary?key?(column_value));
2. 刪除 tempdb.dictmajor的表空間文件
alter?table?tempdb.dictmajor?discard?tablespace;
3. 把備份中的表空間文件復制到tempdb.dictmajor 表空間應該在的位置
???cp?/tmp/tempdb/2016-09-10_18-25-16/tempdb/dictmajor.ibd?/usr/local/mysql/data/tempdb/ ????????cp?/tmp/tempdb/2016-09-10_18-25-16/tempdb/dictmajor.exp?/usr/local/mysql/data/tempdb/ ????????cp?/tmp/tempdb/2016-09-10_18-25-16/tempdb/dictmajor.cfg?/usr/local/mysql/data/tempdb/ ????????chown?-R?mysql:mysql?/usr/local/mysql/data/tempdb/*
???????
4. 導入表空間文件
alter?table?tempdb.dictmajor?import?tablespace;
5. 查看dictmajor表恢復情況
??????select?*?from?dictmajor; ????????+--------------+-----------------+ ????????|?column_value?|?column_mean?????| ????????+--------------+-----------------+ ????????|????????????1?|?漢語言文學??????| ????????|????????????2?|?精算????????????| ????????|????????????3?|?生物制藥????????| ????????|????????????4?|?材料化學????????| ????????|????????????5?|?商務英語????????| ????????|????????????6?|?考古????????????| ????????|????????????7?|?外交????????????| ????????|????????????8?|?導游????????????| ????????+--------------+-----------------+
—————————————————————
上一節用的是xtrabackup 對表進行備份，它的應用場景是單表的數據量大且在備份的過程中還要支持對表的寫操作；也就是說在目前的場景下mysqldump 這個簡單的
備份工具也是可以滿足要求的；
現給出mysqldump 備份的一般步驟
A：創建備份用戶
1.
??????
??create?user?dumper@'127.0.0.1'?identified?by?'dumper123'; ????????grant?select?on?*.*?to?dumper@'127.0.0.1'; ????????grant?show?view?on?*.*?to?dumper@'127.0.0.1'; ????????grant?lock?tables?on?*.*?to?dumper@'127.0.0.1'; ????????grant?trigger?on?*.*?to?dumper@'127.0.0.1';
???????
B：備份tempdb.dictmajor表
1.
mysqldump?--host=127.0.0.1?--port=3306?--user=dumper?--password=dumper123?--quick?tempdb?dictmajor?>/tmp/tempdb.dictmajor.sql
C: 刪除已經備份的表
1.
mysql>drop?table?tempdb.dictmajor;
D：還原tempdb.dictmajor表
1.
mysql?-uroot?-pxxxxx?-h127.0.0.1?-p3306?tempdb?/tempdb.dictmajor.sql
E：檢證還原的有效性
1.
select * from dictmajor;
???+--------------+-----------------+ ????????|?column_value?|?column_mean?????| ????????+--------------+-----------------+ ????????|????????????1?|?漢語言文學??????| ????????|????????????2?|?精算????????????| ????????|????????????3?|?生物制藥????????| ????????|????????????4?|?材料化學????????| ????????|????????????5?|?商務英語????????| ????????|????????????6?|?考古????????????| ????????|????????????7?|?外交????????????| ????????|????????????8?|?導游????????????| ????????+--------------+-----------------+
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
