主从同步（多结构主从）

​ 可以实现数据自动同步的服务结构，大大提升了数据的安全可靠性，当正在提供服务的数据库实例宕机时，另外一台数据库实例也可以提供完整的可靠的数据。

主从前提：master必须启用binlog日志，设置server-id。

实现原理：

​ 在开启主从后，slave会开启两个进程，分别是Slave_IO、Slave_SQL。

Slave_IO：

​ 在master节点上添加一个角色并赋予replication slave权限，Slave_IO从节点slave会使用这个master授权账户去master主节点拷贝binlog日志SQL命令到slave的relay-log（中继日志）文件。会在该mysql的默认data目录生成relay-log.info记录了当前使用的中继日志文件名、偏移量、master的binlog文件名、偏移量等信息，主机名-relay-bin.index ,中继日志文件索引文件 记录了当前使用的中继文件名。主机名-relay-bin.XXXXXX中继日志文件 记录了Slave_IO从master上拷过来的SQL语句。

Slave_SQL:

​ 执行本机relay-log文件里的SQL语句，实现与Master数据一致。

主从同步有三种结构：主从、链式复制（主从从）、一主多从、互为主从。

• 主从：至少两个数据库实例组成。A是主实例（master节点），B是从实例（slave节点）。A作为客户端时时连接使用的数据库实例，B数据库实例时时复制A服务器的binlog日志d的SQL语句执行。

  A （主）----B（从）

• 链式复制：至少由三个数据库实例组成。A（Master）作为客户端时时连接使用的数据库实例，B（slave）实例时时复制A（master）实例的binlog日志d的SQL语句执行，但是也是作为C（slave）实例的主实例。

  A （主）----B(从，主)-----C（从）

