mysql的主从复制及实现过程

浏览数：23 / 时间：2015年06月12日

mysql的主从复制

为什么需要用到mysql的主从复制功能？

一般在实际生产中，仅仅有一台数据库服务器作为独立的数据库是很少见的，因为一旦这台独立的服务器挂掉，那么前端的程序或用户将无法访问后端的数据了，这样会造成服务中断的。对于有些企业来说，保证服务的连续性是非常重要的，因此在这种情况下需要保证数据库服务器的高可用性。

有时某个数据库服务器台过于繁忙的话，我们需要减轻服务器的负载，那么我们可以将这个服务器上的写请求和读请求分开，分别由不同的服务器来完成，这样岂不是更好。对于一个数据库服务器而言，保证数据的安全性是至关重要的，因此我们需要定期的对数据库上的数据做备份。

如果使用热备方式的话，操作起来可能不太容易，使用冷备的话又不能保证其正常服务。如果能有一台数据库服务器上面也存有相同的数据，但是我们却可以手动将数据库上的服务停止掉，然后在将该数据库上面的数据备份起来。等等这些功能，我们都可以利用mysql的主从复制功能来完成。

mysql的主从复制原理

在mysql主从复制架构中，有一台服务器作为MASTER服务器，该服务器负责所有的读请求和写请求。另外一台或多台作为slave服务器。当master上的某个应用程序发起写请求时，该请求会被内核响应并在内核中执行，然后在将其数据写入到磁盘中去。并且将此次的操作以事件的形式记录到二进制文件中去。

此时master上的Binlog dump thread等待slave上的I/O thread连接请求。一旦slave I/O thread连接上了master的Binlog dump thread，则Binlog dump thread会将本地二进制文件中更新的事件复制给slave。

当slave上的I/O thread接受从master复制过来的二进制文件中的事件时，会将其事件写入到slave上的中继日志文件中。然后slave会调用本地的SQL thread重新应用（或回放）中继日志中的事件，然后在将其数据写入到slave上的磁盘中。这就是mysql的主从复制功能。

由于slave上更新的数据不能复制给master，因此，对于slave而言，不能执行写操作。切记切记！！！

mysql的主从复制原理如下图所示：

复制线程

Binlog dump thread：接受slave I/O thread的连接请求，并将master上的二进制文件中更新的事件复制给slave I/O thread。Binlog dump thread位于master上。

slave I/O thread：接受Binlog dump thread复制过来的时间，并将其写入到slave上的中继日志文件中。slave I/O thread位于slave上。

slave SQL thread：读取中继日志文件中的事件重新应用（或回放），并将其数据写入到磁盘中去。

mysql的主从复制具有如下功能：

1、实现mysql数据库的高可用性

2、可以辅助实现数据备份

3、可以异地容灾

4、实现服务器分摊负载，即通过mysql的读写分离来实现分摊负载。

mysql的主从复制需要注意的事项：

一个master可以用于多个slave，而一个slave只能属于某一个master

在主从架构中，如果只有一个slave，那么在slave上重新回放中继日志文件中的事件会引起数据库的改变，因此，这个过程也会以事件的形式写入到二进制日志文件中去。由于二进制日志是用来做及时点还原的，且master保存着一份完整的数据，因此在slave上不需要开启二进制日志功能，但是master上必须要开启。

如果有多个slave的情况下，在这多个slave中，有一个slave即当做master的slave又可以当做其他slave的master，那么此时这个slave上必须开启二进制日志功能。即此时这个slave即充当slave又充当master。

mysql复制过程中数据是以明文的方式进行传输的，不过可以将其配置为基于ssl来传输。

mysql的三种复制模式

mysql的异步模复制式

这是mysql的默认复制模式。异步复制模式就是当master上的写操作完成后，即数据已写入到磁盘上，事件也入到到二进制日志文件中去了。master上的dump thread将其更新的二进制日志中的事件复制给slave I/O thread并被其接受时，则认为复制成功。这种复制模式并不能保证复制过去的事件一定能写入到slave上的中继日志中，因此，可能会导致slave上的数据丢失。导致slave和master上的数据不一致。

而且一般说来，数据的读写操作都是由master先完成的，然后在将其更新的事件复制给slave，因此，数据的同步并不是同时进行的，一般slave写入数据到磁盘都会比master要慢一些，这取决于网络带宽、服务器负载等因素。不过异步复制模式的好处就是可以提升master的性能。

mysql的半同步复制模式

在mysql5.5之后的版本中，引入半同步的概念。不过要想让mysql5.5支持半同步，必须要安装由google提供的一个开源补丁才行。

