mysql的备份

一、备份的目的

做灾难恢复:对损坏的数据进行恢复和还原

需求改变:因需求改变而需要把数据还原到改变以前

测试:测试新功能是否可用

二、备份需要考虑的问题

可以容忍丢失多长时间的数据;

恢复数据要在多长时间内完;

恢复的时候是否需要持续提供服务;

恢复的对象,是整个库,多个表,还是单个库,单个表。

三、备份的类型

1、根据是否需要数据库离线

冷备(cold backup):需要关mysql服务,读写请求均不允许状态下进行;

温备(warm backup): 服务在线,但仅支持读请求,不允许写请求;

热备(hot backup):备份的同时,业务不受影响。

注:

1、这种类型的备份,取决于业务的需求,而不是备份工具

2、MyISAM不支持热备,InnoDB支持热备,但是需要专门的工具

2、根据要备份的数据集合的范围

完全备份:full backup,备份全部字符集。

增量备份: incremental backup 上次完全备份或增量备份以来改变了的数据,不能单独使用,要借助完全备份,备份的频率取决于数据的更新频率。

差异备份:differential backup 上次完全备份以来改变了的数据。

建议的恢复策略:

完全+增量+二进制日志

完全+差异+二进制日志

3、根据备份数据或文件

物理备份:直接备份数据文件

优点:

备份和恢复操作都比较简单,能够跨mysql的版本,

恢复速度快,属于文件系统级别的

建议:

不要假设备份一定可用,要测试

mysql>check tables;检测表是否可用

逻辑备份: 备份表中的数据和代码

优点:

恢复简单、

备份的结果为ASCII文件,可以编辑

与存储引擎无关

可以通过网络备份和恢复

缺点:

备份或恢复都需要mysql服务器进程参与

备份结果占据更多的空间,

浮点数可能会丢失精度

还原之后,缩影需要重建

四:备份的对象

1、 数据;

2、配置文件;

3、代码:存储过程、存储函数、触发器

4、os相关的配置文件

5、复制相关的配置

6、二进制日志

MYSQL双主同步复制配置

1.MySQL同步机制实现概述

MySQL支持单向、异步复制,复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器。而在实际项目中,两台分布于异地的主机上安装有mysql数据库,两台服务器互为主备,客户要求当其中一台机器出现变化或故障时,另外一台能够实时接管服务器上的数据,这就需要两台数据库的数据要实时保持一致,在这里使用MySQL的同步功能实现双机的同步复制。

MySQL同步机制基于master主服务器把所有对数据库的更新操作(更新、删除 等)都记录在二进制日志里,并维护日志文件的一个索引以跟踪日志循环。从服务器在日志中读取最后一次成功更新的位置,并接收从那时起发生的任何更新,然后封锁并等待主服务器通知下一次更新。因此,想要启用同步机制,在master端就必须启用二进制日志。每个slave从服务器接受来自master上在二进制日志中记录的更新操作,因此在slave上执行了这个操作的一个拷贝。应该非常重要地意识到,二进制日志只是从启用二进制日志开始的时刻才记录更新操作的。所以slave必须在启用二进制日志时把master上已经存在的数据拷贝过来(利用快照或备份)。如果运行同步时slave上的数据和master上启用二进制日志时的数据不一致的话,那么slave同步就会失败。

MySQL同步功能由3个线程(master上1个,slave上2个)来实现。执行START SLAVE语句后,slave就创建一个I/O线程。I/O线程连接到master上,并请求master发送二进制日志中的语句。master创建一个线程来把日志的内容发送到slave上。这个线程在master上执行SHOW PROCESSLIST语句后的结果中的 Binlog Dump 线程便是。slave上的I/O线程读取master的Binlog Dump线程发送的语句,并且把它们拷贝到其数据目录下的中继日志(relay logs)中。第三个是SQL线程,salve用它来读取中继日志,然后执行它们来更新数据。

