Backup and recovery of databases

Experiment: Recover a database that has been mistakenly deleted the first step: to measure whether the deleted database has important relationship with other database 2 stop access to the database second step: View the point in time of the full backup (point in time of the Master-data binary log file backup) 1 full backup (-F Generate a binary log file per backup of a database) Mysqldump-a-F--master-data=1 (Master-slave replication recommendation plus 1)--single-transaction (ensure database operation is consistent) >/app/all-' Date +% F%t '. SQL2 insertdrop Database hellodb;3 discovery problem Mysql>flush tables with read Lock;iptables prohibit other user Access 4 view the location of the full backup 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 generate a new log file 6 backup log file 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 Delete drop database hellodb; This command 8 Mysql>set sql_log_bin=0; Subsequent restores do not log binary files 9 mysql>unlock tables;10 mysql> source/app/all-2018-02-2309\:48\:53.sql one mysql> source/app/ bin.sql12 mysql>set sql_log_bin=1;13 iptables restore user access Flush logs experiment: LVM-based backup restore preparation phase Fdisk/dev/sdapartprobepvcreate/ Dev/sda6vgcreate Vg0/dev/sda6lvcreate-n Mysqldata-l 2G vg0lvcreate-n binlogs-l 3G vg0 The remaining 5G is used to place the snapshot 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/Change permissions vim/etc/my.cnf[mysqld] Datadir=/data/mysqldatasocket=/var/lib/mysql/mysql.socklog-bin=/data/binlogs/mysql-binsystemctl Start MARIADB1 Separate the data and binlog into the respective logical volumes Binlogs Mysqldatavim/etc/my.cnfdatadir=/data/mysqldatasocket=/var/lib/mysql/mysql.socklog-bin =/data/binlogs/mysql-bin2 request lock All tables mysql> flush TABLES with READ lock;3 record binary log files and event locations mysql> Flush logs;mysql> Show Master status;mysql-e ' show Master Logs ' >/path/to/somefile (/app/pos.log) 4 Create snapshot (note the binary log file location for snapshot creation) Lvcreate-l # ( 2G)-s-p r-n NAME (mysqldata-snapshot)/dev/vg_name/lv_name (/dev/vg0/mysqldata) Delete hellodb. Student Table 5 release lock mysql> UNLOCK Tables;6 mount the snapshot volume, perform a data backup Mount-o nouuid (without checking the UUID), Norecovery/dev/vg0/mysqldata-snapShot/mnt/snapcp-a/mnt/snap/*/backups/(typically backed up to a remote host) 7 after the backup is complete, delete the snapshot volume umount/mnt/snaplvremove/dev/vg0/ MYSQLDATA-SNAPSHOT8 Destroy database Systemctl stop mariadbrm-rf/data/mysqldata/*9 restore cp-a/backups/*/data/mysqldata/system start MARIADB10 binlog Restore cp/data/binlogs/mysql-bin.00000{4,5}/app/-amysql>flush tables with read lock; (Stop customer changes to database, restore to latest state of database) 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 tool does a full backup very slowly, So a backup of a large database is not appropriate, and it is through select query database data and then exported to a text file Xtrabackup: Percona provides support for InnoDB to do hot spare (physical backup) tools, support full backup, incremental backup of a thread replication redo Log is also the transaction log/data/mysqldata/ib_logfile0 a thread to copy IDB database data yum install percona-xtrabackup-24 Resolve package Dependencies The Xtrabackup tool replicates the InnoDB engine's disk-oriented data blocks, whereas for MyISAM engines, a single file for the disk is copied Innobackupex is Xtrabackup-based encapsulation--apply-log: Used to resolve half of the transactions at a certain point in time, the transaction must be rolled back, the consistency LSN of the transaction is saved (log sequence number), the database log file is on the disk: databaseEach data block in the file has a numbered experiment: full backup and restore 1 in the original host Innobackupex--user=root/backupsscp-r/backups/2018-02-23_11-55-57/192.168.27.17:/app /2 in the target host Vim/etc/my.cnfinnodb_file_per_tablelog-bininnobackupex--apply-log/app/2018-02-23_11-55-57/to organize the database data, Rollback of Systemctl stop Mariadbrm-rf/var/lib/mysql/*innobackupex--copy-back/app/2018-02-23_11-55-57/chown-r for incomplete transactions Mysql.mysql/var/lib/mysql/systemctl start MARIADB Experiment: Full, incremental backup and restore 1 on the original host innobackupex/backupsmkdir/backups/inc{1,2} Insert students (Name,age,gender) VALUES (' a ', +, ' F '); Innobackupex--incremental/backups/inc1--incremental-basedir= /backups/2018-02-23_14-21-42insert students (Name,age,gender) VALUES (' B ', +, ' M '); Innobackupex--incremental/ BACKUPS/INC2--incremental-basedir=/backups/inc1/2018-02-23_14-26-17scp-r/BACKUPS/192.168.27.17:/APP/2 The target host does not start mariadbrm-rf/var/lib/mysql/* defragment the full backup Innobackupex--apply-log--redo-only/app/backups/2018-02-23_14-21-42/ Merge the first incremental backup into a full backup Innobackupex--apply-log--redo-only/app/backups/2018-02-23_14-21-42/--incremental-DIR=/APP/BACKUPS/INC1/2018-02-23_14-26-17 Merge the second incremental backup into a full backup 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/ Ensure that the database data directory is empty 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 Default support innodb_file_per_table experiment: Single table export and import 1 single table backup (database version must be above version 5.6) Back up table data Innobackupex--include= ' hellodb.students '/backups backup table structure 2, MYSQL-E ' Show create table Hellodb.students ' > student.sqlstudent.sql file pick the following content CREATE TABLE ' students ' (' stuid ' int () unsigned not NULL auto_inc Rement, ' Name ' varchar (not null), ' Age ' tinyint (3) unsigned not NULL, ' Gender ' enum (' F ', ' M ') is not null, ' ClassID ' Tinyin T (3) unsigned default null, ' Teacherid ' int (ten) unsigned default NULL, PRIMARY KEY (' Stuid ')) Engine=innodb auto_increment =26 DEFAULT charset=utf83 Delete table mysql-e ' drop table hellodb.students ' organizing database data and exporting will change the database file directory 4 Innobackupex--apply-log-- ExpORT/BACKUPS/2018-02-23_15-03-23/Restore: CREATE TABLE structure 5 mysql> CREATE table ' students ' (' stuid ' int () unsigned not NULL auto_incr Ement, ' Name ' varchar (not null), ' Age ' tinyint (3) unsigned not NULL, ' Gender ' enum (' F ', ' M ') is not null, ' ClassID ' tinyint (3) Unsigned default null, ' Teacherid ' int (ten) unsigned default NULL, PRIMARY KEY (' Stuid ')) Engine=innodb auto_increment= The DEFAULT Charset=utf8 table space: Storage database table Data 5 ALTER TABLE students discard tablespace; Delete students.ibd file 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/associating the table data of a database with the table structure 8 Mysql>alter Table Students Import Tablespace;mysql>select * from students;

Backup and recovery of databases