Mysql主从复制

mysql主从复制

一，主从复制以及主从复制的作用：
在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力

二，mysql主从复制原理

• MySQL数据库主从同步延迟原理

mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率很比较高，下一步， 问题来了，slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随即的，不是顺序的，成本高很多，还可能与slave上的其他查询产生lock争用，由于Slave_SQL_Running也是单线程的，所以一个DDL卡主了，需要执行10分钟，那么所有之后的DDL会等待这个DDL执行完才会继续执行，这就导致了延时。有朋友会问：“主库上那个相同的DDL也需要执行10分，为什么slave会延时？”，答案是master可以并发，Slave_SQL_Running线程却不可以。

• MySQL数据库主从同步延迟是怎么产生的

当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，那么延时就产生了，当然还有就是可能与slave的大型query语句产生了锁等待。

五，MySQL数据库主从同步延迟解决方案

最简单的减少slave同步延时的方案就是在架构上做优化，尽量让主库的DDL快速执行。还有就是主库是写，对数据安全性较高。另外就是使用比主库更好的硬件设备作为slave。

六，MySQL主从复制实战

1.要求：两个CentOS7.x的虚拟机

MySQL 8.0.16

Master_IP:192.168.2.141

Slave_IP:192.168.2.142

1. 初始化

1. 同步时间

分别在主机，从机上执行以下步骤

yum -y install ntp ntpda te

Ntpdate cn.pool.ntp.org

Hwclock --systohc

关闭防火墙

分别在主机，从机上执行以下步骤：

Systemctl stop firewalld

Syst emctl disable firewalld

Sed -i ‘s/seinux=enforcing/selinux=disabled/g’ /etc/selinux/config

Setenforce 0

1. 配置主库

修改配置主库文件：开启log.bin功能，并指定server_id为1，（此处建议用IP后两位，方便记忆）

Vim /etc/my.cnf（主机配置）

Vim /etc/my.cnf（从机配置）

重启服务

systemctl restart mysqld.service

创建一个用于让数据库连接的用户

在从机上测试 用户的客户端是否可以正常登录

mysql -h192.168.2.141 -ucopy -pNebula@123

查询master(主机)的状态

show master status\G;记录下图结果中File和Position的值

1. 配置从库

此处根据上面主服务器的状态来填写，不要直接用下面的SQL，需要根据实际值修改。

CHANGE MASTER TO

master_host = ‘192.168.2.141’,   #主库的IP地址

master_user = ‘copy’,     #在主库上创建的复制账号

master_password = ‘Nebula@123’, #在主库上创建的复制账号密码

master_log_file = ‘mysql-bin.000001’, #开始复制的二进制文件名（从主库查询结果中获取）

master_log_pos = 826; #开始复制的二进制文件位置（从主库查询结果中获取）

查看从服务器状态

//开启复制

start slave;

//查看主从复制是否配置成功 （从机查看）

SHOW SLAVE STATUS\G

当看到如上才表明状态正常。

1.  Slave_SQL_Running: No 的解决方法

 在slave服务器上通过如下命令

显示如下情况表示slave不同步

解决方法(忽略错误，继续同步)：
1、先停掉slave
mysql> stop slave;
2、跳过错误步数，后面步数可变
mysql> set global sql_slave_skip_counter=1; 
3、再启动slave
mysql> start slave;
查看同步状态
mysql> show slave status\G;

Slave_IO_Running:No问题解决方法

1.检查发现他们的auto.cnf中的server-uuid是否相同

vim /var/lib/mysql/auto.cnf

2.停止从库的mysqld服务，删除他的auto.cnf文件，再启动数据库服务即可

systemctl stop mysqld.service

mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.bak

systemctl start mysqld.service

1. 此时再去查看从库auto.cnf，已自动生成新的server-uuid

vim/var/lib/mysql/auto.cnf

4.再启动slave

mysql> start slave;

查看同步状态

mysql> show slave status\G;

1. 测试主从复制是否成功

Master（主机）中和Slave(从机)中执行SQL

SHOW DATABASES;

在Master中创建数据库并创建数据表并插入一条数据

1.create database test;

2.use test;

3.create table tab1(id int auto_increment,name varchar(10),primary key(id));

4.insert into tab1(id,name) values (1,'why');

（建立test库进入创建tab1数据表）

在Slave中查询这条数据是否同步过来:

1. show databases;

2. use test;

3. show tables;

4. select * from tab1;

主从复制完成。