MySQL database backup and recovery

How the database is backed up

热备份：不影响业务的正常读写温备份：对表加锁，只允许对数据库进行读操作冷备份：业务暂停物理备份：备份mysql相关的数据文件逻辑备份：将数据从mysql中导出来，一般是一个sql文件（无索引数据）全备份：完全备份增量备份：针对上一次备份进行叠加差异备份：针对上一次完全备份，选择差异进行备份

Using mysqldump Backup

Logical backup, single process (slow), lock table (usually in the backup from the library);
You can use the Mysqldumper tool for multi-process backups.

Backup instance:

# mysqldump --flush-logs cacti plugin_thold_contacts plugin_thold_log plugin_thold_template_contact plugin_thold_threshold_contact thold_data thold_template >cacti_thold.sql           //cacti库多表备份# mysqldump -h rds.amazonaws.com -u root -p production_sts pubkey_change_logs --where="created<‘2016-05-04‘" > pubkey_change_logs_0312to0503.sql        //指定备份部分内容（归档时使用）# mysqldump -u root -p -d --databases treasure treasure_admin treasure_logs treasure_sts >duobao.sql      //多库备份，及只备份表结构，不备份数据-d# mysqldump  -u root -pxxxx -B ehr --single-transaction --master-data=2 --routines |/usr/bin/bzip2 > /data1/mysql_backup/ehr-db01/ehr_inc.sql.bz        //线上使用的大库备份

Recovery method:

mysql命令直接导入、source命令、load命令

Use Xtrabackup for incremental backups

Hot backup, physical backup, installation and introduction: https://www.percona.com/doc/percona-server/LATEST/index.html
After installation contains Xtrabackup and innobackup two tools, used here with the latter;
Need to scan MySQL related data files, configuration files, not support remote backup (such as: Cloud DB instance).

Backup instance:

# innobackupex --user=root -pxxxxx  /data/backup/innodbbackup/     //最简单的完全备份# innobackupex --user=root -pxxxxx --incremental --incremental_basedir=/data/backup/innodbbackup/2018-02-07_17-08-31/ /data/backup/innodbbackup/      //增量备份，需要指定上次的备份位置（lsn信息）# innobackupex -u root -pxxxxx --no-timestamp --extra-lsndir=/data1/innobackup/0207/lsn_17/ --stream=tar /data1/innobackup/ |gzip  > /data1/innobackup/0207-1737.tgz    //打包压缩# innobackupex --user=root -pxxxxx  /data/backup/innodbbackup/ --stream=tar |gzip|sshpass -p "sshpass" ssh -p 18122 [email protected] "cat - > /data1/xtrabackup/zabbix/0207_1737.tgz"      //备份并发送到远程服务器

Data recovery:

1. Server Preparation: Install the same version of MySQL, after initialization, close the MySQL service, empty the MySQL data directory
2. Get backup: Also can be saved directly in the time of backup
3. Restore command:

# tar  -i  -xf   0207_1737.tgz        //如果打包了，先解包日志文件恢复（完全备份）：完成事务、同步数据等操作，使数据文件达到一致性# innobackupex --defaults-file=/usr/local/mysql/my.cnf  --apply-log --redo-only  --u root  -pxxxx  /tmp/mysql/fullbackup增量日志文件恢复（如果是多个增量，按顺序依次恢复），需要指定lsn# innobackupex --defaults-file=/usr/local/mysql/my.cnf  --apply-log --redo-only -u root  -pxxxxx  /tmp/mysql/incre-1337 --incremental-dir=/tmp/mysql/incre/1337# innobackupex --defaults-file=/usr/local/mysql/my.cnf  --apply-log -u root -pxxxx  /tmp/mysql/2016-04-26_13-48-39      //最后一次将增量和完全备份合并，将未提交的事物回滚，但不需要--redo-only参数。数据恢复：要保证原来的数据目录为空（如果有其他库，先移动在其他目录，后期移回来即可）# innobackupex --defaults-file=/usr/local/mysql/my.cnf  --copy-back -u root -pxxxxx  /tmp/mysql/2016-04-26_13-48-39

4. Finally, re-authorize the Mysql_data directory and start the MySQL service.

Using incremental backup scripts on the wire

#!/bin/bashhour= ' Date +%h ' day= ' date +%f ' now= ' date +%f_%h ' passwd= ' mysqlpass ' function backup () {case $ infull)/usr/bin /innobackupex-u root-p${passwd}--no-timestamp--extra-lsndir=/data1/innobackup/${day}/lsn_${now}/--stream=tar/ data1/innobackup/${day}/|gzip >/data1/innobackup/${day}/${now}.tgz; Incre) If [-d/data1/innobackup/${day}/lsn_${last}/xtrabackup_checkpoints];then/usr/bin/innobackupex-u root -P${PASSWD}--no-timestamp--incremental--incremental_basedir=/data1/innobackup/${day}/lsn_${last}/-- extra-lsndir=/data1/innobackup/${day}/lsn_${now}/--stream=tar/data1/innobackup/${day} |gzip >/data1/ Innobackup/${day}/${now}.tgz else echo "Incremental_basedir not found,do a full backup"/usr/bin/innoback Upex-u root-p${passwd}--no-timestamp--extra-lsndir=/data1/innobackup/${day}/lsn_${now}/--stream=tar/data1/ innobackup/${day}/|gzip >/data1/innobackup/${day}/${now}.tgz fi; *) echo "Nothing Todo";; esac}case $hour In01) last= ' date +%f-d-yesterday ' find/data1/innobackup/-name ' ${last}* '-type d-exec        Rm-r "{}" \; Backup full;; last= ' Date +%f_%h-d -9hours ' backup incre;; 14|18|22) last= ' date +%f_%h-d -4hours ' backup incre; *) echo "Not backup Time" Esac

MySQL database backup and recovery