MySql --主从复制 主主复制

                                        MySql 复制

系统的扩展方式有:scale up [向上扩展或是垂直扩展]  scale out:[向外扩展或是水平扩展]

Mysql的复制就是为了实现mysql的水平扩展

为什么要实现水平扩展

当前端节点很少时可以用垂直扩展的方式进行换更好的硬件,但是这会有上限当达到一定的程度后就无法在继续扩展,同时这种扩展的方式成本会很高.

因此更好的办法还是进行水平扩展.来应对前方的压力.但是这对于mysql水平扩展有一个问题就是数据共享的问题.

共享存储的解决方案有NAS SAN

假设使用的是NAS各个mysql服务器都已此节点共享数据,当两个需要用到同一文件的操作到来时,先到达的操作可以进行相应的操作,但是文件系统就会对文件施加锁,所以后到的就要等待锁的释放才能继续进行,无法达到并行的目的,同时这种共享方式是文件系统级别的效率很低,那么我们如果使用块级别的共享,就要使用集群文件系统来实现锁的管理.否则数据会崩溃,但是当前方的节点很多时这种方式的管理会相当的繁琐.为了解决并行处理,所以最好的方式是文件直接存放在mysql本机之中,但是这会造成数据的不一致,因此就有了mysql的复制.主节点负责写数据,从节点负责读数据.

但是这样就会引入 另一个问题,mysql的查询基于缓存来进行提高效率,当前端负载均衡挑选其中一台进行数据查询,结果就缓存在其中,然而下一次的查询到达时被调度到其他的mysql服务器就无法命中缓存.这还不包含查询语句的不同,所以解决的方式有如下几种

1,在前端应用程序解决,所有的查询语句,都提取其特征码,然后除以后端的mysql服务器个数取余数,相同的都发向同一服务器.这就是取模法

  假设后端有三台服务器,因此用一个数除以3得到的结果无非有0,1,2三种可能但是这种方法有一个极大的缺陷,就是假设后端有一台服务器宕机,数据就无法返回.重新调整程序除的值是2,这样缓存几乎完全失效.

2,为了解决上述问题,于是引入了一致性哈希算法

原理如下

同样也是取模但是这次是这样的假设使用服务器变量为IP地址,这样对其进行特征码提取然后除以2^23这样就会得到一个数字,而这个数字会落在0-2^23-1的一个环上如情况一所示.而同样的对数据库查询语句进行特征码提取同样除以2^23得到一个数,它必将落于此环的某处,而后他需要去找的缓存为顺时针离他最近的服务器,这样就算其中的一台服务器宕机,损失的只是其中的一部分缓存,这是理想情况,但是这种也有缺陷,有可能会是第二种情况,这时就会丢失大部分的缓存.于是引入了虚拟iP机制,是将服务器变量多次取模这样就会使其落在此环的多个位置,就算失效也只是其中的一小部分.

为了解决这种前端与后端的耦合度过大的情况,就引入另一种方式,加中间层,这个中间层就是mysql的反向代理上述算法由其实现,他能实现理解sql语句并作语句路由,读写分开.

3,使用公共缓存如memcached

复制的功用:可以实现负载均衡,数据分布,数据备份,高可用性,mysql升级测试

复制架构的问题:主节点宕机就无法进行写操作,并且写操作无法均衡.要想实现均衡必须多数据进行切片

Mysql复制的工作原理

主从架构

将主节点的二进制日志拿到本地再执行一遍

从库通过io线程将复制请求发送至主库3306端口接受后送至mysqld进程,mysqld进程再有事件操作时,启动dump进程读取binlog发送给io线程,收到数据后在中继日志中缓存,而后sql线程读取中继日志在本地执行.

双主架构,就是上图的扩展,双方各自为主从.

主从模型的实现

首先安装mysql-server两个节点

主节点和从节点要实现时间同步

创建ntp服务器假设在172.16.101.200

Vim /etc/ntp.conf

添加如下行server 127.127.1.0 iburst

启动ntpd服务

在172.16.101.201测试时间服务器

[root@localhost ~]# ntpdate 172.16.101.200

21 Sep 19:19:07 ntpdate[5827]: step time server 172.16.101.200 offset -0.845042 sec

上添加如下行

server 172.16.101.200 iburst

启动ntpd服务

主服务器配置

log-bin=mysql-bin

binlog_format=mixed

server-id       = 1

创建授权复制账号

mysql> grant replication slave ,replication client on *.* to ‘slave‘@‘172.16.101.201‘ identified by ‘hzm132‘;

Query OK, 0 rows affected (0.00 sec)

从服务器配置

relay_log = /relaylogs/relay-log

server-id       = 101

创建中继日志目录并设置属主和属组为mysql

Mkdir /relaylogs

Chown -R mysql.mysql /relaylogs

查看主服务器当前二进制日志所处的位置

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000003 |      357 |              |                  |

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

1 row in set (0.00 sec)

mysql>

change master to 

master_host=‘172.16.101.200‘,master_user=‘slave‘,master_password=‘hzm132‘,master_log_file=‘mysql-bin.000003‘,master_log_pos=357;

Query OK, 0 rows affected (0.04 sec)

启动IO-THEARD SQL-THEARD

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

