MySQL复制的实现

190次阅读

共计 4154 个字符，预计需要花费 11 分钟才能阅读完成。

1.MySQL 复制概念

指将主数据库的 DDL 和 DML 操作通过二进制日志传到复制服务器上，然后在复制服务器上将这些日志文件重新执行，从而使复制服务器和主服务器的数据保持同步。复制过程中一个服务器充当主服务器（master），而一个或多个其它服务器充当从服务器（slaves）。主服务器将更新重新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器、从服务器在日志中读取的最后一次成功更新的位置。从服务器接受从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

2. 复制的用途

通过主从复制（master-slave）的方式来同步数据，再通过读写分离（mysql-proxy）来提升数据库的并发负载能力，或者用来作为主备机的设计，保证在主机停止响应之后在很短的时间内就可以将应用切换到备机上继续运行。

优势：

（1）数据库集群系统具有多个数据库节点，在单个节点出现故障的情况下，其他正常节点可以继续提供服务。

（2）如果主服务器上出现了问题可以切换到从服务器上

（3）通过复制可以在从服务器上执行查询操作，降低了主服务器的访问压力，实现数据分布和负载均衡

（4）可以在从服务器上进行备份，以避免备份期间影响主服务器的服务。

3. 复制的实现（3 种方法）

（1）DRBD 是一种用软件实现的、无共享的、服务器之间镜像块设备内容的存储复制解决方案。

（2）Mysql cluster（又称 mysql 簇）。Mysql replicaion（复制）本身是一个比较简单的结构，即一台从服务器（slave）从一台主服务器（master）读取二进制日志然后再解析并应用到自身。

（3）一个简单复制环境只需要两台运行 mysql 的主机即可，甚至可以在一台物理服务器主机上启动两个 mysqld 实例。一个作为 master 而另一个作为 slave 来完成复制环境的搭配。但是在实际应用环境中，可以根据实际的业务需求利用 mysql 复制的功能自己搭建出其他多种更利于扩展的复制架构，如最常用的主从架构。

主从架构指的是使用一台 mysql 服务器作为 master，一台或多台 mysql 服务器作为 slave，将 master 的数据复制到 slave 上。在实际应用场合，主从架构模式是 mysql 复制最常用的。一般在这种架构下，系统的写操作都是在 master 中进行，而读操作则分散到各个 slave 中进行，因此这种架构特别适合现在互联网高读写的问题。

Mysql 数据库复制操作大概分为以下几个步骤：

（1）master 启用二进制日志。启用二进制日志的操作在日志管理中有详细的介绍。

（2）slave 上面的 I / O 进程连接上 master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容。

（3）master 接受到来自 slave 的 I / O 进程请求后，通过负责复制的 I / O 进程根据请求信息读取指定日志指定位置之后的日志信息，返回给 slave 的 I /O。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到 master 端的 bin-log 文件的名称以及 bin-log 的位置。

（4）Slave的 I/O 进程接收到信息后，将接收到的日志内容依次添加到 slave 端的 relay-log 文件的最末端，并将读取到的 master 端的 bin-log 的文件名和位置记录到 master-info 文件中。

（5）Slave的 sql 进程检测到 relay-log 中新增的内容后，会马上解析 relay-log 的内容，并在自身执行。

4.mysql 复制的集中模式

mysql5.1 之后的版本中，在复制方面的改进就是引进了新的复制技术——基于行的复制。这种技术就是关注表中发生变化的记录，而非以前的照抄 binlog 模式。从 mysql5.1.12 开始，可以用以下 3 种模式来实现。

（1）基于 sql 语句的复制（statement-base replication，sbr）

（2）基于行的复制（rbr）

（3）混合模式复制（mbr）

相应的，binlog 的格式也有 3 种：statement、row、mixed。Mbr 模式中，sbr 模式是默认的。在运行时可以动态地改变 binlog 的格式。设定主从复制模式的方法非常简单，只要在以前设定复制配置的基础上，再添加一个参数，如下：

binlog_format=”statement”#binlog_format=”row”#binlog_format=”mixed”

当然了，也可以在运行时动态修改 binlog 的格式

Mysql> set session binlog_format=”statement”

5. 控制主服务器操作

Master：192.168.11.139

Slave：192.168.11.130

（1）主服务器：

mysql> show variables like '%datadir%';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| datadir       | /application/mysql/data/ |
+---------------+--------------------------+

在主服务器上开启二进制日志：

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

OFF 表示二进制日志是关闭的

开启日志 3 步骤：

①开启 mysql 安装目录 /my.cnf

②找到 [mysqld] 这个标签，在此标签下面一行，添加语句如下：

log_bin[filename]

在该语句中，log-bin 说明要开启二进制文件；filename 是二进制日志的名字。如果没有指定，默认为主机名后面跟 -bin 作为文件名，默认存放在 datadir 目录中。在这里指定 binary_log 如果只对指定数据库生成二进制文件，则需要添加如下语句

Binlog-do-db=db_name（数据库名称）

如果不对指定数据库生成二进制文件日志，则需要添加如下语句

Binlog-ignore-db-db_name（数据库名称）

③重启 mysql 服务。可以在 mysql 安装目录 /data 文件夹下看到“binary_log. 数字编号”文件，如 binary_log.00001. 以后每重启一次 mysql 服务，都会重新生成二进制文件，文件名中的数字编号一次增加。

开机成功后，修改 mysql 的配置文件 my.cnf，设置 server-id，代码如下

Server-id=1
Binlog-do-db=xscj
Binlog-ignore-db=mysql
Server-id=1：每一个数据库服务器都要指定一个唯一的 server-id，通常主服务器为 1，master 和 slave 的 server-id 不能相同。Binlog-do-db：表示需要复制的数据库，这里以 xscj 为例
Binlog-ignore-db：表示不需要复制的数据库

在 master 上创建复制所需要的用户

mysql> grant replication slave on *.* to rep_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec


mysql> show master status\G
*************************** 1. row ***************************
            File: binary_log.000001
        Position: 303
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

将 master 主机的数据备份出来，保存在 /data/binary_dump.txt 文件中，然后导入到 slave 从机中去，具体执行语句如下

[root@localhost bin]# mysqldump -h localhost>/data/binary_dump.txt

（2）控制从服务器操作

修改从服务器的数据库配置文件，配置如下：

Server-id=2 ## 设置从服务器 id
Master-host=192.168.11.129
Master-user=rep_user
Master-password=   ## 设置连接主服务器的密码
Replicate-do-db  ## 设置你要同步的数据库，可以设置多个
Master-port=<port> ## 配置端口号

重启 slave，在 slave 主机的 mysql 重新执行如下命令，关闭 slave 服务
Mysql>stop slave;
设置 slave 实现复制相关的信息，执行如下命令
Mysql>change master to
>master_host=’’,
>master_user=’’,
>master_password=’’,
>master_log_file=’binary_log.000007’,
>master_log_pos=120;

输入:show slave status\G 用于提供有关从服务器线程的关键参数信息。

常用命令如下

选项	功能
Slave start	启动复制线程
Slave stop	停止复制线程
Reset slave	重置复制线程
Show slave status	显示复制线程状态
Show slave status\g	显示复制线程状态（分行显示）
Show master status\G	显示主数据库的状态（分行显示）
Show master logs	显示主数据库日志
Change master to	动态改变到主数据库的配置
Show processlistv	显示有哪些线程正在运行