2023年7月30日发(作者：)

mysql数据库的完整备份、差异备份、增量备份完整备份：（t为数据库名）

Mysql -uroot -pPassword t -e "FLUSH TABLES"; // 关闭所有打开的表， 把内存中的缓存写⼊磁盘

Mysql -uroot -pPassword t -e "FLUSH TABLES WITH READ LOCK"; // 关闭所有打开的表， 加只读锁， 防⽌新的写⼊操作

MysqlAdmin -uroot -pPassword flush-logs

// ⽣成新的⼆进制⽇志⽂件和下⾯的备份时间保持同步， 以后对数据库的操作都会记录这个⽂件中

mysqldump -uroot -pPassword t >

// 完整备份

差异备份：（备份前需要打开, 取消[wampmysqld]中log-bin=mysql-bin的#号）

mysqladmin -u root -p flush-logs // ⽣成新的⼆进制⽇志⽂件, 执⾏后会发现新产⽣了类似mysql-bin.000001这样的⼆进制备份⽂件

恢复完整备份

mysql -uroot -pPassword t <

恢复差异备份（要按顺序恢复）

mysqlbinlog d:wampbinmysqlmysql5.6.17datamysql-bin.000007 | mysql -uroot -pPassword

增量备份：ump增量备份配置执⾏增量备份的前提条件是MySQL打开binlog⽇志功能，在中加⼊log-bin=/opt/Data/MySQL-bin“log-bin=”后的字符串为⽇志记载⽬录，⼀般建议放在不同于MySQL数据⽬录的磁盘上。123456789-----------------------------------------------------------------------------------mysqldump > 导出数据mysql < 导⼊数据 （或者使⽤ source 命令导⼊数据，导⼊前要先切换到对应库下）

