mysql 主从复制授权_mysql主从复制

mysql复制

一、概述

主从复制(也称 AB 复制)允许将来自一个MySQL数据库服务器(主服务器)的数据复制到一个或多个MySQL数据库服务器(从服务器)。

使用场景： 高可用HA

(优点) 读写分离，降低主库的访问压力

负载均衡(读操作)

在从库进行备份，以免备份期间影响主库的服务

二、原理

流程介绍：

主节点：

开启二进制日志,主库上的数据更改(DDL DML DCL)记录到二进制日志(Binary Log)中。

dump线程：读取主库二进制日志事件用来响应从库的请求

从节点：

I/O 线程 :从master 请求二进制日志事件，并保存于中继日志中

SQL thread: 从中继日志中读取日志事件，在本地完成重放

三、特点：

1、基于GTID复制

GTID (Global Transaction ID)是全局事务ID,master生产的自增ID，每个事务的唯一标识，它由UUID:NUMBER构成，因此，不同 mysql节点产生的GTID必然不同，因此在整个集群全局中不会重复。通过 GTID 保证了每个在主库上提交的事务在集群中有一个唯一的ID。这种方式强化了数据库的主备一致性，故障恢复以及容错能力。

2、

1)异步复制

MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这会存在个问题，主从数据不一致，如果此时，强行将从提升为主，可能导致新主上的数据不完整。

补充说明：

2)半同步复制

介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。MySQL以插件的形式支持半同步复制

3)全同步复制

指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

四、三种复制方式：

####statement: 基于sql的binlog,每条修改数据的SQL都会保存到binlog 中

优点：并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点： 2020/01/09 20:44 不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候。这种 情况下会导致master-slave中的数据不一致

* LOAD_FILE()

* UUID()

* USER()

* FOUND_ROWS()

####row：基于行级别，记录每一行数据的变化，也就是将每一行数据的变化都记录到binlog中

优点：安全可靠， 任何情况都可以被复制

缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。

mixed:混合statement和row模式

五、主从配置

1、主节点：启动二进制日志 Log_bin=master-bin

为当前节点设置一个全局唯一的ID号 Server_id=

创建有复制权限的用户账号

从节点：启动中继日志

为当前节点设置一个全局唯一ID

使用有复制权限的用户账号连接至主服务器，并启动复制线程

2、复制架构： 一主一从

主主 环状复制

一主多从，从还可以再有从

环境：主：192.168.159.131

从：192.168.159.132

********** 基于position

master :

1) 创建测试数据

create database masterdb1;

create table test1(name char(50));

insert into tset1 values(1111);

insert into tset1 values(2222);

2)vi /etc/my.cnf

log-bin

server-id = 131

systemctl restart mysqld

3) 创建复制用户并授权

grant replication slave, replication client on *.* to 'rep'@'192.168.159.%' identified by 'Mysql@123';

4)mysqldump -p'mysql123' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql

5)观察二进制分隔点

slave :

1) 测试复制用户

mysql -h 192.168.159.131 -urep -p'Mysql@123'

2) vi /etc/my.cnf

server-id=132

systemctl restart mysqld

3) 导入数据

##set sql_log_bin=0; 如果想在主库上执行一些操作，但不复制到slave库上，可以通过修改参数sql_log_bin来实现。

source ''.sql

4) 设置主服务器

change master to master_host='192.168.159.131',

master_user='rep', master_password='MyMysql@123', master_log_file='master-bin.000003',

master_log_pos=154; ####在备份sql上查看

5)启动从设备

start slave;

6) 查看启动状态(IO-YES/SQL-YES)

show slave status\G;

返回主服务器(master1)更新数据，在从服务器(node1)观察是否同步。

**********基于GTID

master :

1)reset master;

2)添加数据，与上面实验区分

insert into masterdb1.test1 values (55555);

insert into masterdb1.test1 values (66666);

3) vi /etc/my.cnf

log-bin

server-id=1

gtid_mode=ON

enforce_gtid_consistency=1

systemctl restart mysqld

4) grant replication slave, replication client on *.* to 'aaa'@'192.168.159.%' identified by 'Mysql@123';

flush privileges;

5) mysqldump -p'mysql123' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql

slave:

1) 测试复制用户

mysql -h 192.168.159.131 -uraaa -p'

2)2 启动二进制日志，服务器ID，GTID

vim /etc/my.cnf

log-bin

server-id=132

gtid_mode=ON

enforce_gtid_consistency=1

systemctl restart mysqld

4) source ''.sql

5) 设置主服务器

change master to master_host='192.168.159.131',master_user='rep',master_password='Mysql@123',

master_auto_position=1;

start slave;

show slave status\G;

返回主服务器(master1)更新数据，在从服务器(node1)观察是否同步。

***********双主

目前：已经设置master为node1的主服务器，只需设置node12为master1的主服务器，

1 设置node1为master的主服务器

1)创建复制用户

grant replication slave, replication client on *.* to 'aaa'@'192.168.159.%' identified by 'Mysql@123';

flush privileges;

2) node开启bin-log

3) 设置主服务器

change master to master_host='192.168.159.132',master_user='aaa',master_password='Mysql@123',

master_auto_position=1;

start slave;

show slave status\G;

2 、测试

master上插入数据，在node上观察 insert into masterdb1.test1 values (55555);

node1上插入数据，在master上观察 insert into masterdb1.test1 values (55555);

双方同步成功，双主设置完成。

#############################33

半同步复制：配置

Mysql > show plugins;

If not exist : mysql >

Master :install plugin rpl_semi_sync_master soname 'semisync_master.so';

Slave :install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

MASTER： show global variables like '%semi%'；

Slave: set global rpl_semi_sync_slave_enabled=ON;

Master :show global status like '%semi%';

复制过滤器：

从服务器SQL_THREAD在replay 中继日志中的事件时，仅读取与特定数据库相关的数据应用于本地

问题：会造成网络及磁盘IO权限

Replicate_do_db 仅复制哪个库

Replicate_ingnore_db

Replicate_do_table 仅复制哪个表

Replicate_ingnore_table