mysql如何热备份 mysql 主从热备份

一. MySQL热备份主要分两种

1. 主从备份(单向)

主服务器中的数据实时备份到从服务器中，使得从服务器中的数据与主服务器一致。用户只允许对主服务器中的数据进行操作。

2. 主主备份(双向)

两个数据库服务器均同时做主与从，用户可以同时向两边操作数据，自动保持两数据库数据一致。

注意：采用自增的ID冲突(必须额外配置)

(
auto-increment-increment = 2 ####每次自增2(对应几台备份的服务器数量)
auto-increment-offset = 1 ####起始ID为1
)

二. 备份原理简介

简单的说就是把 一个服务器上执行过的sql语句在别的服务器上也重复执行一遍， 这样只要两个数据库的初态是一样的，那么它们就能一直同步。

当然这种复制和重复都是mysql自动实现的，我们只需要配置即可。

我们进一步详细介绍原理的细节， 这有一张图：

上图中有两个服务器， 演示了从一个主服务器(master) 把数据同步到从服务器(slave)的过程。

这是一个主-从复制的例子。 主-主互相复制只是把上面的例子反过来再做一遍。

对于一个mysql服务器， 一般有两个线程来负责复制和被复制。当开启复制之后。

1. 作为主服务器Master， 会把自己的每一次改动都记录到 二进制日志 Binarylog 中。 (从服务器会负责来读取这个log， 然后在自己那里再执行一遍。)

2. 作为从服务器Slave， 会用master上的账号登陆到 master上， 读取master的Binarylog, 写入到自己的中继日志 Relaylog， 然后自己的sql线程会负责读取这个中继日志，并执行一遍。 到这里主服务器上的更改就同步到从服务器上了。

在mysql上可以查看当前服务器的主，从状态。 其实就是当前服务器的 Binary(作为主服务器角色)状态和位置。 以及其RelayLog(作为从服务器)的复制进度。

例如在主服务器上查看主状态：

mysql> show master status\G

*************************** 1. row ***************************

File: mysql-bin.000002

Position: 742409

Binlog_Do_DB: mail_info

Binlog_Ignore_DB:

1 row in set (0.00 sec)

稍微解释一下这几行的意思：

1). 第一行表明 当前正在记录的 binarylog文件名是： binary-bin.000002.

可以在mysql数据目录下，找到这个文件

数据目录参见：

windows下 C:\ProgramData\MySQL\MySQL Server 5.6\data\ (或其他具体安装目录下)

linux下： /usr/local/mysql/data/

2). 第二行， 742409. 表示当前的文件偏移量， 就是写入在binary-bin.000002 文件的记录位置。

这两点就构成了主服务器的状态。 配置从服务器的时候，需要用到这两个值。 告诉从服务器从哪读取主服务器的数据。 (从服务器登录之后，会找到这个日志文件，并从这个偏移量之后开始复制。)

3). 第三行，和第四行，表示需要记录的数据库和需要忽略的数据库。 只有需要记录的数据库，其变化才会被写入到binary-bin.000002日志文件中。

我们还可以在从服务器上，查看从服务器的复制状态。

mysql> show slave status\G
2:  *************************** 1. row ***************************
3:                 Slave_IO_State: Waiting for master to send event
4:                    Master_Host: 192.168.1.229(参照具体IP)
5:                    Master_User: test(参照具体user)
6:                    Master_Port: 3306
7:                  Connect_Retry: 60
8:                Master_Log_File: binary-bin.000002
9:            Read_Master_Log_Pos: 742409
10:                 Relay_Log_File: ***-relay-bin.000003
11:                  Relay_Log_Pos: 742284
12:          Relay_Master_Log_File: binary-bin.000002
13:               Slave_IO_Running: Yes
14:              Slave_SQL_Running: Yes
15:                Replicate_Do_DB: [需要备份的数据库]
16:            Replicate_Ignore_DB:
17:             Replicate_Do_Table:[需要备份的数据表]
18:         Replicate_Ignore_Table:
19:        Replicate_Wild_Do_Table:[需要备份的数据表]
20:    Replicate_Wild_Ignore_Table:
21:                     Last_Errno: 0
22:                     Last_Error:
23:                   Skip_Counter: 0
24:            Exec_Master_Log_Pos: 742409
25:                Relay_Log_Space: 742459
26:                Until_Condition: None
27:                 Until_Log_File:
28:                  Until_Log_Pos: 0
29:             Master_SSL_Allowed: No
………………………………………..
Master_Server_Id: 1
…………………………………………

