mysql 实例复制,MySQL 5.7复制实例

MySQL复制基础

1.MySQL的复制是异步复制

image

2.MySQL复制是基于binlog日志。

存在三种日志格式：

statement ==> BinLog中存储SQL语句，存储日志量是最小的

row ==> 存储event数据，存储日志量大，但是不能很直接的进行读取

mixed ==> 介于row和statment之间，对于不确定的操作使用Row记录，如果每天的数据操作量很大，产生的日志多，可以考虑选择mixed格式

基于日志点复制

1.在Master端建立复制用户

创建用户dba,设置密码为12345：

create user 'dba'@'192.168.0.%' identified by '12345';

授权给用户dba:

grant replication slave on *.* to 'dba'@'192.168.0.%';

2.创建数据库dba,表t,插入数据

create database dba;

use dba;

create table t(

id int,

c1 varchar(10),

primary key(id)

);

insert into t values(1, "aa"),(2, "bb"),(3, "cc");

3.备份Master端的数据，并在Slave端恢复

备份Master端数据：

mysqldump --single-transaction --master-data=2 --all-databases --triggers --routines -uroot -p > dba.sql

在Slave端恢复：

mysql -uroot -p < /tmp/dba.sql

4.使用Change master命令配置复制

change master to

master_host='192.168.0.108',

master_user='dba',

master_password='12345',

master_log_file='mysql-bin.000001',

master_log_pos=154;

配置master_log_file,master_log_pos可以查看备份文件前面部分有记录; 也可以在Master端中使用命令查看：

show master status \G

5.查看slave端状态，开启/停止复制

show slave status \G

start slave

stop slave

注意：

如果查看slave端连接不起，那么注意查看防火墙是否拦截

导致lave_IO_Running 为connecting 的原因主要有以下 3 个方面：

1、网络不通

2、密码不对

3、pos不对

解决步骤：

1、对于第一个问题，一般情况下都是可以排除的，也是最容易排除的。

2、在主库上修改用来复制的用户的密码。

3、 在做chang to 的时候注意log_pos 是否跟此时主机的一样。在主机上 show master status \G

在线把基于日志的复制转变为基于事物的复制

基于事物的复制要比基于日志的复制在数据的完整性更好。

gtid_mode 有四种状态(有序)：off off_permissive on_permissive on

先决条件：

1.查询mysql数据库版本，保证集群中所有数据库版本号大于5.7.6

show variables like '%version%'

image

2.查询集群中数据库的gtid_mode是否都是off状态

show variables like '%gtid_mode%';

image

实现转换步骤：注意查看错误信息 ==> 进入数据目录查看xxxx.err是否有错，没有错才能进入下面操作

1.把主从上强制事物一致性设置为warn，

set @@global.enforce_gtid_consistency=warn

2.把主从上强制事物一致性设置为on

set @@global.enforce_gtid_consistency=on

3.把主从上gtid_mode设置off_permissive

set @@global.gtid_mode=off_permissive

4.把主从上gtid_mode 设置 on_permissve

set @@global.gtid_mode=on_permissive;

5.在从上查看是否还有基于日志的复制操作，只有结果为0才行，若不为0就要等待到所有基于日志的复制完成

show status like 'ongoing_anonymouse_transaction_count';

6.在主从上正式启动基于事物的日志复制

set @@global.gtid_mode=on;

7.在从上重启slave

stop slave

change master to master_auto_position=1

start slave

show slave status \G

image

8.记住在my.cnf/my.ini中添加enforce_gtid_consistency=on , gtid_mode=on

image

在线把基于事物的复制转变为基于日志的复制

stop slave

change master to master_auto_position=0,master_log_file='file',master_log_pos=position

start slave

set @@global.gtid_mode = on_permissive

set @@global.gtid_mode = off_permissive

select @@global.gtid_owned 查询这个值，只有在所有服务器都是空字符串才能继续

set @@global.gtid_mode=off

同时配置my.cnf 中gtid_mode=off enforce_gtid_consistency=off

多线程复制,在slave端进行所有操作

1.在slave服务器上停止所有链路的复制

stop slave

2.set global slave_parallel_type=’logical_clock’

有两个值database,logical_clock。database是基于数据库的多线程，不实用

3.设置并发执行复制的线程数

set global slave_parallel_workers=4

4.查看设置的变量

show variables like ‘slave_parallel%’

5.启动slave

start slave

主从管理的系统视图

image

1.replication_applier_configuration 查看管道名称，以及延迟同步时间

设置延迟1小时同步：

change master to master_delay=3600;

image

service_state ：复制服务状态

remaining_delay: 距离下次同步的剩余时间，在设置了master_delay才有用

3.replication_applier_status_by_coordinator 查看多线程复制相关的操作4.replication_applier_status_by_worker 记录了复制的线程相关信息

show processlist 可以查看系统中所有线程

5.replication_connection_configuration查询连接配置

image

6.replication_connection_status 连接状态

image