• 一主多从：至少由三个数据库实例组成，A（Master）作为客户端时时连接使用的数据库实例，BC..(slave)都作为A（Master）的从库都从A进行复制binlog日志SQL命令。与链式复制不同的是一主多从，从都向 同一台Master 拷贝他的binlog进行执行。

  A（主）-----BC...(从）

• 互为主从 ：至少由两个数据库实例组成，AB他们都是对方的从库，也同时是对方的主库。不管在那台上执行，对方都会拷贝binlog日志SQL命令进行执行。

  A（主，从） --- B（主，从）

• 链式复制的搭建：

  • 一、启用master节点的binlog日志，设置server_id。若实例既为master又为slave的中间实例必须在mysqld下打开允许log_slave_updates允许级联复制。并重启服务，检查binlog是否启用成功，并备份传到到中间实例。

    # 主实例master1:192.168.4.1    (中间实例)master-slave2:192.168.4.2     从实例slave2:192.168.4.3
    # 主实例开启binlog
    sed -i '4a log_bin=master1' /etc/my.cnf
    # 主实例设置服务器ID
    sed -i '5a server_id=1' /etc/my.cnf
    # 主实例修改日志记录模式为混合模式（你还可以修改为行模式、报表模式、混合模式请参考我的这篇https://www.cnblogs.com/lqinghua/p/11675093.html）
    sed -i '6a binlog_format="Mixed"' /etc/my.cnf
    # 你还可以修改复制模式（异步模式、全同步模式、半同步模式请参考我的这篇https://www.cnblogs.com/lqinghua/p/11687636.html）
    # 重启mysql服务并检查是否正确开启了binlog
    systemctl restart mysqld && mysql -uroot -p12345 -e "show master status \G"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    *************************** 1. row ***************************
                 File: master1.000001
             Position: 154
         Binlog_Do_DB: 
     Binlog_Ignore_DB: 
    Executed_Gtid_Set:
    # 正确开启了binlog二进制日志记录，现在添加从服务器角色授权，我写博客图方便就全部授权了所有数据库。
    mysql -uroot -p12345 -e 'grant replication slave on *.* to slave@"%" identified by "123456";'
    mysql -uroot -p12345 -e 'show grants for slave@"%";'
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +-----------------------------------------------+
    | Grants for slave@%                            |
    +-----------------------------------------------+
    | GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' |
    +-----------------------------------------------+
    # 刷新权限
    mysql -uroot -p12345 -e "flush privileges;" 
    # 授权成功,那么开始备份数据库的数据，这边我使用mysqldump进行备份。
    # 重要1：生产环境中因为mysqldump会锁库锁表务必谨慎操作，免得影响生产业务。
    # 我这里还使用了--master-data参数用来记录当前实例的binlog日志文件名和偏移量。
    mysqldump -uroot -p12345 --master-data -B testdb userdb new1 > /tmp/fullback.sql
    # 来查看下备份的sql文件中的binlog当前文件名和偏移量，看博文的请将grep后的master1 改成你设置的名字，如果在主配置文件中没有指定叫什么 是log_bin，那么就请换成你的主机名。 
    grep master1 /tmp/fullback.sql 
    CHANGE MASTER TO MASTER_LOG_FILE='master1.000001', MASTER_LOG_POS=438;
    scp /tmp/fullback.sql root@192.168.4.2:/tmp/
    

    • 中间实例的配置（中间实例是master1（192.168.4.1）的从库，同时它也是实例slave2:192.168.4.3的主库），所以也需要打开主配置文件的binlog，并设置server_id，然后将他设置为master的从实例后，先检查是否成功。

    # 现在开始中间实例的配置，配置主配置文件/etc/my.cnf。
    sed -i '4a log_bin=master-slave2' /etc/my.cnf
    # 多个server_id不允许重复。
    sed -i '5a server_id=2' /etc/my.cnf
    # 修改日志记录模式。
    sed -i '6a binlog_format="Mixed"' /etc/my.cnf
    # 重要：因为是链式复制，本实例即为主也为从必须要打开log_slave_updates允许级联复制。
    sed -i '7a log_slave_updates' /etc/my.cnf
    # 重启mysql服务并查看是否开启记录二进制文件。
    systemctl restart mysqld && mysql -uroot -p12345 -e "show master status \G"mysql: [Warning] Using a password on the command line interface can be insecure.
    *************************** 1. row ***************************
                 File: master-slave2.000001
             Position: 154
         Binlog_Do_DB: 
     Binlog_Ignore_DB: 
    Executed_Gtid_Set:
    # 恢复从msater导过来的数据库数据。
    mysql -uroot -p12345 < /tmp/fullback.sql
    # 查看是否导入成功。
    [root@master-slave2 ~]# mysql -uroot -p12345  -e "show databases;"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | new1               |
    | performance_schema |
    | sys                |
    | testdb             |
    | userdb             |
    +--------------------+
    # 添加主实例信息。因为导入的备份的sql文件中已经含有“CHANGE MASTER TO MASTER_LOG_FILE='master1.000001', MASTER_LOG_POS=438;” 指定即可
    mysql -uroot -p12345  -e 'change master to master_user="slave",master_password="12345",master_host="192.168.4.1",master_log_file="master1.000001",master_log_pos=438;'
    # 开启同步
    mysql -uroot -p12345 -e "start slave;"
    # 查看IO线程和SQL线程
    mysql -uroot -p12345 -e "show slave status \G" | grep IO_Running
    mysql: [Warning] Using a password on the command line interface can be insecure.
                 Slave_IO_Running: Yes
    mysql -uroot -p12345 -e "show slave status \G" | grep SQL_Running |head -1
    mysql: [Warning] Using a password on the command line interface can be insecure.
                Slave_SQL_Running: Yes
    # 那么到这里已经实现了本中间实例做master1实例的从节点。
    

    • 中间实例做从实例slave2:192.168.4.3实例的主节点。这里最主要是在主配置文件中要确定打开了log_slave_updates

    # 在确定一次是否打开了允许级联复制
    grep log_slave_updates /etc/my.cnf
    # 授权从实例同步账号
    mysql -uroot -p12345 -e 'grant replication slave on *.* to slave@"%" identified by "123456";'
    mysql -uroot -p12345 -e 'show grants for slave@"%";'
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +-----------------------------------------------+
    | Grants for slave@%                            |
    +-----------------------------------------------+
    | GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' |
    +-----------------------------------------------+
    # 刷新权限
    mysql -uroot -p12345 -e "flush privileges;" 
    # 导出数据库复制到从实例slave2:192.168.4.3
    mysqldump -uroot -p12345 --master-data -B testdb userdb new1 > /tmp/fullbackmaster2.sql
    scp /tmp/fullbackmaster2.sql root@192.168.4.3:/tmp
    

    • 最后一步修改从实例192.168.4.3的主配置文件/etc/my.cnf server_id ,并导入数据，添加master信息，开启同步。

    # 设置server_id,并重启服务
    sed -i '5a server_id=3' /etc/my.cnf && systemctl restart mysqld
    # 导入备份实例数据
    mysql -uroot -p12345 -e "show databases;"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | new1               |
    | performance_schema |
    | sys                |
    | testdb             |
    | userdb             |
    +--------------------+
    # 得到备份文件中的logfile名和偏移量
    grep master-slave2 /tmp/fullbackmaster2.sql 
    CHANGE MASTER TO MASTER_LOG_FILE='master-slave2.000001', MASTER_LOG_POS=797351;
    # 设定同步master实例的信息
    mysql -uroot -p12345 -e 'change  master to  master_user="slave",master_password="123456",master_host="192.168.4.2",master_log_file="master-slave2.000001",master_log_pos=797351;'
    # 查看IO线程和SQL线程是否OK
    mysql -uroot -p12345 -e "show slave status \G" | grep IO_Running
    mysql: [Warning] Using a password on the command line interface can be insecure.
                 Slave_IO_Running: Yes
    mysql -uroot -p12345 -e "show slave status \G" | grep SQL_Running |head -1
    mysql: [Warning] Using a password on the command line interface can be insecure.
                Slave_SQL_Running: Yes
    

    • 测试阶段：

    # 在实例master1（192.168.4.1）上创建一个数据库，创建一个表，插入一条数据看下。
    mysql -uroot -p12345 -e 'create database test1;'
    mysql -uroot -p12345 -e 'create table test1.tb1(id int(10));'
    mysql -uroot -p12345 -e 'insert into  test1.tb1 values (1),(2),(3);'
    for i in {1..3} ;do ssh root@192.168.4.$i 'mysql -uroot -p12345 -e "select * from test1.tb1;"' ; done
    mysql: [Warning] Using a password on the command line interface can be insecure.
    id
    1
    2
    3
    mysql: [Warning] Using a password on the command line interface can be insecure.
    id
    1
    2
    3
    mysql: [Warning] Using a password on the command line interface can be insecure.
    id
    1
    2
    3