mysql主从配置实践

基本概念

参考资料:MySQL官网-第十六章-主从复制

复制使数据从一个MySQL数据库服务器(主)复制到一个或多个MySQL数据库服务器(即奴隶)。默认情况下,复制是异步的;奴隶不需要被永久连接来接收来自主人的更新。根据配置的不同,您可以在数据库中复制所有数据库、选定的数据库,甚至选择表。

MySQL主从复制是其最重要的功能之一。主从复制是指一台服务器充当主数据库服务器,另一台或多台服务器充当从数据库服务器,主服务器中的数据自动复制到从服务器之中。对于多级复制,数据库服务器即可充当主机,也可充当从机。MySQL主从复制的基础是主服务器对数据库修改记录二进制日志,从服务器通过主服务器的二进制日志自动执行更新。

主从复制的优点

  • 扩展解决方案——将负载分散到多个服务器中以提高性能。在这个环境中,所有的写入和更新都必须在主服务器上进行。然而,查询和读取可能发生在一个或多个从服务器身上。这个模型可以提高写的性能(因为master是专门用来更新的),而在越来越多的奴隶中,读取速度显著提高。

  • 安全性——因为数据被复制到从服务器,而从服务器可以暂停复制进程,因此可以在从服务器上运行备份服务,而不会损坏相应的主数据。

  • 分析——可以在主服务器上创建实时数据,而对信息的分析可以在不影响主服务器性能的情况下对其进行分析。

  • 远程数据分发——您可以使用复制来为远程站点创建数据的本地副本,而不需要永久访问主服务器。

复制方式

从MySQL5.6开始,主从复制有两种方式:

  • 基于日志(binlog)
    传统的方法是基于从主二进制日志中复制事件,并要求日志文件和它们之间的位置在主和从之间同步。有关使用二进制日志文件位置复制的信息,请参阅第16.1.2节“配置复制”

  • 基于GTID(全局事务标示符)
    基于全局事务标识符(GTIDs)的新方法是事务性的,因此不需要在这些文件中使用日志文件或位置,这大大简化了许多常见的复制任务。使用GTIDs的复制保证了主服务器和从服务器之间的一致性,前提是主服务器的所有事务都被应用到从服务器身上。有关在MySQL中关于GTIDs和基于gtid的复制的更多信息,请参见第16.1.3节“使用全局事务标识符的复制”。

本篇是基于日志binlog的主从配置。

日志binlog主从复制原理

  1. Master将数据改变记录到二进制日志(binary log)中,也就是配置文件log-bin指定的文件,这些记录叫做二进制日志事件(binary log events)
  2. Slave通过I/O线程读取Master中的binary log events并写入到它的中继日志(relay log)
  3. Slave重做中继日志中的事件,把中继日志中的事件信息一条一条的在本地执行一次,完成数据在本地的存储,从而实现将改变反映到它自己的数据

实践步骤

为了避免产生不必要的错误信息,这里主从服务器的系统都是centos7.4,MySQL的版本都是5.7.21
主服务器IP:101.200.186.40
从服务器IP:47.94.238.24

主库的操作

  1. 关闭防火墙&停止MySQL服务

    // 关闭防火墙
    systemctl stop firewalld.service
    // 停止MySQL服务
    service mysqld stop

    master-0
  2. 配置my.cnf
    vim /etc/my.cnf
    // 配置日志
    [mysqld]
    log-bin = mysql-bin
    server-id = 1
    
  3. 启动MySQL服务

    service mysqld start

    master-1
  4. 登录MySQL&创建MySQL账户
    每个从库使用MySQL用户名和密码连接到主库,因此主库上必须有用户帐户,从库可以连接。任何帐户都可以用于此操作,只要它已被授予 REPLICATION SLAVE权限。可以选择为每个从库创建不同的帐户,或者每个从库使用相同帐户连接到主库。
    虽然不必专门为复制创建帐户,但应注意,复制用到的用户名和密码会以纯文本格式存储在主信息存储库文件或表中 。因此,需要创建一个单独的帐户,该帐户只具有复制过程的权限,以尽可能减少对其他帐户的危害。
    CREATE USER 'mser'@'47.94.238.24' IDENTIFIED BY 'mser123';
    GRANT REPLICATION SLAVE ON *.* TO 'mser'@'47.94.238.24'; 
    

