資料庫的備份和恢複

標籤：資料庫   備份   恢複   

實驗：恢複誤刪除的資料庫到最新狀態第一步：衡量誤刪除資料庫與其它資料庫2是否有重要關係 停止資料庫的訪問第二步：查看全備份的時間點（master-data 二進位記錄檔備份的時間點）1 完全備份 （-F 每備份一個資料庫就產生一個二進位記錄檔）mysqldump -A -F --master-data=1(主從複製建議加1) --single-transaction（確保資料庫操作一致） > /app/all-`date +%F%T`.sql2 對資料庫修改insertdrop database hellodb;3 發現問題mysql>flush tables with read lock;iptables 禁止其它使用者訪問4 查看完全備份的位置less /app/all-2018-02-2309\:48\:53.sql CHANGE MASTER TO MASTER_LOG_FILE=‘mariadb-bin.000008‘, MASTER_LOG_POS=245;5 flush logs 產生新記錄檔6 備份記錄檔cp /var/lib/mysql/mariadb-bin.000008 /app/mysqlbinlog --start-position=245 mariadb-bin.000008 > /app/bin.sqlmysqlbinlog mariadb-bin.000009 >> /app/bin.sql7 vim /app/bin.sql刪除drop database hellodb; 此命令8 mysql>set sql_log_bin=0; 後續還原不記錄二進位檔案9 mysql>unlock tables;10 mysql> source /app/all-2018-02-2309\:48\:53.sql 11 mysql> source /app/bin.sql12 mysql>set sql_log_bin=1;13 iptables 恢複使用者訪問flush logs實驗：基於LVM的備份還原準備階段fdisk /dev/sdapartprobepvcreate /dev/sda6vgcreate vg0 /dev/sda6lvcreate -n mysqldata -L 2G vg0lvcreate -n binlogs -L 3G vg0剩餘5G用來放快照mkfs.xfs /dev/vg0/mysqldatamkfs.xfs /dev/vg0/binlogsmkdir /data{mysqldata,binlogs} -pvvim /etc/fstabUUID=4a9ba44e-4f5b-40f4-95e5-f48deda9d800 /data/mysqldata xfs defaults 0 0 UUID=3f2eaf34-9c93-4540-b731-46b324e10bc2 /data/binlogs xfs defaults 0 0chown mysql.mysql /data/ 更改許可權vim /etc/my.cnf[mysqld]datadir=/data/mysqldatasocket=/var/lib/mysql/mysql.socklog-bin=/data/binlogs/mysql-binsystemctl start mariadb1 分離資料和binlog到各自的邏輯卷 binlogs mysqldatavim /etc/my.cnfdatadir=/data/mysqldatasocket=/var/lib/mysql/mysql.socklog-bin=/data/binlogs/mysql-bin2 請求鎖定所有表mysql> FLUSH TABLES WITH READ LOCK;3記錄二進位記錄檔及事件位置mysql> FLUSH LOGS;mysql> SHOW MASTER STATUS;mysql -e ‘SHOW MASTER logs‘ > /PATH/TO/SOMEFILE(/app/pos.log)4 建立快照(注意建立快照的二進位記錄檔位置)lvcreate -L #(2G) -s -p r -n NAME(mysqldata-snapshot) /DEV/VG_NAME/LV_NAME(/dev/vg0/mysqldata)刪除hellodb.學生表5 釋放鎖mysql> UNLOCK TABLES;6掛載快照卷，執行資料備份mount -o nouuid(不檢查UUID),norecovery /dev/vg0/mysqldata-snapshot /mnt/snapcp -a /mnt/snap/* /backups/ (通常備份到遠程主機)7備份完成後，刪除快照卷umount /mnt/snaplvremove /dev/vg0/mysqldata-snapshot8 破壞資料庫systemctl stop mariadbrm -rf /data/mysqldata/*9 還原cp -a /backups/* /data/mysqldata/system start mariadb10 binlog還原cp /data/binlogs/mysql-bin.00000{4,5} /app/ -amysql>flush tables with read lock; (停止客戶對資料庫更改，還原到資料庫的最新狀態)mysqlbinlog --start-position=245 mysql-bin.000004 > bin.sqlmysqlbinlog mysql-bin.000005 >> bin.sql>set sql_log_bin=0;MariaDB [(none)]> unlock tables;MariaDB [(none)]>source /app/bin.sqlMariaDB [(none)]>>set sql_log_bin=1;mysqldump 工具做完全備份速度很慢，所以對大型資料庫做備份就不適合，而且它是通過select查詢資料庫資料然後匯出到文字檔中xtrabackup：由Percona提供支援對InnoDB做熱備(物理備份)的工具，支援完全備份、增量備份 一個線程複製 redo log 也即交易記錄 /data/mysqldata/ib_logfile0一個線程複製 idb 資料庫資料yum install percona-xtrabackup-24 解決裝包的依賴性xtrabackup工具對於innodb引擎面向磁碟檔案的資料區塊進行複製，而對於MYISAM引擎是磁碟單個檔案進行複製innobackupex 是基於xtrabackup的封裝--apply-log: 用於解決在某個時間點存在一半的事務，這時需要復原事務，儲存事務的一致性lsn(log sequence number) 資料庫記錄檔在磁碟的序號：資料庫檔案中每個資料區塊都有編號實驗：完全備份及還原1 在原主機 innobackupex --user=root /backupsscp -r /backups/2018-02-23_11-55-57/ 192.168.27.17:/app/ 2 在目標主機vim /etc/my.cnfinnodb_file_per_tablelog-bininnobackupex --apply-log /app/2018-02-23_11-55-57/ 整理資料庫資料，對於不完整的事務需復原systemctl stop mariadbrm -rf /var/lib/mysql/*innobackupex --copy-back /app/2018-02-23_11-55-57/chown -R mysql.mysql /var/lib/mysql/systemctl start mariadb實驗：完全，增量備份及還原1 在原主機innobackupex /backupsmkdir /backups/inc{1,2} insert students (name,age,gender)values(‘a‘,29,‘F‘);innobackupex --incremental /backups/inc1 --incremental-basedir=/backups/2018-02-23_14-21-42insert students (name,age,gender)values(‘b‘,20,‘M‘);innobackupex --incremental /backups/inc2 --incremental-basedir=/backups/inc1/2018-02-23_14-26-17scp -r /backups/ 192.168.27.17:/app/2 在目標主機不啟動mariadbrm -rf /var/lib/mysql/*整理完全備份innobackupex --apply-log --redo-only /app/backups/2018-02-23_14-21-42/將第一次增量備份合并到完全備份中innobackupex --apply-log --redo-only /app/backups/2018-02-23_14-21-42/ --incremental-dir=/app/backups/inc1/2018-02-23_14-26-17將第二次增量備份合并到完全備份中innobackupex --apply-log --redo-only /app/backups/2018-02-23_14-21-42/ --incremental-dir=/app/backups/inc2/2018-02-23_14-28-29/ls /var/lib/mysql/ 保證資料庫資料目錄為空白innobackupex --copy-back /app/backups/2018-02-23_14-21-42/chown -R mysql.mysql /var/lib/mysql/systemctl start mariadbMariaDB-server.x86_64_10.2.13 預設支援innodb_file_per_table實驗：單表匯出和匯入1 單表備份 （資料庫版本必須在5.6版本以上）備份表資料innobackupex --include=‘hellodb.students‘ /backups備份表結構2 、mysql -e ‘show create table hellodb.students‘ > student.sqlstudent.sql檔案挑選下面內容 CREATE TABLE `students` ( `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum(‘F‘,‘M‘) NOT NULL, `ClassID` tinyint(3) unsigned DEFAULT NULL, `TeacherID` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`StuID`)) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf83 刪除表mysql -e ‘drop table hellodb.students‘整理資料庫資料並匯出會使資料庫檔案目錄發生變化4 innobackupex --apply-log --export /backups/2018-02-23_15-03-23/還原：建立表結構5 mysql> CREATE TABLE `students` ( `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum(‘F‘,‘M‘) NOT NULL, `ClassID` tinyint(3) unsigned DEFAULT NULL, `TeacherID` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`StuID`)) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8資料表空間：存放資料庫表資料 5 alter table students discard tablespace; 刪除students.ibd檔案6 cp /backups/2018-02-23_15-03-23/hellodb/students.{cfg,exp,ibd} /var/lib/mysql/hellodb/7 chown -R mysql.mysql /var/lib/mysql/hellodb/將資料庫的表資料與表結構關聯起來8 mysql>alter table students import tablespace;mysql>select * from students;

資料庫的備份和恢複