逻辑备份Mysqlbinlog与Mysqldump命令的深入使用
一、通过binlog恢复数据案例
#查看二进制日志找到位置点
[root@db02 data]# mysqlbinlog mysql-bin.000002
'将位置点之间的数据取出 ！！重要！！'
mysqlbinlog --start-position=631 --stop-position=978 mysql-bin.000002 > /tmp/hf.sql
将数据导入回去
mysql -u root -p密码 < /tmp/hf.sql
############## !!!生产数据恢复: 重要 ##############
方式一:
"可以通过命令先取出所有数据:"
mysqlbinlog --start-position=最初起始位置 --stop-position=最终位置 mysql-bin.000001或所有 > /tmp/mysql_all.sql
方式二:
"通过mysqlbinlog命令查看时间点"
[root@db03 data]# mysqlbinlog mysql-bin.000014
"然后通过时间戳查找要恢复故障的位置点."
cat mysql_all.sql
最后通过时间点把需要的数据再次导出,检查无误后导入数据库.
mysqlbinlog --start-position=故障点起始位 --stop-position=故障点后的数据位 mysql-bin.000001或所有 > /tmp/mysql_all.sql
"直接通过时间点来恢复数据的方式:"
适合于多个binlog文件操作.
mysqlbinlog --start-datetime= --stopdatetime= mysql-bin.000001 mysql-bin.000002...
"如果不能确认的情况下位置点和时间点还可以混用."
############## !!!生产数据恢复: 重要 ##############
二、mysqlbinlog命令
在MySQL或MariaDB中，任意时间对数据库所做的修改，都会被记录到日志文件中。例如，当你添加了一个新的表，或者更新了一条数据，这些事件都会被存储到二进制日志文件中。二进制日志文件在MySQL主从复合中是非常有用的，主服务器会发送其数据到远程服务器中。
1. 使用实例细节
当你需要恢复MySQL时，也会需要使用到二进制日志文件。
mysqlbinlog 命令，以用户可视的方式展示出二进制日志中的内容。同时，也可以将其中的内容读取出来，供其他MySQL实用程序使用。
在此示例中，我们将会涉及以下内容：
- 获取当前二进制日志列表
- mysqlbinlog默认行为
- 获取特定数据库条目
- 禁止恢复过程产生日志
- 在输出中控制base-64 BINLOG
- mysqlbinlog输出调试信息
- 跳过前N个条目
- 保存输出到文件
- 从一个特定位置提取条目
- 将条目截止到一个特定的位置
- 刷新日志以清除Binlog输出
- 在输出中只显示语句
- 查看特定开始时间的条目
- 查看特定结束时间的条目
- 从远程服务器获取二进制日志
- 1.获取当前二进制日志列表
在mysql中执行以下命令，即可查看二进制日志文件的列表。
mysql> SHOW BINARY LOGS;
+----------------------+----------+
| Log_name | File_size |
+--------------------------+------------+
| mysqld-bin.000001 | 15740 |
| mysqld-bin.000002 | 3319 |
..
..
如果熊没有开启此功能，则会显示：
mysql> SHOW BINARY LOGS;
ERROR 1381 (HY000): You are not using binary logging
二进制日志文件默认会存放在 /var/lib/mysql 目录下
$ ls -l /var/lib/mysql/
-rw-rw----. 1 mysql mysql 15740 Aug 28 14:57 mysqld-bin.000001
-rw-rw----. 1 mysql mysql 3319 Aug 28 14:57 mysqld-bin.000002
..
..
- 2. mysqlbinlog 默认行为
下面将以一种用户友好的格式显示指定的二进制日志文件（例如:mysqld.000001）的内容。
$ mysqlbinlog mysqld-bin.000001
mysqlbinlog默认会显示为以下内容：
/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/;# at 4#170726 14:57:37 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.73-log created 170726 14:57:37 at startup# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG ' IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC '/*!*/;
# at 106
#170726 14:59:31 server id 1 end_log_pos 182 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1501095571/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
..
..
..
# at 14191
#170726 15:20:38 server id 1 end_log_pos 14311 Query thread_id=4 exec_time=0 error_code=0SET TIMESTAMP=1501096838/*!*/;
insert into salary(name,dept) values('Ritu', 'Accounting')/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*
上面的命令将会显示出，在该系统上数据库发生的所有改变事件。
- 3. 获取数据库条目
默认情况下，mysqlbinlog会显示所有的内容，太过于杂乱。使用 -d 选项，可以指定一个数据库名称，将只显示在该数据库上所发生的事件。
$ mysqlbinlog -d crm mysqld-bin.000001 > crm-events.txt
也可以使用 --database 命令，效果相同。
$ mysqlbinlog -database crm mysqld-bin.000001 > crm-events.txt
- 4. 禁止恢复过程产生日志
在使用二进制日志文件进行数据库恢复时，该过程中也会产生日志文件，就会进入一个循环状态，继续恢复该过程中的数据。因此，当使用mysqlbinlog命令时，要禁用二进制日志，请使用下面所示的-D选项：
$ mysqlbinlog -D mysqld-bin.000001
也可以使用 --disable-log-bin 命令，效果相同。
$ mysqlbinlog --disable-log-bin mysqld-bin.000001
备注：在输出中，当指定-D选项时，将看到输出中的第二行。也就是SQL_LOG_BIN=0
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!32316 SET @OLD_SQL_LOG_BIN=@@SQL_LOG_BIN, SQL_LOG_BIN=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
当使用-to-last-log选项时，这个选项也会有所帮助。另外，请记住，该命令需要root权限来执行。
- 5. 在输出中控制base-64 BINLOG
使用base64-output选项，可以控制输出语句何时是输出base64编码的BINLOG语句。以下是base64输出设置的可能值：
never
always
decode-rows
auto（默认）
never：当指定如下所示的“never”时，它将在输出中显示base64编码的BINLOG语句。
$ mysqlbinlog --base64-output=never mysqld-bin.000001
将不会有任何与下面类似的行，它具有base64编码的BINLOG。
BINLOG ' IeZ4WQ8BAAAAZgAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
always：当指定“always”选项时，只要有可能，它将只显示BINLOG项。因此，只有在专门调试一些问题时才使用它。
$ mysqlbinlog --base64-output=always mysqld-bin.000001
下面是“always”的输出，它只显示了BINLOG项。
BINLOG ' IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC '/*!*/;
# at 106
#170726 14:59:31 server id 1 end_log_pos 182
BINLOG ' k+Z4WQIBAAAATAAAALYAAAAIAAIAAAAAAAAADAAAGgAAAEAAAAEAAAAAAAAAAAYDc3RkBAgACAAI AHRoZWdlZWtzdHVmZgBCRUdJTg== '/*!*/;
# at 182
#170726 14:59:30 server id 1 end_log_pos 291
BINLOG ' kuZ4WQIBAAAAbQAAACMBAAAAAAIAAAAAAAAADAAAGgAAAEAAAAEAAAAAAAAAAAYDc3RkBAgACAAI AHRoZWdlZWtzdHVmZgBJTlNFUlQgSU5UTyB0IFZBTFVFUygxLCAnYXBwbGUnLCBOVUxMKQ== '/*!*/;
# at 291
#170726 14:59:30 server id 1 end_log_pos 422
BINLOG ' kuZ4WQIBAAAAgwAAAKYBAAAAAAIAAAAAAAAADAAAGgAAAEAAAAEAAAAAAAAAAAYDc3RkBAgACAAI AHRoZWdlZWtzdHVmZgBVUERBVEUgdCBTRVQgbmFtZSA9ICdwZWFyJywgZGF0ZSA9ICcyMDA5LTAx LTAxJyBXSEVSRSBpZCA9IDE=
decode-rows：这个选项将把基于行的事件解码成一个SQL语句，特别是当指定-verbose选项时，如下所示。
$ mysqlbinlog --base64-output=decode-rows --verbose mysqld-bin.000001
auto：这是默认选项。当没有指定任何base64解码选项时，它将使用auto。在这种情况下，mysqlbinlog将仅为某些事件类型打印BINLOG项，例如基于行的事件和格式描述事件。
$ mysqlbinlog --base64-output=auto mysqld-bin.000001
$ mysqlbinlog mysqld-bin.000001
- 6. mysqlbinlog输出调试信息
下面的调试选项，在完成处理给定的二进制日志文件之后，将检查文件打开和内存使用。
$ mysqlbinlog --debug-check mysqld-bin.000001
如下所示，在完成处理给定的二进制日志文件之后，下面的调试信息选项将显示额外的调试信息。
$ mysqlbinlog --debug-info mysqld-bin.000001 > /tmp/m.di
User time 0.00, System time 0.00
Maximum resident set size 2848, Integral resident set size 0
Non-physical pagefaults 863, Physical pagefaults 0, Swaps 0
Blocks in 0 out 48, Messages in 0 out 0, Signals 0
Voluntary context switches 1, Involuntary context switches 2
- 7. 跳过前N个条目
除了读取整个mysql二进制日志文件外，也可以通过指定偏移量来读取它的特定部分。可以使用 -o 选项。o代表偏移。
下面将跳过指定的mysql bin日志中的前10个条目。
$ mysqlbinlog -o 10 mysqld-bin.000001
为了确保它正常工作，给偏移量提供一个巨大的数字，将看不到任何条目。下面的内容将从日志中跳过10,000个条目(事件)。
$ mysqlbinlog -o 10000 mysqld-bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; .. ..
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
在本例中，由于这个特定的日志文件没有10,000个条目，所以在输出中没有显示任何数据库事件。
- 8. 保存输出到文件
也可以使用简单的Linux重定向命令，将输出存储到一个文件中，如下所示。
$ mysqlbinlog mysqld-bin.000001 > output.log
或者也可以使用 -r （结果文件）选项，如下所示，将输出存储到一个文件中。
$ mysqlbinlog -r output.log mysqld-bin.000001
备注：还可以使用 -server-id 指定mysql服务器，确保是由给定服务器id的mysql服务器所生成的日志。
$ mysqlbinlog --server-id=1 -r output.log mysqld-bin.000001
- 9. 从一个特定位置提取条目
通常在mysql二进制日志文件中，你将看到如下所示的位置号。下面是mysqlbinlog的部分输出，你可以看到“15028”是一个位置编号。
#170726 15:38:14 server id 1 end_log_pos 15028 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1501097894/*!*/;
insert into salary values(400,'Nisha','Marketing',9500)
/*!*/;
# at 15028
#170726 15:38:14 server id 1 end_log_pos 15146 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1501097894/*!*/;
insert into salary values(500,'Randy','Technology',6000)
下面的命令将从位置编号为15028的二进制日志条目处开始读取。
$ mysqlbinlog -j 15028 mysqld-bin.000001 > from-15028.out
当在命令行中指定多个二进制日志文件时，开始位置选项将仅应用于给定列表中的第一个二进制日志文件。还可以使用 -H 选项来获得给定的二进制日志文件的十六进制转储，如下所示。
$ mysqlbinlog -H mysqld-bin.000001 > binlog-hex-dump.out
- 10. 将条目截止到一个特定的位置
就像前面的例子一样，你也可以从mysql二进制日志中截止到一个特定位置的条目，如下所示。
$ mysqlbinlog --stop-position=15028 mysqld-bin.000001 > upto-15028.out
上面的示例将在15028的位置上停止binlog。当在命令行中指定多个二进制日志文件时，停止位置将仅应用于给定列表中的最后一个二进制日志文件。
- 11. 刷新日志以清除Binlog输出
当二进制日志文件没有被正确地关闭时，将在输出中看到一个警告消息，如下所示。
$ mysqlbinlog mysqld-bin.000001 > output.out
如下所示，报告中提示binlog文件没有正确地关闭。
# head output.log
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
.. ..
# Warning: this binlog is either in use or was not closed properly.
..
.. .. BINLOG ' IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
当看到这个提示时，需要连接到mysql并刷新日志，如下所示。
mysql> flush logs;
刷新日志之后，再次执行mysqlbinlog命令，将不会看到在mysqlbinlog输出中binlog未正确关闭的警告消息。
- 12. 在输出中只显示语句
默认情况下，正如在前面的示例输出中看到的一样，除了SQL语句之外，在mysqlbinlog输出中还会有一些附加信息。如果只想查看常规的SQL语句，而不需要其他内容，那么可以使用 -s 选项，如下所示。
也可以使用 --short-form 选项，效果相同。
$ mysqlbinlog -s mysqld-bin.000001
$ mysqlbinlog --short-form mysqld-bin.000001
下面是上述命令的部分输出。在这里，它将只显示来自给定二进制日志文件的SQL语句。
SET TIMESTAMP=1501096106/*!*/;
insert into employee values(400,'Nisha','Marketing',9500)/*!*/;
SET TIMESTAMP=1501096106/*!*/;
insert into employee values(500,'Randy','Technology',6000)
..
..
不会显示像下面这样的条目：
# at 1201
#170726 15:08:26 server id 1 end_log_pos 1329 Query thread_id=3 exec_time=0 error_code=0
- 13. 查看特定开始时间的条目
下面将只提取从指定时间开始的条目。在此之前的任何条目都将被忽略。
$ mysqlbinlog --start-datetime="2017-08-16 10:00:00" mysqld-bin.000001
当你想要从一个二进制文件中提取数据时，这是非常有用的，因为你希望使用它来恢复或重构在某个时间段内发生的某些数据库活动。时间戳的格式可以是MySQL服务器所理解的DATETIME和timestamp中的任何类型。
- 14. 查看特定结束时间的条目
与前面的开始时间示例一样，这里也可以指定结束时间，如下所示。
$ mysqlbinlog --stop-datetime="2017-08-16 15:00:00" mysqld-bin.000001
上面的命令将读取到给定结束时间的条目。任何来自于超过给定结束时间的mysql二进制日志文件的条目都不会被处理。
- 15. 从远程服务器获取二进制日志
在本地机器上，还可以读取位于远程服务器上的mysql二进制日志文件。为此，需要指定远程服务器的ip地址、用户名和密码，如下所示。
此处使用-R选项。-R选项与-read-from-remote-server相同。
$ mysqlbinlog -R -h 192.168.101.2 -uroot -p123 mysqld-bin.000001
在上面命令中：
-R 选项指示mysqlbinlog命令从远程服务器读取日志文件
-h 指定远程服务器的ip地址
-p 将提示输入密码。默认情况下，它将使用“root”作为用户名。也可以使用 -u 选项指定用户名。
mysqld-bin.000001 这是在这里读到的远程服务器的二进制日志文件的名称。
下面命令与上面的命令完全相同：
$ mysqlbinlog --read-from-remote-server --host=192.168.101.2 -p mysqld-bin.000001
如果只指定 -h 选项，将会得到下面的错误消息。
$ mysqlbinlog -h 192.168.101.2 mysqld-bin.000001
mysqlbinlog: File 'mysqld-bin.000001' not found (Errcode: 2)
当你在远程数据库上没有足够的特权时，将得到以下“不允许连接”错误消息。在这种情况下，确保在远程数据库上为本地客户机授予适当的特权。
$ mysqlbinlog -R --host=192.168.101.2 mysqld-bin.000001
ERROR: Failed on connect: Host '216.172.166.27' is not allowed to connect
to this MySQL server
如果没有使用 -p 选项指定正确的密码，那么将得到以下“访问拒绝”错误消息。
$ mysqlbinlog -R --host=192.168.101.2 mysqld-bin.000001
ERROR: Failed on connect: Access denied for user 'root'@'216.172.166.27' (using password: YES)
下面的示例显示，还可以使用-u选项指定mysqlbinlog应该用于连接到远程MySQL数据库的用户名。请注意，这个用户是mysql用户（不是Linux服务器用户）。
$ mysqlbinlog -R --host=192.168.101.2 -u root -p mysqld-bin.000001<span style="text-indent: 2em;"> </span>
- 16. -vvv 参数
-v, --verbose Reconstruct pseudo-SQL statements out of row events.
- 显示sql 语句
-vv Reconstruct pseudo-SQL statements out of row events. adds comments on column data types.
- 显示sql 语句加 字段类型
下面来看实验：
1、刷日志 ，往表中出入一条数据
2、不加任何参数，看不出什么信息
#180705 11:01:01 server id 92 end_log_pos 383 CRC32 0xa17cd722 Write_rows: table id 111 flags: STMT_END_F
BINLOG '
7Yk9WxNcAAAALwAAAFcBAAAAAG8AAAAAAAEABHRlc3QABHRlc3QAAQMAAbHvqRQ=
7Yk9Wx5cAAAAKAAAAH8BAAAAAG8AAAAAAAEAAgAB//4BAAAAItd8oQ==
'/*!*/;
# at 383
#180705 11:01:03 server id 92 end_log_pos 414 CRC32 0x528d63a1 Xid = 131
COMMIT/*!*/;/
3、加 -v ：mysqlbinlog -v mysql-bin.000040
### INSERT INTO `test`.`test`
### SET
### @1=1
# at 383
#180705 11:01:03 server id 92 end_log_pos 414 CRC32 0x528d63a1 Xid = 131
COMMIT/*!*/;
4、加-vv ：mysqlbinlog -vv mysql-bin.000040
### INSERT INTO `test`.`test`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
# at 383
#180705 11:01:03 server id 92 end_log_pos 414 CRC32 0x528d63a1 Xid = 131
COMMIT/*!*/;
三、mysqldump 备份
mysqldump 选择场景：
- 优点
压缩比很高：基本上是1比10的比例。
可读性强，节省空间，不需要下载安装。
- 缺点
数据量较少，普通情况下一般建议100G以内。
分布式架构，数据量巨大时，可以采用分布式备份，也可以选择mysqldump
1.权限和常用参数
1) 需要的权限
GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, TRIGGER on *.* to back@'%' identified by '密码';
2) 常用参数
1.不加参数：用于备份单个表
1）备份库和库下所有表(表级别)
mysqldump ku > /tmp/ku.sql
2）备份表
mysqldump ku test > /tmp/ku.sql
3）备份多个表
mysqldump ku test test2 test3 > /tmp/ku.sql
#注意：当不加参数时命令后面跟的是库名，库的后面全都是必须是库下面的表名
# 如果导出的只是表,导入时需要追加导入库的库名:
mysql ku < tmp/ku.sql
2.连接服务端参数(基本参数)：-u -p -h -P -S
3.-A, --all-databases：全库备份
4.-B：指定库备份
[root@db01 ~]# mysqldump -uroot -p123 -B db1 > /backup/db1.sql
[root@db01 ~]# mysqldump -uroot -p123 -B db1 db2 > /backup/db1_db2.sql
5.-F：flush logs在备份时自动刷新binlog
"注意:
如果使用了 -A 会针对每一个库做一次刷新binlog,会导致同时刷新多次.此时需要加入--master data 参数可以使他只刷新一次."
[root@db01 backup]# mysqldump -uroot -p123 -A -F > /backup/full_2.sql
6.--master-data=2：备份时加入change master语句:0没有1不注释2注释
1）等于2：记录binlog信息，并注释（日常备份）
2）等于1：记录binlog信息，不注释（扩展从库）
0）等于0：不记录binlog信息
会自动锁表和解锁。
配合single transaction 可以减少锁表时间
[root@db01 backup]# mysqldump -uroot -p123 --master-data=2 >/backup/full.sql
7.--single-transaction：这个参数和锁表参数是互斥的，两个只能加一个；
--single-transaction是创建一个一致性的快照通过dump 所有的表到一个单独的transaction，
添加这个参数后其实会默认加一个锁，用户在备份期间可以增删改操作，但不能修改表结构
8.-d：仅表结构
9.-t：仅数据
10.-R, --routines：备份存储过程和函数数据
11.--triggers：备份触发器数据
12.-E 备份事件
"报错：1153 - Got a packet bigger than 'max_allowed_packet' bytes"
表示数据包过大不够用，如果设置cnf配置文件需要区分是配置在客户端还是服务端。
13.--max_allowed_packet=64M
14.gzip:压缩备份
#备份成压缩包
[root@db01 ~]# mysqldump -uroot -p123 -A | gzip > /backup/full.sql.gz
#恢复压缩包中的数据
[root@db03 ~]# zcat /tmp/full.sql.gz | mysql -uroot -p123
'完整的备份命令(--master-data=2需开启binlog)'：
mysqldump -uroot -p123 -A -R -E --triggers --master-data=2 --single-transaction > /tmp/full.sql
'报错注意：'
root用户没有访问root@'172.16.1.51'的权限。因为默认设置的用户为root@'localhost'
- 从mysqldump 全备中获取 库和表的备份
1、获得表结构
sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q' full.sql>createtable.sql
2、获得INSERT INTO 语句，用于数据的恢复
grep -i 'INSERT INTO `city`' full.sqll >data.sql &
3.获取单库的备份
sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql
2.注意事项
以下两者的区别：
备份库级别：mysqldump -uroot -p123 -B world >...
备份表级别：mysqldump -uroot -p123 world >...
加-B会增加 create database... world；use world；语句
所以在恢复的时候会有区别。
1）mysqldump在备份和恢复时都需要MySQL实例启动为前提
2）一般数据量级100G以内，大约15-30分钟可以恢复
3）mysqldump是以覆盖的形式恢复数据的
'mysqldump导出的数据里有drop语句，在导入时候必须删除，
否则可能会导致同名的有内容的数据库被删除而丢失数据。'
3. 常见报错
报错1：
ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: ' ?'.
导入数据的格式错误，比如使用了gzip压缩的sql包。
报错2：
MySql 数据库导入"Unknown command '\n'."
错误解决办法：
在CMD 下 输入: Mysql -u root -p --default-character-set=utf8 database <backpath
注:出现这种错误一般是由于备份数据的字符集和恢复时使用的字符集不一致所致，常用的两种字符集是：UTF8 GBK
报错3：
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
# 原因:
因为二进制日志的一个重要功能是用于主从复制，而存储函数有可能导致主从的数据不一致。所以当开启二进制日志后，参数log_bin_trust_function_creators就会生效，限制存储函数的创建、修改、调用。
解决:
set global log_bin_trust_function_creators=1;
报错4：
ERROR 1118 (42000) at line 5: Row size too large (> 8126).
Changing some columns to TEXT or BLOB may help.
In current row format, BLOB prefix of 0 bytes is stored inline.
原因：
这个错误简单理解就是单行记录的合计最大大小超过了8126字节，那么根据文档描述的话，使用dynamic行格式的表行最大大小可以达到65536字节(因为mysql内部使用了2个字节来表示字段长度，因此可以表示最大65535的长度)。那么，从65536到8126，这其中57140字节去哪里了呢？
这其实是因为mysql默认使用16k的page size，在每个data page中，除去header、footer部分，剩下的部分需要能容纳2行的内容，那么16384/2=8192就接近8126这个值了。
————————————————
解决方案：
关闭InnoDB严格模式
set global innodb_strict_mode = 0;
4. 全备导入导出
# 备份时全库导出后导入
[root@db02 ~]# mysqldump -uroot -p123 -A -R -E --triggers --master-data=2 --single-transaction > /tmp/full.sql
[root@db02 ~]# mysql -uroot -p123 < /tmp/full.sql
# 压缩导入导出
导出指定数据库备份并压缩
mysqldump -uroot -proot -h localhost -P 3306 -B my_db | gzip > my_db.sql.gz
从压缩的备份恢复数据库
gunzip < my_db.sql.gz | mysql -uroot -proot
查看change master
gunzip -c my_db.sql.gz | head -n 20
5.使用nohup导入大数据
1、新建一个 mysqlimport.sh shell脚本，脚本里写：
mysql -uroot -p*** < db1.sql
2、回到终端执行：
# 删除文件第一行
sed -i '1d' 文件名
nohup sh mysqlimport.sh &
脚本便会切换到后台执行导入，此时可以使用 `tail -f nohup.log` 查看执行日志。
# 导入指定的输出日志
>nohup.log 2>&1 &
# 避免常见报错
set global max_allowed_packet = 64*1024*1024
# 避免字符报错
--default-character-set=utf8
--hex-blob
# 避免错误表
--ignore-table=db1.tbl1 --ignore-table=db2.tbl1
3、按回车回到终端界面，输入命令 exit 退出当前终端。
'第3步很关键，一定要这样执行exit退出终端'。如果在nohup执行成功后直接点关闭程序按钮关闭终端的话，这时候会断掉该命令所对应的session，导致nohup对应的进程被通知需要一起shutdown，起不到关掉终端后调用程序继续后台运行的作用。
四、Mysqldump使用事项
mysqldump客户端可用来转储数据库或搜集数据库进行备份或将数据转移到另一个SQL服务器(不一定是一个MySQL服务器)。转储包含创建表和/或装载表的SQL语句。
如果你在服务器上进行备份，并且表均为MyISAM表，应考虑使用mysqlhotcopy，因为可以更快地进行备份和恢复。
1.调用mysqldump
有3种方式来操作:
shell> mysqldump [options] db_name [tables] shell> mysqldump [options] ---database DB1 [DB2 DB3...] shell> mysqldump [options] --all--database 如果没有指定任何表或使用了---database或--all--database选项，则转储整个数据库。
要想获得你的版本的mysqldump支持的选项，执行mysqldump ---help。
如果运行mysqldump没有--quick或--opt选项，mysqldump在转储结果前将整个结果集装入内存。如果转储大数据库可能会出现问题。该选项默认启用，但可以用--skip-opt禁用。
如果使用最新版本的mysqldump程序生成一个转储重装到很旧版本的MySQL服务器中，不应使用--opt或-e选项。
2.更多用法
mysqldump最常用于备份一个整个的数据库：
shell> mysqldump --opt db_name > backup-file.sql
你可以这样将转储文件读回到服务器：
shell> mysql db_name < backup-file.sql
或者为：
shell> mysql -e "source /path-to--backup/backup-file.sql"
db_name mysqldump也可用于从一个MySQL服务器向另一个服务器复制数据时装载数据库：
shell> mysqldump --opt db_name | mysql --host=remote_host -C
db_name 可以用一个命令转储几个数据库：
shell> mysqldump --database db_name1 [db_name2 ...] > my_databases.sql
如果你想要转储所有数据库，使用--all--database选项：
shell> mysqldump --all-databases > all_databases.sql
如果表保存在InnoDB存储引擎中，mysqldump提供了一种联机备份的途径(参见下面的命令)。该备份只需要在开始转储时对所有表进行全局读锁定(使用FLUSH TABLES WITH READ LOCK)。获得锁定后，读取二进制日志的相应内容并将锁释放。因此如果并且只有当发出FLUSH...时正执行一个长的更新语句，MySQL服务器才停止直到长语句结束，然后转储则释放锁。因此如果MySQL服务器只接收到短("短执行时间")的更新语句，即使有大量的语句，也不会注意到锁期间。
shell> mysqldump --all-databases --single-transaction > all_databases.sql
对于点对点恢复(也称为“前滚”，当你需要恢复旧的备份并重放该备份以后的更改时)，循环二进制日志或至少知道转储对应的二进制日志内容很有用：
shell> mysqldump --all-databases --master-data=2 > all_databases.sql
或
shell> mysqldump --all-databases --flush-logs --master-data=2 > all_databases.sql
如果表保存在InnoDB存储引擎中，同时使用--master-data和--single-transaction提供了一个很方便的方式来进行适合点对点恢复的联机备份。
五、Mysqldump参数
--help，-？
显示帮助消息并退出。
--add-drop--database
在每个CREATE DATABASE语句前添加DROP DATABASE语句。
--add-drop-tables
在每个CREATE TABLE语句前添加DROP TABLE语句。
--add-locking
用LOCK TABLES和UNLOCK TABLES语句引用每个表转储。重载转储文件时插入得更快。
--all--database，-A
转储所有数据库中的所有表。与使用---database选项相同，在命令行中命名所有数据库。
--allow-keywords
允许创建关键字列名。应在每个列名前面加上表名前缀。
--comments[={0|1}]
如果设置为 0，禁止转储文件中的其它信息，例如程序版本、服务器版本和主机。--skip—comments与---comments=0的结果相同。 默认值为1，即包括额外信息。
--compact
产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、--no-set-names、--skip-disable-keys和--skip-add-locking选项。
--compatible=name
产生与其它数据库系统或旧的MySQL服务器更兼容的输出。值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options或者no_field_options。要使用几个值，用逗号将它们隔开。这些值与设置服务器SQL模式的相应选项有相同的含义。
该选项不能保证同其它服务器之间的兼容性。它只启用那些目前能够使转储输出更兼容的SQL模式值。例如，--compatible=oracle 不映射Oracle类型或使用Oracle注释语法的数据类型。
--complete-insert，-c
使用包括列名的完整的INSERT语句。
--compress，-C
压缩在客户端和服务器之间发送的所有信息（如果二者均支持压缩）。
--create-option
在CREATE TABLE语句中包括所有MySQL表选项。
--database，-B
转储几个数据库。通常情况，mysqldump将命令行中的第1个名字参量看作数据库名，后面的名看作表名。使用该选项，它将所有名字参量看作数据库名。CREATE DATABASE IF NOT EXISTS db_name和USE db_name语句包含在每个新数据库前的输出中。
--debug[=debug_options]，-# [debug_options]
写调试日志。debug_options字符串通常为'd:t:o,file_name'。
--default-character-set=charset
使用charsetas默认字符集。如果没有指定，mysqldump使用默认字符集
--delayed-insert
使用INSERT DELAYED语句插入行。
--delete-master-logs
在主复制服务器上，完成转储操作后删除二进制日志。该选项自动启用--master-data。
--disable-keys，-K
对于每个表，用/*!40000 ALTER TABLE tbl_name DISABLE KEYS */;和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */;语句引用INSERT语句。这样可以更快地装载转储文件，因为在插入所有行后创建索引。该选项只适合MyISAM表。
--extended-insert，-e
使用包括几个VALUES列表的多行INSERT语法。这样使转储文件更小，重载文件时可以加速插入。
--fields-terminated-by=...，--fields-enclosed-by=...，--fields-optionally-enclosed-by=...，--fields-escaped-by=...，--行-terminated-by=...
这些选项结合-T选项使用，与LOAD DATA INFILE的相应子句有相同的含义。
--first-slave，-x
不赞成使用，现在重新命名为--lock-all-tables。
--flush-logs，-F
开始转储前刷新MySQL服务器日志文件。该选项要求RELOAD权限。请注意如果结合--all--database(或-A)选项使用该选项，根据每个转储的数据库刷新日志。
例外情况是当使用--lock-all-tables或--master-data的时候：在这种情况下，日志只刷新一次，在所有 表被锁定后刷新。如果你想要同时转储和刷新日志，应使用--flush-logs连同--lock-all-tables或--master-data。
--force，-f
在表转储过程中，即使出现SQL错误也继续。
--host=host_name，-h host_name
从给定主机的MySQL服务器转储数据。默认主机是localhost。
--hex-blob
使用十六进制符号转储二进制字符串列(例如，'abc' 变为0x616263)。影响到的列有BINARY、VARBINARY、BLOB。
--lock-all-tables，-x
所有数据库中的所有表加锁。在整体转储过程中通过全局读锁定来实现。该选项自动关闭--single-transaction和--lock-tables。
--lock-tables，-l
开始转储前锁定所有表。用READ LOCAL锁定表以允许并行插入MyISAM表。对于事务表例如InnoDB和BDB，--single-transaction是一个更好的选项，因为它不根本需要锁定表。
请注意当转储多个数据库时，--lock-tables分别为每个数据库锁定表。因此，该选项不能保证转储文件中的表在数据库之间的逻辑一致性。不同数据库表的转储状态可以完全不同。
--master-data[=value]
该选项将二进制日志的位置和文件名写入到输出中。该选项要求有RELOAD权限，并且必须启用二进制日志。如果该选项值等于1，位置和文件名被写入CHANGE MASTER语句形式的转储输出，如果你使用该SQL转储主服务器以设置从服务器，从服务器从主服务器二进制日志的正确位置开始。如果选项值等于2，CHANGE MASTER语句被写成SQL注释。如果value被省略，这是默认动作。
--master-data选项启用--lock-all-tables，除非还指定--single-transaction(在这种情况下，只在刚开始转储时短时间获得全局读锁定。
又见--single-transaction。在任何一种情况下，日志相关动作发生在转储时。该选项自动关闭--lock-tables。
--no-create-db，-n
该选项禁用CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name语句，如果给出---database或--all--database选项，则包含到输出中。
--no-create-info，-t
不写重新创建每个转储表的CREATE TABLE语句。
--no-data，-d
不写表的任何行信息。如果你只想转储表的结构这很有用。
--opt
该选项是速记；等同于指定 --add-drop-tables--add-locking --create-option --disable-keys--extended-insert --lock-tables --quick --set-charset。它可以给出很快的转储操作并产生一个可以很快装入MySQL服务器的转储文件。该选项默认开启，但可以用--skip-opt禁用。要想只禁用确信用-opt启用的选项，使用--skip形式；例如，--skip-add-drop-tables或--skip-quick。
--password[=password]，-p[password]
连接服务器时使用的密码。如果你使用短选项形式(-p)，不能在选项和密码之间有一个空格。如果在命令行中，忽略了--password或-p选项后面的 密码值，将提示你输入一个。
--port=port_num，-P port_num
用于连接的TCP/IP端口号。
--protocol={TCP | SOCKET | PIPE | MEMORY}
使用的连接协议。
--quick，-q
该选项用于转储大的表。它强制mysqldump从服务器一次一行地检索表中的行而不是检索所有行并在输出前将它缓存到内存中。
--quote-names，-Q
用`字符引用数据库、表和列名。如果服务器SQL模式包括ANSI_QUOTES选项，用"字符引用名"。默认启用该选项。可以用--skip-quote-names禁用，但该选项应跟在其它选项后面，例如可以启用--quote-names的--compatible。
--result-file=file，-r file
将输出转向给定的文件。该选项应用在Windows中，因为它禁止将新行‘\n’字符转换为‘\r\n’回车、返回/新行序列。
--routines，-R
在转储的数据库中转储存储程序(函数和程序)。使用---routines产生的输出包含CREATE PROCEDURE和CREATE FUNCTION语句以重新创建子程序。
但是，这些语句不包括属性，例如子程序定义者或创建和修改时间戳。这说明当重载子程序时，对它们进行创建时定义者应设置为重载用户，时间戳等于重载时间。
如果你需要创建的子程序使用原来的定义者和时间戳属性，不使用--routines。
相反，使用一个具有mysql数据库相应权限的MySQL账户直接转储和重载mysql.proc表的内容。
该选项在MySQL 5.1.2中添加进来。在此之前，存储程序不转储。
--set-charset
将SET NAMES default_character_set加到输出中。该选项默认启用。要想禁用SET NAMES语句，使用--skip-set-charset。
--single-transaction
该选项从服务器转储数据之前发出一个BEGIN SQL语句。它只适用于事务表，例如InnoDB和BDB，因为然后它将在发出BEGIN而没有阻塞任何应用程序时转储一致的数据库状态。
当使用该选项时，应记住只有InnoDB表能以一致的状态被转储。例如，使用该选项时任何转储的MyISAM或HEAP表仍然可以更改状态。
--single-transaction选项和--lock-tables选项是互斥的，因为LOCK TABLES会使任何挂起的事务隐含提交。
要想转储大的表，应结合--quick使用该选项。
--socket=path，-S path
当连接localhost(为默认主机)时使用的套接字文件。
--skip--comments
参见---comments选项的描述。
--tab=path，-T path
产生tab分割的数据文件。对于每个转储的表，mysqldump创建一个包含创建表的CREATE TABLE语句的tbl_name.sql文件，和一个包含其数据的tbl_name.txt文件。选项值为写入文件的目录。
默认情况，.txt数据文件的格式是在列值和每行后面的新行之间使用tab字符。可以使用--fields-xxx和--行--xxx选项明显指定格式。
注释：该选项只适用于mysqldump与mysqld服务器在同一台机器上运行时。你必须具有FILE权限，并且服务器必须有在你指定的目录中有写文件的许可。
--tables
覆盖---database或-B选项。选项后面的所有参量被看作表名。
--triggers
为每个转储的表转储触发器。该选项默认启用；用--skip-triggers禁用它。
--tz-utc
在转储文件中加入SET TIME_ZONE='+00:00'以便TIMESTAMP列可以在具有不同时区的服务器之间转储和重载。(不使用该选项，TIMESTAMP列在具有本地时区的源服务器和目的服务器之间转储和重载）。--tz-utc也可以保护由于夏令时带来的更改。--tz-utc默认启用。要想禁用它，使用--skip-tz-utc。该选项在MySQL 5.1.2中加入。
--user=user_name，-u user_name
连接服务器时使用的MySQL用户名。
--verbose，-v
冗长模式。打印出程序操作的详细信息。
--version，-V
显示版本信息并退出。
--where='where-condition', -w 'where-condition'
只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。
例如：
"--where=user='jimf'" "-wuserid>1" "-wuserid<1"
--xml，-X
将转储输出写成XML。
还可以使用--var_name=value选项设置下面的变量：
· max_allowed_packet
客户端/服务器之间通信的缓存区的最大大小。最大为1GB。
· net_buffer_length
客户端/服务器之间通信的缓存区的初始大小。当创建多行插入语句时(如同使用选项--extended-insert或--opt)，
mysqldump创建长度达net_buffer_length的行。如果增加该变量，还应确保在MySQL服务器中的net_buffer_length变量至少这么大。
还可以使用--set-variable=var_name=value或-O var_name=value语法设置变量。然而，现在不赞成使用该语法。
六、企业案例
1.背景
1.正在运行的网站系统，MySQL数据库，数据量25G，日业务增量10-15M。
2.备份策略：每天23：00，计划任务调用mysqldump执行全备脚本
3.故障时间点：上午10点开发人员误删除一个核心业务表，如何恢复？
2.思路
1.停库，避免二次伤害
2.创建新库
3.倒入前一天的全备
4.通过binlog找到前一天23：00到第二天10点之间的数据
5.导入找到的新数据
6.恢复业务
a.直接使用临时库顶替原生产库，前端应用割接到新库（数据量特别大的时候）
b.将误删除的表单独导出，然后导入到原生产环境（数据量小的时候）
3.模拟案例
- 1）模拟生产数据
mysql> create database dump;
mysql> use dump
mysql> create table dump(id int);
mysql> insert dump values(1),(2),(3),(4);
mysql> select * from dump;
- 2）模拟23:00全备
[root@db03 mysql]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction > /tmp/full.sql
- 3）模拟23：00到10:00的数据操作
mysql> use dump
mysql> insert dump values(1000),(2000),(3000),(4000);
- 4）模拟删库
mysql> drop database dump;
Query OK, 1 row affected (0.00 sec)
4.恢复数据
- 1）停库，避免二次伤害
[root@db03 mysql]# systemctl stop mysqld
- 2）创建新库
-
3）导入前一天的全备
#老库将数据传输到新库
[root@db03 ~]# scp /tmp/full.sql 172.16.1.52:/tmp/
#新库导入全备数据
[root@db02 ~]# mysql < /tmp/full.sql
- 4）通过binlog找到前一天23：00到第二天10点之间的数据
1.找到binlog的起始位置点
[root@db03 data]# head -22 /tmp/full.sql | tail -1
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=971790;
2.找到结束位置点
[root@db03 data]# mysqlbinlog mysql-bin.000006 > 1.txt
[root@db03 data]# vim 1.txt
过滤 drop database dump，取出命令上面的位置点
# at 972068
......
drop database dump
3.取出位置点之间的数据
[root@db03 data]# mysqlbinlog -d dump --start-position=971790 --stop-position=972068 mysql-bin.000006 > /tmp/new.sql
- 5）导入找到的新数据
#老库将binlog数据传到新库
[root@db03 ~]# scp /tmp/new.sql 172.16.1.52:/tmp/
#新库导入新的数据
[root@db02 ~]# mysql < /tmp/new.sql
- 6）确认数据
mysql> use dump
mysql> show tables;
+----------------+
| Tables_in_dump |
+----------------+
| dump |
+----------------+
mysql> select * from dump;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 1000 |
| 2000 |
| 3000 |
| 4000 |
+------+
- 7）恢复业务
1.直接使用临时库顶替原生产库，前端应用割接到新库（数据量特别大的时候）
2.将误删除的表单独导出，然后导入到原生产环境（数据量小的时候）
1）新库导出指定业务库
[root@db02 ~]# mysqldump dump > /tmp/dump.sql
2）新库将数据推送回老库
[root@db02 ~]# scp /tmp/dump.sql 172.16.1.53:/tmp
3）将恢复的数据导入老库
mysql> create database dump;
mysql> use dump;
mysql> source /tmp/dump.sql
七、MySQL定期备份策略
MySQL定期备份是一项重要的工作，但人工操作太繁琐，也难避免有所疏漏，使用下面的方法即可让系统定期备份数据。
1.定期备份测试
◆1、创建备份文件夹
cd /www
mkdir mysqlbackup
◆2、编写运行脚本
vim autobackup
--------------
filename=`date +%Y%m%d`
mysql_bin_dir/mysqldump –opt dataname -u用户 -p密码 | gzip > /www/mysqlbackup/name$filename.gz
---------------
说明：
(1)mysql_bin_dir：mysql的bin路径；
(2)dataname：数据库名；
(3)name：自定义备份文件前缀标识。
如上例，将自动备份mysql数据库，并以gzip压缩方式存储，文件名为name20080101.gz的形式。
◆3、为脚本添加执行权限
chmod +x autobackup
◆4、让crontab来完成定期执行的任务
编辑crontab：
vim /etc/crontab
在最后一行中加入：
01 5 * * * root /www/autobackup
每天5点运行脚本，也可以修改5为其他指定时间。
（Redhat的方法会不一样）Redhat方法：
Redhat的crontab采用按时间调用4个目录
(/etc/cron.hourly：每小时；
/etc/cron.daily：每天；
/etc/cron.weekly：每周；
/etc/cron.monthly：每月)
中脚本出来运行的方式。
Redhat中只需要将刚才编辑的脚本复制到相应的目录即可。
◆5、重启crontab
/etc/rc.d/init.d/crond restart
2.备份方案
详细说明下：
1.MySQLdump增量备份配置
执行增量备份的前提条件是MySQL打开binlog日志功能，在my.cnf中加入
log-bin=/opt/Data/MySQL-bin
“log-bin=”后的字符串为日志记载目录，一般建议放在不同于MySQL数据目录的磁盘上。
mysqldump > 导出数据
mysql < 导入数据 （或者使用source命令导入数据，导入前要先切换到对应库下）
# 注意一个细节：
若是mysqldump导出一个库的数据，导出文件为a.sql，然后mysql导入这个数据到新的空库下。
如果新库名和老库名不一致，那么需要将a.sql文件里的老库名改为新库名，
这样才能顺利使用mysql命令导入数据（如果使用source命令导入就不需要修改a.sql文件了）。
3.MySQLdump增量备份
mysqldump增量备份只能增量备份binlog日志内容用于日后恢复.
假定星期日下午1点对test库执行增量备份:
1. 适用于MyISAM存储引擎:
[root@test-huanqiu ~]# MySQLdump --lock-all-tables --flush-logs --master-data=2 -u root -p123 test > backup_sunday_1_PM.sql
2. 对于InnoDB将--lock-all-tables替换为--single-transaction
--flush-logs为结束当前日志，生成新日志文件；
[root@test-huanqiu ~]# MySQLdump --single-transaction --flush-logs --master-data=2 -u root -p123 test > backup_sunday_1_PM.sql
3.恢复增量部分备份
"(数据内容覆盖方式)"
[root@db01 ~]# mysql -u root -p < backup_sunday_1_PM.sql
"使用binlog日志方式恢复(语句内容执行方式,会增加和删除.)"
[root@db01 ~]# mysqlbinlog mysql-bin.000012 |mysql -uroot -p123
八、mysqldump完整备份方案及脚本
- 全量备份
- mysqlbinlog二进制日志增量备份
1.注意事项
1）从mysqldump备份文件恢复数据会丢失掉从备份点开始的更新数据，所以还需要结合mysqlbinlog二进制日志增量备份。
首先确保已开启binlog日志功能。在my.cnf中包含下面的配置以启用二进制日志：
[mysqld]
log-bin=mysql-bin
2）mysqldump命令必须带上--flush-logs选项以生成新的二进制日志文件：
mysqldump --single-transaction --flush-logs --master-data=2 > backup.sql
2.mysqldump全量和增量备份脚本
应用场景：
1）增量备份在周一到周六凌晨3点，会复制mysql-bin.00000到指定目录；
2）全量备份则使用mysqldump将所有的数据库导出，每周日凌晨3点执行，并会删除上周留下的mysq-bin.00000，然后对mysql的备份操作会保留在bak.log文件中。
1.）脚本实现：
1）全量备份脚本（假设mysql登录密码为123456；注意脚本中的命令路径）：
[root@test-huanqiu ~]# vim /root/Mysql-FullyBak.sh
#!/bin/bash
# Program
# use mysqldump to Fully backup mysql data per week!
# History
# Path
BakDir=/home/mysql/backup
LogFile=/home/mysql/backup/bak.log
Date=`date +%Y%m%d`
Begin=`date +"%Y年%m月%d日 %H:%M:%S"`
cd BakDir
DumpFile=Date.sql
GZDumpFile=Date.sql.tgz
/usr/local/mysql/bin/mysqldump -uroot -p123456 --quick --events --all-databases --flush-logs --delete-master-logs --single-transaction>DumpFile
/bin/tar -zvcf GZDumpFileDumpFile
/bin/rm DumpFile
Last=`date +"%Y年%m月%d日 %H:%M:%S"`
echo 开始:Begin 结束:LastGZDumpFile succ >> LogFile
cdBakDir/daily
/bin/rm -f *
2）增量备份脚本（脚本中mysql的数据存放路径是/home/mysql/data，具体根据自己的实际情况进行调整）
[root@test-huanqiu ~]# vim /root/Mysql-DailyBak.sh
#!/bin/bash
# Program
# use cp to backup mysql data everyday!
# History
# Path
BakDir=/home/mysql/backup/daily //增量备份时复制mysql-bin.00000*的目标目录，提前手动创建这个目录
BinDir=/home/mysql/data //mysql的数据目录
LogFile=/home/mysql/backup/bak.log
BinFile=/home/mysql/data/mysql-bin.index //mysql的index文件路径，放在数据目录下的
/usr/local/mysql/bin/mysqladmin -uroot -p123456 flush-logs
#这个是用于产生新的mysql-bin.00000*文件
Counter=`wc -l BinFile |awk '{print1}'`
NextNum=0
#这个for循环用于比对Counter,NextNum这两个值来确定文件是不是存在或最新的
for file in `cat BinFile`
do
base=`basenamefile`
#basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./
NextNum=`expr NextNum + 1`
if [NextNum -eq Counter ]
then
echobase skip! >> LogFile
else
dest=BakDir/base
if(test -edest)
#test -e用于检测目标文件是否存在，存在就写exist!到LogFile去
then
echobase exist! >> LogFile
else
cpBinDir/baseBakDir
echo base copying >>LogFile
fi
fi
done
echo `date +"%Y年%m月%d日 %H:%M:%S"` Next Bakup succ! >>LogFile
2.）设置crontab
设置crontab任务，执行备份脚本。先执行的是增量备份脚本，然后执行的是全量备份脚本：
[root@test-huanqiu ~]# crontab -e
#每个星期日凌晨3:00执行完全备份脚本
0 3 * * 0 /bin/bash -x /root/Mysql-FullyBak.sh >/dev/null 2>&1
#周一到周六凌晨3:00做增量备份
0 3 * * 1-6 /bin/bash -x /root/Mysql-DailyBak.sh >/dev/null 2>&1
3.）手动测试
手动执行上面两个脚本，测试下备份效果
[root@test-huanqiu backup]# pwd
/home/mysql/backup
[root@test-huanqiu backup]# mkdir daily
[root@test-huanqiu backup]# ll
total 4
drwxr-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/Mysql-DailyBak.sh
[root@test-huanqiu backup]# ll
total 8
-rw-r--r--. 1 root root 121 Nov 29 11:29 bak.log
drwxr-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 bak.log
mysql-binlog.000030 copying
mysql-binlog.000031 copying
mysql-binlog.000032 skip!
2016年11月29日 11:29:32 Bakup succ!
然后执行全量备份脚本
[root@test-huanqiu backup]# sh /root/Mysql-FullyBak.sh
20161129.sql
[root@test-huanqiu backup]# ll
total 152
-rw-r--r--. 1 root root 145742 Nov 29 11:30 20161129.sql.tgz
-rw-r--r--. 1 root root 211 Nov 29 11:30 bak.log
drwxr-xr-x. 2 root root 4096 Nov 29 11:30 daily
[root@test-huanqiu backup]# ll daily/
total 0
[root@test-huanqiu backup]# cat bak.log
mysql-binlog.000030 copying
mysql-binlog.000031 copying
mysql-binlog.000032 skip!
2016年11月29日 11:29:32 Bakup succ!
开始:2016年11月29日 11:30:38 结束:2016年11月29日 11:30:38 20161129.sql.tgz succ
