MySQL--11 备份的原因

老王的日志 08-13 38

前言：

而今各位老铁们对“8点mysql”可能比较关怀，同学们都想要分析一些“8点mysql”的相关知识。那么小编同时在网络上网罗了一些关于“8点mysql””的相关文章，希望咱们能喜欢，姐妹们快快来学习一下吧！

一.备份的原因

运维工作的核心简单概括就两件事:

1）第一个是保护公司的数据.

2）第二个是让网站能7*24小时提供服务(用户体验)。

1）备份就是为了恢复。

2）尽量减少数据的丢失（公司的损失）

二.备份的类型

冷备份:

这些备份在用户不能访问数据时进行，因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是，对于某些应用程序，会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。

温备份:

这些备份在读取数据时进行，但在多数情况下，在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但是，其不足之处在于无法在进行备份时修改数据集，这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。

热备份:

这些动态备份在读取或修改数据的过程中进行，很少中断或者不中断传输或处理数据的功能。使用热备份时，系统仍可供读取和修改数据的操作访问。

三.备份的方式

逻辑备份:

1. 逻辑备份

1）binlog

2）into outfile

mysql> select * from world.city into outfile '/tmp/world_city.data';#需要先在配置文件添加参数，指定位置[root@db01 ~]# vim /etc/my.cnfsecure_file_priv=/tmp#重启数据库[root@db01 ~]# /etc/init.d/mysqld  stopShutting down MySQL.. SUCCESS! [root@db01 ~]# /etc/init.d/mysqld  startStarting MySQL. SUCCESS! #连接[root@db01 ~]# mysql -uroot -p1 mysql> show variables like 'secure_file_priv';+------------------+-------+| Variable_name    | Value |+------------------+-------+| secure_file_priv | /tmp/ |+------------------+-------+1 row in set (0.01 sec)#把数据库里world.city表导出来mysql> select * from world.city into outfile '/tmp/world_city.data';Query OK, 4079 rows affected (0.01 sec)#查看导出的表前十行[root@db01 ~]# head /tmp/world_city.data 1	Kabul	AFG	Kabol	17800002	Qandahar	AFG	Qandahar	2375003	Herat	AFG	Herat	1868004	Mazar-e-Sharif	AFG	Balkh	1278005	Amsterdam	NLD	Noord-Holland	7312006	Rotterdam	NLD	Zuid-Holland	5933217	Haag	NLD	Zuid-Holland	4409008	Utrecht	NLD	Utrecht	2343239	Eindhoven	NLD	Noord-Brabant	20184310	Tilburg	NLD	Noord-Brabant	193238

3）mysqldump

4）replication（主从复制）

5）mysqlbinlog

2.物理备份:

基于数据文件的备份

1）cp data

2）Xtrabackup（percona公司）速度非常快

四.备份策略

1）全量备份 full

2）增量备份 increamental

3）差异备份

五.备份工具

1）mysqldump（逻辑）

mysql原生自带很好用的逻辑备份工具

2）mysqlbinlog（逻辑）

实现binlog备份的原生态命令

3）xtrabackup（物理）

precona公司开发的性能很高的物理备份工具

备份工具使用

mysqldump的备份

mysqldump常用参数

1）连接服务端参数(基本参数)：

mysqldump-u：指定用户-p：指定密码-h：指定主机域-P：指定端口-S：指定socket文件

2）-A, --all-databases：全库备份

[root@db01 ~]# mysqldump -uroot -p123 -A > /tmp/full.sql

3）不加参数：单库、单表多表备份

#不加参数，只备份库里表的内容。[root@db01 ~]# mysqldump -uroot -p123 db01 > /tmp/db01.sql#导库的时候需要指定库名[root@db02 ~]# mysql -uroot -p1  db01 < /tmp/db01.sql#单表world.city表[root@mysql-db01 backup]# mysqldump -uroot -p123 world city > /tmp/city.sql

4）-B：指定库备份

[root@db01 ~]# mysqldump -uroot -p123 -B db1 > /tmp/db1.sql[root@db01 ~]# mysqldump -uroot -p123 -B db1 db2 > /tmp/db1_db2.sql

5）-F：flush logs在备份时自动刷新binlog（不常用，很鸡肋，有多少个库，刷新多少个binlog）

[root@db01 backup]# mysqldump -uroot -p123 -A -R –triggers -F > /backup/full_2.sql[root@db01 mysql]# mysqldump -uroot -p1 -A -F > /tmp/full_F.sql

6）--master-data=2：备份时加入change master语句0没有1不注释2注释