如上所述,每个mster/slave上都有3个线程。每个master上有多个线程,它为每个slave连接都创建一个线程,每个slave只有I/O和SQL线程。在MySQL中执行SHOW PROCESSLIST语句就会告诉我们所关心的master和slave上发生的情况。

2.停止所有服务防止设置过程中新数据写入

3.备份数据库

4.MySql配置

操作系统为:Linux

mysql:mysql-5.5.49

两台服务器,简称A服务器,B服务器,实现双机同步

A服务器ip: 192.168.1.10

B服务器ip:192.168.1.11

注意:

1、 当两个服务器IP地址不在同一网段时,需要将主服务器和从服务器通过路由器联通。

2、 请确保主服务器和从服务器上的Windows防火墙处于关闭状态!

4.1在主/备服务器上创建同步用户

在A服务器上增加一个B服务器可以登录的帐号,使用mysql终端输入:(注意tongbu是用于同步的帐号,111111是tongbu的密码,实际中可按需自行设置)

mysql>flush privileges;

mysql>GRANT ALL PRIVILEGES ON *.* TO 'tongbu'@'192.168.1.11' IDENTIFIED BY '';

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '';

mysql>flush privileges;

在B服务器上增加一个A服务器可以登录的帐号:

mysql >flush privileges;

mysql >GRANT ALL PRIVILEGES ON *.* TO 'tongbu'@'192.168.1.10' IDENTIFIED BY '';

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '';

mysql >flush privileges;

4.2主备服务器数据库备份(主服务器为新安装时此步省略)

本文是用student数据库做的示例,导出student.sql文件

4.2.1对主服务器数据进行备份:

在命令行输入如下命令,完成对数据库的备份导出:

#mysqldump -uroot -p student > /tmp/studentA_$(date+%Y%m%d_%H%M%S).sql

按步骤输入设置的密码,回车即可

进入对应的目录查看sql文件是否生成:

4.2.2对备服务器数据进行备份:

在命令行输入如下命令,完成对数据库的备份导出:

#mysqldump -uroot -pstudent> /tmp/studentB_$(date +%Y%m%d_%H%M%S).sql

按步骤输入设置的密码,回车即可

进入对应的目录查看sql文件是否生成

4.2.3将主服务器数据导入到备服务器中

主备服务器的数据库均备份完成后,删除备服务器的student数据库,再重新创建该数据库,将studentA_$(date+%Y%m%d_%H%M%S).sql复制到备服务器指定文件夹(比如/tmp)下,然后通过命令导入数据:

#mysql-u root –pstudent

至此,主备数据库双主配置之前数据一致.

Ps:1.在删除备服务器数据库之前切记备份并保管studentB_$(date +%Y%m%d_%H%M%S).sql,以供后续需要时使用

2.数据量大的情况,备份时间会比较久,需耐心等待

3.若有更好的方案能保证主主之前数据库的初始状态一致,也可自行调整

4.sql文件比较大的时候,导入时间会变得很漫长,通过更改my.cnf中innodb_flush_log_at_trx_commit参数可以缩短导入的时间。曾做了一个实验,当此参数默认为1时,导入300M的sql文件耗时105s;设置为0时效率最高,耗时53s,但不安全,mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失,不建议;设置为2时,速度较快,耗时57s,比0安全,只有在操作系统崩溃或者系统断电的情况下,上一秒钟所有事务数据才可能丢失。

5.厂商提供的my.cnf里面为最安全设置:innodb_flush_log_at_trx_commit=1。若在上个步骤中为了导入备份的sql文件更快而修改了参数,应在完成导入后再改回nnodb_flush_log_at_trx_commit=1。

4.3修改主/备服务器配置文件

1、打开A服务器的my.cnf文件,在文件的[mysqld]下添加如下内容:

skip-name-resolve

server-id=1 #必须与其他服务器不同,且必须为1到232–1之间的一个正整数值

#i am master