相关说明：

1). Master_host 指的是 主服务器的地址。

2). Master_user 指的是主服务器上用来复制的用户。 从服务器会用此账号来登录主服务。进行复制。

3). Master_log_file 就是前面提到的， 主服务器上的日志文件名.

4). Read_Master_log_pos 就是前面提到的主服务器的日志记录位置， 从服务器根据这两个条件来选择复制的文件和位置。

5). Slave_IO_Running: 指的就是从服务器上负责读取主服务器的线程工作状态。 从服务器用这个专门的线程链接到主服务器上，并把日志拷贝回来。

6). Slave_SQL_Running: 指的就是专门执行sql的线程。 它负责把复制回来的Relaylog执行到自己的数据库中。 这两个参数必须都为Yes 才表明复制在正常工作。

7). Replicate_Do_Table: 指当主数据库操作数据表时，它所在的数据库为默认数据库(use database)，后面的从数据库备份功能才能起效，否者对该数据表的操作不会热备份到从数据库中(不允许跨数据库更新表格)。对应在配置文件中的格式为replicate_do_table=dbname.tablename1 replicate_do_table=dbname.tablename2

8). Replicate_Wild_Do_Table: 不需要强制当前处理的数据库为默认数据库，始终会热备份对应数据表中的数据。配置文件格式如上。

三、主从热备份配置

【系统：windows 7 with MySQL5.6】

【注意事项：主服务器中的MySQL版本应该低于等于从服务器MySQL版本】

3.1 添加用于备份的用户并授予复制权限

mysql> grant replication slave on . to ‘xiaoming’@’192.168.1.666’ identified by ‘xiaoming666’;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

(备注： 创建一个用户 xiaoming，密码为 xiaoming666，并且给xiaoming用户授予REPLICATION SLAVE 权限，用于建立复制时所需要的用户权限，也就是slave必须被master授权具有该权限的用户，才能通过用户复制, 192.168.1.666 为从服务器IP)

3.2 配置主服务器中的my.ini文件
文件位置：
Windows： 默认安装 C:\ProgramData\MySQL\MySQL Server 5.6\my.ini 或者查看MySQL 5.6 Command Line Client 属性中目标里面的值 "--defaults-file=C:\ProgramData\MySQL\MySQL Server 5.6\my.ini"
Linux: /etc/my.cnf配置内容：
……
[mysqld]
……
# Binary Logging.
log-bin = binary_log
binlog-format = mixed
read-only = 0
binlog-do-db = mail_info
#binlog-ignore-db = ……
……
# Server Id.
server-id=1  ######### ID 必须唯一 #########
……
重启MYSQL56服务，然后查看master状态
mysql>show master status\G
提示相应信息。
3.3 配置从服务器数据库
my.ini文件修改server-id
……
[mysqld]
……
#备份指定数据表(否则备份数据库下的所有数据表)
replicate_wild_do_table=dbname.tablename1(mail_info.mail_ope_info)
replicate_wild_do_table=dbname.tablename2(根据实际情况确定copy几张表)
......
# Server Id.
Server-id = 2 (必须唯一)
MySQL 5.6 Command Line Client 命令行输入
mysql>change master to
>master_host = ‘192.168.1.229’,
>master_user = ‘test’,
>master_password = ‘test’,
>master_log_file = ’binary_log.000002’,
>master_log_pos = 120;
备注： host为主服务器IP， user和password 为主服务器中创建用于备份的， log file和position则记录从什么位置开始进行备份。(最后两项视实际情况而定)
重启从服务器中MYSQL56服务
检查此时data目录下会多出一个文件master.info
检查slave状态：
mysql>start slave;
mysql>show slave status\G
此时对主服务器中数据库进行操作的话会实时同步到从数据库中。
停止备份
mysql>stop slave;
-----------------------------------

本文章为转载内容，稍微改动了一些 ，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。