注意⼀个细节：若是mysqldump导出⼀个库的数据，导出⽂件为，然后mysql导⼊这个数据到新的空库下。如果新库名和⽼库名不⼀致，那么需要将⽂件⾥的⽼库名改为新库名，这样才能顺利使⽤mysql命令导⼊数据（如果使⽤ source 命令导⼊就不需要修改⽂件了）。-----------------------------------------------------------------------------------ump增量备份假定星期⽇下午1点执⾏全量备份，适⽤于MyISAM存储引擎。[root@test-huanqiu ~]# MySQLdump --lock-all-tables --flush-logs --master-data=2 -u root -p test > backup_sunday_1_对于InnoDB将--lock-all-tables替换为--single-transaction--flush-logs为结束当前⽇志，⽣成新⽇志⽂件；--master-data=2 选项将会在输出SQL中记录下完全备份后新⽇志⽂件的名称，⽤于⽇后恢复时参考，例如输出的备份SQL⽂件中含有：CHANGE MASTER TO MASTER_LOG_FILE=’MySQL-bin.000002′, MASTER_LOG_POS=106;ump增量备份其他说明：如果MySQLdump加上–delete-master-logs 则清除以前的⽇志，以释放空间。但是如果服务器配置为镜像的复制主服务器，⽤MySQLdump–delete-master-logs删掉MySQL⼆进制⽇志很危险，因为从服务器可能还没有完全处理该⼆进制⽇志的内容。在这种情况下，使⽤ PURGEMASTER LOGS更为安全。每⽇定时使⽤ MySQLadmin flush-logs来创建新⽇志，并结束前⼀⽇志写⼊过程。并把前⼀⽇志备份，例如上例中开始保存数据⽬录下的⽇志⽂件 MySQL-bin.000002 , ...1.恢复完全备份mysql -u root -p < backup_sunday_1_2.恢复增量备份mysqlbinlog MySQL-bin.000002 … | MySQL -u root -p注意此次恢复过程亦会写⼊⽇志⽂件，如果数据量很⼤，建议先关闭⽇志功能--compatible=name它告诉 MySQLdump，导出的数据将和哪种数据库或哪个旧版本的 MySQL 服务器相兼容。值可以为 ansi、MySQL323、MySQL40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options 等，要使⽤⼏个值，⽤逗号将它们隔开。当然了，它并不保证能完全兼容，⽽是尽量兼容。--complete-insert，-c导出的数据采⽤包含字段名的完整 INSERT ⽅式，也就是把所有的值都写在⼀⾏。这么做能提⾼插⼊效率，但是可能会受到max_allowed_packet 参数的影响⽽导致插⼊失败。因此，需要谨慎使⽤该参数，⾄少我不推荐。--default-character-set=charset指定导出数据时采⽤何种字符集，如果数据表不是采⽤默认的 latin1 字符集的话，那么导出时必须指定该选项，否则再次导⼊数据后将产⽣乱码问题。--disable-keys告诉 MySQLdump 在 INSERT 语句的开头和结尾增加 /*!40000 ALTER TABLE table DISABLE KEYS */; 和 /*!40000 ALTER TABLE tableENABLE KEYS */; 语句，这能⼤⼤提⾼插⼊语句的速度，因为它是在插⼊完所有数据后才重建索引的。该选项只适合 MyISAM 表。--extended-insert = true|false默认情况下，MySQLdump 开启 --complete-insert 模式，因此不想⽤它的的话，就使⽤本选项，设定它的值为 false 即可。--hex-blob使⽤⼗六进制格式导出⼆进制字符串字段。如果有⼆进制数据就必须使⽤本选项。影响到的字段类型有 BINARY、VARBINARY、BLOB。--lock-all-tables，-x在开始导出之前，提交请求锁定所有数据库中的所有表，以保证数据的⼀致性。这是⼀个全局读锁，并且⾃动关闭 --single-transaction 和 --lock-tables 选项。--lock-tables它和 --lock-all-tables 类似，不过是锁定当前导出的数据表，⽽不是⼀下⼦锁定全部库下的表。本选项只适⽤于 MyISAM 表，如果是 Innodb表可以⽤ --single-transaction 选项。--no-create-info，-t只导出数据，⽽不添加 CREATE TABLE 语句。--no-data，-d不导出任何数据，只导出数据库表结构。mysqldump --no-data --databases mydatabase1 mydatabase2 mydatabase3 > 将只备份表结构。--databases指⽰主机上要备份的数据库。--opt这只是⼀个快捷选项，等同于同时添加 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick--set-charset 选项。本选项能让 MySQLdump 很快的导出数据，并且导出的数据能很快导回。该选项默认开启，但可以⽤ --skip-opt 禁⽤。注意，如果运⾏ MySQLdump 没有指定 --quick 或 --opt 选项，则会将整个结果集放在内存中。如果导出⼤数据库的话可能会出现问题。--quick，-q该选项在导出⼤表时很有⽤，它强制 MySQLdump 从服务器查询取得记录直接输出⽽不是取得所有记录后将它们缓存到内存中。--routines，-R导出存储过程以及⾃定义函数。--single-transaction该选项在导出数据之前提交⼀个 BEGIN SQL语句，BEGIN 不会阻塞任何应⽤程序且能保证导出时数据库的⼀致性状态。它只适⽤于事务表，例如 InnoDB 和 BDB。本选项和 --lock-tables 选项是互斥的，因为 LOCK TABLES 会使任何挂起的事务隐含提交。要想导出⼤表的话，应结合使⽤ --quick 选项。--triggers同时导出触发器。该选项默认启⽤，⽤ --skip-triggers 禁⽤它。跨主机备份使⽤下⾯的命令可以将host1上的sourceDb复制到host2的targetDb，前提是host2主机上已经创建targetDb数据库：-C 指⽰主机间的数据传输使⽤数据压缩mysqldump --host=host1 --opt sourceDb| mysql --host=host2 -C targetDb结合Linux的cron命令实现定时备份⽐如需要在每天凌晨1:30备份某个主机上的所有数据库并压缩dump⽂件为gz格式30 1 * * * mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`date '+%m-%d-%Y'`.⼀个完整的Shell脚本备份MySQL数据库⽰例。⽐如备份数据库opspc[root@test-huanqiu ~]# vim /root/#!bin/bashecho "Begin backup mysql database"mysqldump -u root -ppassword opspc > /home/backup/mysqlbackup-`date +%Y-%m-%d`.sqlecho "Your database backup successfully completed"[root@test-huanqiu ~]# crontab -e30 1 * * * /bin/bash -x /root/ > /dev/null 2>&1mysqldump全量备份+mysqlbinlog⼆进制⽇志增量备份1）从mysqldump备份⽂件恢复数据会丢失掉从备份点开始的更新数据，所以还需要结合mysqlbinlog⼆进制⽇志增量备份。⾸先确保已开启binlog⽇志功能。在中包含下⾯的配置以启⽤⼆进制⽇志：[mysqld]log-bin=mysql-bin2）mysqldump命令必须带上--flush-logs选项以⽣成新的⼆进制⽇志⽂件：mysqldump --single-transaction --flush-logs --master-data=2 > 其中参数--master-data=[0|1|2]0: 不记录1：记录为CHANGE MASTER语句2：记录为注释的CHANGE MASTER语句mysqldump全量+增量备份⽅案的具体操作可参考下⾯两篇⽂档：--------------------------------------------------------------------------下⾯分享⼀下⾃⼰⽤过的mysqldump全量和增量备份脚本应⽤场景：1）增量备份在周⼀到周六凌晨3点，会复制mysql-bin.00000*到指定⽬录；2）全量备份则使⽤mysqldump将所有的数据库导出，每周⽇凌晨3点执⾏，并会删除上周留下的mysq-bin.00000*，然后对mysql的备份操作会保留在⽂件中。脚本实现：1）全量备份脚本（假设mysql登录密码为123456；注意脚本中的命令路径）：[root@test-huanqiu ~]# vim /root/#!/bin/bash# Program# use mysqldump to Fully backup mysql data per week!# History# PathBakDir=/home/mysql/backupLogFile=/home/mysql/backup/e=`date +%Y%m%d`Begin=`date +"%Y年%m⽉%d⽇ %H:%M:%S"`cd $BakDirDumpFile=$umpFile=$/usr/local/mysql/bin/mysqldump -uroot -p123456 --quick --events --all-databases --flush-logs --delete-master-logs --single-transaction >$DumpFile/bin/tar -zvcf $GZDumpFile $DumpFile/bin/rm $DumpFileLast=`date +"%Y年%m⽉%d⽇ %H:%M:%S"`echo 开始:$Begin 结束:$Last $GZDumpFile succ >> $LogFilecd $BakDir/daily/bin/rm -f *2）增量备份脚本（脚本中mysql的数据存放路径是/home/mysql/data，具体根据⾃⼰的实际情况进⾏调整）[root@test-huanqiu ~]# vim /root/#!/bin/bash# Program# use cp to backup mysql data everyday!# History# PathBakDir=/home/mysql/backup/daily //增量备份时复制mysql-bin.00000*的⽬标⽬录，提前⼿动创建这个⽬录BinDir=/home/mysql/data //mysql的数据⽬录LogFile=/home/mysql/backup/File=/home/mysql/data/ //mysql的index⽂件路径，放在数据⽬录下的/usr/local/mysql/bin/mysqladmin -uroot -p123456 flush-logs#这个是⽤于产⽣新的mysql-bin.00000*⽂件Counter=`wc -l $BinFile |awk '{print $1}'`NextNum=0#这个for循环⽤于⽐对$Counter,$NextNum这两个值来确定⽂件是不是存在或最新的for file in `cat $BinFile`do base=`basename $file` #basename⽤于截取mysql-bin.00000*⽂件名，去掉./mysql-bin.000005前⾯的./ NextNum=`expr $NextNum + 1` if [ $NextNum -eq $Counter ] then echo $base skip! >> $LogFile else dest=$BakDir/$base if(test -e $dest) #test -e⽤于检测⽬标⽂件是否存在，存在就写exist!到$LogFile去 then echo $base exist! >> $LogFile else cp $BinDir/$base $BakDir echo $base copying >> $LogFile fi fidoneecho `date +"%Y年%m⽉%d⽇ %H:%M:%S"` $Next Bakup succ! >> $LogFile3）设置crontab任务，执⾏备份脚本。先执⾏的是增量备份脚本，然后执⾏的是全量备份脚本：[root@test-huanqiu ~]# crontab -e#每个星期⽇凌晨3:00执⾏完全备份脚本0 3 * * 0 /bin/bash -x /root/ >/dev/null 2>&1#周⼀到周六凌晨3:00做增量备份0 3 * * 1-6 /bin/bash -x /root/ >/dev/null 2>&14）⼿动执⾏上⾯两个脚本，测试下备份效果[root@test-huanqiu backup]# pwd/home/mysql/backup[root@test-huanqiu backup]# mkdir daily[root@test-huanqiu backup]# lltotal 4drwxr-xr-x. 2 root root 4096 Nov 29 11:29 daily[root@test-huanqiu backup]# ll daily/total 0先执⾏增量备份脚本[root@test-huanqiu backup]# sh /root/

