mysql 主从有哪几种模式_MySql的几种数据复制模式介绍

MYSQL的几种主-从服务器数据复制模式介绍

一、设置主-从复制：

所谓主-从复制，就是有两台mysql服务器，一台作为主服务器，另一台作为从服务器，在同时工作的过程中，在主服务器上进行的对数据库的

操作，如create database，insert into 等DML(数据操纵语言)和DDL(数据定义语言)语句，会先写入到数据库中，同时保存至主服务器的二进制

日志文件中，然后主服务器会将其二进制日志文件读出给从服务器，从服务器的I/O Thread会将主服务器的二进制日志文件写入到其中继日志(replay_log)中

，然后由从服务器中的SQL Thread 线程将中继日志中记录的语句一条一条的写入到从服务器的数据中，实现数据的同步。

注：这种主从复制方式，随着时间的变化，在误操作或者其他人为因素下，会不可避免的出现主从服务器上的数据不一致现象。

二、主-从复制的环境配置：

1、在主-从服务器上都要安装mysql，确保从服务器上安装的mysql版本要高于主服务器安装的mysql版本，或者和主服务器的mysql版本一样，

一定不能低于主服务器上的mysql版本。此次实验主从服务器上都是编译安装的mysql-5.5版本。

2、最好为mysql准备一个数据存放的文件系统，便于管理。此次实验在主从服务器上都创建了一个新分区，并做成了逻辑卷，并将逻辑卷自动挂载至了/mydata

目录下，在此目录下创建了一个/data 目录用来存放mysql的数据库文件。

注：mysql的编译安装过程，这里不再给出，可以参考本人LAMP或者LNMP博客中关于mysql的编译安装过程。

3、此实验用的主服务器的IP地址为172.16.24.2，从服务器的IP地址为172.16.24.1

三、主-从复制的具体实现过程：

1、首先要确保主从服务器上的mysql服务能够正常工作，然后修改主服务器上的mysql的配置文件：

#vim /etc/my.cnf

确保二进制日志文件已经启用，默认为启用状态。

log-bin=mysql-bin

确保服务Id为1

server-id  = 1       --主从服务器的server-id 不可以相同，如果相同，会造成主从不分，从而循环复制数据现象。

2、修改从服务器上的mysql的配置文件：

#vim /etc/my.cnf

注：如果从服务器不再配置为其他从服务器的主服务器，可以将其二进制文件禁用，因为从服务器的二进制日志文件是没用的，关闭会节省磁盘空间。

找到  log-bin=mysql-bin 此行，注释掉，即#log-bin=mysql-bin

在 [mysqld] 列下，添加中继日志文件

relay-log=relay-bin

relay-log-index=relay-bin.index

找到 server-id 行，将其id号修改为大于1的值，为了预留空间，可以将其值修改为稍大一点:

server-id = 10

保存退出，然后重启主服务器和从服务器的mysql服务，确保能够正常启动和工作。

3、在主服务器上进行如下操作：

#mysql -uroot -predhat

mysql> GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO repl@'172.16.%.%' IDENTIFIED BY '123456';  --授予从服务器到主服务器同步数据的用户repl的权限

mysql> FLUSH PRIVILEGES;

4、在从服务器上进行如下操作：

#mysql -uroot -p123456

mysql>CHANGE MASTER TO MASTER_HOST='172.16.24.2',MASTER_USER='repl',MASTER_PASSWORD='123456';

mysql>show slave status\G     --查看slave服务启动情况，此时服务未启动，因为从服务器的IO线程和SQL线程处于No状态：状态信息如下图所示;

mysql>start slave;            --开启slave服务

mysql>show slave status\G     --此时再查看slave服务启动状态，就会启动成功，如下图所示：从服务器的IO线程和SQL线程处于Yes状态：

注：即使从服务器的slave服务关闭(mysql>stop slave)，在主服务器上对数据库数据进行的操作信息仍能同步到从服务器上数据库中,在从服务器的slave服务

开启后(mysql>start slave)就能看到新同步到从服务器上的数据。如果关闭从服务器上的mysqld服务(servie mysqld stop)，信息将不会再同步到从服务器上。

