目录

Life in Flow

知不知,尚矣;不知知,病矣。
不知不知,殆矣。

X

主从复制

架构

依赖于二进制日志,“实时”备份的一个多节点架构。

主从复制的前提(搭建主从复制),至少 2 个实例

11.不同的server_id(区别主从) 22.主库要开启二进制日志功能,从库不开(有些特殊情况下也可以开) 33.主库需要开启二进制日志 44.主库需要授权一个专用复制用户 55.主库数据备份 66.开启专用复制线程

准备架构

端口 作用
3307
3308
3309
15.5 MySQL 多实例 2 35.5.1 创建目录 4mkdir -p /data/330{7,8,9}/data 5 65.5.2 准备配置文件 7 8cat > /data/3307/my.cnf <<EOF 9[mysqld] 10basedir=/application/mysql 11datadir=/data/3307/data 12socket=/data/3307/mysql.sock 13log_error=/data/3307/mysql.log 14port=3307 15server_id=7 16log_bin=/data/3307/mysql-bin 17EOF 18 19cat > /data/3308/my.cnf <<EOF 20[mysqld] 21basedir=/application/mysql 22datadir=/data/3308/data 23socket=/data/3308/mysql.sock 24log_error=/data/3308/mysql.log 25port=3308 26server_id=8 27log_bin=/data/3308/mysql-bin 28EOF 29 30cat > /data/3309/my.cnf <<EOF 31[mysqld] 32basedir=/application/mysql 33datadir=/data/3309/data 34socket=/data/3309/mysql.sock 35log_error=/data/3309/mysql.log 36port=3309 37server_id=9 38log_bin=/data/3309/mysql-bin 39EOF 40 415.5.3 初始化三套数据 42mv /etc/my.cnf /etc/my.cnf.bak 43mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/application/mysql 44mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/application/mysql 45mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/application/mysql 46 475.5.4 systemd管理多实例 48cd /etc/systemd/system 49cp mysqld.service mysqld3307.service 50cp mysqld.service mysqld3308.service 51cp mysqld.service mysqld3309.service 52 53 54vim mysqld3307.service 55ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf 56 57vim mysqld3308.service 58ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf 59 60vim mysqld3309.service 61ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf 62 63 645.5.5 授权 65chown -R mysql.mysql /data/* 66 675.5.6 启动 68systemctl start mysqld3307.service 69systemctl start mysqld3308.service 70systemctl start mysqld3309.service 71 725.5.7 验证多实例 73netstat -lnp|grep 330 74mysql -S /data/3307/mysql.sock -e "select @@server_id" 75mysql -S /data/3308/mysql.sock -e "select @@server_id" 76mysql -S /data/3309/mysql.sock -e "select @@server_id"

主从复制搭建

1# 检查3307(主库)的二进制日志情况 2[root@localhost system]# mysql -S /data/3307/mysql.sock -e "show variables like '%log_bin%'"; 3+---------------------------------+----------------------------+ 4| Variable_name | Value | 5+---------------------------------+----------------------------+ 6| log_bin | ON | 7 8# 主库授权一个专门用于连接的用户 9[root@localhost system]# mysql -S /data/3307/mysql.sock 10mysql> grant replication slave on *.* to repl@'192.168.31.%' identified by '123'; 11Query OK, 0 rows affected, 1 warning (0.00 sec) 12 13# 主库进行数据备份 14[root@localhost ~]# mysqldump -S /data/3307/mysql.sock -A --master-data=2 -R -E --triggers --single-transaction >/tmp/full.sql 15 16# 恢复数据到从库3308 17[root@localhost system]# mysql -S /data/3308/mysql.sock 18mysql> set sql_log_bin=0; 19mysql> source /tmp/full.sql; 20 21 22# 查看备份使用的日志文件和起点 23[root@localhost ~]# vim /tmp/full.sql 24-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=448; 25 26# 告诉从库要从哪里复制信息 27mysql> CHANGE MASTER TO 28 MASTER_HOST='192.168.31.210', 29 MASTER_USER='repl', 30 MASTER_PASSWORD='123', 31 MASTER_PORT=3307, 32 MASTER_LOG_FILE='mysql-bin.000001', 33 MASTER_LOG_POS=448, 34 MASTER_CONNECT_RETRY=10; 35 36# 启动复制线程 37mysql> start slave; 38Query OK, 0 rows affected (0.00 sec) 39 40# 在从库上查看状态 41mysql> show slave status\G 42*************************** 1. row *************************** 43 Slave_IO_Running: Yes 44 Slave_SQL_Running: Yes 45

主从复制的工作流程

名词认识

1# 文件 2 主库: binlog(二进制日志文件) 3 从库: relay-log(中继日志)、master.info(主库信息文件)、relay-log.info(中继日志应用信息) 4 5# 线程 6 主库: binlog_dump-thread(二进制日志投递线程) 7 [root@localhost system]# mysql -S /data/3307/mysql.sock -e "show processlist"; 8 从库: IO_Thread(从库IO线程:请求和接收主库发过来的日志) 9 SQL_Thread(从库的SQL线程:回放从主库拿过来的日志)

主从监控

1# 主库 2[root@localhost system]# mysql -S /data/3307/mysql.sock -e "show processlist"; 3+----+------+----------------------+------+-------------+------+---------------------------------------------------------------+------------------ + 4| Id | User | Host | db | Command | Time | State | Info | 5+----+------+----------------------+------+-------------+------+---------------------------------------------------------------+------------------ + 6| 7 | repl | 192.168.31.210:43850 | NULL | Binlog Dump | 4278 | Master has sent all binlog to slave; waiting for more updates | NULL | 7| 10 | root | localhost | NULL | Query | 0 | starting | show processlist | 8+----+------+----------------------+------+-------------+------+---------------------------------------------------------------+------------------ 9 10# 从库 11mysql> show slave status\G 12*************************** 1. row *************************** 13 Slave_IO_State: Waiting for master to send event 14 Master_Host: 192.168.31.210 15 Master_User: repl 16 Master_Port: 3307 17 Connect_Retry: 10 18 Master_Log_File: mysql-bin.000001 19 Read_Master_Log_Pos: 448 20 Relay_Log_File: localhost-relay-bin.000002 21 Relay_Log_Pos: 320 22 Relay_Master_Log_File: mysql-bin.000001 23 Slave_IO_Running: Yes 24 Slave_SQL_Running: Yes 25 Replicate_Do_DB: 26 Replicate_Ignore_DB: 27 Replicate_Do_Table: 28 Replicate_Ignore_Table: 29 Replicate_Wild_Do_Table: 30 Replicate_Wild_Ignore_Table: 31 Last_Errno: 0 32 Last_Error: 33 Skip_Counter: 0 34 Exec_Master_Log_Pos: 448 35 Relay_Log_Space: 531 36 Until_Condition: None 37 Until_Log_File: 38 Until_Log_Pos: 0 39 Master_SSL_Allowed: No 40 Master_SSL_CA_File: 41 Master_SSL_CA_Path: 42 Master_SSL_Cert: 43 Master_SSL_Cipher: 44 Master_SSL_Key: 45 Seconds_Behind_Master: 0 46Master_SSL_Verify_Server_Cert: No 47 Last_IO_Errno: 0 48 Last_IO_Error: 49 Last_SQL_Errno: 0 50 Last_SQL_Error: 51 Replicate_Ignore_Server_Ids: 52 Master_Server_Id: 7 53 Master_UUID: 0e06a3df-b12b-11eb-a316-080027fb878b 54 Master_Info_File: /data/3308/data/master.info 55 SQL_Delay: 0 # 延时从库设定的时间 56 SQL_Remaining_Delay: NULL # 延时从库的剩余时间 57 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 58 Master_Retry_Count: 86400 59 Master_Bind: 60 Last_IO_Error_Timestamp: 61 Last_SQL_Error_Timestamp: 62 Master_SSL_Crl: 63 Master_SSL_Crlpath: 64 Retrieved_Gtid_Set: 65 Executed_Gtid_Set: 66 Auto_Position: 0 67 Replicate_Rewrite_DB: 68 Channel_Name: 69 Master_TLS_Version:

主从延时原因分析

1# 主库 21.日志写入不及时 :(sync_binlog=1) 32.主库并发业务较多:升级硬件、做业务拆分(分布式架构)、 43.从库太多 54.级联主从 6 7# 从库方便 8SQL线程只能有一个:5.7加入了MTS,真正实现了事务级别的并发SQL

延时从库

1# 3~6小时 2stop slave; 3CHANGE MASTER TO MASTER_DELAY=300; 4start slave 5show slave status\G;

作者:Soulboy