mysql主备模型一写多读_第40课 mysql主从复制、读写分离及mysql-proxy及问题解答

1、MYSQL复制的基础概念

2、MYSQL复制的实现

3、MYSQL复制架构及双主模型演示

4、MYSQL复制监控/常见问题及解决方案

5、MariaDB  GTID及多源复制

6、MariaDB  GTID读写分离及mysql-proxy的使用

一、MySQL主从复制的基础知识

二、MySQL主从复制实现(以mariadb 5.5.36为例)

实验环境：主服务器(node1)172.16.100.7

从服务器(node2)172.168.100.8

软件：mariadb-5.5.36-linux-x86_64.tar.gz

说明：主从复制需2台服务器版本一致(或从服务器版本高于主服务器才可以，否则报错)；

复制起点：1、从0开始，适合主从服务器都为新建的服务器

2、master运行了一段时间且数据较大，后需部署从服务器(完备主数据库，并导入到从服务器；从服务器从备份时主服务器二进制日志所在的位置开始复制)

一、 配置主从复制之(主从数据库从0开始)

(1)主、从服务器一定要时间同步，各自下载mariadb数据库到本地服务器

(2)master上配置

#tar xf mariadb-5.5.36-linux-x86_64.tar.gz -C /usr/local

#cd /usr/local

#ls

#ln -sv mariadb-5.5.36-linux-x86_64.tar.gz mysql

#mkdir -pv /mydata/data

#useradd -r mysql

#chown -R mysql.mysql /mydata/data

#cd mysql  #chown -R root.mysql ./*   #ls

#mkdir /etc/mysql

#cp support-files/my-large.cnf /etc/mysql/my.cnf  #编辑配置文件，修改datadir=/mydata/data

#cp support-files/mysql.server /etc/rc.d/init.d/mysqld

#chmod +x /etc/rc.d/init.d/mysqld

#chkconfig --add mysqld

#scripts /mysql_install_db --datadir=/mydata/data --user=mysql #初始化数据库

#mkdir -pv /mydata/binlogs/

#如果安装指定binlog目录就修改配置文件log-bin=mysql-bin改为log-bin=/mydata/binlogs/mysql-bin

#chown -R mysql.mysql /mydata/binlogs

#vi /etc/mysql/my.cnf

#service mysqld start

(3)slave上配置

slave上相同步骤安装MySQL程序，区别于不用二进制日志，要用到中继日志(创建中继日志存放目录)

#useradd -r mysql

#mkdir -pv /mydata/{data,binlogs,relaylogs} 二进制目录可以不用创建

#chown -R mysql.mysql /mydata

#vi /etc/msyql/my.cnf

修改server-id=11

增加datadir=/mydata/data

注释log-bin

启用中继日志 relay-log (如果没有自定义为relay-log = /mydata/relays/relay-log)

(4)在master创建有复制权限的账号

#/usr/local/msyql/bin/mysql  登录MySQL，因为么有修改环境变量所有使用全路径

>grant replication slave,replication client on *.* to rep@'192.168.%.%' identified by '123456';

>flush privileges;

(5)在slave上用有复制权限的账号链接master

#/usr/local/mysql/bin/mysql

>change master to master_host='172.16.100.7',master_user='rep',master_password='123456',master_log_file=master-bin.000001,master_log_pos=495;

说明：(master_log_file、master_log_pos 是主节点的信息，需在主节点查看show master status、help change master to 可以查看其参数，意思为把主节点改为*****)

>show slave status\G; 查看从节点状态信息 \G为纵向排列

>start slave;启动从节点；(也可以分别启动2个线程 start slave sql_thread/io_thread)

(6)在主节点创建数据库分别开启和关闭从节点复制(start\stop slave)来验证主从复制是否成功。

二、复制中应注意的问题：

(1)如何限制从服务器只读？

更改slave的全局服务器变量read-only为yes(注：此限制对于拥有root权限的用户无效)

