mysql中配置主从复制 (原创)

Mysql的主从复制

一、主从复制原理：

主从同步过程中主服务器有一个工作线程I/O dump thread，从服务器有两个工作线程I/O thread和SQL thread。

主库把外界接收的SQL请求记录到自己的binlog日志中，从库的I/O thread去请求主库的binlog日志，并将binlog日志写到中继日志中，然后从库的SQL线程会重做中继日志的SQL语句。主库通过I/O dump thread给从库I/O thread传送binlog日志。

更详细的原理说明

二、主从复制的相关配置

1、主节点配置：

# 服务器ID 必须唯一
server-id=2

# 开启及设置二进制日志文件名称
log_bin=mysql-bin

# 要同步的数据库 ，不指定这个参数则会同步除ignore的db外的所有db
#binlog-do-db =mydb

#不需要同步的数据库
binlog-ignore-db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=performation_schema
binlog_ignore_db=sys

#设置logbin格式
#binlog日志格式，mysql默认采用statement(效率高，但是不能记录一些函数的调用)，Row 行模式， 建议使用mixed(二者的混合模式)
#binlog_format= MIXED 
binlog_format= STATEMENT

2、从节点配置：

#配置从节点
server-id=3
relay-log=mysql-relay

3、关于binlog的三种模式的选择

(1) Row 模式
日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改，只记录要修改的数据，只有value，不会有sql多表关联的情况。
优点：在row模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了，所以row的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程和function，以及trigger的调用和出发无法被正确复制问题。
缺点：在row模式下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

 
(2) Statement 模式
每一条会修改数据的sql都会记录到master的binlog中，slave在复制的时候sql进程会解析成和原来master端执行多相同的sql再执行。
优点：在statement模式下首先就是解决了row模式的缺点，不需要记录每一行数据的变化减少了binlog日志量，节省了I/O以及存储资源，提高性能。因为他只需要记录在master上所执行的语句的细节以及执行语句的上下文信息。
缺点：在statement模式下，由于他是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。另外就是，由于mysql现在发展比较快，很多的新功能不断的加入，使mysql的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement中，目前已经发现不少情况会造成Mysql的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep()函数在有些版本中就不能被正确复制，在存储过程中使用了last_insert_id()函数，可能会使slave和master上得到不一致的id等等。由于row是基于每一行来记录的变化，所以不会出现，类似的问题。

(3)Mixed 模式

从官方文档中看到，之前的 MySQL 一直都只有基于 statement 的复制模式，直到 5.1.5 版本的 MySQL 才开始支持 row 复制。从 5.0 开始，MySQL 的复制已经解决了大量老版本中出现的无法正确复制的问题。但是由于存储过程的出现，给 MySQL Replication 又带来了更大的新挑战。另外，看到官方文档说，从 5.1.8 版本开始，MySQL 提供了除 Statement 和 Row 之外的第三种复制模式：Mixed，实际上就是前两种模式的结合。在 Mixed 模式下，MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种。新版本中的 statment 还是和以前一样，仅仅记录执行的语句。而新版本的 MySQL 中对 row 模式也被做了优化，并不是所有的修改都会以 row 模式来记录，比如遇到表结构变更的时候就会以 statement 模式来记录，如果 SQL 语句确实就是 update 或者 delete 等修改数据的语句，那么还是会记录所有行的变更。

生产环境中，建议使用Mixed 模式

三、使用docker方式实现主从复制(示例)

mkdir work1 && cd work1

vi docker-compose.yml，内容如下：

version: '3'
services:
 
  mysql1:
    container_name: mysql1
    image: mysql:5.7
    ports:
      - "3307:3306"
    environment:
      - MYSQL_ROOT_PASSWORD=123456
    volumes:
      - "/data/mysql1/lib:/var/lib/mysql"
      - "/data/mysql1/mysql.conf.d:/etc/mysql/mysql.conf.d"
      - "/data/mysql1/log:/var/log"
    networks:
      mynet:
        ipv4_address: 10.0.0.2

  mysql2:
    container_name: mysql2
    image: mysql:5.7
    ports:
      - "3308:3306"
    environment:
      - MYSQL_ROOT_PASSWORD=123456
    volumes:
      - "/data/mysql2/lib:/var/lib/mysql"
      - "/data/mysql2/mysql.conf.d:/etc/mysql/mysql.conf.d"
      - "/data/mysql2/log:/var/log"
    networks:
      mynet:
        ipv4_address: 10.0.0.3  


networks:
  mynet:
    driver: bridge
    ipam:
      driver: default
      config:
      - subnet: 10.0.0.0/24

mkdir -p /data/mysql1

docker pull mysql:5.7
docker run --rm --name mysql  mysql:5.7  sh
用docker cp命令，将 mysql容器中的/etc/mysql/mysql.conf.d 目录复制到宿主机/data/mysql1/目录中
用docker cp命令，将 mysql容器中的/var/lib/mysql 目录复制到宿主机/data/mysql1/目录中
用docker cp命令，将 mysql容器中的/var/log 目录复制到宿主机/data/mysql1/目录中

cd /data
cp -rf mysql1 mysql2 #同理复制出mysql2

启动docker容器 ，分别mysql1的配置和mysql2的配置

cd /root/work1
docker-compose up -d 

vi /data/mysql1/mysql.conf.d/mysqld.cnf ，在[mysqld]下新增内容

# 服务器ID 必须唯一
server-id=2

# 开启及设置二进制日志文件名称
log_bin=mysql-bin

# 要同步的数据库 ，不指定这个参数则会同步除ignore的db外的所有db
#binlog-do-db =mydb

#不需要同步的数据库
binlog-ignore-db=mysql    
binlog_ignore_db=information_schema
binlog_ignore_db=performation_schema
binlog_ignore_db=sys

#设置logbin格式
#binlog日志格式，mysql默认采用statement(效率高，但是不能记录一些函数的调用)，Row 行模式， 建议使用mixed(二者的混合模式)
#binlog_format= MIXED 
binlog_format= STATEMENT

vi  /data/mysql2/mysql.conf.d/mysqld.cnf，在[mysqld]下新增内容

#配置从节点
server-id=3
relay-log=mysql-relay 

重启docker容器
docker-compose down 
docker-compose up -d 

数据库master上执行：
 SHOW MASTER STATUS ;

-- master 中创建用户 ，并授权
CREATE USER 'slave1'@'10.0.0.3' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'10.0.0.3';
FLUSH PRIVILEGES;

-- 重置主节点   bin-log日志重置
-- RESET MASTER ;
========================================

数据库slave上执行：
SHOW SLAVE STATUS ;

CHANGE MASTER TO MASTER_HOST='10.0.0.2', MASTER_USER='slave1',MASTER_PASSWORD='123456', 
MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=757;
#启动主从复制
START SLAVE;
#查看从机状态；
SHOW SLAVE STATUS

-- 重置从节点
-- STOP SLAVE ;

CHANGE MASTER TO MASTER_HOST='10.0.0.2', MASTER_USER='slave1',MASTER_PASSWORD='123456', 
MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=757;  -- position的位置很重要

START SLAVE ;

最终实现的效果是，用户对主服务器做的所有修改操作(insert、update、delete、建库、建表等)都会被从服务器所同步 。