MySQL备份恢复与日志

MySQL 数据库的备份与恢复

1.1 备份数据的意义

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

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

MySQL数据库自带了一个很好用的备份命令,就是mysqldump,它的基本使用如下;
语法:mysqldump -u 用户名 -p  数据库名 > 备份的文件名
  1. mysqldump -uroot -p123456 -S /data/3306/mysql.sock oldboy >/opt/oldboy_$(date +%F).sql
我们可以使用grep 查看我们都备份了些什么东西

  1. [root@db02 oldboy]# egrep -v "#|\*|--|^$" /opt/oldboy_2016-06-22.sql
  2. DROP TABLE IF EXISTS `student`;
  3. CREATE TABLE `student` (
  4.   `id` int(4) NOT NULL AUTO_INCREMENT,
  5.   `name` char(20) NOT NULL,
  6.   `age` tinyint(2) NOT NULL DEFAULT '0',
  7.   `dept` varchar(16) DEFAULT NULL,
  8.   PRIMARY KEY (`id`),
  9.   KEY `index_name` (`name`),
  10.   KEY `index_age` (`name`(8)),
  11.   KEY `ind_name_dept` (`name`,`dept`)
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  13. LOCK TABLES `student` WRITE;
  14. UNLOCK TABLES;
  15. DROP TABLE IF EXISTS `test`;
  16. CREATE TABLE `test` (
  17.   `id` int(4) NOT NULL AUTO_INCREMENT,
  18.   `name` char(20) NOT NULL,
  19.   PRIMARY KEY (`id`)
  20. ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
  21. LOCK TABLES `test` WRITE;
  22. INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka');
  23. 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
语法如下:

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

  1. mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B oldboy|gzip>/opt/123.sql.gz
  2. -rw-r--r--  1 root root  899 Jun 22 17:34 123.sql.gz
  3. -rw-r--r--  1 root root 1014 Jun 22 17:27 123.sql
使用gzip压缩打包文件效率是不压缩的3倍

以上参数小结

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

mysqldump的工作原理?

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

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

1.4 如果做分库分表

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

  1. mysqldump -uroot -p123456 -B oldboy
  2. mysqldump -uroot -p123456 -B oldboy_gbk

分库的备份语法:

先取出我们要备份的库

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

执行命令,进行批量备份

  1. 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

 查看最后结果

  1. [root@db02 tmp]# ll
  2. total 20
  3. -rw-r--r-- 1 root root    0 Jun 21 11:55 bak.sql
  4. -rw-r--r-- 1 root root 1382 Jun 22 17:55 cyh.sql.gz
  5. -rw-r--r-- 1 root root 1406 Jun 22 17:55 oldboy123.sql.gz
  6. -rw-r--r-- 1 root root 1409 Jun 22 17:55 oldboy_gbk.sql.gz
  7. -rw-r--r-- 1 root root 2808 Jun 22 17:55 oldboy.sql.gz
  8. -rw-r--r-- 1 root root 1406 Jun 22 17:55 wordpress.sql.gz
提示:可以使用for循环进行备份
备份单个表

  1. mysqldump -uroot -p123456 -S /data/3306/mysql.sock oldboy test >/opt/test.sql
备份多个表

  1. mysqldump -uroot -p123456 -S /data/3306/mysql.sock oldboy test cyh>/opt/test.sql
[提示]只要不加-B 前面是库,后面的都是表
只显示表结构

  1. mysqldump -uroot -p123456 -S /data/3306/mysql.sock oldboy test  -d >/opt/test.sql
直接备份数据 -t参数

  1. mysqldump -uroot -p123456 -S /data/3306/mysql.sock oldboy test  -t>/opt/test.sql
备份数据库表结构(不包含数据)

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

  1. mysqldump -uroot -p123546 -B -d oldboy >/opt/t.sql
语句和数据分离,数据为文本 -T -tab=name

  1. mysqldump -uroot -p123456 oldboy student compact tab=/tmp/
  2. cat /tmp/student.txt
  3. oldboy
  4. oldgirl
  5. inca
  6.  zuma
刷新binlog

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

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

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

刷新binlog介绍
  1. binlog是记录数据库更新的SQL语句,二进制文件
  2. mysqldump永不定时对某一时刻数据的全备,例如:00点进行备份bak.sql.gz
  3. 增量备份:当有数据写入到数据库时,还会同时把更新的SQL语句写入到对应的文件里
  4. 这个文件就叫做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引擎企业生产备份命令(适合所有引擎和混合引擎)
  1. mysqldump -uroot -poldboy123 -A -B -F -R --master-data=2 -x --events|gzip >/opt/all.sql.gz
  2. 提示:-F 也可以不用,与--master-data有些重复
innoDB引擎企业生产备份命令;推荐使用的
  1. mysqldump -uroot poldboy123 -A -B -F -R --master-data=2 --events --single-transaction|gzip >/opt/all.sql.gz
  2. 提示:-F 也可以不用,与--master-data
额外补充:
(一)mysqldump逻辑备份说明
缺点:效率不是特别高。
优点:简单、方便、可靠、迁移
适用于数据量不是特别大的场景,打包前50G以内数据
(二)超过50G可选方案
1、xtrabackup物理备份工具:全量和增量(支持热备)
2、物理备份方案:从库停止SQL线程,打包,cp
数据库恢复事项
提示:
1.数据恢复和字符集关联很大,如果字符集不正确会导致恢复的数据乱码
2.mysql命令以及source命令恢复数据库的原理就是把文件的sql语句,在数据库重新执行的过程
小技巧
  1. Mysql>system ls -l /opt/  可以跳出mysql查看命令
  2. Mysql>source /opt/oldboy_B.sql  直接接路径就可以恢复
  3. source数据恢复和字符集关联很大,如果字符集不正确会导致恢复的数据乱码
  4. UTF8数据库,那么恢复的文件格式需要为"UTF8-没有签名"格式,txt右击另存为设置

1.6 MySQL恢复命令

针对压缩的备份数据恢复;
1、方法
  1. gzip -d /opt/mysql.sql.gz
  2. mysql -uroot -poldboy </opt/mysql.sql
  3. 不删除源备份文件:
  4. gzip -cd 01.sql.gz >2.sql
2、方法
  1. gunzip<b_bak.sql.gz>/opt/mysql.sql
  2. mysql -uroot -poldboy </opt/mysql.sql
  3. 或者
  4. gunzip <b_bak.sql.gz|mysql -uroot -poldboy123
恢复总结:
1、source命令
2、mysql命令

1.7 实现和MySQL非交互式对话

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

  1. [root@www ~]# mysql -uroot -p -e "show databases;"
  2. Enter password: 
  3. +--------------------+
  4. | Database           |
  5. +--------------------+
  6. | information_schema |
  7. | bbs                |
  8. | mysql              |
  9. | performance_schema |
  10. | test               |
  11. | wordpress          |
  12. +--------------------+
查看完整的线程状态,此参数才查看慢查询语句是非常有用
  1. [root@www ~]# mysql -uroot -p -e "show full processlist;"
  2. Enter password: 
  3. +-----+------+-----------+------+---------+------+-------+-----------------------+
  4. | Id  | User | Host      | db   | Command | Time | State | Info                  |
  5. +-----+------+-----------+------+---------+------+-------+-----------------------+
  6. | 797 | root | localhost | NULL | Query   |    0 | NULL  | show full processlist |
  7. +-----+------+-----------+------+---------+------+-------+-----------------------+
企业案例:mysql sleep线程过多的问题案例
  1. mysql> show processlist;
  2. mysql>kill 89;  可以使用kill 杀死进程
=============================================
mysql sleep线程过多问题
在配置文件修改:
  1. [mysqld]
  2. interactive_timeout = 120  <==此参数设置后wait_timout自动失效
  3. wait_timeout = 120
其他方法:
1、PHP程序中,不使用持续连接,即使用mysql_connect而不是pconnect(JAVA调整连接池)
2、PHP程序执行完毕,应该显示调用mysql_close
3、逐渐分析MySQL的SQL查询及慢查询日志,找到查询过慢的SQL,优化

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

不重启数据库修改数据库参数,但是要求重启后还能生效
  1. shell>mysql -uroot -poldboy -e "show variables;"|grep key_buffer
  2. key_buffer_size 16384
  3. shell>mysql -uroot -poldboy -e "set global key_buffer_size = 1024*32;"
  4. shell>mysql -uroot -poldboy -e "show variables;"|grep key_buffer
  5. key_buffer_size 32768
  6. shell>sed -n '32p' /etc/my.cnf
  7. key_buffer_size = 16K
  8. shell>sed -i 's#key_buffer_size = 16K#key_buffer_size = 32K#g' /etc/my.cnf

查看数据库参数是否生效

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

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

 

  1. [root@db02 opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show variables like '%log_bin%';"
  2. +---------------------------------+-------+
  3. | Variable_name                   | Value |
  4. +---------------------------------+-------+
  5. | log_bin                         | ON    |
  6. | log_bin_trust_function_creators | OFF   |
  7. | sql_log_bin                     | ON    |
  8. +---------------------------------+-------+
  9. [root@db02 opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show variables like '%slow_%';"
  10. +---------------------+-------------------------------+
  11. | Variable_name       | Value                         |
  12. +---------------------+-------------------------------+
  13. | log_slow_queries    | OFF                           |
  14. | slow_launch_time    | 2                             |
  15. | slow_query_log      | OFF                           |
  16. | slow_query_log_file | /data/3306/data/db02-slow.log |
  17. +---------------------+-------------------------------+
索引缓冲区

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

修改索引缓冲区的大小

  1. set global key_buffer_size = 1024*1024*32;
  2. root@oldboy 11:03:59->show variables like '%key_buffer%';
  3. +-----------------+----------+
  4. | Variable_name   | Value    |
  5. +-----------------+----------+
  6. | key_buffer_size | 33554432 |
  7. +-----------------+----------+
  8. 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 生产场景通常重要命令小结
  1. show processlist;  #查看数据库正在执行的SQL语句,可能无法看全完整SQL语句
  2. show full prcesslist #查看正在执行的完整SQL语句,完整显示
  3. set global key_buffer_size = 1024*1024*32  #不重启数据库调整数据库参数,直接生效,重启后失效、
  4. show variables;  #查看数据库的配置参数信息,例如:my.cnf里参数的生效情况
  5. kill ID     #杀掉SQL线程的命令,ID为线程号
  6. show session status  #查看当前会话的数据库状态信息
  7. show global status;  #查看整个数据库运行状态信息,很重要。要分析并做好监控
  8. show status;    #mysql运行状态
  9. show engine innodb status #显示InnoDB 引擎的性能状态(早起版本show innodb status)
查看select进行的查看次数

  1. root@oldboy 11:22:46->show global status like '%select%';      
  2. +------------------------+-------+
  3. | Variable_name          | Value |
  4. +------------------------+-------+
  5. | Com_insert_select      | 0     |
  6. | Com_replace_select     | 0     |
  7. | Com_select             | 264   |            
  8. | Select_full_join       | 0     |
  9. | Select_full_range_join | 0     |
  10. | Select_range           | 7     |
  11. | Select_range_check     | 0     |
  12. | Select_scan            | 287   |
  13. +------------------------+-------+
  14. 8 rows in set (0.00 sec)
查看查询和写入的次数

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

过滤出innodb引擎的参数

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

查看引擎innodb状态
  1. show gengine innodb status\G

MySQL工具mysqlbinlog

2.1 mysql的binlog日志是什么?

mysql数据目录下的如下文件就是mysql的binlog日志
  1. 多实例路径:/data/3306/
  2. mysql-bin.00001
  3. mysql-bin.00002
  4. mysql-bin.00003
  5. mysql-bin.00004
  6. mysql-bin.00005
需要在配置文件中开启次功能

  1. grep log-bin my.cnf
  2. log-bin = /data/3306/mysql-bin
主要作用:用于数据库的主从复制以及数据的增量恢复    

2.2 mysqlbinlog工具解析binlog日志实践

默认情况binlog日志是二进制格式的,不能使用查看文本工具的命令来查看,例如:cat vi vim
2.3 解析指定库的binlog日志
范例:利用mysqldump -d参数解析指定库的binlog日志
  1. shell>mysqlbinglog -d oldboy mysql-bin.00001 -r oldboy.sql
  2. 提示:-r代表>输出内容 -d 指定库文件
mysql-bin.000001内容介绍
  1. # at 2154
  2. #160630  7:45:07 server id 1  end_log_pos 2253 Query thread_id=11 exec_time=0 error_code=0
  3. SET TIMESTAMP=1467243907/*!*/;
  4. create database qqqqqqq41233
  5. /*!*/;
  6. DELIMITER ;
  7. # End of log file
  8. ROLLBACK /* added by mysqlbinlog */;
  9. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  10. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

按照位置截取:确定

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

按照时间截图:模糊,不准

  1. mysqlbinlog mysql-bin.00001 --start-datetime='2016-10-16 17:14:15' --stop-datetime='2014-10-16 17:15:15' -r time.sql
  2. 指定开始时间,不指定结束时间,将从开始时间一直到最后
  3. mysqlbinlog mysql-bin.000001 --start-datetime='2016-10-16 17:14:15' -r time.sql
  4. 指定结束时间,不指定开始时间,将从开始一直到指定的结束时间
  5. 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日志的方法。
  1. mysqlbinlog --base64-ouput=decode-rows-v mysql-bin.00001
  2. 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:在配置文件中调整方法,当然可以在启动时加入启动参数

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

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

  1. mysqld_safe --default-file=/data/3306/my.cnf --log-error=/data/3306/mysql_oldboy.err &
  2. root@oldboy 10:09:14->show variables like '%log_error%';
  3. +---------------+---------------------------------+
  4. | Variable_name | Value                           |
  5. +---------------+---------------------------------+
  6. | log_error     | /data/3306/mysql_oldboy3306.err |
  7. +---------------+---------------------------------+
  8. 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服务然后再查看日志文件报有什么错误?

  1. [root@db02 3306]# cat mysql_oldboy3306.err
  2. 160616 16:46:07 mysqld_safe Starting mysqld daemon with databases from /data/3306/data
2、然后mysql3306目录下面所有文件都加上属主并是递归-R
  1. [root@db02 3306]# chown -R mysql *
3、然后查看一下有没有mysql 这个ID
  1. [root@db02 3306]# id mysql
  2. uid=502(mysql) gid=502(mysql) groups=502(mysql)
4、重新启动mysql服务(多实例)
  1. [root@db02 3306]#/data/3306/mysql start
5、此时再查看一下服务有没有启动起来
  1. [root@db02 3306]#lsof -i:3306

2.4 查询日志(也可以叫做访问日志)

  1. [root@db02 ~]grep gene /data/3306/my.cnf
  2. general_log = on
  3. 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)调整

  1. long_query_time=超过指定时间查询(看公司要求,通常2秒)
  2. log-slow-queries= 日志路径
  3. log_queries_not_using_indexes=不使用索引
慢查询的设置,对于数据库SQL的优化非常重要

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

2.6 慢查询日志切割

  1. shell>cat /server/scripts/cut_slow_log.sh
  2. cd /data/3306/ &&\
  3. /bin/mv slow.log slow.log.$(date +%F)&&\
  4. mysqladmin -uroot -poldboy123 -S /data/3306/mysql.sock flush-log
  5. shell>tail -2 /var/spool/cron/root
  6. #cut mysql slow log
  7. 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,>清空
  1. shell>mv /data/3306/slow.log /opt/$(date +%F)_slow.log
  2. shell>mysqladmin -uroot -poldboy -S /data/3306/mysql.sock flush-logs
  3. mysqlsla分析:http://blog.itpub.net/7607759/viewspace-692828

2.7 二进制日志(binary log)介绍与调整

1、二进制日志(binary log)介绍
二进制日志(binary log)记录数据表被修改的相关信息;
2.二进制日志(binary log)调整
  1. root@oldboy 11:13:37->show variables like '%log_bin%';
  2. +---------------------------------+-------+
  3. | Variable_name                   | Value |
  4. +---------------------------------+-------+
  5. | log_bin      记录binlog开关      | ON    |
  6. | log_bin_trust_function_creators | OFF   |
  7. | sql_log_bin 临时不记录binlog开关(增量恢复)| ON 
  8. +---------------------------------+-------+
  9. 3 rows in set (0.01 sec)
可以在配置文件中设置
  1. [root@db02 3306]# grep log-bin /data/3306/my.cnf
  2. 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日志功能如下:
  1. shell>grep log-bin /data/3306/my.cnf
  2. log-bin = /data/3306/mysql-bin
提示:主库和备份的从库都要开启binlog记录功能
小结:增量恢复的条件:
  存在一份全备加上全备之后的时刻到出问题时刻的所有增量binlog文件备份

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

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

  1. [root@db02 3306]#date -s "00:00:00"
  2. [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点的时间
  1. [root@db02 3306]#date -s "10:00:00"
  2. mysql>drop database test;
3.发现问题,并解决
备份后产生了mysql-bin-log,如果不知道可以采用别的方法进行查看
使用gzip查看备份的时候写入那个binlog里面

  1. [root@db02 rh]# gzip -d oldboy-2015-12-22.sql.gz
  2. [root@db02 rh]# grep CHANGE oldboy-2015-12-22.sql
  3. -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=107;
4.导出数据进行备份,并进行分析

  1. [root@db02 rh]# cp /data/3306/mysql-bin.000007 .
  2. [root@db02 rh]# ls -l
  3. total 8
  4. -rw-r----- 1 root root  792 Dec 22 10:10 mysql-bin.000007
  5. -rw-r--r-- 1 root root 3010 Dec 22 00:00 oldboy-2015-12-22.sql
5.以及复制全量备份

  1. [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循环)顺序一定要正确    
  1. [root@db02 rh]# mysqlbinlog -d oldboy mysql-bin.000007 >007.sql
  2. [root@db02 rh]#vim 007.sql
  3. 因为是使用drop删除,日志里面记录了,所以我们使用vim将里面的drop删除
出了问题先把mysql-bin.log拿走,否则直接恢复数据库会再次记录

  1. [root@db02 rh]# mysql -uroot -p123456 -S /data/3306/mysql.sock  <oldboy-2015-12-22.sql
  2. [root@db02 rh]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show databases;"
  3. +--------------------+
  4. | Database           |
  5. +--------------------+
  6. | information_schema |
  7. | cyh                |
  8. | mysql              |
  9. | oldboy             |
  10. | oldboy123          |
  11. | oldboy_gbk         |
  12. | performance_schema |
  13. | wordpress          |
  14. +--------------------+

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

  1. [root@db02 rh]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "select * from oldboy.test;"
  2. +----+--------+
  3. | id | name   |
  4. +----+--------+
  5. |  1 | gongli |
  6. |  2 | gongli |
  7. |  3 | gongli |
  8. |  4 | gongli |
  9. |  5 | gongli |
  10. +----+--------+
  11. 恢复增量数据
恢复增量数据,最好指定数据库名

[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

历史上的今天:

  1. 2017:  Linux 通过host.allow限制特定IP来访(0)
新闻联播老司机

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: