mysql数据库远程同步及备份

拼搏现实的明天。 2022-05-23 05:45 289阅读 0赞

# Mysql数据库复制及备份 #

## 一、mysql数据库复制概述 ##

复制解决的基本问题是让一台服务器的数据和另外的服务器保持同步。一台主服务器可以连接多台从服务器，并且从服务器也可以反过来作为主服务器。主服务器和从服务器可以位于不同的网络拓扑中，还能对整台服务器、特定的数据库，甚至特定的表进行复制。

Mysql复制大部分都是向后兼容的。这意味着版本较新的服务器可以是版本较老的服务器的从服务器。但老版本的服务器通常不能是新版本号的服务器的从服务器。复制通常不会大幅增加主服务器的开销。它需要主服务器启用二进制日志，这或许会有较大的开销，但是出于备份的目的，无论如何这种开销都是需要的。

复制通常的用途有：数据分布、负载平衡、备份、高可用性和故障转移、测试mysql升级。

## 二、复制的工作原理 ##

总体上来说，复制有以下三个步骤：

(a)主服务器把数据更改记录到二进制日志中。（这叫做二进制日志事件（binary log events）。）

(b)从服务器把主服务器的二进制日志事件拷贝到自己的中继日志（relay log）中。

(c)从服务器重放中继日志中的事件，把更改用到自己的数据上。

这三个步骤如下图所示：

![1349840448_2835.jpg][]

第一步：在主服务器上记录二进制日志。在每个更新数据的事务完成之前，主服务器都会把数据更改记录到二进制日志中。即使事务在执行期间是交错的，mysql也会串行地把事务写入到二进制日志中。在把事件写入到二进制日志之后，主服务器告诉存储引擎提交事务。

第二步：从服务器把主服务器的二进制日志拷贝到自己硬盘上，进入所谓的“中继日志（relay log）中。

它首先启动一个工作线程，叫I/O线程。这个I/O线程开启一个普通的客户端连接，然后启动一个特殊的二进制日志转储进程。这个转储进程从主服务器的二进制日志中读取事件。它不会对事件进行轮询。如果它跟上了主服务器，就会进入休眠状态，并等待有新事件发生时主服务器发出的信号。I/O线程把事件写入从服务器的中继日志中。

第三步：SQL从线程处理了该过程的最后一部分。该线程读取了中继日志，并且重放其中的事件，然后更新从服务器的数据。由于这个线程能跟上I/O线程，所以，中继日志一般都是在操作系统的缓存中，因此中继日志的开销很低。同时SQL线程执行的事件也可以被写入从服务器自己的二进制日志中。

## 三、创建复制以及配置主、从服务器 ##

创建复制主要分为下面几个步骤：

1、  在每一台服务器上建立复制账号。

2、  配置主、从服务器。

3、  指导从服务器进行连接与复制。

下面以实际操作的例子来进一步说明复制的创建。

1、  平台说明：

系统环境：ubuntu10.10

软件版本：mysql-5.5.25

2、  Mysql数据库说明：

在两台不同的计算机里面安装好mysql服务器。

主服务器IP：192.168.1.100

从服务器IP：192.168.1.104

下面将按照创建复制的步骤进行说明

第一步：在每一台服务器上建立复制账号

Mysql有一些特殊的权限允许复制进程运行。从I/O线程运行在从服务器上，它创建了到主服务器的连接。基于此，在主服务器上就必须创建一个用户账户并且给它合适的权限，这样I/O线程就可以以这个用户的身份连接到主服务器，并且读取它的二进制日志。

![1349840484_6326.jpg][]

上述命令在主服务器上创建一个用户名为repl的账号。这里把用户限制到了本地网络。

第二步：配置主、从服务器

修改主服务器的配置文件my.cnf：加入如下设置

**sync\_binlog = 1**

**log-bin      =   mysql-bin**

**server-id=   10**

**innodb\_support\_xa  =  1**

在主服务器上最重要的二进制日志设置是sync\_binlog，这使得mysql在每次提交事务的时候把二进制日志的内容同步到磁盘上，即使服务器崩溃也会把事件写入日志中。

如果不想在服务器崩溃后表被破坏，所以我们使用mysql默认的存储引擎InnoDB。

而且必须显式地定义唯一的服务器ID。这里为了避免产生混淆，用10代替1。因为1通常是服务器的默认值。所以使用1容易引起混淆，并导致和没有服务器ID的服务器冲突。

如果log\_bin选项在主服务器的配置文件中没有定义，那就重启mysql。

验证二进制日志文件已经被创建出来，可以运行如下指令：

![1349840542_5116.jpg][]

从服务器也需要和主服务器类似的配置，并且也需要在从服务器上重启mysql。

**log-bin=mysql-bin**

**server-id  = 2**

**relay\_log  = mysql-relay-bin**

**log\_slave\_updates = 1**

**read\_only  = 1**

**skip\_slave\_start**

以上配置单从技术上来说，有些选项是不需要的。实际上，从服务器只有server\_id参数是必须的，这里我们也开启了log\_bin，并且显式地给了二进制文件一个名字。同时，把主、从服务器的文件名设置成一样的，这样做的目的是便于把从服务器变为主服务器。

此外，还添加了两个其他的可选配置参数：relay\_log（定义中继日志的位置和文件名）和log\_slave\_update（使从服务器把复制的事件记录到自己的二进制日志中）。

Skip\_slave\_start选项会阻止从服务器在崩溃后自动启动，可以让管理员有机会去修复服务器。

第三步：指导从服务器进行连接与复制

这一个步骤是告诉从服务器如何连接到主服务器并中继其中二进制日志。这一个步骤中，不要直接在配置文件my.cnf里配置，而是应该使用CHANGE MASTER TO命令，在从服务器上输入如下所示命令：

![1349840572_3228.jpg][]

参数说明：

MASTER\_HOST：指主服务器IP。

MASTER\_USER：指复制账号用户名。

MASTER\_PASSWORD：指复制用户密码。

MASTER\_LOG\_FILE：指复制的二进制日志名。

MASTER\_LOG\_POS：指二进制日志的偏移量。

输入上述指令没有出错的话，将会有成功的提示。

检测输出：

![1349840603_2909.jpg][]

输入上面这条命令，将会出现如下所示内容：

![1349840649_3210.jpg][]

由上图可以看到，此时，从服务器I/O线程和SQL线程正在运行，并且Seconds\_Behind\_Master不再是NULL（如果想看到是NULL的话，就先STOP SLAVE；然后再SHOW SLAVE STATUS\\G）。I/O线程正在等待主服务器的事件，这意味着它已经提取了主服务器的所有二进制日志。日志位置已经增加了，表示一些事件已经提取并执行过了。这时，如果在主服务器上做一些数据更改，那么将会看见从服务器上的文件和位置参数都增加了。些时就可以在从服务器上看到数据库改变了。

查看主、从服务器工作线程：

![1349840724_7183.jpg][]

当然啦，这里显示的例子，是来自运行了一些日子的服务器，所以I/O线程Time列值比较大。从服务器的SQL线程已经空闲了11秒，这表示已经有11秒没有发生事件重放了。Info显示正在执行的查询，目前正在执行的查询为NULL。

前面所述都是假设主、从服务器刚刚安装好，使用的数据都是默认的初始化数据，所以两台服务器默认的有相同数据，并且也知道了主服务器的二进制日志。但如果是主服务器已经运行了一段时间了，然后用一台新安装的从服务器进行同步，那么此时，从服务器还没有主服务器的数据。

所以，在复制数据之前，必须用主服务器初始化一台从服务器。为了让从服务器和主服务器同步数据，需要下面三个条件：

1、  某个时间点上主服务器数据的快照。

2、  主服务器当前的日志，以及从快照时间点以来精确的日志字节偏移量。这两个值叫做日志文件坐标，它们共同确定了二进制日志的位置。可以使用SHOW MASTER STATUS查找主服务器的日志文件坐标。

3、  从快照时间到现在的主服务器二进制日志文件。

这里，由于我们只用了mysql默认的存储引擎InnoDB，所以，可以使用下面的命令来转储主服务器的所有内容，把它装入从服务器中，并且把从服务器的坐标改成主服务器二进制日志的相应位置：

**$mysqldump –h 192.168.1.100 –u repl –p123456 –single-transaction –all-databases --master-data=0 | mysql –p**

这样，就可以把主服务器上的数据同步到从服务器上了。

至此，当主服务器上有数据更改，从服务器上的数据也会发生更改。我们管这种数据复制模式叫做主—从模式。

但现实中，通常需要的是两台服务器，当其中一台服务器的数据发生变化，将会把数据同步到另一台服务器中。这种模式叫主—主模式。

配置这种模式，只要将前面所述的主、从服务器配置反过来设置一次就可以了。主服务器和从服务器的设置是对称的。

当设置好后，就可以实现主—主同步复制了。

## 四、其他复制模式简介 ##

### 1、一主多从模式 ###

刚才配置从服务器的时候加入了log\_slave\_updates选项，这样就可以把一台从服务器变成主服务器了，它会指导mysql把自己执行的事件写到二进制日志中，然后自己的从服务器就可以取得这些事件并执行它。如下图所示：

![1349840828_8100.jpg][]

在这种情形下，主服务器的改变导致事件被写入到二进是中。第1个从服务器提取并执行这个事件。

一般来说，这个时候事件的生命周期就结束了，但是因为log\_slave\_updates 打开了，所以，从服务器就会把它写入到自己的二进制日志中。那么第2个从服务器就可以再次取得这个事件并执行它了。

### 2、复制过滤器 ###

在主、从服务器的数据复制同步过程中，我们可以有选择性的复制同步一部份数据，这时就要用到复制过滤器。复制过滤器有两种：

(a)主服务器上把事件从二进制日志中过滤掉的过滤器。

(b)从服务器上把事件从中继日志中过滤掉的过滤器。

如下图所示：

![1349840876_8131.jpg][]

控制二进制日志过滤的选项是binlog\_do\_db和binlog\_ignore\_db。但通常不要开启它们。

在从服务器上，replicate\_\*选项在从服务器SQL线程从中继日志中读取事件的时候进行过滤。可以复制，或者忽略一个或多个数据库。

### 3、复制拓扑 ###

Mysql数据库的复制拓扑中，必须遵守下面的基本原则：

(a)一个mysql从服务器只能有一个主服务器。

(b)每个从服务器有唯一的服务器ID。

(c)一个主服务器可以有很多从服务器。

(d)如果打开了log\_slave\_update，一个从服务器就能把主服务器的改动传播下去，并且能成为其他从服务器的主服务器。

下面列出一部份复制拓扑的结构示意图：

一台主服务器和多个从服务器

![1349840910_8148.jpg][]

拥有从服务器的环形结构

![1349840996_8159.jpg][]

除上述所讲到的复制拓扑外，不家其他一些拓扑，这里不一一叙述。具体在现实工作环境中，要使用哪一个复制模式，将要按照需求进行选择配置。

## 五、备份与还原 ##

备份数据是数据库管理中最常用的操作。为保证数据库中数据的安全，数据库管理员需要定期的进行数据库备份。当数据库遭到破坏时，通过备份文件来还原数据库。

### 1、使用mysqldump命令备份 ###

Mysqldump命令可以将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。

Mysqldump命令的工作原理：它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一个INSERT语句。这些CREATE语句和INSERT语句都是还原时使用的。还原数据时就可以使用其中的CREATE语句来创建表，使用其中的INSERT语句来还原数据。

使用mysqldump命令备份一个数据库的基本语法如下：

**Mysqldump –u username –p dbname table1 table2……>BackupName.sql**

参数说明：

Dbname：参数表示数据库的名称；

Table1和table2：表示表的名称，没有该参数时将备份整个数据库；

BackupName.sql：表示备份文件的名称，文件名前面可以加上一个绝对路径。一般都会将数据库备份成一个后缀名为sql的文件。

但是mysqldump命令备份的文件并非一定要求后缀名为.sql，备份成其他格式的文件也是可以的，但是通常情况下是备份成后缀名为.sql的文件，因为，后缀名为.sql的文件给人的第一感觉就是与数据库有关的文件。

