浅谈MYSQL之主从复制
- MYSQL 主从复制形式
- MySQL 主从复制原理
- MySQL 主从复制过程
- MySQL 主从复制模式
- 实现主从复制异步模式
- 实验：主从模式复制冲突的解决
MySQL 主从复制概念
- MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。
- MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表
MySQL 主从复制主要用途
- 读写分离
在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。 - 复制：每个节点都有相同的数据集，向外扩展，基于二进制日志的单向复制
- 数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换
- 高可用HA
- 架构扩展
随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。
MYSQL 主从复制形式
- 一主一从架构
- 一主多从，提高系统的读性能
一主一从和一主多从是最常见的主从架构，实施起来简单并且有效，不仅可以实现HA，而且还能读写分离，进而提升集群的并发能力。 - 多主一从（从5.7开始支持）
多主一从可以将多个mysql数据库备份到一台存储性能比较好的服务器上。 - 双主复制
双主复制，也就是互做主从复制，每个master既是master，又是另外一台服务器的slave。这样任何一方所做的变更，都会通过复制应用到另外一方的数据库中。 - 级联复制
级联复制模式下，部分slave的数据同步不连接主节点，而是连接从节点。因为如果主节点有太多的从节点，就会损耗一部分性能用于replication，那么我们可以让3~5个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响。
MySQL 主从复制原理
MySQL主从复制涉及到三个线程，一个运行在主节点（log dump thread），其余两个(I/O thread, SQL thread)运行在从节点，如下图所示:
- 主节点 binary log dump 线程
当从节点连接主节点时，主节点会创建一个log dump 线程，用于发送 bin-log 的内容。在读取 bin-log 中的操作时，此线程会对主节点上的 bin-log加锁，当读取完成，甚至在发动给从节点之前，锁会被释放 - 从节点I/O线程
当从节点上执行start slave命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的 bin-log。I/O线程接收到主节点 binlog dump 进程发来的更新之后，保存在本地 relay-log 中。
- 从节点SQL线程
SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。 - 对于每一个主从连接，都需要三个进程来完成。
- 当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个binary log dump 进程，而每个从节点都有自己的I/O进程，SQL进程。
- 从节点用两个线程将从主库拉取更新和执行分成独立的任务，这样在执行同步数据任务的时候，不会降低读操作的性能。比如，如果从节点没有运行，此时I/O进程可以很快从主节点获取更新，尽管SQL进程还没有执行。
- 如果在SQL进程执行之前从节点服务停止，至少I/O进程已经从主节点拉取到了最新的变更并且保存在本地relay日志中，当服务再次起来之后，就可以完成数据的同步。
- 要实施复制，首先必须打开Master 端的binary log（bin-log）功能，否则无法实现
MySQL 主从复制过程
- 从节点上的I/O 进程连接主节点，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；
- 主节点接收到来自从节点的I/O请求后，通过负责复制的I/O进程根据请求信息读取指定日志指定位置之后的日志信息，返回给从节点。
- 返回信息中除了日志所包含的信息之外，还包括本次返回的信息的bin-log file 的以及bin-log position；
- 从节点的I/O进程接收到内容后，将接收到的日志内容更新到本机的relay log中，并将读取到的binary log文件名和位置保存到master-info 文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log 的哪个位置开始往后的日志内容，请发给我”；
- Slave 的 SQL线程检测到relay-log 中新增加了内容后，会将relay-log的内容解析成在祝节点上实际执行过的操作，并在本数据库中执行。
MySQL 主从复制模式
- MySQL 主从复制默认是异步的模式。MySQL增删改操作会全部记录在binary log中，当slave节点连接master时，会主动从master处获取最新的bin log文件。并把bin log中的sql relay。
异步模式如下图:
实现主从复制异步模式
- 主服务器设置
ntpdate ntp.ntsc.ac.cn / 同步时间
vim /etc/my.cnf
[mysqld]
server_id=2 / 局域网唯一标识，最好是IP地址最后一位
log-bin=/var/mysqllog/mysql-bin / 开始二进制日志
systemctl restart mysqld- 创建复制权限用户
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repliuser'@'172.20.54.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)- 查看二进制日志POS点
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 328 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
/ 二进制日志编号 以及POS编号后面会用到- 从服务器的设置
ntpdate ntp.ntsc.ac.cn / 同步时间
vim /etc/my.cnf
[mysqld]
server_id=1 / 局域网唯一标识，最好是IP地址最后一位
read_only=ON / 设置数据库只读，针对supper user无效
relay_log=/var/mysqllog/relay-log / relay log的文件路径，默认值hostname-relay-bin
systemctl restart mysqld- 使用有复制权限的用户账号连接至主服务器，并启动复制线程
CHANGE MASTER TO
MASTER_HOST='172.20.54.2', / 主服务器地址
MASTER_USER='repliuser', / 复制账户
MASTER_PASSWORD='123456', / 复制账户密码
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001', / 主服务器二进制日志
MASTER_LOG_POS=328, / 主服务器开始同步POS编号以后的数据
MASTER_CONNECT_RETRY=10; / 从服务器重连时间
STRAT SLAVE [IO_THREAD|SQL_THREAD]; / 开始复制线程，不指定直接开启2个- 查看从服务器详细参数
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.20.54.2
Master_User: repliuser
Master_Port: 3306
Connect_Retry: 10 / 主服务器重连时间
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 328 / 主服务器二进制日志开始的POS
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 555 / 中继日志
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes / 从服务器IO线程
Slave_SQL_Running: Yes / 从服务器SQL线程
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: 328
Relay_Log_Space: 858
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: 2
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)- 查看master.info信息
root@Centos7 mysqllog]# cat /data/mysql/master.info
33
mysql-bin.000001
461
172.20.54.2
repliuser
123456
3306
10
/ 其实就是储存CHANGE MASTER TO 命令的相关信息- 在主服务器上查看进程
MariaDB [(none)]> SHOW PROCESSLIST;
+----+-------------+-------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 10 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST | 0.000 |
| 11 | repliuser | 172.20.54.3:55732 | NULL | Binlog Dump | 1121 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0.000 |
+----+-------------+-------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+- 在主服务器修改数据
MariaDB [(none)]> CREATE DATABASE test01;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> CREATE TABLE test01.ceshi(id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> INSERT INTO test01.ceshi VALUES(1,'cehsi');
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SELECT * FROM test01.ceshi;
+------+-------+
| id | name |
+------+-------+
| 1 | cehsi |
+------+-------+
1 row in set (0.00 sec)- 在从服务器上查看进程
MariaDB [(none)]> SHOW PROCESSLIST;
+----+-------------+-----------+------+-----------+------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------+------+-----------+------+-----------------------------------------------------------------------------+------------------+----------+
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 11 | root | localhost | NULL | Sleep | 1066 | | NULL | 0.000 |
| 12 | system user | | NULL | Slave_IO | 1153 | Waiting for master to send event | NULL | 0.000 |
| 13 | system user | | NULL | Slave_SQL | 245 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0.000 |
| 15 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST | 0.000 |
+----+-------------+-----------+------+-----------+------+-----------------------------------------------------------------------------+------------------+----------+
9 rows in set (0.00 sec)- 在从服务器上查看数据
MariaDB [(none)]> SELECT * FROM test01.ceshi;
+------+-------+
| id | name |
+------+-------+
| 1 | cehsi |
+------+-------+
1 row in set (0.00 sec)主从复制异步同步模式实现
实验：主从模式复制冲突的解决
- 模拟主从复制从服务器出现error错误
/ 在从服务器上建立一个主服务器上没有的数据库test02
MariaDB [(none)]> CREATE DATABASE test02;
Query OK, 1 row affected (0.00 sec)
/ 在主服务器上创建数据库test02
MariaDB [(none)]> CREATE DATABASE test02;
Query OK, 1 row affected (0.00 sec)
/ 查看从服务器状态 已经报错
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.20.54.2
Master_User: repliuser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 2889
Relay_Log_File: relay-log.000004
Relay_Log_Pos: 849
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1049 / 错误编码
Last_Error: Error 'Unknown database 'test02'' on query. Default database: 'test03'. Query: 'CREATE TABLE test02.ceshi1 SELECT * FROM hellodb.teachers'- 解决办法一
MariaDB [(none)]> SET GLOBAL sql_slave_skip_counter=1049;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.20.54.2
Master_User: repliuser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 2889
Relay_Log_File: relay-log.000005
Relay_Log_Pos: 555
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: 1047
Exec_Master_Log_Pos: 2889
Relay_Log_Space: 1619
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: 2
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)
ERROR: No query specified- 解决办法二
vim /etc/my.cnf
slave_skip_errors=1007|ALL
systemctl restart mysqld完
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
