MySQL主从复制

本文主要是介绍MySQL主从复制，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

MySQL主从复制

主从复制介绍
主从复制的搭建
- 主从复制搭建前提
- 搭建
主从复制原理
- 主从复制中涉及的文件
- 主从复制中涉及的线程
- 主从复制工作原理
主从复制监控
主从复制故障
主从延时监控及原因
- 主库方面原因
- 从库方面原因
- 主从延时的监控
- 主库方面
- 从库方面

主从复制介绍

1、主从复制基于binlog来实现的
2、主库发生新的操作，都会记录binlog
3、从库取得主库的binlog进行回放
4、主从的过程时异步

主从复制的搭建

主从复制搭建前提

1、2个或以上的数据库实例
2、主库要开启二进制日志
3、server_id要不同，区分不同的节点
4、主库需要建立专用的复制用户
5、从库通过备份主库，恢复主库的部分数据
6、人为告诉从库一些复制信息（ip port user passwd，二进制日志起点）
7、从库开启专门的复制线程

搭建

准备多实例

[root@test01 3307]# cd /data01/3307/data/
[root@test01 data]# ll
total 122920
-rw-r-----. 1 mysql mysql       56 Dec  1 20:52 auto.cnf
-rw-r-----. 1 mysql mysql      356 Dec  1 21:21 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Dec  6 20:53 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Dec  6 20:53 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Dec  1 20:52 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Dec  6 20:53 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Dec  1 20:52 mysql
drwxr-x---. 2 mysql mysql     8192 Dec  1 20:52 performance_schema
drwxr-x---. 2 mysql mysql     8192 Dec  1 20:52 sys
-rw-r-----. 1 mysql mysql        6 Dec  6 20:53 test01.pid
[root@test01 data]# rm -rf *
[root@test01 data]# cd ..
[root@test01 3307]# ls
data  my.cnf  mysql-bin.000001  mysql-bin.000002  mysql-bin.index  mysql.log  mysql.sock  mysql.sock.lock
[root@test01 3307]# rm -rf mysql-bin.*
[root@test01 3307]# mysqld --initialize-insecure --user=mysql --basedir=/aplication/mysql --datadir=/data01/3307/data
[root@test01 3307]# systemctl start mysqld3307
[root@test01 3307]# mysql -S /data01/3307/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit
Bye
[root@test01 3307]# mysql -S /data01/3307/mysql.sock -e "select @@port"
+--------+
| @@port |
+--------+
|   3307 |
+--------+

检查配置文件

二进制日志是否开启
两个节点的server_id

主库创建复制用户

[root@test01 3307]# mysql -uroot -p1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant replication slave on *.* to repl@'192.168.184.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select user,host from mysql.user;
+---------------+---------------+
| user          | host          |
+---------------+---------------+
| repl          | 192.168.184.% |
| root          | 192.168.184.% |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
| root          | localhost     |
+---------------+---------------+
5 rows in set (0.00 sec)

备份主库并恢复到从库

