备份及恢复之Mysqldump的使用
本章实验环境说明
[root@db02 ~]# cat /etc/redhat-release CentOS release 6.8 (Final) [root@db02 ~]# uname -r 2.6.32-642.el6.x86_64 [root@db02 ~]# uname -m x86_64 [root@db02 ~]# mysql --version mysql Ver 14.14 Distrib 5.6.34, for Linux (x86_64) using EditLine wrapper
Mysqldump的介绍
mysqldump是数据库用来备份和数据转移的一个工具，一般在数据量很小的时候（几个G）可以用于备份。当数据量比较大的情况下，就不建议用mysqldump工具进行备份了；mysqldump是一个很好用的mysql数据转移工具，具有兼容强强、跨版本等特点导出对象说明：
mysqldump可以针对单个表、多个表、单个数据库、多个数据库、所有数据库进行导出的操作
mysqldump命令工作原理
利用mysqldump命令备份数据的过程，实际上就是把数据（包括库表）从Mysql库里以SQL语句的形式直接输出或者生成备份文件的工程，这种备份成sql语句的方式成为逻辑备份
逻辑备份效率不是很高，在当下的生产环境中，多用于数据量不是很大的备份情况，例如：30GB以内的数据。若在数据库数据量很大的时候采用此备份方法，所用时间就会很长，恢复的时间也会很长，因此，当数据大于30GB（参考值）后，建议选择其他的如xtrabackup的物理方式进行备份和恢复
企业备份案例解析
如果多个库或者多个表备份到一个文件里面了，此时，如何恢复单个库或者单个表
1、找个第三方测试库，将所有备份导入到这个测试库里，然后把需要的单库或表再备份出来，最后恢复到正式库里。
2、如果是单个表恢复，还可以执行grep -w 表名bak.sql >表名.sql 命令
mysqldump的语法
mysqldump -u 用户名 -p 密码 参数 数据库名 > 备份的文件名.sql 为了方便后面的操作我们将密码写入配置文件，在做登录数据库时不需要密码 --all-databases, -A： 备份所有数据库 --databases, -B： 用于备份多个数据库，如果没有该选项，mysqldump 把第一个名字参数作为数据库名，后面的作为表名。使用该选项，mysqldump 把每个名字都当作为数据库名。 --force, -f：即使发现sql错误，仍然继续备份 --host=host_name, -h host_name：备份主机名，默认为localhost --no-data, -d：只导出表结构 --no-create-info -t 只备份表内行数据（sql语句形式），没有行数据 --tab=name -T 将库表和数据分离成不同的文件，行数据是纯文件，表结构是SQL语句，5.6版本默认没有权限操作，需要修改my.cnf参数 --flush-logs -F 刷新binlog日志，生成新binlog文件，将来增量恢复从这个新binlog文件开始，当备份多个库时，每个库都会刷新一次binlog，如果想只刷新一次binlog，可加--lock-all-tables或--master-data参数 --master-data={1|2} 在备份结束中增加binlog日志文件名及相应的binlog位置点 --lock-all-tables -x 备份时对所有数据库的表执行全局读锁，期间同时禁止--single-transaction和--lock-tables参数功能 --lock-tables -l 锁定所有的表为只读 --single-transaction 在备份InnoDB引擎数据表时通常会启用该选项来获取一个一致性的数据快照备份，它的工作原理是设定本次备份会话的隔离级别为REPEATABLE READ,并将整个备份放在一个事物里，以确保执行本次dump会话时，不会看到其他连接会话已经提交了的数据，即备份开始时刻的数据是什么样，备份出来就是什么样子！也相当于锁表备份数据，但是这个参数是允许备份期间写入数据的，而不是-x锁表后的备份期间无法任何数据的写入，启用该参数会关闭--lock-tables --routines -R 备份存储过程和函数数据 --password[=password], -p[password]：密码 --port=port_num, -P port_num：制定TCP/IP连接时的端口号 --quick, -q：快速导出 --tables：覆盖 --databases or -B选项，后面所跟参数被视作表名- --user=user_name, -u user_name：用户名 --xml, -X：导出为xml文件
使用gzip压缩备份数据库的实践
指定压缩命令gzip压缩备份oldboy数据库
[root@db02 ~]# mysqldump -B oldboy |gzip >/opt/oldboy.sql.gz [root@db02 ~]# ll /opt/ total 12 -rw-r--r--. 1 root root 1965 May 3 11:33 oldboy.sql -rw-r--r--. 1 root root 729 May 3 11:33 oldboy.sql.gz
生产场景下，不同的引擎的mysqldump备份命令
innodb引擎的备份命令如下
mysqldump -A -B --master-data=2 --single-transaction |gzip >/opt/all.sql.gz
--single-transaction是innodb引擎专用备份参数，优势就是备份期间数据依然可以执行写操作。--master-data作用；
适合多引擎混合（例如：myisam与innodb混合）的备份命令如下
mysqldump -A -B --master-data=2 |gzip >/opt/all_$(data +%F).sql.gz
这个备份的特点是会锁表，在备份期间会影响数据的写入，使用--master-data会自动开启-x锁表参数功能。
恢复数据库实践
mysql命令以及source命令恢复数据库的原理就是把文件的sql语句，在数据库里重新执行的过程。数据恢复和字符集关联很大，如果字符集不正确会导致恢复的数据乱码
方法一；用source来恢复数据 [root@db02 ~]# mysql -e "show databases;" +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ [root@db02 ~]# mysql -e "source /opt/oldboy.sql" [root@db02 ~]# mysql -e "show databases;" +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | oldboy | | performance_schema | | test | +--------------------+ 方法二：使用重定向来恢复数据 [root@db02 ~]# mysql -e "show databases;" +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ [root@db02 ~]# mysql </opt/ oldboy.sql oldboy.sql.gz rh/ [root@db02 ~]# mysql </opt/oldboy.sql [root@db02 ~]# mysql -e "show databases;" +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | oldboy | | performance_schema | | test | +--------------------+
binlog的介绍
MySQL Binary Log也就是常说的bin-log, ,是mysql执行改动产生的二进制日志文件,其主要作用有两个:
* 数据回复
* 主从数据库。用于slave端执行增删改，保持与master同步。
查看binlog的状态
mysql> show variables like '%log_bin%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | OFF | | log_bin_basename | | | log_bin_index | | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+-------+ 6 rows in set (0.12 sec)
开启binlog
开启二进制日志配置
修改my.cnf参数文件，该方法需要重启
log_bin = mysql_bin #打开日志(主机需要打开)，这个mysql-bin也可以自定义，这里也可以加上路径，如：/home/www/mysql_bin_log/mysql-bin
关闭二进制日志的方法：log-bin = mysql-bin注释掉即可
[root@db02 ~]# mysql -e "show variables like '%log_bin%';" +---------------------------------+-----------------------------------------------+ | Variable_name | Value | +---------------------------------+-----------------------------------------------+ | log_bin | ON | | log_bin_basename | /application/mysql-5.6.34/data/db02-bin | | log_bin_index | /application/mysql-5.6.34/data/db02-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+-----------------------------------------------+
mysqldump+binlog进行数据完整恢复
实验环境的创建
mysql -e "create database oldboy;" mysql -e "use oldboy;create table test( id int(4) not null , name varchar(16) not null);" mysql -e "use oldboy;insert into test values(1,'qqqqqqq');" mysql -e "use oldboy;insert into test values(2,'wwwwwww');" mysql -e "use oldboy;insert into test values(3,'eeeeeee');" [root@db02 ~]# mysql -e "select * from oldboy.test;" +----+---------+ | id | name | +----+---------+ | 1 | qqqqqqq | | 2 | wwwwwww | | 3 | eeeeeee | +----+---------+ mkdir /data/backup -p date -s "2018/03/19" mysqldump -B --master-data=2 --single-transaction oldboy|gzip>/data/backup/oldboy_$(date +%F).sql.gz mysql -e "use oldboy;insert into test values(4,'rrrrrrr');" mysql -e "use oldboy;insert into test values(5,'ttttttt');" mysql -e "use oldboy;insert into test values(6,'yyyyyyy');" [root@db02 ~]# mysql -e "select * from oldboy.test;" +----+---------+ | id | name | +----+---------+ | 1 | qqqqqqq | | 2 | wwwwwww | | 3 | eeeeeee | | 4 | rrrrrrr | | 5 | ttttttt | | 6 | yyyyyyy | +----+---------+
模拟误删数据
date -s "2018/03/19 10:00" mysql -e "drop database oldboy;show databases;"
开始恢复准备
[root@db02 ~]# cd /data/backup/ [root@db02 backup]# ll /application/mysql/data/ auto.cnf db02.err ib_logfile0 performance_schema/ db02-bin.000001 db02.pid ib_logfile1 test/ db02-bin.index ibdata1 mysql/ cp -a /application/mysql/data/db02-bin.* /data/backup/ zcat oldboy_2018-03-19.sql.gz >/data/backup/oldboy_2018-03-19.sql sed -n '22p' /data/backup/oldboy_2018-03-19.sql mysqlbinlog -d oldboy --start-position=4181 db02-bin.000001 -r bin.sql 删除bin.sql里面的drop掉的库的那一行 mysql <oldboy_2018-03-19.sql mysql -e "show databases;" [root@db02 backup]# mysql -e "show databases;" +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | oldboy | | performance_schema | | test | +--------------------+ mysql -e "use oldboy;select * from test;" [root@db02 backup]# mysql -e "use oldboy;select * from test;" +----+---------+ | id | name | +----+---------+ | 1 | qqqqqqq | | 2 | wwwwwww | | 3 | eeeeeee | +----+---------+ mysql oldboy <bin.sql mysql -e "use oldboy;select * from test;" [root@db02 backup]# mysql -e "use oldboy;select * from test;" +----+---------+ | id | name | +----+---------+ | 1 | qqqqqqq | | 2 | wwwwwww | | 3 | eeeeeee | | 4 | rrrrrrr | | 5 | ttttttt | | 6 | yyyyyyy | +----+---------+
浙公网安备 33010602011771号