5.如果出现这种情况：有一台mysql服务器，估计以后随着业务的增长，数据会不断增长，可能无法满足广大用户的读数据的请求，为了减轻此服务器的读压力，

想要添加一台从服务器，做成主-从复制模式，但是在此之前的数据从服务器中并没有保存，那么如何将这部分数据导入到从服务其中呢，

我们可以这样做：

(1)、在配置好主-从服务器之后，将先前主服务器上的那部分数据进行基于逻辑备份的完全备份。为了让二进制日志文件从新的位置(position)开始记录,通常位置为107

，可以使用flush logs 命令，回滚二进制日志。

(2)、然后可以使用mysqldump命令对主服务器数据库中的所有数据进行备份：

#mysqldump -uroot -predhat --all-databases --single-transaction --master-data=2 >/tmp/all_data.sql

#scp /tmp/all_data.sql 172.16.24.1:/tmp/

(3)、回到从服务器上，

#mysql -uroot -p123456

mysql> stop slave;

mysql> source /tmp/all_data.sql

mysql> start slave;

导入成功后，然后查看数据，就可以看到主服务器上的数据了！

四、设置半同步复制步骤：

半同步复制就是在主从复制过程中，主服务器会设置一个超时时间间隔，如果从服务器在这段时间内，无法完成将主服务器的二进制日志文件读到其中继日志文件中，

主服务器就会中断将自己的二进制日志文件读出给从服务器，从服务器的半同步模式将会降级为异步模式：

1、在Master和Slave的mysql命令行进行如下操作：

On Master  在主服务器上进行操作：

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;

mysql> show status like 'rpl_%';       --查看主服务器半同步状态信息，Rpl_semi_sync_master_status处于ON状态，

但是Rpl_semi_sync_master_clients的值为0，因为我们还未开启从服务器的半同步功能：如下图所示：

On Slave  在从服务器上进行操作：

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

mysql> STOP SLAVE;

mysql> START SLAVE;

mysql> show status like 'rpl_%';       --查看从服务器半同步状态信息,Rpl_semi_sync_master_status处于ON状态，如下图所示：

此时再查看主服务器的状态信息，Rpl_semi_sync_master_status处于ON状态，并且Rpl_semi_sync_master_clients的值为1，如下图所示：

至此说明从服务器的半同步复制模式已经启动成功了，这里不再给出测试信息。

2、以上第1步的操作在关闭mysql服务之后就会失效，要让其永久有效，要修改配置文件：

在Master和Slave的my.cnf中编辑：

On Master

#vim /etc/my.cnf

[mysqld]

rpl_semi_sync_master_enabled=1

rpl_semi_sync_master_timeout=1000     --单位为毫秒(1000ms)

On Slave

#vim /etc/my.cnf

[mysqld]

rpl_semi_sync_slave_enabled=1

# 也可通过设置全局变量的方式来设置,如下：

set global rpl_semi_sync_master_enabled=1

# 取消加载插件

mysql> UNINSTALL PLUGIN rpl_semi_sync_master;

查看从服务器上的semi_sync是否开启:

mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';

查看主服务器上的semi_sync是否开启，注意clients 变为1 ，证明主从半同步复制连接成功:

mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';

五、设置主-主复制：

主-主复制就是两台服务器既是另一台服务器的主，也是另一台服务器的从，这两台服务器互为主从。这种模式在一定程度上可以减轻服务器的读压力，每台服务器

可以负载50%，但是不能减轻写压力，在每一台服务器上进行的写操作，都要同步到另一台服务器上，以保证数据的一致性。

1、在两台服务器上各自建立一个具有复制权限的用户；

server1：在第一台服务器上，姑且称第一台服务器为主服务器，因为这两台服务器互为主从。

mysql>grant replication client, replication slave on *.* to li@'172.16.%.%' identified by '123456';

mysql>flush privileges;

server2：在第二台服务器上：

mysql>grant replication client, replication slave on *.* to guang@'172.16.%.%' identified by '123456';

mysql>flush privileges;

2、修改两台服务为的配置文件：在做主-主复制时，两台服务器的中继日志都不要开启！

# 主服务器上：server1

#vim /etc/my.cnf

[mysqld]

server-id = 10

log-bin = mysql-bin

auto-increment-increment = 2

auto-increment-offset = 1

# 从服务器上：server2：