#主库备份数据
[root@test01 3307]# mysqldump -uroot -p1 -A --master-data=2 --single-transaction -R -E --triggers > /tmp/full.sql
#从库导入数据
```bash
[root@test01 3307]# mysql -S /data01/3307/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source /tmp/full.sql

告知从库关键复制信息
登入从库

help change master to   --查看change master to用法
CHANGE MASTER TO
  MASTER_HOST='192.168.184.128',  --主库主机名
  MASTER_USER='repl',  --主库用户名
  MASTER_PASSWORD='123',  --主库用户名的密码
  MASTER_PORT=3306,  --主库端口
  MASTER_LOG_FILE='master2-bin.004', 
  MASTER_LOG_POS=449,
  --MASTER_LOG_FILE和MASTER_LOG_POS从备份文件中的22行就可以看见
  --例如：
  --[root@test01 3307]# sed -n '22p'  /tmp/full.sql 
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000004', MASTER_LOG_POS=449;
  MASTER_CONNECT_RETRY=10; --主从断开后10次重连

实际操作：

[root@test01 3307]# mysql -S /data01/3307/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CHANGE MASTER TO
    ->   MASTER_HOST='192.168.184.128',
    ->   MASTER_USER='repl',
    ->   MASTER_PASSWORD='123',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='master2-bin.004',
    ->   MASTER_LOG_POS=449,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
#开启复制线程（IO，SQL）
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

6、检查主从复制状态

db01 [mysql]>show slave  status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
 Last_IO_Errno: 0
Last_IO_Error:   --详细错误信息
Last_SQL_Errno: 0
Last_SQL_Error:  --详细错误信息

主从复制原理

主从复制中涉及的文件

主库

binlog

从库

relaylog 中继日志
master.info 主库信息文件
relaylog.info relaylog应用的信息

主从复制中涉及的线程

主库：

binlog_dump Thread：DUMP_T

从库：

SLAVE_IO_THREAD：IO_T
SLAVE_SQL_THREAD：SQL_T

主从复制工作原理

在这里插入图片描述

主从复制监控

[root@test01 ~]# mysql -S /data01/3307/mysql.sock -e "show slave  status \G"
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event

##########################主库有关信息(master.info)###########
                  Master_Host: 192.168.184.128
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql_bin.000005     #主库的binlog信息
          Read_Master_Log_Pos: 154                  #主库的binlog信息


###############从库relay应用信息有关的(relay.info)############
               Relay_Log_File: test01-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000005


########################从库线程运行状态（排错）##############
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 


###################过滤复制有关信息###########################
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 


###################从库延时主库时间（秒）#####################
        Seconds_Behind_Master: 0


########################延时从库############################
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL

######################GTID复制有关的状态信息################
            Retrieved_Gtid_Set: 
            Executed_Gtid_Set:
                Auto_Position: 0

主从复制故障

从库：

IO_T线程故障
- 连接主库
网络，连接信息错误或变更了，防火墙，连接数上限
排查方案：
1、使用复制用户手动登录主数据库
解决：
1、stop slave
2、reset slave all；
3、重新change master to更新信息
4、start slave
- 请求binlog
主库没开binlog、binlog损坏，不存在、主库执行reset master
主库执行reset master的解决方法：
主库查看binlogr日志的时间点，执行show master status;
从库执行：
1、stop slave
2、reset slave all；
3、重新change master to更新信息
4、start slave
- 存储binlog到relaylog
查看relaylog权限等问题
重启主从，stop slave、start slave
SQL_T线程故障
- relay-log损坏
回放relaylog
主要是SQL执行失败导致
场景模拟：在从库建立了test数据库，后来又在主库建了test数据库，在主库的test库中建表，发现从库没得表
问题描述：在从库同步主库的命令的时，发现测试表已经存在
解决方法：
删除从库的测试表
把握一个原则，尽量已主库为准‘，进行反操作
或者重新构建主从

主从延时监控及原因

主库方面原因

binlog写入不及时

sync_binlog=1 优化参数，1秒就写入binlog日志

默认情况下dump_t是串行传输binlog

在并发事务量大，由于dump_t是串行工作的，导致传送日志慢
解决方案：
必须GTID，使用Group commit方式，可以支持DUMP_T并行

从库方面原因

传统复制

如果主库并发事务量很大，或者出现大事务，由于从库是单SQL线程，导致不管传的日志有多少，只能一次执行一个事务
5.7版本中有了增强的GTID，增加了新型的并发SQL线程模式（logical_clock）,MTS技术

主从硬件差异大
主从的参数配置
主从和从库索引不一致
版本差异

主从延时的监控

主库方面

主库：

mysql> show master status \G
*************************** 1. row ***************************
             File: mysql_bin.000005
         Position: 154

从库

[root@test01 ~]# mysql -S /data01/3307/mysql.sock -e "show slave  status \G"
*************************** 1. row ***************************
              Master_Log_File: mysql_bin.000005
          Read_Master_Log_Pos: 154

从库方面

[root@test01 ~]# mysql -S /data01/3307/mysql.sock -e "show slave  status \G"
*************************** 1. row ***************************
           #拿了多少
              Master_Log_File: mysql_bin.000005
          Read_Master_Log_Pos: 154
          #执行了多少
               Relay_Log_File: test01-relay-bin.000002
                Relay_Log_Pos: 320

[root@test01 ~]# mysql -S /data01/3307/mysql.sock -e "show slave  status \G"
*************************** 1. row ***************************
              Master_Log_File: mysql_bin.000005
          Read_Master_Log_Pos: 154

          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 528      
#对比Exec_Master_Log_Pos和 Read_Master_Log_Pos看执行了多少语句，来判断是否延时没拍成看是否是SQL_T的延时

这篇关于MySQL主从复制的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

MySql教程

MySQL主从复制

MySQL主从复制

主从复制介绍

主从复制的搭建

主从复制搭建前提

搭建

主从复制原理

主从复制中涉及的文件

主从复制中涉及的线程

主从复制工作原理

主从复制监控

主从复制故障

主从延时监控及原因

主库方面原因

从库方面原因

主从延时的监控

主库方面

从库方面

前端开发

后端开发

移动端开发

数据库

服务器运维

人工智能

区块链

游戏开发

网站运营

大数据/云计算

软件工程

软件/开发工具使用

资讯