MySql主从复制介绍

mysql主从复制（MySQL Replication），是mysql自带的功能。
它使得MySQL数据库支持大规模高并发读写称为可能，同时有效地保护了物理服务器宕机场景的数据备份。
应用场景1：从服务器作为主服务器的实时数据备份

  1. 主从服务器架构的设置，可以大大加强MySQL数据库架构的健壮性。例如：当主服务器出现问题时，我们可以人工或设置自动切换到从服务器继续提供服务，此时从服务器的数据和宕机时的主数据库几乎是一致的。

应用场景2：主从服务器实时读写分离，从服务器实现负载均衡

  1. 主从服务器架构可通过程序（PHP、Java等）或代理软件（mysql-proxy、Amoeba）实现对用户（客户端）的请求读写分离，即让从服务器仅仅处理用户的select查询请求，降低用户查询响应时间及读写同时在主服务器上带来的访问压力。对于更新的数据（例如update、insert、delete语句）仍然交给主服务器处理，确保主服务器和从服务器保持实时同步。

Mysql主从复制工作流程

原理简介：
主从复制是通过重放binlog实现主库数据的异步复制。即当主库执行了一条sql命令，那么在从库同样的执行一遍，从而达到主从复制的效果。在这个过程中，master对数据的写操作记入二进制日志文件中(binlog)，生成一个 log dump 线程，用来给从库的 i/o线程传binlog。而从库的i/o线程去请求主库的binlog，并将得到的binlog日志写到中继日志（relaylog）中，从库的sql线程，会读取relaylog文件中的日志，并解析成具体操作，通过主从的操作一致，而达到最终数据一致。
技术图片
MySQL Replication一主多从的结构，主要目的是实现数据的多点备份（没有故障自动转移和负载均衡）。相比于单个的mysql，一主多从下的优势如下：

  • 如果让后台读操作连接从数据库，让写操作连接主数据库，能起到读写分离的作用，这个时候多个从数据库可以做负载均衡。
  • 可以在某个从数据库中暂时中断复制进程，来备份数据，从而不影响主数据的对外服务（如果在master上执行backup，需要让master处于readonly状态，这也意味这所有的write请求需要阻塞）。

就各个集群方案来说，其优势为：

  • 主从复制是mysql自带的，无需借助第三方。
  • 数据被删除，可以从binlog日志中恢复。
  • 配置较为简单方便。

