Mysql主从原理及配置

最新推荐文章于 2024-05-19 03:18:57 发布

小锋锋~wyf

最新推荐文章于 2024-05-19 03:18:57 发布

阅读量470

点赞数 1

分类专栏： DB 文章标签： mysql

本文链接：https://blog.csdn.net/adminWangyaofeng/article/details/53872183

版权

DB 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Mysql主从原理

MySQL的Replication（英文为复制）是一个多MySQL数据库做主从同步的方案，特点是异步复制，广泛用在各种对MySQL有更高性能、更高可靠性要求的场合。与之对应的是另一个同步技术是MySQL Cluster，但因为MySQL Cluster配置比较复杂，所以使用者较少。

MySQL的Replication是一个异步复制的过程（mysql5.1.7以上版本分为异步复制和半同步两种模式），它是从一个Mysql instance（instance英文为实例）(我们称之为Master)复制到另一个Mysql instance(我们称之slave)。在master与slave之间实现整个复制过程主要由三个线程来完成，其中两个线程(SQL线程和IO线程)在slave端，另外一个线程(IO线程)在master端。

要实现MySQL的Replication，首先必须打开master端的binlog (mysql-bin.xxxxxx)日志功能，否则无法实现mysql的主从复制。因为mysql的整个主从复制过程实际上就是：slave端从master端获取binlog日志，然后再在自己身上完全顺序的执行该日志中所记录的各种SQL操作。

有关具体如何开启mysql的binlog日志功能，
MySQL主从复制的基本交互过程，如下：
1、slave端的IO线程连接上master端，并请求从指定binlog日志文件的指定pos节点位置(或者从最开始的日志)开始复制之后的日志内容。
2、master端在接收到来自slave端的IO线程请求后，通知负责复制进程的IO线程，根据slave端IO线程的请求信息，读取指定binlog日志指定pos节点位置之后的日志信息，然后返回给slave端的IO线程。该返回信息中除了binlog日志所包含的信息之外，还包括本次返回的信息在master端的binlog文件名以及在该binlog日志中的pos节点位置。
3、slave端的IO线程在接收到master端IO返回的信息后，将接收到的binlog日志内容依次写入到slave端的relaylog文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的master端的binlog文件名和pos节点位置记录到master-info（该文件存在slave端）文件中，以便在下一次读取的时候能够清楚的告诉master“我需要从哪个binlog文件的哪个pos节点位置开始，请把此节点以后的日志内容发给我”。
4、slave端的SQL线程在检测到relaylog文件中新增内容后，会马上解析该log文件中的内容。然后还原成在master端真实执行的那些SQL语句，并在自身按顺丰依次执行这些SQL语句。这样，实际上就是在master端和slave端执行了同样的SQL语句，所以master端和slave端的数据是完全一样的。
以上mysql主从复制交互过程比较拗口，理解起来也比较麻烦，我简化了该交互过程。如下：

1、master在执行sql之后，记录二进制log文件（bin-log）。
2、slave连接master，并从master获取binlog，存于本地relay-log中，然后从上次记住的位置起执行SQL语句，一旦遇到错误则停止同步。

从以上mysql的Replication原理可以看出：

主从间的数据库不是实时同步，就算网络连接正常，也存在瞬间主从数据不一致的情况。
如果主从的网络断开，则从库会在网络恢复正常后，批量进行同步。
如果对从库进行修改数据，那么如果此时从库正在在执行主库的bin-log时，则会出现错误而停止同步，这个是很危险的操作。所以一般情况下，我们要非常小心的修改从库上的数据。
一个衍生的配置是双主、互为主从配置，只要双方的修改不冲突，则可以工作良好。
如果需要多主库的话，可以用环形配置，这样任意一个节点的修改都可以同步到所有节点

Mysql主从配置—linux

主服务器：从服务器ip地址分别为

192.168.1.35、192.168.1.37

1、修改主服务器master

vi /etc/my.cnf  
[mysqld]  
   log-bin=mysql-bin   #[必须]启用二进制日志  
   server-id=35      #[必须]服务器唯一ID，默认是1，一般取IP最后一段

2、修改从服务器slave

vi /etc/my.cnf  
[mysqld]  
    log-bin=mysql-bin   #[不是必须]启用二进制日志  
    server-id=37      #[必须]服务器唯一ID，默认是1，一般取IP最后一段

3、重启两台服务器的MySQL

systemctl restart mariadb

4、在主服务器上建立帐户并授权slave

mysql  
mysql>GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'q123456'; 
//一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.1.37，加强安全。

5、登录主服务器的mysql，查询master的状态

 mysql>show master status;  
 +------------------+----------+--------------+------------------+  
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
 +------------------+----------+--------------+------------------+  
 | mysql-bin.000004 |      308 |              |                  |  
 +------------------+----------+--------------+------------------+  
 1 row in set (0.00 sec)

注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

6、配置从服务器Slave

mysql>change master to master_host='192.168.11.35',master_user='mysync',master_password='q123456',master_log_file='mysql-bin.000004',master_log_pos=308; //注意mysql-bin.000004和308是第五步中的File和Position  
mysql>start slave; //启动从服务器复制功能

7、检查从服务器复制功能状态

