《MYSQL教程淺談mysql 針對單張表的備份與還原》要點：
本文介紹了MYSQL教程淺談mysql 針對單張表的備份與還原，希望對您有用。如果有疑問，可以聯系我們。
MYSQL入門A、MySQL 備份工具xtrabackup 的安裝
MYSQL入門1. percona 官方xtrabackup 的二進制版本；二進制版本解壓就能用了.
MYSQL入門2. 解壓xtrabackup & 創建連接
MYSQL入門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
MYSQL入門3. 設置PATH環境變量
export PATH=/usr/local/xtrabackup/bin/:$PATH????
MYSQL入門B、在mysql數據庫中創建一個用戶備份的用戶 & 授權
MYSQL入門1. 創建用戶
MYSQL入門??????? create user backuper@'localhost' identified by 'backup123';
??????? create user backuper@'127.0.0.1' identified by 'backup123';
MYSQL入門2. 授權
MYSQL入門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';
MYSQL入門C、備份前的檢查,這一步的主要目地是在之后做還原操作時,驗證還原是不是有效；(生產是沒有這一步的,
MYSQL入門1. select * from tempdb.dict__major;
?? select * from dict__major;
MYSQL入門??????? +--------------+-----------------+
??????? | column_value | column_mean???? |
??????? +--------------+-----------------+
??????? |??????????? 1 | 漢語言文學????? |
??????? |??????????? 2 | 精算??????????? |
??????? |??????????? 3 | 生物制藥??????? |
??????? |??????????? 4 | 材料化學??????? |
??????? |??????????? 5 | 商務英語??????? |
??????? |??????????? 6 | 考古??????????? |
??????? |??????????? 7 | 外交??????????? |
??????? |??????????? 8 | 導游??????????? |
??????? +--------------+-----------------+
MYSQL入門D、備份tempdb.dict__major 表
MYSQL入門1. 備份命令
MYSQL入門innobackupex --host=127.0.0.1 --user=backuper --password=backup123 --port=3306 --include='tempdb.dict__major' /tmp/tempdb???
MYSQL入門2. 備份完成后會在備份目錄(/tmp/tempdb) 下生成用當前時間命名的目錄,里面保留的就是備份文件
MYSQL入門??????? tree /tmp/tempdb/
??????? /tmp/tempdb/
??????? └── 2016-09-10_18-25-16
??????????? ├── backup-my.cnf
??????????? ├── ibdata1
??????????? ├── tempdb
??????????? │?? ├── dict__major.frm
??????????? │?? └── dict__major.ibd
??????????? ├── xtrabackup_binlog_info
??????????? ├── xtrabackup_checkpoints
??????????? ├── xtrabackup_info
??????????? └── xtrabackup_logfile
???????????
MYSQL入門E、備份完成后就可以刪除tempdb.dict__major表了(注意這里一定要保留一份表的定義,還原時會用到)
MYSQL入門mysql>drop table tempdb.dict__major;
MYSQL入門F、為了得到一個一致的備份集 在還原操作前還要進行一次日志的前滾和回滾
MYSQL入門1. 前滾&回滾日志
MYSQL入門innobackupex --apply-log --export /tmp/tempdb/2016-09-10_18-25-16/?
MYSQL入門2.? 與前滾& 回滾前的對比
MYSQL入門??????? tree /tmp/tempdb/
??????? /tmp/tempdb/
??????? └── 2016-09-10_18-25-16
??????????? ├── backup-my.cnf
??????????? ├── ibdata1
??????????? ├── ib_logfile0
??????????? ├── ib_logfile1
??????????? ├── tempdb
??????????? │?? ├── dict__major.cfg
??????????? │?? ├── dict__major.exp
??????????? │?? ├── dict__major.frm
??????????? │?? └── dict__major.ibd
??????????? ├── xtrabackup_binlog_info
??????????? ├── xtrabackup_binlog_pos_innodb
??????????? ├── xtrabackup_checkpoints
??????????? ├── xtrabackup_info
??????????? └── xtrabackup_logfile
???????????
MYSQL入門G、還原tempdb.dict__major表
MYSQL入門1. 創建 tempdb.dict__major表
MYSQL入門??????? create table dict__major(
??????? column_value tinyint not null,
??????? column_mean varchar(32) not null,
??????? constraint pk__dict__major primary key (column_value));
MYSQL入門2. 刪除 tempdb.dict__major的表空間文件
MYSQL入門alter table tempdb.dict__major discard tablespace;
MYSQL入門3. 把備份中的表空間文件復制到tempdb.dict__major 表空間應該在的位置
MYSQL入門??????? cp /tmp/tempdb/2016-09-10_18-25-16/tempdb/dict__major.ibd /usr/local/mysql/data/tempdb/
??????? cp /tmp/tempdb/2016-09-10_18-25-16/tempdb/dict__major.exp /usr/local/mysql/data/tempdb/
??????? cp /tmp/tempdb/2016-09-10_18-25-16/tempdb/dict__major.cfg /usr/local/mysql/data/tempdb/
??????? chown -R mysql:mysql /usr/local/mysql/data/tempdb/*
???????
MYSQL入門4. 導入表空間文件
MYSQL入門alter table tempdb.dict__major import tablespace;????
MYSQL入門5. 查看dict__major表恢復情況
MYSQL入門??????? select * from dict__major;
??????? +--------------+-----------------+
??????? | column_value | column_mean???? |
??????? +--------------+-----------------+
??????? |??????????? 1 | 漢語言文學????? |
??????? |??????????? 2 | 精算??????????? |
??????? |??????????? 3 | 生物制藥??????? |
??????? |??????????? 4 | 材料化學??????? |
??????? |??????????? 5 | 商務英語??????? |
??????? |??????????? 6 | 考古??????????? |
??????? |??????????? 7 | 外交??????????? |
??????? |??????????? 8 | 導游??????????? |
??????? +--------------+-----------------+
MYSQL入門---------------------------------------------------------------
MYSQL入門上一節用的是xtrabackup 對表進行備份,它的應用場景是單表的數據量大且在備份的過程中還要支持對表的寫操作；也便是說在目前的場景下mysqldump 這個簡單的
MYSQL入門備份工具也是可以滿足要求的；
MYSQL入門
現給出mysqldump 備份的一般步驟
MYSQL入門A：創建備份用戶
MYSQL入門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';
???????
MYSQL入門B：備份tempdb.dict__major表
MYSQL入門1.
mysqldump --host=127.0.0.1 --port=3306 --user=dumper --password=dumper123 --quick tempdb dict__major >/tmp/tempdb.dict__major.sql
???
MYSQL入門C: 刪除已經備份的表
MYSQL入門1.
mysql>drop table tempdb.dict__major;
MYSQL入門D：還原tempdb.dict__major表
MYSQL入門1.
mysql -uroot -pxxxxx -h127.0.0.1 -p3306 tempdb </tmp/tempdb.dict__major.sql?????
MYSQL入門E：檢證還原的有效性
MYSQL入門1.
select * from dict__major;
MYSQL入門??????? +--------------+-----------------+
??????? | column_value | column_mean???? |
??????? +--------------+-----------------+
??????? |??????????? 1 | 漢語言文學????? |
??????? |??????????? 2 | 精算??????????? |
??????? |??????????? 3 | 生物制藥??????? |
??????? |??????????? 4 | 材料化學??????? |
??????? |??????????? 5 | 商務英語??????? |
??????? |??????????? 6 | 考古??????????? |
??????? |??????????? 7 | 外交??????????? |
??????? |??????????? 8 | 導游??????????? |
??????? +--------------+-----------------+
MYSQL入門以上這篇淺談mysql 針對單張表的備份與還原便是小編分享給大家的全部內容了,希望能給大家一個參考,也希望大家多多支持維易PHP.
《MYSQL教程淺談mysql 針對單張表的備份與還原》是否對您有啟發，歡迎查看更多與《MYSQL教程淺談mysql 針對單張表的備份與還原》相關教程，學精學透。維易PHP學院為您提供精彩教程。