[root@test-huanqiu backup]# lltotal 8-rw-r--r--. 1 root root 121 Nov 29 11:29 xr-xr-x. 2 root root 4096 Nov 29 11:29 daily[root@test-huanqiu backup]# ll daily/total 8-rw-r-----. 1 root root 152 Nov 29 11:29 mysql-binlog.000030-rw-r-----. 1 root root 152 Nov 29 11:29 mysql-binlog.000031[root@test-huanqiu backup]# cat

mysql-binlog.000030 copyingmysql-binlog.000031 copyingmysql-binlog.000032 skip!2016年11⽉29⽇ 11:29:32 Bakup succ!然后执⾏全量备份脚本[root@test-huanqiu backup]# sh /root/

[root@test-huanqiu backup]# lltotal 152-rw-r--r--. 1 root root 145742 Nov 29 11:30 -rw-r--r--. 1 root root 211 Nov 29 11:30 xr-xr-x. 2 root root 4096 Nov 29 11:30 daily[root@test-huanqiu backup]# ll daily/total 0[root@test-huanqiu backup]# cat

mysql-binlog.000030 copyingmysql-binlog.000031 copyingmysql-binlog.000032 skip!2016年11⽉29⽇ 11:29:32 Bakup succ!开始:2016年11⽉29⽇ 11:30:38 结束:2016年11⽉29⽇ 11:30:38 succ