mysql的半同步复制模式就是当master上的写操作完成后，master上的dump thread会将其更新的二进制日志中的事件复制给slave，并且这些事件至少能够在一个slave上正常写入到中继日志中，且中继日志中的事件经过回放将其数据写入到磁盘中。则认为复制操作成功。即至少有一个slave完成写操作就表示所有的复制操作成功了。

在这个过程中，如果没有一个slave能够将其复制过来的事件写入到中继日志中，则master会一直等待，当这个等待超时时长（timeout，默认为10s）过后，则半同步复制模式转变成异步复制模式。

mysql的同步复制模式

mysql的InnoDB和MyISAM引擎默认不支持这种复制模式。所谓同步复制模式即master和slave上的写操作都完成后，则表示复制成功。

mysql的复制过滤

有时候我们不想接受从其他服务器复制过来的所有数据，只是想选择性的接受我们自己想要的数据，则就可以使用mysql的复制过滤功能。mysql的复制过滤既可以在master上使用，又可以在slave上使用，但是如果在master上配置复制过滤功能会导致其二进制日志的完整性，不利用数据及时点恢复。因此，我们一般不再master上配置复制过滤功能，一般配置在slave上。

mysql的主从复制实验过程

实验前提：

这里我使用的linux系统为RHEL 5.8 64bit

本次实验共2台主机，其ip地址分别为：

master.xsl.com 192.168.108.202

slave.xsl.com 192.168.108.150

主从复制

这里使用的是默认的复制模式，即异步复制模式

一、master上的配置

1、安装mysql

这里我使用的mysql-5.5.28，关于mysql的安装方式将不再这里介绍了。

2、修改mysql的配置文件，并添加如下信息：

#vim /etc/my.cnf

如下信息全部添加到[mysqld]这个容器中

datadir=/mydata/data #指定mysql的存储目录

log-bin=master-bin #指定master上二进制日志文件

log-bin-index=master-bin.index 指定二进制日志的索引文件

server-id = 1 指定master的id号，这个id号一定要是唯一的。server-id的有效取值为[1-2^32-1]

innodb_file_per_table = 1 如果mysql的工作引擎是innodb的话，那么就指定配一个表使用一个表空间

3、启动mysqld服务

#service mysqld start

4、修改环境变量

如果在第一步中已经修改了环境变量，那么这一步就不需要操作了。如果还没有修改，那么想修改环境变量可以这么做。

编辑/etc/profile.d/mysql.sh,并添加如下内容：

#vim /etc/profile.d/mysql.sh

export PATH=$PATH:/usr/local/mysql/bin

执行如下命令，可以让内存重读环境变量

#. /etc.profile.d/mysql.sh

5、创建具有复制权限的用户

#mysql -uroot -p

mysql> grant replication slave on *.* to ‘repluser‘@‘192.168.108.%‘ identified by ‘repluser‘;

让内存重读授权表

mysql> flush privileges;

6、查看授权信息

mysql> show grants for ‘repluser‘@‘192.168.108.%‘;

+---------------------------------------------------------------------------------------------------------------------------------+

| Grants for [email protected]% |

+---------------------------------------------------------------------------------------------------------------------------------+

| GRANT REPLICATION SLAVE ON *.* TO ‘repluser‘@‘192.168.108.%‘ IDENTIFIED BY PASSWORD ‘*89A63F9688240669B54B5C2649EEFB795850597E‘ |

+---------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

二、slave上的配置

1、安装mysql，这里安装的也是mysql-5.5.28，安装过程和上面一样。

2、修改mysql的配置文件，并添加如下信息：

#vim /etc/my.cnf

如下信息全部添加到[mysqld]这个容器中

datadir=/mydata/data

relay-log=relay-log 指定中继日志文件

relay-log-index=relay-log.index 指定中继日志索引文件

server-id=11 记住server-id一定要是唯一的.server-id的有效取值为[1-2^32-1]

innodb_file_per_table = 1 如果工作引擎是innodb的话，建议开启一个表使用一个表空间功能

read_only = on 由于slave上不能进行写操作，因此，需要设定为只读操作。不过这个选项，对于具有super权限的用户无效。

sync_log = 1 为了保证事务的安全性及二进制日志的完整性，还需要设定每一次写操作都同步二进制日志一次。

3、启动mysqld服务

#service mysqld start

4、修改环境变量

如果在第一步中已经修改了环境变量，那么这一步就不需要操作了。如果还没有修改，那么想修改环境变量可以这么做。

编辑/etc/profile.d/mysql.sh,并添加如下内容：

#vim /etc/profile.d/mysql.sh