#--master-data=2 （打点备份，温备）	2：打点备份并且注释change master to	1：打点备份不注释change master to	0：相当于不开启，等于没写#--single-transaction(快照备份)[root@db01 ~]# mysqldump -uroot -p1 -A --master-data=2 --single-transaction > /tmp/f.sqlCHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000030', MASTER_LOG_POS=20550;#起点，结束点mysqlbinlog --start-position=20550 --stop-position=25058

7）-d：仅表结构

8）-t：仅数据

备份额外扩展项

1）-R, --routines：备份存储过程和函数数据（开发xieden）

2）--triggers：备份触发器数据( 开发写的‘)

[root@db01 backup]# mysqldump -uroot -p123 -A -R --triggers > /tmp/full_2.sql

mysqldump特殊参数

1）-x：锁表备份（myisam温备份）

2）--single-transaction：快照备份

[root@db01 backup]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 –single-transaction>/tmp/full.sql

找点

[root@db01 ~]# mysql -uroot -p1 -e "show binlog events in 'mysql-bin.000003'"|grep -i drop

3)gzip:压缩备份 (终极备份语句)

[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 –single-transaction|gzip>/tmp/full.sql.gz####了解即可#-d：仅备份表结构#-t：仅备份数据#-x：锁表备份#恢复压缩数据[root@db01 tmp]# zcat /tmp/full_2019-11-13.sql.gz | mysql -uroot -p1

mysqldump的恢复

#先不记录二进制日志mysql> set sql_log_bin=0;#库内恢复操作mysql> source /tmp/full.sql#库外恢复操作[root@db01 ~]# mysql -uroot -p123 < /tmp/full.sql

注意：

1）mysqldump在备份和恢复时都需要MySQL实例启动为前提

2）一般数据量级100G以内，大约15-30分钟可以恢复（PB、EB就需要考虑别的方式）

3）mysqldump是以覆盖的形式恢复数据的

六、企业故障恢复案例

背景：

正在运行的网站系统，MySQL数据库，数据量25G，日业务增量10-15M。

备份策略：

每天23：00，计划任务调用mysqldump执行全备脚本

故障时间点：

上午10点开发人员误删除一个核心业务表，如何恢复？

思路：

1）停业务避免数据的二次伤害，停止连接数据库的服务（php ,tomcat , python... ）

2）找一个临时的库，恢复前一天的全备

3）截取前一天23：00到第二天10点误删除之间的binlog，恢复到临时库

4）测试可用性和完整性

5）开启业务前的两种方式

a.直接使用临时库顶替原生产库，前端应用割接到新库

b.将误删除的表单独导出，然后导入到原生产环境

6）开启业务

故障模拟演练：

1.模拟环境.模拟环境准备，准备数据：

#刷新binlog使内容更清晰mysql> flush logs;#查看当前使用的binlogmysql> show master status;#创建backup库mysql> create database backup;#进入backup库mysql> use backup#创建full表mysql> create table full select * from world.city;#创建full_1表mysql> create table full_1 select * from world.city;#查看表mysql> show tables;

2.模拟23:00 全备：

[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full_$(date +%F).sql.gz

3.模拟第二天数据变化：

#进入backup库mysql> use backup#创建new表mysql> create table new select * from mysql.user;#创建new_1表mysql> create table new_1 select * from world.country;#查看表mysql> show tables;#查看full表中所有数据mysql> select * from full;#把full表中所有的countrycode都改成CHNmysql> update full set countrycode='CHN' where 1=1;#提交mysql> commit;#删除id大于200的数据mysql> delete from full where id>200;#提交mysql> commit;

4).模拟故障：H E

#删除new表mysql> drop table new;#查看表mysql> show tables;

2.模拟恢复数据过程：

1）.停库，停止业务（避免二次伤害数据）

[root@db01 tmp]# /etc/init.d/mysqld stopShutting down MySQL. SUCCESS!

2）.准备新环境

多实例（物理服务器）再买一台云主机

准备新环境（初始化一个新的数据库实例，二进制安装MySQL）

方法一：重新安装和故障环境一样版本的数据库[root@db02 scripts]# ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data[root@db02 scripts]# /etc/init.d/mysqld start. SUCCESS! #方法二：清空新库内容，干净环境[root@db02 ~]# /etc/init.d/mysqld  stopShutting down MySQL.. SUCCESS! [root@db02 ~]# rm -fr /application/mysql/data/*[root@db02 ~]# cd /application/mysql/scripts/[root@db02 ~]# ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data[root@db02 scripts]#  /etc/init.d/mysqld  startStarting MySQL.Logging to '/application/mysql/data/db02.err'. SUCCESS! [root@db02 mysql]# mysqlmysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test               |+--------------------+

3）将昨天晚上23点做的全备导入新环境,拷贝数据到新库上

[root@db01 ~]# scp /tmp/full_2019-11-13.sql.gz root@10.0.0.52:/tmp

4）导入数据

[root@db02 ~]# zcat full_2019-11-13.sql.gz |mysqlmysql> show tables from backup;+------------------+| Tables_in_backup |+------------------+| full             || full_1           |+------------------+mysql> select count(*) from backup.full_1;+----------+| count(*) |+----------+|     4079 |+----------+

1.根据binlog截取昨晚23点到今天上午10点的数据

先找到起点

#找起点[root@db01 tmp]# zcat /tmp/full_2019-11-13.sql|head -25-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.00005', MASTER_LOG_POS=542384;#或者再过滤查找[root@db01 scripts]# zcat /tmp/full_2019-11-13.sql.gz |head -25 |grep -i 'change master to'-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=542384;

找到结束点

方法一：[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000005|less#搜索DROP679230方法二：[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000005 |grep -i -B 5 'drop table'

截取

#截取二进制[root@db01 tmp]#mysqlbinlog -uroot -p123 --start-position=542384 --stop-position=679230 /application/mysql/data/mysql-bin.000017 > /tmp/inc.sql

5）.将截取出来的数据发送到新环境

[root@db01 tmp]# scp /tmp/inc.sql 172.16.1.52:/tmp

6）.导入新增数据

[root@db02 mysql]# set sql_log_bin=0[root@db02 mysql]# mysql -uroot -p1 < /tmp/inc.sql方法二：在新库内恢复数据#不记录二进制日志mysql> set sql_log_bin=0;#恢复全备数据mysql> source /tmp/full_2019-11-13.sql#进入backup库mysql> use backup# 查看表mysql> show tables;#恢复增量数据mysql> source /tmp/inc.sql

查看

mysql> use backupmysql> show tables;+------------------+| Tables_in_backup |+------------------+| full             || full_1           || inc              || inc_1            |+------------------+mysql> select count(*) from full_1;+----------+| count(*) |+----------+|     8158 |+----------+

7）.恢复生产业务

应用割接（修改连接数据库的代码）导出核心业务表，恢复到旧生产环境

[root@db02 mysql]#mysqldump -uroot -p1 db1 tb1 > /tmp/tb1.sql[root@db02 mysql]#scp /tmp/tb1.sql 172.16.1.51:/tmp[root@db01 data]# /etc/init.d/mysqld start[root@db01 data]# mysql -uroot -p1 db1 < /tmp/tb1.sql

在生产库恢复数据

#方法一：mysql -uroot -p1 db1 < /tmp/tb1.sql#方法二：启动数据库#进入backup库mysql> use backup#在生产库恢复数据mysql> source /tmp/new.sql#查看表mysql> show tables;

以上两种方式都可以，选择速度最快的一种方式

改代码：

1）.开发提交gitlab，从gitlab拉取，上线

2）.开发写好了配置文件

8.开启业务

结合：mysqldump全备+binlog增备恢复数据。

#全备[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full_$(date +%F).sql.gz#scp到对端[root@db01 ~]# scp /tmp/full_2019-11-13.sql.gz root@10.0.0.52:/tmp#对端导库[root@db02 ~]# zcat full_2019-11-13.sql |mysql -uroot -p1#mysql-bin数字[root@db01 ~]# zcat /tmp/full_2019-11-13.sql|head -25 |sed -rn "s#.*mysql-bin.(.*)'.*#\1#gp"000005#起点[root@db01 ~]# zcat /tmp/full_2019-11-13.sql|head -25 |sed -rn 's#.*MASTER_LOG_POS=(.*);#\1#gp'542384#结束点[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000005 |grep -i -B 5 'drop table `full_1`'|sed -rn 's#.*at (.*)#\1#gp'408174#截取二进制[root@db01 tmp]#mysqlbinlog -uroot -p123 --start-position=542384 --stop-position=679230 /application/mysql/data/mysql-bin.000017 > /tmp/inc.sql#远程传到对端[root@db01 tmp]# scp /tmp/inc.sql 172.16.1.52:/tmp#导库[root@db02 mysql]# mysql -uroot -p1 < /tmp/inc.sql

本文地址：http://www.longkongtuishu.com/cafe4BARsBFYECVRT.html

标签： #8点mysql