mysql 备份数据库的方式以及使用工具

说明:-uroot 是代表用户名为root

说明2:-pxxx 是代表密码为xxx


备份用的软件

1.mysqldump  <—–数据库自带备份工具

mysqldump基本使用命令

mysqldump -uroot -pxxx 数据库名 > mysql_back.sql    <------备份文件的路径以及名字最好带sql后缀

mysqldump就是将数据以sql语句导出 进行备份  恢复的时候重新执行sql语句进行导入

指定字符集导出数据

附带参数 –default-character-set=utf8

mysqldump -uroot -pxxx --default-character-set=utf8

恢复数据库数据

mysql -uroot -pxxx 数据库名 < /root/mysql_back.sql   

备份数据库以及备份库名和创建库所使用的命令

mysqldump -uroot -pxxx -B 数据库名 > /root/mysql_back_B.sql            <-----------参数 -B 的作用就是将创建数据库时候的操作进行备份 并使用use切换到目标库    所以带上-B不需要提前创建数据库

建议一般备份的时候加上-B  如果你只需要数据可以自行编辑sql文件删除不需要的2步操作

-Bmysqldump恢复方法

mysql -uroot -pxxx < /root/mysql_back_B.sql     <--------注意这里不需要加数据库名,它会自动创建数据库

mysqldump减少输出选项 -compact 

作用:

减少数据    适用于debug 调试模式来查看数据  减少不必要的输出

不适合于备份还原回去

mysqldump备份的原理

实际上就是将数据从mysql库里 以逻辑形式使用sql语句进行导出

mysqldump备份单个或多个表

mysqldump -uroot -pxxx 库名 表名 表名 > /root/mysql_back_tables.sql   <------如果不加-B 默认识别 库名 表名 表名 …      默认库名后面全部识别为表名    如果加了-B 那就是库名 库名

mysqldumo 只备份表结构 不备份表值

使用-d参数

mysqldump -uroot -pxxx -d 库名 > mysql_back_d.sql       <-----默认只有库名的话 则备份所有表结构
mysqldump -uroot -pxxx -d 库名 表名 > mysql_back_d.sql   <-----备份多个指定表的结构

mysqldump 只备份表数据 不备份表结构

使用-t参数

mysqldump -uroot -pxxx. -t 库名 > mysql_back_t.sql 

以上都是myisam引擎的备份

innodb数据库备份

使用参数single-transaction

原理 设定本次会话级别为 REPEATABLE READ 来保持数据一致性

在进行备份的时候可以进行访问和写入数据,但是备份的数据是本次会话的数据,备份过程中的写入数据不进行备份

完整备份mysql所有数据:

mysqldump备份数据库里的所有数据,保护mysql的用户以及其他

mysqldump -uroot -pxxx -A -B --events > /root/mysql_back_all.sql 

使用-F选项  刷新binlog数据

不这么做 完整备份 恢复的时候会报错

mysqldump -uroot -pxxx -A -B -F --events > /root/mysql_back_all.sql

使用–master-data=1 自动找到全库恢复开始的binlog初始位置

mysqldump -uroot -pxxx -A -B -F --events > /root/mysql_back_all.sql

mysqldump关键参数说明:

-B 指定多个库,增加建库语句和use语句

–compact  去掉注释 适合调试输出 生产不用

-A 备份所有库

-F 刷新binlog日志

如果不刷新binlog可以用以下参数

–master-data=1

-x或者–lock-all-tables 锁表  全局读锁,保持数据一致性,建议只用这个

-l或者–lock-tables READ LOCAL锁定表以允许MyISAM表并行插入

-d 只备份表结构

-t 只备份表值

single-transaction 适合innodb事物数据库备份

生产过程中使用的命令总结:

myisam

可以使用-x完全锁表或者有读取访问需求的使用-l只读锁表来进行备份   每次都必须带这2个参数之1

1.单个库备份:

mysqldump -uroot -pxxx -x 库名 > /root/mysql_back.sql

2.多个库备份:

mysqldump -uroot -pxxx -x -B 库名 库名> /root/mysql_back_B.sql

3.单库单表或多表:

mysqldump -uroot -pxxx -x 库名 表名 > /root/mysql_back.sql
mysqldump -uroot -pxxx -x 库名 表名 表名 > /root/mysql_back.sql

4.备份数据库所有数据

mysqldump -uroot -pxxx -A -B --master-data=1 -x --events > /root/mysql_back_all.sql

innodb:

innodb备份的好处:热备份,不需要锁表备份,不影响业务访问

mysqldump -uroot -pxxx --single-transaction 库名 >    /root/mysql_back_innodb.sql

其他命令跟myisam基本一样

innodb备份数据库所有数据

mysqldump -uroot -pxxx -A -B --master-data=1 --single-transaction --events > /root/mysql_back_all.sql

专职DBA所使用的备份命令:

myisam:

mysqldump -uroot -pxxx --all-database --flush-privileges --lock-all-tables \
--master-data=1 --flush-logs --triggers --routines --events \
--hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql 

innodb:

mysqldump -uroot -pxxx --all-database --flush-privileges --single-transaction \
--master-data=1 --flush-logs --triggers --routines --events \
--hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql 

备份工具2:xtrabackup

xtrabackup 对于MyISAM只支持温备,不支持增量备份;支持innodb的物理热备份,支持完全备份,增量备份,而且速度非常快,支持innodb存储引起的数据在不同数据库之间迁移,支持复制模式下的从机备份恢复备份恢复,为了让xtrabackup支持更多的功能扩展,可以设立独立表空间,打开 innodb_file_per_table功能,启用之后可以支持单独的表备份。xtrabackup可以实现完全备份,增量备份,以及部分备份,其支持在线热备与恢复,在大数据量的时候,备份恢复都比较快。

xtraBackup基于InnoDB的crash-recovery功能。它会复制innodb的data file,由于不锁表,复制出来的数据是不一致的,在恢复的时候使用crash-recovery,使得数据恢复一致。