MySQL备份恢复与日志

释放双眼，带上耳机，听听看~！

🤖 由 ChatGPT 生成的文章摘要

MySQL 数据库的备份与恢复

1.1 备份数据的意义

第一是保护公司的数据，第二是网站7*24小时提供服务

1.2 备份单个数据库参数使用

MySQL数据库自带了一个很好用的备份命令，就是mysqldump，它的基本使用如下；

语法：mysqldump -u 用户名 -p 数据库名 > 备份的文件名

mysqldump -uroot -p123456 -S /data/3306/mysql.sock oldboy >/opt/oldboy_$(date +%F).sql

我们可以使用grep 查看我们都备份了些什么东西

[root@db02 oldboy]# egrep -v "#|\*|--|^$" /opt/oldboy_2016-06-22.sql
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  `dept` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`),
  KEY `index_age` (`name`(8)),
  KEY `ind_name_dept` (`name`,`dept`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `student` WRITE;
UNLOCK TABLES;
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka');
UNLOCK TABLES;

小结：mysqldump就是备份了我们的sql语句

mysqldump常用参数说明：

参数

说明

-A

备份所有数据库

-B

增加创建数据库和连接数据库的语句

-t

只备份数据

-d

只备份库表结构

-T

分离库表和数据成不同的文件，数据是文本，非SQL语句

-x

锁表禁止写入数据

-F

刷新binlog日志，生成新的文件，将来增量恢复使用

我们这里只是简单的介绍了一下mysqldump的参数，更多参数可以参考本站的mysqldump参数介绍

1.3 常用命令介绍

-B

使用-B 参数

mysqldump
-uroot -p123456 -S /data/3306/mysql.sock -B oldboy >/opt/oldboy_B_$(date
+%F).sql

不使用-B 参数

[root@db02
oldboy]# mysql -uroot -p123456 -S /data/3306/mysql.sock
</opt/oldboy_2016-06-22.sql

小结：如果不加-B 恢复的时候需要提前创建好数据库并指定

--compact

测试时用的比较多，可以优化输出内容的大小，让容量更少，适合调试

参数说明：该选项使得输出内容更简洁，不包括默认选项中各种注释，有如下几个参数。--skip-add-drop-table --no-set-names
--skip-disable-keys --skip-add-locks

语法如下：

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B oldboy --compact >/opt/123.sql

gzip

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B oldboy|gzip>/opt/123.sql.gz
-rw-r--r--  1 root root  899 Jun 22 17:34 123.sql.gz
-rw-r--r--  1 root root 1014 Jun 22 17:27 123.sql

使用gzip压缩打包文件效率是不压缩的3倍

以上参数小结

备份数据使用-B参数，会在备份数据中增加建库的语句。
备份数据使用-B参数，后面可以直接接多个库名
用gzip对备份的数据压缩，可以提高效率
debug时可以用--compact减少输出，但不用于生产
指定字符集备份用--default-character-set=latin1（一般不用此字符集）

mysqldump的工作原理？

利用mysqldump命令备份数据的过程，实际上就是把数据从mysql库里以逻辑的sql语句的形式直接输出或生产备份文件的过程

备份多个库语法如下：

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B oldboy wordpress >/opt/123.sql
提示：使用mysqldump是把数据库的数据导出通过sql语句的形式存储，这种备份方式成称为逻辑备份，效率不是很高，一般50G以内的数据。
其他的备份方式：物理备份cp、tar（停库），xtrabackup

1.4 如果做分库分表

分库备份实际上就是执行一个备份语句备份一个库，如果数据库里有多个库，就执行多条相同的备份单个的备份语句就可以备份多个库了。注意每个库都可以对应备份的库作为库名，结尾加sql备份多个库的命令如下：

mysqldump -uroot -p123456 -B oldboy
mysqldump -uroot -p123456 -B oldboy_gbk

分库的备份语法：

先取出我们要备份的库

[root@db02 oldboy]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show databases;"|egrep -v "Database|_schema|mysql"
cyh
oldboy
oldboy123
oldboy_gbk
wordpress

执行命令，进行批量备份

mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show databases;"|egrep -v "Database|_schema|mysql"|sed -r 's#^(.*)#mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B \1 >/tmp/\1.sql.gz#g'|bash

查看最后结果

[root@db02 tmp]# ll
total 20
-rw-r--r-- 1 root root    0 Jun 21 11:55 bak.sql
-rw-r--r-- 1 root root 1382 Jun 22 17:55 cyh.sql.gz
-rw-r--r-- 1 root root 1406 Jun 22 17:55 oldboy123.sql.gz
-rw-r--r-- 1 root root 1409 Jun 22 17:55 oldboy_gbk.sql.gz
-rw-r--r-- 1 root root 2808 Jun 22 17:55 oldboy.sql.gz
-rw-r--r-- 1 root root 1406 Jun 22 17:55 wordpress.sql.gz

提示：可以使用for循环进行备份

备份单个表

mysqldump -uroot -p123456 -S /data/3306/mysql.sock oldboy test >/opt/test.sql

备份多个表

mysqldump -uroot -p123456 -S /data/3306/mysql.sock oldboy test cyh>/opt/test.sql

[提示]只要不加-B 前面是库，后面的都是表

只显示表结构

mysqldump -uroot -p123456 -S /data/3306/mysql.sock oldboy test  -d >/opt/test.sql

直接备份数据 -t参数

mysqldump -uroot -p123456 -S /data/3306/mysql.sock oldboy test  -t>/opt/test.sql

备份数据库表结构（不包含数据）

利用mysqldump -d 参数只备份表的结构，例：备份oldboy所有表结构

mysqldump -uroot -p123546 -B -d oldboy >/opt/t.sql

语句和数据分离，数据为文本 -T -tab=name

mysqldump -uroot -p123456 oldboy student –compact –tab=/tmp/
cat /tmp/student.txt
oldboy
oldgirl
inca
 zuma

刷新binlog

mysqldump用于定时对某一时刻的数据的全备，例如：00点进行备份back.sql.gz

增量备份：当有数据写入到数据库时，还会同时把更新的SQL语句写入到对应的文件里这个就叫做binlog文件

mysqldump -uroot -p123456 -S /data/3306/mysql.sock oldboy test -F --compact
-F 在/data/3306就会记录mysql-bin.000005 按照循序设置，刷新binglog日志，在备份之后立刻刷新binlog日志

刷新binlog介绍

binlog是记录数据库更新的SQL语句，二进制文件
mysqldump永不定时对某一时刻数据的全备，例如：00点进行备份bak.sql.gz
增量备份：当有数据写入到数据库时，还会同时把更新的SQL语句写入到对应的文件里
这个文件就叫做binlog文件

例子：10点丢失数据需要恢复数据；

00点时刻备份的bak.sql.gz 数据还原到数据库，这个时候恢复到了00点
00点-10：00数据，就要从binlog里恢复

binlog文件生效需要有一个参数：binlog

binlog日志切割：确定全备和增量的临界点

-F 刷新binlog日志，生成新文件，将来恢复从这个文件开始。
--maser-data在备份语句里添加CHANGE MASTER语句及binlog文件位置点信息
值为1=可执行的CHANGE MASTER语句
值为2=注释的--CHANGE MASTER语句
--master-data除了增量恢复确定临界点外，做主从复制时作用更大。

1.5 生产场景不同引擎mysqldump备份命令

MyISAM引擎企业生产备份命令（适合所有引擎和混合引擎）

mysqldump -uroot -poldboy123 -A -B -F -R --master-data=2 -x --events|gzip >/opt/all.sql.gz
提示：-F 也可以不用，与--master-data有些重复

innoDB引擎企业生产备份命令；推荐使用的

mysqldump -uroot —poldboy123 -A -B -F -R --master-data=2 --events --single-transaction|gzip >/opt/all.sql.gz
提示:-F 也可以不用，与--master-data

额外补充：

（一）mysqldump逻辑备份说明

缺点：效率不是特别高。

优点：简单、方便、可靠、迁移

适用于数据量不是特别大的场景，打包前50G以内数据

（二）超过50G可选方案

1、xtrabackup物理备份工具：全量和增量（支持热备）

2、物理备份方案：从库停止SQL线程，打包，cp

数据库恢复事项

提示：

1.数据恢复和字符集关联很大，如果字符集不正确会导致恢复的数据乱码

2.mysql命令以及source命令恢复数据库的原理就是把文件的sql语句，在数据库重新执行的过程

小技巧

Mysql>system ls -l /opt/  可以跳出mysql查看命令
Mysql>source /opt/oldboy_B.sql  直接接路径就可以恢复
source数据恢复和字符集关联很大，如果字符集不正确会导致恢复的数据乱码
UTF8数据库，那么恢复的文件格式需要为"UTF8-没有签名"格式，txt右击另存为设置

1.6 MySQL恢复命令

针对压缩的备份数据恢复；

1、方法

gzip -d /opt/mysql.sql.gz
mysql -uroot -poldboy </opt/mysql.sql
不删除源备份文件：
gzip -cd 01.sql.gz >2.sql

2、方法

gunzip<b_bak.sql.gz>/opt/mysql.sql
mysql -uroot -poldboy </opt/mysql.sql
或者
gunzip <b_bak.sql.gz|mysql -uroot -poldboy123

恢复总结：

1、source命令

2、mysql命令

1.7 实现和MySQL非交互式对话

1.7.1 利用mysql -e 参数查看mysql数据

[root@www ~]# mysql -uroot -p -e "show databases;"
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbs                |
| mysql              |
| performance_schema |
| test               |
| wordpress          |
+--------------------+

查看完整的线程状态，此参数才查看慢查询语句是非常有用

[root@www ~]# mysql -uroot -p -e "show full processlist;"
Enter password: 
+-----+------+-----------+------+---------+------+-------+-----------------------+
| Id  | User | Host      | db   | Command | Time | State | Info                  |
+-----+------+-----------+------+---------+------+-------+-----------------------+
| 797 | root | localhost | NULL | Query   |    0 | NULL  | show full processlist |
+-----+------+-----------+------+---------+------+-------+-----------------------+

企业案例：mysql sleep线程过多的问题案例

mysql> show processlist;
mysql>kill 89;  可以使用kill 杀死进程

=============================================

mysql sleep线程过多问题

在配置文件修改：

[mysqld]
interactive_timeout = 120  <==此参数设置后wait_timout自动失效
wait_timeout = 120

其他方法：

1、PHP程序中，不使用持续连接，即使用mysql_connect而不是pconnect（JAVA调整连接池）

2、PHP程序执行完毕，应该显示调用mysql_close

3、逐渐分析MySQL的SQL查询及慢查询日志，找到查询过慢的SQL，优化

1.8 不重启数据库修改数据库参数

不重启数据库修改数据库参数，但是要求重启后还能生效

shell>mysql -uroot -poldboy -e "show variables;"|grep key_buffer
key_buffer_size 16384
shell>mysql -uroot -poldboy -e "set global key_buffer_size = 1024*32;"
shell>mysql -uroot -poldboy -e "show variables;"|grep key_buffer
key_buffer_size 32768
shell>sed -n '32p' /etc/my.cnf
key_buffer_size = 16K
shell>sed -i 's#key_buffer_size = 16K#key_buffer_size = 32K#g' /etc/my.cnf

查看数据库参数是否生效

mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show variables\G"
[root@db02 opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show variables like '%server_id%';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+

查看my配置文件有没有在数据库中生效

[root@db02 opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show variables like '%log_bin%';"
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
[root@db02 opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show variables like '%slow_%';"
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| log_slow_queries    | OFF                           |
| slow_launch_time    | 2                             |
| slow_query_log      | OFF                           |
| slow_query_log_file | /data/3306/data/db02-slow.log |
+---------------------+-------------------------------+

索引缓冲区

[root@db02 opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show variables like '%key_buffer%';"
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 16777216 |
+-----------------+----------+

修改索引缓冲区的大小

set global key_buffer_size = 1024*1024*32;
root@oldboy 11:03:59->show variables like '%key_buffer%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 33554432 |
+-----------------+----------+
1 row in set (0.00 sec)

不重启数据库更改数据库参数小结：

1、set global key_buffer_size = 1024*1024*32； #<==及时生效，重启mysql失效

2、配置文件也要改，编辑/etc/my.cnf，修改key_buffer_size = 32M

1.9 生产场景通常重要命令小结

show processlist;  #查看数据库正在执行的SQL语句，可能无法看全完整SQL语句
show full prcesslist #查看正在执行的完整SQL语句，完整显示
set global key_buffer_size = 1024*1024*32  #不重启数据库调整数据库参数，直接生效，重启后失效、
show variables;  #查看数据库的配置参数信息，例如：my.cnf里参数的生效情况
kill ID     #杀掉SQL线程的命令，ID为线程号
show session status  #查看当前会话的数据库状态信息
show global status;  #查看整个数据库运行状态信息，很重要。要分析并做好监控
show status;    #mysql运行状态
show engine innodb status； #显示InnoDB 引擎的性能状态（早起版本show innodb status）

查看select进行的查看次数

root@oldboy 11:22:46->show global status like '%select%';      
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Com_insert_select      | 0     |
| Com_replace_select     | 0     |
| Com_select             | 264   |            
| Select_full_join       | 0     |
| Select_full_range_join | 0     |
| Select_range           | 7     |
| Select_range_check     | 0     |
| Select_scan            | 287   |
+------------------------+-------+
8 rows in set (0.00 sec)

查看查询和写入的次数

[root@db02 opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show status ;"|grep -E "select|insert"
Com_insert     0
Com_insert_select  0
Com_replace_select       0
Com_select     1
Delayed_insert_threads 0
Innodb_rows_inserted   117
Qcache_inserts      58

过滤出innodb引擎的参数

[root@db02 opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show status ;"|grep -E "select|insert"
Com_insert     0
Com_insert_select  0
Com_replace_select       0
Com_select     1
Delayed_insert_threads 0
Innodb_rows_inserted   117
Qcache_inserts      58

查看引擎innodb状态

show gengine innodb status\G

MySQL工具mysqlbinlog

2.1 mysql的binlog日志是什么？

mysql数据目录下的如下文件就是mysql的binlog日志

多实例路径：/data/3306/
mysql-bin.00001
mysql-bin.00002
mysql-bin.00003
mysql-bin.00004
mysql-bin.00005

需要在配置文件中开启次功能

grep log-bin my.cnf
log-bin = /data/3306/mysql-bin

主要作用：用于数据库的主从复制以及数据的增量恢复

2.2 mysqlbinlog工具解析binlog日志实践

默认情况binlog日志是二进制格式的，不能使用查看文本工具的命令来查看，例如：cat vi vim

2.3 解析指定库的binlog日志

范例：利用mysqldump -d参数解析指定库的binlog日志

shell>mysqlbinglog -d oldboy mysql-bin.00001 -r oldboy.sql
提示：-r代表>输出内容 -d 指定库文件

mysql-bin.000001内容介绍

# at 2154    
#160630  7:45:07 server id 1  end_log_pos 2253 	Query	thread_id=11	exec_time=0	error_code=0
SET TIMESTAMP=1467243907/*!*/;
create database qqqqqqq41233
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

按照位置截取：确定

# at 2154
#160630  7:45:07
mysqlbinlog mysql-bin.000001 --start-position=2156 --stop-position=3000 -r pos.sql
其中，--start-posistion可以在配置文件中不存在，那时候取值将会取最接近2156的值

按照时间截图：模糊，不准

mysqlbinlog mysql-bin.00001 --start-datetime='2016-10-16 17:14:15' --stop-datetime='2014-10-16 17:15:15' -r time.sql
指定开始时间，不指定结束时间，将从开始时间一直到最后
mysqlbinlog mysql-bin.000001 --start-datetime='2016-10-16 17:14:15' -r time.sql
指定结束时间，不指定开始时间，将从开始一直到指定的结束时间
mysqlbinlog mysql-bin.000002 --stop-datetime='2016-10-16 17:14:15' -r time.sql

mysqlbinlog命令

1、把binlog解析为sql语句（包含位置和时间点）

2、-d参数genuine指定库拆分binlog（拆分单表binlog可通过SQL关键字过滤）

3、通过位置参数获取部分binlog： --start-position=301 --stop-position=305。精准定位取部分内容

4、通过时间参数截取部分binlog，--start-datetime='2016-10-16 17:14:15'

--stop-datetime='2016-10-16 17:17:17'，模糊取部分内容，会丢失数据。

5、-r 文件名，相当于">文件名"

6、解析ROW级别binlog日志的方法。

mysqlbinlog --base64-ouput=decode-rows-v mysql-bin.00001
mysqlbinlog --base64-ouput="decode-rows" --verbose-mysql-bin.00001

2.3 错误日志（error log）介绍与调整

1.错误日志（error log）介绍

MySQL的错误日志（error log）记录MySQL服务进程mysqld在启动/关闭或运行过程中遇到的错误信息

2.错误日志（error log）实践

法1：在配置文件中调整方法，当然可以在启动时加入启动参数

[mysqld_safe]
log-error=/data/3306/mysql_oldboy3306.err

法2：启动MySQL命令里加入：

mysqld_safe --default-file=/data/3306/my.cnf --log-error=/data/3306/mysql_oldboy.err &
root@oldboy 10:09:14->show variables like '%log_error%';
+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| log_error     | /data/3306/mysql_oldboy3306.err |
+---------------+---------------------------------+
1 row in set (0.04 sec)

新装MySQL服务，无法启动排查思路

1.把/data/3306/data 目录删除后重新建立data目录并加属主属组

2.把原有的my.cnf，mysql这两个也重新还原过

3.重新用mysqld_safe启动过

4.改过my.cnf里面server-id = 2

5.配置文件里面的端口，思路

步骤----------------------------------

1、先把日志文件备份并清空启动一下mysql服务然后再查看日志文件报有什么错误？

[root@db02 3306]# cat mysql_oldboy3306.err
160616 16:46:07 mysqld_safe Starting mysqld daemon with databases from /data/3306/data

2、然后mysql3306目录下面所有文件都加上属主并是递归-R

[root@db02 3306]# chown -R mysql *

3、然后查看一下有没有mysql 这个ID

[root@db02 3306]# id mysql
uid=502(mysql) gid=502(mysql) groups=502(mysql)

4、重新启动mysql服务（多实例）

[root@db02 3306]#/data/3306/mysql start

5、此时再查看一下服务有没有启动起来

[root@db02 3306]#lsof -i:3306

2.4 查询日志（也可以叫做访问日志）

[root@db02 ~]grep gene /data/3306/my.cnf
general_log = on
general_log_file = /data/3306/data/MySQL_oldboy.log

会记录mysql所有的操作

提示：高并发场景企业里普通查询日志一般是关闭的（默认也是关闭）主要因为IO性能问题

2.5 慢查询日志（slow query log ）介绍与调整*****

1.慢查询日志（slow query log）介绍

慢查询日志（slow query log）；记录执行时间超出指定值（long_query_time）的SQL语句

2.慢查询日志（slow query log）调整

long_query_time=超过指定时间查询（看公司要求，通常2秒）
log-slow-queries= 日志路径
log_queries_not_using_indexes=不使用索引

慢查询的设置，对于数据库SQL的优化非常重要

[root@db02 oldboy]# egrep "quer" /data/3306/my.cnf|tail -3
long_query_time = 1
log-slow-queries = /data/3306/slow.log
log_queries_not_using_indexes

2.6 慢查询日志切割

shell>cat /server/scripts/cut_slow_log.sh
cd /data/3306/ &&\
/bin/mv slow.log slow.log.$(date +%F)&&\
mysqladmin -uroot -poldboy123 -S /data/3306/mysql.sock flush-log
shell>tail -2 /var/spool/cron/root
#cut mysql slow log
00 00 * * * /bin/sh /server/scripts/cur_slow_log.sh >/dev/null 2>&1

使用explain优化SQL语句（select语句）的基础流程；

1.抓慢查询SQL语句方法：

a. show full prcesslist;(登录数据库现场抓，连续执行2次，超过2秒)

mysql -uroot -poldboy -S /data/3306/mysql.sock -e "show full processlist;"|egrep -vi "sleep"

b.分析慢查询日志

配置参数记录慢查询语句

2.explain语句检查索引执行情况

大表不能高峰期建立索引，300万条记录

3.分析慢查询的工具mysqlsla（每天早上发邮件）

切割慢查询日志，去重分析后发给领导及相关部门

1）mv，reload进程。2）cp，>清空

shell>mv /data/3306/slow.log /opt/$(date +%F)_slow.log
shell>mysqladmin -uroot -poldboy -S /data/3306/mysql.sock flush-logs
mysqlsla分析：http://blog.itpub.net/7607759/viewspace-692828

2.7 二进制日志（binary log）介绍与调整

1、二进制日志（binary log）介绍

二进制日志（binary log）记录数据表被修改的相关信息；

2.二进制日志（binary log）调整

root@oldboy 11:13:37->show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin      记录binlog开关      | ON    |
| log_bin_trust_function_creators | OFF   |
| sql_log_bin 临时不记录binlog开关（增量恢复）| ON 
+---------------------------------+-------+
3 rows in set (0.01 sec)

可以在配置文件中设置

[root@db02 3306]# grep log-bin /data/3306/my.cnf
log-bin = /data/3306/mysql-bin

二进制日志log-bin作用；

1、以二进制形式记录更改数据库的SQL语句。

2、用于主从复制。

3、增量数据备份及恢复

临时不记录binlog（增量恢复）

mysql>set session sql_log_bin = OFF

mysql>show variables like '%log_bin%';

2.8 增量备份

增量数据是从上次全量备份之后，更新的新数据。对于MySQL来说，binlog日志就是MySQL的增量数据

（1）按天全备情况

优点：恢复时间：短，维护成本：低

缺点：占用空间：多，占用系统资源多，经常锁表影响用户体验。

（2）按周全备情况

优点：占用空间小，占用系统资源少，无需锁表，用户体验好一些

缺点：维护成本高，恢复麻烦，时间长。

企业场景全量和增量的频率是怎么做的呢？

1）中小公司，全量一般是每天一次，业务流量低谷执行全备，备份时会锁表。

增量备：

a.定时推binlog增量。例如每分钟推一次增量

例：rsync -avz /data/3306/mysql-bin.0000* rsync_bakcup@10.0.0.1::backup --password-file=/etc/rsync.password

b.再在其他远程实时读binlog。

-R, --read-from-remote-server

Read binary logs from a MySQL server.

c. inotify 实时推送binlog

d.mysql主从复制（实时复制功能）

2）大公司周备，每周六00点一次全量，周日-下周六00点前都是增量。

优点：节点备份时间，减少备份压力。

单台数据库用rsync（配合定时任务频率大或者inotify，主从复制）把所有binlog备份到远程服务器，尽量做主从复制

缺点：增量的binlog文件副本太多，还原会很麻烦

3）一主多从环境，主从复制本身就是实时远程备份，可以解决服务器物理故障。

4）一主多从环境，可采取一个从库服务器上专门用mysqldump，cp、tar、xtrabackup做备份，延迟同步

MySQL备份常见方法

MySQL备份的常用方式有逻辑备份和物理备份（oracle也是如此）

逻辑备份：以SQL语句的形式对数据库进行备份（mysqldump）

物理备份：直接备份磁盘上的数据信息（cp、tar、xtrabackup）

50G一下的数据选择mysqldump，如果告诉50G 可以使用物理备份，因为那样逻辑备份会比较慢。

物理备份好于逻辑备份

cp tar 停机（至少禁止写入），xtrabackup支持热备

MySQL的mysqldump备份什么时候排上用场？

1.迁移或者升级数据库时。

2.增加从库的时候。

3.因为硬件或特殊异常情况，主库或从库宕机，主从可以互相切换，无需备份

4.认为的DDL，DML语句，主从库没办法了，所有库都会执行。此时需要备份

5.跨机房灾难，需要备份到异地

2.9 什么情况下需要增量恢复？

我们在生产工作中一般常用一主多从的数据库架构，常见的备份方案是在某一个不对外服务的从库上开启binlog，然后实施定时全备和实时增量备份

什么是增量恢复？

利用二进制日志和全备进行恢复的过程，被称为增量备份

1）主或者从库宕机（硬件损坏）是否需要增量恢复？

答：不需要增量恢复，主库宕机，只需要把其中一个同步最快的从库切换为主库即可。

主库宕机，只要选择更新最快的从库（master.info，或者5.5半同步机制）提升为主库

从库宕机，直接不用就好了（一般都会陪LVS负载均衡）或者正常修复

结论：无论是主还是从，硬件损坏导致的故障无需恢复以及增量恢复。

2）人为操作数据库SQL语句破坏主库是否需要增量恢复？

在数据库主库内部命令行误操作，会导致所有的数据库（包括主从库）数据丢失，例如：在主库执行了drop database test；这样的删除语句，这时所有的从库也会执行这个drop database test；语句，从而导致所有的数据库上的oldboy数据丢失。这样的场景是需要增量恢复的。

结论：认为操作数据库SQL语句破坏主库需要增量恢复

3）只有一个主库是否需要增量恢复？

如果公司里只有一个主库的情况，首先应该做定时全量备份（一天每天一次）及增量备份（每个1-10分钟对binlog日志做切割然后备份到其他的服务器上，或者本地其他的硬盘里）或者写到网络文件系统（备份服务器）里。

如果不允许数据丢失，最好的办法就是做从库，通过drbd（基于磁盘块的）同步

正常情况“

主从同步：除了分担读写分离压力外，还可以防止物理设备损坏数据丢失的恢复。

从库备份：在从库进行全量和增量方式的备份，可以防止认为对主库的误操作导致数据丢失。

确保备份的从库实时和主库是同步状态

小结：一般由认为（或程序）逻辑的方式在数据库执行的SQL语句等误操作，才需要增量恢复，因为此时所有的从库夜之星了误操作语句，物理故障，直接切换从库即可，无需恢复

3.0 MySQL增量恢复必备条件

3.1 开启MySQL log-bin 日志功能

Mysql数据库开启了log-bin 参数记录binlog日志功能如下：

shell>grep log-bin /data/3306/my.cnf
log-bin = /data/3306/mysql-bin

提示：主库和备份的从库都要开启binlog记录功能

小结：增量恢复的条件：

存在一份全备加上全备之后的时刻到出问题时刻的所有增量binlog文件备份

3.2 模拟每天00点备份数据，早上10.00领导删除数据，10.10发现问题并进行解决

1.修改时间为00:00，进行全备

[root@db02 3306]#date -s "00:00:00"
[root@db02 3306]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B -F -R -x --master-data=2 oldboy|gzip >/opt/rh/oldboy-$(date +%F).sql.gz

2.备份完成修改成10点的时间

[root@db02 3306]#date -s "10:00:00"
mysql>drop database test;

3.发现问题，并解决

备份后产生了mysql-bin-log，如果不知道可以采用别的方法进行查看

使用gzip查看备份的时候写入那个binlog里面

[root@db02 rh]# gzip -d oldboy-2015-12-22.sql.gz
[root@db02 rh]# grep CHANGE oldboy-2015-12-22.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=107;

4.导出数据进行备份，并进行分析

[root@db02 rh]# cp /data/3306/mysql-bin.000007 .
[root@db02 rh]# ls -l
total 8
-rw-r----- 1 root root  792 Dec 22 10:10 mysql-bin.000007
-rw-r--r-- 1 root root 3010 Dec 22 00:00 oldboy-2015-12-22.sql

5.以及复制全量备份

[root@db02 rh]# mysqlbinlog -d oldboy oldboy-2015-12-22.sql

6.如果恢复的sql文件比较多可以使用模糊匹配

[root@db02 rh]# mysqlbinlog
-d oldboy oldboy-2015-12-22.sql*或者后面接上bin。

log的文件名（也可以使用for循环）顺序一定要正确

[root@db02 rh]# mysqlbinlog -d oldboy mysql-bin.000007 >007.sql
[root@db02 rh]#vim 007.sql
因为是使用drop删除，日志里面记录了，所以我们使用vim将里面的drop删除

出了问题先把mysql-bin.log拿走，否则直接恢复数据库会再次记录

[root@db02 rh]# mysql -uroot -p123456 -S /data/3306/mysql.sock  <oldboy-2015-12-22.sql 
[root@db02 rh]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cyh                |
| mysql              |
| oldboy             |
| oldboy123          |
| oldboy_gbk         |
| performance_schema |
| wordpress          |
+--------------------+

表示已经恢复到我们00点之前的数据

[root@db02 rh]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "select * from oldboy.test;"
+----+--------+
| id | name   |
+----+--------+
|  1 | gongli |
|  2 | gongli |
|  3 | gongli |
|  4 | gongli |
|  5 | gongli |
+----+--------+
恢复增量数据

恢复增量数据，最好指定数据库名

[root@db02 rh]# mysql -uroot -p123456 -S
/data/3306/mysql.sock 007.sql

企业案例：

625某电商网站数据库宕机故障解决实录（上）

http://oldboy.blog.51cto.com/2561410/1431161

http://oldboy.blog.51cto.com/2561410/1431172

{{userData.name}}已认证