MySQL Advanced (one) MySQL backup

Purpose of backup:     for disaster recovery: misoperation, hardware failure, software failure, natural disaster, hacker attack Note:     1, can tolerate the loss of how much data      2, time spent recovering data     3, time required for backup     4, business impact      5, Backup server load backup type      full backup: Back up data      partial backups of the entire database: Back up the specified part of the data       Incremental backup: Back up data that was added after the last backup      differential backup: Back up data that changed since the final full backup      Hot backup: Read and write operations can be      warm backup: Read-only      Cold backup: Non-writable, unreadable      Logical backup: Export data from a database, independent of storage engine      physical backup: Direct copy of data file backup requires:     data       binary log, transaction log      configuration file backup design:     data: Full + incremental, full + partial       Backup: Logical or physical backup tools:     mysqldump:mysql backup tools, logical backups, full backups, partial backups, Win Bei      LVM2 snapshot: Almost hot standby, need to use tools such as Cp,tar, LVM2 only responsible for generating snapshots     xtrabackup: Support hot Backup to InnoDB (physical backup      TAR,CP: Cold Backup  &nbSp;  mysqldump tool Use: logical Backup tool, can be used when the data is less than 1G, the data is greater than 1G is not recommended to use mysqldump [options] database [ Tables] #不会自动创建数据库mysqldump  [options] --databases db1 [db2&nbsp ...] #备份指定数据库, the database mysqldump [options] --all-databases  #备份所有数据库 is created automatically, and the database is created automatically      Common options:-u# user-p# password--flush-logs  #执行二进制日志文件滚动--single-transaction# hot backup of InnoDB storage engine (only InnoDB support)--master-data=2     #在数据文件中, record backup the location of the binary log files,     and annotations are used primarily for reference and are used for point-in-time restore          --lock-all-tables     #在执行备份时锁定所有库的所有表 (second choice, must)--lock-tables      #对单个数据库在备份时锁定所有表 (second choice, must)          example: mysqldump  -uroot -p123.com --databases --lock-tables --flush-logs              --master-data=2 test > /tmp/mysql.back . ' date  ' +%f__%r "'     LVM2 snapshot implementation data almost hot-standby          implementation conditions: MySQL data is stored in an LVM logical volume and requires sufficient space for a snapshot backup strategy:         1, creating global locks         mysql>  FLUSH TABLES WITH READ LOCK;    2, logging binary log files and event locations          mysql -uroot -p123.com -e  ' flush logs; ' #进行日志滚动         mysql -uroot -p123.com -e  ' SHOW  MASTER STATUS; ' >/tmp/test. ' date +%f '            #记录二进制日志位置      3, creating an LVM snapshot         lvcreate -l 1gb -s  -n test_snap /dev/vg_test/lv_test                 -L 1G  #快照大小, general and data files almost                  -s     #表示创建快照           -n test_snap  #快照名/dev/vg_test/lv_test  #要创建快照的逻辑卷     4, releasing global locks         mysql> UNLOCK TABLES;         5, Mount snapshot volume         mount /dev/vg_test/test_ Snap /mnt        6, backing up data and deleting snapshot volumes          cp -a /mnt/date/mysql /tmp# Copying data files          umount /mnt# unmount the snapshot volume         lvremove /dev/vg_test/test_snap   #删除快照         7, make a backup plan, back up the binaries every once in a while           backup MySQL binaries at intervals for future point-in-time restore data               Example: CP -p /data/mysql-bin.000007 /tmp/mysql_7.sql# Backup binary log 8, restore               restore simply copy the data file to the MySQL data directory and use the binary log to make a point-in-time restore example of the data: Cp -a /tmp/mysql  /data/mysql# Restoring Data     mysql> source /tmp/mysql_7.sql;# Implementing a point-in-time restore xtrabackup implementation of MySQL hot:     Note: Xtrabackup can only implement warm and incremental backups for InnoDB      https://www.percona.com/downloads/xtrabackup/latest/#     Installation: 1, configuration good Epel source 2, yum -y  localinstall percona-xtrabackup-2.3.2-1.el6.x86_64.rpm     #安装      Backup utility: Xtrabackup contains two main tools, Xtrabackup and Innobackupexinnobackupex: A Perl script that xtrabackup re-encapsulates, capable of innodb and MyISAM backups,                  MyISAM cannot implement hot standby and incremental backup xtrabackup:xtrabackup can only back up tables of InnoDB and xtradb two engines,                  cannot back up MyisaM engine table Innobackupex Basic use      common parameters:         --help# View Help information--backup# a backup operation, which can be saved by default to perform a backup operation--apply-log# the required collation--defaults-file# the specified profile path, which can be saved and automatically read to the database--redo-only   #在做整理操作时, uncommitted data does not roll back, the option to do an incremental backup restore must be added--copy-back# perform the restore operation--incremental# perform an incremental backup--user=name# the specified user name, default to Root--host =name# specifies the hostname, the default is host--port= #指定端口, the default is 3306--password=name# to specify the password, the default is empty--databases= ' db1 bd2 '   #备份指定的数据库,-- Defaults-file= #指定配置文件, save--no-timestamp= #自定义备份目录名incremental-basedir= #基于那一次备份做增量备份      Use the instance:innodb_file_per_table=1     #在mysql配置文件中添加 to indicate that each table uses a separate tablespace file datadir=/usr/local/mysql/ data# is added in the MySQL configuration file to indicate the location of the specified data file mkdir /backups# Create backup file storage directory      full library full backup: innobackupex -- user=root --password=123.com /backups//backups/#备份文件存放目录注意: completed ok! is successful at the end or error       Restore a full backup: innobackupex --apply-log /backups/2017-02-20_04-40-44/# Defragment the backup file Innobackupex --copy-back /backups/2017-02-20_04-40-44/#还原数据chown  mysql:mysql /usr/local/mysql/* #修改权限      Incremental backup:innobackupex --user=root --password=123.com  --incremental /backups/              --incremental-basedir=/backups/2017-02-20_ 04-40-44/                / backups/#增量备份存储位置--incremental-basedir# Specifies which backup file to base the incremental backup on, typically a previous incremental backup      incremental backup restore:         innobackupex --apply-log  --redo-only /backups/ 2017-02-20_05-01-49/#整理完全备份         innobackupex --apply-log   --redo-only /backups/2017-02-20_05-01-49                --incremental-dir=/backups/2017-02-20_05-04-34# finishing the first incremental backup          innobackupex --copy-back /backups/2017-02-20_05-01-49/#还原数据      Partial backup:             innobackupex --databases= "Testdb mysql"  /backups/ --user=root --password=123.com        # Back up the TestDB and MySQL databases, backup the MySQL database      restore a partial backup, regardless of the backup database:         innobackupex --apply-log /backups/2017-02-20_05-24-07/#准备          innobackupex --copy-back /backups/2017-02-20_05-24-07/#还原          chown mysql:mysql /usr/local/mysql/* #修改权限

This article is from the "Automated Operations" blog, please be sure to keep this source http://hongchen99.blog.51cto.com/12534281/1934238

MySQL Advanced (one) MySQL backup