测试 主：>grant create,update,insert,delete,alter on testdb.* user1@'172.16.%.%' identified by '132465';

>flush privileges;

从：>setect user,host from mysql.user  查看已经创建了用户

>set global read_only = 1;(临时权限 重启失效)

主：#/usr/lcoal/mysql/bin/mysql -uuser1 -h172.16.100.8 -p 输入密码

>use testdb; >create table t2(name char(20));  提示没有权限，如果想永久生效修改my.cnf 在 [mysqld]下增加 read_only = 1;如果要使所有用户都只读(slave)使用全局锁；

slave上: >flush tables with read lock;(会话不能终止) >unlock tables;解锁

(2)如何保证主从复制的事务安全？

前提：mysql对二进制日志时间数据库会有缓冲(此情况事务提高到内存中，但为生成二进制日志)在master上设置参数 sync_binlog=1

主节点  > set global synv_binlog=1;

(3)半同步复制(多从情况)

半同步：master要等待一个从节点吧数据完整复制过去，由google贡献的补丁(以插件形式存在，此文件在/usr/local/mysql/lib/plugin中 semisync_master.so,semisync_slave.so)

安装半同步：

1.stop slave

2.主：install plugin rpl_semi_sunc_master soname 'semisync_master.so';

3.主：show globalvariables like '%semi%'  查看已安装，但未启用

4.set global rpl_semi_sync_master_enabled=1;

set global rpl_semi_sync_master_timeout=1000;默认1000毫秒，等待从节点响应时间，如果1000毫秒从节点无响应，自动降级为异步复制

slave：install plugin prl_semi_sync_slave soname 'semisync_slave.so';

show global variables like 'rpl_semi%';

set global rep_semi_synv_slave_enabled=1;

start slave;(启用从节点或重启I/O进程即可)

注，主节点在联系从节点超时(1000))，会自动从半同步降级为异步复制。

查看半同步：show global status like '%semi%';

(4)复制过滤：让slave仅复制有限的几个数据库，甚至仅复制某数据库内有限的几张表的机制。

有2中方法：1 主节点过滤，在向二进制日志记录事件时，仅记录指定数据库或表的相关操作

binlog_do_db =       ##数据库白名单，一般启用白名单只能库级别

binlog_ignore_db =   ##数据库黑名单

2  从节点过滤，仅从中继日志中读取指定的数据库或表相关的事件并应用于本地；

replicate_do_db =

replicate_ignore_db =

replicate_db_table = db_name.tb_name

replicate_ignore_table =

replicate_wild_do_table =

replicate_wild_ignore_table =

三、配置主从复制之二(主运行了一段时间后，加入从库实现主从不同)

思想：备份主库到从库上还原，再进行同步，在主库扇建数据库mydb,查看同步效果。