从库的操作

  1. 停止MySQL服务。

    service mysql.server stop

    slaver-0
  2. 配置my.cnf&重启MySQL服务

    vim /etc/my.cnf
    // 配置从库复制
    [mysqld]
    server-id=2
    service mysql.server start

    slaver-1
  3. 查看主服务器 binlog File 文件名称和 Position值位置

    // 登录主服务器MySQL
    mysql -uroot -p
    // 查看 binlog File 文件名称和 Position值位置
    show master status;

    master-2
  4. 从库上配置与主库通信设置

    mysql -uroot -p*
    CHANGE MASTER TO MASTER_HOST=’101.200.186.40’, MASTER_USER=’mser’, MASTER_PASSWORD=’mser123’, MASTER_LOG_FILE=’mysql-bin.000019’, MASTER_LOG_POS=621;
    // 启动从服务器复制线程
    START SLAVE;

    slaver-2
  5. 检查主从复制通信状态

    show slave status\G

    slaver-3
    Slave_IO_State 从站的当前状态
    Slave_IO_Running: Yes 读取主程序二进制日志的I/O线程是否正在运行
    Slave_SQL_Running: Yes 执行读取主服务器中二进制日志事件的SQL线程是否正在运行。与I/O线程一样
    Seconds_Behind_Master 是否为0,0就是已经同步了
  6. 测试主从复制

    // 登录主服务器MySQL
    mysql -uroot -p
    // 创建库、表
    CREATE DATABASE ms;
    use ms;
    create TABLE master_slave_test……..//省略
    show tables;

    master-3
    此时登录从服务器MySQL可以查看到库表已经同步
    slaver-test

总结梳理

MySQL主从复制使用了三个线程,其中一个在主服务器上,另一个在从服务器上
master-slave

  1. 主服务器的Binlog转储线程
    当从服务器与主服务器连接时,主服务器会创建一个线程将二进制日志内容发送到从服务器。该线程可以使用语句SHOW PROCESSLIST在服务器 mysql 控制台输出中标识为Binlog Dump线程。二进制日志转储线程获取服务器上二进制日志上的锁,用于读取要发送到从服务器的每个事件。一旦事件被读取,即使在将事件发送到从服务器之前,锁会被释放。

  2. 从服务器I/O线程
    当在从服务器mysql 控制台发出 START SLAVE 语句时,从服务器将创建一个I/O线程,该线程连接到主服务器,并要求它发送记录在主服务器上的二进制更新日志。从机I/O线程读取主服务器Binlog Dump线程发送的更新 (参考上面 Binlog转储线程 介绍),并将它们复制到自己的本地文件二进制日志中。该线程的状态详情,可使用 SHOW SLAVE STATUS\G 查看。

  3. 从服务器SQL线程
    从服务器创建一个SQL线程来读取由I/O线程写入的二级制日志,并执行其中包含的事件。

如上所述,每个主/从连接有三个线程。主服务器为每个当前连接的从服务器创建一个二进制日志转储线程,每个从服务器都有自己的I/O和SQL线程。
从服务器使用两个线程将读取更新与主服务器更新事件,并将其执行为独立任务。因此,如果语句执行缓慢,则读取语句的任务不会减慢。

例如,如果从服务器开始几分钟没有运行,或者即使SQL线程远远落后,它的I/O线程也可以从主服务器建立连接时,快速获取所有二进制日志内容。

如果从服务器在SQL线程执行所有获取的语句之前停止,则I/O线程至少获取已经读取到的内容,以便将语句的安全副本存储在自己的二级制日志文件中,准备下次执行主从服务器建立连接,继续同步。

chosen1cwp wechat
欢迎您扫一扫上面的微信公众号,订阅我的博客!