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.sqlDROP 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
–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 oldboymysqldump -uroot -p123456 -B oldboy_gbk
分库的备份语法：
先取出我们要备份的库
[root@db02 oldboy]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show databases;"|egrep -v "Database|_schema|mysql"cyholdboyoldboy123oldboy_gbkwordpress
执行命令，进行批量备份
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]# lltotal 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.txtoldboyoldgirlincazuma
刷新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.gzmysql -uroot -poldboy </opt/mysql.sql不删除源备份文件：gzip -cd 01.sql.gz >2.sql
2、方法
gunzip<b_bak.sql.gz>/opt/mysql.sqlmysql -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_bufferkey_buffer_size 16384shell>mysql -uroot -poldboy -e "set global key_buffer_size = 1024*32;"shell>mysql -uroot -poldboy -e "show variables;"|grep key_bufferkey_buffer_size 32768shell>sed -n '32p' /etc/my.cnfkey_buffer_size = 16Kshell>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 0Com_insert_select 0Com_replace_select 0Com_select 1Delayed_insert_threads 0Innodb_rows_inserted 117Qcache_inserts 58
过滤出innodb引擎的参数
[root@db02 opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show status ;"|grep -E "select|insert"Com_insert 0Com_insert_select 0Com_replace_select 0Com_select 1Delayed_insert_threads 0Innodb_rows_inserted 117Qcache_inserts 58
查看引擎innodb状态
show gengine innodb status\G
MySQL工具mysqlbinlog
2.1 mysql的binlog日志是什么？
mysql数据目录下的如下文件就是mysql的binlog日志
多实例路径：/data/3306/mysql-bin.00001mysql-bin.00002mysql-bin.00003mysql-bin.00004mysql-bin.00005
需要在配置文件中开启次功能
grep log-bin my.cnflog-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=0SET TIMESTAMP=1467243907/*!*/;create database qqqqqqq41233/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
按照位置截取：确定
# at 2154#160630 7:45:07mysqlbinlog 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.00001mysqlbinlog --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)
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.err160616 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 mysqluid=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.cnfgeneral_log = ongeneral_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 -3long_query_time = 1log-slow-queries = /data/3306/slow.loglog_queries_not_using_indexes
2.6 慢查询日志切割
shell>cat /server/scripts/cut_slow_log.shcd /data/3306/ &&\/bin/mv slow.log slow.log.$(date +%F)&&\mysqladmin -uroot -poldboy123 -S /data/3306/mysql.sock flush-logshell>tail -2 /var/spool/cron/root#cut mysql slow log00 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.logshell>mysqladmin -uroot -poldboy -S /data/3306/mysql.sock flush-logsmysqlsla分析：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.cnflog-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.cnflog-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 -ltotal 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。
-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
897
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