#vim /etc/my.cnf

[mysqld]

server-id = 20

log-bin = mysql-bin

auto-increment-increment = 2

auto-increment-offset = 2

3.数据库复制过滤：

数据库复制过滤就是对进行复制的数据库和表进行限制，只允许哪些数据库或者哪些表参与复制。

(1).在主服务器上做复制过滤：

[mysqld]

binlog_do_db = database_name : 表示只复制指定的数据库中的数据，其他则不复制，相当于一个白名单。

binlog_ignore_db = database_name : 表示只复制此处指定的数据库之外的所有数据库中的数据，相当于一个黑名单。

如果binlog_do_db和binlog_ignore_db同时使用的话，限制范围小的生效，也就是白名单会生效！

这里必须指出的是，如果是主-从模式的复制，一定不要在主服务器上做复制过滤，因为在主服务器上做过滤，任何不涉及到数据库改变

的相关的写操作都不会记录到二进制日志文件中！！！

(2).在从服务器上做过滤：

[mysqld]

replicate_do_db = database_name           --白名单，只复制指定的database_name数据库中的数据。

replicate_ignore_db = database_name       --黑名单，复制除了指定的database-name之外的所有数据库中的数据。

也可以对单张表进行限制：

格式1：

replicate_do_table = table_name

replicate_ignore_table = table_name

格式2：

replicate_do_table = database_name.tb1       --复制database_name数据库中的tb1这张表

replicate_ignore_table = database_name.tb2   --复制除了database_name数据库中tb2表以外的所有表

(3).复制的格式也支持通配符：

replicate_wild_do_table = jiaowu.tb%

replicate_wild_do_table = jiaowu.tb_

其中"%"表示匹配任意长度任意字符，"_"表示匹配任意单个字符

如果只想复制某一个数据库，可以写成这样：

表示只复制jiaowu这个数据库中的数据！

# 主服务器上：server1

#vim /etc/my.cnf

[mysqld]

server-id = 10

log-bin = mysql-bin

replicate-do-db = jiaowu

auto-increment-increment = 2        --自动增长的步长

auto-increment-offset = 1           --自动增长步长的起始位置，此处为1，server1和server2不能一样！

# 从服务器上：server2：

#vim /etc/my.cnf

[mysqld]

server-id = 20

log-bin = mysql-bin

replicate-do-db = jiaowu

auto-increment-increment = 2

auto-increment-offset = 2         --自动增长步长的起始位置，此处为2

4、如果此时两台服务器均为新建立，且无其它写入操作，各服务器只需记录当前自己二进制日志文件及事件位置，以之作为另外的服务器复制起始位置即可

server1:

mysql>SHOW MASTER STATUS\G

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

File: mysql-bin.000001

Position: 107

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

server2

mysql>SHOW MASTER STATUS\G

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

File: mysql-bin.000003

Position: 490

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

5、各服务器接下来指定对另一台服务器为自己的主服务器即可：

server1:172.16.24.1

mysql>CHANGE MASTER TO MASTER_HOST='172.16.24.2', MASTER_USER='guang', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=490;

mysql>SHOW SLAVE STATUS\G       --查看从服务器的二进制日志状态信息，此时 Slave_IO_Running 和Slave_SQL_Running均处于No状态，如下图所示：

mysql>START SLAVE;              --开启从服务

mysql>SHOW SLAVE STATUS\G       --再查看从服务器的二进制日志状态信息，此时 Slave_IO_Running 和Slave_SQL_Running均处于Yes状态，如下图所示：

server2

mysql>CHANGE MASTER TO MASTER_HOST='172.16.24.1', MASTER_USER='li', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

mysql>SHOW SLAVE STATUS\G       --查看从服务器的二进制日志状态信息，此时 Slave_IO_Running 和Slave_SQL_Running均处于No状态，如下图所示：

mysql>START SLAVE;              --开启从服务

mysql>SHOW SLAVE STATUS\G       --查看从服务器的二进制日志状态信息，此时 Slave_IO_Running 和Slave_SQL_Running均处于Yes状态，如下图所示：

至此主-主复制的模式已经建立完成了，在一台服务器上进行的使数据库发生改变的操作，如DML、DDL语句，都会同步到另外一台服务器上。