菜鸟涛学java

Mysql数据库自身提供的主从复制功能可实现数据的多处自动备份，实现数据库的拓展，多个数据备份可以加强数据的安全性，同时，通过实现读写分离提升数据库的负载性能。master数据库进行写操作，slave数据库进行读操作。在一主多从的数据库体系中，多个从服务器采用异步的方式更新主数据库的变化，业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行，读操作则是在各从服务器上进行。

主从复制原理

msyql之间数据复制的基础是二进制日志文件（binary log file）,一台mysql数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以事件的方式记录在二进制日志中，其他数据库作为slave通过一个IO线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化。则会把变化复制到自己的中继日志中，然后slave的一个sql线程会把相关的事件执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

实现粒度

详细的主从同步主要有三种形式：statement,row,mixed

• statement : 对数据库操作的sql语句写入binlog中
• row ：将每一条数据的变化写入binlog中
• mixed ：statement与row的混合

主从复制实现

| 节点 | host | ip |
| - | - | - |
| master | web-01 | 192.168.199.197 |
| slave | web-02 | 192.168.199.198 |
| slave | web-03 | 192.168.199.199 |

master节点配置

1、开启二进制日志及配置唯一的server-id

[root@bogon software]# cd /etc/
[root@bogon etc]# vim my.cnf

  
   [mysqld]
   character_set_server=utf8
   init_connect='SET NAMES utf8'
  
   #开启二进制日志
   log-bin=mysql-bin
  
   #配置唯一server-id
   server_id=197

2、重启msql

[root@bogon etc]# systemctl restart mysqld

3、获取master二进制日志文件名及位置（position）

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

mysql> 

4、创建同步数据用户帐号

mysql> GRANT replication slave ON *.* TO 'synchro'@'%' IDENTIFIED BY '!1Qaz@2Wsx';
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> 

• replication slave :分配复制权限
• *.* : 表示可操作数据库
• synchro ： 用户名
• % ：可以在所有地方使用帐号登录

slave节点的配置

1、配置唯一的server-id

web-02配置

[root@web-02 software]# vim /etc/my.cnf

   [mysqld]
   character_set_server=utf8
   init_connect='SET NAMES utf8'
   server_id=198

重启mysql服务

[root@web-02 software]# systemctl restart mysqld

使用master分配的用户账号读取master二进制日志

mysql> change master to master_host="192.168.199.197",master_user="synchro",master_password="!1Qaz@2Wsx",master_log_file="mysql-bin.000001",master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.09 sec)

mysql> start slave; #启用slave服务
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status;

查看是否配置成功

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.199.197
                  Master_User: synchro
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 592
               Relay_Log_File: web-02-relay-bin.000002
                Relay_Log_Pos: 758
        Relay_Master_Log_File: mysql-bin.000001
             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: 592
              Relay_Log_Space: 966
              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: 197
                  Master_UUID: 3a587846-551c-11ea-84c4-0800273ab3e0
             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)

ERROR: 
No query specified

mysql> 

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

表示主从复制配置成功，其他机器操作类似

注：如果主库有初始化数据时，那么在主从复制前需要把数据先导入到从库以保证数据一致。

参数说明

• master_log_file : slave中的io线程当前正在读取的主服务器二进制日志文件名称
• read_master_log_pos : 在当前的主服务器二进制日志中，slave中的io线程已经读取的位置
• relay_log_file : sql线程当前正在读取和执行的中继日志文件的名称
• relay_log_pos : 在当前的中继日志中，sql线程已经读取和执行的位置
• relay_master_log_file : 由sql线程执行的包含多数近期事件的主服务器二进制日志文件的名称
• slave_io_running : io线程是否被启动并成功连接到主服务器上
• slave_sql_running ： sql线程是否被启动
• seconds_behind_master : 从属服务器sql线程和从属服务器io线程之间的时间差距，单位：秒

从库同步延迟情况

• seconds_behind_master不为0，这个数值可能会很大
• relay_master_log_file和master_log_file显示bin-log的编号相差很大，说明bin-log在从库上没有及时同步
• mysql从库数据目录下存在大量mysql-relay-log日志，该日志同步完成之后就会被系统自动删除。

同步延迟产生原因

• 主库针对写操作，顺序写binlog，从库单线程去主库顺序读binlog，mysql的主从复制都是单线程操作，顺序写所以效率高，slave的slave_sql_running线程将主库的DDL和DML操作在slave实施，DDL和DML的io操作是随机的，不是顺序的，成本高，还可能slave上其他查询产生lock争用。
• 当主库的TPS并发较高时，产生的DDL数量超slave一个sql线程所能承受的范围。

首要原因：数据库在业务上读写压力太大，cpu计算负荷大，网卡负荷大，硬盘随机IO高。

次要原因：读写binlog带来的性能影响，网络传输延迟。

主从复制--异步复制原理、半同步复制和并行复制原理