使用mysqldump备份多个数据库：

**Mysqldump –u username –p –databases dbname1 dbname2…… > BackupName.sql**

可以看到，备份多个数据库和备份一个数据库不同，备份多个数据库要加上“\--databases”这个选项，然后后面跟多个数据库的名称。

使用mysqldump备份所有的数据库：

**Mysqldump –u username –p –all-databases > BackupName.sql**

### 2、远程备份 ###

远程备份可以分为两种情况：

第一：两台服务器没有进行数据同步设置、从服务器数据跟不上主服务器数据更新或者设置数据同步的时候不是完全数据同步而只份数据同步。

第二：两台服务器进行了数据同步设置、服务器数据几本上不落后主服务器数据并且设置数据同步的时候是完全数据同步。

下面先讨论第一种情况。假设，需要备份的服务器主机IP为：192.168.1.100，首先在该主机上为mysql系统添加root远程登录权限：

**mysql> grant all privileges on \*.\* to** [**root@'%'**][root]** identified by '123456;**

此时在远端的服务器上就可以登录主机服务器了：

**Mysql –h 192.168.1.100 –u root –p**

然后输入密码后就可以登录进主服务器的root用户了。

远程备份全部数据库：

** Mysqldump –h 192.168.1.100 –u root –p123456 –all-databases >/home/Backup.sql**

这样就可以把远程主机上的数据库数据备份到本地设备上了。

第二种情况比较简单，因为两台服务器数据是完全同步的，也就是说两台服务器上的数据库数据是一致的，所以，对于这种情况，我们只需要用mysqldump命令按本地备份模式进行备份就可以了。

### 3、数据还原 ###

当管理员的非法操作和计算机的故障等都会破坏数据库文件。当数据库遭到这些意外时，可以通过备份文件将数据库还原到备份时的状态。这样可以将损失降低到最小。

前面说了，我们是用mysqldump命令将数据库中的数据备份成一个文本文件。并且通常来说这个文件会以.sql后缀名结尾。需要还原的时候，可以使用mysql命令来还原备份的数据。

备份文件中通常包含CREATE语句和INSERT语句。Mysql命令可以执行备份文件中的CREATE语句和INSERT语句。通过CREATE语句来创建数据库和表。通过INSERT语句来插入备份的数据。从而达到还原的目的。

Mysql命令的基本语法如下：

**Mysql –u root –p \[dbname\] < backup.sql**

参数说明：

Dbname参数表示数据库名称。该参数可选，可以指定数据库名，也可以不指定。指定数据库名时，表示还原该数据库下的表。不指定数据库名时，表示还原特定的一个数据库。

[1349840448_2835.jpg]: https://img-my.csdn.net/uploads/201210/10/1349840448_2835.jpg
[1349840484_6326.jpg]: https://img-my.csdn.net/uploads/201210/10/1349840484_6326.jpg
[1349840542_5116.jpg]: https://img-my.csdn.net/uploads/201210/10/1349840542_5116.jpg
[1349840572_3228.jpg]: https://img-my.csdn.net/uploads/201210/10/1349840572_3228.jpg
[1349840603_2909.jpg]: https://img-my.csdn.net/uploads/201210/10/1349840603_2909.jpg
[1349840649_3210.jpg]: https://img-my.csdn.net/uploads/201210/10/1349840649_3210.jpg
[1349840724_7183.jpg]: https://img-my.csdn.net/uploads/201210/10/1349840724_7183.jpg
[1349840828_8100.jpg]: https://img-my.csdn.net/uploads/201210/10/1349840828_8100.jpg
[1349840876_8131.jpg]: https://img-my.csdn.net/uploads/201210/10/1349840876_8131.jpg
[1349840910_8148.jpg]: https://img-my.csdn.net/uploads/201210/10/1349840910_8148.jpg
[1349840996_8159.jpg]: https://img-my.csdn.net/uploads/201210/10/1349840996_8159.jpg
[root]: mailto:root@'%20rel=