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、开启定时任务

使上述定时任务生效

查看定时任务

完成。