mysql> show slave status\G  
************************** 1. row ***************************
          Slave_IO_State: Waiting for master to send event  
          Master_Host: 192.168.1.25  //主服务器地址  
          Master_User: mysync   //授权帐户名，尽量避免使用root  
          Master_Port: 3306    //数据库端口，部分版本没有此行  
          Connect_Retry: 60  
          Master_Log_File: mysql-bin.000004  
          Read_Master_Log_Pos: 600     //同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos  
          Relay_Log_File: ddte-relay-bin.000003  
          Relay_Log_Pos: 251  
          Relay_Master_Log_File: mysql-bin.000004  
          Slave_IO_Running: Yes    //此状态必须YES  
          Slave_SQL_Running: Yes     //此状态必须YES 
              ......

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)
以上操作过程，主从服务器配置完成。

8、主从服务器测试
主服务器Mysql，建立数据库，并在这个库中建表插入一条数据：

 mysql> create database hi_db;  
   Query OK, 1 row affected (0.00 sec)  

   mysql> use hi_db;  
   Database changed  

   mysql>  create table hi_tb(id int(3),name char(10));  
   Query OK, 0 rows affected (0.00 sec)  

   mysql> insert into hi_tb values(001,'bobu');  
   Query OK, 1 row affected (0.00 sec)  

   mysql> show databases;  
     +--------------------+  
     | Database           |  
     +--------------------+  
     | information_schema |  
     | hi_db                |  
     | mysql                |  
     | test                 |  
     +--------------------+  
     4 rows in set (0.00 sec)  

从服务器Mysql查询：  

    mysql> show databases;  

     +--------------------+  
     | Database               |  
     +--------------------+  
     | information_schema |  
     | hi_db                 |       //I'M here，大家看到了吧  
     | mysql                 |  
     | test          |  

     +--------------------+  
     4 rows in set (0.00 sec)  

     mysql> use hi_db  
     Database changed  
     mysql> select * from hi_tb;           //查看主服务器上新增的具体数据  
     +------+------+  
     | id   | name |  
     +------+------+  
     |    1 | bobu |  
     +------+------+  
     1 row in set (0.00 sec)

Mysql主从配置—windows

一、安装Mysql

1、解压这里写图片描述
2、将解压的文件放一个目录（我是放在phpstudy目录下的）

这里写图片描述

3、配置 mysql环境变量 ( 修改PATH，添加E:\phpStudy\mysql-5.6.24\bin )
4、E:\phpStudy\mysql-5.6.24 目录下新建my.ini 配置文件

[mysqld]
port=3307
basedir=E:\phpStudy\mysql-5.6.24
datadir=E:\phpStudy\mysql-5.6.24\data

//auto-increment-offset = 2  # 设定数据库中自动增长的起点
//auto-increment-increment= 2 # 应设为整个结构中服务器的总数

5、管理员身份打开cmd 进入E:\phpStudy\mysql-5.6.24\bin

Mysqld -install

net start mysql   启动mysql服务
Net stop mysql   停止mysql服务
net start mysql   启动mysql服务

—-服务保持开启—-

二、配置Mysql主从复制

1．添加一个mysql主从复制需要的账号
grant 权限 on 数据库对象 to 用户

GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* 
TO mysql_backup@'%'
IDENTIFIED BY '123456';
flush privileges;   //刷新MySQL的系统权限相关表,否则会出现拒绝访问

2、配置主服务器
mysql配置文件加入：

server-id=1   #主服务器A的ID值 
log-bin=mysql-bin  #二进制变更日志

//auto-increment-offset = 1  # 设定数据库中自动增长的起点
//auto-increment-increment= 2 # 应设为整个结构中服务器的总数

重启master，运行SHOW MASTER STATUS，输出如下：
查看master数据库当前正在使用的二进制日志及当前执行二进制日志位置

这里写图片描述
3、配置从服务器
Slave的配置与master类似，修改配置同样需要重启slave的MySQL。如下：

log_bin           = mysql-bin
server_id         = 2
relay_log         = mysql-relay-bin
log_slave_updates = 1
read_only         = 1

      server_id是必须的，而且唯一。slave没有必要开启二进制日志，但是在一些情况下，必须设置，例如，如果slave为其它slave的master，必须设置bin_log。在这里，我们开启了二进制日志，而且显示的命名(默认名称为hostname，但是，如果hostname改变则会出现问题)。
       relay_log配置中继日志，log_slave_updates表示slave将复制事件写进自己的二进制日志(后面会看到它的用处)。
      有些人开启了slave的二进制日志，却没有设置log_slave_updates，然后查看slave的数据是否改变，这是一种错误的配置。所以，尽量使用read_only，它防止改变数据(除了特殊的线程)。但是，read_only并是很实用，特别是那些需要在slave上创建表的应用。

*连接主库

CHANGE MASTER TO master_host = '127.0.0.1',
 master_user = 'mysql_backup',
 master_password = '123456',
 master_log_file = 'mysql-bin.000112',
 master_log_pos = 2916;

*启动从库

Start slave

*停止从库

Stop slave

*查看从库状态

Show slave status

这里写图片描述

恭喜您配置成功，去测试！

小锋锋~wyf

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql主从原理及配置

Mysql主从原理MySQL的Replication（英文为复制）是一个多MySQL数据库做主从同步的方案，特点是异步复制，广泛用在各种对MySQL有更高性能、更高可靠性要求的场合。与之对应的是另一个同步技术是MySQL Cluster，但因为MySQL Cluster配置比较复杂，所以使用者较少。MySQL的Replication是一个异步复制的过程（mysql5.1.7以上版本分为异步复制和半同
复制链接

扫一扫