export PATH=$PATH:/usr/local/mysql/bin

5、连锲master服务器

在连接master服务器之前，我们还需要查看一下master正在使用的二进制日志文件是哪一个，以及二进制日志文件中事件的相对位置。这样我们只需要复制这个二进制日志文件中这个position以后的事件即可。

查看master正在使用的二进制日志文件以及事件所处的相对位置，可以在master上使用如下命令完成。

mysql> show master status;

+-------------------+----------+--------------+------------------+

+-------------------+----------+--------------+------------------+

| master-bin.000001 | 344 | | |

+-------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

查看完成之后，我们在连接master服务器

#mysql -uroot -p

mysql> change master to master_host=‘192.168.108.202‘,master_port=3306，master_log_file=‘master-bin.000001‘,master_log_pos=344,master_user=‘repuser‘,master_password=‘repuser‘;

这里的master_log_file就是用来指定我们要复制master上的哪个二进制日志文件中的事件，msater_log_pos用来指定复制二进制日志文件这个相对位置以后的事件。

master_user就是在master上创建的具有复制权限的用户，master_password就是这个用户的密码

6、启动slave上的I/O线程和SQL 线程

mysql> start slave ;

使用这个命令会将slave I/O线程和slave SQL线程同时开启。如果想手动的开启某个线程可以使用如下命令来完成：

mysql> start slave IO_Thread ; 启动I/O线程

mysql> start slave SQL_Thread ; 启动SQL线程

7、查看slave上的I/O线程和SQL线程是否启用，以及slave上的其他相关信息

mysql> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.108.202

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000001

Read_Master_Log_Pos: 344

Relay_Log_File: relay-log.000002

Relay_Log_Pos: 254

Relay_Master_Log_File: master-bin.000001

Slave_IO_Running: Yes #这里已经启动了IO线程

Slave_SQL_Running: Yes #这里已经启动了SQL线程

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 344

Relay_Log_Space: 404

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

1 row in set (0.00 sec)

ERROR:

No query specified

三、测试，在master上添加数据，观看数据是否会复制到slave上。

在master上创建一个数据库testrepl

mysql> create database testrepl;

查看master上所有库；

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

| testrepl |

+--------------------+

5 rows in set (0.00 sec)

master上已经创建了testrepl库

看看，在master上刚刚创建的testrepl库是否会复制到slave上去。

在slave上使用如下命令查看slave上的所有库：

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

| testrepl |

+--------------------+

5 rows in set (0.00 sec)

看看，果然master上的testrepl这个库复制过来了，说明mysql的主从复制功能已经实现了。

说明：在slave上的数据目录下即/mydata/data/下，有2个文件master.info和relay-log.info 。这两个文件都是文本文件，其中master.info保留的是连接到master主机上的一些信息，因此，当mysql开启自启动或重启时会自动连接到master上去。一般建议mysql自启动时不要自动连接到master上。而relay-log.info保留的是当前使用的中继日志文件和master上正在使用的二进制日志文件以及这些文件中事件的相对位置。

如果不要让slave开机就直接连上master，可以将这两个文件放置在其他目录下；或者在配置文件中添加skip-slave-start=on等参数都可以来实现。

配置半同步模式的主从复制（这里使用的复制模式为半同步复制模式）

如果在主从复制中要想使用半同步复制模式，还需要如下步骤：

mysql的半同步复制模式是在mysql-5.5以后才有的，不过要想使用半同步复制模式，还要安装由google提供的一些开源插件，这些插件位于安装目录下的/lib/plugin/下，在我的系统上则是位于/usr/local/mysql/lib/plugin/下。

在这个目录下有2个插件，分别是semisync_master.so， semisync_slave.so。其中semisync_master.so是master上的插件，semisync_slave.so是slave上的插件。分别在各自的服务器上安装即可。

一、在master上的配置

1、在master上安装插件

mysql> install plugin rpl_semi_sync_master soname ‘semisync_master.so‘;

这里的rpl_semi_sync_master soname为插件名称，semisync_master.so为模块名称

删除插件的语法格式为：

UNINSTALL PLUGIN plugin_name

2、设置全局变量，启用半同步复制功能，并设定超时时长

mysql> set global rpl_semi_sync_master_enabled=1;

mysql> set global rpl_semi_sync_master_timeout=10000; 设定超时时长为10秒，默认单位为毫秒

3、查看与半同步复制相关的变量信息

mysql> show global variables like ‘%semi%‘;

+------------------------------------+-------+

| Variable_name | Value |

+------------------------------------+-------+

| rpl_semi_sync_master_enabled | ON |

| rpl_semi_sync_master_timeout | 10000 |

