一、备份类型
1、mysqldump 说明
mysqldump [OPTIONS] database [tables] #支持指定数据库和指定多表的备份,但数据库本身定义不备份 mysqldump [OPTIONS] -B DB1 [DB2 DB3...] #支持指定数据库备份,包含数据库本身定义也会备份 mysqldump [OPTIONS] -A [OPTIONS] #备份所有数据库,包含数据库本身定义也会备份
2、mysqldump 常见通用选项:
-u, --user=name User for login if not current user -p, --password[=name] Password to use when connecting to server -A, --all-databases #备份所有数据库,含create database -B, --databases db_name… #指定备份的数据库,包括create database语句 -E, --events:#备份相关的所有event scheduler -R, --routines:#备份所有存储过程和自定义函数 --triggers:#备份表相关触发器,默认启用,用--skip-triggers,不备份触发器 --default-character-set=utf8 #指定字符集 --master-data[=#]:#注意:MySQL8.0.26版以后,此选项变为--source-data #此选项须启用二进制日志 #1:所备份的数据之前加一条记录为CHANGE MASTER TO语句,非注释,不指定#,默认为1,适合于主从复 制多机使用 #2:记录为被注释的#CHANGE MASTER TO语句,适合于单机使用,适用于备份还原 #此选项会自动关闭--lock-tables功能,自动打开-x | --lock-all-tables功能(除非开启-- single-transaction) -F, --flush-logs #备份前滚动日志,锁定表完成后,执行flush logs命令,生成新的二进制日志文件,配合-A 或 -B 选项时,会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新,可通过和-- single-transaction或-x,--master-data 一起使用实现,此时只刷新一次二进制日志 --compact #去掉注释,适合调试,节约备份占用的空间,生产不使用 -d, --no-data #只备份表结构,不备份数据,即只备份create table -t, --no-create-info #只备份数据,不备份表结构,即不备份create table -n,--no-create-db #不备份create database,可被-A或-B覆盖 --flush-privileges #备份mysql或相关时需要使用 -f, --force #忽略SQL错误,继续执行 --hex-blob #使用十六进制符号转储二进制列,当有包括BINARY,VARBINARY,BLOB,BIT的数据类型的列时使用,避免乱码 -q, --quick #不缓存查询,直接输出,加快备份速度
mysqldump的MyISAM存储引擎相关的备份选项:
-x,--lock-all-tables #加全局读锁,锁定所有库的所有表,同时加--single-transaction或--lock-tables选项会关闭此选项功能,注意:数据量大时,可能会导致长时间无法并发访问数据库 -l,--lock-tables #对于需要备份的每个数据库,在启动备份之前分别锁定其所有表,默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致 #注:以上选项对InnoDB表一样生效,实现温备,但不推荐使用
--single-transaction #此选项Innodb中推荐使用,不适用MyISAM,此选项会开始备份前,先执行START TRANSACTION指令开启事务 #此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表(目前只有InnoDB可以); 转储不保证与其他存储引擎保持一致。 在进行单事务转储时,要确保有效的转储 文件(正确的表内容和二进制日志位置),没有其他连接应该使用以下语句:ALTER TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLE,此选项和--lock-tables(此选项隐含提交挂起的事务)选 项是相互排斥,备份大型表时,建议将--single-transaction选项和--quick结合一起使用
3、生产环境实战备份策略
mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --masterdata=2 --flush-privileges --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql #新版8.0.26以上 mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --sourcedata=2 --flush-privileges --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sq
4、特定数据库的备份脚本
[root@centos8 ~]#cat mysql_backup.sh #!/bin/bash TIME=`date +%F_%H-%M-%S` DIR=/backup DB=hellodb PASS=magedu [ -d $DIR ] || mkdir $DIR mysqldump -uroot -p "$PASS" -F -E -R --triggers --single-transaction --masterdata=2 --default-character-set=utf8 -q -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz
5、分库备份并压缩
6、分库备份的实战脚本
[root@centos8 ~]#cat backup_db.sh #!/bin/bash TIME=`date +%F_%H-%M-%S` DIR=/backup PASS=magedu [ -d "$DIR" ] || mkdir $DIR for DB in `mysql -uroot -p "$PASS" -e 'show databases' | grep -Ewv "^Database|.*schema$"`;do mysqldump -F --single-transaction --master-data=2 --default-characterset=utf8 -q -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz done
7、利用二进制日志,还原数据库最新状态
#二进制日志独立存放 [mysqld] log-bin=/data/mysql/mysql-bin #完全备份,并记录备份的二进制位置 mysqldump -uroot -pmagedu -A -F --default-character-set=utf8 --singletransaction --master-data=2 | gzip > /backup/all_`date +%F`.sql.gz #修改数据库 insert students (name,age,gender)value('mage',20,'M'); insert students (name,age,gender)value('wang',22,'M'); #损坏数据库 rm -rf /var/lib/mysql/* #还原 cd /backup gzip -d all_2019-11-25.sql.gz #CentOS 8 需要事先生成数据库相关文件,CentOS7 不需要执行此步 mysql_install_db --user=mysql
8、mysqldump 和二进制日志结合实现差异(增量)备份
[root@centos8 ~]#mysqldump -uroot -p -A -F --single-transaction --master-data=2 |gzip > /backup/all-`date +%F`.sql.gz #观察上面备份文件中记录的二进制文件和位置,定期将其之后生成的所有二进制日志进行复制备份 [root@centos8 ~]#cp /var/lib/mysql/mariadb-bin.000003 /backup #假设mariadbbin.000003是后续生成的二进制日志 [root@centos8 ~]#mysqlbinlog backup/mariadb-bin.000003 > /backup/inc.sql
文章来源地址https://www.toymoban.com/news/detail-825406.html
文章来源:https://www.toymoban.com/news/detail-825406.html
三、xtrabackup 备份工具
1、xtrabackup 工具介绍
yum install percona-xtrabackup
3、xtrabackup 用法
innobackupex [option] BACKUP-ROOT-DIR
Prepare预准备:
innobackupex --apply-log [option] BACKUP-DIR
--apply-log:#一般情况下,在备份完成后,数据尚且不能用于恢复操作,因为备份的数据中可能会包含尚 未提交的事务或已经提交但尚未同步至数据文件中的事务。因此,此时数据文件仍处理不一致状态。此选项作 用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态 --use-memory:#和--apply-log选项一起使用,当prepare 备份时,做crash recovery分配的内存 大小,单位字节,也可1MB,1M,1G,1GB等,推荐1G --export:#表示开启可导出单独的表之后再导入其他Mysql中 --redo-only:#此选项在prepare base full backup,往其中合并增量备份时候使用,但不包括对最 后一个增量备份的合并
innobackupex --copy-back [选项] BACKUP-DIR innobackupex --move-back [选项] [--defaults-group=GROUP-NAME] BACKUP-DIR
还原注意事项:
4、利用 xtrabackup 实现完全备份及还原
5、利用xtrabackup8.0 完全,增量备份及还原MySQL8.0
6、xtrabackup单表导出和导入
到了这里,关于第二十三天:mysql数据备份及还原的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!