其劣势为：

  • 从库要从binlog获取数据并重放，这肯定与主库写入数据存在时间延迟，因此从库的数据总是要滞后主库。
  • 对主库与从库之间的网络延迟要求较高，若网络延迟太高，将加重上述的滞后，造成最终数据的不一致。
  • 单一的主节点挂了，将不能对外提供写服务。

    搭建主从复制-文件准备

    分别创建主从数据库的数据存放目录和配置文件
    主数据库 ``` mkdir -p /home/mysqlms/master/data mkdir -p /home/mysqlms/master/conf.d
  1. 配置文件

vi /home/mysqlms/master/conf.d/my.cnf

  1. my.cnf主要内容如下

[mysqld] default-time_zone = ‘+8:00’ collation-server=utf8mb4_unicode_ci server-id=100 log-bin=master-bin binlog-format=row lower_case_table_names=1

  1. 从数据库

mkdir -p /home/mysqlms/slave/data mkdir -p /home/mysqlms/slave/conf.d

  1. 配置文件

vi /home/mysqlms/slave/conf.d/my.cnf

  1. my.cnf主要内容如下

[mysqld] server-id=101 read_only=1 default-time_zone = ‘+8:00’ collation-server=utf8mb4_unicode_ci lower_case_table_names=1

  2. > 从库的server-id不能和主库相同，主库要开启log-bin,从库设置为只读。<br />
  3. 其他配置参考后面给出，如果做读写分类，主库可以侧重写性能，从库则侧重读优化。
  6. ### 搭建主从复制-创建容器
  7. 创建一个docker-compose.yml，用compose部署这个服务。<br />如果是高版本的docker，并且开启了swarm，则会使用 `docker stack deploy`部署<br />Editor中编辑的内容

version: “2” services: mysql57m: image: mysql:5.7 ports:

  1.   - "4306:3306"
  2. command:
  3.   --character-set-server=utf8mb4
  4.   --collation-server=utf8mb4_general_ci
  5.   --default-authentication-plugin=mysql_native_password
  6. volumes:
  7.   - /home/mysqlms/master/conf.d:/etc/mysql/conf.d
  8.   - /home/mysqlms/master/data:/var/lib/mysql
  9. environment:
  10.   MYSQL_ROOT_PASSWORD: 123456

mysql57s: image: mysql:5.7 ports:

  1.   - "4307:3306"
  2. command:
  3.   --character-set-server=utf8mb4
  4.   --collation-server=utf8mb4_general_ci
  5.   --default-authentication-plugin=mysql_native_password
  6. volumes:
  7.   - /home/mysqlms/slave/conf.d:/etc/mysql/conf.d
  8.   - /home/mysqlms/slave/data:/var/lib/mysql
  9. environment:
  10.   MYSQL_ROOT_PASSWORD: 123456
  2. > 内容很简单，分别配置主从库的容器，映射3306端口，指定默认编码，挂载主机上的数据目录和配置文件目录，指定root密码
  5. 启动容器
  6. ### 搭建主从复制-数据库配置
  7. **进入到主库中:**   `docker exec -it mysqlms_mysql57m_1 bash`<br />登录主数据库

mysql -uroot -p123456

  1. 创建同步账号

mysql> CREATE USER ‘slave’@’%’ IDENTIFIED BY ‘123456’; mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO ‘slave’@’%’;

  1. 查看主库当前日志文件和位置

mysql> show master status;

  1. 记录下文件名`master-bin.000003`和位置值`14234`<br />**以同样方式登录从数据库**<br />
  2. 配置从库

mysql> change master to master_host=’mysql57m’,master_user=’slave’,master_password=’123456’,master_port=3306,master_log_file=’master-bin.000003’, master_log_pos=14234; mysql> start slave;

  2. > master_host，由于主从服务器是编排在一个服务中的，master_host直接用的主库服务名，实际可以是主库的容器ip；或者是主机ip，此时master_port要取映射到主机上的端口。<br />
  3. 因为主从数据库都是空库，没有手动复制数据的过程。从库配置过程中，主库不能接入客户端有任何操作，否则log-bin文件和偏移量可能会变。
  6. 查看从库同步状态

mysql> show slave status * 1. row * Slave_IO_State: Waiting for master to send event Master_Host: mysql57m Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 14234 Relay_Log_File: 4bb7c8dd7ea1-relay-bin.000002 Relay_Log_Pos: 321 Relay_Master_Log_File: master-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 14234 Relay_Log_Space: 535 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 100 Master_UUID: 6a476f6f-30b4-11eb-8271-0242ac130003 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

  1. 验证复制<br />
  2. 登录主库创建test数据库

CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE test;

CREATE TABLE user ( id int(11) NOT NULL, name varchar(200) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

  1. 登录从库

mysql> show databases; +——————————+ | Database | +——————————+ | information_schema | | mysql | | performance_schema | | phpmyadmin | | sys | | test | +——————————+ 6 rows in set (0.00 sec)

mysql> use test; mysql> show tables; +————————+ | Tables_in_test | +————————+ | user | +————————+ 1 row in set (0.00 sec)

  1. ### 主从数据不一致的情况说明:

1.网络的延迟 由于mysql主从复制是基于binlog的一种异步复制，通过网络传送binlog文件，理所当然网络延迟是主从不同步的绝大多数的原因，特别是跨机房的数据同步出现这种几率非常的大，所以做读写分离，注意从业务层进行前期设计。

2.主从两台机器的负载不一致 由于mysql主从复制是主数据库上面启动1个io线程，而从上面启动1个sql线程和1个io线程，当中任何一台机器的负载很高，忙不过来，导致其中的任何一个线程出现资源不足，都将出现主从不一致的情况。

3.max_allowed_packet设置不一致 主数据库上面设置的max_allowed_packet比从数据库大，当一个大的sql语句，能在主数据库上面执行完毕，从数据库上面设置过小，无法执行，导致的主从不一致。 4.key自增键开始的键值跟自增步长设置不一致引起的主从不一致。

5.mysql异常宕机情况下，如果未设置sync_binlog=1或者innodb_flush_log_at_trx_commit=1很有可能出现binlog或者relaylog文件出现损坏，导致主从不一致。

6.mysql本身的bug引起的主从不同步。

7.版本不一致，特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库上面不支持该功能。 以上就是常见的一些主从不同步的情况。或许还有其他的一些不同步的情况，请说出你所遇到的主从不一致的情况。 基于以上情况，先保证max_allowed_packet、自增键开始点和增长点设置一致，再者牺牲部分性能在主上面开启sync_binlog，对于采用innodb的库，推荐配置下面的内容： 1)、innodb_flush_logs_at_trx_commit = 1 2)、innodb-support_xa = 1 # Mysql 5.0 以上 3)、innodb_safe_binlog # Mysql 4.0 同时在从数据库上面推荐加入下面两个参数： 1)、skip_slave_start 2)、read_only

8.主库的从库太多，导致复制延迟 从库数据以3-5个为宜，要复制的从节点数量过多，会导致复制延迟

9.从库硬件比主库差，导致复制延迟 查看Master和Slave的系统配置，可能会因为机器配置不当，包括磁盘I/O、CPU、内存等各方面因素造成复制的延迟。一般发生在高并发大数据量写入场景中

10.慢SQL语句过多 假如一条SQL语句执行时间是20秒，那么从执行完毕到从库上能查到数据至少需要20秒，这样就延迟20秒了。 一般要把SQL语句的优化作为常规工作不断地进行监控和优化，如果单个SQL的写入时间长，可以修改后分多次写入。通过查看慢查询日志或show full processlist命令，找出执行时间长的查询语句或大的事务

11.主从复制的设计问题 例如主从复制单线程，如果主库写并发太大，来不及传送到从库，就会导致延迟。更高版本的Mysql可以支持多线程复制，门户网站则会开发自己的多线程同步功能。

12.主从库之间的网络延迟 主从库的网卡、网线、交换机等网络设备都可能成为复制的瓶颈，导致复制延迟。另外，跨公网的主从复制很容易导致主从复制延迟

13.主库读写压力大，导致复制延迟 架构的前端要加buffer及缓存层

```