mysql 创建主从数据库_Mysql数据库主从搭建

1、MySQL 主从复制原理

主库将变更写入 binlog 日志，然后从库连接到主库之后，从库有一个 IO 线程，将主库的 binlog 日志拷贝到自己本地，写入一个 relay 中继日志中。接着从库中有一个 SQL 线程会从中继日志读取 binlog，然后执行 binlog 日志中的内容，也就是在自己本地再次执行一遍 SQL，这样就可以保证自己跟主库的数据是一样的。

注意：

从库同步主库数据的过程是串行化的，也就是说主库上并行的操作，在从库上会串行执行。由于从库从主库拷贝日志以及串行执行 SQL 的特点，在高并发场景下，从库的数据一定会比主库慢一些，是有延时的。所以经常出现，刚写入主库的数据可能是读不到的，要过几十毫秒，甚至几百毫秒才能读取到。

如果主库突然宕机，然后恰好数据还没同步到从库，那么有些数据可能在从库上是没有的，有些数据可能就丢失了。

一般来说，如果主从延迟较为严重，有以下解决方案：

分库，将一个主库拆分为多个主库，每个主库的写并发就减少了几倍，此时主从延迟可以忽略不计。

打开 MySQL 支持的并行复制，多个库并行复制。如果说某个库的写入并发就是特别高，单库写并发达到了 2000/s，并行复制还是没意义。

重写代码，写代码的同学，要慎重，插入数据时立马查询可能查不到。

如果确实是存在必须先插入，立马要求就查询到，然后立马就要反过来执行一些操作，对这个查询设置直连主库。

2、创建三个虚拟机，mysql 一主两从

1、192.168.8.162 主

2、192.168.8.134 从

3、192.168.8.176 从

3、Mysql的主从配置

a.配置主库

192.168.8.162是window10系统，mysql版本是mysql5.7.31，设置成主库

在E:\Program Files\mysql-5.7.31-winx64\my.ini中添加一下

#server-id给数据库服务的唯一标识

server-id=1

#

##log-bin设置此参数表示启用binlog功能，并指定路径名称

log-bin=E:\Program Files\mysql-5.7.31-winx64\data\mysqlbin

sync_binlog=0

##设置日志的过期天数

expire_logs_days=7

binlog-do-db=test1

binlog-do-db=test2

binlog-ignore-db=information_schema

binlog-ignore-db=sys

binlog-ignore-db=mysql

binlog-ignore-db=performance_schema

这里的server-id用于标识唯一的数据库，在从库必须设置为不同的值。

binlog-ignore-db：表示同步的时候忽略的数据库

binlog-do-db：指定需要同步的数据库

重启数据库，重启成功后，登录mysql

赋予从库权限账号，允许用户在主库上读取日志，赋予192.168.8.134和192.168.8.176也就是Slave机器有File权限，

只赋予Slave机器有File权限还不行，还要给它REPLICATION SLAVE的权限才可以。

grant FILE on *.* to 'root'@'192.168.8.134' identified by 'root';

grant replication slave on *.* to 'root'@'192.168.8.134' identified by 'root';

flush privileges;

grant FILE on *.* to 'root'@'192.168.8.176' identified by 'root';

grant replication slave on *.* to 'root'@'192.168.8.176' identified by 'root';

flush privileges;

这里的用户是同步的时候从库使用的用户。

查看主库信息

show master status;

File是同步会使用到的binlog文件，Position是同步的时候也要用到的

b、在window中配置从库

192.168.8.176 是window2012R2系统，mysql版本是mysql8.0.18，设置成从库1

在C:\mysql-8.0.18-winx64\my.ini中添加一下

log-bin=mysqlbin

server-id=3

binlog-ignore-db=information_schema

binlog-ignore-db=sys

binlog-ignore-db=mysql

replicate-ignore-db=mysql

replicate-do-db=test1

replicate-do-db=test2

log-slave-updates

slave-skip-errors=all

slave-net-timeout=60

注意：两个从库的server-id不一样，需要唯一。小于主库

修改完my.cnf后，重启一下mysql。重启成功后，登录mysql

stop slave;

change master to master_host='192.168.8.162',master_user='root',master_password='root',master_log_file='mysqlbin.000001', master_log_pos=1580;

start slave;

注意：上面的master_log_file是在主配Master中show master status显示的File，

而master_log_pos是在主配Master中show master status显示的Position。

然后可以通过show slave status \G查看配置信息

上面的信息有Slave_IO_Running: Yes和Slave_SQL_Running: Yes，证明主从同步成功。

出错清理掉之前的配置，防止同步已经同步了的数据，执行以下命令

stop slave;

reset slave all;

c、在linux中配置从库

192.168.8.134 是centor7系统，mysql版本是mysql5.7.31，设置成从库2

修改/etc/my.cnf

vi /etc/my.cnf添加

log-bin=mysqlbin

server-id=2

binlog-ignore-db=information_schema

binlog-ignore-db=sys

binlog-ignore-db=mysql

replicate-ignore-db=mysql

replicate-do-db=test1

replicate-do-db=test2

log-slave-updates

slave-skip-errors=all

slave-net-timeout=60

注意：两个从库的server-id不一样，需要唯一。小于主库。

修改完my.cnf后，重启一下mysql。重启成功后，登录mysql。

service mysql restart

stop slave;

change master to master_host='192.168.8.162',master_user='root',master_password='root',master_log_file='mysqlbin.000001', master_log_pos=1580;

start slave;

注意：上面的master_log_file是在主配Master中show master status显示的File，

而master_log_pos是在主配Master中show master status显示的Position。

然后可以通过show slave status \G查看配置信息

面的信息有Slave_IO_Running: Yes和Slave_SQL_Running: Yes，证明主从同步成功。

出错清理掉之前的配置，防止同步已经同步了的数据，执行以下命令

stop slave;

reset slave all;

4、验证主从配置

操作完成后。可以在Master建表插入数据，然后再从2个库中查看，如果2个都有数据，则证明主从数据库同步成功。