CentOS7mysql5.7使用mysqldump与binlo

2018-12-11 本文已影响0人 summer_coen

数据备份

1.mysqldump备份

语法：

mysqldump -uroot -p [database name] > [dump file]

列如：

mysqldump -uroot -p --all-databases > test.sql #备份所有数据库结构和数据

mysqldump -uroot -p --databases a b > test.sql #备份a、b数据库结构和数据

mysqldump -uroot -p a > test.sql #备份a数据库结构和数据，但是生成的sql文件没有CREATE DATABASE 和USE语句（不推荐）

mysqldump -uroot -proot --no-data --databases a >test.sql #备份a数据库的结构

mysqldump -uroot -p --databases a --tables a1 a2 > test.sql #备份a数据库下a1,a2表结构和数据（创建的sql语句没有use选库，恢复的时候得先登录，在use dbname;source test.sql;）

mysqldump -hhost1 -uroot -proot --databases db1 |mysql -hhost2 -uroot -proot db2 #跨服务器导出导入数据，将host1服务器中的db1数据库的所有数据导入到host2中的db2数据库中，db2的数据库必须存在否则会报错（可以加上 -C参数可以启用压缩传递）

mysqldump -uroot -p -B -F -R -x --master-data=2 a|gzip >ops_$(date +%F).sql.gz #-B：指定数据库，-F：刷新日志，-R：备份存储过程等，-x：锁表，--master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息，备份a数据库，输出文件名年月日的gz格式

其余参数：

-no-create-info, -t 只导出数据，而不添加CREATE TABLE 语句

no-create-db, -n 只导出数据，而不添加CREATE DATABASE 语句

--ignore-table 导出数据库时忽略某个表

--force，-f 在导出过程中忽略出现的SQL错误，当出现错误时仍然继续后面的操作

--add-drop-database 每个数据库创建之前添加drop数据库语句

--add-drop-table 每个数据表创建之前添加drop数据表语句，默认为打开状态，使用–skip-add-drop-table取消选项

--add-locks在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE，默认为打开状态，使用–skip-add-locks取消选项

--default-character-set 设置默认字符集，默认值为utf8

--comments 附加注释信息，默认为打开，可以--skip-comments取消

--compact导出更少的输出信息(用于调试)，去掉注释和头尾等结构，（可以使用选项 –skip-add-drop-table –skip-add-locks –skip-comments –skip-disable-keys）

--events, -E 导出事件

--flush-privileges 在导出mysql数据库之后，发出一条FLUSH PRIVILEGES 语句

--flush-logs 开始导出之前刷新日志，请注意，假如一次导出多个数据库(使用选项–databases或者–all-databases)，将会逐个数据库刷新日志

除使用--lock-all-tables或者--master-data外，在这种情况下，日志将会被刷新一次，相应的表同时被锁定，因此，如果打算同时导出和刷新日志应该使用–lock-all-tables 或者–master-data 和–flush-logs

--delayed-insert 采用延时插入方式（INSERT DELAYED）

--comments 添加注释信息

--compact 压缩模式，产生更少的输出

--complete-insert 输出完成的插入语句

--default-character-set 指定默认字符集

--lock-tables 备份前，锁定所有数据库表

--obt 建表语句包含drop table if exists tableName,insert之前包含一个锁表语句lock tables tableName write，insert之后包含unlock tables

--lock-all-tables, -x提交请求锁定所有数据库中的所有表，以保证数据的一致性，这是一个全局读锁，并且自动关闭–single-transaction 和–lock-tables 选项

--debug 输出debug信息，用于调试（默认值为d:t:o,/tmp/mysqldump.trace）例 mysqldump -uroot -p –all-databases –debug=” d:t:o,/tmp/debug.trace”

在导入sql语句的时候，mysql会使用LOCK TABLES与UNLOCK TABLES来锁表，是表级锁，分为可读，可写。

语法： LOCK TABLES tablename READ | WRITE；

如果一个线程获得在一个表上的一个READ锁，该线程和所有其他线程只能从表中读。

如果一个线程获得一个表上的一个WRITE锁，那么只有持锁的线程READ或WRITE表，其他线程被阻塞。

UNLOCK TABLES释放被当前线程持有的任何锁。

当线程发出另外一个LOCK TABLES时，或当服务器的连接被关闭时，当前线程锁定的所有表会自动被解锁。

2.从备份文件恢复数据库

语法：

mysql -uroot -p < [backup file name]

列如：

1.在Shell命令下：

mysql –uroot –p < test.sql

2,在mysql命令下，用source命令导入备份文件：

mysql> source test.sql; //已登录mysql，用source命令

如果备份文件中不包含CREATE DATABASE和USE语句，那么在恢复的时候必须先创建数据库。

3.mysqlbinlog二进制日志增量备份

从mysqldump备份文件恢复数据会丢失掉从备份点开始的更新数据，所以还需要结合mysqlbinlog二进制日志增量备份。

binlog简介

binlog日志由配置文件的 log-bin 选项负责启用，MySQL服务器将在数据根目录创建两个新文件XXX-bin.001和xxx-bin.index，若配置选项没有给出文件名，Mysql将使用主机名称命名这两个文件，其中.index文件包含一份全体日志文件的清单。

Mysql会把用户对所有数据库的内容和结构的修改情况记入XXX-bin.n文件，而不会记录 SELECT和没有实际更新的UPDATE语句。

当MySQL数据库停止或重启时，服务器会把日志文件记入下一个日志文件，Mysql会在重启时生成一个新的binlog日志文件，文件序号递增，此外，如果日志文件超过max_binlog_size系统变量配置的上限时，也会生成新的日志文件。