log-bin=mysql-bin #二进制文件

binlog-do-db=student#要同步的数据库

binlog-ignore-db=information_schema#不同步的数据库

binlog-ignore-db=db_Im

binlog-ignore-db=db_Mdr

binlog-ignore-db=db_mrs

binlog-ignore-db=mysql

binlog-ignore-db=performance_schema

binlog-ignore-db=test

slave-skip-errors=1032,1062

2、重启A服务器的mysql服务

3、查看A服务器mysql的mysql库中user表

MySQL>use mysql;

MySQL>select user,host from user;

此时出现了配置好的登陆账号

4、打开B服务器的my.cnf文件,在文件的[mysqld]下添加如下内容:

skip-name-resolve

server-id=2

#i am master

log-bin=mysql-bin

binlog-do-db =student

binlog-ignore-db=information_schema

binlog-ignore-db=db_Im

binlog-ignore-db=db_Mdr

binlog-ignore-db=db_mrs

binlog-ignore-db=mysql

binlog-ignore-db=performance_schema

binlog-ignore-db=test

slave-skip-errors=1032,1062

5、重启B服务器的mysql服务

6、查看B服务器mysql的mysql库中user表

MySQL>use mysql;

MySQL>select user,host from user;

此时出现了配置好的登陆账号

4.4指定主/备服务器同步信息

1、在A服务器上查看当前二进制日志名和偏移量值:

MySQL> show master status;

记录Mysql二进制文件的file和position值

1、在A服务器上查看当前二进制日志名和偏移量值:

MySQL> show master status;

记录Mysql二进制文件的file和position值

2、在B服务器上指定更新点(根据上面样例修改MASTER_LOG_FILE值为样例结果中File的值;修改MASTER_LOG_POS值为样例结果中Position的值)

MySQL> stop slave;

MySQL> change master to master_host='192.168.1.10', master_port=3306, master_user='tongbu',master_password='',master_log_file='mysql-bin.000004',master_log_pos=1219;

MySQL> start slave;

3、在B服务器上查看当前二进制日志名和偏移量值:

MySQL> show master status;

记录Mysql二进制文件的file和position值

4、在A服务器上指定更新点(根据上面样例修改MASTER_LOG_FILE值为样例结果中File的值;修改MASTER_LOG_POS值为样例结果中Position的值)

MySQL> stop slave;

MySQL>change master to master_host='192.168.1.11',master_port=3306, master_user='tongbu',master_password='',master_log_file='mysql-bin.000003',master_log_pos=154;

MySQL> start slave;

5、重启A、B服务器的mysql服务

4.5检查数据库同步状态

1、查看A、B数据库同步状态, 输入以下命令,会出来很多信息,但是主要看Slave_IO_Running与Slave_SQL_Running这两个字段都显示yes就行了

MySQL> showslave status\G;

2、查看A、B服务器的同步线程状态,输入以下命令,

MySQL > showfull processlist;

若命令窗口有下图的三个线程,则证明配置成功,若缺少任何一个线程,请重启主、备两台机器的MySql服务,然后再在MySql命令窗口中输入上述命令查看是否有下图标注的三个线程。若没有这三个线程,请重新检查配置是否正确。

Ps:主备服务器的MySql进程必须都有这三个线程才证明配置成功。有时mysql的进程会比较多,需查看有这三个即可.

5.启动所有服务

mysql全量备份、增量备份。开启mysql的logbin日志功能。在/etc/my.cnf文件中加入以下代码:

重启mysql即可。其中路径 /home/mysql的用户和组要改成mysql。

2、增量备份

在/home/mysql/目录下建立以下目录:

增量备份脚本

赋予binlogbak.sh执行权限

3、全量备份

赋予databak.sh 执行权限

4、开启定时任务

使上述定时任务生效

查看定时任务

完成。

  • 发表于:
  • 原文链接:https://kuaibao.qq.com/s/20181203G0OMN400?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券