查看slave状态

mysql> show slave status \G;

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 172.16.101.200

                  Master_User: slave

                  Master_Port: 3306

                  Connect_Retry: 60

                  Master_Log_File: mysql-bin.000003

                  Read_Master_Log_Pos: 357

                  Relay_Log_File: relay-log.000002

                  Relay_Log_Pos: 251

                  Relay_Master_Log_File: mysql-bin.000003

                  Slave_IO_Running: Yes

                  Slave_SQL_Running: Yes

                  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: 357

                   Relay_Log_Space: 400

                   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: 

1 row in set (0.00 sec)

测试是否成功

主库创建数据库

mysql> create database tdb;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| retestdb           |

| tdb                |

| test               |

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

5 rows in set (0.00 sec)

丛库查看

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| retestdb           |

| tdb                |

| test               |

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

5 rows in set (0.00 sec)

主从复制实现

为了防止向从库写数据

配置文件中read_only=YES 此限制对super权限的用户无效

如何保证主从复制时的事务安全设置如下参数

在master中设置 sync_binlog=1

半同步复制;master至少要等待一个从节点复制数据结束,由google贡献的mysql补丁,此补丁以插件形式存在

需要安装插件,必须二进制或源代码编译安装才会有在安装目录的plugin中

安装方法

主节点执行

mysql> install plugin rpl_semi_master soname ‘semisync_master.so’

安装成功后会引入新的变量

rpl_semi_sync_master_enabled  OFF

rpl_semi_sync_master_timeout  100000

rpl_semi_sync_master_trace_level 32

rpl_semi_sync_master_wait_no_slave ON

查看方式show global variables like ‘%semi%’

从节点安装

Install plugin rpl_semi_slave soname ‘semisync_slave.so’

变量

rpl_semi_sync_slave_enable OFF

复制过滤器:让slave仅复制有限的几个数据库,甚仅复制库中的有限的几张表

方法;

1,在主节点过滤

在向二进制日志记录事件时,及记录指定的库相关操作;不可取

binlog_do_db=

binlog_ignore_db=

2,从节点过滤

即从中继日志中读取指定的库或表的相关事件到本地

replicate_do_db=

replicate_ignore_db=

replicate_db_table=

replicate_ignore_table=

replicate_wild_do_table=

replication_wild_ignore_table=这两个可使用通配符

Mysql的复制架构详细分类

双主模型的实现

假设为A和B两个节点 A=172.16.101.200 B=172.16.101.201

首先时间同步参考上面

然后A节点启用二进制日志中继日志

配置如下选项;

log-bin=mysql-bin

# binary logging format - mixed recommended

binlog_format=mixed

relay_log = /relaylogs/relay-log

auto_increment_offset=1

auto_increment_increment=2

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id       = 1

并且创建授权可以来复制的用户

mysql> grant replication slave ,replication client on *.* to ‘slave‘@‘172.16.101.201‘ identified by ‘hzm132‘;

Query OK, 0 rows affected (0.00 sec)

创建中继日志目录并设置属主和属组为mysql

Mkdir /relaylogs

Chown -R mysql.mysql /relaylogs

查看B节点所处的位置

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000001 |      367 |              |                  |

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

1 row in set (0.00 sec)

mysql> create database slavedb;

Query OK, 1 row affected (0.00 sec)

更改到B节点复制数据

mysql>

change master to 

master_host=‘172.16.101.201‘,master_user=‘master‘,master_password=‘hzm132‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=367;

Query OK, 0 rows affected (0.04 sec)

启动IO-THEARD SQL-THEARD

mysql> start slave;

B节点设置

log-bin=mysql-bin

relay_log = /relaylogs/relay-log

# binary logging format - mixed recommended

binlog_format=mixed

auto_increment_offset=2

auto_increment_increment=2

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id       = 101

授权A节点可以进行复制的权限

mysql> grant replication slave ,replication client on *.* to ‘master‘@‘172.16.101.200‘ identified by ‘hzm132‘;

Query OK, 0 rows affected (0.00 sec)

查看B节点所处的位置

Mkdir /relaylogs

Chown -R mysql.mysql /relaylogs

查看主服务器当前二进制日志所处的位置

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000003 |      357 |              |                  |

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

1 row in set (0.00 sec)

mysql>

change master to 

master_host=‘172.16.101.200‘,master_user=‘slave‘,master_password=‘hzm132‘,master_log_file=‘mysql-bin.000003‘,master_log_pos=357;

Query OK, 0 rows affected (0.04 sec)

启动IO-THEARD SQL-THEARD

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

现在测试

在B节点创建slavedb看A上是否会出现

mysql> create database slavedb;

Query OK, 1 row affected (0.00 sec)

A上查看

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| retestdb           |

| slavedb            |

| tdb                |

| test               |

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

6 rows in set (0.00 sec)

在A节点创建masterdb看B上是否会出现

mysql> create database masterdb;

Query OK, 1 row affected (0.00 sec)

B上查看

mysql> show databases;

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

| Database           |

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

| information_schema |

| masterdb           |

| mysql              |

| retestdb           |

| slavedb            |

| tdb                |

| test               |

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

7 rows in set (0.00 sec)

由此可见实现了数据的复制