3.1开启binlog日志

修改 MySQL 的配置文件my.cnf 如下：

[mysqld]

log-bin = ON

binlog_format = row

log-bin-index =/var/lib/mysql/data/mysql-bin.index

#指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录

log-bin-basename =/var/lib/mysql/data/mysql-bin

#binlog日志的基本文件名，后面会追加标识来表示每一个文件

server-id = 1

#需要记录binlog数据库

binlog_do_db = mall

binlog_format的几种格式：（STATEMENT，ROW和MIXED）

STATEMENT：基于SQL语句的复制(statement-based replication, SBR) ，日志文件小，节约IO，提高性能。准确性差，对一些系统函数不能准确复制或不能复制，如now()、uuid()等

ROW：基于行的复制(row-based replication, RBR) ，准确性强，能准确复制数据的变更。日志文件大，较大的网络IO和磁盘IO。

MIXED：混合模式复制(mixed-based replication, MBR)。准确性强，文件大小适中，有可能发生主从不一致问题。

5.7.3以后版本必须配置server-id，随机指定一个不能和其他集群中机器重名的字符串，如果只有一台机器，那就可以随便指定了。

可以省略log-bin-index 和log-bin-basename参数，把log-bin = ON 换成log-bin = /var/lib/mysql/data/mysql-bin即可。

查看binlog开启情况

登录mysql；show variables like '%log_bin%'；查看

查看binlog日志

mysqlbinlog /var/lib/mysql/data/mysql-bin.000001 #一般的statement格式的二进制文件

mysqlbinlog -v /var/lib/mysql/data/mysql-bin.000001 #如果是row格式，加上-v或者-vv参数就行

如下：

server id 13453 ：数据库主机的服务号；

end_log_pos 535： sql结束时的pos节点；

查看binlog日志列表

使用

mysql> show master logs;

删binlog日志

（1）使用reset master,该命令将会删除所有日志，并让日志文件重新从000001开始。

mysql > reset master;

（2）使用命令：PURGE{BINARY|MASTER}LOGS{TO'log_name'|BEFOREdatetime_expr }

mysql> purge master logs to "binlog_name.00000X" #将会清空00000X之前的所有日志文件

binlog日志恢复

这个binlog二进制binlog日志包括两类文件：

* 索引文件（文件名后缀为.index）用于记录哪些日志文件正在被使用

* 日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

在恢复前了解一下基本知识，使用mysqlbinlog 可以查看操作，但是不直观，下面介绍一种mysql中经常使用的。

语法：mysql>show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

参数解释：

IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)

FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)

LIMIT [offset,] ：偏移量(不指定就是0)

row_count ：查询总条数(不指定就是所有行)

列如：

mysql> show binlog events in 'mysql-bin.000001'\G; #指定查询 mysql-bin.000001这个文件。

mysql> show binlog events in 'mysql-bin.000001' from 294\G; #指定查询 mysql-bin.000001这个文件，从pos点:294开始查起。

mysql> show binlog events in 'mysql-bin.000001' from 294 limit 10\G; #指定查询 mysql-bin.000001这个文件，从pos点:294开始查起，查询10条（即10条语句）。

mysql> show binlog events in 'mysql-bin.000001' from 294 limit 2,10\G; #指定查询 mysql-bin.000001这个文件，从pos点:294 开始查起，偏移2行（即中间跳过2个），查询10条

查询数据含义：

Log_name：查询的binlog日志文件名

Pos：pos起始点

Event_type：事件类型（

我这数据库的Binlog模式是row 数据引擎innodb，类型说明如下：

1) QUERY：与STATEMENT模式处理相同，存储的是SQL，主要是一些与数据无关的操作，eg: begin、drop table、truncate table 等；

2) TABLE_MAP：记录了下一条事件所对应的表信息，在其中存储了数据库名和表名；(test.a_view)

3) WRITE_ROWS：操作类型为insert；（insert）

4) UPDATE_ROWS：操作类型为update；（update）

5) DELETE_ROWS：操作类型为delete；（delete）

6) XID，用于标识事务提交。

以一条insert语句为例，包含4个事件：

QUERY (begin)，TABLE_MAP，WRITE_ROWS，XID

）

Server_id：标识是由哪台服务器执行的

End_log_pos：pos结束点(即：下行的pos起始点)

Info：说明

进入正题。

恢复语法格式：

mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码数据库名

常用参数选项解释：
--start-position #起始pos点

--stop-position #结束pos点

--start-datetime="xxxx-xx-xx xx:xx:xx" #起始时间点

--stop-datetime="xxxx-xx-xx xx:xx:xx" #结束时间点

--database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)

-u --user=name 连接到远程主机的用户名

-p --password[=name] 连接到远程主机的密码

-h --host=name 从远程主机上获取binlog日志

--read-from-remote-server 从某个MySQL服务器上读取binlog日志

实践

接下来我对takeout数据库users表修改一条数据，在把表删除。

name字段，从as1改成test。连接mysql。

查看现在写入的是哪个日志

mysql> show master status;

现在是mysql-bin.000009，把mysql-bin.000009备份，刷新日志写入，保证接下来的日志不会写入000009。

查看000009日志情况。

mysql> show binlog events in 'mysql-bin.000009'\G;

update_rows修改事件，pos起始位置3787，结束pos4339，因为是一个事务，会有其他什么开启事务啊，提交事务什么的；我们要恢复修改之前数据，所以结束pos点为3787。

[root@UsaOfficeLuke data]# mysqlbinlog --stop-position=3787 mysql-bin.000009 | mysql -uroot -p -v takeout

然后看数据库

修改成功！（也可以根据日志中开始执行的时间来进行恢复）