| rpl_semi_sync_master_trace_level | 32 |

| rpl_semi_sync_master_wait_no_slave | ON |

+------------------------------------+-------+

4 rows in set (0.00 sec)

二、slave上的配置

1、在slave上安装插件

mysql> install plugin rpl_semi_sync_slave soname ‘semisync_slave.so‘;

2、启用半同步复制功能

mysql> set global rpl_semi_sync_slave_enabled =1;

查看设置是否成功

mysql> show global variables like ‘%semi%‘;

+---------------------------------+-------+

| Variable_name | Value |

+---------------------------------+-------+

| rpl_semi_sync_slave_enabled | ON |

| rpl_semi_sync_slave_trace_level | 32 |

+---------------------------------+-------+

2 rows in set (0.01 sec)

3、启动slave上的两个进程

mysql> slave stop；

mysql> slave start；

4、验证半同步复制是否生效

在master上执行如下命令，可以查看半同步复制是否生效

mysql> show global status like ‘%semi%‘;

+--------------------------------------------+-------+

| Variable_name | Value |

+--------------------------------------------+-------+

| Rpl_semi_sync_master_clients | 1 | 说明已经有一个客户端连接上来了

| Rpl_semi_sync_master_net_avg_wait_time | 0 |

| Rpl_semi_sync_master_net_wait_time | 0 |

| Rpl_semi_sync_master_net_waits | 0 |

| Rpl_semi_sync_master_no_times | 0 |

| Rpl_semi_sync_master_no_tx | 0 |

| Rpl_semi_sync_master_status | ON | 说明半同步复制功能已开启

| Rpl_semi_sync_master_timefunc_failures | 0 |

| Rpl_semi_sync_master_tx_avg_wait_time | 0 |

| Rpl_semi_sync_master_tx_wait_time | 0 |

| Rpl_semi_sync_master_tx_waits | 0 |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |

| Rpl_semi_sync_master_wait_sessions | 0 |

| Rpl_semi_sync_master_yes_tx | 0 |

+--------------------------------------------+-------+

14 rows in set (0.00 sec)

5、在master上创建表bincp,并验证在slave上是否存在

mysql> use testrepl

mysql> create table bancp (id int)；

mysql> show tables;

+--------------------+

| Tables_in_testrepl |

+--------------------+

| bancp |

+--------------------+

