Backup and recovery of databases

Source: Internet
Author: User
Tags uuid iptables percona

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/ /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/ 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.