实验准备：1、service mysqld stop 2.rm -rf /mydata/data/* (删除从服务器的数据库)

3、rm -rf /mydata/data/relaylogs/*

4、cd /usr/local/mysql; scripts /mysql_install_db --datadir =/mysql/data/

5、service MySQL start ，删除从服务器上数据

安装：master：show master status; 查看主库状态 file&pos

/usr/local/mysql/bin >mysqldump -A --LOCAL-ALL-TALES --master_data=2 > all.sql

vim all.sql 查看all.sql信息，并记录master-bin.000003  master_log_pos=8096

scp all.sql node2:/root/  将all.sql传到node2的root目录下；

slave：初始化从节点：# cd /usr/local/mysql

#scripts /mysql_install_db --datadir=/mydata/data --user=mysql

#service mysqld start

#/usr/lcoal/mysql/bin/mysql

#确认主节点有复制权限的账号

#/usr/local/mysql/bin/mysql < all.sql 导入all数据库

#/usr/local/mysql/bin/mysql

#change master to

master_host='172.16.100.7',

master_user='rep',

naster_possword='123456',

master_log_file='master-bin.000003',

master_log_pos=8096;

#start slave ,#show slave status\G;#show databales; 测试成功。

四、除了主从复制外还有好多复制模式；

mariadb 10以上支持多主模型，5秒切换到另一个master上，且2个master存不用的数据，要不然就冲突了；还有环形复制等。

双主复制

1、双节点都得创建有复制权限的用户

2、双节点都得启用中继日志和二进制日志

3、为保证具有增长功能的字段能正确生成ID，需要配置2个节点分别使用偶数和奇数ID号。

4、都要配置为自己的主节点

具体配置：

node1:#service msyqld stop

#rm -rf /mydata/data/* /mydata/relay/* /mydata/binlogs/*

#cd /usr/local/mysql #scripts/ mysql_install_db --datadir=/mydata/data --user=mysql

node2:同样的设置删除主从复制的数据,同样创建

#mkdir /mydata/relaylogs -pv

#chown -R mysql.mysql /mydata/relaylogs

node1配置文件：                             node1配置文件：

[mysqld]                                    [mysqld]

server-id=                                  server-id=

log-bin=                                    log-bin=

relay-log=                                  relay-log=

auto-increment-increment=2          auto-increment-increment=2

auto-increment-offset=1               auto-increment-offset=1

node1#/usr/local/mysql/bin/mysql

>usr mydb;

>grant replication salve,replication client on *.* to rep@'172.16.%.%' identified by '123456';     #  node2 执行相同的操作

>show master status;   #查看master_log_file、matser_log_pos

>change master to master_host='172.16.100.8',master_user='rep',master_password='123456',

master_log_file='master-bin.0000001',master_log_pos=424;

注意，先node1--172.16.100.7 为主，node2--172.16.100.8上执行相同的操作，完成以上操作后再开启复制功能。

node2#/usr/local/mysql/bin/mysql

>usr mydb;

>grant replication salve,replication client on *.* to rep@'172.16.%.%' identified by '123456';     #

>show master status;   #查看master_log_file、matser_log_pos

>change master to master_host='172.16.100.7',master_user='rep',master_password='123456',

master_log_file='master-bin.0000002',master_log_pos=324;

两台主机相同操作：

>start slave;

>show slave status\G;

>create table tb1(id int(4)); 在node2上 >show tables from mydb;

#ls /mydata/data

master.info 保存slave连接master时所需信息，纯文本文件

relay-log.info 保存了当前slave节点上(主节点上的)二进制日志和当前节点中继日志的对应关系，

清理二进制日志：purge master logs to 'master-bin.000002';

五，mysql复制监控与常见问题解决方案(管理和维护)

1、监控复制

主：show master status，show binlog events，show binary logs

从：show slave status

2.slave 是否发生复制延迟   seconds_behind_master :0

3:如何确定master/slave节点数据是否一致

表自身的checksum ,使用percona_tools中的pt-table-checksum

4.数据不一致的修改方法

重设复制机制，使用mysqldump从master导出slave不同数据

5.为slave设定新的主库

在从库停止复制线程，而后重新设定change master to命令即可：

6.提示冲库为主库

计划内提示一个从库为主库：

(1)停止向主库写入数据

(2)让计划提示主库的从库赶上主库

(3)提示从库为主库

(4)修改其他从库的指向

计划外提示一个从库为主库：

(1)确定哪个从库的数据为最新最全，master_log_file,master_log_pos

(2) 等待所有的从库执行从主库复制而来的生成中继报告

六、zabbix监控msyql主从复制

在高并发网站架构中，MySQL数据库主从同步是不可或缺的，不过经常会发生由于网络原因或者操作错误，MySQL主从经常会出现不同步的情况，那么如何监控MySQL主从同步，也变成检测网站正常运行的重要环节。

MySQL同步功能由3个线程(master上1个，slave上2个)来实现，简单的说就是：master发送日志一个，slave接收日志一个，slave运行日志一个。

首先，我们解释一下 show slave status  中重要的几个参数:Seconds_Behind_Master：本字段是从属服务器“落后”多少的一个指示。

当从属SQL线程正在运行时(处理更新)，

本字段为在主服务器上由此线程执行的最近的一个事件的时间标记开始，已经过的秒数。

当此线程被从属服务器I/O线程赶上，并进入闲置状态，等待来自I/O线程的更多的事件时，

本字段为零。

总之，本字段测量从属服务器SQL线程和从属服务器I/O线程之间的时间差距，单位以秒计。

那么如何监控从服务器是否正常运行呢？

1、创建监控MYSQL的账号

2、写一个监控脚本

这个脚本主要用于获取MYSQL主从同步信息；

我们先执行一个命令

我们在输出的信息中选择

这两项来监控，我测试了一下，当操作的数据出现异常的时候，Slave_SQL_Running就会变成No，当执行slave stop的时候，两个都会变成No；

脚本内容如下mysql -uzabbix -e'show slave status\G'

|grep -E"Slave_IO_Running|Slave_SQL_Running"|awk '{print $2}'|grep -c Yes

我只写了一条命令，这里详细说明一下它的意思；

先用zabbix这个用户来获取slave的所有状态，然后grep出这两个状态，然后再输出第二列，最后查看有几个Yes状态的，正常情况下有两个Yes状态。

3、在zabbixagent里面添加监控项

文件的最后添加如下一行

等号后面的字符串中，逗号前面是Key，后面是执行的脚本(脚本不要忘了执行权限)

添加好之后，就重启agent程序

4、Server端添加监控项

这里的192.168.0.34 是我的agent的IP，如果主从复制正常，就会返回2 (代表两个状态都是Yes)，现在就说明Server已经可以从agent获取状态了，现在在管理界面中先添加监控项：

配置--模板

先创建一个模板，这个模板的主要功能是用于监视MYSQL主从复制的状态；

添加触发器

当返回的值小于2时，说明只存在1个YES或0个YES，这时候说明MYSQL主从同步出现异常，即产生报警；

获取最新数据

在这里可以看到，已经获取了最新的监控数据，说明监控添加成功；

七、MYSQL GTID多源复制、读写分离和mysql-proxy的使用

GTID:

读写分离：在主从复制架构中，一般主写从读；有2种方法实现读写分离

1 在程序中自写代码实现读写分离，耦合度太高，

2 在应用程序和主从复制集群之间添加一个能使读写分离并让从数据库负载均衡的节点--读写分离器

如：mysql-prxoy(官方)、amoeba(java研发)

如果主从架构主服务器挂了，让一台从服务器接替主服务器工作，其他从服务器的主重新指向新主。

常见的集群有三种：负载均衡集群、高可用集群、高性能集群

安装mysql-proxy方式有2种：yum (epel源)、二进制安装

#tar xf mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local/

#cd /usr/local/

#ln -sv mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit  mysql-proxy

#cd mysql-proxy  #ls

#ls share/doc/mysql-proxy   #官方提供的脚本文件(rw-splitting.lua读写分离)

#vim /etc/re.d/init.d/mysql-proxy (复制读写分离脚本内容-mysql5.6 Replication)

#chmod +x /etc/rc.d/init.d/mysql-proxy

#chkconfig --add mysql-proxy

#useradd mysql-proxy

#vim /etc/sysconfig/mysql-proxy  (复制配置脚本)

#vim /usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua (复制配置脚本)

#service mysql-proxy start

#ss -tnl  #4041端口

#msyql -uadmin -padmin -h172.16.100.9 --port=4041    #测试(192.16.100.9为mysql-proxy)

主>grant all on *.* to admin@172.16.%.% identfied by 'admin'

>flush privileges

#msyql -uadmin -padmin -h192.168.100.9   连接msyql数据库，还未确定连到主或从

>create database testdb;

主>show databases; #在主服务器查看是否创建了数据库testdb

>use msyql;select * from user;

从>show databases;

>select * from backends; 查看复制架构各节点数据库状态

mysql-proxy