1 row in set (0.00 sec

在slave上验证，是否存在表bincp

mysql> use testrepl

Database changed

mysql> show tables;

+--------------------+

| Tables_in_testrepl |

+--------------------+

| bancp |

+--------------------+

1 row in set (0.00 sec)

说明表复制成功，mysql的半复制功能也设置成功了。

如果在slave上停止其IO线程和SQL线程，然后在创建一个数据库的话，你会发现创建需要好长时间，这个时间就是我们在master上设置的超时时长。一旦第一次连接超时，则就会自动切换成异步模式。

说明：刚刚在上面修改全局变量的操作都是在命令行中完成的，不会永久生效的，要想永久生效，需要将其变量写入到配置文件中去。

mysql的复制过滤功能的实现过程

一般说来，如果在master上做过滤的话，会影响到二进制日志的完整性的，会对以后做及时点恢复有影响的，因此，一般我们不会在master上做过滤。只在slave上做过滤。

如果slave上不想接受某些库或表的话，可以在slave上做相应的配置，这样slave就不会接受master上某些库或指定表的相关数据了。

如果要在slave上开启过滤功能，还需要配置如下步骤：

1、在slave上的配置文件中，且在[mysqld]这个容器中添加如下参数：

#replicate_do_db=db_name 表示只接受某个数据库

#replicate_ignore_db=db_name 表示不解释某个数据库

#replicate_do_table=db_table.tb_name 表示只接受指定库的某个表

#replicate_ignore_table=db_table.tb_name 表示不接受指定库的某个表

#replicate_wild_do_table=db_name.test% 对于表来说，还可以通配符来进行匹配。这里表示只接受指定库中以test开头且test后面为任意长度的任意字符的所有表

#replicate_wild_ignore_table=db_name.test_ 这也是使用通配符来进行匹配的。这里表示不接受指定库中以test开头且后面为任意单个字符的所有表

replicate_ignore_table=testrepl.xsl

说明如果要指定多个库或多个表，可以将命令多写几次就可以了。在设置过滤的时候，最好不要同时使用do和ignore参数

2、重启mysqld服务

#service mysqld restart

2、在master的testrepl库上创建两个表，分别为xsl、abc

mysql> use testrepl

mysql> create table abc(id int);

mysql> create table xsl(id int);

查看常见的表

mysql> show tables;

+--------------------+

| Tables_in_testrepl |

+--------------------+

| abc |

| bancp |

| xsl |

+--------------------+

3 rows in set (0.00 sec)

3、在slave上观察表xsl是否复制过来了。

mysql> use testrepl

mysql> show tables;

+--------------------+

| Tables_in_testrepl |

+--------------------+

| abc |

| bancp |

+--------------------+

2 rows in set (0.00 sec)

看看，slave上确实是没有表xsl，说明设置成功了。

mysql的数据基于ssl加密传输

默认情况下，mysql的数据是明文传送的，不能够保证数据的安全性和完整性。因此，需要借助ssl的加密功能来完成数据传输。

默认情况下，mysql是没有启动加密功能的，这可以如果如下命令查看：

mysql> show global variables like ‘%ssl%‘;

+---------------+----------+

| Variable_name | Value |

+---------------+----------+

| have_openssl | DISABLED |

| have_ssl | DISABLED |

| ssl_ca | |

| ssl_capath | |

| ssl_cert | |

| ssl_cipher | |

| ssl_key | |

+---------------+----------+

7 rows in set (0.00 sec)

如果要让mysql的数据传输是通过加密方式进行的，那么需要配置如下步骤：

1、在master上创建私有CA

# (umask 077 ;openssl genrsa -out /etc/pki/CA/private/cakey.pem 2048)

2、生成自签证书

# openssl req -new -x509 -key /etc/pki/CA/private/cakey.pem -out /etc/pki/CA/cacert.pem -days 3650

3、创建文件和目录

自签证书生成完成后，还需要创建一些文件和目录。如index,serial,cert,crl,newcerts

# cd /etc/pki/CA/

#touch index.txt serial

#echo ‘01‘ > serial

#mkdir -p certs crl newcerts

创建完文件和目录以后，还需要修改ssl的配置文件/etc/pki/tls/openssl.conf,修改CA的目录为如下：

dir = /etc/pki/CA

3、在master上创建私钥以及颁发证书

#mkdir /usr/local/mysql/ssl/

#cd /usr/local/mysql/ssl/

#(umask 077;openssl genrsa -out mysql.key 2048)

# openssl req -new -key mysql.key -out mysql.csr

#openssl ca -in mysql.csr -out mysql.crt -days 3650

#chwon -R mysql.mysql ./

4、在slave上配置

在slave上创建私钥

# (umask 077;openssl genrsa -out /etc/pki/CA/private/mysql.key 2048)

5、生成颁发请求

# openssl req -new -key /etc/pki/CA/private/mysql.key -out /etc/pki/CA/mysql.csr

6、将请求文件发送给master（在slave上执行）

# scp /etc/pki/CA/mysql.csr 192.168.108.202:/tmp

7、master为slave签署请求文件（在master上执行）

#openssl ca -in /tmp/mysql.csr -out /tmp/mysql.crt -days 3650

8、将签署好的证书发送给slave(在master上执行)

#scp /tmp/mysql.crt 192.168.108.150:/usr/local/mysql/ssl/

#scp /etc/pki/CA/cacert.pem 192.168.108.150:/etc/pki/CA/cacert.pem

9、修改master上的mysql配置文件/etc/my.cnf，并在[mysqld]容器中添加如下信息：

[mysqld]

ssl-ca=/etc/pki/CA/cacert.pem

ssl-cert=/usr/local/mysql/ssl/mysql.crt

ssl-key=/usr/local/mysql/ssl/mysql.key

然后在重启master上的mysqld服务

#service mysqld restart

10、在master上创建具有复制权限的用户且使用ssl来连接

mysql>grant replication slave on *.* to ‘repluser‘@‘192.168.108.%‘ identified by ‘repuser‘ require ssl

mysql> flush privileges;

11、连接master服务器

mysql> change master to master_host=‘192.168.108.202‘, master_user=‘repluser‘, master_password=‘repluser‘, master_log_file=‘mysql-bin.000011‘, master_log_pos=107,master_ssl=1, master_ssl_ca=‘/usr/local/mysql/ssl/cacert.pem‘, master_ssl_capath=‘/usr/local/mysql/ssl/‘, master_ssl_cert=‘/usr/local/mysql/ssl/mysql.crt‘,master_ssl_key=‘/usr/local/mysql/ssl/mysql.key‘;

mysql>slave start

查看slave上的状态信息

mysql> show slave status\G

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

Slave_IO_State: Connecting to master

Master_Host: 192.168.108.202

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000011

Read_Master_Log_Pos: 107

Relay_Log_File: relay-log.000001